DB2 V9.7中新特性（二）---关于锁的改进-数据库日志内容（一）

 我在上一篇文章中讲到DB2 V9.7之所以能够实现类似ORACLE回滚段多版本读的功能，主要是去读取数据库日志的内容，那么数据库日志的内容是什么呢？下面我们给大家讲解如何数据库的日志内容。

1、首先创建一个数据库mydb

C:/>db2 create db mydb
DB20000I  CREATE DATABASE 命令成功完成。

2、创建一张表t1(id int,name varchar(12))

C:/>db2 connect to mydb

   数据库连接信息

 数据库服务器         = DB2/NT 9.5.4
 SQL 授权标识         = ORACLE
 本地数据库别名       = MYDB

C:/>db2 create table t1(id int ,name varchar(12))
DB20000I  SQL 命令成功完成。
3、我们都知道数据库日志中只记录insert、update、delete、create、alter和drop等SQL语句的内容。下面我们分别执行insert、update和delete看看数据库中主要记录什么内容。在这里大家一定要把数据库（ORACLE、INFORMIX、DB2和SYBASE）中的SQL和命令区分开：SQL是DML/DCL/DDL，而命令是数据库特定的操作例如：备份恢复数据库、创建表空间等，SQL的操作室记录在数据库日志中的，而命令是记录在DB2数据库中的历史日志文件中的。

分别执行下列三个操作：

C:/>db2 create table t1(id int ,name varchar(12))
DB20000I  SQL 命令成功完成。

C:/>db2 insert into t1 values(1,'aa')
DB20000I  SQL 命令成功完成。

C:/>db2 update t1 set name='bb' where id=1
DB20000I  SQL 命令成功完成。

C:/>db2 delete from t1
DB20000I  SQL 命令成功完成。
现在我们查看日志内容（读者主要关注红颜色字体部分）：

C:/>cd C:/DB2_01/NODE0000/SQL00003/SQLOGDIR

C:/DB2_01/NODE0000/SQL00003/SQLOGDIR>db2pdlog -C

DB2 UDB V8 -- Log File Formatted Dump
Version 12.01

Enter Log File name ==>S0000000.LOG

Enter Output file name ==>log.txt

Input file is "S0000000.LOG".
Output file is "log.txt".

Processing log page 1 of "S0000000.LOG" (LSN 000001388FFB)...

Last full log record 0000000001388F7B.

45 log records dumped from the log files.
----------------------

C:/DB2_01/NODE0000/SQL00003/SQLOGDIR>

 

 

                 DB2 UDB V8 -- Log File Formatted Dump (v12.01)

                 (C) Copyright IBM Corporation 1989, 1998, 1999

--------------------------------------------------------------------------------

Log File "S0000000.LOG"

            Validity Check 1 = 4
              Format Version = 9
            Log Extent State = 0x00000001
                   Signature = IBMLOG
               Extent Number = 0
                 Extent Size = 1024
             Number of Pages = 1024
          Previous Extent ID = 0                    
             Database Log ID = 1244276641  2009-06-06-08.24.01.000000 GMT
           Current Extent ID = 1244276703  2009-06-06-08.25.03.000000 GMT
                    firstlsn = 000001388000
              minTruncOffset = 000000000000
               fileEntryNum1 = 0
               fileEntryNum2 = 0
              logFileChainId = 4294967295
            Validity Check 2 = 4

            Validity Check 1 = 3
              Format Version = 9
            Log Extent State = 0x00000001
                   Signature = IBMLOG
               Extent Number = 0
                 Extent Size = 1024
             Number of Pages = 1024
          Previous Extent ID = 0                    
             Database Log ID = 1244276641  2009-06-06-08.24.01.000000 GMT
           Current Extent ID = 1244276703  2009-06-06-08.25.03.000000 GMT
                    firstlsn = 000001388000
              minTruncOffset = 000000000000
               fileEntryNum1 = 0
               fileEntryNum2 = 0
              logFileChainId = 4294967295
            Validity Check 2 = 3

--------------------------------------------------------------------------------

    Log Page 1
          Log Data Byte Count = 0FF0 = 4080
         First Log Data Index = 000C = 12
               Log Page Flags = 0010
                      pagelsn = 000001388FFB

---------------------------略----------------------------------------   

    Record 9         Log Page Offset = 0308 = 776
              Log File Offset = 00002308 = 8968
                   Record LSN = 0000 0000 0138 8308
                  Record Size = 0068 = 104
                  Record Type = 4E = Normal
             Log Header Flags = 0001
                   Record TID = 0000000000CC
             Back Pointer LSN = 0000 0138 8248
              Originator Code = 04 = Data Object Manager
                  Function ID = 65 = 101 : CREATE_OBJECT_DP----创建t1表
                      Pool ID = 2 Object ID = 4 ----对象ID，可以通过select tabname from syscat.tables where tbspaceid=2 and tableid=4获取,结果如下：

C:/DB2_01/NODE0000/SQL00003/SQLOGDIR>db2 select tabname from syscat.tables where tbspaceid=2 and tableid=4

TABNAME

--------------------------------------------------------------------------------------------------------------------

T1

  1

-----------待续-----------

条记录已选择。