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

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命令