db2move - Database movement tool command

The tool queries the system catalog tables for a particular database and compiles a list of all user tables. It then exports these tables in PC/IXF format. The PC/IXF files can be imported or loaded to another local DB2 database on the same system, or can be transferred to another workstation platform and imported or loaded to a DB2 database on that platform. Tables with structured type columns are not moved when this tool is used.

Authorization

This tool calls the DB2 export, import, and load APIs, depending on the action requested by the user. Therefore, the requesting user ID must have the authorization that the APIs require, or the request fails.

Command syntax

                            .----------------------------.   
                            V                            |   
>>-db2move--dbname--action----+------------------------+-+-----><
                              +- -tc--table_definers---+     
                              +- -tn--table_names------+     
                              +- -sn--schema_names-----+     
                              +- -ts--tablespace_names-+     
                              +- -tf--filename---------+     
                              +- -io--import_option----+     
                              +- -lo--load_option------+     
                              +- -co--copy_option------+     
                              +- -l--lobpaths----------+     
                              +- -u--userid------------+     
                              +- -p--password----------+     
                              '- -aw-------------------'     

Command parameters

dbname

Specifies the name of the database.

action

Specifies an action. Values are as follows:

EXPORT

Exports all tables that meet the filtering criteria according to the option specified. If you do not specify an option then all tables are exported. Internal staging information is stored in the db2move.lst file.

IMPORT

Imports all tables listed in the db2move.lst internal staging file. Use the -io option for IMPORT specific actions.

LOAD

Loads all tables listed in the internal staging file db2move.lst. Use the -lo option for LOAD specific actions.

COPY

Duplicates schemas into a target database. The target database must be a local database. Use the -sn option to specify one or more schemas. See the -co option for COPY specific options. Use the -tn or -tf option to filter tables in LOAD_ONLY mode. You must use the SYSTOOLSPACE table space if you use the ADMIN_COPY_SCHEMA() stored procedure or if you use the db2move command with the COPY parameter.

-tc table_definers

Specifies one or more table definers (creators).

This parameter applies only to the EXPORT action. If you specify the -tc parameter, only those tables that were created by the specified definers are exported. If you do not specify this parameter, all definers are used. If you specify multiple definers, you must separate them with commas; no blanks are allowed between definer IDs. You can use this parameter with the -tn table_names parameter to select the tables for export.

You can use an asterisk (*) as a wildcard character anywhere in the string.

-tn table_names

Specifies one or more table names. This parameter applies only to the EXPORT and COPY actions.

If you specify the -tn parameter with the EXPORT action, only those tables whose names match those in the specified string are exported. If you do not specify this parameter, all user tables are used. If you specify multiple table names, you must separate them with commas; no blanks are allowed between table names. Table names must be listed unqualified. To filter schemas, you should use the -sn parameter.

For export, you can use an asterisk (*) as a wildcard character anywhere in the string.

If you specify the -tn parameter with the COPY action, you must also specify the -co "MODE" LOAD_ONLY copy_option parameter, and only the specified tables are repopulated in the target database. The table names must be listed with their schema qualifiers in the format "schema"."table".

-sn schema_names

Specifies one or more schema names. If you specify this parameter, only those tables whose schema names match those in the specified string are exported or copied. The default for the EXPORT action is all schemas. The default does not apply to the COPY action.

If you specify multiple schema names, you must separate them with commas; no blanks are allowed between schema names. Schema names of fewer than 8 characters are padded to 8 characters in length.

In the case of the EXPORT action, if you use the asterisk (*) wildcard character in the schema names, it is changed to a percent sign (%), and the table name (with the percent sign) is used in the LIKE predicate of the WHERE clause. If you use the -sn parameter with the -tn or -tc parameter, the db2move command acts on only those tables whose schemas match the specified schema names or whose definers match the specified definers. A schema name fred has to be specified as -sn fr*d* instead of -sn fr*d when using an asterisk.

Note: The -sn option is not supported on DB2 for z/OS.

-ts tablespace_names

Specifies a list of table space names. This parameter applies only to the EXPORT action.

If you specify the -ts parameter, only those tables in the specified table space are exported. If you use the asterisk (*) wildcard character in the table space name, it is changed to a percent sign (%), and the table name (with the percent sign) is used in the LIKE predicate in the WHERE clause. If you do not specify the -ts parameter, all table spaces are used. If you specify multiple table space names, you must separate them with commas; no blanks are allowed between table space names. Table space names with fewer than 8 characters are padded to 8 characters in length. To specify a table space name mytb, it has to be specified as-ts my*b* instead of -sn my*b when using an asterisk.

-tf filename

Specifies a file name. This parameter applies only to the EXPORT and COPY actions. If you specify the -tf parameter with the EXPORT action, only those tables whose names match those in the specified file are exported. In the file, you should list one table per line, and you should fully qualify each table name. Wildcard characters are not allowed in the strings. Sample file contents are as follows:

   "SCHEMA1"."TABLE NAME1"
   "SCHEMA NAME77"."TABLE155"

If you do not specify the -tf parameter, all user tables are used.

If you specify this parameter with the COPY action, you must also specify the -co "MODE" LOAD_ONLY copy_option parameter, and only those tables that you specify in the file are repopulated in the target database. In the file, you should list the table names with their schema qualifier in the format "schema"."table".

-io import_option

Specifies options for the IMPORT action. Valid options are INSERT, INSERT_UPDATE, REPLACE, CREATE, and REPLACE_CREATE. The default is REPLACE_CREATE. For limitations of the import create function, see "IMPORT command options CREATE and REPLACE_CREATE are deprecated" .

-lo load_option

Specifies options for the LOAD action. Valid options are INSERT and REPLACE. The default is INSERT.

-co

Specifies options for the COPY action.

"TARGET_DB db name [USER userid USING password]"

Specifies the name of the target database, user ID, and password. (The source database userid and password can be specified using the existing -p and -u options). The USER USING clause is optional. If USER specifies a userid, then the password must either be supplied following the USING clause, or if it is not specified, then db2move will prompt for the password information. The reason for prompting is for security reasons discussed in the following section. TARGET_DB is a mandatory option for the COPY action. The TARGET_DB cannot be the same as the source database and must be a local database. The ADMIN_COPY_SCHEMA procedure can be used for copying schemas within the same database. The COPY action requires inputting at least one schema (-sn) or one table (-tn or -tf).

Running multiple db2move commands to copy schemas from one database to another will result in deadlocks. Only one db2move command should be issued at a time. Changes to tables in the source schema during copy processing may mean that the data in the target schema is not identical following a copy.

"MODE"

This option is optional.

DDL_AND_LOAD

Creates all supported objects from the source schema, and populates the tables with the source table data. This is the default option.

DDL_ONLY

Creates all supported objects from the source schema, but does not repopulate the tables.

LOAD_ONLY

Loads all specified tables from the source database to the target database. The tables must already exist on the target. The LOAD_ONLY mode requires inputting at least one table using the -tn or -tf option.

This is an optional option that is only used with the COPY action.

"SCHEMA_MAP"

Renames a schema when copying to a target. This option is optional.

To use this option, provide a list of the source-target schema mappings, separated by commas, surrounded by parentheses, for example, schema_map ((s1, t1), (s2, t2)). In this case, objects from schema s1 are copied to schema t1 on the target, and objects from schema s2 are copied to schema t2 on the target. The default and recommended target schema name is the source schema name. The reason is that the db2move command does not attempt to modify the schema of any qualified objects within object bodies. Therefore, using a different target schema name might lead to problems if there are qualified objects within the object body.

Consider the following example, which creates a view called v1:

create view FOO.v1 as 'select c1 from FOO.t1'

In this case, copy of schema FOO to BAR, v1 will be regenerated as:

create view BAR.v1 as 'select c1 from FOO.t1'

This will either fail since schema FOO does not exist on the target database, or have an unexpected result due to FOO being different than BAR. Maintaining the same schema name as the source will avoid these issues. If there are cross dependencies between schemas, all inter-dependent schemas must be copied or there may be errors copying the objects with the cross dependencies.

For example:

create view FOO.v1 as 'select c1 from BAR.t1'

In this case, the copy of v1 will either fail if BAR is not copied as well, or have an unexpected result if BAR on the target is different than BAR from the source. db2move will not attempt to detect cross schema dependencies.

This is an optional option that is only used with the COPY action.

If a target schema already exists, the utility will fail. Use the ADMIN_DROP_SCHEMA procedure to drop the schema and all objects associated with that schema.

"NONRECOVERABLE"

This option allows the user to override the default behavior of the load to be done with COPY-NO. With the default behavior, the user will be forced to take backups of each table space that was loaded into. When specifying this NONRECOVERABLE keyword, the user will not be forced to take backups of the table spaces immediately. It is, however, highly recommended that the backups be taken as soon as possible to ensure the newly created tables will be properly recoverable. This is an optional option available to the COPY action.

"OWNER"

Allows the user to change the owner of each new object created in the target schema after a successful COPY. The default owner of the target objects will be the connect user; if this option is specified, ownership will be transferred to the new owner. This is an optional option available to the COPY action.

"TABLESPACE_MAP"

The user may specify table space name mappings to be used instead of the table spaces from the source system during a copy. This will be an array of table space mappings surrounded by brackets. For example, tablespace_map ((TS1, TS2),(TS3, TS4)). This would mean that all objects from table space TS1 will be copied into table space TS2 on the target database and objects from table space TS3 will be copied into table space TS4 on the target. In the case of ((T1, T2),(T2, T3)), all objects found in T1 on the source database will be re-created in T2 on the target database and any objects found in T2 on the source database will be re-created in T3 on the target database. The default is to use the same table space name as from the source, in which case, the input mapping for this table space is not necessary. If the specified table space does not exist, the copy of the objects using that table space will fail and be logged in the error file.

The user also has the option of using the SYS_ANY keyword to indicate that the target table space should be chosen using the default table space selection algorithm. In this case, db2move will be able to choose any available table space to be used as the target. The SYS_ANY keyword can be used for all table spaces, example: tablespace_map SYS_ANY. In addition, the user can specify specific mappings for some table spaces, and the default table space selection algorithm for the remaining. For example, tablespace_map ((TS1, TS2),(TS3, TS4), SYS_ANY). This indicates that table space TS1 is mapped to TS2, TS3 is mapped to TS4, but the remaining table spaces will be using a default table space target. The SYS_ANY keyword is being used since it's not possible to have a table space starting with "SYS".

This is an optional option available to the COPY action.

"PARALLEL" number_of_threads

Specify this option to have the load operations for the tables in the schema(s) spread across a number of threads. The value range for number_of_threads is 0-16

• If PARALLEL is not specified, no threads are used and the load operations are performed serially.
• If PARALLEL is specified without a number of threads, the db2move utility will choose an appropriate value.
• If PARALLEL is specified and number_of_threads is provided, the specified number of threads is used. If number_of_threads is 0 or 1, the load operation is performed serially.
• The maximum value that can be specified for number_of_threads is 16.

This is an optional option available to the COPY action.

-l lobpaths

For IMPORT and EXPORT, if this option is specified, it will be also used for XML paths. The default is the current directory.

This option specifies the absolute path names where LOB or XML files are created (as part of EXPORT) or searched for (as part of IMPORT or LOAD). When specifying multiple paths, each must be separated by commas; no blanks are allowed between paths. If multiple paths are specified, EXPORT will use them in round-robin fashion. It will write one LOB document to the first path, one to the second path, and so on up to the last, then back to the first path. The same is true for XML documents. If files are not found in the first path (during IMPORT or LOAD), the second path will be used, and so on.

-u userid

The default is the logged on user ID.

Both user ID and password are optional. However, if one is specified, the other must be specified. If the command is run on a client connecting to a remote server, user ID and password should be specified.

-p password

The default is the logged on password. Both user ID and password are optional. However, if one is specified, the other must be specified. When the -p option is specified, but the password not supplied, db2move will prompt for the password. This is done for security reasons. Inputting the password through command line creates security issues. For example, a ps -ef command would display the password. If, however, db2move is invoked through a script, then the passwords will have to be supplied. If the command is issued on a client connecting to a remote server, user ID and password should be specified.

-aw

Allow Warnings. When -aw is not specified, tables that experience warnings during export are not included in the db2move.lst file (although that table's .ixf file and .msg file are still generated). In some scenarios (such as data truncation) the user might want to allow such tables to be included in the db2move.lst file. Specifying this option allows tables which receive warnings during export to be included in the .lst file.

Usage notes

• When copying one or more schemas into a target database the schemas must be independent of each other. If not, some of the objects might not be copied successfully into the target database
• Loading data into tables containing XML columns is only supported for the LOAD and not for the COPY action. The workaround is to manually issue the IMPORT or EXPORT commands, or use the db2move Export and db2move Import behaviour. If these tables also contain GENERATED ALWAYS identity columns, data cannot be imported into the tables.
• A db2move EXPORT, followed by a db2move IMPORT or db2move LOAD, facilitates the movement of table data. It is necessary to manually move all other database objects associated with the tables (such as aliases, views, or triggers) as well as objects that these tables may depend on (such as user-defined types or user-defined functions).
• If the IMPORT action with the CREATE or REPLACE_CREATE option is used to create the tables on the target database (both options are deprecated and may be removed in a future release), then the limitations outlined in "Imported table re-creation" are imposed. If unexpected errors are encountered during the db2move import phase when the REPLACE_CREATE option is used, examine the appropriate tabnnn.msg message file and consider whether the errors might be the result of the limitations on table creation.
• Tables that contain GENERATED ALWAYS identity columns cannot be imported or loaded using db2move. You can, however, manually import or load these tables. For more information, see "Identity column load considerations" or "Identity column import considerations".
• When export, import, or load APIs are called by db2move, the FileTypeMod parameter is set to lobsinfile. That is, LOB data is kept in files that are separate from the PC/IXF file, for every table.
• The LOAD command must be run locally on the machine where the database and the data file reside.
• When using db2move LOAD and logarchmeth1 is enabled for the database (the database is recoverable):
  • If the NONRECOVERABLE option is not specified, then db2move will invoke the db2Load API using the default COPY NO option, and the table spaces where the loaded tables reside are placed in the Backup Pending state upon completion of the utility (a full database or table space backup is required to take the table spaces out of the Backup Pending state).
  • If the NONRECOVERABLE option is specified, the table spaces are not placed in backup-pending state, however if rollforward recovery is performed later, the table is marked inaccessible and it must be dropped. For more information aboutLoad recoverability options, see "Options for improving load performance".
• Performance for the db2move command with the IMPORT or LOAD actions can be improved by altering the default buffer pool, IBMDEFAULTBP, and by updating the configuration parameters sortheap, util_heap_sz, logfilsiz, and logprimary.
• When running data movement utilities such as export and db2move, the query compiler might determine that the underlying query will run more efficiently against an MQT than the base table or tables. In this case, the query will execute against a refresh deferred MQT, and the result of the utilities might not accurately represent the data in the underlying table.
• The db2move command is not available with DB2 clients. If you issue the db2move command from a client machine, you will receive a db2move is not recognized as an internal or external command, operable program or batch file error message. To avoid this issue, you can issue the db2move command directly on the server.
• The db2move COPY command and the ADMIN_COPY_SCHEMA procedure perform similar tasks. The ADMIN_COPY_SCHEMA procedure copies schemas within the same database, and the db2copy COPY command copies from one database to another. Many of the usage notes, behaviors, and restrictions that are covered in ADMIN_COPY_SCHEMA procedure - Copy a specific schema and its objects, also apply to the db2copy COPY command.
• Row and Column Access Control (RCAC) applies for any SQL access to tables protected with row permissions and column masks. RCAC includes SQL in applications and utilities like IMPORT and EXPORT. For example, when exporting data from a table that is protected with row permissions and column masks that use the EXPORT utility, only the data that you are authorized to access are exported. If your intent is to export the full content of the table, you need to make sure the SECADM grants you the proper authorization.

Files Required/Generated When Using EXPORT

• Input: None.
• Output:

  EXPORT.out

  The summarized result of the EXPORT action.

  db2move.lst

  The list of original table names, their corresponding PC/IXF file names (tabnnn.ixf), and message file names (tabnnn.msg). This list, the exported PC/IXF files, and LOB files (tabnnnc.yyy) are used as input to the db2move IMPORT or LOAD action.

  tabnnn.ixf

  The exported PC/IXF file of a specific table.

  tabnnn.msg

  The export message file of the corresponding table.

  tabnnnc.yyy

  The exported LOB files of a specific table.

  nnn is the table number. c is a letter of the alphabet. yyy is a number ranging from 001 to 999.

  These files are created only if the table being exported contains LOB data. If created, these LOB files are placed in the lobpath directories. There are a total of 26,000 possible names for the LOB files.

  system.msg

  The message file containing system messages for creating or deleting file or directory commands. This is only used if the action is EXPORT, and a LOB path is specified.

Files Required/Generated When Using IMPORT

• Input:

  db2move.lst

  An output file from the EXPORT action.

  tabnnn.ixf

  An output file from the EXPORT action.

  tabnnnc.yyy

  An output file from the EXPORT action.

• Output:

  IMPORT.out

  The summarized result of the IMPORT action.

  tabnnn.msg

  The import message file of the corresponding table.

Files Required/Generated When Using LOAD

• Input:

  db2move.lst

  An output file from the EXPORT action.

  tabnnn.ixf

  An output file from the EXPORT action.

  tabnnnc.yyy

  An output file from the EXPORT action.

• Output:

  LOAD.out

  The summarized result of the LOAD action.

  tabnnn.msg

  The LOAD message file of the corresponding table.

Files Required/Generated When Using COPY

• Input: None
• Output:

  COPYSCHEMA.msg

  An output file containing messages generated during the COPY operation.

  COPYSCHEMA.err

  An output file containing an error message for each error encountered during the COPY operation, including DDL statements for each object which could not be re-created on the target database.

  LOADTABLE.msg

  An output file containing messages generated by each invocation of the Load utility (used to repopulate data on the target database).

  LOADTABLE.err

  An output file containing the names of tables that either encountered a failure during Load or still need to be populated on the target database. See the "Restarting a failed copy schema operation" topic for more details.

  These files are timestamped and all files that are generated from one run will have the same timestamp.