上周五就答应帮同事解决一个困扰她好几天的关于透明网关的问题，今天上午带闺女去儿研所看病（她有些咳嗽），下午回到公司，开始解决上述问题。
经过了一个下午的努力，连猜带蒙，成功解决了上述问题，我们现在先来看一下配通后的用oracle连db2 for z/OS的效果：
SQL> conn sys/sys@giggles as sysdba;
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as SYS
SQL> select count(*) from sysibm.systables@locdsn1;
COUNT(*)
-----------
523
SQL> select * from DSN8910.DEPT@locdsn1;
DEPTNO DEPTNAME MGRNO ADMRDEPT LOCATION
------ ------------------------------------------------------------------------ ------------ -------- --------------------------------
A00 SPIFFY COMPUTER SERVICE DIV.000010 A00
B01 PLANNING 000020 A00
C01 INFORMATION CENTER 000030 A00
D01 DEVELOPMENT CENTER A00
D11 MANUFACTURING SYSTEMS 000060 D01
D21 ADMINISTRATION SYSTEMS 000070 D01
E01 SUPPORT SERVICES 000050 A00
E11 OPERATIONS 000090 E01
E21 SOFTWARE SUPPORT 000100 E01
F22 BRANCH OFFICE F2 E01
G22 BRANCH OFFICE G2 E01
H22 BRANCH OFFICE H2 E01
I22 BRANCH OFFICE I2 E01
J22 BRANCH OFFICE J2 E01
14 rows selected
这个问题的起因是我的同事想配通从oracle到db2 for z/OS的transparent gateway，但是在一切工作做完后发现无论如何也连不上db2，她原先的tnsnams.ora如下：
# tnsnames.ora Network Configuration File: /iprau01/app/oracle/10g/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
GIGGLES =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = P550_04_LA)(PORT = 1588))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = giggles)
)
)
LISTENER_GIGGLES =
(ADDRESS = (PROTOCOL = TCP)(HOST = P550_04_LA)(PORT = 1588))
LISTENER_IPRA =
(ADDRESS = (PROTOCOL = TCP)(HOST = P550_04_LA)(PORT = 1588))
IPRA =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = P550_04_LA)(PORT = 1588))
(CONNECT_DATA =
(SERVER = SHARED)
(SERVICE_NAME = ipra)
)
)
ASTCA =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = P550_04_LA)(PORT = 1521))
(CONNECT_DATA =
(SERVER = SHARED)
(SERVICE_NAME = astca)
)
)
LOCDSN1=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.15.57)(PORT = 1541))
)
(CONNECT_DATA =
(SID =LOCDSN1)
(HS=OK)
)
)
当在oracle执行select * from sysibm.systables@locdsn1的时候总是报同样的错：
ORA-28546: connection initialization failed, probable Net8 admin error
此时无论怎样改tg的配置都不行！
我也被上述问题折腾了好几个小时的时间，后来发现把tnsnames.ora中关于LOCDSN1的那段连接字符串改成如下的就可以解决上述问题了：
LOCDSN1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = ORAIPC))
)
(CONNECT_DATA = (SID =LOCDSN1) )
(HS= OK)
)
即首先要HS=OK，然后要把HS=OK从CONNECT_DATA中挪出来！
这里oracle的tg for drda文档中的描述是不准确的。
接着我依然执行select count(*) from sysibm.systables@locdsn1的时候报了另外一个错误：
SQL> select count(*) from sysibm.systables@locdsn1;
select count(*) from sysibm.systables@locdsn1
ORA-09119: Message 9119 not found;
product=RDBMS; facility=ORA
TG4DRDA v10.2.0.1.0 grc=-9119, drc=-701 (0000,0000), errp=
ORA-02063: preceding 3 lines from LOCDSN1
此时执行GTW$_BIND_PKG@locdsn1也报同样的错：
SQL> exec GTW$_BIND_PKG@locdsn1;
begin GTW$_BIND_PKG@locdsn1; end;
ORA-04052: error occurred when looking up remote object [email protected]
ORA-00604: error occurred at recursive SQL level 1
ORA-09119: Message 9119 not found;
product=RDBMS; facility=ORA
TG4DRDA v10.2.0.1.0 grc=-9119, drc=-701 (0000,0000), errp=
ORA-02063: preceding 3 lines from LOCDSN1
我仔细看了一下上述tg的配置文件initLOCDSN1.ora，发现里面的配置项DRDA_DEFAULT_CCSID=935，接着我看了一下字符集映射文件codepage.map，发现里面没有935这一项，估计是这里出了问题，于是将上述配置项删掉，让oracle 用缺省的值。
删完后重新执行select count(*) from sysibm.systables@locdsn1，果然这里错误信息已经发生了变化：
SQL> select count(*) from sysibm.systables@locdsn1;
select count(*) from sysibm.systables@locdsn1
ORA-28527: Heterogeneous Services datatype mapping error
TG4DRDA v10.2.0.1.0 grc=0, drc=-332 (865B,0001), errp=GDJREACS
errmc=836,819
ORA-02063: preceding 3 lines from LOCDSN1
注意到这里错误信息里有errmc=836,819，这就表明db2 for z/OS上用的字符集是836，oracle这里缺省用的是819（即AMERICAN_AMERICA.WE8ISO8859P1），这里的错误提示表明oracle这里无法正确的mapping。
怎么办？束手无策！
无奈之下仔细看tg for drda的文档中的附录D里的关于codepage.map的示例，偶然发现里面居然有836的定义，附录D里关于codepage.map的示例有这样4行：
#S 836 > ZHS16DBCS # Simplified Chinese single-byte EBCDIC
#D 837 > ZHS16DBCS shift # Simplified Chinese double-byte EBCDIC
#M 935 = ZHS16DBCS shift # Simplified Chinese multi-byte EBCDIC
#MBC 935 = 836 837 # Simplified Chinese multi-byte EBCDIC
把上述注释去掉，然后copy上述4行到tg的字符集映射文件codepage.map中，再次执行select count(*) from sysibm.systables@locdsn1，欣喜的发现错误再次发生了变化：
SQL> select count(*) from sysibm.systables@locdsn1;
select count(*) from sysibm.systables@locdsn1
ORA-02085: database link LOCDSN1.ACCA connects to HO.WORLD
上述问题好解决，HO和WORLD分别是HS_DB_DOMAIN和HS_DB_NAME的缺省值，这里显然表明tg的配置文件initLOCDSN1.ora中的配置项HS_DB_DOMAIN和HS_DB_NAME没有配置，所以oracle用了默认值。
于是做了如下更改：
HS_DB_DOMAIN=ACCA
HS_DB_NAME=LOCDSN1
改完再次执行select count(*) from sysibm.systables@locdsn1，发现错误又一次发生了变化：
SQL> select count(*) from sysibm.systables@locdsn1;
select count(*) from sysibm.systables@locdsn1
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
TG4DRDA v10.2.0.1.0 grc=0, drc=-30020 (839C,0000), errp=GDJMRCM
sqlcode=-805, sqlstate=51002, errd=FFFFFF9C,0,0,FFFFFFFF,0,0
errmc=124C
ORA-02063: preceding 4 lines from LOCDSN1
这个问题oracle的tg for drda的文档的149页写的很清楚，我们看一下oracle对上述问题的官方解释：
For example, the following error message indicates that the DRDA Server database did
not recognize the collection ID or package name specified with the
DRDA_PACKAGE_COLLID or DRDA_PACKAGE_NAME parameters in the initsid.ora file:
ORA-28500: connection from ORACLE to a non-Oracle system returned the message:
TG4DRDA v10.2.0.1.0 grc=0, drc=-30020 (839C,0000), errp=GDJMRCM
sqlcode=-805, sqlstate=51002, errd=FFFFFF9C,0,0,FFFFFFFF,0,0
errmc=124c
接着我去看了一下tg的配置文件initLOCDSN1.ora，发现DRDA_PACKAGE_COLLID 和DRDA_PACKAGE_NAME是缺省值，不应该有问题。
再次陷入束手无策之中。
无奈之下执行了一下exec GTW$_BIND_PKG@locdsn1，呵呵，奇迹出现了！
SQL>exec GTW$_BIND_PKG@locdsn1;
PL/SQL procedure successfully completed
SQL> commit;
Commit complete
SQL> select count(*) from sysibm.systables@locdsn1;
COUNT(*)
-----------
523
SQL> select count(*) from DSN8910.DEPT@locdsn1;
COUNT(*)
-----------
14
SQL> select * from DSN8910.DEPT@locdsn1;
DEPTNO DEPTNAME MGRNO ADMRDEPT LOCATION
------ ------------------------------------------------------------------------ ------------ -------- --------------------------------
A00 SPIFFY COMPUTER SERVICE DIV.000010 A00
B01 PLANNING 000020 A00
C01 INFORMATION CENTER 000030 A00
D01 DEVELOPMENT CENTER A00
D11 MANUFACTURING SYSTEMS 000060 D01
D21 ADMINISTRATION SYSTEMS 000070 D01
E01 SUPPORT SERVICES 000050 A00
E11 OPERATIONS 000090 E01
E21 SOFTWARE SUPPORT 000100 E01
F22 BRANCH OFFICE F2 E01
G22 BRANCH OFFICE G2 E01
H22 BRANCH OFFICE H2 E01
I22 BRANCH OFFICE I2 E01
J22 BRANCH OFFICE J2 E01
14 rows selected
至此，上述透明网关故障被我成功解决！
[ 本帖最后由 allantrey 于 2008-8-19 20:49 编辑 ]
|
