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
--#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 ;
Below is the Rexx Program : RUNQUERY
/** INPUTS **/
PARSE UPPER ARG MEMFILE
MEMFILE = SPACE(MEMFILE)
A = date()' 'time()
A = A' Executing 'MEMFILE
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
Joined: 01 Sep 2006 Posts: 2227 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...
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.
/** 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.