How to call external stored procedure from SQL stored procedure and handle commitment control

user_9868034 0
Viewed 364

I am trying to call a external stored procedure from Sql stored procedure by passing two parameters. one of the parameters is expected to return back with a string value, which when arrives I need to stop the data to be committed in any file.

To explain the situation - SP1 (SQL stored procedure) will call SP2 (External stored procedure), which will call RPGLE program PGM1, which will call another RPGLE program PGM2.

Now I am tasked to handle commitment control of File1 and File 2 used in PGM2 from SP1. If at any point File1 is updated and File2 gives an error while updating any record, data from File 1 should also be rolled back. but this rollback should happen in SP1.

So far I have tried to split this issue in two parts-

PARTA - How to Call External stored procedure from SQL stored procedure.

PARTB - How to handle commitment in SQL stored procedure in essence, if PGM2 gives back error data should be rolled back.

Below is the piece of code so far I have tried. But have no luck.

CREATE OR REPLACE PROCEDURE MYLIB.SP1 (    
                    IN PRINPUT CHAR(1200) ,       
                    INOUT PRERR CHAR(50) )        
SPECIFIC MYLIB.SP1                         
BEGIN                                             
  DECLARE SQLSTATE CHAR(5) DEFAULT ' ';           
  DECLARE SQLCODE INTEGER DEFAULT 0;              
    CALL MYLIB.SP2(PRINPUT, PRERR);      
      IF SQLCODE = 0 THEN                         
        COMMIT;                                   
      ENDIF;                                      
END

Any suggestion/Guidance is appreciated.

rpgle ibm-midrange db2 stored-procedures
2 Answers

Try this my friend: On SP1

BEGIN                                             
  DECLARE SQLSTATE CHAR(5) DEFAULT ' ';           
  DECLARE SQLCODE INTEGER DEFAULT 0;    
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;         
    CALL MYLIB.SP2(PRINPUT, PRERR);      
      IF SQLCODE = 0 THEN                         
        COMMIT;
      ELSE 
        ROLLBACK;                          
      ENDIF;                                      
END

Answers:

PARTA: If the Ext Stored Proc exists, the way you're calling it, it's correct.

PARTB: The set transaction scopes all the rows changed from the moment is issued, to the execution of commit or rollback. Remember this, every SQL Stored proc runs on *caller actgrp, so, you need to check if your RPG program runs on *caller too.

Finally, last time I tested, dinos still walked the earth, the commit on SQL Stored Proc scoped the changes made with a RPG program called within, but the RPG STRCMTCTL doesn't get the changes made on SQL Stored Proc called within the RPG.

Have fun!

statements-set-transaction

control-example-using-transaction-logging-file-start-application

definition-example-jobs-commitment-definitions

user_31586330

Thank you @Jairo, I got it working.

Your solution was correct and it worked, only challenge was the SP2 didn't have any SQL statements which was causing issue, I converted the statements in SP2 to SQL statements and it worked completely fine.

user_98680340
Related