Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2010
    Posts
    14

    Unanswered: commit statement in procedures

    Hi All,

    how can i use commit statement to commit every single record in a stored procedure when updating or inserting several records.

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Yes, you can. BUT committing inside the stored procedure influences/commits the transaction initiated by the DB2 client. You may break the ACID properties that the client may expect. So if you can do without a commit, that's usually preferred. (There are always exceptions, of course.)
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Aug 2010
    Posts
    14
    hi Stolze,
    Thank you for quick reply.
    Some times need to update multiple records at a time, at that time if we did not give commit for every few records, i think transition log will fill will cause unable to update complete records.

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    The requirement you stated is quite common and perfectly understandable. The solution is to open a cursor, fetch the rows one at a time, and then delete the row if it meets your criteria, and commit every delete (or commit every n deletes if you prefer). In order to not have the cursor closed when you do a commit, define the cursor using WITH HOLD option. There are many other threads in this forum where this is discussed in detail and where you might find some sample code.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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