The number of rows in our DB2 tables never seems to cease. The availability of the data increases. Many installations runs 24 hours online availablity all seven days of the week. The number of concurrent users has never been higher. All of the above makes it increasingly harder to make mass changes of the contents of our DB2 tables. Nowadays most developers know how to make programs that commit on a regular basis in order to free the locks that are held by the updates.

In order to commit regularly the most common way is to maintain a counter in the updating program. When the counter reaches a certain value a COMMIT is executed. This approach is easy to code and understand. The disadvantage is that sometimes it takes a short while for the counter to reach the commit value and on other occasions it takes a long period of time. Both situations can easily occur during the same execution of the program. In order to make the time between commits approximately the same each time some installations are using commit on time. In this tip I will show you an easy way to implement commit on time.

The first step is to determine the current time. This can be done using the DB2 current timestamp register. The problem is that if you use a current timestamp returned by each row in the main cursor in your program, it will have the same value for all rows. Instead you can use the expression TIMESTAMP(GENERATE_UNIQUE()), which also returns a timestamp, but with a new value for each row you are fetching.

The second step is to determine when the next commit is to occur. At program initialisation and after each commit you can use a SET :nextCommitAt = current timestamp + :commitSeconds SECONDS. The hostvariable commitSeconds must contain the number of seconds to pass before the next commit is to occur. I will recommend you to declare this variable as a PIC S9(4) BINARY in COBOL and FIXED BIN(15) in PL/I. If you use other programming languages the declaration must correspond to a SMALLINT in DB2.

Each time you have processed a row you can compare the hostvariable containing the timestamp created by GENERATE_UNIQUE with the vaule of nextCommitAt and when NextCommitAt contains the smallest value it is time to execute a COMMIT. Now the interesting issue is what value to use for the commitSeconds hostvariable. In DB2 version 8 and newer DB2 checks for deadlocks each fifth second or sooner. The default is each fifth second. If you want to avoid deadlocks it is a very good idea to set commitSeconds to five or less. Please consult a DBA or a DB2 systems programmer to find out how often DB2 checks for deadlocks at your installation. You can set commitSeconds in correspondance with this value. You can also experiment with higher values than five, but the risk of creating problems for your program and for other concurrent users of DB2 increases by doing it. And please take into account the speed of the newest IBM mainframes. They are extremely fast and are able to process a lot in five seconds.