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
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.
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.
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?