Sample DB2 REXX program

The below sample REXX program executes a SQL query.

/*******************************REXX*******************************/

Trace On
Main_Control:
Call Initproc
Call Process_A
Call PrepareSols
Call Closeproc
Return
Trace Off


Initproc:

SUBCOM 'DB2' /* Check environment isn't */
/* already set up */
IF RC > 0 THEN DO
ADDRESS TSO "REXXCOMM ADD DB2 REXXDB2 REXX-DB2 I-FACE'"
END


/*------------------------ CONNECT TO DB2 ----------------------------*/

SSID = "DB2P" /* setup DB2 Subsystem */
ADDRESS DB2 "LOGON" SSID /* Connect to subsystem */
IF RC = 0 THEN
NOP
ELSE DO
SAY "Unable to logon..."
EXIT
END



Process_A:

OUTDS = "PDS.OUTDS.OUPUT"
OUTDS = "'" || OUTDS || "'"
"ALLOC DSN("OUTDS") DDN(OUTPT) SHR REUSE"
If RC ¬= 0 Then
Do
Say "File Allocation Failed.."
Call CloseProc
End

FromDate = "2003-04-05"
ToDate = "2004-04-05"
FromDate = Strip(FromDate) || "-23.59.59.999999"
ToDate = Strip(ToDate) || "-00.00.00.000000"


A_Sql = "SELECT A.T_K,A.E_T",
" FROM CIL.T_C A,CIL.C_C B",
" WHERE A.T_S_CD = 'E'",
" AND A.E_T > '"Strip(FromDate)"'",
" AND A.E_T < '"Strip(ToDate)"'",
" AND A.O_S_I = 'X'",
" AND A.T_K = B.C_C_ID",
" AND A.T_U_NO LIKE ('A04 - - 811%')",
" WITH UR;"


ADDRESS DB2 "SQL" A_Sql

If RC = 0 then
Do
If DB2ROWS = 0 then
NOP
Else
Do
No_Of_A04s = Strip(DB2ROWS,'L',0)
ADDRESS TSO


Do i = 1 to No_Of_A04s
QUEUE "--" || i || "--------------------A-811-----------------------"
QUEUE "UPDATE CILXA1A.TR"
QUEUE " SET TR_S_C = 'G'"
QUEUE " WHERE O_S_ID = 'XE'"
QUEUE " AND T_K = '"Strip(T_K.i)"'"
QUEUE " AND E_T = '"Strip(E_T.i)"'"
QUEUE " AND T_S_CD = 'E';"
QUEUE "COMMIT;"
End
End
End
Else


Do
Say "REXX SQL Execution Failed..Cannot Continue..."
Say "Program Aborting.."
Call Closeproc
End
Return

PrepareSols:
If (No_Of_A04s = 0) Then
Do
Say "No Errors Today"
Return
End

CCSVar. = ''
Do i = 1 to QUEUED()
PULL Record
CCSVar.i = Record
End

If No_Of_A04s ¬= 0 Then
Do
"EXECIO * DISKW OUTPT (STEM CCSVar. FINIS"
"DELSTACK"
"FREE F(OUTPT"
Drop TILVar
Address ISPEXEC "SETMSG MSG(CC001)"
DelStack
End
Return

Closeproc:
/******** Logoff From db2**/

ADDRESS DB2 "LOGOFF"
IF RC = 0 THEN
Exit
ELSE
DO
SAY "RC = "DB2RC" WITH REASONCODE =" DB2REASON "FROM LOGOFF",
"TO" SSID
SAY = "Logoff NOT Successful from "SSID
END
Return