Developer needs advice from Infromix guru 
Author Message
 Developer needs advice from Infromix guru

I am developing a batch job using transactions that span databases on
two different servers.  My plan was to use remote synonyms and write
the job as a stored procedure, but the DBAs fear that remote synonyms
may take down the target server, is anyone aware of this as a problem
(v7.31)?

Their suggestion is to reference the remote tables with explicit path

don't want to hard code environment specific information into a
procedure that has to live in dev, qa, and prod environments.  Is
there any way to make this dynamic within the procedure?  Also, will
begin/rollback/commit work within this procedure given that part of
the transaction will be remote?

TIA - Chris



Sun, 21 Mar 2004 00:20:51 GMT
 Developer needs advice from Infromix guru


Quote:
>I am developing a batch job using transactions that span databases on
>two different servers.  My plan was to use remote synonyms and write
>the job as a stored procedure, but the DBAs fear that remote synonyms
>may take down the target server, is anyone aware of this as a problem
>(v7.31)?

That's a bizarre suggestion. Can these DBA quote a case history and an exact
engine version they saw this hypothetical bug on? Even if there was a bug in
such an area, it would have to be very version-specific and very quickly
nailed down. After all, synonyms ain't brain surgery. They're just a
substitute for an explicit table name.

POSSIBLY they used synonyms once to communicate with a remote engine that
had grossly underallocated logs or something. In the course of a fairly big
transaction they may have deadlocked the other engine. But the exact same
problem would have happened if they used an explicit remote table name
instead of the synonym, and indeed if they did the same amount of work by
opening the remote database explicitly. This suggested problem would be
fixed by correct sizing of whatever resources ran out.

Summary: yeah right... Relax, don't worry.

Quote:
>Their suggestion is to reference the remote tables with explicit path

>don't want to hard code environment specific information into a
>procedure that has to live in dev, qa, and prod environments. Is
>there any way to make this dynamic within the procedure?

Nup - standard SPL is not dynamic. Can't prepare statements. Synonyms are
exactly the tool for the job. Or a view. Or a view on synonyms. Whatever.

Quote:
>Also, will
>begin/rollback/commit work within this procedure given that part
>of the transaction will be remote?

Informix transactions can cope correctly with multiple engines (except for
one clearly documented situation - RTFM for that). The databases need to be
in the same logging mode. Apart from that there are no serious issues apart
from possible deadlocks (which timeout). Some deadlocks can't be detected
100% when remote databases are involved, so there is built-in facility to
time-out suspect transactions. Once again, RTFM to understand the details.
You do need to be slightly thoughtful depending on the complexity of your
actions and the actions of other programs that may be modifying rows at the
same time on either engine.
--
Space Corps Directive #592
In an emergency situation involving two or more
officers of equal rank, seniority will be granted to
whichever officer can program a VCR.
    -- Red Dwarf
..  ... .--. .. -  --- -.  --- .-. .- -.-. .-.. .


Sun, 21 Mar 2004 07:48:51 GMT
 Developer needs advice from Infromix guru

Quote:
> I am developing a batch job using transactions that span databases on
> two different servers.  My plan was to use remote synonyms and write
> the job as a stored procedure, but the DBAs fear that remote synonyms
> may take down the target server, is anyone aware of this as a problem
> (v7.31)?

> Their suggestion is to reference the remote tables with explicit path

> don't want to hard code environment specific information into a
> procedure that has to live in dev, qa, and prod environments.  Is
> there any way to make this dynamic within the procedure?  Also, will
> begin/rollback/commit work within this procedure given that part of
> the transaction will be remote?

You can dynamically create SQLs using bladelet in SPLs, search www.iiug.org
for details regarding this.  You need to do some work in putting in the
bladelet into your database engine (I wasn't the one who installed it into
our engine so I don't know how it was done, but I guess the information in
the IIUG archives would help you acheiving this).
Quote:
> TIA - Chris



Sun, 21 Mar 2004 21:00:08 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. Developer needs advice from Informix guru

2. Guru Advice Needed for Query on Interesting Data Scenario

3. SQL Server Guru urgently needed for setup advice

4. guru advice needed on temporary table problem

5. Guru advice needed:16-bit app crashes Windows.

6. Guru's advice needed on dbms_job related question

7. Web Server Gurus...Advice Needed !!!

8. In Need of FileMaker Guru for advice on complex Date/Time calculation

9. Need advice from a database guru

10. Happy New Year Visual Basic Gurus- Developers needed nationwide-(Recruiter)

11. SEEKING INFORMATION FROM INFROMIX DEVELOPER

12. SEEKING INFORMATION FROM INFROMIX DEVELOPER


 
Powered by phpBB® Forum Software