Solved

DB2 Stored Proc Cursor Question

Posted on 2007-03-28
3
3,034 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
0
Comment
Question by:fshtank
3 Comments
 
LVL 37

Accepted Solution

by:
momi_sabag earned 450 total points
ID: 18814532
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
 
LVL 50

Assisted Solution

by:Lowfatspread
Lowfatspread earned 50 total points
ID: 18815803
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
 

Author Comment

by:fshtank
ID: 18827057
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

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
This video discusses moving either the default database or any database to a new volume.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now