分类目录归档：DB2

DB2备份恢复(全备与恢复)

发表于 2012 年 4 月 22 日由惜分飞

日志模式

[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 | 评论关闭

DB2日志参数介绍和修改归档模式

发表于 2012 年 4 月 18 日由惜分飞

cfg关于log参数

[db2inst1@xifenfei ~]$ db2 get db cfg for xff|grep -i log
 Log retain for recovery status                          = NO
 User exit for logging status                            = NO
 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) = 13
 Number of secondary log files               (LOGSECOND) = 4
 Changed path to log files                  (NEWLOGPATH) = 
 Path to log files                                       = /home/db2inst1/db2inst1/NODE0000/SQL00003/SQLOGDIR/
 Overflow log path                     (OVERFLOWLOGPATH) = 
 Mirror log path                         (MIRRORLOGPATH) = 
 First active log file                                   = 
 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) = OFF
 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

LOGPRIMARY:主日志组日志文件个数(首次连接或激活时直接分配)
LOGSECOND:辅助日志组日志文件个数(主日志文件写满时按需分配)
LOGFILSIZ:每个日志文件页数,每页大小为4K
LOGARCHMETH1/LOGARCHMETH2:OFF表示循环模式,其他值表示归档模式(disk:/xifenfei/archive)
NEWLOGPATH:修改新的日志路径
LOGBUFSZ:日志缓冲区大小
MIRRORLOGPATH:日志镜像路径
LOGRETAIN:归档日志保留在日志文件中(不推荐该做法)
USEREXIT:归档日志通过用户出口程序管理(8.2后不推荐该做法)

当前日志位置

[db2inst1@xifenfei ~]$ ls -l /home/db2inst1/db2inst1/NODE0000/SQL00003/SQLOGDIR/
total 53404
-rw-------  1 db2inst1 db2iadm1 4202496 Apr  5 16:20 S0000000.LOG
-rw-------  1 db2inst1 db2iadm1 4202496 Apr  5 16:20 S0000001.LOG
-rw-------  1 db2inst1 db2iadm1 4202496 Apr  5 16:20 S0000002.LOG
-rw-------  1 db2inst1 db2iadm1 4202496 Apr  5 16:20 S0000003.LOG
-rw-------  1 db2inst1 db2iadm1 4202496 Apr  5 16:20 S0000004.LOG
-rw-------  1 db2inst1 db2iadm1 4202496 Apr  5 16:20 S0000005.LOG
-rw-------  1 db2inst1 db2iadm1 4202496 Apr  5 16:20 S0000006.LOG
-rw-------  1 db2inst1 db2iadm1 4202496 Apr  5 16:20 S0000007.LOG
-rw-------  1 db2inst1 db2iadm1 4202496 Apr  5 16:20 S0000008.LOG
-rw-------  1 db2inst1 db2iadm1 4202496 Apr  5 16:20 S0000009.LOG
-rw-------  1 db2inst1 db2iadm1 4202496 Apr  5 16:20 S0000010.LOG
-rw-------  1 db2inst1 db2iadm1 4202496 Apr  5 16:20 S0000011.LOG
-rw-------  1 db2inst1 db2iadm1 4202496 Apr  5 16:20 S0000012.LOG

修改cfg参数(改为归档模式)

[db2inst1@xifenfei ~]$ db2 update db cfg for xff using LOGPRIMARY 6
DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.
[db2inst1@xifenfei ~]$ mkdir -p  xff/redolog
[db2inst1@xifenfei ~]$ mkdir -p  xff/archivelog
[db2inst1@xifenfei ~]$ db2 update db cfg for xff using NEWLOGPATH /home/db2inst1/xff/redolog
DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.
[db2inst1@xifenfei ~]$ db2 update db cfg for xff using LOGARCHMETH1 disk:/home/db2inst1/xff/archivelog
DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.

查看修改后参数

[db2inst1@xifenfei ~]$ db2 get db cfg for xff|grep -i log
 Log retain for recovery status                          = NO
 User exit for logging status                            = NO   --没有生效
 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) = /home/db2inst1/xff/redolog/NODE0000/
 Path to log files                                       = /home/db2inst1/db2inst1/NODE0000/SQL00003/SQLOGDIR/
 Overflow log path                     (OVERFLOWLOGPATH) = 
 Mirror log path                         (MIRRORLOGPATH) = 
 First active log file                                   = 
 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/
 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

参数生效情况

[db2inst1@xifenfei ~]$ ll /home/db2inst1/xff/redolog/NODE0000/
total 0
[db2inst1@xifenfei ~]$ db2stop
04/06/2012 05:33:24     0   0   SQL1064N  DB2STOP processing was successful.
SQL1064N  DB2STOP processing was successful.
[db2inst1@xifenfei ~]$ db2start
04/06/2012 05:33:34     0   0   SQL1063N  DB2START processing was successful.
SQL1063N  DB2START processing was successful.
[db2inst1@xifenfei ~]$ ll /home/db2inst1/xff/redolog/NODE0000/
total 0
[db2inst1@xifenfei ~]$ db2 connect to xff
SQL1116N  A connection to or activation of database "XIFENFEI" cannot be made 
because of BACKUP PENDING.  SQLSTATE=57019
[db2inst1@xifenfei ~]$ db2 backup db xff to /tmp

Backup successful. The timestamp for this backup image is : 20120406053431

[db2inst1@xifenfei ~]$ ll /home/db2inst1/xff/redolog/NODE0000/
total 24652
-rw-------  1 db2inst1 db2iadm1 4202496 Apr  6 05:33 S0000000.LOG
-rw-------  1 db2inst1 db2iadm1 4202496 Apr  6 05:33 S0000001.LOG
-rw-------  1 db2inst1 db2iadm1 4202496 Apr  6 05:33 S0000002.LOG
-rw-------  1 db2inst1 db2iadm1 4202496 Apr  6 05:33 S0000003.LOG
-rw-------  1 db2inst1 db2iadm1 4202496 Apr  6 05:33 S0000004.LOG
-rw-------  1 db2inst1 db2iadm1 4202496 Apr  6 05:33 S0000005.LOG
-rw-------  1 db2inst1 db2iadm1     512 Apr  6 05:33 SQLLPATH.TAG
[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 get db cfg for xff|grep -i log
 Log retain for recovery status                          = NO
 User exit for logging status                            = YES
 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                                   = S0000005.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/
 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

修改备份模式参数需要重启并且备份库后生效

测试归档日志

[db2inst1@xifenfei ~]$ db2 list history archive log  all for xff

                    List History File for xff

Number of matching file entries = 0

[db2inst1@xifenfei ~]$ db2 "create table t_xifenfei like syscat.tables"
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 "insert into t_xifenfei select * from syscat.tables"
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 "insert into t_xifenfei select * from t_xifenfei"
DB20000I  The SQL command completed successfully.
……N次……
[db2inst1@xifenfei ~]$ db2 "select count(*) from t_xifenfei"

1          
-----------
      94208

  1 record(s) selected.

[db2inst1@xifenfei ~]$ db2 list history archive log  all for xff

                    List History File for xff

Number of matching file entries = 11


 Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log  Backup ID
 -- --- ------------------ ---- --- ------------ ------------ --------------
  X  D  20120406054854      1    D  S0000000.LOG C0000000      
 ----------------------------------------------------------------------------

 ----------------------------------------------------------------------------
    Comment: 
 Start Time: 20120406054854
   End Time: 20120406055029
     Status: A
 ----------------------------------------------------------------------------
  EID: 2 Location: /home/db2inst1/xff/archivelog/db2inst1/XIFENFEI/NODE0000/C0000000/S0000000.LOG

……省略……

 Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log  Backup ID
 -- --- ------------------ ---- --- ------------ ------------ --------------
  X  D  20120406055041      P    D  S0000010.LOG C0000000      
 ----------------------------------------------------------------------------

 ----------------------------------------------------------------------------
    Comment: 
 Start Time: 20120406055041
   End Time: 
     Status: A
 ----------------------------------------------------------------------------
  EID: 14 Location: /home/db2inst1/xff/redolog/NODE0000/S0000010.LOG

[db2inst1@xifenfei ~]$ ll /home/db2inst1/xff/archivelog/db2inst1/XIFENFEI/NODE0000/C0000000
total 41080
-rw-r-----  1 db2inst1 db2iadm1 4202496 Apr  6 05:50 S0000000.LOG
-rw-r-----  1 db2inst1 db2iadm1 4202496 Apr  6 05:50 S0000001.LOG
-rw-r-----  1 db2inst1 db2iadm1 4202496 Apr  6 05:50 S0000002.LOG
-rw-r-----  1 db2inst1 db2iadm1 4202496 Apr  6 05:50 S0000003.LOG
-rw-r-----  1 db2inst1 db2iadm1 4202496 Apr  6 05:50 S0000004.LOG
-rw-r-----  1 db2inst1 db2iadm1 4202496 Apr  6 05:50 S0000005.LOG
-rw-r-----  1 db2inst1 db2iadm1 4202496 Apr  6 05:50 S0000006.LOG
-rw-r-----  1 db2inst1 db2iadm1 4202496 Apr  6 05:50 S0000007.LOG
-rw-r-----  1 db2inst1 db2iadm1 4202496 Apr  6 05:50 S0000008.LOG
-rw-r-----  1 db2inst1 db2iadm1 4202496 Apr  6 05:50 S0000009.LOG

[db2inst1@xifenfei ~]$ ll /home/db2inst1/xff/redolog/NODE0000
total 32868
-rw-------  1 db2inst1 db2iadm1 4202496 Apr  6 05:50 S0000005.LOG
-rw-------  1 db2inst1 db2iadm1 4202496 Apr  6 05:50 S0000006.LOG
-rw-------  1 db2inst1 db2iadm1 4202496 Apr  6 05:50 S0000007.LOG
-rw-------  1 db2inst1 db2iadm1 4202496 Apr  6 05:50 S0000008.LOG
-rw-------  1 db2inst1 db2iadm1 4202496 Apr  6 05:50 S0000009.LOG
-rw-------  1 db2inst1 db2iadm1 4202496 Apr  6 05:50 S0000010.LOG
-rw-------  1 db2inst1 db2iadm1 4202496 Apr  6 05:50 S0000011.LOG
-rw-------  1 db2inst1 db2iadm1 4202496 Apr  6 05:50 S0000012.LOG
-rw-------  1 db2inst1 db2iadm1     512 Apr  6 05:33 SQLLPATH.TAG

发表在 DB2 | 评论关闭

DB2数据迁移之db2lock/db2move

发表于 2012 年 4 月 15 日由惜分飞

1.模拟带有identity表

[db2inst1@xifenfei ~]$ db2 "create table t_xff(xid smallint not null generated always as identity 
> (start with 1,increment by 1),x_name varchar(200)) in ts_xifenfei"
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 "insert into t_xff(x_name) values('www.xifenfei.com')"
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 "insert into t_xff(x_name) values('XIFENFEI')"
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 "insert into t_xff(x_name) values('xifenfei')"
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 "select * from t_ff"
SQL0204N  "DB2INST1.T_FF" is an undefined name.  SQLSTATE=42704
[db2inst1@xifenfei ~]$ db2 "select * from t_xff"

XID    X_NAME           
------ -------------
     1 www.xifenfei.com 
     2 XIFENFEI         
     3 xifenfei         

  3 record(s) selected.

2.导出表结构

[db2inst1@xifenfei ~]$ mkdir move_s
[db2inst1@xifenfei ~]$ cd move_s/
[db2inst1@xifenfei move_s]$ db2look -d sample -e -l -o db2_sample.ddl
-- No userid was specified, db2look tries to use Environment variable USER
-- USER is: DB2INST1
-- Creating DDL for table(s)
-- Output is sent to file: db2_sample.ddl

3.导出数据

[db2inst1@xifenfei move_s]$ db2move sample export

Application code page not determined, using ANSI codepage 1208

*****  DB2MOVE  *****

Action:  EXPORT

Start time:  Thu Apr  5 20:21:28 2012


Connecting to database SAMPLE ... successful!  Server : DB2 Common Server V9.5.9

Binding package automatically ... /home/db2inst1/sqllib/bnd/db2common.bnd ... successful!

Binding package automatically ... /home/db2inst1/sqllib/bnd/db2move.bnd ... successful!

EXPORT:     18 rows from table "DB2INST1"."ACT"
EXPORT:      5 rows from table "DB2INST1"."CL_SCHED"
EXPORT:     14 rows from table "DB2INST1"."DEPARTMENT"
EXPORT:     42 rows from table "DB2INST1"."EMPLOYEE"
EXPORT:  10000 rows from table "DB2INST1"."EMPMDC"
EXPORT:     73 rows from table "DB2INST1"."EMPPROJACT"
EXPORT:      8 rows from table "DB2INST1"."EMP_PHOTO"
EXPORT:      8 rows from table "DB2INST1"."EMP_RESUME"
EXPORT:    145 rows from table "SYSTOOLS"."HMON_ATM_INFO"
EXPORT:      0 rows from table "SYSTOOLS"."HMON_COLLECTION"
EXPORT:      3 rows from table "DB2INST1"."IN_TRAY"
EXPORT:      8 rows from table "DB2INST1"."ORG"
EXPORT:      5 rows from table "SYSTOOLS"."POLICY"
EXPORT:     65 rows from table "DB2INST1"."PROJACT"
EXPORT:     20 rows from table "DB2INST1"."PROJECT"
EXPORT:     41 rows from table "DB2INST1"."SALES"
EXPORT:     35 rows from table "DB2INST1"."STAFF"
EXPORT:     35 rows from table "DB2INST1"."STAFFG"
EXPORT:      3 rows from table "DB2INST1"."T_XFF"

Disconnecting from database ... successful!

End time:  Thu Apr  5 20:21:32 2012

4.目标端创建数据库

C:\Windows\system32>db2 "create db db_XFF pagesize 8 k"
DB20000I  CREATE DATABASE命令成功完成。

5.目标端创建对象

C:\Windows\system32>DB2 -tvf D:\move_s\db2_sample.ddl -l d:\xifenfei.log

--检查xifenfei.log文件,发现错误,手工修复

6.导入数据文件

D:\move_s>db2move db_xff load

*****  DB2MOVE  *****

Action:  LOAD

Start time:  Sun Apr 15 23:00:17 2012


Connecting to database DB_XFF ... successful!  Server : DB2 Common Server V9.5.0

Binding package automatically ... E:\DB2\9.5\SQLLIB\BND\DB2COMMON.BND ... successful!

Binding package automatically ... E:\DB2\9.5\SQLLIB\BND\DB2MOVE.BND ... successful!

* LOAD:  table "DB2INST1"."ACT"                
  -Rows read:         18
  -Loaded:            18
  -Rejected:           0
  -Deleted:            0
  -Committed:         18

* LOAD:  table "DB2INST1"."CL_SCHED"           
  -Rows read:          5
  -Loaded:             5
  -Rejected:           0
  -Deleted:            0
  -Committed:          5

--中间很多记录省略

--发现identity表导入失败，需要手工处理
* LOAD:  table "DB2INST1"."T_XFF"              
***  WARNING 3107.  Check message file tab19.msg!
***  SQL Warning!  SQLCODE is  3107
***  SQL3107W  消息文件中至少有一条警告消息。


  -Rows read:          3
  -Loaded:             0
  -Rejected:           3
  -Deleted:            0
  -Committed:          3


Disconnecting from database ... successful!

End time:  Sun Apr 15 23:00:26 2012

7.重新导入identity表

D:\move_s>db2 connect to db_xff

   数据库连接信息

 数据库服务器         = DB2/NT 9.5.0
 SQL 授权标识         = XIFENFEI
 本地数据库别名       = DB_XFF
D:\move_s>DB2 "load from tab19.ixf of ixf modified by identityoverride insert into db2inst1.t_xff"
SQL3501W  由于禁用数据库正向恢复，因此表所驻留的表空间将不被置于备份暂挂状态。

SQL3551W  表至少包含实用程序将覆盖的一个 GENERATED ALWAYS 列。

SQL3109N  实用程序正在开始从文件 "D:\move_s\tab19.ixf" 装入数据。

SQL3500W  在时间 "2012-04-15 23:06:52.393775"，实用程序在开始 "LOAD"。

SQL3150N  PC/IXF 文件中的 H 记录具有产品 "DB2    02.00"，日期 "20120405"和时间 "202132"。

SQL3153N  PC/IXF 文件中的 T 记录具有名称 "tab19.ixf"，限定符 "" 和源 ""。

SQL3519W  开始装入一致点。输入记录数 = "0"。

SQL3520W  “装入一致点”成功。

SQL3110N  实用程序已完成处理。从输入文件读了 "3" 行。

SQL3519W  开始装入一致点。输入记录数 = "3"。

SQL3520W  “装入一致点”成功。

SQL3515W  在时间 "2012-04-15 23:06:52.451619"，实用程序已经完成了 "LOAD"。


读取行数         = 3
跳过行数         = 0
装入行数         = 3
拒绝行数         = 0
删除行数         = 0
落实行数         = 3

D:\move_s>db2 "select * from db2inst1.t_xff"

XID    X_NAME


------ ------------------------------
     1 www.xifenfei.com
     2 XIFENFEI
     3 xifenfei

  3 条记录已选择。

发表在 DB2 | 评论关闭

17813235971

QQ 咨询

分类目录归档：DB2

DB2备份恢复(全备与恢复)

DB2日志参数介绍和修改归档模式

DB2数据迁移之db2lock/db2move

文章分类

最近发表

分类目录归档：DB2

DB2备份恢复(全备与恢复)

DB2日志参数介绍和修改归档模式

DB2数据迁移之db2lock/db2move

标签云

文章分类

最近发表