用法:db2move <dbname> <action> [options]
<dbname> 是数据库的名称.
<action> 动作包括export、import、load、copy:
EXPORT - 根据 [options],导出相应的表
(如果options为空,则导出所有的表).
表的映射信息存放在 'db2move.lst'.
导出信息存放在 'EXPORT.msg'.
IMPORT - 从 'db2move.lst'文件中,读取表和文件的映射关系,并全部导入数据库中.
输出信息存放在 'IMPORT.msg'..
可以使用-io,设置导入的模式,具体使用如下.
LOAD - 从 'db2move.lst'文件中,读取表和文件的映射关系,并全部导入数据库中.
输出信息存放在 'LOAD.msg'.
可以使用-lo,设置导入的模式,具体使用如下.
COPY - 将一个或多个模式,复制到目标数据库中.
使用 -sn 设置一个或多个schema.
使用 -co 设置目标库、映射关系等设置.
在LOAD_ONLY模式下, -tn 或 -tf 过滤复制的表.
复制信息存放在 'COPY.<timestamp>.msg'
错误信息存放在 'COPY.<timestamp>.err'
加载信息存放在 'LOADTABLE.<timestamp>.msg'
表加载错误信息存放在 'LOADTABLE.<timestamp>.err'
[options] 具体使用如下:
Option Description Use for 备注
描述 Action
------------------------------------------------------------------------
-tc <value> 表创建者. 映射 EXPORT 逗号分隔,
SYSCAT.TABLES.DEFINER字段 通配符(*)有效.
-tn <value> 表名. 映射 EXPORT 逗号分隔,
SYSCAT.TABLES.TABNAME COPY 通配符(*)对EXPORT有效,对COPY无效.
-sn <value> 模式名. 映射 EXPORT, 逗号分隔,
SYSCAT.TABLES.TABSCHEMA COPY 通配符(*)对EXPORT有效,对COPY无效.
-ts <value> 表空间名. 映射 EXPORT 逗号分隔,
SYSCAT.TABLES.TBSPACE 通配符(*)有效.
-tf <file> 将需要导出的表放置在配 EXPORT 多个表如下配置(模式名、双引号不可缺少):
置文件中 COPY "MYSCHEMA1"."TAB1"
"MYSCHEMA2"."TAB2"
-aw 允许存在警告 EXPORT 当遇到警告时,允许继续导出数据.
-io <value> IMPORT 的导入方式. IMPORT 五个导入方式分别是: INSERT,
默认是 REPLACE_CREATE INSERT_UPDATE, REPLACE,
CREATE, REPLACE_CREATE
-lo <value> LOAD 的导入方式. LOAD 两个导入方式分别是: INSERT,
默认是 INSERT REPLACE
-l <path> lob文件的路径. EXPORT, Comma seperated list,
默认是 cwd IMPORT, no space characters
(当前路径) LOAD
-u <value> 用户名. ALL
默认是当前用户
-p <value> 密码. ALL
-co <opt> COPY 的options. COPY 看如下的设置.
TARGET_DB <dbname> [USER <userid> [USING <passwd>]
设置目标数据库名称、用户名、密码. (默认用户名/密码 是 当前的 用户名/密码).
MODE <value>
模式分别为 DDL_AND_LOAD (default), DDL_ONLY, or LOAD_ONLY.
SCHEMA_MAP <value>
目标表在目标数据库上重命名模式名. 如, 重命名模式名 'srcsch1' to 'tgtsch1', 'srcsch2' to 'tgtsch2',
语句为:"((srcsch1,tgtsch1), (scrsch2,tgtsch2))".
TABLESPACE_MAP <value>
目标表在目标数据库上重命名表空间. 如, 在源数据库表空间'TS1'下的所有表,
导入目标数据库表空间'TS2'中,其他的表空间使用默认的表空间,
语句为: "((TS1,TS2), SYS_ANY)".
NONRECOVERABLE
To override the default Load recoverability action COPY-NO, to
NONRECOVERABLE.
OWNER <owner-name>
在目标数据库上,使用设定的用户名创建表.
举例:
1) 导出所有表:
>db2move dbname EXPORT
2) 导出所有表,并按表名过滤'mytab*',按用户名过滤'user1',lob文件放置在/home/use/lob:
>db2move dbname EXPORT -tn mytab* -tc USER1 -l /home/use/lob
3) 使用import导入所有表:
>db2move dbname IMPORT
4) 使用load导入所有表:
>db2move dbname LOAD
5) Use db2move to Load (append) all previously exported tables, where
lobfiles reside in /bigfs/mydir:
>db2move dbname LOAD -lo INSERT -l /bigfs/mydir
6) 将源数据库dbsrc下的schema1模式,复制到目标数据库dbtgt中 :
>db2move dbsrc COPY -sn schema1 -co TARGET_DB dbtgt USER myuser1 USING mypass1
7) 将源数据库dbsrc下的schema1模式,复制到目标数据库dbtgt中,在目标数据库上,
重命名模式名为'newschema1',表模式名由'ts1'改为'ts2':
>db2move dbsrc COPY -sn schema1 -co TARGET_DB dbtgt
USER myuser1 USING mypass1 SCHEMA_MAP ((schema1,newschema1))
TABLESPACE_MAP ((ts1,ts2), SYS_ANY))
原文如下:
db2move <dbname> <action> [options]
<dbname> is the name of the database.
<action> is one of:
EXPORT - Export all tables that meet the filtering criteria in [options]
(If no [options] specified, export all tables).
Internal staging information is stored in file 'db2move.lst'.
Messages are stored in 'EXPORT.msg'.
IMPORT - Import all tables listed in the staging file 'db2move.lst'.
Messages are stored in 'IMPORT.msg'..
Use the -io option for IMPORT specific actions.
LOAD - Load all tables listed in the staging file 'db2move.lst'.
Messages are stored in 'LOAD.msg'.
Use the -lo option for LOAD specific actions.
COPY - Duplicate a schema(s) into a target 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.
Messages are stored in 'COPY.<timestamp>.msg'
Error only messages in 'COPY.<timestamp>.err'
Load messages in 'LOADTABLE.<timestamp>.msg'
List of tables that failed Load in 'LOADTABLE.<timestamp>.err'
[options] are one or more of:
Option Description Use for Notes
Action
------------------------------------------------------------------------
-tc <value> Table Definers. Filter on EXPORT Comma seperated list,
SYSCAT.TABLES.DEFINER Wildcard (*) allowed.
-tn <value> Table Names. Filter on EXPORT Comma seperated list,
SYSCAT.TABLES.TABNAME COPY Wildcard (*) allowed for
EXPORT (not COPY).
-sn <value> Schema Names. Filter on EXPORT, Comma seperated list,
SYSCAT.TABLES.TABSCHEMA COPY Wildcard (*) allowed for
EXPORT (not COPY).
-ts <value> Tablespace Names. Filter on EXPORT Comma seperated list,
SYSCAT.TABLES.TBSPACE Wildcard (*) allowed.
-tf <file> Fully qualified list of EXPORT one table per line,like:
table names in <file> COPY "MYSCHEMA1"."TAB1"
"MY SCHEMA2"."TAB2"
-aw Allow warnings EXPORT Don't disclude tables
that encounter warnings.
-io <value> IMPORT specific actions. IMPORT One of: INSERT,
Default is REPLACE_CREATE INSERT_UPDATE, REPLACE,
(see docs for limitations CREATE, REPLACE_CREATE
of Import create function)
-lo <value> LOAD specific options. LOAD One of: INSERT,
Default is INSERT REPLACE
-l <path> Path to lobfiles. EXPORT, Comma seperated list,
Default is cwd IMPORT, no space characters
LOAD
-u <value> Userid to connect to db. ALL
Default is current user
-p <value> Password to connect to db. ALL
-co <opt> COPY specific options. COPY See options listed below.
TARGET_DB <dbname> [USER <userid> [USING <passwd>]
This mandatory option specifies the target database (and optional
user/passwd) where the schema(s) will be created. (Default
user/passwd are current user/passwd).
MODE <value>
The mode is one of DDL_AND_LOAD (default), DDL_ONLY, or LOAD_ONLY.
SCHEMA_MAP <value>
To rename the schema on the target db. For example, to rename schema
'srcsch1' to 'tgtsch1', and schema 'srcsch2' to 'tgtsch2', use:
"((srcsch1,tgtsch1), (scrsch2,tgtsch2))".
TABLESPACE_MAP <value>
To override tablespaces on the target db. For example, to
recreate all tables that reside in TS1 on the source db, into TS2 on
the target db, and all other tablespaces to use the default tablespace
on the target, use: "((TS1,TS2), SYS_ANY)".
NONRECOVERABLE
To override the default Load recoverability action COPY-NO, to
NONRECOVERABLE.
OWNER <owner-name>
To override the owner of each new object created on the target db.
Examples:
1) Use db2move to Export all tables:
>db2move dbname EXPORT
2) Use db2move to Export all tables, filtering on table-name with 'mytab*'
and table-definer with 'USER1', and lobs in /bigfs/mydir/:
>db2move dbname EXPORT -tn mytab* -tc USER1 -l /bigfs/mydir/
3) Use db2move to Import (recreate and repopulate) all previously exported
tables:
>db2move dbname IMPORT
4) Use db2move to Load (repopulate) all previously exported tables:
>db2move dbname LOAD
5) Use db2move to Load (append) all previously exported tables, where
lobfiles reside in /bigfs/mydir:
>db2move dbname LOAD -lo INSERT -l /bigfs/mydir
6) Use db2move to duplicate schema 'schema1' from source database 'dbsrc'
to target database 'dbtgt':
>db2move dbsrc COPY -sn schema1 -co TARGET_DB dbtgt
USER myuser1 USING mypass1
7) Use db2move to duplicate schema 'schema1' from source database 'dbsrc'
to target database 'dbtgt', rename the schema to 'newschema1' on the
target, and map source tablespace 'ts1' to 'ts2' on the target:
>db2move dbsrc COPY -sn schema1 -co TARGET_DB dbtgt
USER myuser1 USING mypass1 SCHEMA_MAP ((schema1,newschema1))
TABLESPACE_MAP ((ts1,ts2), SYS_ANY))
<dbname> 是数据库的名称.
<action> 动作包括export、import、load、copy:
EXPORT - 根据 [options],导出相应的表
(如果options为空,则导出所有的表).
表的映射信息存放在 'db2move.lst'.
导出信息存放在 'EXPORT.msg'.
IMPORT - 从 'db2move.lst'文件中,读取表和文件的映射关系,并全部导入数据库中.
输出信息存放在 'IMPORT.msg'..
可以使用-io,设置导入的模式,具体使用如下.
LOAD - 从 'db2move.lst'文件中,读取表和文件的映射关系,并全部导入数据库中.
输出信息存放在 'LOAD.msg'.
可以使用-lo,设置导入的模式,具体使用如下.
COPY - 将一个或多个模式,复制到目标数据库中.
使用 -sn 设置一个或多个schema.
使用 -co 设置目标库、映射关系等设置.
在LOAD_ONLY模式下, -tn 或 -tf 过滤复制的表.
复制信息存放在 'COPY.<timestamp>.msg'
错误信息存放在 'COPY.<timestamp>.err'
加载信息存放在 'LOADTABLE.<timestamp>.msg'
表加载错误信息存放在 'LOADTABLE.<timestamp>.err'
[options] 具体使用如下:
Option Description Use for 备注
描述 Action
------------------------------------------------------------------------
-tc <value> 表创建者. 映射 EXPORT 逗号分隔,
SYSCAT.TABLES.DEFINER字段 通配符(*)有效.
-tn <value> 表名. 映射 EXPORT 逗号分隔,
SYSCAT.TABLES.TABNAME COPY 通配符(*)对EXPORT有效,对COPY无效.
-sn <value> 模式名. 映射 EXPORT, 逗号分隔,
SYSCAT.TABLES.TABSCHEMA COPY 通配符(*)对EXPORT有效,对COPY无效.
-ts <value> 表空间名. 映射 EXPORT 逗号分隔,
SYSCAT.TABLES.TBSPACE 通配符(*)有效.
-tf <file> 将需要导出的表放置在配 EXPORT 多个表如下配置(模式名、双引号不可缺少):
置文件中 COPY "MYSCHEMA1"."TAB1"
"MYSCHEMA2"."TAB2"
-aw 允许存在警告 EXPORT 当遇到警告时,允许继续导出数据.
-io <value> IMPORT 的导入方式. IMPORT 五个导入方式分别是: INSERT,
默认是 REPLACE_CREATE INSERT_UPDATE, REPLACE,
CREATE, REPLACE_CREATE
-lo <value> LOAD 的导入方式. LOAD 两个导入方式分别是: INSERT,
默认是 INSERT REPLACE
-l <path> lob文件的路径. EXPORT, Comma seperated list,
默认是 cwd IMPORT, no space characters
(当前路径) LOAD
-u <value> 用户名. ALL
默认是当前用户
-p <value> 密码. ALL
-co <opt> COPY 的options. COPY 看如下的设置.
TARGET_DB <dbname> [USER <userid> [USING <passwd>]
设置目标数据库名称、用户名、密码. (默认用户名/密码 是 当前的 用户名/密码).
MODE <value>
模式分别为 DDL_AND_LOAD (default), DDL_ONLY, or LOAD_ONLY.
SCHEMA_MAP <value>
目标表在目标数据库上重命名模式名. 如, 重命名模式名 'srcsch1' to 'tgtsch1', 'srcsch2' to 'tgtsch2',
语句为:"((srcsch1,tgtsch1), (scrsch2,tgtsch2))".
TABLESPACE_MAP <value>
目标表在目标数据库上重命名表空间. 如, 在源数据库表空间'TS1'下的所有表,
导入目标数据库表空间'TS2'中,其他的表空间使用默认的表空间,
语句为: "((TS1,TS2), SYS_ANY)".
NONRECOVERABLE
To override the default Load recoverability action COPY-NO, to
NONRECOVERABLE.
OWNER <owner-name>
在目标数据库上,使用设定的用户名创建表.
举例:
1) 导出所有表:
>db2move dbname EXPORT
2) 导出所有表,并按表名过滤'mytab*',按用户名过滤'user1',lob文件放置在/home/use/lob:
>db2move dbname EXPORT -tn mytab* -tc USER1 -l /home/use/lob
3) 使用import导入所有表:
>db2move dbname IMPORT
4) 使用load导入所有表:
>db2move dbname LOAD
5) Use db2move to Load (append) all previously exported tables, where
lobfiles reside in /bigfs/mydir:
>db2move dbname LOAD -lo INSERT -l /bigfs/mydir
6) 将源数据库dbsrc下的schema1模式,复制到目标数据库dbtgt中 :
>db2move dbsrc COPY -sn schema1 -co TARGET_DB dbtgt USER myuser1 USING mypass1
7) 将源数据库dbsrc下的schema1模式,复制到目标数据库dbtgt中,在目标数据库上,
重命名模式名为'newschema1',表模式名由'ts1'改为'ts2':
>db2move dbsrc COPY -sn schema1 -co TARGET_DB dbtgt
USER myuser1 USING mypass1 SCHEMA_MAP ((schema1,newschema1))
TABLESPACE_MAP ((ts1,ts2), SYS_ANY))
原文如下:
db2move <dbname> <action> [options]
<dbname> is the name of the database.
<action> is one of:
EXPORT - Export all tables that meet the filtering criteria in [options]
(If no [options] specified, export all tables).
Internal staging information is stored in file 'db2move.lst'.
Messages are stored in 'EXPORT.msg'.
IMPORT - Import all tables listed in the staging file 'db2move.lst'.
Messages are stored in 'IMPORT.msg'..
Use the -io option for IMPORT specific actions.
LOAD - Load all tables listed in the staging file 'db2move.lst'.
Messages are stored in 'LOAD.msg'.
Use the -lo option for LOAD specific actions.
COPY - Duplicate a schema(s) into a target 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.
Messages are stored in 'COPY.<timestamp>.msg'
Error only messages in 'COPY.<timestamp>.err'
Load messages in 'LOADTABLE.<timestamp>.msg'
List of tables that failed Load in 'LOADTABLE.<timestamp>.err'
[options] are one or more of:
Option Description Use for Notes
Action
------------------------------------------------------------------------
-tc <value> Table Definers. Filter on EXPORT Comma seperated list,
SYSCAT.TABLES.DEFINER Wildcard (*) allowed.
-tn <value> Table Names. Filter on EXPORT Comma seperated list,
SYSCAT.TABLES.TABNAME COPY Wildcard (*) allowed for
EXPORT (not COPY).
-sn <value> Schema Names. Filter on EXPORT, Comma seperated list,
SYSCAT.TABLES.TABSCHEMA COPY Wildcard (*) allowed for
EXPORT (not COPY).
-ts <value> Tablespace Names. Filter on EXPORT Comma seperated list,
SYSCAT.TABLES.TBSPACE Wildcard (*) allowed.
-tf <file> Fully qualified list of EXPORT one table per line,like:
table names in <file> COPY "MYSCHEMA1"."TAB1"
"MY SCHEMA2"."TAB2"
-aw Allow warnings EXPORT Don't disclude tables
that encounter warnings.
-io <value> IMPORT specific actions. IMPORT One of: INSERT,
Default is REPLACE_CREATE INSERT_UPDATE, REPLACE,
(see docs for limitations CREATE, REPLACE_CREATE
of Import create function)
-lo <value> LOAD specific options. LOAD One of: INSERT,
Default is INSERT REPLACE
-l <path> Path to lobfiles. EXPORT, Comma seperated list,
Default is cwd IMPORT, no space characters
LOAD
-u <value> Userid to connect to db. ALL
Default is current user
-p <value> Password to connect to db. ALL
-co <opt> COPY specific options. COPY See options listed below.
TARGET_DB <dbname> [USER <userid> [USING <passwd>]
This mandatory option specifies the target database (and optional
user/passwd) where the schema(s) will be created. (Default
user/passwd are current user/passwd).
MODE <value>
The mode is one of DDL_AND_LOAD (default), DDL_ONLY, or LOAD_ONLY.
SCHEMA_MAP <value>
To rename the schema on the target db. For example, to rename schema
'srcsch1' to 'tgtsch1', and schema 'srcsch2' to 'tgtsch2', use:
"((srcsch1,tgtsch1), (scrsch2,tgtsch2))".
TABLESPACE_MAP <value>
To override tablespaces on the target db. For example, to
recreate all tables that reside in TS1 on the source db, into TS2 on
the target db, and all other tablespaces to use the default tablespace
on the target, use: "((TS1,TS2), SYS_ANY)".
NONRECOVERABLE
To override the default Load recoverability action COPY-NO, to
NONRECOVERABLE.
OWNER <owner-name>
To override the owner of each new object created on the target db.
Examples:
1) Use db2move to Export all tables:
>db2move dbname EXPORT
2) Use db2move to Export all tables, filtering on table-name with 'mytab*'
and table-definer with 'USER1', and lobs in /bigfs/mydir/:
>db2move dbname EXPORT -tn mytab* -tc USER1 -l /bigfs/mydir/
3) Use db2move to Import (recreate and repopulate) all previously exported
tables:
>db2move dbname IMPORT
4) Use db2move to Load (repopulate) all previously exported tables:
>db2move dbname LOAD
5) Use db2move to Load (append) all previously exported tables, where
lobfiles reside in /bigfs/mydir:
>db2move dbname LOAD -lo INSERT -l /bigfs/mydir
6) Use db2move to duplicate schema 'schema1' from source database 'dbsrc'
to target database 'dbtgt':
>db2move dbsrc COPY -sn schema1 -co TARGET_DB dbtgt
USER myuser1 USING mypass1
7) Use db2move to duplicate schema 'schema1' from source database 'dbsrc'
to target database 'dbtgt', rename the schema to 'newschema1' on the
target, and map source tablespace 'ts1' to 'ts2' on the target:
>db2move dbsrc COPY -sn schema1 -co TARGET_DB dbtgt
USER myuser1 USING mypass1 SCHEMA_MAP ((schema1,newschema1))
TABLESPACE_MAP ((ts1,ts2), SYS_ANY))