Rexx and DB2

/*-----------------------REXX-----------------------------------------*/


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

JCL for executing rexx in batch

//***** JOB STEP for executing REXX program that uses two arguments ******


//STEP01 EXEC PGM=IKJEFT01

//SYSEXEC DD DISP=SHR,DSN=UID.NARINDER.REXX

//SYSPRINT DD SYSOUT=*

//SYSTSPRT DD SYSOUT=*

//SYSTSIN DD *

TESTARG NARINDER SINGH

/*





/* REXX for above JCL job that uses two arguments */

ARG arg1 arg2

QUEUE arg1 arg2

"ALLOC FI(XXOUT) DA(REXX.OUTPUT) MOD REUSE"

IF RC \= 0 THEN

DO

SAY '*** ERROR - OUTPUT ALLOCATION FAILED: ABENDING'

EXIT 0

END

"EXECIO" Queued() "DISKW XXOUT (FINIS"

"FREE F(XXOUT)"

EXIT