db2 export情况汇总

最新推荐文章于 2024-04-28 09:43:15 发布

原创最新推荐文章于 2024-04-28 09:43:15 发布 · 972 阅读

2 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #db2

本文汇总了在DB2数据库中进行数据导出时遇到的源表与目标表结构不一致的各种情况及解决方案。包括字段顺序不同、源表字段多于或少于目标表、字段名称相似但顺序不同、字段长度和数据类型不匹配等，并给出了相应的SQL导出语句示例。

db2 export情况汇总

一、获取源表与目标表的表结构
1.分别登录原表、目标表所在的服务器
2.分别进入指定的实例
3.分别连接源表与目标表所在的服务器
4.db2 describe table Source_table;
db2 describe table Target_table;

一.源表与目标表表结构相同
export to /file path/Target_table.del of del messages /file path/Target_table.msg select * from tabname;

二.源表与目标表的表结构不同
1.字段顺序不同
Source_table
Column name                     schema    Data type name      Length     Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
PRD_CTG_KEY                     SYSIBM    INTEGER                      4     0 No
OM_BRAND_CD                     SYSIBM    CHARACTER                    8     0 No
PRD_CTG_CD                      SYSIBM    CHARACTER                    8     0 No
RPT_BRAND_CD                    SYSIBM    VARCHAR                     20     0 No
RPT_BRAND_SUBGROUP              SYSIBM    VARCHAR                     20     0 No

        5 record(s) selected.

Target_table
Column name                     schema    Data type name      Length     Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
PRD_CTG_KEY                     SYSIBM    INTEGER                      4     0 No
PRD_CTG_CD                      SYSIBM    CHARACTER                    8     0 No
OM_BRAND_CD                     SYSIBM    CHARACTER                    8     0 No
RPT_BRAND_CD                    SYSIBM    VARCHAR                     20     0 No
RPT_BRAND_SUBGROUP              SYSIBM    VARCHAR                     20     0 No

5 record(s) selected.

解决方案：按照目标表的字段顺序导出
export to /file path/Target_table.del of del messages /file path/Target_table.msg
select PRD_CTG_KEY,PRD_CTG_CD,OM_BRAND_CD,RPT_BRAND_CD,RPT_BRAND_SUBGROUP from Source_table;

2.源表字段比目标表多

Source_table
Column name                     schema    Data type name      Length     Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
PRD_CTG_KEY                     SYSIBM    INTEGER                      4     0 No
OM_BRAND_CD                     SYSIBM    CHARACTER                    8     0 No
PRD_CTG_CD                      SYSIBM    CHARACTER                    8     0 No
RPT_BRAND_CD                    SYSIBM    VARCHAR                     20     0 No
RPT_BRAND_SUBGROUP              SYSIBM    VARCHAR                     20     0 No

4 record(s) selected.

解决方案：把目标表需要的字段按顺序导出
export to /file path/Target_table.del of del messages /file path/Target_table.msg
select PRD_CTG_KEY,PRD_CTG_CD,OM_BRAND_CD,RPT_BRAND_CD from Source_table;

3.源表比目标表少
Source_table
Column                         Type      Type
name                           schema    name               Length   Scale Nulls
------------------------------ --------- ------------------ -------- ----- ------
PROD_CAT_KEY                   SYSIBM    INTEGER                   4     0 No
PRD_CTG_CD                     SYSIBM    CHARACTER                 8     0 No
OM_BRAND_CD                    SYSIBM    CHARACTER                 8     0 No

3 record(s) selected.

Target_table
Column                         Type      Type
name                           schema    name               Length   Scale Nulls
------------------------------ --------- ------------------ -------- ----- ------
PROD_CAT_KEY                   SYSIBM    INTEGER                   4     0 No
PRD_CTG_CD                     SYSIBM    CHARACTER                 8     0 No
OM_BRAND_CD                    SYSIBM    CHARACTER                 8     0 No
IW_ROW_UPDT_TS                 SYSIBM    TIMESTAMP                10     0 Yes

4 record(s) selected.

解决方案：造数据
export to /file path/Target_table.del of del messages /file path/Target_table.msg
select PRD_CTG_KEY,PRD_CTG_CD,OM_BRAND_CD,Current Timestamp from Source_table;

4.源表和目标表的字段名称相似（字段类型/长度/可否为空相同）
Source_table
Column                         Type      Type
name                           schema    name               Length   Scale Nulls
------------------------------ --------- ------------------ -------- ----- ------
PROD_CAT_KEY                   SYSIBM    INTEGER                   4     0 No
PRD_CTG_CD                     SYSIBM    CHARACTER                 8     0 No
ACTIVE_INDC                    SYSIBM    CHARACTER                 1     0 Yes
PCAT_KEY                       SYSIBM    INTEGER                   4     0 No

4 record(s) selected.

Target_table
Column name                     schema    Data type name      Length     Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
PRD_CTG_KEY                     SYSIBM    INTEGER                      4     0 No
PRD_CTG_CD                      SYSIBM    CHARACTER                    8     0 No
PRD_CTG_ACTIVE_INDC             SYSIBM    CHARACTER                    1     0 Yes
PCAT_KEY                        SYSIBM    INTEGER                      4     0 No

4 record(s) selected.

解决方案：按照目标表的字段顺序导出
export to /file path/Target_table.del of del messages /file path/Target_table.msg
select PRD_CTG_KEY,PRD_CTG_CD,PRD_CTG_ACTIVE_INDC,PCAT_KEY from Source_table;

5.源表与目标表的字段长度不同----目标字段比源字段短

Source_table
Column                         Type      Type
name                           schema    name               Length   Scale Nulls
------------------------------ --------- ------------------ -------- ----- ------
PROD_CAT_KEY                   SYSIBM    INTEGER                   4     0 No
PRD_CTG_CD                     SYSIBM    CHARACTER                10     0 No
ACTIVE_INDC                    SYSIBM    CHARACTER                 1     0 Yes
PCAT_KEY                       SYSIBM    INTEGER                   4     0 No

4 record(s) selected.

解决方案：使用substr()方法取目标字段长度的值
export to /file path/Target_table.del of del messages /file path/Target_table.msg
select PRD_CTG_KEY,substr(PRD_CTG_CD,1,8),PRD_CTG_ACTIVE_INDC,PCAT_KEY from Source_table;

6.源表与目标表的字段长度不同----目标字段比源字段长

4 record(s) selected.

解决方案：按照目标字段的顺序直接导出
export to /file path/Target_table.del of del messages /file path/Target_table.msg
select PRD_CTG_KEY,PRD_CTG_CD,PRD_CTG_ACTIVE_INDC,PCAT_KEY from Source_table;

7.源表与目标表的字段数据类型不同

Source_table
Column                         Type      Type
name                           schema    name               Length   Scale Nulls
------------------------------ --------- ------------------ -------- ----- ------
PROD_CAT_KEY                   SYSIBM    INTEGER                   4     0 No
PRD_CTG_CD                     SYSIBM    INTEGER                   4     0 No
ACTIVE_INDC                    SYSIBM    CHARACTER                 1     0 Yes
PCAT_KEY                       SYSIBM    INTEGER                   4     0 No

4 record(s) selected.

Target_table
Column name                     schema    Data type name      Length     Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
PRD_CTG_KEY                     SYSIBM    INTEGER                      4     0 No
PRD_CTG_CD                      SYSIBM    CHARACTER                    4     0 No
PRD_CTG_ACTIVE_INDC             SYSIBM    CHARACTER                    1     0 Yes
PCAT_KEY                        SYSIBM    INTEGER                      4     0 No

4 record(s) selected.

解决方案：将源字段的数据类型转换为目标字段的数据类型导出
export to /file path/Target_table.del of del messages /file path/Target_table.msg
select PRD_CTG_KEY,cast(PRD_CTG_CD CHARACTER(4)),PRD_CTG_ACTIVE_INDC,PCAT_KEY from Source_table;

8.以上几种情况组合出现