Coding SQL statements in REXX

shreejita · New User Joined: 16 Aug 2009 Posts: 60 Location: chennai

Hi All,

Plz help me to write a REXX program using SQL query with example.

Help me with sample code to run DB2 - REXX program.

I saw in manuals but could not understand.

Regards
Shreejita

dick scherrer · Posted: Wed Oct 14, 2009 8:58 pm

Hello and welcome to the forum,

Please post what you found in the manuals that is not clear. Someone here should be able to clarify.

Also, there are multiple topics in the forum of using SQL in REXX.

John Alexander1 · Posted: Thu Oct 22, 2009 6:31 am

Hi
below is a simple example, selecting 3 columns from a table, to test just change the table & column names and run .
Hope this helps.

/* Rexx */
PARSE UPPER ARG SSID

"ISPEXEC VGET ZSYSID"
"ISPEXEC VGET ZPREFIX"

SAY
SAY'SSN = 'SSID
SAY

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

ADDRESS DSNREXX "CONNECT" SSID
ADDRESS DSNREXX "EXECSQL SET CURRENT PACKAGESET='DSNREXUR'"

SQL='SELECT COL1, COL2, COL3 ',
'FROM CRT.TABLE_XYZ ',
'WITH UR'
ADDRESS DSNREXX "EXECSQL DECLARE C1 CURSOR FOR S1"
K=1
IF SQLCODE¬=0 THEN CALL BYE
ADDRESS DSNREXX "EXECSQL PREPARE S1 INTO :OUTSQLDA FROM :SQL "
K=2
IF SQLCODE¬=0 THEN CALL BYE
ADDRESS DSNREXX "EXECSQL OPEN C1"
K=3
IF SQLCODE¬=0 THEN CALL BYE
N=0

DO UNTIL(SQLCODE=100)
ADDRESS DSNREXX "EXECSQL FETCH C1 USING DESCRIPTOR :OUTSQLDA "

ADDRESS DSNREXX "EXECSQL FETCH C1 INTO :HV1 :HV1IND,
:HV2 :HV2IND,
:HV3 :HV3IND"
IF SQLCODE=0 THEN DO
N=N+1
DO I=1 TO OUTSQLDA.SQLD /* FOR EACH COLUMN */
END
SAY 'LINE 'N' 'HV1' 'HV2' 'HV3
END
END

EXIT

BYE:

SAY'SQLCODE='SQLCODE
SAY'SQLSTATE='SQLSTATE
SAY'SQLERRMC='SQLERRMC
OUTCODE=SQLCODE-K*1000
SAY
SAY'OUTCODE=SQLCODE-K*1000='OUTCODE
SAY

EXIT OUTCODE

shreejita · New User Joined: 16 Aug 2009 Posts: 60 Location: chennai

Thanks for the help

Regards
Shreejita