DB2's Virtual Time - Page 2

Virtual Database Time

Each database partition holds a virtual time clock, which it advances after each successful commit. The virtual database time is calculated internally by the DB2 engine at commit time and is used as the main "magic" number in the database.

To better understand the virtual time concept, I made one graphical representation for the DB2 EEE transaction.

The DB2 database has 3 partitions. A time server does not exist on the network, nor does time synchronization take place. As a result, every partition has a different virtual time. The difference between partitions is very small, less then 2 minutes. One  active transaction is in progress and is actually modifying data in partitions 2 and 3. This transaction did not do any work in partition 1. The commit took place at 11:08:34 in partition 1 and at 11:07:44 in the partition 2. The virtual time, at the commit time, on partition 2 was more advanced than the time on partition 3 was, and will be used as reference. The new virtual time 11:08:34 was written first into the database log files at partition 2 and this time was propagated to the partition 3 log files.  After the log records are written, the actual commit takes place.   To support the database point in time recovery, both the virtual time and the CUT time are written to the database log files. Partition 1  was not involved in the transaction, and the virtual timestamp remains unchanged. Using this delicate virtual timestamp mechanism, DB2 succeeds in keeping the distributed transactions in order.

The Actual database virtual time can be viewed in several ways. One example is analyzing the database backup image file header.

C:\db2\bin\SAMPLE2.0\DB2\NODE0000\CATN0000\20040508>db2ckbkp -H 220242.001 | grep Time
         Time of last describe DB = 0
 Last time DB marked inconsistent = 0
              Last Reset Log Time = 0
            Last Commit Timestamp = 1022652131
     On-line Backup End Timestamp = 0
           Recoverable Start Time = 0
   Rollforward stoptime in effect = 0
             Backup End Timestamp = 0
                Virtual Timestamp = 102246108
              Max Time Difference = 0
         Time of last describe DB = 0
 Last time DB marked inconsistent = 0
              Last Reset Log Time = 0

DB2 UDB Inter-Partition Time Synchronization

In the distributed database environment, a special database configuration parameter, max_time_diff, is used for protecting transactions against time differences between database partitions. DB2 database uses CUT as a transaction timestamp, so that the different time zones have no impact during commit time.

>db2 get dbm cfg | grep DIFF
Max time difference between nodes (min) (MAX_TIME_DIFF) = 60

MAX_TIME_DIFF defines the maximum allowed time difference between database nodes for distributed database operation to succeed.

The following example demonstrates different situations from real practice:

a.)   a single partition database and the time drift

A single node DB2 database is running on a dedicated SUN Solaris machine. The machine has a problem with the BIOS on the motherboard and the system time suddenly jumped ahead 12 days. The DB2 database continued to work without any difficulties. New transactions were recorded with the new, drifted timestamp in the database log files.

The Max Time Difference has been advanced and recorded in the database log files. Database log timestamp was set ahead to 12/10/2004, and the log entries accepted the new, higher timestamp. After some database activity, the system clock was turned back to 1/10/2004. Nevertheless, the existing database log files still have an old timestamp while the new log files are taking the actual timestamp.

DB2:ARTIST >ls -lrt
-rwxrwxrwa   1 artist               12288 Oct 10  2004 S0000001.LOG
-rwxrwxrwa   1 artist               12288 Oct 10  2004 S0000000.LOG

->  date changed back to 01.10.2004,and the new log file has been generated 

DB2:ARTIST> db2 archive log for database artist
DB20000I  The ARCHIVE LOG command completed successfully.

DB2:ARTIST>ls -lrt
-rwxrwxrwa   1 artist              12288 Oct  1  2004 S0000002.LOG
-rwxrwxrwa   1 artist              12288 Oct 10  2004 S0000001.LOG
-rwxrwxrwa   1 artist              12288 Oct 10  2004 S0000000.LOG

In the 12 days, the system time will be equalized with the database log timestamp and the database would be in synchrony with the operating system. Should a recovery operation be needed before, a time-based recovery would be hard to manage.

b.)   a multi-partition database and the time drift

For a multi-partitioned database two possible scenarios come in play:

• the time difference between partitions occurred before a new connect request
• the time difference between partitions occurred during transaction activity

Two database partitions with a time difference of 31 minutes between them represent a DB2 EEE database. The DB2 database has been activated and waiting to accept connect request. A database application issued a connect request to partition 2, causing an initial time check between partitions. The time drift has been discovered and compared against DBM configuration parameter max_time_diff. The resulting time drift was larger than the defined database manager value and the client connection request was refused.

An application was already connected to the database partition 2 and due to a mistake, the system time on partition 2 was changed one hour ahead. After some activity, the database application has to commit changes. The application commit request has initiated the time check between database partitions. The measured time drift was 60 minutes, which was double the maximum allowed with max_time_diff DBM parameter. The database transaction is rolled back with the SQLCODE error message.

85B5  -1472  System clock difference exceeds max_time_diff on connect (log synchronization)

Conclusion

IBM has enhanced the DB2 database with many features that we cannot find in any other databases. It brings a new level of complexity, especially into multi-partitioned environments. It seems so easy, having only one database parameter in play for correct time, but a much deeper complexity lies in the background. Many more questions about internal database life remain to be answered.

» See All Articles by Columnist Marin Komadina