[DB2]归档日志实验：在线与离线

    所谓联机归档日志，就是归档日志文件和活动日志文件同在一个目录下；而离线归档日志，则是归档日志与活动日志分开存放。

第一篇在线归档日志实验

1、对日志文件进行修改

1.1、缩小日志文件的容量

为的是演示日志能够迅速写满，然后立刻去写下一个日志。

db2 => update db cfg for demo using LOGFILSIZ 12

DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.

SQL1363W  One or more of the parameters submitted for immediate modification

were not changed dynamically. For these configuration parameters, all

applications must disconnect from this database before the changes become

effective.

         提示需要断开数据库的连接：

db2 => disconnect demo

DB20000I  The SQL DISCONNECT command completed successfully.

db2 => connect to demo

   Database Connection Information

 Database server        = DB2/LINUX 9.7.1

 SQL authorization ID   = DB2INST1

 Local database alias   = DEMO

 

         我们验证下，日志的容量（日志的路径通过get db cfg，去查看Path to log files）：

[db2inst1@localhost SQLOGDIR]$ ll -h

total 624K

-rw------- 1 db2inst1 db2iadm1 48K Jul 23 22:36 S0000000.LOG

-rw------- 1 db2inst1 db2iadm1 48K Jul 23 22:36 S0000001.LOG

-rw------- 1 db2inst1 db2iadm1 48K Jul 23 22:36 S0000002.LOG

-rw------- 1 db2inst1 db2iadm1 48K Jul 23 22:36 S0000003.LOG

-rw------- 1 db2inst1 db2iadm1 48K Jul 23 22:36 S0000004.LOG

-rw------- 1 db2inst1 db2iadm1 48K Jul 23 22:36 S0000005.LOG

-rw------- 1 db2inst1 db2iadm1 48K Jul 23 22:36 S0000006.LOG

-rw------- 1 db2inst1 db2iadm1 48K Jul 23 22:36 S0000007.LOG

-rw------- 1 db2inst1 db2iadm1 48K Jul 23 22:36 S0000008.LOG

-rw------- 1 db2inst1 db2iadm1 48K Jul 23 22:36 S0000009.LOG

-rw------- 1 db2inst1 db2iadm1 48K Jul 23 22:36 S0000010.LOG

-rw------- 1 db2inst1 db2iadm1 48K Jul 23 22:36 S0000011.LOG

-rw------- 1 db2inst1 db2iadm1 48K Jul 23 22:36 S0000012.LOG

 

1.2、修改主日志数和辅日志数：

db2 => update db cfg for demo using logprimary 6

DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.

SQL1363W  One or more of the parameters submitted for immediate modification

were not changed dynamically. For these configuration parameters, all

applications must disconnect from this database before the changes become

effective.

db2 => update db cfg for demo using logsecond 2

DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.

db2 => disconnect demo

DB20000I  The SQL DISCONNECT command completed successfully.

db2 => connect to demo

   Database Connection Information

 Database server        = DB2/LINUX 9.7.1

 SQL authorization ID   = DB2INST1

 Local database alias   = DEMO

         通过get db cfg命令，可以查看到Path to log files，这个就是日志文件存放的目录。当connect to demo后，可以立刻观察到日志数变成了6个（生成主日志了，只有当主日志被写满时，才会自动生成2个从日志）。

2、启用归档模式，类型为联机归档

         将数据库改为归档模式下的联机归档日志模式，只需将数据库参数LOGARCHMETH1设置为logretain，LOGARCHMETH2设置为off即可（保持默认）：

db2 => update db cfg for demo using LOGARCHMETH1 logretain

DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.

SQL1363W  One or more of the parameters submitted for immediate modification

were not changed dynamically. For these configuration parameters, all

applications must disconnect from this database before the changes become

effective.

db2 => force applications all

DB20000I  The FORCE APPLICATION command completed successfully.

DB21024I  This command is asynchronous and may not be effective immediately.

db2 => disconnect demo

DB20000I  The SQL DISCONNECT command completed successfully.

db2 => connect to demo

SQL1116N  A connection to or activation of database "DEMO" cannot be made

because of BACKUP PENDING.  SQLSTATE=57019

         提示，需要进行备份。先创建好备份的目录bak

db2 => backup database demo to /home/db2inst1/bak_rich

Backup successful. The timestamp for this backup image is : 20100723234347

 

3、查看活动日志文件和禁用自动commit

3.1、查看活动日志使用如下命令：

db2 => !db2pd -db demo -logs

 

Database Partition 0 -- Database DEMO -- Active -- Up 0 days 00:13:12

 

Logs:

Current Log Number            2        

Pages Written                 6        

Cur Commit Disk Log Reads     0                  

Cur Commit Total Log Reads    0                  

Method 1 Archive Status       n/a

Method 1 Next Log to Archive  n/a

Method 1 First Failure        n/a

Method 2 Archive Status       n/a

Method 2 Next Log to Archive  n/a

Method 2 First Failure        n/a

Log Chain ID                  0        

Current LSN                   0x00000000779D219A

 

Address    StartLSN         State      Size       Pages      Filename

0x9ADD2048 00000000779B8010 0x00000000 10         10         S0000000.LOG

0x9ADD2888 00000000779C2010 0x00000000 10         10         S0000001.LOG

0x9ADD30C8 00000000779CC010 0x00000000 10         10         S0000002.LOG

0x9ADD3908 00000000779D6010 0x00000000 10         10         S0000003.LOG

0x9ADD4148 00000000779E0010 0x00000000 10         10         S0000004.LOG

0x9ADD4988 00000000779EA010 0x00000000 10         10         S0000005.LOG

3.2、禁止自动commit：

         db2默认是自动提交的，本例中我们将这个给禁止掉。首先查看下auto-commit的情况，发现时开启的。

db2 => list command options

     Command Line Processor Option Settings

 

 Backend process wait time (seconds)        (DB2BQTIME) = 1

 No. of retries to connect to backend        (DB2BQTRY) = 60

 Request queue wait time (seconds)          (DB2RQTIME) = 5

 Input queue wait time (seconds)            (DB2IQTIME) = 5

 Command options                           (DB2OPTIONS) =

 

 Option  Description                               Current Setting

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

   -a    Display SQLCA                             OFF

   -c    Auto-Commit                               ON

   -d    Retrieve and display XML declarations     OFF

   -e    Display SQLCODE/SQLSTATE                  OFF

   -f    Read from input file                      OFF

   -i    Display XML data with indentation         OFF

   -l    Log commands in history file              OFF

   -m    Display the number of rows affected       OFF

   -n    Remove new line character                 OFF

   -o    Display output                            ON

   -p    Display interactive input prompt          ON

   -q    Preserve whitespaces & linefeeds          OFF

   -r    Save output to report file                OFF

   -s    Stop execution on command error           OFF

   -t    Set statement termination character       OFF

   -v    Echo current command                      OFF

   -w    Display FETCH/SELECT warning messages     ON

   -x    Suppress printing of column headings      OFF

   -z    Save all output to output file            OFF

         将command的c选项（即auto-commit）关闭即可：

db2 => update command options using c off

DB20000I  The UPDATE COMMAND OPTIONS command completed successfully.

4、插入数据，查看日志文件的变化

         我们打算向emp表中，插入记录，先查询下是否有记录。

db2 => select count(*) from emp

1         

-----------

    2974800

  1 record(s) selected.

         现在可以使用inser into…select from语句了：

db2 => insert into emp select * from emp  fetch  first 1000 rows only

DB21034E  The command was processed as an SQL statement because it was not a

valid Command Line Processor command.  During SQL processing it returned:

SQL0964C  The transaction log for the database is full.  SQLSTATE=57011

         提示，日志已满。由于我们没有提交事务，而活动日志都已经写满了，无法进行归档，所以报错。

我们现在看下，活动日志的情况，发现总数是8个。由于日志是先写6个主日志，再写2个从日志的。

db2 => !db2pd -db demo -logs

Database Partition 0 -- Database DEMO -- Active -- Up 0 days 00:37:22

Logs:

Current Log Number            10       

Pages Written                 5        

Cur Commit Disk Log Reads     0                  

Cur Commit Total Log Reads    0                  

Method 1 Archive Status       n/a

Method 1 Next Log to Archive  n/a

Method 1 First Failure        n/a

Method 2 Archive Status       n/a

Method 2 Next Log to Archive  n/a

Method 2 First Failure        n/a

Log Chain ID                  0        

Current LSN                   0x0000000077A21CD7

 

Address    StartLSN         State      Size       Pages      Filename

0x9ADD3908 00000000779D6010 0x00000000 10         10         S0000003.LOG

0x9ADD4148 00000000779E0010 0x00000000 10         10         S0000004.LOG

0x9ADD4988 00000000779EA010 0x00000000 10         10         S0000005.LOG

0x9EE01B28 00000000779F4010 0x00000000 10         10         S0000006.LOG

0x9EE02368 00000000779FE010 0x00000000 10         10         S0000007.LOG

0x9ADD2048 0000000077A08010 0x00000000 10         10         S0000008.LOG

0x9ADD2888 0000000077A12010 0x00000000 10         10         S0000009.LOG

0x9ADD30C8 0000000077A1C010 0x00000000 10         10         S0000010.LOG

         再观察日志的编号，活动的日志文件是会变化的：开始是0~5号，6个日志文件；现在序号是3~10号，包含了新生成的两个从日志文件。其他的文件都是“联机归档”日志。

         我们执行下commit，再去查询下emp表的记录数，发现记录数相同，上一次操作是不成功的。

 

         下面减少插入的记录数，查看下活动日志的情况，发现从变为了6~12。于此同时，也发生了日志的归档。

db2 => insert into emp select * from emp  fetch  first 200 rows only

DB20000I  The SQL command completed successfully.

db2 => commit

DB20000I  The SQL command completed successfully.

db2 => !db2pd -db demo -logs

 

Database Partition 0 -- Database DEMO -- Active -- Up 0 days 01:06:14

 

Logs:

Current Log Number            11       

Pages Written                 4        

Cur Commit Disk Log Reads     0                  

Cur Commit Total Log Reads    0                  

Method 1 Archive Status       n/a

Method 1 Next Log to Archive  n/a

Method 1 First Failure        n/a

Method 2 Archive Status       n/a

Method 2 Next Log to Archive  n/a

Method 2 First Failure        n/a

Log Chain ID                  0        

Current LSN                   0x0000000077A2A453

 

Address    StartLSN         State      Size       Pages      Filename

0x9EE01B28 00000000779F4010 0x00000000 10         10         S0000006.LOG

0x9EE02368 00000000779FE010 0x00000000 10         10         S0000007.LOG

0x9ADD2048 0000000077A08010 0x00000000 10         10         S0000008.LOG

0x9ADD2888 0000000077A12010 0x00000000 10         10         S0000009.LOG

0x9ADD30C8 0000000077A1C010 0x00000000 10         10         S0000010.LOG

0x9ADD40C8 0000000077A26010 0x00000000 10         10         S0000011.LOG

0x9EE02FE8 0000000077A30010 0x00000000 10         10         S0000012.LOG

 

第二篇离线归档实验

将LOGARCHMETH1修改为DISK，LOGARCHMETH2设置为off（保持默认即可）。这样归档日志就会变成“离线”模式的。与活动日志不在同一目录下：

db2 => update db cfg for demo using LOGARCHMETH1 DISK:/home/db2inst1/archlog

DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.

         现在我们向进行一些事务操作，产生一定量的日志。

可以看到，我们指定的/home/db2inst1目录下，又生成了如下的层次结构，/home/db2inst1/archlog/db2inst1/DEMO/NODE0000/C0000000。

插入多条记录，发现以前的日志还存在：

 

         查看归档文件夹，已经生成了新的归档日志：

1.jpg

2.jpg

3.jpg

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/14600958/viewspace-669495/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/14600958/viewspace-669495/