DB2's Virtual TimeMay 31, 2004 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 TimeAccording 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.
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 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 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
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.
The total time difference between the servers is sum of
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
The Network Time Protocol (NTP)
daemon (unix process xntpd), runs on 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:
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
|
|
|
The Network for Technology Professionals
Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers
