Is there a way to call a DB2 stored procedure via JCL or SPUFI?

Question

I am trying to execute my stored procedure like this:

CREATE PROCEDURE DWR000A.SELECT_PSID_RIZ(
  IN P_CONN INT                          
 ,IN P_PLNN INT                          
)                                        
LANGUAGE SQL                             
RESULT SETS 1                            
READS SQL DATA                           
BEGIN                                    
  DECLARE C1 CURSOR FOR                  
  SELECT PSID,                           
         JOBINFO                         
  FROM DWR000A.PSLOGT                    
  WHERE CONN = P_CONN                    
  AND PLNN = P_PLNN;                     
  OPEN C1;                               
END             

I am a beginner trying to learn IBM DB2 stored procedures. I am trying to execute my simple stored procedure without the need to write a host Cobol program. Is there a way to achieve that via DB2 SPUFI or JCL? Is there an easier way to execute a stored procedure?

I tried using JCL RUNNING PROC DSNTEP2 BUT getting below error. I am not sure what I am doing wrong since no issue during creating my stored procedure.

000002 PAGE    1                                                               
 000003 ***INPUT STATEMENT:                                                     
 000004  EXEC SQL                                                               
 000005       CALL SELECT_PSID_RIZ(761,01)                                      
 000006  END-EXEC                                                               
 000007   ;                                                                     
 000008 SQLERROR ON   EXEC      COMMAND, PREPARE   FUNCTION                     
 000009  RESULT OF SQL STATEMENT:                                               
 000010  DSNT408I SQLCODE = -84, ERROR:  UNACCEPTABLE SQL STATEMENT             
 000011  DSNT418I SQLSTATE   = 42612 SQLSTATE RETURN CODE                       
 000012  DSNT415I SQLERRP    = DSNHAPL2 SQL PROCEDURE DETECTING ERROR           
 000013  DSNT416I SQLERRD    = 1  0  0  -1  10  101 SQL DIAGNOSTIC INFORMATION  
 000014  DSNT416I SQLERRD    = X'00000001'  X'00000000'  X'00000000'  X'FFFFFFFF
 000015           INFORMATION                                                   
  
                                                                                

Can someone kindly assist and advice. Appreciate any help

Updated 11/30/2024 after comments below, I ran using command CALL SELECT_PSID_RIZ(761,01). However I still get error the same

***INPUT STATEMENT:                                                     
      CALL SELECT_PSID_RIZ(761,01)                                      
  ;                                                                     
SQLERROR ON   CALL      COMMAND, PREPARE   FUNCTION                     
 RESULT OF SQL STATEMENT:                                               
 DSNT408I SQLCODE = -84, ERROR:  UNACCEPTABLE SQL STATEMENT             
 DSNT418I SQLSTATE   = 42612 SQLSTATE RETURN CODE                       
 DSNT415I SQLERRP    = DSNHAPL2 SQL PROCEDURE DETECTING ERROR           
 DSNT416I SQLERRD    = 1  0  0  -1  1  101 SQL DIAGNOSTIC INFORMATION   
 DSNT416I SQLERRD    = X'00000001'  X'00000000'  X'00000000'  X'FFFFFFFF
          INFORMATION                                                                                                                           
   

Here's a screenshot of the DB2 stored procedure that does exist in my system:

 RQRL          -------------- RC/Q Routine List -------------- 11-30-2024 04:57 
COMMAND ===>                                                  SCROLL ===> CSR  
                                                                               
 DB2 Object ===> R                        Option ===> L    Where => N          
       Name ===> SELECT_PSID_RIZ      >   Schema ===> *                      > 
Sp. Name ===> *                       >    Owner ===> *                      > 
 Version ===> *                       >  R. Type ===> *                        
Loc: LOCAL ---------- SSID: DBB0 ----------ORMA001 -          LINE 1 OF 1    > 
CMD      ROUTINE NAME       SCHEMA   VERSION  ACT SPECIFIC NAME      TYPE      
________ SELECT_PSID_RIZ    DWR000A  V1       Y   SELECT_PSID_RIZ    PROC      
******************************* BOTTOM OF DATA ********************************      

                    

Appreciate any inputs and suggestion. Thanks

Answer 1

You can call a DB2 stored procedure in JCL using a DSNTEP2 or DSNTEP4 utility, or by embedding the SQL CALL statement in a COBOL program that is executed through JCL. Here's an example using DSNTEP2: JCL Example:

//STEP01   EXEC PGM=IKJEFT01,REGION=0M
//SYSTSPRT DD  SYSOUT=*
//SYSPRINT DD  SYSOUT=*
//SYSUDUMP DD  SYSOUT=*
//SYSIN    DD  DUMMY
//SYSTSIN  DD  *
DSN SYSTEM(DB2X)
RUN PROGRAM(DSNTEP2) PLAN(DSNTEP2) -
    PARMS('SQL')
END
/*
//SYSIN    DD  *
CALL MYSCHEMA.MY_STORED_PROC('param1', 'param2');
/*

Replace DB2X with your DB2 subsystem name.

Answer 2

Instead of this - EXEC SQL
CALL SELECT_PSID_RIZ(761,01)
END-EXEC
;

give this - CALL SELECT_PSID_RIZ(761,01)
;

Answer 3

The EXEC SQL and END-EXEC is only needed for embedding SQL in application programs (COBOL, PL/I, C, ASM,...). With DSNTEP2 you just put your SQL-statement as is and terminate it with a ;. You might get away with omitting the semicolon for the last statement in your input.