We are using DB2 Iseries V7R3 on AS400 system. In one of the stored procedure, we are preparing dynamic SQL queries. Each SQL query is assigned to different variables. When we execute the stored procedure sometimes it fails but when retry with same parameters it works. Upon putting logs in the stored procedure, we have observed that during the failed cases value used for variable 2 is from variable 1.
Attached is the stored procedure and screenshot of the logs.
Appreciate any help on this, running out of the thinking options for this.
However, sometimes it uses select * for variable1 as well. After retry it works ok.
create stored procedure (
)DYNAMIC RESULT SETS 1
LANGUAGE SQL
SPECIFIC SYMDTA.PRC_RETRIEVE_CLAIM_LIST
NOT DETERMINISTIC
MODIFIES SQL DATA
CALLED ON NULL INPUT
COMMIT ON RETURN YES
CONCURRENT ACCESS RESOLUTION USE CURRENTLY COMMITTED
SET OPTION ALWBLK = *ALLREAD ,
ALWCPYDTA = *OPTIMIZE ,
COMMIT = *NONE ,
DECRESULT = (31, 31, 00) ,
DYNDFTCOL = *NO ,
DYNUSRPRF = *USER ,
SRTSEQ = *HEX
BEGIN
DECLARE DATACLAIM CLOB ( 1048576 ) DEFAULT ' ' ;
DECLARE GCLAIMCOUNT CLOB ( 1048576 ) DEFAULT ' ' ;
DECLARE CR_CLAIM_LIST_STMT CURSOR WITH HOLD FOR CLM_DATA_STMT ;
DECLARE CR_CLAIM_COUNT_STMT CURSOR WITH HOLD FOR CLM_COUNT_STMT ;
SET DATACLAIM = 'SELECT * FROM table ';
SET GCLAIMCOUNT = 'select count(*) from table';
INSERT INTO DEBUGGING_DYNAMIC_QUERIES VALUES ( POLICY_NO , DATACLAIM , CURRENT TIMESTAMP , 'DATACLAIM' ) ;
INSERT INTO DEBUGGING_DYNAMIC_QUERIES VALUES ( GCLAIMCOUNT , CURRENT TIMESTAMP , 'GCLAIMCOUNT' ) ;
PREPARE CLM_DATA_STMT FROM DATACLAIM ;
OPEN CR_CLAIM_LIST_STMT ;
PREPARE CLM_COUNT_STMT FROM GCLAIMCOUNT ;
OPEN CR_CLAIM_COUNT_STMT ;
FETCH CR_CLAIM_COUNT_STMT INTO TOTAL_RECORDS_G4 ;
CLOSE CR_CLAIM_COUNT_STMT ;Output the debug table :-
Wrong :- DATACLAIM = "select * " - 2020-01-01 11:00 AM GCLAIMCOUNT = "Select * " - 2020-01-01 11:01 AM
After retry :- DATACLAIM = "select * " - 2020-01-01 12:00 pm GCLAIMCOUNT = "Select count(*) " - 2020-01-01 12:01 Pm