Restarting a failed copy schema operation

Errors occurring during a db2move COPY operation can be handled in various ways depending on the type of object being copied, or the phase during which the COPY operation failed (that is, either the recreation of objects phase, or the loading of data phase).

About this task

The db2move utility reports errors and messages to the user using message and error files. Copy schema operations use the COPYSCHEMA_timestamp.MSG message file, and the COPYSCHEMA_timestamp.err error file. These files are created in the current working directory. The current time is appended to the file name to ensure uniqueness of the files. It is up to the user to delete these message and error files when they are no longer required.

Note: It is possible to have multiple db2move instances running simultaneously. The COPY option does not return any SQLCODES. This is consistent with db2move behavior.

The type of object being copied can be categorized as one of two types: physical objects and business objects.

A physical object refers to an object that physically resides in a container, such as tables, indexes and user-defined structured types. A business object refers to cataloged objects that do not reside in containers, such as views, user-defined structured types (UDTs), and aliases.

Errors occurring during the recreation of a physical object cause the utility to roll back, whereas, errors during the recreation of a logical object do not.

Procedure

To restart the copy schema operation:

After addressing the issues causing the load operations to fail (described in the error file), reissue the db2move-COPY command.

Use the -tf parameter with the LOADTABLE.err file name to specify which tables to copy and populate with data.

For example:

    db2move sourcedb COPY -tf LOADTABLE.err -co TARGET_DB mytarget_db 
      -mode load_only

You can also input the table names manually using the -tn parameter. For example:

    db2move sourcedb COPY -tn "FOO"."TABLE1","FOO 1"."TAB 444", 
      -co TARGET_DB mytarget_db -mode load_only

Note: The load_only mode requires inputting at least one table using the -tn or -tf parameter.

Examples

Example 1: Schema copy errors related to physical objects

Failures which occur during the recreation of physical objects on the target database, are logged in the error file COPYSCHEMA_timestamp.err. For each failing object, the error file contains information such as object name, object type, DDL text, time stamp, and a string formatted sqlca (sqlca field names, followed by their data values).

Sample output for the COPYSCHEMA_timestamp.err error file:

  	1. schema: FOO.T1
  	Type:      TABLE
	  Error Msg: SQL0104N An unexpected token 'FOO.T1'...
	  Timestamp: 2005-05-18-14.08.35.65
	  DDL:       create view FOO.v1 

	  2. schema:  FOO.T3
	  Type:      TABLE
	  Error Msg: SQL0204N FOO.V1 is an undefined name.
	  Timestamp: 2005-05-18-14.08.35.68
	  DDL:       create table FOO.T3

If any errors creating physical objects are logged at the end of the recreation phase and before attempting the load phase, the db2move utility fails and an error is returned. All object creation on the target database is rolled back, and all internally created tables are cleaned up on the source database. In order to gather all possible errors into the error file, the rollback occurs at the end of the recreation phase after attempting to re-create each object, rather than after the first failure. This allows you the opportunity to fix any problems before restarting the db2move operation. If there are no failures, the error file is deleted.

Example 2: Schema copy errors related to business objects

Failures that occur during the recreation of business objects on the target database, do not cause the db2move utility to fail. Instead, these failures are logged in the COPYSCHEMA_timestamp.err error file. Upon completion of the db2move utility, you can examine the failures, address any issues, and manually re-create each failed object (the DDL is provided in the error file for convenience).

If an error occurs when db2move is attempting to repopulate table data using the load utility, the db2move utility does not fail. Rather, generic failure information is logged to the COPYSCHEMA_timestamp.err file (for example, the object name, object type, DDL text, time stamp, and sqlca), and the fully qualified name of the table is logged into another file, LOADTABLE_timestamp.err. Each table is listed per line to satisfy the db2move -tf parameter format, similar to the following:

	"FOO"."TABLE1"
	"FOO 1"."TAB 444"

Example 3: Other types of db2move failures

Internal operations such as memory errors, or file system errors can cause the db2move utility to fail.

If the internal operation failure occurs during the DDL recreation phase, all successfully created objects are rolled back from the target schema. All internally created tables such as the DMT table and the db2look table, are cleaned up on the source database.

If the internal operation failure occurs during the load phase, all successfully created objects remain on the target schema. All tables that experience a failure during a load operation, and all tables which are not yet loaded, are logged in the LOADTABLE.err error file. You can then issue the db2move COPY command using the LOADTABLE.err as discussed in Example 2. If the db2move utility abends (for example a system crash occurs, the utility traps, or the utility is killed), then the information regarding which tables still must be loaded is lost. In this case, you can drop the target schema using the ADMIN_DROP_SCHEMA procedure and reissue the db2move COPY command.

Regardless of what error you might encounter during an attempted copy schema operation, you always have the option of dropping the target schema using the ADMIN_DROP_SCHEMA procedure. You can then reissue the db2move COPY command.