Help needed for connect statement using sqlrpg

80 pts.
Tags:
AS/400
AS/400 Server
RPG
SQL
SQLRPGLE
Hi All,

I need to do some checking in 2 different AS400 servers. I will read the data from one server(say local) then using connect type 2, connect to the other server(say remote), check the data and then returns back to my local server. I'm using sqlrpg for this and created 2 pgms for this. One will read the local and the other will read the remote. For the first record everything happens to be fine. But when i read the next record in my local, and then try to fetch (m using like in sql and is fine) some selected record from the sql, the sql code is showing me -900. I have added a connect statement in my local too after that remote call is over. But i'm not getting how i can resolve this because if i remove this connect in local i'm getting -501.

Can somebody help me please?

Thanks in advance.



Software/Hardware used:
app. software

Answer Wiki

Thanks. We'll let you know when a new response is added.

Thanks a lot tom….

Discuss This Question: 7  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • TomLiotta
    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
    125,585 pointsBadges:
    report
  • 1607
    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.
    80 pointsBadges:
    report
  • 1607
    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
    80 pointsBadges:
    report
  • TomLiotta
    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
    125,585 pointsBadges:
    report
  • 1607
    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,
    80 pointsBadges:
    report
  • TomLiotta
    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
    125,585 pointsBadges:
    report
  • TomLiotta
    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
    125,585 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following