Database Connectors and Record Locking

Your DB2 database may be set up for wait-level locks. The DB2 universal database products have the ability to time-out a lock and send an error return code to the waiting application. See your database administrator for details. (This SQL code would be placed in ERROR_MAP_FILE.)

Database Connectors provides semi-automated ways to handle transaction logging based on the setting of the 4GL_COMMIT_COUNT environment variable. You can also directly alter your source code to deal with this issue. Individual users determine how much work they wish to do to conform to Oracle's transaction management system by choosing the method that best fits their needs and resources. The following methods are listed in order of increasing amount of work:

4GL_COMMIT_COUNT = 0 (Default)

When you set this variable to zero, the run time system tracks the number of logical locks that are currently in effect. When the number of logical locks reaches zero, the run time system assumes that a transaction is complete and issues a COMMIT statement.

4GL_COMMIT_COUNT = n

When you set this variable to a nonzero value, the run time system tracks the number of WRITE, REWRITE, and DELETE operations, until the value of 4GL_COMMIT_COUNT is reached, at which time the run time system issues a COMMIT statement. The READ, START, and READ NEXT operations do not count toward this total, because the run time system is tracking data-altering operations rather than logical record locks. The disadvantage of this method is that when a COMMIT is issued, any record locks held by the run time system are released.

4GL_COMMIT_COUNT = -1

No commit is issued by Database Connectors. When 4GL_COMMIT_COUNT is set to -1, alternate ways to perform a commit or rollback include:

  • Call your database query tool with COMMIT WORK or ROLLBACK WORK.

A COMMIT WORK is, however, issued on exit from the run time system (for example, on execution of a STOP RUN).