|
View previous topic :: View next topic
|
| Author |
Message |
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
Hi,
Sorry, this is a combination of Db2 & Rexx. Main code issues could be in Rexx, so posting in Rexx forum.
I am writing a Db2 Stored procedure which would run an external REXX program which tries to run DSNTEP2 program. When i run the rexx program in TSO, Rexx program successfully executes DSNTEP2 but when calling via stored procedure, i don't get any result.
Below is the Stored Procedure definition
| Code: |
--#SET TERMINATOR `
CREATE PROCEDURE SUSHANTH.RUNQUERY
(IN MEMFILE VARCHAR(50) FOR SBCS DATA CCSID EBCDIC ,
OUT CMDRESULT VARCHAR(32704) FOR SBCS DATA CCSID EBCDIC )
DYNAMIC RESULT SETS 1
EXTERNAL NAME 'RUNQUERY'
LANGUAGE REXX PARAMETER CCSID EBCDIC PARAMETER STYLE GENERAL
NOT DETERMINISTIC FENCED CALLED ON NULL INPUT MODIFIES SQL DATA
NO PACKAGE PATH NO DBINFO NO COLLID WLM ENVIRONMENT DB2DAE17
ASUTIME NO LIMIT STAY RESIDENT YES PROGRAM TYPE MAIN
SECURITY USER INHERIT SPECIAL REGISTERS
STOP AFTER SYSTEM DEFAULT FAILURES RUN OPTIONS 'TRAP(OFF)'
COMMIT ON RETURN NO `
--#SET TERMINATOR ;
COMMIT;
|
Below is the Rexx Program : RUNQUERY
| Code: |
/** INPUTS **/
PARSE UPPER ARG MEMFILE
MEMFILE = SPACE(MEMFILE)
A = date()' 'time()
A = A' Executing 'MEMFILE
SAY A
OUTFILE = 'ATEST.SUSHANTH.TEP2.TEST'
/** GET DB2 GROUP NAME **/
DB2QRY = 'SELECT CURRENT MEMBER FROM SYSIBM.SYSDUMMY1'
DB2MEMBR = RUN_QUERY('DB2D', DB2QRY)
SAY 'DB2 MEMBER : 'DB2MEMBR
DB2MEMBR = SUBSTR(DB2MEMBR,1,3)||'0'
SAY 'REPLACING MEMBER NAME WITH DB2 GROUP NAME : 'DB2MEMBR
/** Turning off messages **/
MSGX = MSG(OFF)
/** Initial FREE **/
"FREE F(SYSPRINT)"
"FREE F(SYSIN)"
/** Read Input **/
"ALLOC DA('"MEMFILE"') F(INPUT) SHR REUSE"
"EXECIO * DISKR INPUT (STEM SQLIN. FINIS"
"FREE F(INPUT)"
SAY 'Input : 'MEMFILE
SAY 'Lines Read : 'SQLIN.0
/** Allocating DSNTEP2 datasets **/
"ALLOC F(SYSIN) NEW RECFM(F B) LRECL(80) SP(20 20) CYL"
"EXECIO * DISKW SYSIN (FINIS STEM SQLIN."
/*
DO I=1 TO SQLIN.0
SAY SQLIN.I
END
*/
"ALLOC F(SYSPRINT) CYL SPACE(50,50) UNIT(VIO) NEW"
/** RUN DSNTEP2 **/
DROPBUF
QUEUE 'DSNE'
QUEUE "RUN PROGRAM(DSNTEP2) PLAN(DSNTEP2)",
"LIB('SYSDB2.'"DB2MEMBR"'.RUNLIB.LOAD')"
QUEUE 'END'
X= OUTTRAP(BCOM.)
ADDRESS ATTCHMVS "DSNESM71"
X= OUTTRAP("OFF")
DROPBUF
SAY 'BCOM = 'BCOM.0
/** Read Outputs **/
"EXECIO * DISKR SYSPRINT (STEM CMDOUT. FINIS "
/** Print Outputs on screen **/
DO CO = 1 TO CMDOUT.0
TEMP = STRIP(CMDOUT.CO, 'T')
SAY STRIP(TEMP, 'T')
END
/** Write outputs **/
SAY 'WRITING TO DATASET : 'OUTFILE
"ALLOC FI(OUTREC) DA('"OUTFILE"') MOD REUSE",
"CYL SPACE(50,50) LRECL(133) RECFM(F,B,A) DSORG(PS)"
"EXECIO * DISKW OUTREC (STEM CMDOUT. FINIS"
"FREE F(OUTREC)"
"FREE F(SYSPRINT)"
"FREE F(SYSIN)"
MSGX = MSG(ON)
EXIT 'OUTPUT IS AVAILABLE IN 'OUTFILE
/** FUNCTIONS **/
RUN_QUERY: PROCEDURE
SSID = STRIP(ARG(1))
SQLSTMT = SPACE(STRIP(ARG(2)))
SAY 'SQL STMT : ' SQLSTMT
ADDRESS TSO "SUBCOM DSNREXX" /* HOST CMD ENV AVAILABLE */
IF RC THEN /* IF NOT, MAKE IT AVAILABLE */
DO /* ADD HOST CMD ENVIRONMENT */
S_RC = RXSUBCOM('ADD','DSNREXX','DSNREXX')
END
ADDRESS DSNREXX "EXECSQL DECLARE C1 CURSOR FOR S1"
ADDRESS DSNREXX "EXECSQL PREPARE S1 FROM :SQLSTMT"
ADDRESS DSNREXX "EXECSQL OPEN C1"
I=1
A = ''
FETCH = "EXECSQL FETCH C1 INTO :DB2MEMBR"
DO UNTIL SQLCODE <> 0
ADDRESS DSNREXX FETCH
IF SQLCODE = 0 THEN
DO
A = DB2MEMBR
END
END
IF SQLCODE = 100 THEN NOP
ELSE
DO
CALL SQLCA
SAY "RETURN CODE " RC
END
/* WHEN DONE WITH DSNREXX, REMOVE IT */
S_RC = RXSUBCOM('DELETE','DSNREXX','DSNREXX')
RETURN A
SQLCA:
SAY 'SQLCODE = ' SQLCODE
SAY 'SQLSTATE = ' SQLSTATE
RETURN
|
When running in TSO, it would be like below, i would specify the Db2 Group where it needs to connect. When trying to run via stored procedure DSN SYSTEM is not required.
| Code: |
/** RUN DSNTEP2 **/
"NEWSTACK"
QUEUE "RUN PROGRAM(DSNTEP2) PLAN(DSNTEP2)",
"LIB('SYSDB2."DB2MEMBR".RUNLIB.LOAD')"
QUEUE "END"
"DSN SYSTEM ("DB2MEMBR")"
DB2_RC = RC
/* Say 'DB2_RC = 'DB2_RC */
"DELSTACK"
|
Thanks,
Sushanth |
|
| Back to top |
|
 |
Pedro
Global Moderator
Joined: 01 Sep 2006
Posts: 2608
Location: Silicon Valley
|
|
|
|
You are using the default host command environment of TSO. When you run your rexx program in a TSO environment, it works correctly.
When you run your rexx in a non-TSO environment, it likely does not. I think the ALLOC and FREE commands will not work (maybe others). Try using BPXWDYN to perform the alloc and free functions.
It is not clear if you have already worked with DB2 Stored Procedures... my suggestion is to have a small 'hello world' program and get that to work correctly and then increasingly adding your logic. That is, start small, make sure it works, then add a little bit, then make sure it works, etc... |
|
| Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
Hi Pedro,
ALLOC, FREE, EXECIO works for instance in the above program SQLIN. will contain queries which it had read from member. In the bottom on the main function, if i change the EXECIO from CMDOUT. to SQLIN while calling the stored procedure. Query gets written to dataset.
| Code: |
/** Write outputs **/
SAY 'WRITING TO DATASET : 'OUTFILE
"ALLOC FI(OUTREC) DA('"OUTFILE"') MOD REUSE",
"CYL SPACE(50,50) LRECL(133) RECFM(F,B,A) DSORG(PS)"
"EXECIO * DISKW OUTREC (STEM SQLIN. FINIS"
|
Above program is the built up of small multiple programs which i had tested, when i combined it DSNTEP2 part alone is not running.
So far i have setup stored procedure to execute BIND, FREE and single DML statements. Flow of call would be a REST API call would be made from off-host to Db2 REST service which will call the Db2 REXX Stored procedure.
When executing multiple statements from a member and programming it via REXX-Db2, it could get complex real fast, where statements could be a mix of SELECT & DML statements. I wanted to tryout with DSNTEP2 because, it will handle multiple statements, error handling and produce report for the execution. DSNTEP2 worked successfully when called directly from REXX in TSO. But its not working when called from Stored procedure. No errors during execution and no results as well.
Thanks,
Sushanth |
|
| Back to top |
|
|
Pedro
Global Moderator
Joined: 01 Sep 2006
Posts: 2608
Location: Silicon Valley
|
|
|
|
Can you show us the JCL you use when you run DSNTEP2 from a batch job?
Here is the example for the DB2 manual:
| Code: |
//RUNTEP2 EXEC PGM=IKJEFT01,DYNAMNBR=20
//SYSTSPRT DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(DSN)
RUN PROGRAM(DSNTEP2) PLAN(DSNTEPB1) PARMS('/ALIGN(LHS) MIXED TOLWARN(YES)') -
LIB('DSN1110.RUNLIB.LOAD')
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSIN DD *
SELECT * FROM DSN8B10.PROJ; |
But I recall that you should also have PROFILE PREFIX(mypfx) as the first line of the SYSTSIN. |
|
| Back to top |
|
|
Pedro
Global Moderator
Joined: 01 Sep 2006
Posts: 2608
Location: Silicon Valley
|
|
|
|
Show us how you call your rexx program from TSO.
The first line of the rexx is:
| Code: |
| PARSE UPPER ARG MEMFILE |
so you must be calling the rexx program with the name of the data set for the input file, but I do not know enough about stored procedures to understand how you specify that data set name there. |
|
| Back to top |
|
|
|
|
|
