DB2数据迁移之export/import

全备数据库

[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.