SQLRPG

Are you trying to use RPG/400 for this? How old are your systems? (I.e., what OS/400 version/release is on both?) Is there a reason you can’t use ILE RPG?

Can you show us the compile statements for the two programs? Prompt the commands, press <F14=Command string> and copy/paste the resulting command strings.

I have added a connect statement in my local too after that remote call is over.

And did you RELEASE (or DISCONNECT) and COMMIT before leaving the remote work? Did you SET CONNECTION back to the *LOCAL database?

It might be easier if we saw the SQL sequences.

Tom

Hi,
I’m using SQLRPGLE type with version as V6R1(both). PGM A which is running is just compile as RDB as local. And for PGM B, i’ve compiled with RDB as the remote server name which i’ve already added using WRKRDBDIRE & connect method as DUW& i’ve seen the package got created in the remote too. In PGM B, i’ve used Connect in *INZSR & before leaving issued Disconnect. I used to call this PGM B from PGM A & i’ve added one Connect to local again in PGM A after calling PGM B, as below —
PGM A PGM B
Read file A Connect remote
Do while do something ……….
fetch using SQL Disconnect remote
Call PGM B using some param
Connect local
do something…
Enddo

First time its all fine in PGM A & B. Next time the SQL cursor seems to work indifferently with different codes(-900 & -501).
===> CRTSQLRPGI OBJ(LIB/TEST) COMMIT(*NONE) RDB(REMOTE) USER(ABCDE) PASSWORD()
SQLPKG(lib/TEST)
Currently i’m creating the package in QGPL in remote. First time fetch is working fine in PGM A, but problem happens after PGM B is called.

Hi,
dropping again regarding the pgm specs.

PGM A
Read file A
Do while
fetch using SQL-> here it breaks while moving 2nd time(-900)
Call PGM B using some param
Connect local (i used connect reset too)
do something…
Enddo
——–
PGM B
Connect remote
do something(sql fetch)
disconnect remote
Commit

fetch using SQL-> here it breaks while moving 2nd time(-900)
Call PGM B using some param
Connect local

I’m not quite certain that I understand that sequence. It looks as if you are CALLing PGMB while still connected to *LOCAL. Then after returning, you want to CONNECT to *LOCAL and iterate the DO-loop to FETCH from the previous CONNECT again.

That is, when PGMB is activated, the connection is already set to *LOCAL and the connection is actively in use.

You will probably want PGMA and PGMB to run in two different activation groups. You will want as much isolation and control as you can get. Keeping the connections separated might be enough in your case. The activation groups can stay active, so you shouldn’t be concerned yet about that. You might simply name the AGs PGMA and PGMB.

Are you doing any SQL updates (UPDATE , INSERT or DELETE)? I don’t have a lot of trouble with basic access, but it takes more care across multiple connections when updating.

Tom

Thanks Tom.
It looks as if you are CALLing PGMB while still connected to *LOCAL. – yes thats true. And when i go inside PGMB i connect to remote. Fetch something and disconnect,commit & then again return to PGMA. It is working infact. Now it creates problem in PGMA when try to do some fetch from the local even after doing a connect to local. May be AG’s might solve this problem. Currently i just remove all the SQL from PGMA to resolve the same & is working fine too. I’m just preparing a report.
Another thing i would like to ask regarding connection to multiple server – do you have any example using SQLCONNECT() API to do the same stuff? Is it possible to share with me in case you have one?
Regards,

I’m just preparing a report.

If you are only using SELECT/FETCH in the two programs, then basic activation group separation should work fine. I have done it that way without complications. The earliest working code doing that that I have right now is on fully PTFed V5R3. Anything later should be even more certain (assuming DB2 PTFs are appropriate!).

Tom

do you have any example using SQLCONNECT() API to do the same stuff?

No, not that I can post here and that would help you at the same time. Direct coding of SQLConnect() is easy enough, but there’s a lot of necessary supporting code. The “simple” examples that I have are calls to stored procs rather than direct data retrieval.

SQL CLI coding is essentially the same as ODBC coding on Windows, with pretty much the same APIs. To handle data rows, it can take quite a few lines of code to supply the APIs with all of the column data definitions. If you don’t already have procs pre-coded, it takes a lot of first-time setup work.

I suggest starting here — CLI Programs in RPG. This will probably be handy to look through — SQL CLI Frequently Asked Questions.

I can get into specifics or maybe fine details if you hit obstacles, but SQL CLI is a big subject to cover.

Tom