DB2中表损坏问题和db2dart工具的使用
DB2中表损坏问题和db2dart工具的使用
这几天需要从一个备份集中恢复一个数据库，恢复后发现问题很多，本身这个备份中可能有存在不完整的log，处理完一个个问题后发现还是有表损坏，在db2diag中其日志信息如下，倒是很清楚的看到损坏对象：DB2数据库坏块代码
DB2数据库性能调整和优化(第1、2版) PDF
DB2数据库性能优化介绍
<code type="section" width="100%">
<heading refname=Listing 2" type="code">常规表的DDL语句示例</heading>
2014-04-27-05.06.42.071142-240 I36137A535 LEVEL: Severe
PID : 14680376 TID : 13881 PROC : db2sysc 0
INSTANCE: db2rilo NODE : 000 DB : WEB
APPHDL : 0-40 APPID: 9.32.130.62.37608.140427090600
AUTHID : DEVPRCBK
EDUID : 13881 EDUNAME: db2agent (WEB) 0
FUNCTION: DB2 UDB, data management, sqldFetchDirect, probe:4603
RETCODE : ZRC=0x87040001=-2029780991=SQLD_BADPAGE "Bad Data Page"
DIA8500C A data file error has occurred, record id is "".
2014-04-27-05.06.42.071901-240 I36673A555 LEVEL: Severe
PID : 14680376 TID : 13881 PROC : db2sysc 0
INSTANCE: db2rilo NODE : 000 DB : WEB
APPHDL : 0-40 APPID: 9.32.130.62.37608.140427090600
AUTHID : DEVPRCBK
EDUID : 13881 EDUNAME: db2agent (WEB) 0
FUNCTION: DB2 UDB, trace services, sqlt_logerr_string (secondary logging fu, probe:0
MESSAGE : TABLESPACE ATTRIBUTES:
DATA #1 : String, 73 bytes
Tablespace Seed = 5, Bufferpool ID = 7, Extent Size = 16, Page Size = 8k
2014-04-27-05.06.42.072336-240 I37229A535 LEVEL: Severe
PID : 14680376 TID : 13881 PROC : db2sysc 0
INSTANCE: db2rilo NODE : 000 DB : WEB
APPHDL : 0-40 APPID: 9.32.130.62.37608.140427090600
AUTHID : DEVPRCBK
EDUID : 13881 EDUNAME: db2agent (WEB) 0
FUNCTION: DB2 UDB, trace services, sqlt_logerr_string (secondary logging fu, probe:0
MESSAGE : PAGE OBJECT IDENTIFIERS:
DATA #1 : String, 51 bytes
Tablespace ID = 5, Object ID = 72, Object Type = 0
2014-04-27-05.06.42.072612-240 I37765A511 LEVEL: Severe
PID : 14680376 TID : 13881 PROC : db2sysc 0
INSTANCE: db2rilo NODE : 000 DB : WEB
APPHDL : 0-40 APPID: 9.32.130.62.37608.140427090600
AUTHID : DEVPRCBK
EDUID : 13881 EDUNAME: db2agent (WEB) 0
FUNCTION: DB2 UDB, trace services, sqlt_logerr_string (secondary logging fu, probe:0
MESSAGE : PAGE NUMBERS:
DATA #1 : String, 38 bytes
Obj Page = 15430, Pool Page = 1061398
2014-04-27-05.06.42.072878-240 I38277A485 LEVEL: Severe
PID : 14680376 TID : 13881 PROC : db2sysc 0
INSTANCE: db2rilo NODE : 000 DB : WEB
APPHDL : 0-40 APPID: 9.32.130.62.37608.140427090600
AUTHID : DEVPRCBK
EDUID : 13881 EDUNAME: db2agent (WEB) 0
FUNCTION: DB2 UDB, trace services, sqlt_logerr_string (secondary logging fu, probe:0
MESSAGE : lifeLSN:
DATA #1 : String, 17 bytes
000000003CA9A178
</code>
分析：对于DB2中数据库出现坏块问题，如果没有使用该表时候，并不会影响数据库的正常使用，一旦有访问该表的会话，那么会造成整个数据库实例宕机，从而影响业务的应用，所以问题比较棘手，对于此类问题没有很好的解决，唯独可以通过db2dart离线方式将表中的数据导出，并且将表mark为unvaliable后重建即可，db2dart只开放了部分免费功能，如标记索引，导出数据，表空间高水位线处理及一些查看功能，但是对于标记表失效需要客服提供密码才可以使用。其实根据上面提供的信息已经比较完全了，只需要找出是那张表表即可。
Tablespace ID = 5, Object ID = 72, Object Type = 0
其中tablespace id即是坏块所在表的表空间ID，object id对应出现坏块的对象ID，可以是表索引或LOB，object type代表损坏对象的类型，0表示表，1表示索引，还有其他的LOB之类的吧，不过很少见，上面可见损坏的是表，所以所以根据信息就可以通过syscat.tables查看损坏的表名称：
db2 select tabschema,tabname from syscat.tables where tableid=72 and tbspaceid=5
TABSCHEMA TABNAME
-----------------------------------------------------------------
WWPP2 WWIDE_FNSHD_PART
然后需要停掉实例直接db2stop force即可
db2dart web /MT /tsi 5 /oi 72
Please enter Table ID or name, tablespace ID, and password:
72,5,********（需IBM 800提供密码）
Mark table phase start.
Set drop-pending (unavailable) state for table objects.
Set state for DAT object 1190 tablespace 8.
Modification for page (obj rel 0, pool rel 982568) of pool ID (5) obj ID (72), 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:
/db2_src/db2rilo/sqllib/db2dump/DART0001/web.RPT
这样将表标记为unvaliable了，这时还是可以通过db2look将表结构导出来的，倒出来后直接Drop 掉这个表重建即可。
如果是索引损坏，就可以通过object id和tablespace id在视图syscat.indexes中查到索引的ID号，IID,不同的是标记索引是是个免费功能，参数使用MI，过程大致相当，标记失效后的索引不需要经过drop，根据这个参数设置情况：
Index re-creation time and redo index build (INDEXREC) = SYSTEM (RESTART)
失效的索引会自动重建。
类似的db2dart还有离线导出表中数据的功能，也为免费使用，相对比较简单，通过如下SQL找到相关需要的对象
select rtrim(tabschema)||'.'||tabname,TABLEID||','||TBSPACEID||','||1||','||fpages from syscat.tables where tabschema='EBIZ1' and tabname='TEST'
然后使用ddel即可将怀表中的数据导出格式为DEL的数据文件
db2dart web /DDEL /OI 134 /TSI 6 /PS 1 /NP 100 /V Y /RPT /db2_backup/db2rilo/WEB/db2dart /RPTN WEB_QUOTE
当然整个过程需要交互进行。
至于各个选项代表什么意思，可以通过如下方式查看：
$ db2dart
____________________________________________________________________
_____ D B 2 D A R T _____
Database Analysis Tool
I B M
DB2 6000
The DB2DART Tool is a utility for the analysis of databases,
tablespaces, and tables. DART's primary function is to
examine databases for their architectural correctness, and to
report any encountered errors.
____________________________________________________________________
___________________________________________________________________________
DB2 V9.7 DB2DART HELP
db2dart - Database Analysis Tool
The db2dart command analyses databases, table spaces and tables.
The primary function of this command is to examine databases for
architectural correctness, and to report any encountered errors.
Requirements:
db2dart must be run with no users connected to the database.
Syntax:
db2dart <database name> [action] [options ...]
('db2dart /H' for extended help)
Command parameters:
/H Displays this help.
(press <enter> for more text)
/H
Inspect actions:
/DB (default) Inspects entire database.
/T Inspects one or more tables. (See notes 1, 3, 13)
/TSF Inspects only the table space files and containers.
/TSC Inspects the table space constructs of one or more
table spaces (but does not inspect tables).
/TS Inspects one or more table spaces and their tables.
(/TSC and /TS require a table space ID or a list of table
space IDs to be specified. See notes 1 and 2.)
/ATSC Inspects constructs of all table spaces (but not their tables).
Data format actions:
/DD Dumps formatted table data. (See notes 1, 4, 13, 15)
/DM Dumps formatted block map data. (See notes 1, 4, 13, 15)
/DI Dumps formatted index data. (See notes 1, 4, 12, 15)
/DXA Dumps formatted xda data in ASCII. (See notes 1, 4, 13, 15)
/DXH Dumps formatted xda data in Hex. (See notes 1, 4, 13, 15)
/DP Dumps pages in hex format. (See notes 1, 6, 13)
/DTSF Dumps formatted table space file information.
/DEMP Dumps formatted EMP information for a DMS table.
(See notes 1, 3, 13)
/DDEL Dumps formatted table data in delimited ASCII format.
(See note 13, 15)
/DHWM Dumps high water mark information. (See notes 1, 2, 14)
/LHWM Suggests ways of lowering high water mark.
(See notes 1, 7, 14)
(press <enter> for more text)
/DDEL
Input value options:
/OI object-id Specifies the object ID.
For the /T, a comma-separated list of up to 64
object IDs can be specified. If the corresponding
/TSI parameter contains more than one input ID,
only the first ID is used. Duplicate IDs are
skipped. Logical ID can be specified for the
/T parameter.
/TN table-name Specifies the table name.
/TSI tablespace-id Specifies the table space ID.
For the /TS and /TSC, a comma-separated list of
up to 64 physical table space IDs can be specified.
Duplicate IDs are skipped.
/ROW sum Identifies whether L/F descriptors, LOB descriptors
and control information should be checked.
(1) Checks control information in rows.
(2) Checks long field and LOB descriptors.
(see note 8)
/PS number Specifies the page number to start with.
(When using the /DP action or working with a
pool-relative tablespace, you may suffix
the page number with 'p' for pool relative.)
/NP number Specifies the number of pages.
/V Y/N Specifies whether or not to use verbose option.
(Y) Verbose.
(N) No verbose.
/RPT path The path to place report output file (optional).
/RPTN file-name The name of the report file (optional).
/SCR Y/M/N Specifies the type of screen output, if any.
(Y) Produces normal screen output.
(M) Produces minimal screen output.
(N) Produces no screen output.
/RPTF Y/E/N Specifies the type of report file output, if any.
(Y) Produces normal output.
(E) Sends only error information to report file.
(N) Produces no report file output.
/ERR Y/N/E Specifies the type of log to produce in DART.INF,
if any.
(Y) Produces a normal log in DART.INF file.
(default)
(N) Minimizes output to log DART.INF file.
(E) Minimizes DART.INF file and screen output.
Only error information is logged.
/WHAT DBBP OFF/ON Specifies the database backup pending state.
(OFF) Off state.
(ON) On state.
/QCK [sum] Specifies a quick option.
The default /QCK value is 1, the same as when
you specify /QCK 1. (see note 8)
Valid values are:
(1) Applies only to the /DB, /T, and /TS actions.
Inspects only page 0 of the DAT objects
and partially inspects the index objects
(does not inspect BMP, LOB, LF objects and
does not traverse the entirety of the DAT
or INX objects).
(2) Applies only to the /DB, /T, /TS, /DD, /DI,
/DM, /DEMP, /DDEL, /DXA, and /DXH actions.
Skips system catalog table look up on
non-partitioned database environments and on
the catalog partition of partitioned database
environments. This option has no effect on
non-catalog partitions of partitioned database
environments. Not applies to the applicable
actions above when /TN with a table name is
specified, or if /OI and /TSI with logical IDs
are specified.
(4) Applies only to the /T, /TS, and /TSC actions.
For /TS, /TSC, skips inspection of the special
system catalog tables. For /T, skips inspection
of the system catalog table space constructs.
(8) Applies only to /T and /TS actions.
For /T, skips inspection of all container files.
For /TS, inspects only container files that
are associated with the specified table space.
/TYP Specifies the type of object. Valid values are:
(DAT) Object type is DAT.
(INX) Object type is INDEX.
(BKM) Object type is BMP.
(press <enter> for more text)
本文永久更新链接地址：
评论暂时关闭