The db2move utility and the ADMIN_COPY_SCHEMA
procedure allow you to quickly make copies of a database schema. Once
a model schema is established, you can use it as a template for creating
new versions.
Procedure
- Use the ADMIN_COPY_SCHEMA procedure to copy a single schema
within the same database.
- Use the db2move utility with the -co
COPY action to copy a single schema or multiple schemas
from a source database to a target database. Most
database objects from the source schema are copied to the target database
under the new schema.
Troubleshooting tips
Both the ADMIN_COPY_SCHEMA
procedure and the db2move utility invoke the LOAD command.
While the load is processing, the table spaces wherein the database
target objects reside are put into backup pending state.
- ADMIN_COPY_SCHEMA procedure
- Using this procedure with the COPYNO option places the table spaces
wherein the target object resides into backup pending state, as described
in the previous note. To get the table space out of the set integrity
pending state, this procedure issues a SET INTEGRITY statement. In
situations where a target table object has referential constraints
defined, the target table is also placed in the set integrity pending
state. Because the table spaces are already in backup pending state,
the attempt by the ADMIN_COPY_SCHEMA procedure to issue a SET INTEGRITY
statement fails.
To resolve this situation, issue a BACKUP
DATABASE command to get the affected table spaces out of
backup pending state. Next, look at the Statement_text column
of the error table generated by this procedure to find a list of tables
in the set integrity pending state. Then issue the SET INTEGRITY statement
for each of the tables listed to take each table out of the set integrity
pending state.
- db2move utility
- This utility attempts to copy all allowable schema objects except
for the following types:
- table hierarchy
- staging tables (not supported by the load utility in multiple
partition database environments)
- jars (Java™ routine archives)
- nicknames
- packages
- view hierarchies
- object privileges (All new objects are created with default authorizations)
- statistics (New objects do not contain statistics information)
- index extensions (user-defined structured type related)
- user-defined structured types and their transform functions
- Unsupported type errors
- If an object of one of the unsupported types is detected in the
source schema, an entry is logged to an error file. The error file
indicates that an unsupported object type is detected. The COPY operation
still succeeds; the logged entry is meant to inform you of objects
not copied by this operation.
- Objects not coupled with schemas
- Objects that are not coupled with a schema, such as table spaces
and event monitors, are not operated on during a copy schema operation.
You should create them on the target database before the copy schema
operation is invoked.
- Replicated tables
- When copying a replicated table, the new copy of the table is
not enabled for replication. The table is recreated as a regular table.
- Different instances
- The source database must be cataloged if it does not reside in
the same instance as the target database.
- SCHEMA_MAP option
- When using the SCHEMA_MAP option to specify a different schema
name on the target database, the copy schema operation will perform
only minimal parsing of the object definition statements to replace
the original schema name with the new schema name. For example, any
instances of the original schema that appear inside the contents of
an SQL procedure are not replaced with the new schema name. Thus the
copy schema operation might fail to recreate these objects. Other
examples might include staging table, result table, materialized query
table. You can use the DDL in the error file to manually recreate
these failed objects after the copy operation completes.
- Interdependencies between objects
- The copy schema operation attempts to recreate objects in an order
that satisfies the interdependencies between these objects. For example,
if a table T1 contains a column that references a user-defined function
U1, then it will recreate U1 before recreating T1. However, dependency
information for procedures is not readily available in the catalogs,
so when re-creating procedures, the copy schema operation will first
attempt to re-create all procedures, then try to re-create those that
failed again (on the assumption that if they depended on a procedure
that was successfully created during the previous attempt, then during
a subsequent attempt they will be re-created successfully). The operation
will continually try to recreate these failed procedures as long as
it is able to successfully recreate one or more during a subsequent
attempt. During every attempt at recreating a procedure, an error
(and DDL) is logged into the error file. You might see many entries
in the error file for the same procedures, but these procedures might
have even been successfully recreated during a subsequent attempt.
You should query the SYSCAT.PROCEDURES table upon completion of the
copy schema operation to determine if these procedures listed in the
error file were successfully recreated.
For more information, see the
ADMIN_COPY_SCHEMA procedure and the db2move utility.