Toad World® Forums

Performance problems with nested cursor loop in stored procedure

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

I have ran into the same issue you are talking about. Below is what I have done to get around this.

  1. Commit after so many records.

I have a ‘work’ table with all records I want to delete which I write a cursor to look through the main table and commit after so many records (I found out that 5k works out well) and just keep looping until no records are left to delete.

  1. Run JCL

The other way to I have gotten around the performance issue is to write JCL and run the SQL on the mainframe. I generally dont like writing JCL so I tend to stay away from this option.

I hope I understand your issue correctly and helped you out. No idea why the JCL will do a massive delete but native SQL sp will not.

Yeah it sucks!