I have tried to call the SQL stored procedure with output parameter within a CL program using QZDFMDB2 to run SQL interactively in a CL program and I have succeeded a lot in that. Now, I can call the SQL stored procedure with the output parameter in the CL program, the only drawback that I face is I am not getting the output parameter value returned in my CL program.

To call the SQL stored procedure with output parameter in the CL program I have used the, so I created a SQL variable and passed that as a return parameter in the form of a command string to program QZDFMDB2 that allows to run SQL statements in CL program. This makes my first work done i.e. calling a SQL stored procedure in a CL program with output parameters which earlier seemed very difficult or almost impossible. So congrats for this success that we have achieved so far.

Now, the turn is for receiving the return value in the output parameter which is  SQL variable here. So, it was sad but we are not getting return value from SQL stored procedure in our CL program. So, I decided to use some tricks and tweaks so that I would anyway receive the return value or value returned by the output parameter   SQL stored procedure in my CL program. Let me show you the code and steps that I followed to achieve this:

1. Create a SQL variable using the CREATE VARIABLE statement from STRSQL.

   create or replace variable out char(1) default ' '

2. Write a SQL stored procedure that returns the output parameter.

   CREATE OR REPLACE PROCEDURE STORED33(                    
           IN P_NAME CHAR(20),                              
           IN P_GENDER CHAR(1),                             
           OUT SUCCESS CHAR(1)                              
            )                                               
   SPECIFIC STORED33                                        
   BEGIN                                                    
   DECLARE SQLSTATE CHAR(5) DEFAULT ' ';                    
   DECLARE SQLCODE INTEGER DEFAULT 0;                       
                                                            
   DECLARE STMT CHAR(500) DEFAULT ' ';                      
                                                            
   INSERT INTO PF8_D(NAME,GENDER) VALUES(P_NAME,P_GENDER);  
   IF SQLCODE = 0 THEN                                      
     SET SUCCESS = '1';                                     
   SET STMT = 'CREATE OR REPLACE VARIABLE TESTLIB.OUT '  
              CONCAT 'CHAR(1) DEFAULT ''1''';               
   EXECUTE IMMEDIATE STMT;                                  
   ELSE                                                     
    SET SUCCESS = '0';                                     
   SET STMT = 'CREATE OR REPLACE VARIABLE TESTLIB.OUT ' 
              CONCAT 'CHAR(1) DEFAULT ''0''';              
   EXECUTE IMMEDIATE STMT;                                 
   END IF;                                                 
   END   

   In the SQL stored procedure code, a few lines are written intentionally such as we are writing create or replace SQL variables and setting their values as 1 or 0 according to SQL code value. Here, the SQL variable works as a return value for my CL program. Please note that SQL variable value is accessible to the same session in which its value is set by default during the create variable statement or SET statement.

3. Compile the SQL stored procedure using the RUNSQLSTM command.

   RUNSQLSTM SRCFILE(TESTLIB/STOREDPROC) 
             SRCMBR(STORED33)               
             COMMIT(*NONE)                  
             DBGVIEW(*SOURCE)    

4. Create a view in the qtemp library using the CREATE VIEW statement.

   create or replace view qtemp.rtnval   as (   
   select   testlib.out as                   
   rtn  from sysibm.sysdummy1 ) rcdfmt rtnvalf  

5. Write a CL program calling SQL stored procedure with the output parameter.

   pgm                                                     
   dcl        var(&cmd) type(*char) len(200)               
   dcl        var(&in1) type(*char) len(20) value('name1') 
   dcl        var(&in2) type(*char) len(1)  value('M')     
   dcl        var(&sqlout) type(*char) len(1)              
   dcl        var(&LIB) type(*char) len(10) +              
                value('TESTLIB')                        
   DCLF       FILE(rtnval)                                 
                                                           
   CHGVAR     VAR(&CMD) VALUE('create or replace variable +
                testlib.out char(1) default '' ''')     
   call       pgm(qzdfmdb2) parm((&cmd))                   
                                                           
   CHGVAR     VAR(&CMD) VALUE('CALL +                      
                testlib.STORED33(''' *CAT &IN1 *TCAT +  
                ''' , ''' *CAT &IN2 *TCAT ''', ' +         
                *TCAT &lib *TCAT '.OUT)')                  
   call       pgm(qzdfmdb2) parm((&cmd))   
                   
   RUNSQL     SQL('create or replace view qtemp/rtnval as + 
                (select testlib.out as rtn from +        
                sysibm.sysdummy1) rcdfmt rtnvalf') +        
                COMMIT(*NONE)                               
   RCVF                                                     
   CHGVAR     VAR(&SQLOUT) VALUE(&RTN)    
                     
   return                                                   
   endpgm  

   Please, note that in step 1 we created a SQL variable named OUT in the TESTLIB library so that we can later create a view RTNVAL in qtemp library using that at step 4. For your knowledge as we have declared the view rtnval in my CL program so that I can populate that view with the value that was set by SQL stored procedure in SQL variable (SQL global variable).
   At the beginning of the program, we create an SQL variable out in testlib with a default blank value by passing the create or replace variable statement to the QZDFMDB2 program as a parameter in the string format.
   Then, we call the SQL stored procedure from within the CL program with input and output parameters by first building the &cmd string, and in, we pass the SQL variable instead of any CL program variable. Then we pass it to the QZDFMDB2 program as a parameter to run the SQL stored procedure. Now till this point, we have not received any return value from the SQL stored procedure call using QZDFMDB2. However, within the SQL stored procedure script, we did set the respective success/failure value in the SQL variable globally and now I am ready to receive that in my CL program which would be the actual return value by my SQL stored procedure call.
   So, I selected the global SQL variable value and output it in a view named rtnval, and after that, I just read the view using the RCVF command and evaluated the value from the view column &rtn to the cl program variable &sqlout. Therefore, &sqlout would not contain the return value or the output parameter value that should be returned by the actual SQL stored procedure call within the CL program with the output parameter

We can call SQL stored procedure from within the CL program using the QZDFMDB2 program which is the datalink file manager DB2 CLP program present in the QSYS library.

pgm                                                     
dcl        var(&cmd) type(*char) len(200)               
dcl        var(&in1) type(*char) len(20) value('test') 
dcl        var(&in2) type(*char) len(1)  value('F')                                                                                                                               
               
CHGVAR     VAR(&CMD) VALUE('CALL +                      
             testlib.STORED33B(''' *CAT &IN1 *TCAT +  
             ''' , ''' *CAT &IN2 *TCAT ''')')                  
call       pgm(qzdfmdb2) parm((&cmd))   
                  
return                                                   
endpgm  

We need to build the command string which is the SQL stored procedure CALL statement with the required parameter. Later we pass that command string to the program qzdfmdb2 which executes SQL interactively from within the CL program.

ILE RPG data types mapped to SQL data types:

You can use this SQL to replace the carriage return and line feed characters (X’0D25′) with spaces (X’4040′).

update libname/filename
set fieldname = replace(fieldname, X'0D25', X'4040')
where fieldname like '%' concat X'0D25' concat '%'

Note: Please take a backup of your file before running the update to verify your results and in case any issue occurs you may then be able to restore your original file data.

We can extract the week from a YYYYMMDD formatted date using the week() function and substr() function. Please use the below SQL query to achieve this.

select week(substr(char(datefield),1,4) concat '-' concat
            substr(char(datefield),5,2) concat '-' concat
            substr(char(datefield),7,2)) as weeknumber
from libraryname/filename

or you can simply run the SQL query to test one of the values using a dummy table.

select week(substr(char(20240909),1,4) concat '-' concat
            substr(char(20240909),5,2) concat '-' concat
            substr(char(20240909),7,2)) as weeknumber
from sysibm.sysdummy1

The output week is 37.