[转]DB2 修改模式 - 失落

DB2 Viper提供了一个copy schema的新功能。可以使用ADMIN_COPY_SCHEMA存储过程在同一个数据库中copy一个schema，也可以使用db2move的-co COPY选择在两个数据库间copy一个或多个schema。schema的大部分对象都会被拷贝。

测试环境：

OS: Windows XP

DB2 v9.0.0.257

测试数据库: DB2带的sample

ADMIN_COPY_SCHEMA存储过程
Syntax

>>-ADMIN_COPY_SCHEMA--(--sourceschema--,--targetschema--,------->

>--copymode--,--objectowner--,--sourcetbsp--,--targettbsp--,---->

>--errortabschema--,--errortab--)------------------------------><

Example:

CALL SYSPROC.ADMIN_COPY_SCHEMA('SOURCE_SCHEMA', 'TARGET_SCHEMA',

'COPY', NULL, 'SOURCETS1 , SOURCETS2', 'TARGETTS1, TARGETTS2,

SYS_ANY', 'ERRORSCHEMA', 'ERRORNAME')

测试：

要执行ADMIN_COPY_SCHEMA先要创建SYSTOOLSPACE表空间，
(SYSTOOLSTMPSPACE不是必须的)

CREATE TABLESPACE SYSTOOLSPACE IN IBMCATGROUP

MANAGED BY DATABASE USING ( FILE 'SYSTOOLSPACE' 32 M )

AUTORESIZE YES

EXTENTSIZE 4;

CREATE USER TEMPORARY TABLESPACE SYSTOOLSTMPSPACE

MANAGED BY SYSTEM USING ( 'SYSTOOLSTMPSPACE' )

EXTENTSIZE 4;

然后copy schema

CALL SYSPROC.ADMIN_COPY_SCHEMA('DB2ADMIN', 'HENRY', 'COPY', NULL, 'USERSPACES1', 'USERSPACES1', 'DB2ADMIN', 'ERRORTABLE')

输出：

Value of output parameters

--------------------------

Parameter Name : ERRORTABSCHEMA

Parameter Value : DB2ADMIN

Parameter Name : ERRORTABNAME

Parameter Value : ERROR

Return Status = 0

报错了。检查ERRORTABNAME表（BTW: 如果CALL ADMIN_COPY_SCHEMA时指定的ERRORTABLE存在会报错）

select substr(OBJECT_SCHEMA,1,10),substr(OBJECT_NAME,1,20),substr(OBJECT_TYPE,1,10),

SQLCODE,substr(diagtext,1,30),substr(statement,1,50)

from db2admin.errortable;

输出：

1 2 3 SQLCODE 5 6

---------- -------------------- ---------- ----------- ------------------------------ --------------------------------------------------

HENRY CATALOG TABLE -3529 LOAD operation problem SQLCODE = -3529 SQLSTATE = TOKENS = LOADXML

HENRY CUSTOMER TABLE -3529 LOAD operation problem SQLCODE = -3529 SQLSTATE = TOKENS = LOADXML

HENRY PRODUCT TABLE -3529 LOAD operation problem SQLCODE = -3529 SQLSTATE = TOKENS = LOADXML

HENRY PURCHASEORDER TABLE -3529 LOAD operation problem SQLCODE = -3529 SQLSTATE = TOKENS = LOADXML

HENRY SUPPLIERS TABLE -3529 LOAD operation problem SQLCODE = -3529 SQLSTATE = TOKENS = LOADXML

5 record(s) selected.

这些表里都用XML字段。用

CALL SYSPROC.ADMIN_COPY_SCHEMA('DB2ADMIN', 'HENRY2', 'COPYNO', NULL, 'USERSPACES1,IBMDB2SAMPLEREL,IBMDB2SAMPLEXML',

'USERSPACES1,IBMDB2SAMPLEREL,IBMDB2SAMPLEXML',

'DB2ADMIN', 'ERRORTABLE2');

也不行。估计是ADMIN_COPY_SCHEMA调用了LOAD，而在DB2 Viper里LOAD对XML有特殊的选项，难道设计者没考虑进去？支持XML可是DB2 Viper里最大的特性呀。

其他表都拷贝成功。

ADMIN_DROP_SCHEMA
Syntax

>>-ADMIN_DROP_SCHEMA--(--schema--,--dropmode--,----------------->

>--errortabschema--,--errortab--)------------------------------><

Example

CALL SYSPROC.ADMIN_DROP_SCHEMA('SCHNAME', NULL, 'ERRORSCHEMA', 'ERRORTABLE')

测试：

CALL SYSPROC.ADMIN_DROP_SCHEMA('HENRY', NULL, 'DB2ADMIN', 'ERRORTABLE')

输出：

Value of output parameters

--------------------------

Parameter Name : ERRORTABSCHEMA

Parameter Value : -

Parameter Name : ERRORTAB

Parameter Value : -

Return Status = 0

成功！

db2move
To copy a schema using the command line processor (CLP), use the following syntax:

db2move <dbname> COPY -co <COPY-options>

-u <userid> -p <password>

The following is an example of a db2move -co COPY operation that copies schema BAR into FOO from the sample database to the target database:

db2move sample COPY -sn BAR -co target_db target schema_map

"((BAR,FOO))" -u userid -p password

测试：

C:\tmp\copyschema>db2move sample COPY -sn DB2ADMIN -co target_db copydb schema_

map "((DB2ADMIN,HENRY))" -u db2admin -p password

Application code page not determined, using ANSI codepage 1252

***** DB2MOVE *****

Action: COPY

Start time: Thu Jul 27 05:26:34 2006

All schema names matching: DB2ADMIN;

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

Copy schema DB2ADMIN to HENRY on the target database COPYDB

Create DMT : "SYSTOOLS"."DMT_44c886cb37536"

Start Load Phase :

db2move finished successfully, however there are errors reported in the

error file, COPYSCHEMA.20060727052634.ERR. Please refer to this file to correct

any failures.

Files generated:

-----------------

COPYSCHEMA.20060727052634.msg

LOADTABLE.20060727052634.ERR

LOADTABLE.20060727052634.MSG

COPYSCHEMA.20060727052634.ERR

Please delete these files when they are no longer needed.

End time: Thu Jul 27 05:27:15 2006

COPYSCHEMA.20060727052634.ERR文件的内容

1 Schema : HENRY.CATALOG

Type : TABLE