Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Sample code for coding sql in rexx

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> CLIST & REXX
View previous topic :: :: View next topic  
Author Message
ofer71

Global Moderator


Joined: 27 Dec 2005
Posts: 2360
Location: Israel

PostPosted: Fri Oct 13, 2006 1:09 am    Post subject:
Reply with quote

Have you tried the fine manual for some good examples?

O.
Back to top
View user's profile Send private message

jz1b0c

Active User


Joined: 25 Jan 2004
Posts: 160
Location: Toronto, Canada

PostPosted: Tue Oct 17, 2006 8:16 pm    Post subject:
Reply with quote

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
Back to top
View user's profile Send private message
sankarsclm

New User


Joined: 11 Oct 2006
Posts: 4

PostPosted: Thu Oct 19, 2006 2:04 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> CLIST & REXX All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts JCL to Set Return code based on DB2 S... vinu78 DB2 17 Mon Mar 13, 2017 9:47 pm
No new posts REXX cdoe to purge a JOB from spool a... Sulabh Agrawal CLIST & REXX 11 Thu Mar 09, 2017 11:36 am
No new posts IMS region inactive - User abend code... gthmrj IMS DB/DC 5 Tue Mar 07, 2017 12:29 pm
No new posts Overriding PS data in rexx Shaheen Shaik CLIST & REXX 8 Fri Mar 03, 2017 5:08 pm
No new posts CA Workload Automation event code help vinu78 CA Products 10 Wed Mar 01, 2017 10:05 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us