ADMIN_COPY_SCHEMA procedure - Copy a specific schema and its objects

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.

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.

Syntax

>>-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.

Restrictions

Usage notes

Transactional considerations