DB2 Commit Hold

I discovered something today in SQL Stored procedures for DB2 that I thought you all might like to know.

If you have a SQL stored procedure (A) that calls another SQL stored procedure (B), if you are looping through a cursor in procedure A and you have a commit in procedure B, when the commit in procedure B is executed, it will close the cursor that you are looping through in procedure A.

To resolve this issue, you need to change the COMMIT statement in procedure B to COMMIT HOLD. This keeps the cursor in procedure A open when the COMMIT is executed. Make sure you remember to close your cursor in procedure A when you are done with it.

Don’t you all feel smarter now?