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
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"
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.
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.
Joined: 01 Sep 2006 Posts: 2227 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.
