Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2015
    Posts
    31

    Unanswered: Stored Procedure Slow with cursor

    Hello,
    when I was working in sybase, to speed up the execution of the Stored Procedure with cursor, we were doing the commit each 500 insert (in ex )
    In DB2 , can we do something similar ?
    I tried to open a transaction but i'm not able to, it give me a sintax error.



    CREATE OR REPLACE PROCEDURE BDV.SP_SAT_PERSON_GC (out v_errState CHAR(5 ) , out v_errMsg VARCHAR(4096))
    DYNAMIC RESULT SETS 1
    LANGUAGE SQL
    P1: BEGIN

    ...

    END P1

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Yes you can so something similar. In DB2, there is no explicit START to a transaction. They just are everything between the last commit/rollback to the next commit. So just issue a commit after every 500th insert.

    Andy

  3. #3
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    DB2 lets you define a cursor as "WITH HOLD" which has the effect of keeping the cursor open after a COMMIT.
    This is not a performance improvement technique.
    Instead it is a way to reduce lock contention, increase concurrency, have smaller units of work etc.

    The best way to speed up execution is to ensure optimal access plans along with using the best tool for the job.

    Study the DB2 documentation (for your specified DB2 version and operating-system platform) along with the EXAMPLEs that IBM provides you in the SAMPLES tree on your DB2 server.

  4. #4
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    surprisingly no one has mentioned the most probable item here, is that you issuing a commit is not going to speed up your procedure at all. If you'd like help with speeding it up, share the SQL and table info, explain, etc... and we may be able to help you with the performance. By the way if you are going to commit every 500th record how many records are being processed by the procedure?
    Dave

Posting Permissions

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