Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Simple DB2 query in REXX

Status
Not open for further replies.

guitara

Technical User
Jul 17, 2007
2
US
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!
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top