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

最新推荐文章于 2022-12-27 10:31:35 发布

OnTheWay_Seeking

最新推荐文章于 2022-12-27 10:31:35 发布

阅读量314

点赞数

分类专栏：数据库DB2

数据库DB2 专栏收录该内容

112 篇文章 5 订阅

订阅专栏

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运行之前，要求数据库处于离线状态
---------------------
作者：匿_名_用_户
来源：CSDN
原文：https://blog.csdn.net/qingsong3333/article/details/59693113
版权声明：本文为博主原创文章，转载请附上博文链接！