记一次存储故障导致数据库坏块处理过程
线上架构说明：
IBM DS4800存储一套
P560小机HA架构一套
两个数据库资源组平时run在HA架构中的任意一台中，资源组全部使用共享存储问题描述：
由于存储在数据库运行过程中发生了异常宕机，导致两个库存在不同程度的坏块错误信息及解决过程
数据库A：
A:root:/db2dumph/istclhis >
2016-04-09-04.26.10.787138 Instance:istclhis Node:000
PID:1405020(db2agnsc (WCELHISP) 0) TID:1 Appid:none
base sys utilities sqleMarkDBad Probe:210
Database logging stopped due to mark db bad.
PID:1405020 TID:1 Node:000 Title: SQLE_AGENTCB
Dump File:/db2dumph/istclhis/14050201.000
PID:1405020 TID:1 Node:000 Title: SQLE_AGENT_PRIVATECB
Dump File:/db2dumph/istclhis/14050201.000
PID:1405020 TID:1 Node:000 Title: SQLE_DBCB
Dump File:/db2dumph/istclhis/14050201.000
PID:1405020 TID:1 Node:000 Title: SQLE_TRAN_CB
Dump File:/db2dumph/istclhis/14050201.000
PID:1405020 TID:1 Node:000 Title: SQLE_MASTER_APP_CB
Dump File:/db2dumph/istclhis/14050201.000
PID:1405020 TID:1 Node:000 Title: SQLE_APP_CB
Dump File:/db2dumph/istclhis/14050201.000
PID:1405020 TID:1 Node:000 Title: SQLE_COORDINATOR_CB
Dump File:/db2dumph/istclhis/14050201.000
2016-04-09-04.26.10.798863 Instance:istclhis Node:000
PID:1405020(db2agnsc (WCELHISP) 0) TID:1 Appid:none
data management sqldRedo Probe:5124
DIA8500C A data file error has occurred, record id is "".
ZRC=0x87040001M-^?
2016-04-09-04.26.10.799431 Instance:istclhis Node:000
PID:1405020(db2agnsc (WCELHISP) 0) TID:1 Appid:none
data management sqldmrdo Probe:770
DIA8500C A data file error has occurred, record id is "".
ZRC=0x87040001M-^?
016-04-09-04.26.10.799998 Instance:istclhis Node:000
PID:1405020(db2agnsc (WCELHISP) 0) TID:1 Appid:none
data management sqldmrdo Probe:770
Error during REDO of LSN:
0x487FCB0A : A782 8672 A223 M-'..rM-"#
2016-04-09-04.26.10.802006 Instance:istclhis Node:000
PID:1405020(db2agnsc (WCELHISP) 0) TID:1 Appid:none
data management sqldmrdo Probe:770
Error during REDO of log record:
0x40119237 : 016A 0005 0007 1EE6 4164 0F00 00D6 04A8 .j.....M-fAd...M-V.M-(
0x40119247 : 0000 1000 00D6 0100 00CE 5353 3031 3031 .....M-V...M-NSS0101
0x40119257 : 4E52 4437 3942 3030 3130 2020 2020 3130 NRD79B0010 10
0x40119267 : 2020 2020 2020 2020 2020 2020 2020 2020
0x40119277 : 2020 2020 2020 2020 2020 2020 2020 2020
0x40119287 : 2020 5050 4C41 4330 3730 4E4A 3031 3030 PPLAC070NJ0100
0x40119297 : 4346 5753 5730 3730 3730 4E4A 3031 2020 CFWSW07070NJ01
0x401192A7 : 4C32 4620 3643 5554 2030 2E35 5420 2020 L2F 6CUT 0.5T
0x401192B7 : 2020 2020 2020 2020 2020 2020 2020 2020
0x401192C7 : 2020 2020 2020 2020 2020 2020 2020 2020
0x401192D7 : 2020 2020 2020 2020 2020 2020 2020 2020
0x401192E7 : 2020 2020 2020 2020 2020 2020 2020 2020
0x401192F7 : 2020 2020 2020 2020 2020 2020 2020 2020
0x40119307 : 2020 2020 2020 2020 2020 2020 2020 2016 .
0x40119317 : 0408 2130 0100 0088 ..!0....
2016-04-09-04.26.10.804185 Instance:istclhis Node:000
PID:1405020(db2agnsc (WCELHISP) 0) TID:1 Appid:none
recovery manager sqlpRecDbRedo Probe:155
REDO failed on LSN
0x487FCB08 : 0x0000A7828672A223 ..M-'..rM-"#
PID:1405020 TID:1 Node:000 Title: SQLP_DBCB
Dump File:/db2dumph/istclhis/14050201.000
2016-04-09-04.26.10.807143 Instance:istclhis Node:000
PID:1405020(db2agnsc (WCELHISP) 0) TID:1 Appid:none
recovery manager sqlpPRecProcLog Probe:250
DIA8500C A data file error has occurred, record id is "".
ZRC=0x87040001M-^?
2016-04-09-04.26.10.807784 Instance:istclhis Node:000
PID:1405020(db2agnsc (WCELHISP) 0) TID:1 Appid:none
recovery manager sqlpPRecProcLog Probe:250
qEntry for 0000A7828672A223entryFlags 1 queueId 10 waitOthers 0 numBlocked 0 lrHeader:
查看备份信息
WSRHISDB:istclhis:/istclhis >db2 list history backup all for wcelhisp
Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID
-- --- ------------------ ---- --- ------------ ------------ --------------
B D 20160408043017001 N A S5618312.LOG S5618358.LOG
----------------------------------------------------------------------------
Contains 9 tablespace(s):
00001 SYSCATSPACE
00002 USERSPACE1
00003 TBS32K01
00004 INX32K01
00005 TBS8K01
00006 INX8K01
00007 TBS8K02
00008 INX8K02
00009 TBS8K03
----------------------------------------------------------------------------
Comment: DB2 BACKUP WCELHISP ONLINE
Start Time: 20160408043017
End Time: 20160408045340
----------------------------------------------------------------------------
00008 Location: adsm/libtsm.a
WSRHISDB:istclhis:/istclhis >db2adutl query
Query for database WCELHISP
Retrieving FULL DATABASE BACKUP information.
1 Time: 20160414043017 Oldest log: S5632477.LOG DB Partition Number: 0 Sessions: 1
2 Time: 20160413043017 Oldest log: S5629252.LOG DB Partition Number: 0 Sessions: 1
3 Time: 20160412043017 Oldest log: S5625975.LOG DB Partition Number: 0 Sessions: 1
4 Time: 20160411043016 Oldest log: S5622825.LOG DB Partition Number: 0 Sessions: 1
5 Time: 20160410043016 Oldest log: S5619565.LOG DB Partition Number: 0 Sessions: 1
6 Time: 20160408043017 Oldest log: S5618312.LOG DB Partition Number: 0 Sessions: 1
7 Time: 20160407043016 Oldest log: S5615044.LOG DB Partition Number: 0 Sessions: 1
8 Time: 20160406043016 Oldest log: S5611782.LOG DB Partition Number: 0 Sessions: 1
9 Time: 20160405043017 Oldest log: S5608522.LOG DB Partition Number: 0 Sessions: 1
10 Time: 20160404043017 Oldest log: S5605279.LOG DB Partition Number: 0 Sessions: 1
11 Time: 20160403043017 Oldest log: S5602016.LOG DB Partition Number: 0 Sessions: 1
12 Time: 20160402043017 Oldest log: S5598746.LOG DB Partition Number: 0 Sessions: 1
13 Time: 20160401043017 Oldest log: S5595490.LOG DB Partition Number: 0 Sessions: 1
14 Time: 20160331043016 Oldest log: S5592249.LOG DB Partition Number: 0 Sessions: 1
15 Time: 20160330043016 Oldest log: S5589006.LOG DB Partition Number: 0 Sessions: 1
16 Time: 20160329043017 Oldest log: S5585759.LOG DB Partition Number: 0 Sessions: 1
17 Time: 20160328043016 Oldest log: S5582508.LOG DB Partition Number: 0 Sessions: 1
18 Time: 20160327043017 Oldest log: S5579256.LOG DB Partition Number: 0 Sessions: 1
19 Time: 20160320043016 Oldest log: S5556434.LOG DB Partition Number: 0 Sessions: 1
20 Time: 20160319043017 Oldest log: S5553162.LOG DB Partition Number: 0 Sessions: 1
21 Time: 20160318043017 Oldest log: S5549896.LOG DB Partition Number: 0 Sessions: 1
22 Time: 20160317043017 Oldest log: S5546624.LOG DB Partition Number: 0 Sessions: 1
23 Time: 20160310043017 Oldest log: S5523773.LOG DB Partition Number: 0 Sessions: 1
24 Time: 20160309043016 Oldest log: S5520518.LOG DB Partition Number: 0 Sessions: 1
25 Time: 20160308043016 Oldest log: S5517262.LOG DB Partition Number: 0 Sessions: 1
26 Time: 20160307043016 Oldest log: S5514015.LOG DB Partition Number: 0 Sessions: 1
27 Time: 20160306043017 Oldest log: S5510775.LOG DB Partition Number: 0 Sessions: 1
28 Time: 20160305043016 Oldest log: S5507523.LOG DB Partition Number: 0 Sessions: 1
29 Time: 20160304043017 Oldest log: S5504270.LOG DB Partition Number: 0 Sessions: 1
30 Time: 20160303043017 Oldest log: S5501010.LOG DB Partition Number: 0 Sessions: 1
31 Time: 20160302043016 Oldest log: S5497760.LOG DB Partition Number: 0 Sessions: 1
32 Time: 20160301043017 Oldest log: S5494510.LOG DB Partition Number: 0 Sessions: 1
33 Time: 20160229043017 Oldest log: S5491258.LOG DB Partition Number: 0 Sessions: 1
Retrieving INCREMENTAL DATABASE BACKUP information.
No INCREMENTAL DATABASE BACKUP p_w_picpaths found for WCELHISP
Retrieving DELTA DATABASE BACKUP information.
No DELTA DATABASE BACKUP p_w_picpaths found for WCELHISP
Retrieving TABLESPACE BACKUP information.
No TABLESPACE BACKUP p_w_picpaths found for WCELHISP
Retrieving INCREMENTAL TABLESPACE BACKUP information.
No INCREMENTAL TABLESPACE BACKUP p_w_picpaths found for WCELHISP
Retrieving DELTA TABLESPACE BACKUP information.
No DELTA TABLESPACE BACKUP p_w_picpaths found for WCELHISP
Retrieving LOAD COPY information.
No LOAD COPY p_w_picpaths found for WCELHISP
Retrieving LOG ARCHIVE information.
No LOG ARCHIVE p_w_picpaths found for WCELHISP
确保没有用户使用Db2:
#db2 list applications for wcelhisp
#db2stop force
#db2start
数据库恢复
db2 restore db wcelhisp use tsm taken at 20160408043017
这时数据库处于rollforward-pending state的状态，需要做roll forward 操作：
WSRHISDB:istclhis:/istclhis/cfg >db2 "rollforward db wcelhisp to end of logs and stop overflow log path(/istclhis/arclog/WCELHISP/NODE0000)"
SQL4970N Roll-forward recovery on database "WCELHISP" cannot reach the
specified stop point (end-of-log or point-in-time) because of missing log
file(s) on node(s) "0".
arclog还原
dsmc retrieve /istclhis/arclog/WCELHISP/NODE0000/S5618318.LOG
dsmc retrieve /istclhis/arclog/WCELHISP/NODE0000/S5618319.LOG
dsmc retrieve /istclhis/arclog/WCELHISP/NODE0000/S5618320.LOG
dsmc retrieve /istclhis/arclog/WCELHISP/NODE0000/S5618321.LOG
dsmc retrieve /istclhis/arclog/WCELHISP/NODE0000/S5618322.LOG
dsmc retrieve /istclhis/arclog/WCELHISP/NODE0000/S5618323.LOG
dsmc retrieve /istclhis/arclog/WCELHISP/NODE0000/S5618324.LOG
dsmc retrieve /istclhis/arclog/WCELHISP/NODE0000/S5618325.LOG
dsmc retrieve /istclhis/arclog/WCELHISP/NODE0000/S5618326.LOG
dsmc retrieve /istclhis/arclog/WCELHISP/NODE0000/S5618327.LOG
dsmc retrieve /istclhis/arclog/WCELHISP/NODE0000/S5618328.LOG
dsmc retrieve /istclhis/arclog/WCELHISP/NODE0000/S5618329.LOG
dsmc retrieve /istclhis/arclog/WCELHISP/NODE0000/S5618330.LOG
dsmc retrieve /istclhis/arclog/WCELHISP/NODE0000/S5618331.LOG
dsmc retrieve /istclhis/arclog/WCELHISP/NODE0000/S5618332.LOG
dsmc retrieve /istclhis/arclog/WCELHISP/NODE0000/S5618333.LOG
dsmc retrieve /istclhis/arclog/WCELHISP/NODE0000/S5618334.LOG
dsmc retrieve /istclhis/arclog/WCELHISP/NODE0000/S5618335.LOG
dsmc retrieve /istclhis/arclog/WCELHISP/NODE0000/S5618336.LOG
dsmc retrieve /istclhis/arclog/WCELHISP/NODE0000/S5618337.LOG
dsmc retrieve /istclhis/arclog/WCELHISP/NODE0000/S5618338.LOG
dsmc retrieve /istclhis/arclog/WCELHISP/NODE0000/S5618339.LOG
dsmc retrieve /istclhis/arclog/WCELHISP/NODE0000/S5618340.LOG
dsmc retrieve /istclhis/arclog/WCELHISP/NODE0000/S5618341.LOG
dsmc retrieve /istclhis/arclog/WCELHISP/NODE0000/S5618342.LOG
dsmc retrieve /istclhis/arclog/WCELHISP/NODE0000/S5618343.LOG
dsmc retrieve /istclhis/arclog/WCELHISP/NODE0000/S5618344.LOG
dsmc retrieve /istclhis/arclog/WCELHISP/NODE0000/S5618345.LOG
dsmc retrieve /istclhis/arclog/WCELHISP/NODE0000/S5618346.LOG
dsmc retrieve /istclhis/arclog/WCELHISP/NODE0000/S5618347.LOG
dsmc retrieve /istclhis/arclog/WCELHISP/NODE0000/S5618348.LOG
dsmc retrieve /istclhis/arclog/WCELHISP/NODE0000/S5618349.LOG
dsmc retrieve /istclhis/arclog/WCELHISP/NODE0000/S5618350.LOG
dsmc retrieve /istclhis/arclog/WCELHISP/NODE0000/S5618351.LOG
dsmc retrieve /istclhis/arclog/WCELHISP/NODE0000/S5618352.LOG
dsmc retrieve /istclhis/arclog/WCELHISP/NODE0000/S5618353.LOG
dsmc retrieve /istclhis/arclog/WCELHISP/NODE0000/S5618354.LOG
dsmc retrieve /istclhis/arclog/WCELHISP/NODE0000/S5618355.LOG
dsmc retrieve /istclhis/arclog/WCELHISP/NODE0000/S5618356.LOG
dsmc retrieve /istclhis/arclog/WCELHISP/NODE0000/S5618357.LOG
dsmc retrieve /istclhis/arclog/WCELHISP/NODE0000/S5618358.LOG
WSRHISDB:istclhis:/istclhis/cfg >db2 rollforward db wcelhisp query status using local time
Rollforward Status
Input database alias = wcelhisp
Number of nodes have returned status = 1
Node number = 0
Rollforward status = DB working
Next log file to be read = S5618324.LOG
Log files processed = S5618312.LOG - S5618322.LOG
Last committed transaction = 2016-04-08-04.34.46.000000
WSRHISDB:istclhis:/db2dumph/istclhis >dsmc retrieve /istclhis/arclog/WCELHISP/NODE0000/S5618324.LOG
IBM Tivoli Storage Manager
Command Line Backup/Archive Client Interface
Client Version 5, Release 4, Level 0.0
Client date/time: 04/09/16 18:27:09
(c) Copyright by IBM Corporation and other(s) 1990, 2007. All Rights Reserved.
Retrieve function invoked.
Node Name: WSRHISDB
Session established with server WSRTSM01_SERVER1: Windows
Server Version 5, Release 4, Level 0.0
Server date/time: 04/09/16 18:27:20 Last access: 04/09/16 18:27:19
--- User Action is Required ---
File '/istclhis/arclog/WCELHISP/NODE0000/S5618324.LOG' exists
Select an appropriate action
1. Replace this object
2. Replace all objects that already exist
3. Skip this object
4. Skip all objects that already exist
A. Abort this operation
Action [1,2,3,4,A] : 2
** Interrupted **
ANS1114I Waiting for mount of offline media.
Retrieving 32,776,192 /istclhis/arclog/WCELHISP/NODE0000/S5618324.LOG [Done]
Retrieve processing finished.
Total number of objects retrieved: 1
Total number of objects failed: 0
Total number of bytes transferred: 31.25 MB
Data transfer time: 7.53 sec
Network data transfer rate: 4,246.39 KB/sec
Aggregate data transfer rate: 2,116.78 KB/sec
Elapsed processing time: 00:00:38
WSRHISDB:istclhis:/db2dumph/istclhis >db2 "rollforward db wcelhisp to end of logs and stop overflow log path(/istclhis/arclog/WCELHISP/NODE0000)"
Rollforward Status
Input database alias = wcelhisp
Number of nodes have returned status = 1
Node number = 0
Rollforward status = not pending
Next log file to be read =
Log files processed = S5618312.LOG - S5618359.LOG
Last committed transaction = 2016-04-07-20.54.22.000000
DB20000I The ROLLFORWARD command completed successfully.
WSRHISDB:istclhis:/db2dumph/istclhis >db2 connect to wcelhisp
Database Connection Information
Database server = DB2/6000 8.1.6
SQL authorization ID = ISTCLHIS
Local database alias = WCELHISP
WSRHISDB:istclhis:/db2dumph/istclhis >db2 list tablespace show detail |grep -i 0x
WSRHISDB:istclhis:/db2dumph/istclhis >db2 list tablespaces show detail |grep -i 0x
State = 0x0000
State = 0x0000
State = 0x0000
State = 0x0000
State = 0x0000
State = 0x0000
State = 0x0000
State = 0x0000
State = 0x0000
State = 0x0000
State = 0x0000
State = 0x0000数据库B
一、数据库报错信息及定位：
10.54.200.8 WCELPPTP数据库报错
db2diag.log报错
016-04-18-04.29.29.516197 Instance:istclppt Node:000
PID:1400852(db2agent (WCELPPTP) 0) TID:1 Appid:GA36C803.D1C9.1801F7202929
index manager sqlischd Probe:99 Database:WCELPPTP
DIA8541C The index key could not be found, the value was "".
ZRC=0x8709002C
2016-04-18-04.29.29.516829 Instance:istclppt Node:000
PID:1400852(db2agent (WCELPPTP) 0) TID:1 Appid:GA36C803.D1C9.1801F7202929
index manager sqlischd Probe:99 Database:WCELPPTP
Obj={10;6;1} Par={9;6}
10是tablespaceID 6是objectID 1是类型列index（0是table）
查询
select * from SYSCAT.INDEXES where TBSPACEID =10 and INDEX_OBJECTID= 6
二、处理过程：
1、停止所有应用程序
db2 force applications all
2、停止10.54.200.3上删除ppaneldb的程序
3、数据库全备
db2adutl delete keep 32 db WCELPPTP without prompting
sleep 10
db2 " backup db WCELPPTP online use tsm "
WSRCELDB:istclppt:/istclppt/arclog/WCELPPTP/NODE0000 >~/cfg/db_backup.ksh
Query for database WCELPPTP
Retrieving FULL DATABASE BACKUP information.
Taken at: 20160305013017 DB Partition Number: 0 Sessions: 1
Retrieving INCREMENTAL DATABASE BACKUP information.
No INCREMENTAL DATABASE BACKUP p_w_picpaths found for WCELPPTP
Retrieving DELTA DATABASE BACKUP information.
No DELTA DATABASE BACKUP p_w_picpaths found for WCELPPTP
Retrieving TABLESPACE BACKUP information.
No TABLESPACE BACKUP p_w_picpaths found for WCELPPTP
Retrieving INCREMENTAL TABLESPACE BACKUP information.
No INCREMENTAL TABLESPACE BACKUP p_w_picpaths found for WCELPPTP
Retrieving DELTA TABLESPACE BACKUP information.
No DELTA TABLESPACE BACKUP p_w_picpaths found for WCELPPTP
Retrieving LOAD COPY information.
No LOAD COPY p_w_picpaths found for WCELPPTP
Backup successful. The timestamp for this backup p_w_picpath is : 20160501080011
ISTCLPPT db2bp 363 *LOCAL.istclppt.100BF1000011 0001 1 0 1986798 Performing a Backup Not
db2 list history backup all for wcelpptp
Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID
-- --- ------------------ ---- --- ------------ ------------ --------------
B D 20160501080011001 N A S0409006.LOG S0409007.LOG
----------------------------------------------------------------------------
Contains 11 tablespace(s):
00001 SYSCATSPACE
00002 USERSPACE1
00003 TBS8K01
00004 INX8K01
00005 TBS8K02
00006 INX8K02
00007 TBS8K03
00008 INX8K03
00009 TBS8K04
00010 INX8K04
00011 TBS8K05
----------------------------------------------------------------------------
Comment: DB2 BACKUP WCELPPTP ONLINE
Start Time: 20160501080011
End Time: 20160501085502
----------------------------------------------------------------------------
00013 Location: adsm/libtsm.a
WSRCELDB:istclppt:/test/20160415 >db2adutl query
Query for database WCELBRMP
Retrieving FULL DATABASE BACKUP information.
No FULL DATABASE BACKUP p_w_picpaths found for WCELBRMP
Retrieving INCREMENTAL DATABASE BACKUP information.
No INCREMENTAL DATABASE BACKUP p_w_picpaths found for WCELBRMP
Retrieving DELTA DATABASE BACKUP information.
No DELTA DATABASE BACKUP p_w_picpaths found for WCELBRMP
Retrieving TABLESPACE BACKUP information.
No TABLESPACE BACKUP p_w_picpaths found for WCELBRMP
Retrieving INCREMENTAL TABLESPACE BACKUP information.
No INCREMENTAL TABLESPACE BACKUP p_w_picpaths found for WCELBRMP
Retrieving DELTA TABLESPACE BACKUP information.
No DELTA TABLESPACE BACKUP p_w_picpaths found for WCELBRMP
Retrieving LOAD COPY information.
No LOAD COPY p_w_picpaths found for WCELBRMP
Retrieving LOG ARCHIVE information.
No LOG ARCHIVE p_w_picpaths found for WCELBRMP
Query for database WCELPPTP
Retrieving FULL DATABASE BACKUP information.
1 Time: 20160501080011 Oldest log: S0409006.LOG DB Partition Number: 0 Sessions: 1
2 Time: 20160501013016 Oldest log: S0409003.LOG DB Partition Number: 0 Sessions: 1
3 Time: 20160430013017 Oldest log: S0409001.LOG DB Partition Number: 0 Sessions: 1
4 Time: 20160429013016 Oldest log: S0408999.LOG DB Partition Number: 0 Sessions: 1
5 Time: 20160428013016 Oldest log: S0408998.LOG DB Partition Number: 0 Sessions: 1
6 Time: 20160427013016 Oldest log: S0408992.LOG DB Partition Number: 0 Sessions: 1
7 Time: 20160426013017 Oldest log: S0408936.LOG DB Partition Number: 0 Sessions: 1
8 Time: 20160425013017 Oldest log: S0408930.LOG DB Partition Number: 0 Sessions: 1
9 Time: 20160424013016 Oldest log: S0408924.LOG DB Partition Number: 0 Sessions: 1
10 Time: 20160423013016 Oldest log: S0408902.LOG DB Partition Number: 0 Sessions: 1
11 Time: 20160422013016 Oldest log: S0408878.LOG DB Partition Number: 0 Sessions: 1
12 Time: 20160421013016 Oldest log: S0408849.LOG DB Partition Number: 0 Sessions: 1
13 Time: 20160420013016 Oldest log: S0408812.LOG DB Partition Number: 0 Sessions: 1
14 Time: 20160419013016 Oldest log: S0408773.LOG DB Partition Number: 0 Sessions: 1
15 Time: 20160418013017 Oldest log: S0408735.LOG DB Partition Number: 0 Sessions: 1
16 Time: 20160417013017 Oldest log: S0408706.LOG DB Partition Number: 0 Sessions: 1
17 Time: 20160416090013 Oldest log: S0408682.LOG DB Partition Number: 0 Sessions: 1
18 Time: 20160415013016 Oldest log: S0408625.LOG DB Partition Number: 0 Sessions: 1
19 Time: 20160414013017 Oldest log: S0408586.LOG DB Partition Number: 0 Sessions: 1
20 Time: 20160413013017 Oldest log: S0408547.LOG DB Partition Number: 0 Sessions: 1
21 Time: 20160412013017 Oldest log: S0408492.LOG DB Partition Number: 0 Sessions: 1
22 Time: 20160411083232 Oldest log: S0408428.LOG DB Partition Number: 0 Sessions: 1
23 Time: 20160408013017 Oldest log: S0408300.LOG DB Partition Number: 0 Sessions: 1
24 Time: 20160407013017 Oldest log: S0408253.LOG DB Partition Number: 0 Sessions: 1
25 Time: 20160406013016 Oldest log: S0408216.LOG DB Partition Number: 0 Sessions: 1
26 Time: 20160405013017 Oldest log: S0408186.LOG DB Partition Number: 0 Sessions: 1
27 Time: 20160404013016 Oldest log: S0408174.LOG DB Partition Number: 0 Sessions: 1
28 Time: 20160403013016 Oldest log: S0408146.LOG DB Partition Number: 0 Sessions: 1
29 Time: 20160310013016 Oldest log: S0407338.LOG DB Partition Number: 0 Sessions: 1
30 Time: 20160309013017 Oldest log: S0407290.LOG DB Partition Number: 0 Sessions: 1
31 Time: 20160308013017 Oldest log: S0407253.LOG DB Partition Number: 0 Sessions: 1
32 Time: 20160307013016 Oldest log: S0407219.LOG DB Partition Number: 0 Sessions: 1
33 Time: 20160306013017 Oldest log: S0407161.LOG DB Partition Number: 0 Sessions: 1
Retrieving INCREMENTAL DATABASE BACKUP information.
No INCREMENTAL DATABASE BACKUP p_w_picpaths found for WCELPPTP
Retrieving DELTA DATABASE BACKUP information.
No DELTA DATABASE BACKUP p_w_picpaths found for WCELPPTP
Retrieving TABLESPACE BACKUP information.
No TABLESPACE BACKUP p_w_picpaths found for WCELPPTP
Retrieving INCREMENTAL TABLESPACE BACKUP information.
No INCREMENTAL TABLESPACE BACKUP p_w_picpaths found for WCELPPTP
Retrieving DELTA TABLESPACE BACKUP information.
No DELTA TABLESPACE BACKUP p_w_picpaths found for WCELPPTP
Retrieving LOAD COPY information.
No LOAD COPY p_w_picpaths found for WCELPPTP
Retrieving LOG ARCHIVE information.
No LOG ARCHIVE p_w_picpaths found for WCELPPTP
Query for database WCELUACP
Retrieving FULL DATABASE BACKUP information.
No FULL DATABASE BACKUP p_w_picpaths found for WCELUACP
Retrieving INCREMENTAL DATABASE BACKUP information.
No INCREMENTAL DATABASE BACKUP p_w_picpaths found for WCELUACP
Retrieving DELTA DATABASE BACKUP information.
No DELTA DATABASE BACKUP p_w_picpaths found for WCELUACP
Retrieving TABLESPACE BACKUP information.
No TABLESPACE BACKUP p_w_picpaths found for WCELUACP
Retrieving INCREMENTAL TABLESPACE BACKUP information.
No INCREMENTAL TABLESPACE BACKUP p_w_picpaths found for WCELUACP
Retrieving DELTA TABLESPACE BACKUP information.
No DELTA TABLESPACE BACKUP p_w_picpaths found for WCELUACP
Retrieving LOAD COPY information.
No LOAD COPY p_w_picpaths found for WCELUACP
Retrieving LOG ARCHIVE information.
No LOG ARCHIVE p_w_picpaths found for WCELUACP
4、ppanledb表数据导出
18912.61 expppanedb.ixf
WSRCELDB:root:/test/20160415 >more exp.sh
#/bin/sh
db2 connect to wcelpptp
db2 "export to /test/20160415/expppanedb.ixf of ixf messages /test/20160415/expppaneldb.log select * from w2.ppaneldb"
WSRCELDB:istclppt:/test/20160415 >tail -f expppaneldb.log
SQL3104N The Export utility is beginning to export data to file
"/test/20160415/expppanedb.ixf".
SQL3105N The Export utility has finished exporting "5381266" rows.
5、数据验证
select count (* ) from W2.PPANELDB
6、空间确认
此次将ppaneldb从tbs8k04迁移至tbs8k05
查看tbs8k05表空间使用情况
空间足够迁移
7、建立新表
SET SCHEMA = 'ISTCLPPT' ;
CREATE TABLE "W2"."PPANELDB160501" (
"CHIPID" CHARACTER(14) NOT NULL ,
......
)
IN "TBS8K05"
INDEX IN "INX8K04" ;
ALTER TABLE "W2"."PPANELDB160501 "
DATA CAPTURE NONE
LOCKSIZE ROW
APPEND OFF
NOT VOLATILE ;
ALTER TABLE "W2"."PPANELDB160501 "
ADD PRIMARY KEY
("CHIPID") ;
GRANT SELECT , INSERT , UPDATE , DELETE ON TABLE "W2"."PPANELDB160501 " TO USER "WC1USR";
GRANT SELECT , INSERT , UPDATE , DELETE ON TABLE "W2"."PPANELDB160501 " TO USER "WC2USR";
8、import数据至新表
WSRCELDB:istclppt:/test/20160415 >more imp.sh
#/bin/sh
db2 connect to wcelpptp
db2 "import from /test/20160415/expppanedb.ixf of ixf commitcount 10000 messages /test/20160415/imp.log replace into w2.PPANELDB160501"
nohup ./imp.sh&
导入过程中实时查看archivelog空间使用率，如满了则进行tsm归档
WSRCELDB:root:/test/20160415 >df -g |grep istclppt
/dev/istclppt 0.75 0.04 95% 679 7% /istclppt
/dev/cldbarclog 5.00 4.22 16% 50 1% /istclppt/arclog
/dev/cldb 0.25 0.25 1% 8 1% /istclppt/cldb
/dev/cldbactlog 3.12 2.46 22% 43 1% /istclppt/cldb/actlog
/dev/cptbsl 1.50 1.50 1% 4 1% /istclppt/cldb/cptbsl
/dev/cldbmactlog 3.12 2.46 22% 41 1% /istclppt/cldb/mactlog
/dev/cldbtmp 30.00 29.60 2% 13 1% /istclppt/temptbs
WSRCELDB:istclppt:/istclppt >~/cfg/arc_log.ksh
IBM Tivoli Storage Manager
Command Line Backup/Archive Client Interface
Client Version 5, Release 4, Level 0.0
Client date/time: 05/01/16 09:49:38
(c) Copyright by IBM Corporation and other(s) 1990, 2007. All Rights Reserved.
Archive function invoked.
Node Name: WSRCELDB
Session established with server WSRTSM01_SERVER1: Windows
Server Version 5, Release 4, Level 0.0
Server date/time: 05/01/16 09:49:45 Last access: 05/01/16 09:45:51
Normal File--> 20,488,192 /istclppt/arclog/WCELPPTP/NODE0000/S0409339.LOG [Sent]
Successful deletion of archived file /istclppt/arclog/WCELPPTP/NODE0000/S0409339.LOG
Normal File--> 20,488,192 /istclppt/arclog/WCELPPTP/NODE0000/S0409340.LOG [Sent]
Successful deletion of archived file /istclppt/arclog/WCELPPTP/NODE0000/S0409340.LOG
Normal File--> 20,488,192 /istclppt/arclog/WCELPPTP/NODE0000/S0409341.LOG [Sent]
Successful deletion of archived file /istclppt/arclog/WCELPPTP/NODE0000/S0409341.LOG
Normal File--> 20,488,192 /istclppt/arclog/WCELPPTP/NODE0000/S0409342.LOG [Sent]
Successful deletion of archived file /istclppt/arclog/WCELPPTP/NODE0000/S0409342.LOG
Normal File--> 20,488,192 /istclppt/arclog/WCELPPTP/NODE0000/S0409343.LOG [Sent]
Successful deletion of archived file /istclppt/arclog/WCELPPTP/NODE0000/S0409343.LOG
Normal File--> 20,488,192 /istclppt/arclog/WCELPPTP/NODE0000/S0409344.LOG [Sent]
Successful deletion of archived file /istclppt/arclog/WCELPPTP/NODE0000/S0409344.LOG
Normal File--> 20,488,192 /istclppt/arclog/WCELPPTP/NODE0000/S0409345.LOG [Sent]
q v
导入完成
SQL3110N The utility has completed processing. "5381266" rows were read from
the input file.
SQL3221W ...Begin COMMIT WORK. Input Record Count = "5381266".
SQL3222W ...COMMIT of any database changes was successful.
SQL3149N "5381266" rows were processed from the input file. "5381266" rows
were successfully inserted into the table. "0" rows were rejected.
9、新表创建index
SET SCHEMA = 'ISTCLPPT' ;
CREATE INDEX "W2"."160501_INDEX1"
ON "W2"."PPANELDB160501"
( "CASETID" ASC )
DISALLOW REVERSE SCANS ;
......
10、删除view，rename原表，rename index
DROP VIEW W2.VPPANELDB;
DROP VIEW W2.VPPANELDB2;
RENAME TABLE W2.PPANELDB TO PPANELDBOLD;
rename indexes
RENAME INDEX W2.PPANELDB_INDEX1 TO OLD_INDEX1;
......
11、新表rename，index rename
RENAME TABLE W2.PPANELDB160501 TO PPANELDB;
rename indexes
RENAME INDEX "W2"."160501_INDEX1" TO PPANELDB_INDEX1 ;
......
12、重建view的DDL
SET SCHEMA = W2;
SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","WC1USR" ;
CREATE VIEW W2.VPPANELDB AS
SELECT ...) ;
SET SCHEMA = 'ISTCLPPT' ;
GRANT CONTROL ON TABLE "W2"."VPPANELDB" TO USER "ISTCLPPT" ;
GRANT SELECT , INSERT , UPDATE , DELETE ON TABLE "W2"."VPPANELDB" TO USER "ISTCLPPT" WITH GRANT OPTION ;
SET SCHEMA = ISTCLPPT;
SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","ISTCLPPT" ;
create view w2.vppaneldb2 as
select a.lt_outboxid, b.DPNGRP_ID, b.dpartno, dec(a.pnl_chpbod) pnl_chpbod
from w2.ppaneldb a, w2.ppngrp_dpn_ary b, w2.ppngrp c
where c.MASK_FLG = 'Y' and b.DPNGRP_ID = c.DPNGRP_ID and b.dpartno = a.stb_partno;
SET SCHEMA = 'ISTCLPPT' ;
13、runstat新表
db2 runstats on table w2.PPANELDB and indexes all
WSRCELDB:istclppt:/istclppt >db2 "reorgchk update statistics on table w2.PPANELDB"
Doing RUNSTATS ....
Table statistics:
F1: 100 * OVERFLOW / CARD < 5
F2: 100 * (Effective Space Utilization of Data Pages) > 70
F3: 100 * (Required Pages / Total Pages) > 80
SCHEMA NAME CARD OV NP FP ACTBLK TSIZE F1 F2 F3 REORG
----------------------------------------------------------------------------------------
W2 PPANELDB 5381266 0 5e+06 5e+06 - 2.27e+10 0 51 100 -*-
----------------------------------------------------------------------------------------
Index statistics:
F4: CLUSTERRATIO or normalized CLUSTERFACTOR > 80
F5: 100 * (KEYS * (ISIZE + 9) + (CARD - KEYS) * 5) / ((NLEAF - NUM EMPTY LEAFS) * INDEXPAGESIZE) > 50
F6: (100 - PCTFREE) * ((INDEXPAGESIZE - 96) / (ISIZE + 12)) ** (NLEVELS - 2) * (INDEXPAGESIZE - 96) / (KEYS * (ISIZE + 9) + (CARD - KEYS) * 5) < 100
F7: 100 * (NUMRIDS DELETED / (NUMRIDS DELETED + CARD)) < 20
F8: 100 * (NUM EMPTY LEAFS / NLEAF) < 20
SCHEMA NAME CARD LEAF ELEAF LVLS ISIZE NDEL KEYS F4 F5 F6 F7 F8 REORG
-------------------------------------------------------------------------------------------------
Table: W2.PPANELDB
SYSIBM SQL160501093122750 5e+06 24215 0 3 14 0 5e+06 100 62 1 0 0 -----
W2 PPANELDB_INDEX1 5e+06 4228 0 3 7 0 13 100 77 11 0 0 -----
W2 PPANELDB_INDEX10 5e+06 4119 0 3 15 0 30870 100 81 7 0 0 -----
W2 PPANELDB_INDEX11 5e+06 5853 0 3 15 0 711172 100 84 5 0 0 -----
W2 PPANELDB_INDEX12 5e+06 4119 0 3 11 0 3750 100 79 9 0 0 -----
W2 PPANELDB_INDEX13 5e+06 3971 0 3 21 0 625 100 82 6 0 0 -----
W2 PPANELDB_INDEX14 5e+06 4111 0 3 11 0 2 100 79 9 0 0 -----
W2 PPANELDB_INDEX15 5e+06 4111 0 3 11 0 2 100 79 9 0 0 -----
W2 PPANELDB_INDEX16 5e+06 4102 0 3 13 0 12438 100 80 8 0 0 -----
W2 PPANELDB_INDEX17 5e+06 4101 0 3 13 0 11809 100 80 8 0 0 -----
W2 PPANELDB_INDEX18 5e+06 4082 0 3 13 0 3604 100 80 8 0 0 -----
W2 PPANELDB_INDEX19 5e+06 4112 0 3 11 0 270 100 79 9 0 0 -----
W2 PPANELDB_INDEX2 5e+06 4316 0 3 5 0 8 100 76 12 0 0 -----
W2 PPANELDB_INDEX20 5e+06 4228 0 3 7 0 16 100 77 11 0 0 -----
W2 PPANELDB_INDEX21 5e+06 4079 0 3 13 0 1974 100 80 8 0 0 -----
W2 PPANELDB_INDEX22 5e+06 4164 0 3 9 0 741 100 78 10 0 0 -----
W2 PPANELDB_INDEX23 5e+06 48857 0 4 106 0 3e+06 100 88 9 0 0 -----
W2 PPANELDB_INDEX24 5e+06 3920 0 3 31 0 2998 100 84 5 0 0 -----
W2 PPANELDB_INDEX25 5e+06 9984 0 3 11 0 3e+06 100 87 3 0 0 -----
W2 PPANELDB_INDEX26 5e+06 4210 0 3 13 0 59807 100 80 8 0 0 -----
W2 PPANELDB_INDEX3 5e+06 4519 0 3 2 0 1 100 72 15 0 0 -----
W2 PPANELDB_INDEX4 5e+06 4228 0 3 7 0 65 100 77 11 0 0 -----
W2 PPANELDB_INDEX5 5e+06 4090 0 3 12 0 2 100 80 9 0 0 -----
W2 PPANELDB_INDEX6 5e+06 4316 0 3 5 0 2 100 76 12 0 0 -----
W2 PPANELDB_INDEX7 5e+06 4228 0 3 7 0 69 100 77 11 0 0 -----
W2 PPANELDB_INDEX8 5e+06 4115 0 3 11 0 1771 100 79 9 0 0 -----
W2 PPANELDB_INDEX9 5e+06 4228 0 3 7 0 2 100 77 11 0 0 -----
-------------------------------------------------------------------------------------------------
CLUSTERRATIO or normalized CLUSTERFACTOR (F4) will indicate REORG is necessary
for indexes that are not in the same sequence as the base table. When multiple
indexes are defined on a table, one or more indexes may be flagged as needing
REORG. Specify the most important index for REORG sequencing.
Tables defined using the ORGANIZE BY clause and the corresponding dimension
indexes have a '*' suffix to their names. The cardinality of a dimension index
is equal to the Active blocks statistic of the table.
