db2基础知识

1.DB2启动关闭

--关闭db2 [db2inst1@xifenfei ~]$ db2stop 03 /28/2012 09:23:39     0   0   SQL1064N  DB2STOP processing was successful. SQL1064N  DB2STOP processing was successful.   --开启db2 [db2inst1@xifenfei ~]$ db2start 03 /28/2012 09:23:55     0   0   SQL1063N  DB2START processing was successful. SQL1063N  DB2START processing was successful.

2.查看DB2数据库

[db2inst1@xifenfei ~]$ db2 list db directory     System Database Directory     Number of entries in the directory = 1   Database 1 entry:     Database alias                       = TOOLSDB   Database name                        = TOOLSDB   Local database directory             = /home/db2inst1   Database release level               = d.00   Comment                              =   Directory entry type                 = Indirect   Catalog database partition number    = 0   Alternate server hostname            =   Alternate server port number         =

3.连接DB2数据库

[db2inst1@xifenfei ~]$ db2 (c) Copyright IBM Corporation 1993,2007 Command Line Processor for DB2 Client 9.7.4   You can issue database manager commands and SQL statements from the command prompt. For example:      db2 => connect to sample      db2 => bind sample.bnd   For general help, type : ?. For command help, type : ? command , where command can be the first few keywords of a database manager command . For example:   ? CATALOG DATABASE for help on the CATALOG DATABASE command   ? CATALOG          for help on all of the CATALOG commands.   To exit db2 interactive mode, type QUIT at the command prompt. Outside interactive mode, all commands must be prefixed with 'db2' . To list the current command option settings, type LIST COMMAND OPTIONS.   For more detailed help, refer to the Online Reference Manual.   db2 => connect to TOOLSDB       Database Connection Information     Database server        = DB2 /LINUX 9.7.4   SQL authorization ID   = DB2INST1   Local database alias   = TOOLSDB

4.查看数据库中包含包

db2 => list tables   Table /View                      Schema          Type  Creation time ------------------------------- --------------- ----- --------------------------      0 record(s) selected.   db2 => create table t_xff ( id int,name varchar(100)) DB20000I  The SQL command completed successfully. db2 => list tables   Table /View                      Schema          Type  Creation time ------------------------------- --------------- ----- -------------------------- T_XFF                           DB2INST1        T     2012-03-28-09.29.54.572395      1 record(s) selected.

5.常见DML操作

db2 =>  insert into t_xff values(1, 'xifenfei' ) DB20000I  The SQL command completed successfully. db2 => insert into t_xff values(2, 'www.xifenfei' ) DB20000I  The SQL command completed successfully. db2 => select * from t_xff   ID          NAME ----------- ---------------------------------------            1 xifenfei            2 www.xifenfei            2 record(s) selected.   db2 => delete from t_xff where id =1 DB20000I  The SQL command completed successfully. db2 => select * from t_xff   ID          NAME ----------- -----------------------------------------            2 www.xifenfei                                  1 record(s) selected.   db2 => quit DB20000I  The QUIT command completed successfully. 查看db2版本 db2 => select * from sysibm.sysversions   列出所有实例 [db2inst1@xifenfei ~]$ db2ilist db2inst1   列出当前实例 [db2inst1@xifenfei ~]$ db2 get instance     The current database manager instance is:  db2inst1   察看示例配置文件 [db2inst1@xifenfei ~]$ db2 get dbm cfg| more   察看数据库配置参数信息 [db2inst1@xifenfei ~]$ db2 get db cfg for TOOLSDB| more   列出所有表空间的详细信息 [db2inst1@xifenfei ~]$ db2 list tablespaces show detail| more   连接数据库 [db2inst1@xifenfei ~]$ db2 connect to TOOLSDB       Database Connection Information     Database server        = DB2 /LINUX 9.7.4   SQL authorization ID   = DB2INST1   Local database alias   = TOOLSDB   sql操作数据库 [db2inst1@xifenfei ~]$ db2 "select * from t_xff"   查看端口号 [db2inst1@xifenfei ~]$ db2 get dbm cfg| grep SVCENAME   查看表结构 [db2inst1@xifenfei ~]$ db2 describe table t_xifenfei   查看某个表索引 [db2inst1@xifenfei ~]$ db2 describe indexes for table t_xff   显示当前活动数据库 [db2inst1@xifenfei ~]$ db2 list active databases   列出所有的系统表 [db2inst1@xifenfei ~]$ db2 list tables for system   列出表空间 [db2inst1@xifenfei ~]$ db2 list tablespaces   显示用户数据库的存取权限 [db2inst1@xifenfei ~]$ db2 GET AUTHORIZATIONS   检查 DB2 数据库管理程序配置 [db2inst1@xifenfei ~]$ db2 get dbm cfg DB2备份恢复(全备与恢复) 日志模式 [db2inst1@xifenfei ~]$ db2 get db cfg for xff | grep -i log   Log retain for recovery status                          = NO   User exit for logging status                            = YES   --(1)   Catalog cache size (4KB)              (CATALOGCACHE_SZ) = 260   Log buffer size (4KB)                        (LOGBUFSZ) = 98   Log file size (4KB)                         (LOGFILSIZ) = 1024   Number of primary log files                (LOGPRIMARY) = 6   Number of secondary log files               (LOGSECOND) = 4   Changed path to log files                  (NEWLOGPATH) =   Path to log files                                       = /home/db2inst1/xff/redolog/NODE0000/   Overflow log path                     (OVERFLOWLOGPATH) =   Mirror log path                         (MIRRORLOGPATH) =   First active log file                                   = S0000013.LOG   Block log on disk full                (BLK_LOG_DSK_FUL) = NO   Block non logged operations            (BLOCKNONLOGGED) = NO   Percent max primary log space by transaction  (MAX_LOG) = 0   Num. of active log files for 1 active UOW(NUM_LOG_SPAN) = 0   Percent log file reclaimed before soft chckpt (SOFTMAX) = 520   Log retain for recovery enabled             (LOGRETAIN) = OFF   User exit for logging enabled                (USEREXIT) = OFF   HADR log write synchronization mode     (HADR_SYNCMODE) = NEARSYNC   First log archive method                 (LOGARCHMETH1) = DISK: /home/db2inst1/xff/archivelog/  --(2)   Options for logarchmeth1                  (LOGARCHOPT1) =   Second log archive method                (LOGARCHMETH2) = OFF   Options for logarchmeth2                  (LOGARCHOPT2) =   Failover log archive path                (FAILARCHPATH) =   Number of log archive retries on error   (NUMARCHRETRY) = 5   Log archive retry Delay (secs)         (ARCHRETRYDELAY) = 20   Log pages during index build            (LOGINDEXBUILD) = OFF 由(1)和(2)可以判断该数据库处于归档日志模式下 查看当前存在备份 [db2inst1@xifenfei ~]$ db2 list history backup all for xff                        List History File for xff   Number of matching file entries = 1     Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log  Backup ID   -- --- ------------------ ---- --- ------------ ------------ --------------    B  D  20120406053431001   F    D  S0000000.LOG S0000000.LOG   ----------------------------------------------------------------------------    Contains 2 tablespace(s):     00001 SYSCATSPACE   00002 USERSPACE1   ----------------------------------------------------------------------------      Comment: DB2 BACKUP XFF OFFLINE    --离线备份   Start Time: 20120406053431     End Time: 20120406053439       Status: A   ----------------------------------------------------------------------------    EID: 1 Location: /tmp 在线全备 [db2inst1@xifenfei ~]$ db2 backup db xff online to /tmp  include logs   Backup successful. The timestamp for this backup image is : 20120411165312   [db2inst1@xifenfei ~]$ db2 list history backup all for xff                        List History File for xff   Number of matching file entries = 2     Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log  Backup ID   -- --- ------------------ ---- --- ------------ ------------ --------------    B  D  20120406053431001   F    D  S0000000.LOG S0000000.LOG   ----------------------------------------------------------------------------    Contains 2 tablespace(s):     00001 SYSCATSPACE   00002 USERSPACE1   ----------------------------------------------------------------------------      Comment: DB2 BACKUP XFF OFFLINE  --本次试验的online备份   Start Time: 20120406053431     End Time: 20120406053439       Status: A   ----------------------------------------------------------------------------    EID: 1 Location: /tmp     Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log  Backup ID   -- --- ------------------ ---- --- ------------ ------------ --------------    B  D  20120411165312001   N    D  S0000013.LOG S0000013.LOG   ----------------------------------------------------------------------------    Contains 3 tablespace(s):     00001 SYSCATSPACE   00002 USERSPACE1   00003 SYSTOOLSPACE   ----------------------------------------------------------------------------      Comment: DB2 BACKUP XFF ONLINE   Start Time: 20120411165312     End Time: 20120411165322       Status: A   ----------------------------------------------------------------------------    EID: 19 Location: /tmp 当前数据库当前数据 [db2inst1@xifenfei ~]$ db2 connect to xff       Database Connection Information     Database server        = DB2 /LINUX 9.5.9   SQL authorization ID   = DB2INST1   Local database alias   = XFF   [db2inst1@xifenfei ~]$ db2 list tables   Table /View                      Schema          Type  Creation time ------------------------------- --------------- ----- -------------------------- T_XFF                           DB2INST1        T     2012-04-05-09.45.29.148434 T_XIFENFEI                      DB2INST1        T     2012-04-06-05.50.11.111469      2 record(s) selected.   [db2inst1@xifenfei ~]$ db2 "create table t_xifenfei01 like t_xff" DB20000I  The SQL command completed successfully. [db2inst1@xifenfei ~]$ db2 "insert into t_xifenfei01 > select * from t_xff" DB20000I  The SQL command completed successfully. [db2inst1@xifenfei ~]$ db2 "insert into t_xifenfei01 select * from t_xff" DB20000I  The SQL command completed successfully. [db2inst1@xifenfei ~]$ db2 "select count(*) from t_xifenfei01"   1 -----------          734      1 record(s) selected. 恢复数据库 [db2inst1@xifenfei ~]$ db2 restore db xff from /tmp taken at 20120411165312 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@xifenfei ~]$ db2 "rollforward db xff to end of logs and stop"                                     Rollforward Status     Input database alias                   = xff   Number of nodes have returned status   = 1     Node number                            = 0   Rollforward status                     = not pending   Next log file to be read               =   Log files processed                    = S0000013.LOG - S0000014.LOG   Last committed transaction             = 2012-04-11-08.56.20.000000 UTC   DB20000I  The ROLLFORWARD command completed successfully. [db2inst1@xifenfei ~]$ db connect to xff - bash : db: command not found [db2inst1@xifenfei ~]$ db2 connect to xff       Database Connection Information     Database server        = DB2 /LINUX 9.5.9   SQL authorization ID   = DB2INST1   Local database alias   = XFF   [db2inst1@xifenfei ~]$ db2 list tables   Table /View                      Schema          Type  Creation time ------------------------------- --------------- ----- -------------------------- T_XFF                           DB2INST1        T     2012-04-05-09.45.29.148434 T_XIFENFEI                      DB2INST1        T     2012-04-06-05.50.11.111469 T_XIFENFEI01                    DB2INST1        T     2012-04-11-16.55.51.853649      3 record(s) selected.   [db2inst1@xifenfei ~]$ db2 "select count(*) from t_xifenfei01"   1 -----------          734      1 record(s) selected. 备份恢复是dba最重要的职责,本篇做为db2学习过程中第一篇关于备份恢复文章,后续将继续学习db2增量备份恢复等知识.

DB2备份恢复(增量备份与恢复)

全备数据库

[db2inst1@xifenfei ~]$ db2 backup db xff online to /tmp  include logs   Backup successful. The timestamp for this backup image is : 20120411181918   [db2inst1@xifenfei ~]$ db2 list history backup all for xff                        List History File for xff   Number of matching file entries = 1     Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log  Backup ID   -- --- ------------------ ---- --- ------------ ------------ --------------    B  D  20120411181918001   N    D  S0000015.LOG S0000015.LOG   ----------------------------------------------------------------------------    Contains 3 tablespace(s):     00001 SYSCATSPACE   00002 USERSPACE1   00003 SYSTOOLSPACE   ----------------------------------------------------------------------------      Comment: DB2 BACKUP XFF ONLINE   Start Time: 20120411181918     End Time: 20120411181925       Status: A   ----------------------------------------------------------------------------    EID: 27 Location: /tmp

修改数据

[db2inst1@xifenfei ~]$ db2 list tables   Table /View                      Schema          Type  Creation time ------------------------------- --------------- ----- -------------------------- T_XFF                           DB2INST1        T     2012-04-05-09.45.29.148434 T_XIFENFEI                      DB2INST1        T     2012-04-06-05.50.11.111469 T_XIFENFEI01                    DB2INST1        T     2012-04-11-16.55.51.853649      3 record(s) selected.   [db2inst1@xifenfei ~]$ db2 "drop table t_xff" DB20000I  The SQL command completed successfully. [db2inst1@xifenfei ~]$ db2 "drop table t_xifenfei" DB20000I  The SQL command completed successfully. [db2inst1@xifenfei ~]$ db2 "drop table t_xifenfei01" DB20000I  The SQL command completed successfully. [db2inst1@xifenfei ~]$ db2 "create table t_01xff like syscat.tables" DB20000I  The SQL command completed successfully. [db2inst1@xifenfei ~]$ db2 "insert into t_01xff select * from syscat.tables" DB20000I  The SQL command completed successfully. [db2inst1@xifenfei ~]$ db2 "select count(*) from t_01xff"   1 -----------          370      1 record(s) selected.   [db2inst1@xifenfei ~]$ db2 list tables   Table /View                      Schema          Type  Creation time ------------------------------- --------------- ----- -------------------------- T_01XFF                         DB2INST1        T     2012-04-11-18.23.05.723478      1 record(s) selected.

增量备份SQL2426N解决

[db2inst1@xifenfei ~]$ db2 backup db xff online incremental to /tmp SQL2426N  The database has not been configured to allow the incremental backup operation. Reason code = "1" .   [db2inst1@xifenfei ~]$ db2 ? SQL2426N   SQL2426N  The database has not been configured to allow the incremental        backup operation. Reason code = "<reason-code>" .   Explanation:   Incremental backups are not enabled for a table space until after modification tracking has been activated for the database and a non-incremental backup has been performed on the table space.   Possible reason codes: 1. The configuration parameter TRACKMOD has not been set for the     database. 2. The TRACKMOD configuration parameter has been set but at least one     table space has not had a non-incremental backup taken since the     TRACKMOD parameter was set .   User response:   The action is based on the reason code as follows: 1. Activate modification tracking for the database by setting the     TRACKMOD database configuration parameter to on, then perform a full     database backup. 2. Consult the db2diag.log file to determine the name of the table     space, then perform a full backup of that table space.   [db2inst1@xifenfei ~]$ db2 get db cfg for xff| grep TRACKMOD   Track modified pages                         (TRACKMOD) = NO   [db2inst1@xifenfei ~]$ db2 update db cfg for xff using TRACKMOD ON DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully. SQL1363W  One or more of the parameters submitted for immediate modification were not changed dynamically. For these configuration parameters, the database must be shutdown and reactivated before the configuration parameter changes become effective.   [db2inst1@xifenfei ~]$ db2stop force 04 /11/2012 17:49:59     0   0   SQL1064N  DB2STOP processing was successful. SQL1064N  DB2STOP processing was successful. [db2inst1@xifenfei ~]$ db2start 04 /11/2012 17:50:09     0   0   SQL1063N  DB2START processing was successful. SQL1063N  DB2START processing was successful.

累积增量备份

[db2inst1@xifenfei ~]$ db2 backup db xff online incremental to /tmp   Backup successful. The timestamp for this backup image is : 20120411182708   [db2inst1@xifenfei ~]$ db2 list history backup all for xff                        List History File for xff   Number of matching file entries = 2     Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log  Backup ID   -- --- ------------------ ---- --- ------------ ------------ --------------    B  D  20120411181918001   N    D  S0000015.LOG S0000015.LOG   ----------------------------------------------------------------------------    Contains 3 tablespace(s):     00001 SYSCATSPACE   00002 USERSPACE1   00003 SYSTOOLSPACE   ----------------------------------------------------------------------------      Comment: DB2 BACKUP XFF ONLINE   Start Time: 20120411181918     End Time: 20120411181925       Status: A   ----------------------------------------------------------------------------    EID: 27 Location: /tmp     Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log  Backup ID   -- --- ------------------ ---- --- ------------ ------------ --------------    B  D  20120411182708001   O    D  S0000017.LOG S0000017.LOG   ----------------------------------------------------------------------------    Contains 3 tablespace(s):     00001 SYSCATSPACE   00002 USERSPACE1   00003 SYSTOOLSPACE   ----------------------------------------------------------------------------      Comment: DB2 BACKUP XFF ONLINE   Start Time: 20120411182708     End Time: 20120411182712       Status: A   ----------------------------------------------------------------------------    EID: 33 Location: /tmp

再次修改数据

[db2inst1@xifenfei ~]$ db2 "create table t_02xff like syscat.tables" DB20000I  The SQL command completed successfully. [db2inst1@xifenfei ~]$ db2 "insert into t_02xff select * from syscat.tables" DB20000I  The SQL command completed successfully. [db2inst1@xifenfei ~]$ db2 list tables   Table /View                      Schema          Type  Creation time ------------------------------- --------------- ----- -------------------------- T_01XFF                         DB2INST1        T     2012-04-11-18.23.05.723478 T_02XFF                         DB2INST1        T     2012-04-11-18.30.26.639326      2 record(s) selected.   [db2inst1@xifenfei ~]$ db2 "select count(*) from t_02xff"   1 -----------          371      1 record(s) selected.

迭代备份

[db2inst1@xifenfei ~]$ db2 backup db xff online incremental delta to /tmp   Backup successful. The timestamp for this backup image is : 20120411183129   [db2inst1@xifenfei ~]$ ll /tmp/XFF * -rw-------  1 db2inst1 db2iadm1 122044416 Apr 11 18:19 /tmp/XFF .0.db2inst1.NODE0000.CATN0000.20120411181918.001 -rw-------  1 db2inst1 db2iadm1  55128064 Apr 11 18:27 /tmp/XFF .0.db2inst1.NODE0000.CATN0000.20120411182708.001 -rw-------  1 db2inst1 db2iadm1  55128064 Apr 11 18:31 /tmp/XFF .0.db2inst1.NODE0000.CATN0000.20120411183129.001 --这里可以看出最近一次的增量备份和迭代备份备份文件大小相同,说明迭代备份是在最近一次增量备份基础之上进行   [db2inst1@xifenfei ~]$ db2 list history backup all for xff                        List History File for xff   Number of matching file entries = 3     Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log  Backup ID   -- --- ------------------ ---- --- ------------ ------------ --------------    B  D  20120411181918001   N    D  S0000015.LOG S0000015.LOG   ----------------------------------------------------------------------------    Contains 3 tablespace(s):     00001 SYSCATSPACE   00002 USERSPACE1   00003 SYSTOOLSPACE   ----------------------------------------------------------------------------      Comment: DB2 BACKUP XFF ONLINE   Start Time: 20120411181918     End Time: 20120411181925       Status: A   ----------------------------------------------------------------------------    EID: 27 Location: /tmp     Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log  Backup ID   -- --- ------------------ ---- --- ------------ ------------ --------------    B  D  20120411182708001   O    D  S0000017.LOG S0000017.LOG   ----------------------------------------------------------------------------    Contains 3 tablespace(s):     00001 SYSCATSPACE   00002 USERSPACE1   00003 SYSTOOLSPACE   ----------------------------------------------------------------------------      Comment: DB2 BACKUP XFF ONLINE   Start Time: 20120411182708     End Time: 20120411182712       Status: A   ----------------------------------------------------------------------------    EID: 33 Location: /tmp     Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log  Backup ID   -- --- ------------------ ---- --- ------------ ------------ --------------    B  D  20120411183129001   E    D  S0000019.LOG S0000019.LOG   ----------------------------------------------------------------------------    Contains 3 tablespace(s):     00001 SYSCATSPACE   00002 USERSPACE1   00003 SYSTOOLSPACE   ----------------------------------------------------------------------------      Comment: DB2 BACKUP XFF ONLINE   Start Time: 20120411183129     End Time: 20120411183133       Status: A   ----------------------------------------------------------------------------    EID: 36 Location: /tmp

还原数据库

[db2inst1@xifenfei ~]$ db2 restore db xff  incremental automatic from /tmp taken at 20120411183129 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@xifenfei ~]$ db2 connect to xff SQL1117N  A connection to or activation of database "XIFENFEI" cannot be made because of ROLL-FORWARD PENDING.  SQLSTATE=57019 --数据库需要前滚,因为是在线备份

恢复数据库

[db2inst1@xifenfei ~]$  db2 "rollforward db xff to end of logs and stop"                                     Rollforward Status     Input database alias                   = xff   Number of nodes have returned status   = 1     Node number                            = 0   Rollforward status                     = not pending   Next log file to be read               =   Log files processed                    = S0000019.LOG - S0000019.LOG   Last committed transaction             = 2012-04-11-10.31.30.000000 UTC   DB20000I  The ROLLFORWARD command completed successfully.

验证恢复过程

[db2inst1@xifenfei ~]$ db2 connect to xff       Database Connection Information     Database server        = DB2 /LINUX 9.5.9   SQL authorization ID   = DB2INST1   Local database alias   = XFF   [db2inst1@xifenfei ~]$ db2 list tables   Table /View                      Schema          Type  Creation time ------------------------------- --------------- ----- -------------------------- T_01XFF                         DB2INST1        T     2012-04-11-18.23.05.723478 T_02XFF                         DB2INST1        T     2012-04-11-18.30.26.639326      2 record(s) selected.   [db2inst1@xifenfei ~]$ db2 "select count(*) from t_01xff"   1 -----------          370      1 record(s) selected.   [db2inst1@xifenfei ~]$ db2 "select count(*) from t_02xff"   1 -----------          371      1 record(s) selected.

本篇主要测试了增量备份和数据库恢复,在下篇中将对不完全恢复进行测试,坚持逐步学习db2数据库相关知识

DB2备份恢复(不完全恢复)

全备数据库

[db2inst1@xifenfei ~]$ db2 backup db xff online to /tmp  include logs   Backup successful. The timestamp for this backup image is : 20120411213218   [db2inst1@xifenfei ~]$ db2 list history backup all for xff                        List History File for xff   Number of matching file entries = 1     Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log  Backup ID   -- --- ------------------ ---- --- ------------ ------------ --------------    B  D  20120411213218001   N    D  S0000021.LOG S0000021.LOG   ----------------------------------------------------------------------------    Contains 3 tablespace(s):     00001 SYSCATSPACE   00002 USERSPACE1   00003 SYSTOOLSPACE   ----------------------------------------------------------------------------      Comment: DB2 BACKUP XFF ONLINE   Start Time: 20120411213218     End Time: 20120411213229       Status: A   ----------------------------------------------------------------------------    EID: 42 Location: /tmp

数据操作(包括误操作)

[db2inst1@xifenfei ~]$ db2 connect to xff       Database Connection Information     Database server        = DB2 /LINUX 9.5.9   SQL authorization ID   = DB2INST1   Local database alias   = XFF   [db2inst1@xifenfei ~]$ db2 list tables   Table /View                      Schema          Type  Creation time ------------------------------- --------------- ----- -------------------------- T_01XFF                         DB2INST1        T     2012-04-11-18.23.05.723478 T_02XFF                         DB2INST1        T     2012-04-11-18.30.26.639326      2 record(s) selected.   [db2inst1@xifenfei ~]$ db2 "create table t_03xff like t_01xff" DB20000I  The SQL command completed successfully. [db2inst1@xifenfei ~]$ db2 "insert into t_03xff select * from t_01xff" DB20000I  The SQL command completed successfully. [db2inst1@xifenfei ~]$ date Wed Apr 11 21:33:42 CST 2012 [db2inst1@xifenfei ~]$ db2 "select count(*) from t_03xff"   1 -----------          370      1 record(s) selected.   --以下是错误操作,需要回滚 [db2inst1@xifenfei ~]$ date Wed Apr 11 21:36:38 CST 2012 [db2inst1@xifenfei ~]$ db2 "insert into t_03xff select * from t_01xff" DB20000I  The SQL command completed successfully. [db2inst1@xifenfei ~]$ db2 "select count(*) from t_03xff"   1 -----------          740      1 record(s) selected.

还原数据库

db2inst1@xifenfei ~]$ db2 restore db xff from /tmp taken at 20120411213218 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@xifenfei ~]$ db2 rollforward db xff to 2012-04-11-21.36.00.00000 using local time                                     Rollforward Status     Input database alias                   = xff   Number of nodes have returned status   = 1     Node number                            = 0   Rollforward status                     = DB  working   Next log file to be read               = S0000023.LOG   Log files processed                    = S0000021.LOG - S0000021.LOG   Last committed transaction             = 2012-04-11-21.33.27.000000 Local   DB20000I  The ROLLFORWARD command completed successfully. [db2inst1@xifenfei ~]$ db2 connect to xff SQL1117N  A connection to or activation of database "XIFENFEI" cannot be made because of ROLL-FORWARD PENDING.  SQLSTATE=57019   --停止前滚 [db2inst1@xifenfei ~]$ db2 rollforward db xff stop                                     Rollforward Status     Input database alias                   = xff   Number of nodes have returned status   = 1     Node number                            = 0   Rollforward status                     = not pending   Next log file to be read               =   Log files processed                    = S0000021.LOG - S0000022.LOG   Last committed transaction             = 2012-04-11-21.33.27.000000 Local   DB20000I  The ROLLFORWARD command completed successfully.

验证数据

[db2inst1@xifenfei ~]$ db2 connect to xff       Database Connection Information     Database server        = DB2 /LINUX 9.5.9   SQL authorization ID   = DB2INST1   Local database alias   = XFF   [db2inst1@xifenfei ~]$ db2 "select count(*) from t_03xff"   1 -----------          370      1 record(s) selected.