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
>>-ADMIN_COPY_SCHEMA--(--sourceschema--,--targetschema--,------->
>--copymode--,--objectowner--,--sourcetbsp--,--targettbsp--,---->
>--errortabschema--,--errortab--)------------------------------><
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 formatColumn 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.