Sample code for coding sql in rexx

ofer71 · Posted: Fri Oct 13, 2006 1:09 am

Have you tried the fine manual for some good examples?

O.

jz1b0c · Posted: Tue Oct 17, 2006 8:16 pm

Here you go with an example:

* REXX */
TRACE O
PARSE ARG CMD3
SUBSYS = 'DB2E'
/* CONNECT DB2 */
ADDRESS TSO "SUBCOM DSNREXX"
IF RC <> 0 THEN S_RC = RXSUBCOM('ADD','DSNREXX','DSNREXX')
ADDRESS DSNREXX "CONNECT" SUBSYS
IF RC <> 0 THEN DO
CALL EXITKO 'CONNECT TO' SUBSYS 'KO' SQLCODE
ADDRESS DSNREXX "DISCONNECT"
EXIT
END
/* SQL REQUEST */
SQLCMD = "SELECT"
SQLCMD = SQLCMD || " A.USBD_NAME"
SQLCMD = SQLCMD || " ,A.USBD_PROGRAMMER"
SQLCMD = SQLCMD || " ,B.USTSO_LOGON_PROC"
SQLCMD = SQLCMD || " FROM"
SQLCMD = SQLCMD || " R5M00.USER_BD A"
SQLCMD = SQLCMD || " ,R5M00.USER_TSO_DATA B"
SQLCMD = SQLCMD || " WHERE"
SQLCMD = SQLCMD || " B.USTSO_LOGON_PROC = '"CMD3"'"
SQLCMD = SQLCMD || " AND B.USTSO_NAME = A.USBD_NAME"
SQLCMD = SQLCMD || " ORDER BY"
SQLCMD = SQLCMD || " 1 , 2"
/* SQL REQUEST PROCESS */
ADDRESS DSNREXX "EXECSQL DECLARE C1 CURSOR FOR S1"
ADDRESS DSNREXX "EXECSQL PREPARE S1 FROM :SQLCMD"
ADDRESS DSNREXX "EXECSQL DESCRIBE S1 INTO :O"
ADDRESS DSNREXX "EXECSQL OPEN C1"
IF RC <> 0 THEN DO
CALL EXITKO 'OPEN C1 KO' SQLCODE
ADDRESS DSNREXX "DISCONNECT"
EXIT
END
I = 1
/* FORMATTED DISPLAY */
ADDRESS ISPEXEC
TABLE = 'REC'||RANDOM(0,99999)
'TBCREATE 'TABLE' KEYS(CODEUSER)
NAMES(NOM TSOPROC) NOWRITE'
IF RC <> 0 THEN CALL EXITKO 'CHECK ON TBCREATE' RC
DO FOREVER
ADDRESS DSNREXX "EXECSQL FETCH C1 INTO :CL1 , :CL2 , :CL3"
IF SQLCODE ^= 0 THEN LEAVE
CODEUSER = CL1
NOM = CL2
TSOPROC = CL3
'TBADD 'TABLE''
IF RC <> 0 THEN CALL EXITKO 'CHECK ON TBADD' RC
I = I + 1
END
ADDRESS ISPEXEC
ZWINTTL = 'LIST.RACF - SEARCH FOR LOGON PROCEDURE'
'ADDPOP'
'TBTOP 'TABLE''
'TBDISPL 'TABLE' PANEL(PATSOLOG) AUTOSEL(NO)'
'REMPOP'
'TBCLOSE 'TABLE''
IF RC <> 0 THEN CALL EXITKO 'CHECK ON TBCLOSE' RC
ADDRESS DSNREXX "DISCONNECT"
EXIT
/* KO MANAGEMENT */
EXITKO: PROCEDURE
PARSE ARG MESSAGE SQLCODE
IF MESSAGE = '' THEN DO
MESSAGE = 'ERROR :'
END
SAY MESSAGE SQLCODE
EXIT
RETURN

sankarsclm · New User Joined: 11 Oct 2006 Posts: 4

You can also use the following program as an example

/* REXX */
ADDRESS TSO "SUBCOM DSNREXX"
IF RC THEN
DO
S_RC =RXSUBCOM('ADD ','DSNREXX','DSNREXX')
IF S_RC=0 THEN
RXSTATUS = 'ADDED'
ELSE RXSTATUS = 'ISNOTADDED'
END
ADDRESS 'DSNREXX '
SSID='DSN1'
"CONNECT" SSID
SAY SQLCODE
IF SQLCODE <> "0" THEN
DO
SAY "FAILED TO CONNECT TO THE DATABASE"
SAY SQLCODE
EXIT 8
END
SQLSTMT = "SELECT EMPID,EMPNAME FROM EMP"
EXECSQL "DECLARE C1 CURSOR FOR S1"
EXECSQL "PREPARE S1 INTO :OUTSQLDA FROM :SQLSTMT"
EXECSQL "OPEN C1"
DO UNTIL (SQLCODE ?= 0)
EXECSQL "FETCH C1 USING DESCRIPTOR :OUTSQLDA"
IF SQLCODE = 0 THEN
DO
LINE = ''
LINE1 = ''
DO I = 1 TO OUTSQLDA.SQLD
LINE = LINE OUTSQLDA.I.SQLDATA
LINE1 = LINE1 OUTSQLDA.I.SQLLEN
END I
SAY LINE
SAY LINE1
END
END
EXIT

In the above program
SSID='DSN1'
This is where you have provide the DB2 region you want to access.