IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Execute DSNTEP2 in REXX which is called from Db2 Stored Proc


IBM Mainframe Forums -> CLIST & REXX
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1020
Location: India

PostPosted: Wed Mar 04, 2020 3:27 pm
Reply with quote

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
View user's profile Send private message
Pedro

Global Moderator


Joined: 01 Sep 2006
Posts: 2545
Location: Silicon Valley

PostPosted: Thu Mar 05, 2020 8:00 am
Reply with quote

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
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1020
Location: India

PostPosted: Fri Mar 06, 2020 11:46 am
Reply with quote

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
View user's profile Send private message
Pedro

Global Moderator


Joined: 01 Sep 2006
Posts: 2545
Location: Silicon Valley

PostPosted: Sat Mar 07, 2020 3:23 am
Reply with quote

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
View user's profile Send private message
Pedro

Global Moderator


Joined: 01 Sep 2006
Posts: 2545
Location: Silicon Valley

PostPosted: Sat Mar 07, 2020 3:38 am
Reply with quote

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
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> CLIST & REXX

 


Similar Topics
Topic Forum Replies
No new posts Running REXX through JOB CLIST & REXX 13
No new posts Error to read log with rexx CLIST & REXX 11
No new posts isfline didnt work in rexx at z/OS ve... CLIST & REXX 7
No new posts run rexx code with jcl CLIST & REXX 15
No new posts Execute secondary panel of sdsf with ... CLIST & REXX 1
Search our Forums:

Back to Top