Move DB2 schema objects to another schema using ADMIN_COPY_SCHEMA

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