DB2 z/OS V8 NFM and Oracle Transparent Gateway

David S Waugh

DB2 z/OS V8 NFM and Oracle Transparent Gateway
Greetings all:

My client recently made an attempt to go from DB2 V8 CM to DB2 V8 ENFM, then to NFM on their Test subsystem.

Before doing this, they upgraded their Oracle Transparent Gateway for this subsystem to Oracle HS Agent, TG4DB2 Version (which is the one that supposedly supports DB2 V8 and V9). The Oracle Servers they're using vary from 9i to 10g.

Yes, yes, I know - they're somewhat behind on supported software. Can't be helped at the moment.

I believe (but cannot confirm) that their Oracle Servers and Databases are all set up as ASCII rather than Unicode. The DB2 application data they're trying to access remains in EBCDIC, as always (the only Unicode data we have on DB2 V8 is in the DB2 Catalog & Directory). The CCSIDs we specified for DB2 V8 are: EBCDIC CCSID = 037, ASCII CCSID = 819, UNICODE CCSID = 1208. MIXED DATA is NO (we only have SBCS data).

And... we started having problems immediately when trying to SELECT data from DB2 tables over to Oracle via the Oracle Transparent Gateway. Basically, somewhere in the translation (and I think it is in the new OTG) character columns end up on the Oracle side 3 times longer than they are defined on DB2. So a CHAR(5) DB2 column's contents show up in Oracle as 15 bytes instead of 5, CHAR(3) shows up as 9 bytes, etc. The same appears to be happening to numeric data as well. The error messages they're seeing are:

Cause: FDPSTP failed due to ORA-12899: value too large for
column xxxxxxxx (actual: nn, maximum: nn)

Information: FRM-40831: Truncation occurred: Value too
long for field xxxxxxxx.

Obviously not good, and a show-stopper for moving Production DB2 to NFM.

I'm thinking there's probably some simple way of fixing this problem, either by applying a Bug Fix or by specifying something in the sidENV parameter member that the OTG address space starts up with. I suspect the problem has something to do with UTF8 or AL32UTF8 being the default (AL32UTF8 can use from 1 to 3 bytes when translating characters, which sounds suspicious). Unfortunately, I'm not the least bit familiar with Oracle or OTG, not enough to be able to troubleshoot this problem to any great degree, much less fix it.

Oracle is, of course, not being very helpful so far. They've asked for us to turn on a "trace" (but didn't specify what kind - we're assuming an OTG trace via TRACELEVEL=255) while the application runs.

I was hoping someone on the list has run into this before, and has a solution in hand. Or can at least share their parameter settings with us so we can compare them with what we have.

Thanks in advance for your help!

David Waugh
DSW Consulting & Services

Top Online Degrees
Browse our directory of 1000 degree to find your best fit. Free info!


* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *

http://www.idug.org/db2-videos.html has hundreds of video presentations!
Did you miss out on attending an IDUG conference?
Many of the presentations were recorded and are available on our website!

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L