DB2 修改模式

最後更新：2018-12-07 來源：互聯網

上載者：User

創建阿里雲帳戶，並獲得超過 40 款產品的免費試用版；而企業帳戶則可以享有總值 $1200 的免費試用版。立即註冊！

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