Tag Archives: db2

关于DB2v9.5的审计

如何进行DB2的审计,网上有很多文章介绍,基本上都是关于DB2版本8或者更老的。而新版的DB2审计功能,关于用户执行的SQL命令的记录,与老版本的有较大区别。

老版本的SQL命令,在CONTEXT审计事件STMTTEXT字段中,而新版本的SQL statement,在EXECUTE审计事件STMTTEXT字段中。以下是在DB2 v9.5版本中使用SQL审计的步骤:

  1. 准备工作:修改audit_buf_sz参数并重启实例,否则审计活动将严重影响数据库性能:

    db2 update dbm cfg using audit_buf_sz 32
    db2stop force
    db2start

  2. 将secadm权限授权给安全管理员用户,编辑sqllib/misc目录下的脚本db2audit.ddl,创建容纳审计数据的表:

    db2 “GRANT SECADM ON DATABASE TO USER BI”
    db2 create schema audit
    db2 connect to mybi user bi using bipwd
    db2 set current schema audit
    db2 -tvf /db2home/db2inst1/sqllib/misc/db2audit.ddl

  3. 创建EXECUTE类型的审计POLICY,在db2 -t命令行执行(注意此类命令必须要COMMIT):

    CREATE AUDIT POLICY STATEMENTS CATEGORIES EXECUTE WITH DATA
       STATUS BOTH ERROR TYPE AUDIT;
    COMMIT;

  4. 启用该数据库审计,在db2 -t命令行执行:

    AUDIT DATABASE USING POLICY STATEMENTS;
    COMMIT;

  5. 建立审计文件的备份目录和装载文件目录:

    cd /db2home/db2inst1/sqllib/security
    mkdir auditarchive
    mkdir auditdelasc

  6. 使用以下命令,将审计文件装载到数据库:

    \rm /db2home/db2inst1/sqllib/security/auditarchive/*
    \rm /db2home/db2inst1/sqllib/security/auditdelasc/*
    db2audit archive database mybi to /db2home/db2inst1/sqllib/security/auditarchive
    db2audit extract delasc to /db2home/db2inst1/sqllib/security/auditdelasc from files /db2home/db2inst1/sqllib/security/auditarchive/*
    db2 connect to mybi user bi using bipwd
    db2 “LOAD FROM /db2home/db2inst1/sqllib/security/auditdelasc/validate.del OF DEL MODIFIED BY DELPRIORITYCHAR LOBSINFILE INSERT INTO audit.validate”
    db2 “LOAD FROM /db2home/db2inst1/sqllib/security/auditdelasc/context.del OF DEL MODIFIED BY DELPRIORITYCHAR LOBSINFILE INSERT INTO audit.context”
    db2 “LOAD FROM /db2home/db2inst1/sqllib/security/auditdelasc/audit.del OF DEL MODIFIED BY DELPRIORITYCHAR LOBSINFILE INSERT INTO audit.audit”
    db2 “LOAD FROM /db2home/db2inst1/sqllib/security/auditdelasc/checking.del OF DEL MODIFIED BY DELPRIORITYCHAR LOBSINFILE INSERT INTO audit.checking”
    db2 “LOAD FROM /db2home/db2inst1/sqllib/security/auditdelasc/sysadmin.del OF DEL MODIFIED BY DELPRIORITYCHAR LOBSINFILE INSERT INTO audit.sysadmin”
    db2 “LOAD FROM /db2home/db2inst1/sqllib/security/auditdelasc/objmaint.del OF DEL MODIFIED BY DELPRIORITYCHAR LOBSINFILE INSERT INTO audit.objmaint”
    db2 “LOAD FROM /db2home/db2inst1/sqllib/security/auditdelasc/secmaint.del OF DEL MODIFIED BY DELPRIORITYCHAR LOBSINFILE INSERT INTO audit.secmaint”
    db2 “LOAD FROM /db2home/db2inst1/sqllib/security/auditdelasc/execute.del OF DEL MODIFIED BY DELPRIORITYCHAR LOBSINFILE INSERT INTO audit.execute”
    db2 terminate

    如果不是系统管理员而是安全审计员BI用户,则可以使用SYSPROC.AUDIT_ARCHIVESYSPROC.AUDIT_DELIM_EXTRACT两个存储过程替代db2audit archive和db2audit extract命令。


Data Warehouse For Ever原创文章,转载请注明出处

谁说DB2不支持竖线分隔符?

我们ETL开发人员在做数据装载时,发现如果使用“|”作为分隔符,无论是export还是load,DB2都会报SQL3017N的错误。

SQL3017N  A delimiter is not valid or is used more than once.

SQL3017N错误的产生,原因如下(摘自官方文档):

对于“定界”ASCII (DEL) 文件,发生以下两种错误之一:

  • 对列定界符、字符串定界符或小数点字符指定的字符无效。
    • 对于 SBCS 或 UTF-8 数据,定界符的有效范围是 0×00 – 0x7F(包括 0×00 和 0x7F 在内)。
    • 对于 MBCS 数据,定界符的有效范围是 0×00 – 0x3F(包括 0×00 和 0x3F 在内)。
    • 对于 EBCDIC MBCS 数据,定界符的有效范围是 0×00 – 0x3F(包括 0×00 和 0x3F 在内),但是,有一种例外情况就是,定界符不能为 SHIFT-OUT(0x0E)或者 SHIFT-IN(0x0F)字符。
  • 为多个前述项目指定了同一个字符。

经过查询,我们数据库的字符集是1386,属于MBCS数据,因此定界符最大是0x3F,而“|”的ASCII码为0x7C,超过了有效范围。解决办法,是做Export或者Load操作时,增加codepage选项,让DB2自动进行字符集转换:

EXPORT TO "/data/temp/card.dat" OF DEL
MODIFIED BY codepage=1208 COLDEL|
MESSAGES "/data/temp/card.log"
SELECT * FROM BI.STG_CDR_OBS_CARD

LOAD CLIENT FROM "/data/temp/card.dat" OF DEL
MODIFIED BY CODEPAGE=1208 COLDEL|
MESSAGES "/data/temp/card.log"
INSERT INTO BI.STG_CDR_OBS_CARD


Data Warehouse For Ever原创文章,转载请注明出处

查看DB2表空间容器的方法

方法一:查询CONTAINER_UTILIZATION或SNAPCONTAINER管理视图

SELECT * FROM SYSIBMADM.CONTAINER_UTILIZATION
WHERE TBSP_ID=7
ORDER BY DBPARTITIONNUM,CONTAINER_ID;

SYSIBMADM.CONTAINER_UTILIZATION视图提供了表空间的Container和空间使用情况等信息,其实这个管理视图是LIST TABLESPACE CONTAINERS命令的SQL版本。

SELECT * FROM SYSIBMADM.SNAPCONTAINER
WHERE TBSP_ID=7
ORDER BY DBPARTITIONNUM,CONTAINER_ID;

SYSIBMADM.SNAPCONTAINER视图与CONTAINER_UTILIZATION视图提供的信息基本相同,除了表空间使用情况的单位没有从字节转换成KB之外,其他看不出啥区别。这个管理视图是GET SNAPSHOT FOR TABLESPACES ON database-alias 命令的SQL版本。实际上,IBM的官方文档上解释,CONTAINER_UTILIZATION的数据是基于SNAPCONTAINER的信息。

IBM DB2 9.5与该视图相关的官方文档请看这里

方法二:执行list tablespace containers或get snapshot for tablespaces命令


$db2 list tablespace containers for 5

Tablespace Containers for Tablespace 5

Container ID = 0
Name = /dev/rlvtmp_n00_01
Type = Disk


$db2 list tablespace containers for 5 show detail

Tablespace Containers for Tablespace 5

Container ID = 0
Name = /dev/rlvtmp_n00_01
Type = Disk
Total pages = 1064960
Useable pages = 1064928
Accessible = Yes

get snapshot for tablespaces on database-alias的信息是最丰富的,额外提供了表空间bufferpool、直接读写、table space map等信息,可以据此发现表空间可能存在的性能问题。但是需要注意的是,通过命令的方式,只能看到当前所连接节点的相关信息,如果是多节点(DPF)环境,默认为0节点。如果要看其它节点的情况,则需要指定DB2NODE来显示指定连接节点,或者通过db2_all来在所有节点上运行相关命令。

方法三:执行db2pd -tablespaces命令


$db2pd -db zjbi2 -alldbp -tablespaces 7

Database Partition 1 — Database ZJBI2 — Active — Up 5 days 18:18:32

Tablespace 7 Configuration:
Address            Type Content PageSz ExtentSz Auto Prefetch BufID BufIDDisk FSC NumCntrs MaxStripe  LastConsecPg Name
0x0700000039F3E1A0 DMS  Large   32768  24       No   192      2     2         Off 4        0          23           TBS_CDR

Tablespace 7 Statistics:
Address            TotalPgs   UsablePgs  UsedPgs    PndFreePgs FreePgs    HWM        State      MinRecTime NQuiescers
0x0700000039F3E1A0 63897600   63897504   25619832   0          38277672   25644504   0×00000000 0          0         

Tablespace 7 Autoresize Statistics:
Address            AS  AR  InitSize    IncSize     IIP MaxSize     LastResize                 LRF
0x0700000039F3E1A0 No  No  0           0           No  0           None                       No 

Containers:
Address            ContainNum Type    TotalPgs   UseablePgs StripeSet  Container
0x0700000039F3E840 0          Disk    15974400   15974376   0          /dev/rlvcdr_n01_01
0x0700000039F3E9A0 1          Disk    15974400   15974376   0          /dev/rlvcdr_n01_02
0x0700000039F3EB00 2          Disk    15974400   15974376   0          /dev/rlvcdr_n01_03
0x0700000039F3EC60 3          Disk    15974400   15974376   0          /dev/rlvcdr_n01_04

Database Partition 2 — Database ZJBI2 — Active — Up 5 days 18:18:32

Tablespace 7 Configuration:
Address            Type Content PageSz ExtentSz Auto Prefetch BufID BufIDDisk FSC NumCntrs MaxStripe  LastConsecPg Name
0x0700000039F3E1A0 DMS  Large   32768  24       No   192      2     2         Off 4        0          23           TBS_CDR

Tablespace 7 Statistics:
Address            TotalPgs   UsablePgs  UsedPgs    PndFreePgs FreePgs    HWM        State      MinRecTime NQuiescers
0x0700000039F3E1A0 63897600   63897504   25694472   96         38202936   25719240   0×00000000 0          0         

Tablespace 7 Autoresize Statistics:
Address            AS  AR  InitSize    IncSize     IIP MaxSize     LastResize                 LRF
0x0700000039F3E1A0 No  No  0           0           No  0           None                       No 

Containers:
Address            ContainNum Type    TotalPgs   UseablePgs StripeSet  Container
0x0700000039F3E840 0          Disk    15974400   15974376   0          /dev/rlvcdr_n02_01
0x0700000039F3E9A0 1          Disk    15974400   15974376   0          /dev/rlvcdr_n02_02
0x0700000039F3EB00 2          Disk    15974400   15974376   0          /dev/rlvcdr_n02_03
0x0700000039F3EC60 3          Disk    15974400   15974376   0          /dev/rlvcdr_n02_04

以上db2pd命令带了三个参数,第一个参数-db指定了数据库的名称是zjbi2,第二个参数-alldbp指定该命令在所有数据库分区上执行,第三个命令-tablespaces指定了该命令只查看id为7的表空间情况。

db2pd命令不需要连接数据库,但必须在该表空间物理所在的主机上执行,该命令的执行结果反映的是该主机上所有节点的情况。如本人的DB2 DPF环境,有三台主机,主机A仅有管理节点(0节点),主机B和主机C各有4个数据节点。如果我要查询应用数据表空间的使用情况,则需要在主机B和主机C上分别执行db2pd命令(考虑数据表空间一般不占用0节点的情况)。


Data Warehouse For Ever原创文章,转载请注明出处

勿混用过滤条件(where)和连接条件(on)

SQL中,where子句是过滤条件,on子句是连接条件,两者不能混用。如果混用,可能会导致DB2产生错误的执行计划,从而引发严重的性能问题。很多新手比较容易犯此类错误,在没有发生严重的的性能问题时,往往因为没有引起注意而不会得到重视。下面是我在监控DB2性能问题时发现的一个问题SQL:

select t.* , case when u.user_id is not null then 1 else 0 end as flag
from shenll_gprs_flow_200908 t left join dw_user_m_200908 u
on t.user_id=u.user_id and u.is_bill=1 and u.user_type not in(2,3,4) and t.area_id='570'

该SQL语句将过滤条件写到关联条件中,导致DB2生成了错误的执行计划,使用了内连接NLJoin对两张大表进行了关联,引起了严重的性能问题。以下是上述语句的执行计划:

错误的执行计划

对该语句进行修改,使用正确的on语句和where语句:

select t.* , case when u.user_id is not null then 1 else 0 end as flag
from shenll_gprs_flow_200908 t left join dw_user_m_200908 u
on t.user_id=u.user_id
where u.is_bill=1 and u.user_type not in(2,3,4) and t.area_id='570'

上述SQL的执行计划如下:

正确的执行计划

从上面的执行计划中可以看到,DB2使用了哈希连接,整个SQL的执行成本从5亿降低到6万,执行效率得到了数据级的提升。


Data Warehouse For Ever原创文章,转载请注明出处

DB2归档及恢复试验

[试验环境]
1.单节点:Windows XP, DB2 9.5
2.8+1节点:AIX 5.2, DB2 9.5 + DPF

[试验步骤]
1. 修改日志文件大小参数

修改日志文件大小参数,改到最小,否则很难搞到日志爆满。
这是日志的原始大小:

$ls -l
total 24048
-rw——- 1 db2inst1 db2iadm1 4104192 Apr 09 10:46 S0000000.LOG
-rw——- 1 db2inst1 db2iadm1 4104192 Apr 09 10:46 S0000001.LOG
-rw——- 1 db2inst1 db2iadm1 4104192 Apr 09 10:46 S0000002.LOG

执行以下命令:

db2 “force applications all”
db2 terminate
db2 connect to sample
db2 “update db cfg for sample using logfilsiz 4″

这里执行完即可发现C:\DB2\NODE0000\SQL00002\SQLOGDIR目录下的日志都变成很小的了:

$ls -l
total 144
-rw——- 1 db2inst1 db2iadm1 24576 Apr 09 10:47 S0000000.LOG
-rw——- 1 db2inst1 db2iadm1 24576 Apr 09 10:47 S0000001.LOG
-rw——- 1 db2inst1 db2iadm1 24576 Apr 09 10:47 S0000002.LOG

注意改成4个4kpagesize后,实际上日志大小有6个4kpagesize。
说明日志的实际大小为:logfilsiz*4k+2k。

另:日志最小为4个4k pagesize,否则会报错:

$db2 “update db cfg for sample using logfilsiz 1″
SQL5130N The value specified for the configuration parameter “logfilsiz” is
not in the valid range of “4″ to “1048572″.

2. 修改LOGARCHMETH1参数以指定备份目录

修改LOGARCHMETH1 参数,LOGARCHMETH2这里就不改了,这样只备份至一个地方

db2 “update db cfg for sample using LOGARCHMETH1 disk:/db2home2/db2inst2/dbbackup”

注意,修改该参数之前,必须保证该备份目录存在,否则会报错:

$db2 “update db cfg for sample using LOGARCHMETH1 disk:c:/db2/backup”
SQL5099N 由数据库配置参数 “LOGARCHMETH1″ 指示的值 “c:/db2/backup”
无效,原因码为 “2″。 SQLSTATE=08004

完了之后,尝试连接,可以发现归档目录自动创建了一些文件夹:
F:\db2backup\DB2\SAMPLE\NODE0000\C0000000
这时,如果连接数据库会报错:
中文环境:

$db2 connect to sample
SQL1116N 因为 BACKUP PENDING,所以不能连接或激活数据库 “SAMPLE”。
SQLSTATE=57019

英文环境:

$db2 connect to sample
SQL1116N A connection to or activation of database “SAMPLE” cannot be made
because of BACKUP PENDING. SQLSTATE=57019

3. 发出备份命令备份数据库

如果是单节点数据库,则执行如下命令:

db2 backup db sample TO /db2home2/db2inst2/dbbackup

如果是DPF多节点数据库,必须在所有节点上执行备份,否则查询跨分区表空间仍会报错:

$db2 “select count(*) from act”
1
———–
SQL1116N A connection to or activation of database “SAMPLE” cannot be made
because of BACKUP PENDING. SQLSTATE=57019

$db2 “select * from act”
ACTNO ACTKWD ACTDESC
—— —— ——————–
10 MANAGE MANAGE/ADVISE
20 ECOST ESTIMATE COST
70 CODE CODE PROGRAMS
80 TEST TEST PROGRAMS
170 ADMDC ADM DATA COMM
SQL1116N A connection to or activation of database “SAMPLE” cannot be made
because of BACKUP PENDING. SQLSTATE=57019

因此,在DPF多节点数据库环境,则执行如下命令:

$db2 BACKUP db sample ON ALL DBPARTITIONNUMS TO /db2home2/db2inst2/dbbackup
Part Result
—- ————————————————————————
0000 DB20000I The BACKUP DATABASE command completed successfully.
0001 DB20000I The BACKUP DATABASE command completed successfully.
0002 DB20000I The BACKUP DATABASE command completed successfully.
0003 DB20000I The BACKUP DATABASE command completed successfully.
0004 DB20000I The BACKUP DATABASE command completed successfully.
0005 DB20000I The BACKUP DATABASE command completed successfully.
0006 DB20000I The BACKUP DATABASE command completed successfully.
0007 DB20000I The BACKUP DATABASE command completed successfully.
0008 DB20000I The BACKUP DATABASE command completed successfully.
Backup successful. The timestamp for this backup image is : 20100409114802

可以使用list backup命令进行查询:

$db2 list backup all for db sample

List History File for sample
Number of matching file entries = 2
Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID
– — —————— —- — ———— ———— ————–
B D 20100409112609001 F D S0000000.LOG S0000000.LOG
—————————————————————————-
Contains 3 tablespace(s):
00001 SYSCATSPACE
00002 USERSPACE1
00003 IBMDB2SAMPLEREL
—————————————————————————-
Comment: DB2 BACKUP SAMPLE OFFLINE
Start Time: 20100409112609
End Time: 20100409112611
Status: A
—————————————————————————-
EID: 3 Location: /db2home2/db2inst2/dbbackup
Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID
– — —————— —- — ———— ———— ————–
B D 20100409114802001 F D S0000030.LOG S0000030.LOG
—————————————————————————-
Contains 3 tablespace(s):
00001 SYSCATSPACE
00002 USERSPACE1
00003 IBMDB2SAMPLEREL
—————————————————————————-
Comment: DB2 BACKUP SAMPLE OFFLINE
Start Time: 20100409114802
End Time: 20100409114812
Status: A
—————————————————————————-
EID: 34 Location: /db2home2/db2inst2/dbbackup

这时,查看备份目录的文件:

$ls -l
total 958568
-rw——- 1 db2inst2 db2iadm1 102588416 Apr 09 11:26 SAMPLE.0.db2inst2.NODE0000.CATN0000.20100409112609.001
-rw——- 1 db2inst2 db2iadm1 102588416 Apr 09 11:48 SAMPLE.0.db2inst2.NODE0000.CATN0000.20100409114802.001
-rw——- 1 db2inst2 db2iadm1 35688448 Apr 09 11:48 SAMPLE.0.db2inst2.NODE0001.CATN0000.20100409114802.001
-rw——- 1 db2inst2 db2iadm1 35688448 Apr 09 11:48 SAMPLE.0.db2inst2.NODE0002.CATN0000.20100409114802.001
-rw——- 1 db2inst2 db2iadm1 35688448 Apr 09 11:48 SAMPLE.0.db2inst2.NODE0003.CATN0000.20100409114802.001
-rw——- 1 db2inst2 db2iadm1 35688448 Apr 09 11:48 SAMPLE.0.db2inst2.NODE0004.CATN0000.20100409114802.001
-rw——- 1 db2inst2 db2iadm1 35688448 Apr 09 11:48 SAMPLE.0.db2inst2.NODE0005.CATN0000.20100409114802.001
-rw——- 1 db2inst2 db2iadm1 35688448 Apr 09 11:48 SAMPLE.0.db2inst2.NODE0006.CATN0000.20100409114802.001
-rw——- 1 db2inst2 db2iadm1 35688448 Apr 09 11:48 SAMPLE.0.db2inst2.NODE0007.CATN0000.20100409114802.001
-rw——- 1 db2inst2 db2iadm1 35688448 Apr 09 11:48 SAMPLE.0.db2inst2.NODE0008.CATN0000.20100409114802.001
drwxr-x— 3 db2inst2 db2iadm1 256 Apr 09 11:20 db2inst2

4. 做一些insert、update、delete等DML操作

5. 发出恢复命令恢复数据库

恢复数据库操作,数据库进入ROLL-FORWARD PENDING状态

$db2 “restore db sample from /db2home2/db2inst2/dbbackup taken at 20100409114802 replace existing without prompting”
SQL2540W Restore is successful, however a warning “2539″ was encountered
during Database Restore while processing in No Interrupt mode.

这时如果连接数据库,会报如下错误:

$db2 connect to sample
SQL1117N A connection to or activation of database “SAMPLE” cannot be made
because of ROLL-FORWARD PENDING. SQLSTATE=57019

如果是多分区数据库,则使用如下语句:

db2_all ‘<<+0< db2 “restore db sample from /db2home2/db2inst2/dbbackup taken at 20100409114802 replace existing”‘
db2_all ‘<<+1< db2 “restore db sample from /db2home2/db2inst2/dbbackup taken at 20100409114802 replace existing”‘
db2_all ‘<<+2< db2 “restore db sample from /db2home2/db2inst2/dbbackup taken at 20100409114802 replace existing”‘
db2_all ‘<<+3< db2 “restore db sample from /db2home2/db2inst2/dbbackup taken at 20100409114802 replace existing”‘
db2_all ‘<<+4< db2 “restore db sample from /db2home2/db2inst2/dbbackup taken at 20100409114802 replace existing”‘
db2_all ‘<<+5< db2 “restore db sample from /db2home2/db2inst2/dbbackup taken at 20100409114802 replace existing”‘
db2_all ‘<<+6< db2 “restore db sample from /db2home2/db2inst2/dbbackup taken at 20100409114802 replace existing”‘
db2_all ‘<<+7< db2 “restore db sample from /db2home2/db2inst2/dbbackup taken at 20100409114802 replace existing”‘
db2_all ‘<<+8< db2 “restore db sample from /db2home2/db2inst2/dbbackup taken at 20100409114802 replace existing”‘

查询状态,注意默认时间是UTC时间:

$db2 “rollforward db sample query status”

Rollforward Status

Input database alias = sample
Number of nodes have returned status = 9

Node number Rollforward Next log Log files processed Last committed transaction
status to be read
———– ————————– ——————- ————————- ————————–
0 DB pending S0000030.LOG – 2010-04-09-03.48.06.000000 UTC
1 not pending – 1970-01-01-00.00.00.000000 UTC
2 not pending – 1970-01-01-00.00.00.000000 UTC
3 not pending – 1970-01-01-00.00.00.000000 UTC
4 not pending – 1970-01-01-00.00.00.000000 UTC
5 not pending – 1970-01-01-00.00.00.000000 UTC
6 not pending – 1970-01-01-00.00.00.000000 UTC
7 not pending – 1970-01-01-00.00.00.000000 UTC
8 not pending – 1970-01-01-00.00.00.000000 UTC

$db2 “rollforward db sample query status”

Rollforward Status

Input database alias = sample
Number of nodes have returned status = 9

Node number Rollforward Next log Log files processed Last committed transaction
status to be read
———– ————————– ——————- ————————- ————————–
0 DB pending S0000030.LOG – 2010-04-09-03.48.06.000000 UTC
1 DB pending S0000000.LOG – 2010-04-09-03.48.11.000000 UTC
2 DB pending S0000000.LOG – 2010-04-09-03.48.10.000000 UTC
3 DB pending S0000000.LOG – 2010-04-09-03.48.11.000000 UTC
4 DB pending S0000000.LOG – 2010-04-09-03.48.11.000000 UTC
5 DB pending S0000000.LOG – 2010-04-09-03.48.10.000000 UTC
6 DB pending S0000000.LOG – 2010-04-09-03.48.11.000000 UTC
7 DB pending S0000000.LOG – 2010-04-09-03.48.10.000000 UTC
8 DB pending S0000000.LOG – 2010-04-09-03.48.11.000000 UTC

$db2 “rollforward db sample query status using local time”

Rollforward Status

Input database alias = sample
Number of nodes have returned status = 9

Node number Rollforward Next log Log files processed Last committed transaction
status to be read
———– ————————– ——————- ————————- ————————–
0 DB pending S0000030.LOG – 2010-04-09-11.48.06.000000 Local
1 DB pending S0000000.LOG – 2010-04-09-11.48.11.000000 Local
2 DB pending S0000000.LOG – 2010-04-09-11.48.10.000000 Local
3 DB pending S0000000.LOG – 2010-04-09-11.48.11.000000 Local
4 DB pending S0000000.LOG – 2010-04-09-11.48.11.000000 Local
5 DB pending S0000000.LOG – 2010-04-09-11.48.10.000000 Local
6 DB pending S0000000.LOG – 2010-04-09-11.48.11.000000 Local
7 DB pending S0000000.LOG – 2010-04-09-11.48.10.000000 Local

8 DB pending S0000000.LOG – 2010-04-09-11.48.11.000000 Local

6. 执行前滚操作

执行以下命令:

db2 rollforward db sample to end of logs
db2 rollforward db sample complete

以下是操作日志:

$db2 rollforward db sample to end of logs

Rollforward Status

Input database alias = sample
Number of nodes have returned status = 9

Node number Rollforward Next log Log files processed Last committed transaction
status to be read
———– ————————– ——————- ————————- ————————–
0 DB working S0000383.LOG S0000030.LOG-S0000382.LOG 2010-04-09-14.15.03.000000 Local
1 DB working S0000004.LOG S0000000.LOG-S0000004.LOG 2010-04-09-13.51.53.000000 Local
2 DB working S0000004.LOG S0000000.LOG-S0000004.LOG 2010-04-09-13.51.53.000000 Local
3 DB working S0000004.LOG S0000000.LOG-S0000004.LOG 2010-04-09-13.51.53.000000 Local
4 DB working S0000001.LOG S0000000.LOG-S0000001.LOG 2010-04-09-13.51.53.000000 Local
5 DB working S0000005.LOG S0000000.LOG-S0000005.LOG 2010-04-09-13.51.53.000000 Local
6 DB working S0000002.LOG S0000000.LOG-S0000002.LOG 2010-04-09-13.51.53.000000 Local
7 DB working S0000002.LOG S0000000.LOG-S0000002.LOG 2010-04-09-13.51.53.000000 Local
8 DB working S0000004.LOG S0000000.LOG-S0000004.LOG 2010-04-09-13.51.53.000000 Local

DB20000I The ROLLFORWARD command completed successfully.

这个时候还没完,还必须发出一个complete命令,否则连接数据库还是报错。

$db2 rollforward db sample complete

Rollforward Status

Input database alias = sample
Number of nodes have returned status = 9

Node number Rollforward Next log Log files processed Last committed transaction
status to be read
———– ————————– ——————- ————————- ————————–
0 not pending S0000030.LOG-S0000383.LOG 2010-04-09-14.15.03.000000 Local
1 not pending S0000000.LOG-S0000004.LOG 2010-04-09-13.51.53.000000 Local
2 not pending S0000000.LOG-S0000004.LOG 2010-04-09-13.51.53.000000 Local
3 not pending S0000000.LOG-S0000004.LOG 2010-04-09-13.51.53.000000 Local
4 not pending S0000000.LOG-S0000001.LOG 2010-04-09-13.51.53.000000 Local
5 not pending S0000000.LOG-S0000005.LOG 2010-04-09-13.51.53.000000 Local
6 not pending S0000000.LOG-S0000002.LOG 2010-04-09-13.51.53.000000 Local
7 not pending S0000000.LOG-S0000002.LOG 2010-04-09-13.51.53.000000 Local
8 not pending S0000000.LOG-S0000004.LOG 2010-04-09-13.51.53.000000 Local

DB20000I The ROLLFORWARD command completed successfully.

7. 完成。数据库恢复正常。


Data Warehouse For Ever原创文章,转载请注明出处