本文僅僅讨論Db2資料庫中，當分區表出現壞頁，并且資料庫能正常連接配接的情況下，如何處理。
由于是分區表，直接從syscat.tables裡查表ID，表空間ID不再适用。在db2diag.log報的obj ID是PARTITIONOBJECTID而非TABLEID，這時候需要查詢SYSCAT.DATAPARTITIONS這個視圖。
在db2diag.log中遇到以下報錯，說明資料頁有壞頁:
2017-12-23-10.54.39.329394+480 I107589A2588 LEVEL: Severe
PID : 133323 TID : 42 PROC : db2sysc 0
INSTANCE: db2inst1 NODE : 000
EDUID : 42 EDUNAME: db2pfchr (SAMPLE) 0
FUNCTION: DB2 UDB, buffer pool services, sqlbVerifyCBITS, probe:1110
MESSAGE : ZRC=0x86020019=-2046689255=SQLB_CSUM "Bad Page, Checksum Error"
DIA8426C A invalid page checksum was found for page "".
DATA #1 : String, 64 bytes
Error encountered trying to read a page - information follows :
DATA #2 : String, 95 bytes
CBIT verification error
bitExpected is 1, userByte is 0, sector 24 (from head of page, 0 based)
DATA #3 : Page ID, PD_TYPE_SQLB_PAGE_ID, 4 bytes
59785978
DATA #4 : Object descriptor, PD_TYPE_SQLB_OBJECT_DESC, 72 bytes
Obj: {pool:6;obj:4;type:0} Parent={6;4}
下面是一個完整的例子：
1. 建立分區表
db2=> connect to sample
db2=> create tablespace tbs1 managed by automatic storage
db2=> create tablespace tbs2 managed by automatic storage
db2=> create tablespace tbs3 managed by automatic storage
db2=> create table t1(birthdate date) partition by range(birthdate)(partition post80 starting '1980-01-01' ending '1989-12-31' in tbs1,partition post90 starting '1990-01-01' ending '1999-12-31' in tbs2, partition post00 starting '2000-01-01' ending '2009-12-31' in tbs3)
db2=> insert into t1 values('2003-01-01')
db2=> insert into t1 values('1989-02-02')
db2=> insert into t1 values('1998-02-28')
db2=> insert into t1 values('2007-12-31')
2. 根據pool ID和obj ID找到對應的表和分區
假設db2diag.log中報的錯為 Obj: {pool:6;obj:4;type:0} Parent={6;4}
直接查詢 syscat.tables 沒有結果，說明是個分區表：
[email protected]:~> db2 "select substr(tabname, 1, 10) as tabname from syscat.tables where TBSPACEID=6 AND TABLEID=4"
TABNAME
----------
0 record(s) selected.
這時候需要把 syscat.tables 換成 SYSCAT.DATAPARTITIONS, 把TABLEID換成 PARTITIONOBJECTID 來查詢：
[email protected]:~> db2 "select substr(tabname, 1, 4) as tabname, substr(DATAPARTITIONNAME,1,10) as DATAPARTITIONNAME from SYSCAT.DATAPARTITIONS where TBSPACEID=6 and PARTITIONOBJECTID=4"
TABNAME DATAPARTITIONNAME
------- -----------------
T1 POST80 <----可以看到，是表T1有壞頁，分區為POST80
1 record(s) selected.
[email protected]:~> db2 "SELECT TBSPACEID, PARTITIONOBJECTID , DATAPARTITIONID, substr(DATAPARTITIONNAME,1,10) as DATAPARTITIONNAME from SYSCAT.DATAPARTITIONS where tabname='T1'"
TBSPACEID PARTITIONOBJECTID DATAPARTITIONID DATAPARTITIONNAME
----------- ----------------- --------------- -----------------
6 4 0 POST80
7 4 1 POST90
8 4 2 POST00
3 record(s) selected.
3. 使用db2dart DDEL導出對應分區的資料：
[email protected]:~$ db2 terminate
DB20000I The TERMINATE command completed successfully.
[email protected]:~> db2dart sample /DDEL
Table object data formatting start.
Please enter
Table ID or name, tablespace ID, first page or logical row, num of pages or logical rows:
(may suffix page number with 'p' for pool relative if working with a pool-relative tablespace)
4,6,0,9999999999999 <--這裡的4指PARTITIONOBJECTID， 6指TBSPACEID
1 of 1 columns in the table will be dumped.
Column numbers and datatypes of the columns dumped:
0 DATE
Default filename for output data file is SAMPLE_TS6T4.DEL,
do you wish to change filename used? y/n
n
Filename used for output data file is SAMPLE_TS6T4.DEL. If the file exists, the data will be appended to it.
Formatted data being dumped ...
Dumping Page 0 ....
Table object data formatting end.
The requested DB2DART processing has completed successfully!
Complete DB2DART report found in: SAMPLE.RPT
[email protected]:~> cat SAMPLE_TS6T4.DEL
19890202
[email protected]:~>
如果是db2 9.7版本的，導出的資料預設是放在db2dump/DARTXXXX目錄下的
4. 使用db2dart MT選項将對應分區标記為無效
[email protected]:~$ db2dart sample /MT
Please enter Table ID or name, tablespace ID, and password:
4,6,XLQIXMNG <--分别輸入表空間ID，PARTITIONOBJECTID和服務密碼（服務密碼有效期7天，需要向IBM申請）
Mark table phase start.
Set drop-pending (unavailable) state for table objects.
Set state for DAT object 4 tablespace 6.
Modification for page (obj rel 0, pool rel 128) of pool ID (6) obj ID (4), written out to disk successfully.
Modification written out successfully.
Mark table phase end.
The requested DB2DART processing has completed successfully!
Complete DB2DART report found in: SAMPLE.RPT
[email protected]:~>
5. detach并删除對應分區
[email protected]:~$ db2 connect to sample
[email protected]:~$ db2 "select * from t1"
BIRTHDATE
----------
SQL1477N For table "INST105.T1" an object "4" in table space "6" cannot be
accessed. SQLSTATE=55019
[email protected]:~$ db2 "alter table t1 DETACH PARTITION POST80 into temp" <--把這個分區detach出去之後，表T1即可恢複通路
DB20000I The SQL command completed successfully.
[email protected]:~$ db2 "select * from t1"
BIRTHDATE
----------
02/28/1998
01/01/2003
12/31/2007
3 record(s) selected.
[email protected]:~> db2 "drop table temp"
DB20000I The SQL command completed successfully.
6. 根據第三步中導出的資料，可以建立一個表，導入資料之後，再attach到t1上
![【複習】SQL 内，外，左，右連接配接[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)