Adding SQL commit calls

Existing application programs that perform many updates may cause DB2® locking issues.

The only way to reduce the number of concurrently locked pages in DB2 is to issue SQL COMMIT calls. If you encounter locking issues, you may have to consider changing your application programs.

The least disruptive way to do this is to code a new program that simply issues a COMMIT. Code this program to uses either the DB2 remote recovery services attachment (RRSAF) or the attach facility (CAF). Instructions for both methods are in the appropriate DB2 manual. Add code to the long-running update programs to call the new module.

Bind the DBRM from the commit program into the same collection as the CICS® VT DDM driver.

Be aware of the following considerations:

• Your new program does not need to include code to connect to DB2. Providing that you call it after a number of VSAM calls to a migrated data set have occurred, a DB2 thread will already exist.
• You must carefully consider how to establish the optimum points in your program to call the commit module.
• You must consider very carefully the implications of restarting your application program if an abend occurs after one or more commit calls have been processed.

Using this technique is only appropriate if your long running batch programs only update VSAM files. It is not appropriate if other resources such as DB2 or MQSeries are involved.