DB2恢复数据之终极工具：db2dart

当数据库出现问题无法启动的时候，由于无法连库，则需要把所有的表（或者您认为比较重要的表）使用db2dart导出来。并且因为无法查询，无法直接得知库中有哪些表、这些表对应的table ID和tablespace ID是什么。

前提说明:

没有数据库备份，但是需要有平时的db2look备份，包括创建表空间/表/bufferpool等的语句(如果没有，恢复会非常困难)。

1. 先把 syscat.datapartitions这个系统表导出来，方法如下：

因为用来存放系统表的表空间SYSCATSPACE的表空间ID是0，可以先把表空间ID是0的表都dart出来:

TSID=0
objid=1

export DB2NODE=0
db2 terminate

while (($objid < 50))
do
  echo "TBS ID: $TSID"
  echo "obj ID: $objid"
  db2dart sample /DDEL /RPT /home/db2inst1/test/RPT_DATA /RPTN $TSID.$objid.RPT <<EOF
  $objid,$TSID,0,0      
   y
  $TSID.$objid.del
EOF

  let objid=$objid+1
done

说明:

1. TSID表示表空间ID， objid表示object id即表id

 2.  $objid,$TSID,0,0 分别表示: Table ID or name, tablespace ID, first page, num of pages (最后一个0在这里表示所有page)

输出结果类似:

FYI: An active connection to the database has been detected.
     False errors may be reported.  
     Deactivate all connections and re-run to verify.


   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)

   4 of 5 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  VARCHAR() -VARIABLE LENGTH CHARACTER STRING
         3  VARCHAR() -VARIABLE LENGTH CHARACTER STRING
   Column numbers of columns not dumped:
         4
   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 TS0T48.DEL, 
do you wish to change filename used? y/n

   Please enter filename for output data file (maximum characters 8 for name and 3 for extension):

   Filename used for output data file is 0.48.del.  If existing file, data will be appended to it.

到此，所有SYSCATSPACE表空间的系统表都dart出来了。那么如何找到syscat.datapartitions呢，这里有一个小技巧:

可以在导出的del文件中搜索PART0，例如:

[db2inst1@test DART0000]$ grep PART0 *.del
0.10.del:"PART0","SYSIBM  ","SYSTABLES",0,0,5,0,"F","",0,"Y","","Y","",0,443,2,29,29,0,0,502,0.00000E+00,0,0,0.00000E+00,"2019-05-09-14.53.11.080398",00010101
0.10.del:"PART0","SYSIBM  ","SYSCOLUMNS",0,0,6,0,"F","",0,"Y","","Y","",0,5808,33,159,160,0,0,219,0.00000E+00,0,0,0.00000E+00,"2019-05-09-14.53.11.785178",00010101
0.10.del:"PART0","SYSIBM  ","SYSINDEXES",0,0,7,0,"F","",0,"Y","","Y","",0,396,0,20,20,0,0,391,0.00000E+00,0,0,0.00000E+00,"2019-05-09-14.53.10.578194",00010101
0.10.del:"PART0","SYSIBM  ","SYSVIEWS",0,0,11,0,"F","",0,"Y","","Y","",0,-1,-1,-1,-1,-1,0,-1,-1.00000E+00,-1,-1,-1.00000E+00,,00010101
0.10.del:"PART0","SYSIBM  ","SYSVIEWDEP",0,0,12,0,"F","",0,"Y","","Y","",0,-1,-1,-1,-1,-1,0,-1,-1.00000E+00,-1,-1,-1.00000E+00,,00010101
0.10.del:"PART0","SYSIBM  ","SYSPLAN",0,0,13,0,"F","",0,"Y","","Y","",0,333,0,22,22,0,0,507,0.00000E+00,0,0,0.00000E+00,"2019-05-09-14.53.10.774334",00010101
0.10.del:"PART0","SYSIBM  ","SYSPLANDEP",0,0,14,0,"F","",0,"Y","","Y","",0,-1,-1,-1,-1,-1,0,-1,-1.00000E+00,-1,-1,-1.00000E+00,,00010101
0.10.del:"PART0","SYSIBM  ","SYSSECTION",0,0,15,0,"F","",0,"Y","","Y","",0,-1,-1,-1,-1,-1,0,-1,-1.00000E+00,-1,-1,-1.00000E+00,,00010101
0.10.del:"PART0","SYSIBM  ","SYSSTMT",0,0,16,0,"F","",0,"Y","","Y","",0,-1,-1,-1,-1,-1,0,-1,-1.00000E+00,-1,-1,-1.00000E+00,,00010101

这里就可以确定0.10.del文件是表syscat.datapartitions的数据文件。把该文件内容取出，放到一个脚本里(如TS0T10)。也可以建一个临时库，临时表，把这个文件导入到临时表中:

db2 create table TEST.MY_DATAPARTS  like syscat.datapartitions  然后把0.10.del导入到这个临时表中。

================================================================

接着就可以开始恢复每一个表空间下的表。

1. 首先，先找到该库下所有表的tabid的最大值，可以在0.10.del中找列PARTITIONOBJECTID的最大值，也可以在创建的临时表中找:select max(PARTITIONOBJECTID) from TEST.MY_DATAPARTS;  --比如是1200

这里需要知道，对于非分区表，表syscat.datapartitions中的PARTITIONOBJECTID字段即为表的tabid； 多分区表的SYSCAT.TABLES 的TABLEID<0; 单个node内每个表(非临时表dgtt)的PARTITIONOBJECTID不重复。可以通过该SQL验证:

SELECT * FROM SYSCAT.DATAPARTITIONS a, SYSCAT.TABLES b WHERE a.TABSCHEMA=b.TABSCHEMA and a.TABNAME=b.TABNAME and (b.TYPE<>'G') 
 AND a.PARTITIONOBJECTID<>b.TABLEID WITH ur; --0条

2. 做db2dart导出文本文件数据。 根据node(DPF环境)和表空间来导。举例，DPF环境共4个node0--3，每个node下有多个表空间(可能有点表空间跨所有node，有的只在个别node下)，如1,3,4,5,6:

  2.1)比如先在node0下执行，先执行node0的tbspid 1的表，脚本如:

TSID=0
objid=1

export DB2NODE=0
db2 terminate

while (($objid < 12000))        #1200:the max value of all tables' PARTITIONOBJECTID
do
  echo "TBS ID: $TSID"
  echo "obj ID: $objid"
  db2dart sample /DDEL /RPT /home/db2inst1/test/RPT_DATA /RPTN $TSID.$objid.RPT <<EOF
  $objid,$TSID,0,0      
   y
  $TSID.$objid.del
EOF

  let objid=$objid+1
done

这里: /DDEL /RPT /home/db2inst1/test/RPT_DATA表示del文件和rpt文件都放到这个路径下，rpt文件用于记录导出数据时的信息相当于日志。/RPTN $TSID.$objid.RPT是指定rpt文件的名字。y表示需要修改导出数据文件的名字，改为$TSID.$objid.del

2.2）导出node0 的表空间1下的所有表之后，在导node0下的2,3.....。 node0 结束后然后再开始导node1,2,3依次类推，每个node下的del一定要放在不同的路径下。

注意事项:

(1)如果需要重复导，比如导出失败了，要重新db2dart导出，需要把该表的RPT文件删掉，不然会报错(already exist....),del文件如果没成功最好也删了。

(2)通过这种简单的形式(即从1--12000)来导出，有可能会遇到有的tabid不存在，这时候del文件大小为0，rpt文件里会显示错误信息。最好在脚本里加个判断，根据系统表来判断下，只dart存在的表。

(3)临时表空间的数据无法导出。

 

==========================================

del文件都dart出来之后，需要根据相应的表名字加载到表中。需要新建个临时库/中间库，根据之前导出的syscat.datapartitions的数据写一个脚本把他们对应起来，然后load到相应的表中:

#/bin/ksh

###################################################
##
## TS0T10 --> sysibm.sysdatapartitions
## 0--> tablespace id,   10--> table id
###################################################

###### tablespace id  #########
USERSPACE1=2
WEBTBS1=4
SALLTBS=6


while read line 
do
    tbs_id=` echo $line |awk  -F"," '{print $5}'  `
    if [ $tbs_id -eq "2" ]   ; then
      echo $tbs_id
      echo  $line  | sed  's/"//g'   |awk -F","  '{ print "load client from 2." $6 ".DEL  of del insert into " $2"."$3  " nonrecoverable  ; "  }'   >> load_USERSPACE1.sql
    fi
    
    
    if [ $tbs_id -eq "4" ]   ; then
      echo $tbs_id
      echo  $line  | sed  's/"//g'   |awk -F","  '{ print "load client from 4." $6 ".DEL  of del insert into " $2"."$3  " nonrecoverable  ; "  }'   >> load_WEBTBS1.sql
    fi
    
    
    if [ $tbs_id -eq "6" ]   ; then
      echo $tbs_id
      echo  $line  | sed  's/"//g'   |awk -F","  '{ print "load client from 6." $6 ".DEL  of del insert into " $2"."$3  " nonrecoverable  ; "  }'   >> load_SALLTBS.sql
    fi
    
   
done < TS0T10.DEL  

每个node上都要执行，因为有些表空间是跨多个node的。