利用CTAS方式通过透明网关创建非ORACLE数据库的表结构导致列长度增大3倍

标题比较长，说明一下情况，源库是个informix数据库，目标库是个ORACLE数据库，
利用透明网关将informix数据库的表结构复制到ORACLE数据库中，发现CHAR和VARCHAR2类型的列长度
增大为原来的3倍。

 

如下所示：

 

这是表test在informix中的表结构：

validno VARCHAR 30
comcode CHAR 4
ownername VARCHAR 60
ownertype CHAR 1
identifytype CHAR 2
identifynumber CHAR 20
***code CHAR 1
postaddress VARCHAR 120
postcode CHAR 6
phonenumber VARCHAR 32
companytype CHAR 3

在ORACLE中通过CTAS方式创建后的表结构如下：

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE 10.2.0.5.0 Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production

SQL> create table huateng
  2 as
  3 select * from "test"@dg4ifmx where 1=0;

Table created.

SQL> desc huateng
 Name Null? Type
 ----------------------------------------- -------- ----------------------------

 validno NOT NULL VARCHAR2(90)
 comcode NOT NULL CHAR(12)
 ownername NOT NULL VARCHAR2(180)
 ownertype NOT NULL CHAR(3)
 identifytype NOT NULL CHAR(6)
 identifynumber NOT NULL CHAR(60)
 ***code CHAR(3)
 postaddress VARCHAR2(360)
 postcode CHAR(18)
 phonenumber VARCHAR2(96)
 companytype CHAR(9)

可以看到列的长度都被无缘无故的增大了3倍。

 

查询了MOS，ORACLE在DOC [ID 374744.1] 描述了这个问题：

 

In certain cases, the the length of Oracle CHAR and VARCHAR columns might be different from the original DB2 columns.
This is due to character set differences that require more (or fewer) bytes in the Oracle representation
than in the original DB2 representation.

In the most common case, the Oracle character set Unicode AL32UTF8 uses 1, 2 or 3 bytes to represent one character. If the DB2 column is single-byte character set and defined as CHAR(10), it shows up through the gateway as CHAR(30). This is required because each single byte character (which only takes 1 byte) may require 1, 2 or 3 bytes to represent the character in Unicode AL32UTF8.

Mainly affected are CHARACTER (CHAR) columns as in Oracle the maximum length of a CHAR column is 2000 and those CHAR columns are padded with spaces.

Root cause is the NLS_LENGTH_SEMANTICS used by the gateway is byte oriented.

ORACLE给出了以下解决方法：

设置透明网关参数HS_KEEP_REMOTE_COLUMN_SIZE

 

11.2.0.1 设置为ALL

 

HS_KEEP_REMOTE_COLUMN_SIZE=ALL

 

11.2.0.2 设置为LOCAL
 
HS_KEEP_REMOTE_COLUMN_SIZE=LOCAL

 

 

我的透明网关版本是11.2.0.1，在透明网关初始化参数中增加参数HS_KEEP_REMOTE_COLUMN_SIZE=ALL：

[gateway@dbserver admin]$ cat initdg4ifmx.ora
# This is a customized agent init file that contains the HS parameters
# that are needed for the Database Gateway for Informix

#
# HS init parameters
#
HS_FDS_CONNECT_INFO=20.1.32.104:50001/nm_1500_cb_ids/dg4ifmx
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER
HS_FDS_TRACE_LEVEL=OFF
HS_LANGUAGE=AL32UTF8
HS_RPC_FETCH_SIZE=200000
HS_FDS_FETCH_ROWS=500
HS_NLS_LENGTH_SEMANTICS=CHAR
HS_KEEP_REMOTE_COLUMN_SIZE=ALL
#HS_FDS_TRACE_FILE_NAME =dg4ifmx_trace.trc

重新启动监听器。

 

[oracle@dbserver admin]$ lsnrctl start

LSNRCTL for Linux: Version 10.2.0.5.0 - Production on 29-AUG-2012 16:28:41

Copyright (c) 1991, 2010, Oracle. All rights reserved.

Starting /u01/app/oracle/product/db10gr2/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 10.2.0.5.0 - Production
System parameter file is /u01/app/oracle/product/db10gr2/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/product/db10gr2/db_1/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dbserver)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbserver)(PORT=1521)))
STATUS of the LISTENER
------------------------

Alias LISTENER
Version TNSLSNR for Linux: Version 10.2.0.5.0 - Production
Start Date 29-AUG-2012 16:28:41
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/db10gr2/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/product/db10gr2/db_1/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dbserver)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))
Services Summary...
Service "PL***tProc" has 1 instance(s).
  Instance "PL***tProc", status UNKNOWN, has 1 handler(s) for this service...
Service "carlpdb" has 1 instance(s).
  Instance "carlpdb", status UNKNOWN, has 1 handler(s) for this service...
Service "dg4ifmx" has 1 instance(s).
  Instance "dg4ifmx", status UNKNOWN, has 1 handler(s) for this service...
Service "piccdb" has 1 instance(s).
  Instance "piccdb", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

再次创建表问题解决。

 

SQL> desc huateng
 Name Null? Type
 ----------------------------------------- -------- ----------------------------

 validno NOT NULL VARCHAR2(90)
 comcode NOT NULL CHAR(12)
 ownername NOT NULL VARCHAR2(180)
 ownertype NOT NULL CHAR(3)
 identifytype NOT NULL CHAR(6)
 identifynumber NOT NULL CHAR(60)
 ***code CHAR(3)
 postaddress VARCHAR2(360)
 postcode CHAR(18)
 phonenumber VARCHAR2(96)
 companytype CHAR(9)

SQL> create table huateng1
  2 as
  3 select * from "test"@dg4ifmx where 1=0;

Table created.

SQL> desc huateng1
 Name Null? Type
 ----------------------------------------- -------- ----------------------------

 validno NOT NULL VARCHAR2(30)
 comcode NOT NULL CHAR(4)
 ownername NOT NULL VARCHAR2(60)
 ownertype NOT NULL CHAR(1)
 identifytype NOT NULL CHAR(2)
 identifynumber NOT NULL CHAR(20)
 ***code CHAR(1)
 postaddress VARCHAR2(120)
 postcode CHAR(6)
 phonenumber VARCHAR2(32)
 companytype CHAR(3)

SQL>