抢救DB2数据之终极工具：db2dart

DB2数据库有时会出现意外，导致表、表空间甚至整个数据库都不可访问，这时候如果没有备份、也没有IBM的支持，可以使用db2dart工具来抢救数据，这也是最后的办法了。

 如果数据库可以连接，那么可以先根据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/nN   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/nn   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命令