DB2's Virtual Time

Monday May 31st 2004 by Marin Komadina

Marin Komadina investigates the DB2 database's internal behavior, examining system and database time interaction, the time drift influence on the backup and recovery and more, to help you understand how time influences the regular database life circle.

The time problem occupied my attention, during a recent recovery situation. On the SUN Solaris machine, the system time jumped forward unexpectedly. This situation raised questions about system and database time interaction, the time drift influence on the backup and recovery and many others. For example, what happens in complex multi-partition DB2 database environments with millions of transactions when the time goes wild? This article investigates the DB2 database's internal behavior and uncovers some of those scenarios, helping you to better understanding time influence in the regular database life circle.

This article covers:

  • The System and Database Time
  • The Log Sequence Number (LSN)
  • Virtual Database Time
  • DB2 UDB Inter-Partition Time Synchronization
  • Conclusion

The System and Database Time

According IBM documentation, the database utilities distinguish Coordinated Universal Time and local system time. Nevertheless, many authors use additional phrases, such as Greenwich Mean Time or the ISO time.

CUT(Coordinated Universal Time) GMT(Greenwich Mean Time) ISO time

7-part character string that identifies a combined date and time data

yyyy-mm-dd-hh.mm.ss.nnnnnn (year,month,day,hour,minutes,seconds,microseconds)

- used during roll forward operations to point in time

- min recovery time in the LIST TABLESPACES report

- QUIESCE history records time

Local time

Time associated with

- backup images ( database timestamp converted always to server local time zone)

- drop table history entries

- used from GUI (Control Center)

Table 1: The time terms explanation

Even though many DB2 authors refer to the CUT as GMT or the ISO time, this time is slightly different from the time obtained to the time we are getting from official time servers (Universal Time Center - UTC). The difference exists in the leap seconds. Those differences are ignored in calculations and CUT time is considered as equal to the UTC time.

For example, the timestamp used for roll forward operations is specified as Coordinated Universal Time (CUT), which is the result of subtracting the Current Time zone from the Local Time.

db2 => values (current date,current time ,current timezone)

---------- --------  --------
05/09/2004 15:21:52   20000,    -> timezone +2

db2 => values (current date,current time - current timezone)

CUT_date   CUT_time
---------- --------
05/09/2004 13:22:03
Listing 1: CUT time calculation

The CURRENT TIMEZONE special register specifies the difference between CUT and local time, presented as a decimal number in which the first two digits represent the number of hours, the next two digits represent the number of minutes and the last two digits represent the number of seconds. In our case, the time zone difference was 2 hours. A second method of calculating the CUT time is by using UNIX operating system commands:

Mon May 10 09:58:45 MEST DST 2004

db2inst1:/export/home/db2inst1$set | grep TZ

db2inst1:/export/home/db2inst1$date -u
Mon May 10 07:58:48 GMT 2004
Listing 2: UNIX system time

The displayed UNIX machine has the Middle European Summer Time (MEST) time zone. The CUT time calculation:

CUT Time= Local Time + Time Zone Diff + Daylight Savings Difference
              = 09:58 MEST+ 1 (MET) + 1 (EDT)
              = 07:58

The number (+)1 specifies the time zone west of GMT in hours to the CUT time. The third part (EDT) is specified if daylight savings time is used. Or simply, using the UNIX command date -u we are obtaining CUT time.

Every DB2 DBA has already seen CUT time, but maybe it was not so obvious.

db2inst1:/export/home/db2inst1$db2 list application global
Auth Id  Application    Appl.      Application Id                 DB       # of
         Name           Handle                                    Name    Agents
-------- -------------- ---------- ------------------------------ -------- -----
DB2INST1 db2bp          48         *LOCAL.db2inst1.040510075945   ARTIST   1    
Listing 3: CUT time included in the application qualifier

An application in the application list uses connect time in CUT format as part of the application identifier.

Serious systems have dedicated time synchronization services in force with dedicated time servers on the network. The following picture demonstrates a corporation network with enforced time service synchronization system for the database servers.

Picture 1: Corporate time infrastructure

The total time difference between the servers is sum of

  • transaction communication delay due to the complexity of the network infrastructure
  • transaction operational delay due to the operation delay on the local database partition
  • partition time delay due to the commit delay for the distribution transaction

A local time server uses external, Internet time reference server for local system clock synchronization. At regular intervals, the database servers are pooled and their clocks synchronized with the local time server. A DBA can check for the existence of the time daemon process using the following procedure:

>>  ps -edf | grep xn*
    root   497     1  0   Nov 24 2004        0:03 /usr/lib/inet/xntpd

root@ARTSIT01:/etc# ntpdate -q -s
server, stratum 2, offset -0.000155, delay 0.02605
Listing 4: Network Time Protocol Demon check

The Network Time Protocol (NTP) daemon (unix process xntpd), runs on
the database server machine and references the synchronization point at IP
address The local NTP daemon checks the time on the NTP server at regular intervals, collecting several time samples and adjusting the local time. In our case the actual time drift was 0,02605 seconds.

The Log Sequence Number (LSN)

For the DB2 database, the Log Sequence Number has a very high priority. It is some kind of internal database marker that registers the oldest changed database page (MinBuffLSN) and oldest uncommitted transaction (LowTranLSN) in the buffer pool. During database activity, the LSN markers are updated in the in the Log Control Header, which is maintained in memory and disk.

The actual database Log Sequence Numbers can be found in:

Log Control File Header

Written by agent on commit, by DB2 EDU after a checkpoint, reset log or incomplete recovery

Data and Index Pages

Written by agent in the Page Header used to specify the LSN of the last change in the page

Recovery History File

The last log sequence number saved by the database backup or processed during a roll forward recovery operation

Table 2: LSN registration

Each database commit increments the LSN latch that is unique per transaction. Access to LSN latch is strictly controlled, and access serialized. Only one transaction can access to the latch and the right to increment LSN sequence at a time. The Log Sequence Number is a 48-bit sequence, and consists of a base value (4 bytes) along with a wrap value (2 bytes).

C:\db2\bin\SAMPLE2.0\DB2\NODE0000\CATN0000\20040508>db2ckbkp -H 220242.001 | grep LSN  
                      Base LSN = 0000 00EA 6000
                      Next LSN = 0000 00EA 600C
                  Low Tran LSN = 0000 00EA 600C
                  Min Buff LSN = 0000 00EA 600C
                      Head LSN = 0000 00EA 600C
                      Tail LSN = 0000 00EA 600C
         Forward Rec Trunc LSN = 0000 0000 0000
     Forward Rec Last Read LSN = 0000 00B8 E0EF
       TableSpace Low Tran LSN = 0000 0000 000C
       TableSpace Min Buff LSN = 0000 0000 000C
        TableSpace Restart LSN = 0000 0000 0000
      TableSpace Last Read LSN = 0000 0000 0000
                   Initial LSN = 0000 00EA 600C
            Initial Extent LSN = 0000 00EA 600C
       LastRecLsn value in LFH = 0000 00DA DFC0
                 Reset Log LSN = 0000 0000 0000
Listing 5: Listing LSN numbers from DB2 backup file

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.

Click for larger image

Picture 2: DB2 EEE virtual time concept

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
Listing 6: Database virtual time from backup header

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

Listing 7: DBM CFG parameter max_time_diff

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


Configurable Range (min)

Default ( min)


1- 3600


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.

Table 3: System time drift and DB2 transactions

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
Listing 1: Listing archived log files directly from the TSM server

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

Picture 3: Time drift check on first client connect

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.

Picture 4: Time drift check on commit

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)


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

Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved