This tool, when used in the EXPORT/IMPORT/LOAD mode,
facilitates the movement of large numbers of tables between DB2® databases located on workstations.
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. When used in the COPY mode, this tool facilitates
the duplication of a schema.
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 correct authorization required by those APIs,
or the request will fail.
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
- Name of the database.
- action
- Must be one of:
- EXPORT
- Exports all tables that meet the filtering criteria in options.
If no options are specified, exports all the tables.
Internal staging information is stored in the db2move.lst file.
- IMPORT
- Imports all tables listed in the internal staging file db2move.lst.
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
a table space named SYSTOOLSPACE when either the ADMIN_COPY_SCHEMA()
stored procedure is used, or when the db2move utility is used with
the -COPY option.
See below for a list of files that are generated during
each action.
- -tc table-definers
- The default is all definers.
This is an EXPORT action only.
If specified, only those tables created by the definers listed with
this option are exported. If not specified, the default is to use
all definers. When specifying multiple definers, they must be separated
by commas; no blanks are allowed between definer IDs. This option
can be used with the -tn table-names option
to select the tables for export.
An asterisk (*) can be used
as a wildcard character that can be placed anywhere in the string.
- -tn table-names
- The default is all user tables.
This is an EXPORT or COPY
action only.
If specified with the EXPORT action, only those
tables whose names match those in the specified string are exported.
If not specified, the default is to use all user tables. When specifying
multiple table names, they must be separated by commas; no blanks
are allowed between table names. Table names should be listed unqualified
and the -sn option should be used to filter schemas.
For
export, an asterisk (*) can be used as a wildcard character that can
be placed anywhere in the string.
If specified with the COPY
action, the -co "MODE" LOAD_ONLY copy-option must
also be specified, and only those tables specified will be repopulated
on the target database. The table names should be listed with their
schema qualifier in the format "schema"."table".
- -sn schema-names
- The default for EXPORT is all schemas (not for COPY).
If specified,
only those tables whose schema names match will be exported or copied.
If multiple schema names are specified, they must be separated by
commas; no blanks are allowed between schema names. Schema names of
less than 8 characters are padded to 8 characters in length.
In
the case of export: If the asterisk wildcard character (*) is used
in the schema names, it will be changed to a percent sign (%) and
the table name (with percent sign) will be used in the LIKE predicate
of the WHERE clause. If not specified, the default is to use all schemas.
If used with the -tn or -tc option, db2move will
only act on those tables whose schemas match the specified schema
names and whose definers match the specified definers. A schema name fred has
to be specified -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
- The default is all table spaces.
This is an EXPORT action
only. If this option is specified, only those tables that reside
in the specified table space will be exported. If the asterisk wildcard
character (*) is used in the table space name, it will be changed
to a percent sign (%) and the table name (with percent sign) will
be used in the LIKE predicate in the WHERE clause. If the -ts option
is not specified, the default is to use all table spaces. If multiple
table space names are specified, they must be separated by commas;
no blanks are allowed between table space names. Table space names
less than 8 characters are padded to 8 characters in length. For
example, a table space name mytb has to be specified -ts
my*b* instead of -sn my*b when using the
asterisk.
- -tf filename
-
If specified with EXPORT action, only those tables whose
names match exactly those in the specified file are exported. If not
specified, the default is to use all user tables. The tables should
be listed one per line, and each table should be fully qualified.
Wildcard characters are not allowed in the strings. Here is an example
of the contents of a file:
"SCHEMA1"."TABLE NAME1"
"SCHEMA NAME77"."TABLE155"
If specified with
the COPY action, the -co "MODE" LOAD_ONLY copy-option must
also be specified, and only those tables specified in the file will
be repopulated on the target database. The table names should be listed
with their schema qualifier in the format "schema"."table".
- -io import-option
- The default is REPLACE_CREATE. See "IMPORT command
options CREATE and REPLACE_CREATE are deprecated" for limitations
of import create function.
Valid options are: INSERT, INSERT_UPDATE,
REPLACE, CREATE, and REPLACE_CREATE.
- -lo load-option
- The default is INSERT.
Valid options are: INSERT and REPLACE.
- -co
- When the db2move action is COPY, the following -co
follow-on options will be available:
- "TARGET_DB db name [USER userid USING password]"
- Allows the user to specify the name of the target database and
the user/password. (The source database user/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's not specified, then db2move will prompt
for the password information. The reason for prompting is for security
reasons discussed below. 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"
-
- 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"
- Allows user to rename schema when copying to target. Provides
a list of the source-target schema mapping, separated by commas, surrounded
by brackets. e.g schema_map ((s1, t1), (s2, t2)). This would mean
objects from schema s1 will be copied to schema t1 on the target;
objects from schema s2 will be copied to schema t2 on the target.
The default, and recommended, target schema name is the source schema
name. The reason for this is db2move will not attempt
to modify the schema for any qualified objects within object bodies.
Therefore, using a different target schema name may lead to problems
if there are qualified objects within the object body.
For example:
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-dependant 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 recreated in
T2 on the target database and any objects found in T2 on the source
database will be recreated 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.
- -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.
Examples
- To export all tables in the SAMPLE database (using default values
for all options), issue:
db2move sample export
- To export all tables created by userid1 or user
IDs LIKE us%rid2, and with the name tbname1 or
table names LIKE %tbname2, issue:
db2move sample export -tc userid1,us*rid2 -tn tbname1,*tbname2
- To import all tables in the SAMPLE database (LOB paths D:\LOBPATH1 and C:\LOBPATH2 are
to be searched for LOB files; this example is applicable to Windows operating systems only), issue:
db2move sample import -l D:\LOBPATH1,C:\LOBPATH2
- To load all tables in the SAMPLE database (/home/userid/lobpath subdirectory
and the tmp subdirectory are to be searched for LOB
files; this example is applicable to Linux and UNIX systems
only), issue:
db2move sample load -l /home/userid/lobpath,/tmp
- To import all tables in the SAMPLE database in REPLACE mode using
the specified user ID and password, issue:
db2move sample import -io replace -u userid -p password
- To duplicate schema schema1 from source database dbsrc to
target database dbtgt, issue:
db2move dbsrc COPY -sn schema1 -co TARGET_DB dbtgt USER myuser1 USING mypass1
- To duplicate schema schema1 from source database dbsrc to
target database dbtgt, rename the schema to newschema1 on
the target, and map source table space ts1 to ts2 on
the target, issue:
db2move dbsrc COPY -sn schema1 -co TARGET_DB dbtgt USER myuser1 USING mypass1
SCHEMA_MAP ((schema1,newschema1)) TABLESPACE_MAP ((ts1,ts2), SYS_ANY))
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/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 logretain 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 on Load 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.
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:
DB2 Version 9.7 for Linux, UNIX, and Windows