美创科技技术社区

注册

 

发新话题 回复该主题

db2逻辑迁移并更改pagesize [复制链接]

1#

查询生产端pagesize

生产端pagesize

[db2inst1@backup data]$ db2 get db cfg for test | grep page

Database code page                                      = 1208

Database page size                                      = 4096

1、导出ddl语句:

[db2inst1@backup data]$ db2look -d xuytest -e -a -l -x -o test.sql

– Generate statistics for all creators

– Creating DDL for table(s)

– Output is sent to file: test.sql

– Binding package automatically …

– Bind is successful

– Binding package automatically …

– Bind is successful

2、导出dMl语句:

[db2inst1@backup temp]$ db2move xuytest export

Application code page not determined, using ANSI codepage 1208

*****  DB2MOVE  *****

Action:  EXPORT

Start time:  Sun Jan 22 21:45:05 2017

Connecting to database XUYTEST … successful!  Server : DB2 Common Server V10.1.0

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

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

Disconnecting from database … successful!

End time:  Sun Jan 22 21:45:05 2017

[db2inst1@backup temp]$ ls

db2move.lst  EXPORT.out

3、传送备份文件至备端

4、建立数据库并设置pagesize

[db2inst1@test ~]$ db2 create database xuytest pagesize 8192

DB20000I  The CREATE DATABASE command completed successfully.

5、导入ddl语句:

[db2inst1@test ~]$ db2 -tvf /home/db2inst1/test.sql > /home/db2inst1/temp/a.log

CONNECT TO XUYTEST

   Database Connection Information

Database server        = DB2/LINUXX8664 10.1.0

SQL authorization ID   = DB2INST1

Local database alias   = XUYTEST

ALTER STOGROUP "IBMSTOGROUP" OVERHEAD 6.725000 DEVICE READ RATE 100.000000 DATA TAG NONE SET AS DEFAULT

DB20000I  The SQL command completed successfully.

ALTER TABLESPACE "SYSCATSPACE" PREFETCHSIZE AUTOMATIC OVERHEAD INHERIT NO FILE SYSTEM CACHING AUTORESIZE YES TRANSFERRATE INHERIT

DB20000I  The SQL command completed successfully.

ALTER TABLESPACE "SYSCATSPACE" USING STOGROUP "IBMSTOGROUP"

DB20000I  The SQL command completed successfully.

ALTER TABLESPACE "TEMPSPACE1" PREFETCHSIZE AUTOMATIC OVERHEAD INHERIT FILE SYSTEM CACHING TRANSFERRATE INHERIT

DB20000I  The SQL command completed successfully.

ALTER TABLESPACE "USERSPACE1" PREFETCHSIZE AUTOMATIC OVERHEAD INHERIT NO FILE SYSTEM CACHING AUTORESIZE YES TRANSFERRATE INHERIT DATA

TAG INHERIT

DB20000I  The SQL command completed successfully.

ALTER TABLESPACE "USERSPACE1" USING STOGROUP "IBMSTOGROUP"

DB20000I  The SQL command completed successfully.

COMMIT WORK

DB20000I  The SQL command completed successfully.

CONNECT RESET

DB20000I  The SQL command completed successfully.

TERMINATE

DB20000I  The TERMINATE command completed successfully.

6、导入dml语句:

[db2inst1@test ~]$ db2move xuytest load >load.log

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

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

7、尝试连接数据库

[db2inst1@test ~]$ db2 connect to xuytest

   Database Connection Information

Database server        = DB2/LINUXX8664 10.1.0

SQL authorization ID   = DB2INST1

Local database alias   = XUYTEST

8、查看目标端pagesize

[db2inst1@test ~]$ db2 get db cfg for xuytest | grep page

Database code page                                      = 1208


Database page size                                      = 8192

分享 转发
TOP
发新话题 回复该主题