I have the following simple stored procedue to clean up records in a table impleneted in ibm db2.
The problem is the performance of nested cursor loop.
The sp runs endless and i don’t no why. Without nested cursor loop the procedure is completed after a few seconds. Can anybody help me?
CREATE PROCEDURE ZRPGESDAT.CLEANUPBMZZUSTELLUNG ( )
RESULT SETS 1
LANGUAGE SQL
ALLOW DEBUG MODE
SPECIFIC ZRPGESDAT.CLEANUPBMZZUSTELLUNG
BEGIN
DECLARE MANDANTID DECIMAL ( 15 , 0 ) ;
DECLARE KOERPERSCHAFTID DECIMAL ( 15 , 0 ) ;
DECLARE ZUSTELLUNGID DECIMAL ( 15 , 0 ) ;
DECLARE RECCOUNT INT ;
DECLARE EOF SMALLINT DEFAULT 0 ;
DECLARE EXPIRY INT DEFAULT 180 ;
DECLARE C1 CURSOR WITH RETURN FOR
SELECT CDEZA , CDE10A , GEPVA FROM "ZRPGESDAT" . "GEPWT" WHERE GE2SCA = 0 AND GE10A = '01.01.0001' AND CD1W3A <> 'No';
DECLARE C2 CURSOR WITH RETURN FOR
SELECT CD231A , CDDCA FROM "ZRPGESDAT" . "GEPWTLT" WHERE CDEZA = MANDANTID AND CDE10A = KOERPERSCHAFTID AND GEPVA = ZUSTELLUNGID ORDER BY CDE45A ;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET EOF = 1 ;
DECLARE GLOBAL TEMPORARY TABLE SESSION . "Zustellungen_ToDelete" LIKE ZRPGESDAT . GEPWTLT WITH REPLACE ON COMMIT PRESERVE ROWS NOT LOGGED ;
OPEN C1 ;
FETCH FROM C1 INTO MANDANTID , KOERPERSCHAFTID , ZUSTELLUNGID ;
WHILE EOF = 0 DO
SUB : BEGIN
DECLARE LOGTYPE VARCHAR ( 1 ) ;
DECLARE DELZUSTELLUNG VARCHAR ( 1 ) ;
DECLARE ADDDATE DATE ;
DECLARE DIFFDATE INT DEFAULT 0 ;
DECLARE EOF1 SMALLINT DEFAULT 0 ;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET EOF1 = 1 ;
OPEN C2 ;
FETCH FROM C2 INTO LOGTYPE , ADDDATE ;
WHILE EOF1 = 0 DO
FETCH FROM C2 INTO LOGTYPE , ADDDATE ;
SET DIFFDATE = ( DAYS ( CURRENT DATE ) - DAYS ( DATE ( ADDDATE ) ) ) ;
IF ( LOGTYPE = 'I' AND DIFFDATE > EXPIRY ) THEN
SET DELZUSTELLUNG = 'Y' ;
ELSEIF ( LOGTYPE = 'D' ) THEN
SET DELZUSTELLUNG = 'N' ;
END IF ;
FETCH FROM C2 INTO LOGTYPE , ADDDATE ;
END WHILE ;
IF ( DELZUSTELLUNG = 'Y' ) THEN
INSERT INTO SESSION . "Zustellungen_ToDelete" VALUES (MANDANTID , KOERPERSCHAFTID , ZUSTELLUNGID , 1 , 'D' , 'SQL' , CURRENT DATE , CURRENT TIME ) ;
END IF ;
CLOSE C2 ;
END ;
FETCH FROM C1 INTO MANDANTID , KOERPERSCHAFTID , ZUSTELLUNGID ;
END WHILE ;
SET RECCOUNT = ( SELECT COUNT ( * ) FROM SESSION . "Zustellungen_ToDelete" ) ;
CLOSE C1 ;
IF ( RECCOUNT > 0 ) THEN
DELETE FROM GEPWT AS T1 WHERE EXISTS ( SELECT 1 FROM SESSION . "Zustellungen_ToDelete" AS T2 WHERE T1 . CDEZA = T2 . CDEZA AND T1 . CDE10A = T2 . CDE10A AND T1 . GEPVA = T2 . GEPVA ) WITH NONE ;
INSERT INTO "ZRPGESDAT" . "GEPWTLT#" ( SELECT * FROM SESSION . "Zustellungen_ToDelete" ) WITH NONE ;
END IF ;
END