ADDRESS TSO "SUBCOM DSNREXX" /* DSNREXX ENVIRONMENT AVILABLE?*/
IF RC <> 0 THEN
S_RC = RXSUBCOM('ADD','DSNREXX','DSNREXX')
SSID='ssid'
SQ1='SELECT Query'
ADDRESS DSNREXX
"CONNECT" SSID
"EXECSQL DECLARE C1 CURSOR FOR S1"
DROP OUTSQLDA1
"EXECSQL PREPARE S1 INTO :OUTSQLDA1 FROM :SQ1"
"EXECSQL OPEN C1"
IF SQLCODE ¬= 0 THEN DO
SAY 'ERROR OPENING CURSOR'
EXIT 0
END
"EXECSQL FETCH C1 USING DESCRIPTOR :OUTSQLDA1"
DO UNTIL SQLCODE ¬= 0
IF SQLCODE = 0 THEN DO
select_field1 = STRIP(OUTSQLDA1.1.SQLDATA)
select_field2 = STRIP(OUTSQLDA1.2.SQLDATA)
END
"EXECSQL FETCH C1 USING DESCRIPTOR :OUTSQLDA1"
END
"EXECSQL CLOSE C1"
ADDRESS "TSO"
/*-------------------------- END OF PROGRAM --------------------------*/
REXX to update/insert/delete data in a DB2 table
/*-----------------------REXX-----------------------------------------*/
ADDRESS TSO "SUBCOM DSNREXX" /* DSNREXX ENVIRONMENT AVILABLE?*/
IF RC <> 0 THEN
S_RC = RXSUBCOM('ADD','DSNREXX','DSNREXX')
SSID='ssid'
SQ1='INSERT/UPDATE/DELETE Query'
ADDRESS DSNREXX
"CONNECT" SSID
"EXECSQL PREPARE S1 FROM :SQ1"
"EXECSQL EXECUTE S1"
"EXECSQL COMMIT"
SAY ' --> SQLCODE='SQLCODE
ADDRESS "TSO"
RETURN
/*-------------------------- END OF PROGRAM --------------------------*/
The above Rexx can also be executed from within a job(JCL) as shown bellow:
//************************************************
//STEP010 EXEC PGM=IKJEFT01,DYNAMNBR=75
//SYSPROC DD DISP=SHR,DSN=Your-REXX-Library
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSTSIN DD *
YOURREXX-NAME
/*
Common Errors:
1)
Job abended with MAXCC=12.
spool information in SYSTSPRT
+++ S_RC = RXSUBCOM('ADD','DSNREXX','DSNREXX')
Error running YOURREXX-NAME, line 62: Routine not found
You may have to add a SDSNLOAD library(that contains a member RXSUBCOM) to joblib in your JCL.
e.g.
//JOBLIB DD DSN=ssidPRD.ssid.SDSNLOAD,DISP=SHR