Simple DB2 query in REXX

[guitara]

I'm trying to perform a simple DB2 query from rexx, that will return the results to me on screen. I'm running rexx from an ispf edit session. This is as far as i've managed to piece together

/* REXX */
SUBSYS = DB2D
SQLSTMT1 ="my sql here"

ADDRESS TSO "SUBCOM DSNREXX"
IF RC THEN S_RC = RXSUBCOM('ADD','DSNREXX','DSNREXX')

subsys is correct, and the sql is correct as well (i lifted it straight from a spufi request). I know i need to connect, and do some other things. BTW, this code produces no errors or any other output, so the address tso subcom command must be working. Thanks!

 

[Bartym]

Our process steps are

1) connect to DB2

/*Rexx*/
parse arg DB2SYS
Address TSO "SUBCOM DSNREXX"
IF RC THEN
S_RC = RXSUBCOM('ADD','DSNREXX','DSNREXX')
Address DSNREXX "CONNECT "DB2SYS

2) Run SQL

SQLSTMT = "SELECT REFCOLSEQ,REFCOLNAME
FROM RELATIONS
WHERE RELATION_TYPE = 'R' AND
CREATOR = ? AND
TBNAME = ? AND
REFCREATOR = ? AND
REFTBNAME = ? AND
RELNAME = ?
ORDER BY 1"

Address DSNREXX "EXECSQL PREPARE S4 INTO :TBSQLDA FROM :SQLSTMT"

Address DSNREXX "EXECSQL DECLARE C4 CURSOR FOR S4"

Address DSNREXX "EXECSQL OPEN C4 USING ",
":iSoCre,",
":iSoTab,",
":iRfCre,",
":iRfTab,",
":iRelName"
** the host variables refer back to the ? in the sql **

Address DSNREXX "EXECSQL FETCH C4 USING DESCRIPTOR :TBSQLDA"

Address DSNREXX "EXECSQL CLOSE C4"

3) Disconnect from DB2

Address DSNREXX "DISCONNECT"

The above is only a single fetch. Normally we would code a Do While SQLCODE = 0.

The two columns are returned in a stem variable.
REFCOLSEQ in TBSQLDA.1.SQLDATA
REFCOLNAME in TBSQLDA.2.SQLDATA

Other interesting variables are
SQLCODE
SQLWARN.0 to SQLWARN.10
SQLERRD.0 to SQLERRD.6
SQLERRP
SQLERRM

Hope this helps

 

[guitara]

thanks - it's working now!