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
This topic is locked: you cannot edit posts or make replies. Need help on If condition continuatio... ashok_uddaraju CLIST & REXX 4 Fri May 19, 2017 8:55 am
No new posts Job failing with USER = 4093 REASON C... Pradeepa S ABENDS & Debugging 1 Wed May 17, 2017 3:35 pm
No new posts COBOL Code Parsers and Lineage Establ... balimanja COBOL Programming 2 Tue May 02, 2017 3:30 am
No new posts Cobol upgrade - source code missing f... gthmrj IBM Tools 1 Wed Apr 26, 2017 6:04 pm
No new posts Running an REXX EXEC implicitly sho7hills CLIST & REXX 3 Thu Apr 20, 2017 8:14 am


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