We help IT Professionals succeed at work.
Get Started

DB2 Stored Proc Cursor Question

fshtank
fshtank asked
on
3,131 Views
Last Modified: 2008-02-01
Hello Everyone,

I have an  AS/400  DB2 Stored Procedure / CURSOR question.

1) I want to Open a cursor to get a list of "IDs",
2) cursor loop thru those ID's,
3) DELETE the matching records of those IDs from two different tables.

- The cursor query returns data - how do I get the processing LOOP to execute?

The PROC Compiles and I am able call it from iSQL with normal return code, but nothing happens.

Thanks

[Code below culled from IBM Redbooks]

PROCEDURE: - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

CREATE PROCEDURE PSIAUDIT.DELTRANBYDATE
                                    ( FROM_DATE      DECIMAL(8,0)
                                      ,TO_DATE          DECIMAL(8,0)
                                     )

      LANGUAGE SQL
      SPECIFIC DELTRANBYDATE

P1: BEGIN
         DECLARE TRAN_NO   DECIMAL (11,0);
         DECLARE CLAIM_NO  DECIMAL (15,0);
         DECLARE CLAIM_SEQ DECIMAL (3,0);

      DECLARE at_end INT DEFAULT 0;
      DECLARE not_found CONDITION FOR '02000';  

            
    DECLARE cursor1 CURSOR FOR
                select   a.TRTRNO as AuditTranNumber  
                   , a.TRRXNO as ClaimNumber
                   , a.TRRXSQ as ClaimSeq
              from PSIAUDIT.AUTRANL1 a  
              WHERE TRCRTD >= FROM_DATE AND TRCRTD <= TO_DATE;


      DECLARE CONTINUE HANDLER FOR not_found
                  SET at_end = 1;

            OPEN cursor1;
                       
             FETCH cursor1 INTO  TRAN_NO, CLAIM_NO, CLAIM_SEQ;
   
        WHILE at_end = 0 DO  

                   DELETE FROM PSIAUDIT.AUTRSC     WHERE TSTRNO=TRAN_NO;
              DELETE FROM PSIAUDIT.AUTRSCHST  WHERE TSTRNO=TRAN_NO;                    
   
                FETCH cursor1 INTO  TRAN_NO, CLAIM_NO, CLAIM_SEQ;
   
            END WHILE;
                       
               CLOSE cursor1;

END P1
Comment
Watch Question
This problem has been solved!
Unlock 2 Answers and 3 Comments.
See Answers
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE