Copying schemas

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 note above. 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 ADMIN_COPY_SCHEMA procedure's attempt to issue a SET INTEGRITY statement will fail.

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 with the exception of 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, indicating that an unsupported object type is detected. The COPY operation will still succeed-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 may 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 recreating procedures, the copy schema operation will first attempt to recreate all procedures, then retry to recreate those that failed (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 recreated 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.