DB2 Version 10.1 for Linux, UNIX, and Windows

Example of schema copy using the ADMIN_COPY_SCHEMA procedure

Use the ADMIN_COPY SCHEMA procedure as shown in the following example, to copy a single schema within the same database.

    DB2 "SELECT SUBSTR(OBJECT_SCHEMA,1, 8)                                         
      AS OBJECT_SCHEMA, SUBSTR(OBJECT_NAME,1, 15)                                  
      AS OBJECT_NAME, SQLCODE, SQLSTATE, ERROR_TIMESTAMP, SUBSTR(DIAGTEXT,1, 80)   
      AS DIAGTEXT, SUBSTR(STATEMENT,1, 80)                                         
      AS STATEMENT FROM COPYERRSCH.COPYERRTAB"                                    

    CALL SYSPROC.ADMIN_COPY_SCHEMA('SOURCE_SCHEMA', 'TARGET_SCHEMA',     
      'COPY', NULL, 'SOURCETS1 , SOURCETS2', 'TARGETTS1, TARGETTS2,     
      SYS_ANY', 'ERRORSCHEMA', 'ERRORNAME')

The output from this SELECT statement is shown in the following example:

OBJECT_SCHEMA OBJECT_NAME     SQLCODE     SQLSTATE ERROR_TIMESTAMP               
------------- --------------- ----------- -------- --------------------------    
SALES         EXPLAIN_STREAM         -290 55039    2006-03-18-03.22.34.810346    
                                                                                 
DIAGTEXT                                                                         
-------------------------------------------------------------------------------- 
[IBM][CLI Driver][DB2/LINUXX8664] SQL0290N  Table space access is not allowed.   
                                                                                 
STATEMENT                                                                        
-------------------------------------------------------------------------------- 
set integrity for "SALES  "."ADVISE_INDEX" , "SALES"."ADVISE_MQT" , "SALES"."    
                                                                                
1 record(s) selected.