This tool, when used in the EXPORT, IMPORT,
or LOAD mode, facilitates the movement of large numbers
of tables between DB2® databases
located on workstations. When used in the COPY mode,
this tool facilitates the duplication of a schema.
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 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 the following section 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, userid
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"
-
- 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. For example: 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-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.
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 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.
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