Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Cursor Not Closing Properly in DB2

Posted on 2009-04-15
3
Medium Priority
?
591 Views
Last Modified: 2012-05-06
Hi,
   My Procedure (given below) which is used to Insert/Update/ Delete records from one database to another using Federated Server. The cursor used in it is not exiting out properly. It runs till the last record correctly and for the Last record in the cursor it executes infinite times and I have to stop the execution of procedure manually.

Can anyone tell me what is wrong in the code below. Can you try it once and suggest me what is wrong.
Since I have created it, I am not able to identify the reason and thought a third eye will help me in that.
Thanks in advance.

Regards,
rrjegan17
DROP PROCEDURE test.proc@
 
CREATE PROCEDURE test.proc (
   IN P_jobexecid INTEGER
)
LANGUAGE SQL
BEGIN
    DECLARE EOF INT DEFAULT 0;
    DECLARE P_col1 INTEGER;
    DECLARE P_col2 VARCHAR(40);
    DECLARE P_col3 VARCHAR(60);
    DECLARE P_col4 VARCHAR(20000);
    DECLARE SQLCODE INTEGER DEFAULT 0;
 
    DECLARE query_cur CURSOR FOR
    SELECT col1,col2,col3,col4
    FROM mytable;
 
    DECLARE CONTINUE HANDLER FOR NOT FOUND
    SET EOF = 1;
 
        OPEN query_cur;
        FETCH query_cur INTO P_col1, P_col2, P_col3, P_col4;
        WHILE (SQLCODE <> 100)
        DO
              EXECUTE IMMEDIATE P_col4;
 
              IF SQLCODE < 0
              THEN
                   ROLLBACK WORK;
-- Have to rollback if any of the Execute Immediate command Fails and exit the cursor immediately.
              END IF;
 
              FETCH query_cur INTO P_col1, P_col2, P_col3, P_col4;
        END WHILE;
        CLOSE query_cur;
 
    COMMIT WORK;
 
END@

Open in new window

0
Comment
Question by:Raja Jegan R
  • 2
3 Comments
 
LVL 37

Accepted Solution

by:
momi_sabag earned 2000 total points
ID: 24155102
i guess your problem is the not found handler
in your handler you set the eof flag to 1
in your loop you check for sqlcode <> 100
when your cursor fetches after the last record, you get a sqlcode 100 and the handler code is execute,
the set eof=1 statement is done successfully so sqlcode is set to 0 and your loop just keep on going
try this

DROP PROCEDURE test.proc@
 
CREATE PROCEDURE test.proc (
   IN P_jobexecid INTEGER
)
LANGUAGE SQL
BEGIN
    DECLARE P_col1 INTEGER;
    DECLARE P_col2 VARCHAR(40);
    DECLARE P_col3 VARCHAR(60);
    DECLARE P_col4 VARCHAR(20000);
 
    DECLARE query_cur CURSOR FOR
    SELECT col1,col2,col3,col4
    FROM mytable;
 
        OPEN query_cur;
        FETCH query_cur INTO P_col1, P_col2, P_col3, P_col4;
        WHILE (SQLCODE <> 100)
        DO
              EXECUTE IMMEDIATE P_col4;
 
              IF SQLCODE < 0
              THEN
                   ROLLBACK WORK;
-- Have to rollback if any of the Execute Immediate command Fails and exit the cursor immediately.
              END IF;
 
              FETCH query_cur INTO P_col1, P_col2, P_col3, P_col4;
        END WHILE;
        CLOSE query_cur;
 
    COMMIT WORK;
 
END@
0
 
LVL 57

Author Comment

by:Raja Jegan R
ID: 24155183
Have added that Continue Handler during testing of the query and forgot to remove it.
Thanks for pointing it out.
Will test it and revert back.
0
 
LVL 57

Author Closing Comment

by:Raja Jegan R
ID: 31570825
As I mentioned earlier, I included that one for testing and forgot to remove it.
And definitely your third eye on it helped me out.
Thanks momi_sabag
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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 (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…
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…
Suggested Courses

564 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