DB2 modification mode

Last Update:2018-12-07 Source: Internet

Author: User

Developer on Alibaba Coud: Build your first app with APIs, SDKs, and tutorials on the Alibaba Cloud. Read more ＞

DB2 Viper provides a new copy schema function. You can use the ADMIN_COPY_SCHEMA stored procedure to copy a schema in the same database, or use the db2move-co COPY option to copy one or more Schemas between two databases. Most schema objects are copied.

Test environment:

OS: Windows XP

DB2 v9.0.0.257

Test Database: DB2 sample

ADMIN_COPY_SCHEMA Stored Procedure
Syntax

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

> -- Copymode --, -- objectowner --, -- sourcetbsp --, -- targettbsp --, ---->

> -- Errortabschema --, -- errortab --) ------------------------------> <

Example:

Call sysproc. ADMIN_COPY_SCHEMA ('source _ scheme', 'target _ scheme ',

'Copy', NULL, 'sourcets1, SOURCETS2 ', 'targetts1, TARGETTS2,

SYS_ANY ', 'errorschema', 'errorname ')

Test:

To execute ADMIN_COPY_SCHEMA, you must first create an existing oolspace tablespace,
(Required oolstmpspace is not required)

Create tablespace using oolspace IN IBMCATGROUP

Managed by database using (FILE 'your oolspace '32 M)

AUTORESIZE YES

EXTENTSIZE 4;

Create user temporary tablespace extends oolstmpspace

Managed by system using ('your oolstmpspace ')

EXTENTSIZE 4;

Then copy schema

Call sysproc. ADMIN_COPY_SCHEMA ('db2admin', 'henry', 'copy', NULL, 'userspaces1', 'userspaces1', 'db2admin', 'errortable ')

Output:

Value of output parameters

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

Parameter Name: ERRORTABSCHEMA

Parameter Value: DB2ADMIN

Parameter Name: ERRORTABNAME

Parameter Value: ERROR

Return Status = 0

An error is reported. Check the ERRORTABNAME table (BTW: if the specified ERRORTABLE exists in CALL ADMIN_COPY_SCHEMA, an error is returned)

Select substr (OBJECT_SCHEMA,), substr (OBJECT_NAME,), substr (OBJECT_TYPE ),

SQLCODE, substr (diagtext,), substr (statement)

From db2admin. errortable;

Output:

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.

These tables use XML fields. Use

Call sysproc. ADMIN_COPY_SCHEMA ('db2admin', 'henry2', 'copyno', NULL, 'userspaces1, IBMDB2SAMPLEREL, ibmdb2samplexml ',

'Userspaces1, IBMDB2SAMPLEREL, ibmdb2samplexml ',

'Db2admin', 'errortable2 ');

No. It is estimated that ADMIN_COPY_SCHEMA calls LOAD, and the LOAD in DB2 Viper has special options for XML. Didn't the designer take it into consideration? XML is the biggest feature of DB2 Viper.

All other tables are copied successfully.

ADMIN_DROP_SCHEMA
Syntax

<! -- [If! Vml] --> <! -- [Endif] --> <! -- [If! Vml] --> <! -- [Endif] -- >>>-ADMIN_DROP_SCHEMA -- (-- schema --, -- dropmode --, ----------------->

> -- Errortabschema --, -- errortab --) ------------------------------> <

Example

Call sysproc. ADMIN_DROP_SCHEMA ('schname', NULL, 'errorschema', 'errorable ')

Test:

Call sysproc. ADMIN_DROP_SCHEMA ('henry', NULL, 'db2admin', 'errortable ')

Output:

Value of output parameters

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

Parameter Name: ERRORTABSCHEMA

Parameter Value :-

Parameter Name: ERRORTAB

Parameter Value :-

Return Status = 0

Successful!

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

Test:

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

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

Content of the COPYSCHEMA.20060727052634.ERR File

1 Schema: HENRY. CATALOG

Type: TABLE

Error Msg: LOAD operation problem