DB2 Version 10.1 for Linux, UNIX, and Windows

ADMIN_COPY_SCHEMA procedure - Copy a specific schema and its objects

The ADMIN_COPY_SCHEMA procedure is used to copy a specific schema and all objects contained in it. The new target schema objects will be created using the same object names as the objects in the source schema, but with the target schema qualifier. The ADMIN_COPY_SCHEMA procedure can be used to copy tables with or without the data of the original tables.

Syntax

Read syntax diagramSkip visual syntax diagram
>>-ADMIN_COPY_SCHEMA--(--sourceschema--,--targetschema--,------->

>--copymode--,--objectowner--,--sourcetbsp--,--targettbsp--,---->

>--errortabschema--,--errortab--)------------------------------><

The schema is SYSPROC.

Procedure parameters

sourceschema
An input argument of type VARCHAR(128) that specifies the name of the schema whose objects are being copied. The name is case-sensitive.
targetschema
An input argument of type VARCHAR(128) that specifies a unique schema name to create the copied objects into. The name is case-sensitive. If the schema name already exists, the procedure call will fail and return a message indicating that the schema must be removed before invoking the procedure.
copymode
An input argument of type VARCHAR(128) that specifies the mode of copy operation. Valid options are:
  • 'DDL': create empty copies of all supported objects from the source schema.
  • 'COPY': create empty copies of all objects from the source schema, then load each target schema table with data. Load is done in 'NONRECOVERABLE' mode. A backup must be taken after calling the ADMIN_COPY_SCHEMA, otherwise the copied tables will be inaccessible following recovery.
  • 'COPYNO': create empty copies of all objects from the source schema, then load each target schema table with data. Load is done in 'COPYNO' mode.
Note: If copymode is 'COPY' or 'COPYNO', a fully qualified filename, for example 'COPYNO /home/mckeough/loadoutput', can be specified along with the copymode parameter value. When a path is passed in, load messages will be logged to the file indicated. The file name must be writable by the user ID used for fenced routine invocations on the instance. If no path is specified, then load message files will be discarded (default behavior).
objectowner
An input argument of type VARCHAR(128) that specifies the authorization ID to be used as the owner of the copied objects. If NULL, then the owner will be the authorization ID of the user performing the copy operation.
sourcetbsp
An input argument of type CLOB(2 M) that specifies a list of source table spaces for the copy, separated by commas. Delimited table space names are supported. For each table being created, any table space found in this list, and the tables definition, will be converted to the nth entry in the targettbsp list. If NULL is specified for this parameter, new objects will be created using the same table spaces as the source objects use.
targettbsp
An input argument of type CLOB(2 M) that specifies a list of target table spaces for the copy, separated by commas. Delimited table space names are supported. One table space must be specified for each entry in the sourcetbsp list of table spaces. The nth table space in the sourcetbsp list will be mapped to the nth table space in the targettbsp list during DDL replay. It is possible to specify 'SYS_ANY' as the final table space (an additional table space name, that does not correspond to any name in the source list). When 'SYS_ANY' is encountered, the default table space selection algorithm will be used when creating objects (refer to the IN tablespace-name1 option of the CREATE TABLE statement documentation for further information about the selection algorithm). If NULL is specified for this parameter, new objects will be created using the same table spaces as the source objects use.
errortabschema
An input and output argument of type VARCHAR(128) that specifies the schema name of a table containing error information for objects that could not be copied. This table is created for the user by the ADMIN_COPY_SCHEMA procedure in the SYSTOOLSPACE table space. If no errors occurred, then this parameter is NULL on output.
errortab
An input and output argument of type VARCHAR(128) that specifies the name of a table containing error information for objects that could not be copied. This table is created for the user by the ADMIN_COPY_SCHEMA procedure in the SYSTOOLSPACE table space. This table is owned by the user ID that invoked the procedure. If no errors occurred, then this parameter is NULL on output. If the table cannot be created or already exists, the procedure operation fails and an error message is returned. The table must be cleaned up by the user following any call to the ADMIN_COPY_SCHEMA procedure; that is, the table must be dropped in order to reclaim the space it is consuming in SYSTOOLSPACE.
Table 1. ADMIN_COPY_SCHEMA errortab format
Column name Data type Description
OBJECT_SCHEMA VARCHAR(128) object_schema - Object schema monitor element
OBJECT_NAME VARCHAR(128) object_name - Object name monitor element
OBJECT_TYPE VARCHAR(30) objtype - Object type monitor element
SQLCODE INTEGER The error SQLCODE.
SQLSTATE CHAR(5) The error SQLSTATE.
ERROR_TIMESTAMP TIMESTAMP Time of failure for the operation that failed.
STATEMENT CLOB(2 M) DDL for the failing object. If the failure occurred when data was being loaded into a target table, this field contains text corresponding to the load command that failed.
DIAGTEXT CLOB(2 K) Error message text for the failed operation.

Authorization

In order for the schema copy to be successful, the user must have the CREATE_SCHEMA privilege as well as DB2® object-specific privileges.

Example: CREATE_TABLE privilege is needed to copy a table and CREATE_INDEX privilege is needed to copy an index under the ADMIN_COPY_SCHEMA command.

If a table in the source schema is protected by label based access control (LBAC), the user ID must have LBAC credentials that allow creating that same protection on the target table. If copying with data, the user ID must also have LBAC credentials that allow both reading the data from the source table and writing that data to the target table.

EXECUTE privilege on the ADMIN_COPY_SCHEMA procedure is also needed.

Default PUBLIC privilege

In a non-restrictive database, EXECUTE privilege is granted to PUBLIC when the procedure is automatically created.

Example

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

Restrictions

  • Only DDL copymode is supported for HADR databases.
  • XML with COPY or COPY NO is not supported.
  • Using the ADMIN_COPY_SCHEMA procedure with the COPYNO option places the table spaces in which the target database object resides in backup pending state. After the load operation completes, target schema tables are in set integrity pending state, and the ADMIN_COPY_SCHEMA procedure issues a SET INTEGRITY statement to get the tables out of this state. Because the table spaces are already in backup pending state, the SET INTEGRITY statement fails. For information about how to resolve this problem, see "Copying a schema".

Usage notes

  • References to fully qualified objects within the objects being copied will not be modified. The ADMIN_COPY_SCHEMA procedure only changes the qualifying schema of the object being created, not any schema names that appear within SQL expressions for those objects. This includes objects such as generated columns and trigger bodies.
  • This procedure does not support copying the following objects:
    • index extensions
    • nicknames
    • packages
    • typed tables
    • array types
    • user-defined structured types (and their transform functions)
    • typed views
    • jars (Java™ routine archives)
    • staging tables
    • aliases with base objects that do not belong to the same source schema
    • partitioned tables that require table space mapping
  • If one of these objects exists in the schema being copied, the object is not copied but an entry is added to the error table indicating that the object has not been copied.
  • When a replicated table is copied, the new copy of the table does not have subscriptions enabled. The table is re-created as a basic table only.
  • The operation of this procedure requires the existence of the SYSTOOLSPACE table space. This table space is used to hold metadata used by the ADMIN_COPY_SCHEMA procedure as well as error tables returned by this procedure. If the table space does not exist, an error is returned.
  • Statistics for the objects in the target schema are set to default.
  • If a table has a generated identity column, and copymode is either 'COPY' or 'COPYNO', the data values from the source table are preserved during the load.
  • A new catalog entry is created for each external routine, referencing the binary of the original source routine.
  • If a table is in set integrity pending state at the beginning of the copy operation, the data is not loaded into the target table and an entry is logged in errortab indicating that the data was not loaded for that table.
  • If a Load or DDL operation fails, an entry is logged in errortab for any object that was not created. All objects that are successfully created remain. To recover, a manual load can be initiated, or the new schema can be dropped using the ADMIN_DROP_SCHEMA procedure and the ADMIN_COPY_SCHEMA procedure can be called again.
  • During DDL replay, the default schema is overridden to the target schema if it matches the source schema.
  • The function path used to compile a trigger, view or SQL function is the path used to create the source object, with the following exception: if the object's function path contains the source schema name, this entry in the path is modified to the target schema name during DDL replay.
  • Running multiple ADMIN_COPY_SCHEMA procedures will result in deadlocks. Only one ADMIN_COPY_SCHEMA procedure call should be issued at a time. Changes to tables in the source schema during copy processing might mean that the data in the target schema is not identical following a copy operation.
  • Careful consideration should be taken when copying a schema with tables from a table space in a single-partition database partition group to a table space in a multiple-partition database partition group. Unless automatic distribution key selection is preferred, the distribution key should be defined on the tables before the copy schema operation is undertaken. Altering the distribution key can only be done to a table whose table space is associated with a single-partition database partition group.

Transactional considerations

  • If the ADMIN_COPY_SCHEMA procedure is forced to roll back due to a deadlock or lock timeout during its processing, any work performed in the unit of work that called the ADMIN_COPY_SCHEMA procedure is also rolled back.
  • If a failure occurs during the DDL phase of the copy, all the changes that were made to the target schema are rolled back to a savepoint.
  • If copymode is set to 'COPY' or 'COPYNO', the ADMIN_COPY_SCHEMA procedure commits once the DDL phase of the copy is complete, also committing any work done in the unit of work that called the procedure.