Db2 Sql Error Sqlcode=-501 Sqlstate=24501
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us
Db2 Sql Error Sqlcode 803 Sqlstate 23505
Learn more about Stack Overflow the company Business Learn more about hiring developers or db2 sql error sqlcode 104 sqlstate 42601 posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow db2 sql error sqlcode 206 sqlstate 42703 Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up SQLCODE=-501 SQLSTATE=24501 with Cursor up vote 0 down vote http://www.ibm.com/support/knowledgecenter/SSEPEK_10.0.0/codes/src/tpc/n501.html favorite I have written a stored procedure as CREATE OR REPLACE PROC1() DECLARE VAR1 INT; DECLARE VAR2 INT; DECLARE TEXT VARCHAR(1000); DECLARE exitcode INTEGER DEFAULT 0; DECLARE SQLCODE INTEGER DEFAULT 0; DECLARE CUR1 CURSOR WITH HOLD for STMT1 ; DECLARE CONTINUE HANDLER FOR NOT FOUND SET exitcode = 1; SET TEXT= (-----); PREPARE STMT1 FROM TEXT; open cur1; fetch from cur1 into var1,var2; while(sqlcode =0) do -- http://stackoverflow.com/questions/27633462/sqlcode-501-sqlstate-24501-with-cursor -- CALL SYSPROC.ADMIN_CMD('REORG TABLE emp1 '); set exit code = 0; fetch from cur1 into var1,var2; IF (exitcode = 1) THEN LEAVE wloop; END IF; end while; close cur1; My question is even I declared my cursor as WITH HOLD option, after the first fetch the cursor is closing & throwing -501 error.If i remove the REORG statement from the loop. Then cursor is working normally,fetching all the rows. Can some one tel me the way to keep my cursor to be open even If i use the REORG statement inside the loop. Thanks in advance db2 share|improve this question asked Dec 24 '14 at 7:51 HABBIE 15 Uh, your cursor doesn't do anything.... If it touches emp1, then it's probably closing the cursor because you changed the table it was looking at. You shouldn't be re-orging in a loop in the first place (it has the potential to be a really long-running operation). Also - anything above 0 means "success with a warning" (like, say, no rows affected, statement plan re-evaluated, etc); whether or not you want to continue depends on if you care about the specific warning... –Clockwork-Muse Dec 25 '14 at 8:53 As pe
this is your first visit, be sure to check out the FAQ by clicking the link above. You may http://www.dbforums.com/showthread.php?1694942-Cursor-exception-(SQLSTATE-24501)-during-nested-error-handling have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. Results 1 to 3 of 3 Thread: Cursor exception (SQLSTATE=24501) during nested error handling Tweet Thread Tools Show Printable Version Subscribe to db2 sql this Thread… Search Thread Advanced Search Display Linear Mode Switch to Hybrid Mode Switch to Threaded Mode 07-17-13,15:03 #1 harjeet_21 View Profile View Forum Posts Registered User Join Date Oct 2010 Posts 4 Unanswered: Cursor exception (SQLSTATE=24501) during nested error handling I am getting error when running following procedure. Logs indicate that db2 sql error cursor cur_loop_test (cursor is declared using WITH HOLD option) is thorwing error on 2 record's fetch. Error message is: SQLCODE=-501/SQLSTATE=24501/MSG=SQL0501N The cursor specified in a FETCH statement or CLOSE statement is not open or a cursor variable in a cursor scalar function reference is not open. Any help is appreciated. Thanks in advance !!! ----- CREATE PROCEDURE SP_LOOP_TEST2() P1: BEGIN DECLARE vTxnKey INT; DECLARE tmp_msg VARCHAR(1000) DEFAULT ''; DECLARE eop SMALLINT DEFAULT 0; DECLARE vErrorMsg VARCHAR(32672); DECLARE vSession VARCHAR(50); DECLARE SQLCODE INTEGER DEFAULT 0; DECLARE SQLSTATE CHAR(5) DEFAULT '00000'; DECLARE vErrorCount INTEGER DEFAULT 0; -- DECLARE cur_loop_test CURSOR WITH HOLD FOR SELECT TXN_KEY FROM TXNS WHERE TXN_KEY IN (100,101,102,103) FOR FETCH ONLY; -- DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING BEGIN -- GET DIAGNOSTICS EXCEPTION 1 vErrorMsg = MESSAGE_TEXT; -- SET vErrorMsg = 'SQLCODE=' || NVL(TRIM(CHAR(SQLCODE)), '?') || '/SQLSTATE=' || NVL(SQLSTATE, '?') || '/MSG=' || NVL(vErrorMsg, '?'); -- ROLLBACK; -- INSERT INTO ATT_LOGS (CREATED_TIMESTAMP, CREATED_BY, SES