dba-db2.com
Move DB2 schema objects to another schema using ADMIN_COPY_SCHEMA

28 June,2013 by Jack Vamvas

Question: How can I move DB2  objects from one schema to another schema in the same database? When I migrated a database to a new DB2 instance with new logon , I couldn’t access the tables.

Answer: What you would really like to do is rename the schema. The ADMIN_COPY_SCHEMA stored procedure manages the process.   Under the covers ,  ADMIN_COPY_SCHEMA is actually copying the objects to the other schema , rather than just renaming the schema.

This test example, illustrates the basic steps. There are other more advanced options, check documentation for more details.   Read the DB2 user manual for usage notes. Also, very important , always take a FULL BACKUP prior to using ADMIN_COPY_SCHEMA.

This example copies schema ‘MYSCHEM1’ to ‘MYSCHEM2’

 

db2 "call SYSPROC.ADMIN_COPY_SCHEMA ('MYSCHEM1','MYSCHEM2','COPY', NULL, NULL, NULL, 'COPYSCHEMA', 'COPYERROR')"


Value of output parameters
--------------------------
Parameter Name : ERRORTABSCHEMA
Parameter Value : COPYSCHEMA

Parameter Name : ERRORTABNAME
Parameter Value : COPYERROR

Return Status = 0

 

Using  ADMIN_COPY_SCHEMA requires systoolspace tablespace. If it doesn’t exist create it,before using the ADMIN_COPY_SCHEMA  procedure. An example with AUTOMATIC STORAGE and expected output

 

db2 "CREATE TABLESPACE SYSTOOLSPACE MANAGED BY AUTOMATIC STORAGE EXTENTSIZE 4"
DB20000I The SQL command completed successfully

Once you’re satisfied with the new schema , remove the old schema, by using  the ADMIN_DROP_SCHEMA stored procedure . Read more on DB2 - ADMIN_DROP_SCHEMA to drop a schema - DBA DB2 . Here is an example:

 

db2 "CALL SYSPROC.ADMIN_DROP_SCHEMA('TEST', NULL, 'ERRORSCHEMA', 'ERRORTABLE')"

Value of output parameters
--------------------------
Parameter Name : ERRORTABSCHEMA
Parameter Value : ERRORSCHEMA

Parameter Name : ERRORTAB
Parameter Value : ERRORTABLE

Return Status = 0

 If you any problems or any other feedback , please contact me

 

Read More

DB2 Database objects overviews - DBA DB2

DB2 SET CURRENT SCHEMA - DBA DB2

 

Author: Jack Vamvas(http://www.dba-db2.com)

Subscribe to DBA-DB2.com

Latest posts delivered to you daily

Delivered by FeedBurner

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been saved. Comments are moderated and will not appear until approved by the author. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment

Comments are moderated, and will not appear until the author has approved them.


dba-db2.com | DB2 Performance Tuning | DBA DB2:Everything | FAQ | Contact | Copyright