• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3090
  • Last Modified:

DB2 Stored Proc Cursor Question

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
0
fshtank
Asked:
fshtank
2 Solutions
 
momi_sabagCommented:
hi
check out this question
http://www.experts-exchange.com/Database/DB2/Q_22152103.html
i'm pretty much sure you have the same problem
you enter the loop for the first time,
do the delete
the delete does not find any rows to delete so you get the not found sqlstate
you enter the handler
and then exit the loop

what you should do is have a variable that contains an indication if you are currently fetching or currently deletiing, and inside the not found handler, you will need to check
if the indicator says you got the not found after delete, you ignore it
if the indicator says you got the not found after fetch, you set the eof variable and stop the loop

momi
0
 
LowfatspreadCommented:
hummmm thought that sounded familiar...
;-)

you may also want to change your cursor to avoid attempting deletes when no required

e.g.
DECLARE cursor1 CURSOR FOR
                select   a.TRTRNO as AuditTranNumber  
                   , a.TRRXNO as ClaimNumber
                   , a.TRRXSQ as ClaimSeq
              from PSIAUDIT.AUTRANL1 a  
              WHERE TRCRTD between  FROM_DATE AND  TO_DATE
                and ( exists (select tstrno from psiaudit.autrsc where tstrno=a.trtrno)
                        or exists (select tstrno from psiaudit.autrschst where tstrno=a.trtno)                  
                        )
                order by a.trtno

DECLARE cursor1 CURSOR FOR
                select   a.TRTRNO as AuditTranNumber  
                   , a.TRRXNO as ClaimNumber
                   , a.TRRXSQ as ClaimSeq
                   ,b.tstrno as autrsc
                   ,c.tstrno as autrschst
              from PSIAUDIT.AUTRANL1 a  
               left outer join psiaudit.autrsc as b
                  on a.trtno = b.tstrno
               left outer join psiaudit.autrschst as b
                  on a.trtno = c.tstrno

              WHERE a.TRCRTD between  FROM_DATE AND  TO_DATE
                 and (b.tstrno is not null or c.tstrno is not null)
              Order by a.trtno

and use the addition columns as controls for the deletes..

hth
0
 
fshtankAuthor Commented:
Hey Everyone,

Thanks for your replies.

I did come to the conclusion to manage the variable correctly.  The HST table did not allow deletes and returned  02000 SQL Condition.

The Tables to delete will always have something to delete if they are in the Cursor table.

Thanks for the responses.

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now