How can my REXX access remote DB2 table?

[bajistaman]

I am working on a mainframe, OS/390, DB2 version 7. I go into DB2 Interactive (DB2I Primary Menu) to run my SPUFI:

SELECT * FROM DB2_table_name;

I get this connection error:

SQLCODE = -204, ERROR: DB2_table_name IS AN UNDEFINED NAME

I get this error because I am running on a local DB2 Subsystem, named localA, but the table I am accessing is on a remote DB2 Subsystem, called remoteB (resides on a different server).

To fix that problem, on my DB2I Spufi screen I set my 'CONNECT LOCATION' to the remote location:

For remote SQL processing:
10 CONNECT LOCATION ===> remoteB

and rerun my spufi successfully:

DSNE625I CONNECT TO LOCATION remoteB PERFORMED, SQLCODE IS 0
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0

My problem is: I want to run the SELECT statement from my REXX, which looks like:

SQLSTMT = "SELECT * FROM DB2_table_name"

ADDRESS TSO "SUBCOM DSNREXX" /* HOST CMD ENV AVAILABLE ? */
IF RC THEN S_RC = RXSUBCOM('ADD','DSNREXX','DSNREXX')

ADDRESS DSNREXX "CONNECT" localA /*tried remoteB as well - neither works*/

ADDRESS DSNREXX "EXECSQL PREPARE S1 FROM :SQLSTMT1"

etc...

The above works when my DB2_table_name is on a local DB2 subsystem, but fails with the connection error when the table is on a remote subsystem. How do I execute the 'CONNECT LOCATION ===> remoteB' from within my REXX? What is it that DB2I is doing 'behind the scenes' to allow remote SQL processing, and how do I mimic that within my REXX?

Thanks for your help.

 

[stevexff]

DB2 knows about the remote locations. Do a

SELECT * FROM SYSIBM.SYSLOCATIONS

to see them all, and so you can maybe change your query accordingly.

SELECT * FROM [i]location.owner.DB2tablename[/i]

might do the trick. I've never tried it with a PREPARE though...

 

[bajistaman]

steveexff,

Thank you for your reply. I made the query you suggested:

SELECT * FROM SYSIBM.LOCATIONS /* we don't have table SYSLOCATIONS but just LOCATIONS - I think it changed names with DB2 version5*/

and got this back:

---------+---------+---------+---------+----
LOCATION LINKNAME IBMREQD PORT
---------+---------+---------+---------+----
remoteB lname1 port#

When I ran the REXX with these statements:

SELECT * FROM remoteB.owner.DB2tablename

and

ADDRESS DSNREXX "CONNECT" localA

I got this error:
SQLCODE = -30072
SQLSTATE = 58016
Programmer Response: The connection to the server has been broken, and
the server has therefore rolled back the unit of work. In this case, the
only SQL statement that may be successfully executed is ROLLBACK. However,
if the requester detects this error on a COMMIT, then it is unknown
whether the unit of work was committed or rolled back at the server.

Any suggestions?

Questions:
1) What is the linkname lname1 on table SYSIBM.LOCATIONS for?
2) On my CONNECT command I connect to the local DB2, not the remote, correct?

Thanks for your help.

 

[stevexff]

[ol][li]Don't know, but I'm guessing it gets set up when the links are built.[/li]
[li]Correct.[/li][/ol]
Your DBA should be able to set up the two DB2 subsystems localA and remoteB so that they can talk to each other. Then it's just a case of connecting to the local DB2 and specifying a three-level name, and DB2 takes care of the rest. Unless RACF gets in the way.