DB2数据库有时会出现意外,导致表、表空间甚至整个数据库都不可访问,这时候如果没有备份、也没有IBM的支持,可以使用db2dart工具来抢救数据,这也是最后的办法了。
分两种情况来讨论,第一种:数据库可以连接,仅仅表无法访问,比如访问的时候遇到 SQL1477N,或者坏页;第二种:数据库无法连接。
1.) 数据库可以连接
如果数据库可以连接,那么可以先根据syscat.tables这个视图查到该表对应的table ID和表空间ID,然后使用db2dart的/DDEL选项导出,以EMPLOYEE表为例:
$ db2 "select tableid, tbspaceid, substr(tbspace,1,30) as tbspace from syscat.tables where tabschema='E97Q6C' and tabname='EMPLOYEE'"
TABLEID TBSPACEID TBSPACE
------- --------- ------------------------------
6 2 USERSPACE1
1 record(s) selected.
/**停库操作略**/
$ db2dart SAMPLE /DDEL
Table object data formatting start.
Please enter
Table ID or name, tablespace ID, first page, num of pages:
6,2,0,999999999 <--这里的四项分别输入Table ID、 tablespace ID、 起始页、要导出的页数(一般选一个比较大的数字以确保所有的页都能被导出来)
1 of 1 columns in the table will be dumped.
Column numbers and datatypes of the columns dumped:
0 INTEGER
Default filename for output data file is TS2T6.DEL,
do you wish to change filename used? y/n
N
Filename used for output data file is TS2T6.DEL. If existing file, data will be appended to it.
Formatted data being dumped ...
Dumping Page 0 ....
Dumping Page 1 ....
..
Dumping Page 122 ....
Table object data formatting end.
The requested DB2DART processing has completed successfully!
Complete DB2DART report found in:
/home/db2users/e97q6c/sqllib/db2dump/DART0000/SAMPLE.RPT
完成之后,可以在/home/db2users/e97q6c/sqllib/db2dump/DART0000/目录里找到TS2T6.DEL,便是对应的数据。
2.) 数据库无法连接
由于无法连库,则需要把所有的表(或者您认为比较重要的表)使用db2dart导出来。并且因为无法查询,无法直接得知库中有哪些表、这些表对应的table ID和tablespace ID是什么。这时候可以先把 SYSIBM.SYSTABLES这个系统表导出来,方法如下:
$ db2dart SAMPLE /DDEL
Table object data formatting start.
Please enter
Table ID or name, tablespace ID, first page, num of pages:
(may suffix page number with 'p' for pool relative if working with a pool-relative tablespace)
5,0,0,9999999999999
67 of 75 columns in the table will be dumped.
Column numbers and datatypes of the columns dumped:
0 VARCHAR() -VARIABLE LENGTH CHARACTER STRING
1 VARCHAR() -VARIABLE LENGTH CHARACTER STRING
2 CHAR() -FIXED LENGTH CHARACTER STRING
3 TIMESTAMP
4 VARCHAR() -VARIABLE LENGTH CHARACTER STRING
7 SMALLINT
8 SMALLINT
9 SMALLINT
10 BIGINT
11 BIGINT
12 BIGINT
13 BIGINT
14 SMALLINT
15 SMALLINT
16 SMALLINT
17 SMALLINT
18 SMALLINT
20 VARCHAR() -VARIABLE LENGTH CHARACTER STRING
21 VARCHAR() -VARIABLE LENGTH CHARACTER STRING
22 VARCHAR() -VARIABLE LENGTH CHARACTER STRING
23 VARCHAR() -VARIABLE LENGTH CHARACTER STRING
24 VARCHAR() -VARIABLE LENGTH CHARACTER STRING
25 SMALLINT
26 SMALLINT
28 TIMESTAMP
29 VARCHAR() -VARIABLE LENGTH CHARACTER STRING
31 CHAR() -FIXED LENGTH CHARACTER STRING
32 CHAR() -FIXED LENGTH CHARACTER STRING
33 CHAR() -FIXED LENGTH CHARACTER STRING
34 SMALLINT
35 CHAR() -FIXED LENGTH CHARACTER STRING
36 SMALLINT
37 VARCHAR() -VARIABLE LENGTH CHARACTER STRING
38 VARCHAR() -VARIABLE LENGTH CHARACTER STRING
39 CHAR() -FIXED LENGTH CHARACTER STRING
40 CHAR() -FIXED LENGTH CHARACTER STRING
41 CHAR() -FIXED LENGTH CHARACTER STRING
42 TIMESTAMP
43 CHAR() -FIXED LENGTH CHARACTER STRING
44 CHAR() -FIXED LENGTH CHARACTER STRING
46 CHAR() -FIXED LENGTH CHARACTER STRING
48 CHAR() -FIXED LENGTH CHARACTER STRING
49 CHAR() -FIXED LENGTH CHARACTER STRING
50 VARCHAR() -VARIABLE LENGTH CHARACTER STRING
52 CHAR() -FIXED LENGTH CHARACTER STRING
53 CHAR() -FIXED LENGTH CHARACTER STRING
54 BIGINT
55 SMALLINT
56 SMALLINT
57 REAL -SINGLE PRECISION FLOATING-POINT
58 SMALLINT
59 REAL -SINGLE PRECISION FLOATING-POINT
60 SMALLINT
61 SMALLINT
62 TIMESTAMP
63 INTEGER
64 CHAR() -FIXED LENGTH CHARACTER STRING
65 TIMESTAMP
66 CHAR() -FIXED LENGTH CHARACTER STRING
67 TIMESTAMP
68 INTEGER
69 CHAR() -FIXED LENGTH CHARACTER STRING
70 CHAR() -FIXED LENGTH CHARACTER STRING
71 CHAR() -FIXED LENGTH CHARACTER STRING
72 CHAR() -FIXED LENGTH CHARACTER STRING
73 CHAR() -FIXED LENGTH CHARACTER STRING
74 DATE
Column numbers of columns not dumped:
5
6
19
27
30
45
47
51
Warning: Some columns within the specified table cannot be processed by DB2DART,
they will be skipped and not included in the delimited ASCII dumped data.
Default filename for output data file is TS0T5.DEL,
do you wish to change filename used? y/n
n
Filename used for output data file is TS0T5.DEL. If existing file, data will be appended to it.
Formatted data being dumped ...
Dumping Page 0 ....
Dumping Page 1 ....
Dumping Page 2 ....
Dumping Page 3 ....
Dumping Page 4 ....
Dumping Page 5 ....
Dumping Page 6 ....
Dumping Page 7 ....
Dumping Page 8 ....
Dumping Page 9 ....
Dumping Page 10 ....
Dumping Page 11 ....
Dumping Page 12 ....
Dumping Page 13 ....
Dumping Page 14 ....
Dumping Page 15 ....
Dumping Page 16 ....
Dumping Page 17 ....
Dumping Page 18 ....
Dumping Page 19 ....
Dumping Page 20 ....
Dumping Page 21 ....
Dumping Page 22 ....
Dumping Page 23 ....
Dumping Page 24 ....
Dumping Page 25 ....
Dumping Page 26 ....
Dumping Page 27 ....
Dumping Page 28 ....
Dumping Page 29 ....
Table object data formatting end.
DB2DART Processing completed with warning(s)!
Complete DB2DART report found in:
/home/db2users/e97q9a/sqllib/db2dump/DART0000/SAMPLE.RPT
导出的文件 TS0T5.DEL中,第1、2、7、8、20列(逗号为分割线)分别表示 table name, schema name, table ID, tablespace ID, tablespace name,有了这个对应关系之后,就可以使用db2dart挨个导出每个表了。
说明1:db2dart仅能导出以下字段:SMALLINT, FLOAT, REAL,INTEGER,TIME,DECIMAL,CHAR(),VARCHAR(),DATE,TIMESTAMP,BIGINT。 其他的,比如LOB字段就会被跳过。
说明2:分区表的table ID和 tablespace ID和普通表不一样,不能直接导出
说明3:db2dart导出的过程中,需要有交互输入,不适合写成脚本批量导出。 如果想要避免交互,可以参考 链接
说明4:db2dart运行之前,要求数据库处于离线状态
参考资料:
db2dart命令