Load作为DB2导入大量数据的工具，在数据迁移的过程中有着很重要的应用。下面对load的操作进行测试。
I、测试load对数据的可恢复性的影响
由于load是直接对数据文件的操作，因此会记录很少的日志。这对数据的可恢复性提出了要求。为了保证数据的可恢复性，load提供了参数选项来保证数据库的可恢复性。
1、COPY NO
这个是默认选项，在此选项下，load操作完毕后，load数据的表所在的表空间将处于backup pending的状态，必须对表空间进行备份之后，表才能够使用。演示如下：
load前的表空间的状态：
db2inst1@baseDB2:/db2home/test_data> db2 list tablespaces
Tablespaces for Current Database
Tablespace ID = 0
Name = SYSCATSPACE
Type = Database managed space
Contents = All permanent data. Regular table space.
State = 0x0000
Detailed explanation:
Normal
Tablespace ID = 1
Name = TEMPSPACE1
Type = System managed space
Contents = System Temporary data
State = 0x0000
Detailed explanation:
Normal
Tablespace ID = 2
Name = USERSPACE1
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0000
Detailed explanation:
Normal
Tablespace ID = 3
Name = SYSTOOLSPACE
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0000
Detailed explanation:
Normal
Tablespace ID = 4
Name = SYSTOOLSTMPSPACE
Type = System managed space
Contents = User Temporary data
State = 0x0000
Detailed explanation:
Normal
进行数据的load操作：
db2inst1@baseDB2:/db2home/test_data> db2 "load from part.tbl of del modified by coldel| replace into tpcd.part"
SQL3109N The utility is beginning to load data from file
"/db2home/test_data/part.tbl".
SQL3500W The utility is beginning the "LOAD" phase at time "02/11/2014
03:18:41.601877".
SQL3519W Begin Load Consistency Point. Input record count = "0".
SQL3520W Load Consistency Point was successful.
SQL3110N The utility has completed processing. "200000" rows were read from
the input file.
SQL3519W Begin Load Consistency Point. Input record count = "200000".
SQL3520W Load Consistency Point was successful.
SQL3515W The utility has finished the "LOAD" phase at time "02/11/2014
03:18:42.290729".
Number of rows read = 200000
Number of rows skipped = 0
Number of rows loaded = 200000
Number of rows rejected = 0
Number of rows deleted = 0
Number of rows committed = 200000
db2inst1@baseDB2:/db2home/test_data> db2set -all
[i] DB2OPTIONS=+c
[g] DB2SYSTEM=baseDB2
[g] DB2INSTDEF=db2inst1
db2inst1@baseDB2:/db2home/test_data> db2 commit
DB20000I The SQL command completed successfully.
load操作成功后，查看表空间的状态：
db2inst1@baseDB2:/db2home/test_data> db2 list tablespaces
Tablespaces for Current Database
Tablespace ID = 0
Name = SYSCATSPACE
Type = Database managed space
Contents = All permanent data. Regular table space.
State = 0x0000
Detailed explanation:
Normal
Tablespace ID = 1
Name = TEMPSPACE1
Type = System managed space
Contents = System Temporary data
State = 0x0000
Detailed explanation:
Normal
Tablespace ID = 2
Name = USERSPACE1
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0020
Detailed explanation:
Backup pending
Tablespace ID = 3
Name = SYSTOOLSPACE
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0000
Detailed explanation:
Normal
Tablespace ID = 4
Name = SYSTOOLSTMPSPACE
Type = System managed space
Contents = User Temporary data
State = 0x0000
Detailed explanation:
Normal
可见表空间已经处于backup pending的状态。此时对此表空间的表的操作会：
db2inst1@baseDB2:/db2home/test_data> db2 "select count(*) from tpcd.part"
1
-----------
200000
1 record(s) selected.
db2inst1@baseDB2:/db2home/test_data> db2 "select count(*) from tpcd.SUPPLIER"
1
-----------
0
1 record(s) selected.
db2inst1@baseDB2:/db2home/test_data> db2 "delete from tocd.part"
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0204N "TOCD.PART" is an undefined name. SQLSTATE=42704
db2inst1@baseDB2:/db2home/test_data> db2 "delete from tpcd.SUPPLIER"
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0290N Table space access is not allowed. SQLSTATE=55039
db2inst1@baseDB2:/db2home/test_data>
可见，在表空间backup pending的状态下，表空间上的表只能被select，而不能进行更新操作；
由于默认的load操作具有以上的特性，因此，在生产系统的工作时间，进行load操作时，一定不能采用此种操作方式，否则，只能对表空间进行备份来解决表空间的backup pending，而在表空间的backup pending状态没有解决的期间，对表空间上的表的更新操作都会很失败。
解决表空间的backup pending：对此表空间进行备份：
db2inst1@baseDB2:/db2home/test_data> cat test_reslove_tablespace_bakcup_pending.sh
sh get_tablespaces_status.sh &
db2 "backup database tpcd tablespace USERSPACE1 online to /dev/null"
processno=` jobs -l | awk '{print $3}'`
kill -9 $processno
db2inst1@baseDB2:/db2home/test_data>
db2inst1@baseDB2:/db2home/test_data> cat get_tablespaces_status.sh
db2 connect to tpcd > /dev/null
while true
do
db2 list tablespaces >> tablespace_status.txt
done
db2inst1@baseDB2:/db2home/test_data>
查看备份时的表空间的状态：
Tablespaces for Current Database
Tablespace ID = 0
Name = SYSCATSPACE
Type = Database managed space
Contents = All permanent data. Regular table space.
State = 0x0000
Detailed explanation:
Normal
Tablespace ID = 1
Name = TEMPSPACE1
Type = System managed space
Contents = System Temporary data
State = 0x0000
Detailed explanation:
Normal
Tablespace ID = 2
Name = USERSPACE1
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0820
Detailed explanation:
Backup pending
Backup in progress
备份成功后：
db2inst1@baseDB2:/db2home/test_data> sh test_reslove_tablespace_bakcup_pending.sh
Backup successful. The timestamp for this backup image is : 20140211042021
test_reslove_tablespace_bakcup_pending.sh: line 4: kill: Running: arguments must be process or job IDs
db2inst1@baseDB2:/db2home/test_data> db2 list tablespaces
Tablespaces for Current Database
Tablespace ID = 0
Name = SYSCATSPACE
Type = Database managed space
Contents = All permanent data. Regular table space.
State = 0x0000
Detailed explanation:
Normal
Tablespace ID = 1
Name = TEMPSPACE1
Type = System managed space
Contents = System Temporary data
State = 0x0000
Detailed explanation:
Normal
Tablespace ID = 2
Name = USERSPACE1
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0000
Detailed explanation:
Normal
此时再对表进行更新操作：
db2inst1@baseDB2:/db2home/test_data> db2 "delete from tpcd.SUPPLIER"
SQL0100W No row was found for FETCH, UPDATE or DELETE; or the result of a
query is an empty table. SQLSTATE=02000
db2inst1@baseDB2:/db2home/test_data> db2 commit
DB20000I The SQL command completed successfully.
db2inst1@baseDB2:/db2home/test_data>
已经可以操作了，表空间的状态正常了。
2、COPY YES
使用此选项，会建立一个导入数据的备份介质，如果需要前滚，会用此介质进行前滚恢复，将load操作对数据库的修改重新放入数据库（因为load操作本身记录的日志比较少，如果没有此导入数据的备份介质，从以前的备份进行数据库恢复时，在使用日志进行前滚到日志末尾时，load操作所做的修改将不会出现在数据库，而这样的话，就相当于损失了数据）。演示如下：
确认导入前数据库中的表的条数：
db2inst1@baseDB2:/db2home/test_data> db2 "select count(*) from tpcd.part"
1
-----------
200000
1 record(s) selected.
对数据库进行在线备份：
db2inst1@baseDB2:/db2home/test_data> db2 backup db tpcd online
Backup successful. The timestamp for this backup image is : 20140211050752
db2inst1@baseDB2:/db2home/test_data>
删除一些数据：
db2inst1@baseDB2:/db2home/test_data> db2 "delete from tpcd.part where P_PARTKEY in (150158,150159,150160,150161,150162)"
DB20000I The SQL command completed successfully.
db2inst1@baseDB2:/db2home/test_data> db2 commit
DB20000I The SQL command completed successfully.
db2inst1@baseDB2:/db2home/test_data> db2 "select count(*) from tpcd.part"
1
-----------
199995
1 record(s) selected.
db2inst1@baseDB2:/db2home/test_data>
进行带COPY YES选项的load操作：
db2inst1@baseDB2:/db2home/test_data> db2 "load from part.tbl of del modified by coldel| insert into tpcd.part copy yes to /db2home/test_data"
SQL3109N The utility is beginning to load data from file
"/db2home/test_data/part.tbl".
SQL3500W The utility is beginning the "LOAD" phase at time "02/11/2014
05:19:28.685060".
SQL3519W Begin Load Consistency Point. Input record count = "0".
SQL3520W Load Consistency Point was successful.
SQL3110N The utility has completed processing. "200000" rows were read from
the input file.
SQL3519W Begin Load Consistency Point. Input record count = "200000".
SQL3520W Load Consistency Point was successful.
SQL3515W The utility has finished the "LOAD" phase at time "02/11/2014
05:19:29.348648".
Number of rows read = 200000
Number of rows skipped = 0
Number of rows loaded = 200000
Number of rows rejected = 0
Number of rows deleted = 0
Number of rows committed = 200000
操作成功后，查看表空间的状态：
db2inst1@baseDB2:/db2home/test_data> db2 list tablespaces
Tablespaces for Current Database
Tablespace ID = 0
Name = SYSCATSPACE
Type = Database managed space
Contents = All permanent data. Regular table space.
State = 0x0000
Detailed explanation:
Normal
Tablespace ID = 1
Name = TEMPSPACE1
Type = System managed space
Contents = System Temporary data
State = 0x0000
Detailed explanation:
Normal
Tablespace ID = 2
Name = USERSPACE1
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0000
Detailed explanation:
Normal
表空间状态正常
同时在db2 "load from part.tbl of del modified by coldel| replace into tpcd.part copy yes to /db2home/test_data" 这个to的路径下生成一个备份介质：
-rw------- 1 db2inst1 db2iadm1 139710464 2014-02-11 05:07 TPCD.0.db2inst1.NODE0000.CATN0000.20140211050752.001
-rw-r----- 1 db2inst1 db2iadm1 36233216 2014-02-11 05:14 TPCD.4.db2inst1.NODE0000.CATN0000.20140211051455.001
-rw-r----- 1 db2inst1 db2iadm1 36233216 2014-02-11 05:19 TPCD.4.db2inst1.NODE0000.CATN0000.20140211051928.001
其中的4表示是load操作的备份介质。
查看导入之后的数据大小：
db2inst1@baseDB2:/db2home/test_data> db2 "select count(*) from tpcd.part"
1
-----------
399995
1 record(s) selected.
db2inst1@baseDB2:/db2home/test_data>
再删除一些数据：
db2inst1@baseDB2:/db2home/test_data> db2 "delete from tpcd.part where P_PARTKEY in (150158,150159,150160,150161,150162)"
DB20000I The SQL command completed successfully.
db2inst1@baseDB2:/db2home/test_data> db2 commit
DB20000I The SQL command completed successfully.
db2inst1@baseDB2:/db2home/test_data> db2 "select count(*) from tpcd.part"
1
-----------
399990
1 record(s) selected.
db2inst1@baseDB2:/db2home/test_data>
进行restore操作：
db2inst1@baseDB2:/db2home/test_data> db2 deactivate db tpcd
DB20000I The DEACTIVATE DATABASE command completed successfully.
db2inst1@baseDB2:/db2home/test_data>
db2inst1@baseDB2:/db2home/test_data>
db2inst1@baseDB2:/db2home/test_data>
db2inst1@baseDB2:/db2home/test_data> db2 restore db tpcd taken at 20140211050752
SQL2539W Warning! Restoring to an existing database that is the same as the
backup image database. The database files will be deleted.
Do you want to continue ? (y/n) y
DB20000I The RESTORE DATABASE command completed successfully.
db2inst1@baseDB2:/db2home/test_data>
进行前滚：
db2inst1@baseDB2:/db2home/test_data> db2 rollforward db tpcd to end of logs and stop
Rollforward Status
Input database alias = tpcd
Number of nodes have returned status = 1
Node number = 0
Rollforward status = not pending
Next log file to be read =
Log files processed = S0000000.LOG - S0000003.LOG
Last committed transaction = 2014-02-10-21.21.18.000000 UTC
DB20000I The ROLLFORWARD command completed successfully.
查看数据库中的数据条数：
db2inst1@baseDB2:/db2home/test_data> db2 activate db tpcd
DB20000I The ACTIVATE DATABASE command completed successfully.
db2inst1@baseDB2:/db2home/test_data> db2 connect to tpcd
Database Connection Information
Database server = DB2/LINUXX8664 9.7.0
SQL authorization ID = DB2INST1
Local database alias = TPCD
db2inst1@baseDB2:/db2home/test_data> db2 "select count(*) from tpcd.part"
1
-----------
399990
1 record(s) selected.
db2inst1@baseDB2:/db2home/test_data>
和load、delete操作之后的总数据条数一致。说明load导入的数据在rollforward的过程中通过load的数据备份介质进行了导入。
但如果把这个load的备份介质删除，进行恢复前滚的时候，又会怎样呢？测试如下：
移走load的备份：
db2inst1@baseDB2:/db2home/db2inst1> cd /db2home/test_data
db2inst1@baseDB2:/db2home/test_data> mv TPCD.4.* /db2home/db2inst1
db2inst1@baseDB2:/db2home/test_data> ls -rtl
total 277875
-rw-r--r-- 1 db2inst1 db2iadm1 1409184 2014-02-07 11:38 supplier.tbl
-rw-r--r-- 1 db2inst1 db2iadm1 24135125 2014-02-07 11:38 part.tbl
-rw-r--r-- 1 db2inst1 db2iadm1 118984616 2014-02-07 11:39 partsupp.tbl
-rwxr-xr-x 1 db2inst1 db2iadm1 2072 2014-02-07 11:43 dss.ri
-rwxr-xr-x 1 db2inst1 db2iadm1 3814 2014-02-07 11:43 dss.ddl
drwxr-xr-x 2 root root 912 2014-02-07 13:36 tpds
drwxr-xr-x 2 db2inst1 db2iadm1 376 2014-02-10 07:04 1Day
-rw-r--r-- 1 db2inst1 db2iadm1 1070 2014-02-11 03:46 DART.INF
-rw-r--r-- 1 db2inst1 db2iadm1 159 2014-02-11 04:18 test_reslove_tablespace_bakcup_pending.sh
-rw-r--r-- 1 db2inst1 db2iadm1 97 2014-02-11 04:20 get_tablespaces_status.sh
-rw------- 1 db2inst1 db2iadm1 139710464 2014-02-11 05:07 TPCD.0.db2inst1.NODE0000.CATN0000.20140211050752.001
db2inst1@baseDB2:/db2home/test_data>
再次进行恢复前滚：
db2inst1@baseDB2:/db2home/test_data> db2 restore db tpcd taken at 20140211050752
SQL2539W Warning! Restoring to an existing database that is the same as the
backup image database. The database files will be deleted.
Do you want to continue ? (y/n) y
DB20000I The RESTORE DATABASE command completed successfully.
db2inst1@baseDB2:/db2home/test_data> db2 rollforward db tpcd to end of logs and stop
SQL3799W Load recovery for table "TPCD .PART" at time "2014021105145" on
node "0" is pending due to warning "-2036" with additional information
"/db2home/test_data/TPCD.4.db2inst1".
Do you want to continue(c), terminate this device only(d), abort the utility(t) ? (c/d/t) c
不能继续前滚，因为load recovery的缘故，选择c，continue：
SQL3799W Load recovery for table "TPCD .PART" at time "2014021105145" on
node "0" is pending due to warning "-2036" with additional information
"/db2home/test_data/TPCD.4.db2inst1".
不行，选择t，abort
Do you want to continue(c), terminate this device only(d), abort the utility(t) ? (c/d/t) t
SQL1271W Database "TPCD" is recovered but one or more table spaces are
off-line on node(s) "0".
数据库可以连接了，但是表空间的状态不对：
db2inst1@baseDB2:/db2home/test_data> db2 connect to tpcd
Database Connection Information
Database server = DB2/LINUXX8664 9.7.0
SQL authorization ID = DB2INST1
Local database alias = TPCD
db2inst1@baseDB2:/db2home/test_data> db2 list tablespaces
Tablespaces for Current Database
Tablespace ID = 0
Name = SYSCATSPACE
Type = Database managed space
Contents = All permanent data. Regular table space.
State = 0x0000
Detailed explanation:
Normal
Tablespace ID = 1
Name = TEMPSPACE1
Type = System managed space
Contents = System Temporary data
State = 0x0000
Detailed explanation:
Normal
Tablespace ID = 2
Name = USERSPACE1
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0100
Detailed explanation:
Restore pending
查询表中的数据：
db2inst1@baseDB2:/db2home/test_data> db2 "select count(*) from tpcd.part"
1
-----------
SQL0290N Table space access is not allowed. SQLSTATE=55039
db2inst1@baseDB2:/db2home/test_data>
尴尬了……不知道怎么弄，只好再来一次恢复前滚：
db2inst1@baseDB2:/db2home/test_data> db2 restore db tpcd taken at 20140211050752
SQL2539W Warning! Restoring to an existing database that is the same as the
backup image database. The database files will be deleted.
Do you want to continue ? (y/n)
Do you want to continue ? (y/n)
Do you want to continue ? (y/n) y
DB20000I The RESTORE DATABASE command completed successfully.
db2inst1@baseDB2:/db2home/test_data> db2 rollforward db tpcd to end of log and stop
SQL0104N An unexpected token "log" was found following "OF". Expected tokens
may include: "LOGS". SQLSTATE=42601
db2inst1@baseDB2:/db2home/test_data> db2 rollforward db tpcd to end of logs and stop
SQL3799W Load recovery for table "TPCD .PART" at time "2014021105145" on
node "0" is pending due to warning "-2036" with additional information
"/db2home/test_data/TPCD.4.db2inst1".
Do you want to continue(c), terminate this device only(d), abort the utility(t) ? (c/d/t) d
SQL3799W Load recovery for table "TPCD .PART" at time "20140211051455" on
node "0" is pending due to warning "3798" with additional information
"action".
Do you want to continue(c), terminate this device only(d), abort the utility(t) ? (c/d/t) t
SQL1271W Database "TPCD" is recovered but one or more table spaces are
off-line on node(s) "0".
db2inst1@baseDB2:/db2home/test_data>
还是不行，选择参数d也是不行，用了t，又回到刚才的状态了……
尝试将load的备份文件拿回，然后强制设置数据库的状态为rollforward pending，再进行前滚，结果，还是不行……
db2inst1@baseDB2:/db2home/test_data> db2 rollforward db tpcd to end of logs and stop
SQL1271W Database "TPCD" is recovered but one or more table spaces are
off-line on node(s) "0".
db2inst1@baseDB2:/db2home/test_data> db2 rollforward db tpcd query status
Rollforward Status
Input database alias = tpcd
Number of nodes have returned status = 1
Node number = 0
Rollforward status = not pending
Next log file to be read =
Log files processed = -
Last committed transaction = 2014-02-11-15.32.43.000000 UTC
db2inst1@baseDB2:/db2home/test_data>
db2inst1@baseDB2:/db2home/test_data>
db2inst1@baseDB2:/db2home/test_data>
db2inst1@baseDB2:/db2home/test_data> db2 activate db tpcd
DB20000I The ACTIVATE DATABASE command completed successfully.
db2inst1@baseDB2:/db2home/test_data> db2 connect to tpcd
Database Connection Information
Database server = DB2/LINUXX8664 9.7.0
SQL authorization ID = DB2INST1
Local database alias = TPCD
db2inst1@baseDB2:/db2home/test_data> db2 list tablespaces
Tablespaces for Current Database
Tablespace ID = 0
Name = SYSCATSPACE
Type = Database managed space
Contents = All permanent data. Regular table space.
State = 0x0000
Detailed explanation:
Normal
Tablespace ID = 1
Name = TEMPSPACE1
Type = System managed space
Contents = System Temporary data
State = 0x0000
Detailed explanation:
Normal
Tablespace ID = 2
Name = USERSPACE1
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0100
Detailed explanation:
Restore pending
Tablespace ID = 3
Name = SYSTOOLSPACE
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0000
Detailed explanation:
Normal
Tablespace ID = 4
Name = SYSTOOLSTMPSPACE
Type = System managed space
Contents = User Temporary data
State = 0x0000
Detailed explanation:
Normal
db2inst1@baseDB2:/db2home/test_data>
目前看来，只能重新恢复（并将load的备份放在指定位置）或者恢复到指定的时点了。
可见，在load过程中，copy yes参数设定的情况下，生成的load数据的备份文件对以后数据库的前滚至关重要，如果其丢失了，在做前滚时，将不能应用曾经导入的load的数据而造成数据的损失。
因此，虽然load过程中指定copy yes可以避免表空间的backup pending，但是，从保险的角度来说，做完load之后，还是进行一下表空间的备份为妙。
由以上两种参数的调查，得出的load的最稳妥的做法是，使用copy yes同时，在copy yes的load导入成功后，立即进行表空间的备份（或者全备）。
3、NONERECOVERABLE
如果在load过程中，指定了nonerecoverable参数，则表明被导入数据的表是不能进行前滚恢复的，如果进行前滚恢复，此表会置于unaviliable状态。验证如下：
db2inst1@baseDB2:/db2home/test_data> db2 "load from part.tbl of del modified by coldel| insert into tpcd.part nonrecoverable"
SQL3109N The utility is beginning to load data from file
"/db2home/test_data/part.tbl".
SQL3500W The utility is beginning the "LOAD" phase at time "02/12/2014
00:29:58.182231".
SQL3519W Begin Load Consistency Point. Input record count = "0".
SQL3520W Load Consistency Point was successful.
SQL3110N The utility has completed processing. "200000" rows were read from
the input file.
SQL3519W Begin Load Consistency Point. Input record count = "200000".
SQL3520W Load Consistency Point was successful.
SQL3515W The utility has finished the "LOAD" phase at time "02/12/2014
00:29:58.822294".
Number of rows read = 200000
Number of rows skipped = 0
Number of rows loaded = 200000
Number of rows rejected = 0
Number of rows deleted = 0
Number of rows committed = 200000
db2inst1@baseDB2:/db2home/test_data> db2 list tablespaces
Tablespaces for Current Database
Tablespace ID = 0
Name = SYSCATSPACE
Type = Database managed space
Contents = All permanent data. Regular table space.
State = 0x0000
Detailed explanation:
Normal
Tablespace ID = 1
Name = TEMPSPACE1
Type = System managed space
Contents = System Temporary data
State = 0x0000
Detailed explanation:
Normal
Tablespace ID = 2
Name = USERSPACE1
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0000
Detailed explanation:
Normal
Tablespace ID = 3
Name = SYSTOOLSPACE
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0000
Detailed explanation:
Normal
Tablespace ID = 4
Name = SYSTOOLSTMPSPACE
Type = System managed space
Contents = User Temporary data
State = 0x0000
Detailed explanation:
Normal
db2inst1@baseDB2:/db2home/test_data>
使用此参数完成导入后，表空间的状态也是正常的。
然后，对此表进行操作，删除一些数据：
db2inst1@baseDB2:/db2home/test_data> db2 "delete from tpcd.part where P_PARTKEY in (150158,150159,150160,150161,150162)"
DB20000I The SQL command completed successfully.
db2inst1@baseDB2:/db2home/test_data> db2 commit
DB20000I The SQL command completed successfully.
db2inst1@baseDB2:/db2home/test_data>
db2inst1@baseDB2:/db2home/test_data>
db2inst1@baseDB2:/db2home/test_data>
db2inst1@baseDB2:/db2home/test_data>
db2inst1@baseDB2:/db2home/test_data> db2 "select count(*) from tpcd.part"
1
-----------
199995
1 record(s) selected.
db2inst1@baseDB2:/db2home/test_data>
然后，进行恢复前滚：
db2inst1@baseDB2:/db2home/test_data> db2 restore db tpcd taken at 20140211050752
SQL2539W Warning! Restoring to an existing database that is the same as the
backup image database. The database files will be deleted.
Do you want to continue ? (y/n) y
DB20000I The RESTORE DATABASE command completed successfully.
db2inst1@baseDB2:/db2home/test_data> db2 rollforward db tpcd to end of logs and stop
Rollforward Status
Input database alias = tpcd
Number of nodes have returned status = 1
Node number = 0
Rollforward status = not pending
Next log file to be read =
Log files processed = S0000000.LOG - S0000005.LOG
Last committed transaction = 2014-02-11-16.38.35.000000 UTC
DB20000I The ROLLFORWARD command completed successfully.
db2inst1@baseDB2:/db2home/test_data> db2 activate db tpcd
DB20000I The ACTIVATE DATABASE command completed successfully.
db2inst1@baseDB2:/db2home/test_data> db2 connect to tpcd
Database Connection Information
Database server = DB2/LINUXX8664 9.7.0
SQL authorization ID = DB2INST1
Local database alias = TPCD
db2inst1@baseDB2:/db2home/test_data> db2 "select count(*) from tpcd.part"
1
-----------
SQL1477N For table "TPCD.PART" an object "270" in table space "2" cannot be
accessed. SQLSTATE=55019
db2inst1@baseDB2:/db2home/test_data> db2 list tablespaces
Tablespaces for Current Database
Tablespace ID = 0
Name = SYSCATSPACE
Type = Database managed space
Contents = All permanent data. Regular table space.
State = 0x0000
Detailed explanation:
Normal
Tablespace ID = 1
Name = TEMPSPACE1
Type = System managed space
Contents = System Temporary data
State = 0x0000
Detailed explanation:
Normal
Tablespace ID = 2
Name = USERSPACE1
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0000
Detailed explanation:
Normal
Tablespace ID = 3
Name = SYSTOOLSPACE
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0000
Detailed explanation:
Normal
Tablespace ID = 4
Name = SYSTOOLSTMPSPACE
Type = System managed space
Contents = User Temporary data
State = 0x0000
Detailed explanation:
Normal
db2inst1@baseDB2:/db2home/test_data> db2 load query table tpcd.part
Tablestate:
Unavailable
db2inst1@baseDB2:/db2home/test_data>
可以看到，恢复和前滚操作都显示成功了，但是被导入数据的表tpcd.part不可用，无法进行查询，同时显示表的状态是Unavailable。
这时，就只能通过以前的数据库备份进行重新恢复并前滚到指定的时间点，演示如下：
db2inst1@baseDB2:/db2home/test_data> db2 rollforward db tpcd to 2014-02-11-05.07.55.000000 using local time
Rollforward Status
Input database alias = tpcd
Number of nodes have returned status = 1
Node number = 0
Rollforward status = DB working
Next log file to be read = S0000003.LOG
Log files processed = S0000000.LOG - S0000002.LOG
Last committed transaction = 2014-02-11-05.07.55.000000 Local
DB20000I The ROLLFORWARD command completed successfully.
db2inst1@baseDB2:/db2home/test_data> db2 rollforward db tpcd stop
Rollforward Status
Input database alias = tpcd
Number of nodes have returned status = 1
Node number = 0
Rollforward status = not pending
Next log file to be read =
Log files processed = S0000000.LOG - S0000003.LOG
Last committed transaction = 2014-02-11-05.07.55.000000 Local
DB20000I The ROLLFORWARD command completed successfully.
db2inst1@baseDB2:/db2home/test_data> db2 connect to tpcd
Database Connection Information
Database server = DB2/LINUXX8664 9.7.0
SQL authorization ID = DB2INST1
Local database alias = TPCD
db2inst1@baseDB2:/db2home/test_data> db2 load query table tpcd.part
Tablestate:
Normal
db2inst1@baseDB2:/db2home/test_data>
db2inst1@baseDB2:/db2home/test_data> db2 "select count(*) from tpcd.part"
1
-----------
200000
1 record(s) selected.
db2inst1@baseDB2:/db2home/test_data>
