db2逻辑整库迁移





—————————Step 1———————————

On product environment

Listing 1. 导出表格数据

$ mkdir -p /db2backup/ctrold/exp

$ cd /db2backup/ctrold/exp

$ db2move ctrold export

—————————Step 2—————————

On product environment

Listing 2. 导出DDL语句

$ db2look -d ctrold -e -l -a -o db2look.sql

– Generate statistics for all creators

– Creating DDL for table(s)

– Output is sent to file: db2look.sql

—————————Step 3—————————

On target environment

Listing 3. 传到目标环境

$ mkdir -p /db2backup/ctrold/exp

$ cd /db2backup/ctrold/exp

ftp 10.44.65.74

ftp> cd /db2backup/ctrold/exp

ftp> prompt

Interactive mode Off .

ftp> bin

200 Type set to I.

ftp> mget *

—————————Step 4—————————

On target environment

Listing 3.修改db2look.sql文件

CONNECT TO IDM;

 

————————————

– DDL Statements for BUFFERPOOLS –

————————————

 

CREATE BUFFERPOOL "BP16K"  SIZE 200 PAGESIZE 16384;

 

CREATE BUFFERPOOL "IDMBPTMP"  SIZE 5000 PAGESIZE 16384;

 

CONNECT RESET;

CONNECT TO IDM;

 

————————————

– DDL Statements for TABLESPACES –

————————————

 

CREATE REGULAR TABLESPACE IDMTBSUSR IN DATABASE PARTITION GROUP IBMDEFAULTGROUP PAGESIZE 16384 MANAGED BY DATABASE

         USING (FILE '/db2/idm/oausrdata/IDMTBSUSR01.DAT'449000)

         EXTENTSIZE 16

         PREFETCHSIZE 16

         BUFFERPOOL BP16K

         OVERHEAD 10.500000

         TRANSFERRATE 0.140000

         DROPPED TABLE RECOVERY ON;

 

CREATE LARGE TABLESPACE IDMTBSUSRLARGE IN DATABASE PARTITION GROUP IBMDEFAULTGROUP PAGESIZE 4096 MANAGED BY DATABASE

         USING (FILE '/db2/idm/oausrdata/IDMTBSUSR02.DAT'256000)

         EXTENTSIZE 8

         PREFETCHSIZE 8

         BUFFERPOOL IBMDEFAULTBP

         OVERHEAD 10.500000

         TRANSFERRATE 0.140000;

 

CREATE USER TEMPORARY TABLESPACE IDMTBSTMP IN DATABASE PARTITION GROUP IBMDEFAULTGROUP PAGESIZE 16384 MANAGED BY DATABASE

         USING (FILE '/db2/idm/oatmpdata/IDMTBSTMP.DAT'64000)

         EXTENTSIZE 16

         PREFETCHSIZE 16

         BUFFERPOOL IDMBPTMP

         OVERHEAD 10.500000

         TRANSFERRATE 0.140000;

 

CREATE REGULAR TABLESPACE SYSTOOLSPACE IN DATABASE PARTITION GROUP IBMCATGROUP PAGESIZE 4096 MANAGED BY SYSTEM

         USING ('/db2/idm/SYSTOOLSPACE')

         EXTENTSIZE 32

         PREFETCHSIZE AUTOMATIC

         BUFFERPOOL IBMDEFAULTBP

         OVERHEAD 12.670000

         TRANSFERRATE 0.180000

         DROPPED TABLE RECOVERY ON;

 

在目标库下面建立相应的表空间oausrdataoatmpdata,SYSTOOLSPACE

 

 

导入ddl语句

$ db2 -tvf db2look.sql > /db2backup/ctrold/exp/db2look.out

$ db2 -tvf db2look.sql > /db2backup/ctrold/exp/db2look2.out

导入数据

$ db2move ctrold load

—————————Step 5—————————

Listing 5. 对比

product

db2 => select count(*),type from syscat.tables where tabschema='db2inst1' GROUP BY TYPE

1           TYPE

———– —-

          8 N  

         27 T  

          3 V  

  3 record(s) selected.

 

target

db2 => select count(*),type from syscat.tables where tabschema='db2inst1' GROUP BY TYPE

1           TYPE

———– —-

         27 T  

          3 V  

  2 record(s) selected.

 

 

TYPE    CHAR(1)        Type of object.

    A = Alias

    G = Global temporary table

    H = Hierarchy table

    L = Detached table

    N = Nickname

    S = Materialized query table

    T = Table (untyped)

    U = Typed table

    V = View (untyped)

    W = Typed view

以上的结果表明table 类型为 N = Nickname没有创建  nickname类似于oracledblink

db2 => CREATE WRAPPER "DRDA" LIBRARY 'libdb2drda.a' OPTIONS (DB2_FENCED  'N')

DB21034E  The command was processed as an SQL statement because it was not a

valid Command Line Processor command.  During SQL processing it returned:

SQL20076N  The instance for the database is not enabled for the specified

action or operation.  Reason code = "1".  SQLSTATE=0A502

db2 => ? SQL20076N

解决方法:

db2 update database manager configuration using federated yes

重启实例

db2stop

db2start

创建nickname

CREATE WRAPPER "DRDA" LIBRARY 'libdb2drda.a' OPTIONS (DB2_FENCED  'N')

CREATE SERVER "ROEEESERVER" TYPE DB2/UDB VERSION '8.1' WRAPPER "DRDA" AUTHORIZATION "db2inst1" PASSWORD "db2inst1" OPTIONS(DBNAME  'ROEEEOLD');

CREATE USER MAPPING FOR db2inst1 SERVER "ROEEESERVER" OPTIONS (REMOTE_AUTHID  'db2inst1',REMOTE_PASSWORD  'db2inst1');

 

 

 

select 'select count(1) from '||tabschema||'.'||TABNAME from syscat.tables where TABSCHEMA='WAVESET'

 

select count(1)from WAVESET .ACCOUNT

select count(1)from WAVESET .ACCTATTR

select count(1)from WAVESET .ACCTCHANGE

select count(1)from WAVESET .ATTRIBUTE

select count(1)from WAVESET .ENTATTR

select count(1)from WAVESET .ENTCHANGE

select count(1)from WAVESET .ENTITLE

select count(1)from WAVESET .EXPLAIN_ARGUMENT

select count(1)from WAVESET .EXPLAIN_INSTANCE

select count(1)from WAVESET .EXPLAIN_OBJECT

select count(1)from WAVESET .EXPLAIN_OPERATOR

select count(1)from WAVESET .EXPLAIN_PREDICATE

select count(1)from WAVESET .EXPLAIN_STATEMENT

select count(1)from WAVESET .EXPLAIN_STREAM

select count(1)from WAVESET .LOG

select count(1)from WAVESET .LOGATTR

select count(1)from WAVESET .OBJCHANGE

select count(1)from WAVESET .OBJECT

select count(1)from WAVESET .OPERATETIME

select count(1)from WAVESET .ORG

select count(1)from WAVESET .ORGATTR

select count(1)from WAVESET .ORGCHANGE

select count(1)from WAVESET .ORG_ID_NAME

select count(1)from WAVESET .ORG_SAP

select count(1)from WAVESET .ORG_SAPTOIDM

select count(1)from WAVESET .ORG_SAPTOIDM_TEST

select count(1)from WAVESET .ORG_SAP_IDM

select count(1)from WAVESET .QATTR

select count(1)from WAVESET .QCHANGE

select count(1)from WAVESET .QUEUE

select count(1)from WAVESET .ROLEATTR

select count(1)from WAVESET .ROLECHANGE

select count(1)from WAVESET .ROLEOBJ

select count(1)from WAVESET .SAPORIDMORG

select count(1)from WAVESET .SAPORIDMUSER

select count(1)from WAVESET .SLOGATTR

select count(1)from WAVESET .SYSLOG

select count(1)from WAVESET .TASK

select count(1)from WAVESET .TASKATTR

select count(1)from WAVESET .TASKCHANGE

select count(1)from WAVESET .TEMPSAPUSER

select count(1)from WAVESET .TESTORG

select count(1)from WAVESET .USERATTR

select count(1)from WAVESET .USERCHANGE

select count(1)from WAVESET .USEROBJ

select count(1)from WAVESET .USER_SAP

select count(1)from WAVESET .USER_SAP_BACKUP

select count(1)from WAVESET .USER_SAP_COMPARE

select count(1)from WAVESET .USER_SAP_IDM

select count(1)from WAVESET .USER_SAP_IDM_TEST

select count(1)from WAVESET .USER_TEST

select count(1)from WAVESET .USER_TEST2

select count(1)from WAVESET .ZJYC_ORG

select count(1)from WAVESET .ZJYC_PROPERTY

select count(1)from WAVESET .ZY_PROPERTY

 

 

 

可能会遇到

 

db2 => select count(1)from WAVESET .EXPLAIN_ARGUMENT

 

1         

———–

SQL0668N  Operation not allowed for reason code "1" on table

"WAVESET.EXPLAIN_ARGUMENT".  SQLSTATE=57016

 

 

解决方法:

db2 "select 'db2 set integrity for oatest.'||TABNAME||' immediate checked' from syscat.tables where TABSCHEMA='OATEST' and STATUS='C'"

 

[db2inst1@devsvr2 oatest0303]$ db2 "select 'db2 set

 integrity for oatest.'||TABNAME||' immediate checked'

 from syscat.tables where TABSCHEMA='OATEST' and STATUS='C'"

 

 1

 

 —————————————————————–

 db2 set integrity for oatest.CAL_AUTH immediate checked

 

……

 

db2 set integrity for oatest.UM_USER immediate checked

 

 

 81 条记录已选择。

 

 

 

 执行这 81 条语句,语句有可能报错

 

 主要命令:

 

db2 set integrity for oatest. CAL_AUTH immediate checked

 

 [db2inst1@devsvr2 oatest0303]$ db2 set integrity for oatest.CAL_AUTH immediate checked

 

 DB20000I SQL 命令成功完成。

 

[db2inst1@devsvr2 oatest0303]$ db2 set integrity for oatest.UM_USER immediate checked

 

 DB20000I SQL 命令成功完成。

 

…………

 

 

 

 

如果报错,再反复执行这些语句直到下面的语句结果返回 0 条记录

 

 

 

 

[db2inst1@devsvr2 oatest0303]$ db2 "select 'db2 set integrity

 for oatest.'||TABNAME||' immediate checked' from syscat.tables

 where TABSCHEMA='OATEST' and STATUS='C'"

 

 

 1

 

 —————————————————————

 

 0 条记录已选择。

 




Related posts

coded by nessus
分享:  DeliciousGReader鲜果豆瓣CSDN网摘
Trackback

no comment untill now

Add your comment now

无觅相关文章插件