Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2009
    Posts
    33

    Unanswered: stored procedures execution error

    Hello ALl,
    Db2 v9.7 fp4 on windows 64 bit. A stored proc while executing is resulting in an error which is given below. The first time i executed it was successfull. While testing for higher workloads the second time when executed it is giving an error. it worked absolutely fine the first time after which i hve not changed anything on the database. pls assist me.
    >db2 "call cardpro.sync_card()"
    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.
    SQLSTATE=24501

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    as indicated in message the cursor is being used without open statement
    always add exception handler : it might be possible the open failed and the sp continues..
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    Jan 2009
    Posts
    33

    stored proc execution error

    Hello,
    I dropped the database and restored the db with image before the procedure and functions were created. then created them again, it ran the first time but again second time onwards it is not running. All the functions and procs have open cursor stmts. kindly assist.

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    You should atleast post the procedure code ..


    ==
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  5. #5
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    you have to think it over before taking such a decision - you could also format the disks
    why not publishing the code for this procedure ?
    try to debug or add some debug statements..
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  6. #6
    Join Date
    Jan 2009
    Posts
    33

    stored procedures execution error

    Hello ,
    I am attaching the procedure code. It is a procedure converted from oracle to db2. A few lines commented are actually from source oracle. Kindly ignore them. It is compiled successfully and executed first time successfully. The second time onwards i am facing the error as given in my previous conversations. Please find the attached code. Kindly assist me.

    Error msg
    db2 "call cardpro.sync_card()"
    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.
    SQLSTATE=24501
    Attached Files Attached Files

  7. #7
    Join Date
    Nov 2011
    Posts
    334
    I think maybe the problem is on the statment "rollback" which is at line 167 of your attaching file.
    After rollback, all cursor will be closed even them are deaclared with "with hold"。On your first execution ,every thing is ok,and in the following re-execution, "rollback" is issued for some reason。So the cursor is closed,and fetch operation get SQL0501N error msg。

    BTW: I think the var "var_success" is unnecessary for you
    Code:
      var_count := CARDPRO.SYNC_INDACC_TBL(temp_CB_INDIVIDUAL_ACCTNO);
      if var_count = 1 THEN
          var_success := true;
      else
          var_success := false;
      end if;
      if var_success THEN
          var_count := CARDPRO.SYNC_CRDLNK_TBL(temp_CB_CARDHOLDER_NO);
          if var_count = 1 THEN
              var_success := true;
          else
              var_success := false;
          end if;
      end if;
      if var_success THEN
          var_count := CARDPRO.SYNC_MCDISR_TBL(temp_CB_CARDHOLDER_NO);
          if var_count = 1 THEN
              var_success := true;
          else
              var_success := false;
          end if;
      end if;
      if var_success THEN
          if(mod(var_RecordCount, 50)=0) then
              COMMIT;
          end if;
      else
          ROLLBACK;
      end if;
      END LOOP;
    could be change to this:
    Code:
    var_count := CARDPRO.SYNC_INDACC_TBL(temp_CB_INDIVIDUAL_ACCTNO);
    IF var_count = 1 THEN
       var_count := CARDPRO.SYNC_CRDLNK_TBL(temp_CB_CARDHOLDER_NO);
       IF var_count = 1 THEN
          var_count := CARDPRO.SYNC_MCDISR_TBL(temp_CB_CARDHOLDER_NO);
       END IF;
    END IF;
    
    IF var_count = 1 THEN
       if(mod(var_RecordCount, 50)=0) then
          COMMIT;
       end if;
    else
       ROLLBACK;
       -- you must leave loop here ,or reopen your curosr.
    END IF
    END LOOP

  8. #8
    Join Date
    Jan 2009
    Posts
    33

    stored procedures execution error

    hello ,
    as instructed if i include an open cursor after the rollback will the procedure go on an infinite loop or should i include it after the loop. I dont have expertise in plsql . sorry if teh questions are pretty silly ones.
    Last edited by sharaths_81; 04-04-12 at 05:29. Reason: giving some clarity

  9. #9
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Haven't looked at the procedure in detail and of course, have no knowledge of the business context of the procedure.
    One thing you may consider is using SAVEPOINT and ROLLBACK TO SAVEPOINT and update the procedure

    Check the documentation on these statements. These are basic DBMS concepts and not PLSQL specific. I hope you can do this ;-)

    If you are using DataStudio, try Debug option .. that will give you a better idea of where the problem occurs ...
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  10. #10
    Join Date
    Jan 2009
    Posts
    33
    hello sathyaram and db forum members, i included the savepoint as suggested by you and the procedure is running absolutely fine. Thanks a lot guys, cheers, this gives me motivation to look into this forum and provide solutions for any issues I am aware of.

    Thanks a lot team.. keep it going.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •