DB2’s Virtual Time

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)

CURR_DATE CURR_TIME CURR_TZ
———- ——– ——–
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:


db2inst1:/export/home/db2inst1$date
Mon May 10 09:58:45 MEST DST 2004

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

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 201.34.2.22
server 201.34.2.22, 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 201.34.2.22. 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

Marin Komadina
Marin Komadina
Marin was born June 27, 1968 in Zagreb, Croatia. He graduated in 1993 form The Faculty for Electrotechnology and Computer Sciences, University of Zagreb in Croatia. He started his professional career as a System specialist and DBA for the Croatian company Informatika System. His most important project was the development and implementation of the enterprise, distributed point of sales solution, based on the Oracle technology. In 1999, Marin became the company CTO, where he played an active role in company development and technical orientation. After Informatika System, Marin worked as an IT Manager Assistant for the Austrian international retail company "Segro," on location in Graz (Austria) and Zagreb (Croatia). He was responsible for the company's technical infrastructure and operational support. Segro used IBM technology, OS/400 operating system and DB2 database. In 1998, Marin joined the international telecommunication company VIPNet GSM that was a part of greater concern, Mobilkom Austria& Western Wireless Int. USA. After one year, Marin took over the IT System Manager position, where he managed many multi-platform, telecommunication projects and was leading the IT system department. In 2001, Marin started to work in Germany as a senior system architect. He is currently working for German banks on different banking projects.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles