[转]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_dbtarget schema_map
"((BAR,FOO))" -u userid -p password
测试：
C:\tmp\copyschema>db2move sample COPY -sn DB2ADMIN -co target_db copydbschema_
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
Error Msg : LOAD operation problem
DDL : SQLCODE = -901 SQLSTATE = 58004 TOKENS =
2 Schema : HENRY.CUSTOMER
Type : TABLE
Error Msg : LOAD operation problem
DDL : SQLCODE = -901 SQLSTATE = 58004 TOKENS =
3 Schema : HENRY.PRODUCT
Type : TABLE
Error Msg : LOAD operation problem
DDL : SQLCODE = -901 SQLSTATE = 58004 TOKENS =
4 Schema : HENRY.PURCHASEORDER
Type : TABLE
Error Msg : LOAD operation problem
DDL : SQLCODE = -901 SQLSTATE = 58004 TOKENS =
5 Schema : HENRY.SUPPLIERS
Type : TABLE
Error Msg : LOAD operation problem
DDL : SQLCODE = -901 SQLSTATE = 58004 TOKENS =
LOADTABLE.20060727052634.ERR文件的内容
"DB2ADMIN"."CATALOG"
"DB2ADMIN"."CUSTOMER"
"DB2ADMIN"."PRODUCT"
"DB2ADMIN"."PURCHASEORDER"
"DB2ADMIN"."SUPPLIERS"
db2move支持XML数据类型也有问题呀。
参考资料
DB2信息中心的原文：
Use the ADMIN_COPY_SCHEMA procedure to copy a single schema within the same database or use the db2move utility with the -co COPY action to copy a single schema or multiple schemas from a source database to a target database. Most database objects from the source schema are copied to the target database under the new schema.
Restrictions
* The db2move utility attempts to successfully copy all allowable schema objects with the exception of the following types:
o table hierarchy
o staging tables (not supported by the load utility in multiple partition database environments)
o jars (Java(TM) routine archives)
o nicknames
o packages
o view hierarchies
o object privileges (All new objects are created with default authorizations)
o statistics (New objects do not contain statistics information)
o index extensions (user-defined structured type related)
o user-defined structured types and their transform functions
更多限制信息请参考DB2信息中心
页:
[1]