Solved

DB2 Stored Proc Cursor Question

Posted on 2007-03-28
3
3,038 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
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 Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

911 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

16 Experts available now in Live!

Get 1:1 Help Now