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.
CALL SYSPROC.ADMIN_COPY_SCHEMA('SOURCE_SCHEMA', 'TARGET_SCHEMA',
'COPY', NULL, 'SOURCETS1 , SOURCETS2', 'TARGETTS1, TARGETTS2, SYS_ANY',
'ERRORSCHEMA', 'ERRORNAME')If the operation is successful, the schema is copied, and the output indicates the error
information table is not
populated.
Value of output parameters
--------------------------
Parameter Name : ERRORTABSCHEMA
Parameter Value : -
Parameter Name : ERRORTABNAME
Parameter Value : -
Return Status = 0
If the operation fails, the schema is not copied, and the output indicates the error information
table is
populated.
Value of output parameters
--------------------------
Parameter Name : ERRORTABSCHEMA
Parameter Value : ERRORSCHEMA
Parameter Name : ERRORTABNAME
Parameter Value : ERRORNAME
Return Status = 0
Use a SELECT statement as shown in the following example, to query the error information table
and determine why the ADMIN_COPY_SCHEMA operation
failed.
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 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.