CRM表损坏问题处理

一、问题描述：

xx月xx日xxxxDB异常：无法导入数据，db2diag日志：提示有表损坏

20XX-xx-xx-xx.49.04.773235+480 I520988E442 LEVEL: Error

PID : 6177 TID : 140737043752704PROC : db2sysc 0

INSTANCE: db2inst1 NODE : 000

EDUID : 33 EDUNAME: db2pfchr (XXXXDB) 0

FUNCTION: DB2 UDB, buffer pool services, sqlbErrorHandler, probe:0

MESSAGE : ZRC=0x86020019=-2046689255=SQLB_CSUM "Bad Page, Checksum Error" DIA8426C A invalid page checksum was found for page "".

20XX-xx-xx-xx.49.04.773301+480 I521431E439 LEVEL: Error

PID : 6177 TID : 140737043752704PROC : db2sysc 0

INSTANCE: db2inst1 NODE : 000

EDUID : 33 EDUNAME: db2pfchr (XXXXDB) 0

FUNCTION: DB2 UDB, buffer pool services, sqlbErrorHandler, probe:0

DATA #1 : String, 108 bytes

Obj={pool:6;obj:680;type:0} State=x27 Parent={6;680}, EM=69568, PP0=0

Prefetcher Error, in sqlbProcessRange

20XX-xx-xx-xx.49.04.773345+480 I521871E904 LEVEL: Error

PID : 6177 TID : 140737043752704PROC : db2sysc 0

INSTANCE: db2inst1 NODE : 000

EDUID : 33 EDUNAME: db2pfchr (XXXXDB) 0

FUNCTION: DB2 UDB, buffer pool services, sqlbErrorHandler, probe:0

DATA #1 : Object descriptor, PD_TYPE_SQLB_OBJECT_DESC, 88 bytes Obj: {pool:6;obj:680;type:0} Parent={6;680}

lifeLSN: 000000D06E2BA10F

tid: 0 0 0

extentAnchor: 69568

initEmpPages: 0

poolPage0: 0

poolflags: 122

objectState: 27

lastSMP: 0

pageSize: 32768

extentSize: 32

bufferPoolID: 5

partialHash: 44564486

bufferPool: 0x00007ffaccdf9f80

pdef: 0x00007ffad04576a0

提示：

Object descriptor, PD_TYPE_SQLB_OBJECT_DESC, 88 bytes Obj: {pool:6;obj:680;type:0} Parent={6;680}

二、环境信息：

Redhat5.8 db2 v9.7

三、问题分析：

在db2diag.log中遇到以下报错，说明数据页有坏页

FUNCTION: DB2 UDB, buffer pool services, sqlbErrorHandler, probe:0

DATA #1 : Object descriptor, PD_TYPE_SQLB_OBJECT_DESC, 88 bytes Obj: {pool:6;obj:680;type:0} Parent={6;680}

获取有坏页的表，直接从syscat.tables里查表ID

db2 "select char(tabname,20), char(tabschema,20) from

syscat.tables where tableid=680 and tbspaceid=6"

得到表名等信息

TABNAME TABSCHEMA

OCRM_F_CI_ALONG_ORG CRM

----------

1 record(s) selected.

四、解决办法：

使用db2 export工具导出该表的剩余数据。
db2 "export to table.del of del select * from table-name"

改名坏表，重建表OCRM_F_CI_ALONG_ORG，并导入数据，观察几天没有没用删除怀表

五、延伸其他：

db2dart工具介绍

https://www.ibm.com/docs/zh/db2/9.7?topic=commands-db2dart-database-analysis-reporting-tool

举例使用 db2dart /ddel导出表数据

 使用 db2dart /ddel导出数据，删除表，重建，导入数据, 步骤如下：
 $ 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)
6,4,0,999999       --->指定表的tablespace_id, table_id, 起始page(一般是0开始)，总共page数， 四个参数

   3 of 3 columns in the table will be dumped.
   Column numbers and datatypes of the columns dumped:
         0  INTEGER
         1  FLOAT
         2  INTEGER
   Default filename for output data file is TEST.T1.DEL,
do you wish to change filename used? y/n       ----->使用系统指定的文件名
n

   Filename used for output data file is TEST.T1.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     --->导出成功

查看数据
$ more  TEST.T1.DEL