[DB2-L] DB2 z/OS V8 NFM and Oracle Transparent Gateway 10.2.0.3.0

PUSCH Othmar

[DB2-L] DB2 z/OS V8 NFM and Oracle Transparent Gateway 10.2.0.3.0
Hi David !

o) We've had code-page_problems in this area too ... similar to the prob's in your eMAIL-info.
o) We tried with ORACLE & IBM a lot (z/OS-1.10, DB2/V9 and OTGW/10.2.0.3.0 at problem-time) ... trace-level's (4,16, ..., 255), dump's etc. ...
o) Solution for us is/was ORACLE's Patch-Set B6682743 for OTGW-Mainframe ... since all our (historical) ccsid's of the different oracle_client's are correct supported. With this Mainframe-B6682743 ya bring up OTGW/Oracle-Part to 10.2.0.3.92 and the dyn. z/OS-subsystem OTGW/OSDI-Part to 1.2.1.4. Don't forget to code in DD-ORA$ENV-Parmlib of the Gateway-Part of OTGW a (new) FDS-Class_Statement ---> (FDS_CLASS_VERSION = '10.2.0.3.92')

Hope this helps a little bit / kinds - Othmar

-----Ursprüngliche Nachricht-----
Von: IDUG DB2-L [mailto:[login to unmask email] Im Auftrag von David S Waugh
Gesendet: Mittwoch, 25. November 2009 23:17
An: [login to unmask email]
Betreff: [DB2-L] DB2 z/OS V8 NFM and Oracle Transparent Gateway 10.2.0.3.0

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 10.2.0.3.0 (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!
http://thirdpartyoffers.juno.com/TGL2131/c?cp=zKeo13oR_b0pHhD5vr13TAAAJz2i9R_j_Y5ef6WP9NxqQ2yLAAQAAAAFAAAAAOgCjT4AAAMlAAAAAAAAAAAAAAAAAAAQRQAAAAA=

_____________________________________________________________________

* 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

_____________________________________________________________________

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

http://www.idug.org/events/index.html is your DB2 Events calendar! RUG meetings,
Webcasts, Conferences- what is going on next?
RUG leaders- get your events on the calendar today!
_____________________________________________________________________

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

David S Waugh

Re: DB2 z/OS V8 NFM and Oracle Transparent Gateway 10.2.0.3.0
(in response to PUSCH Othmar)
Thanks Othmar:

We applied Gateway-TG4DB2 10.2.0.3 patch 6682743 (plus another patch they said was needed - OSDI for z/OS: Patch 6138068), and set FDS_CLASS_VERSION='10.2.0.3.93' in ORA$ENV.

Still no dice - CHAR(6) columns are still coming across as 18 bytes.

It's not mis-translating the data - that comes across from DB2 OK, it's just followed by 12 blanks.

Any other ideas?

David Waugh
DSW Consulting & Services


---------- Original Message ----------
From: PUSCH Othmar <[login to unmask email]>
To: [login to unmask email]
Subject: [DB2-L] AW: [DB2-L] DB2 z/OS V8 NFM and Oracle Transparent Gateway 10.2.0.3.0
Date: Thu, 26 Nov 2009 09:40:47 +0100

Hi David !

o) We've had code-page_problems in this area too ... similar to the prob's in your eMAIL-info.
o) We tried with ORACLE & IBM a lot (z/OS-1.10, DB2/V9 and OTGW/10.2.0.3.0 at problem-time) ... trace-level's (4,16, ..., 255), dump's etc. ...
o) Solution for us is/was ORACLE's Patch-Set B6682743 for OTGW-Mainframe ... since all our (historical) ccsid's of the different oracle_client's are correct supported. With this Mainframe-B6682743 ya bring up OTGW/Oracle-Part to 10.2.0.3.92 and the dyn. z/OS-subsystem OTGW/OSDI-Part to 1.2.1.4. Don't forget to code in DD-ORA$ENV-Parmlib of the Gateway-Part of OTGW a (new) FDS-Class_Statement ---> (FDS_CLASS_VERSION = '10.2.0.3.92')

Hope this helps a little bit / kinds - Othmar

-----Ursprüngliche Nachricht-----
Von: IDUG DB2-L [mailto:[login to unmask email] Im Auftrag von David S Waugh
Gesendet: Mittwoch, 25. November 2009 23:17
An: [login to unmask email]
Betreff: [DB2-L] DB2 z/OS V8 NFM and Oracle Transparent Gateway 10.2.0.3.0

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 10.2.0.3.0 (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!
http://thirdpartyoffers.juno.com/TGL2131/c?cp=zKeo13oR_b0pHhD5vr13TAAAJz2i9R_j_Y5ef6WP9NxqQ2yLAAQAAAAFAAAAAOgCjT4AAAMlAAAAAAAAAAAAAAAAAAAQRQAAAAA=

_____________________________________________________________________

* 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

_____________________________________________________________________

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

http://www.idug.org/events/index.html is your DB2 Events calendar! RUG meetings,
Webcasts, Conferences- what is going on next?
RUG leaders- get your events on the calendar today!
_____________________________________________________________________

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

____________________________________________________________
Hotel
Save on hotels. Click here to find and compare hotel deals.
http://thirdpartyoffers.juno.com/TGL2131/c?cp=Qx7GbgianMNx7gHE2vyjggAAJz2i9R_j_Y5ef6WP9NxqQ2yLAAYAAAAAAAAAAAAAAAAAAADNAAAAAAAAAAAAAAAAAAATRAAAAAA=

_____________________________________________________________________

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

http://www.idug.org/db2-content/index.html has THOUSANDS of free technical presentations!
DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI, Warehouses, - among
many more categories of help waiting for you!
Whether you are an old hand or a DB2 newbie, we have presentations for every level.
_____________________________________________________________________

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

PUSCH Othmar

AW: [DB2-L] DB2 z/OS V8 NFM and Oracle Transparent Gateway 10.2.0.3.0
(in response to David S Waugh)
Hi dear David !
o) Thanks for your feedback. Puhh/Mnnn/Puuu from my only-mainframe-point-of-view. This patch_set 6138068 belongs to the B6682743 too ; this is definitely correct.
o) I just 'attached' my dec. oracle-guru's ... they've right now also no (new) idea in this "3_times_longer-area".
o) In google I found some info's about ORA-12899 & FRM-40831--> e.g. --> "Did you try to check value of "Data Length Semantics" property?" !
Sorry not more; kinds / Othmar

-----Ursprüngliche Nachricht-----
Von: IDUG DB2-L [mailto:[login to unmask email] Im Auftrag von David S Waugh
Gesendet: Donnerstag, 03. Dezember 2009 01:28
An: [login to unmask email]
Betreff: Re: [DB2-L] DB2 z/OS V8 NFM and Oracle Transparent Gateway 10.2.0.3.0
Thanks Othmar:
We applied Gateway-TG4DB2 10.2.0.3 patch 6682743 (plus another patch they said was needed - OSDI for z/OS: Patch 6138068), and set FDS_CLASS_VERSION='10.2.0.3.93' in ORA$ENV.
Still no dice - CHAR(6) columns are still coming across as 18 bytes. It's not mis-translating the data - that comes across from DB2 OK, it's just followed by 12 blanks.
Any other ideas? David Waugh DSW Consulting & Services

---------- Original Message ----------
From: PUSCH Othmar <[login to unmask email]>
To: [login to unmask email]
Subject: [DB2-L] AW: [DB2-L] DB2 z/OS V8 NFM and Oracle Transparent Gateway 10.2.0.3.0
Date: Thu, 26 Nov 2009 09:40:47 +0100
Hi David !
o) We've had code-page_problems in this area too ... similar to the prob's in your eMAIL-info.
o) We tried with ORACLE & IBM a lot (z/OS-1.10, DB2/V9 and OTGW/10.2.0.3.0 at problem-time) ... trace-level's (4,16, ..., 255), dump's etc. ...
o) Solution for us is/was ORACLE's Patch-Set B6682743 for OTGW-Mainframe ... since all our (historical) ccsid's of the different oracle_client's are correct supported. With this Mainframe-B6682743 ya bring up OTGW/Oracle-Part to 10.2.0.3.92 and the dyn. z/OS-subsystem OTGW/OSDI-Part to 1.2.1.4. Don't forget to code in DD-ORA$ENV-Parmlib of the Gateway-Part of OTGW a (new) FDS-Class_Statement ---> (FDS_CLASS_VERSION = '10.2.0.3.92')

Hope this helps a little bit / kinds - Othmar

-----Ursprüngliche Nachricht-----
Von: IDUG DB2-L [mailto:[login to unmask email] Im Auftrag von David S Waugh
Gesendet: Mittwoch, 25. November 2009 23:17
An: [login to unmask email]
Betreff: [DB2-L] DB2 z/OS V8 NFM and Oracle Transparent Gateway 10.2.0.3.0

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 10.2.0.3.0 (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!
http://thirdpartyoffers.juno.com/TGL2131/c?cp=zKeo13oR_b0pHhD5vr13TAAAJz2i9R_j_Y5ef6WP9NxqQ2yLAAQAAAAFAAAAAOgCjT4AAAMlAAAAAAAAAAAAAAAAAAAQRQAAAAA=

_____________________________________________________________________

* 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

_____________________________________________________________________

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

http://www.idug.org/events/index.html is your DB2 Events calendar! RUG meetings,
Webcasts, Conferences- what is going on next?
RUG leaders- get your events on the calendar today!
_____________________________________________________________________

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

____________________________________________________________
Hotel
Save on hotels. Click here to find and compare hotel deals.
http://thirdpartyoffers.juno.com/TGL2131/c?cp=Qx7GbgianMNx7gHE2vyjggAAJz2i9R_j_Y5ef6WP9NxqQ2yLAAYAAAAAAAAAAAAAAAAAAADNAAAAAAAAAAAAAAAAAAATRAAAAAA=

_____________________________________________________________________

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

http://www.idug.org/db2-content/index.html has THOUSANDS of free technical presentations!
DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI, Warehouses, - among
many more categories of help waiting for you!
Whether you are an old hand or a DB2 newbie, we have presentations for every level.
_____________________________________________________________________

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

_____________________________________________________________________

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

http://www.idug.org/db2-content/index.html has THOUSANDS of free technical presentations!
DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI, Warehouses, - among
many more categories of help waiting for you!
Whether you are an old hand or a DB2 newbie, we have presentations for every level.
_____________________________________________________________________

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

David S Waugh

Re: DB2 z/OS V8 NFM and Oracle Transparent Gateway 10.2.0.3.0
(in response to PUSCH Othmar)
Othmar:

Yes, we checked "Data Length Semantics", at least as far as NLS_LENGTH_SEMANTICS, and it is set to 'BYTE' in the Oracle database. We tried ALTER SESSION SET NLS_LENGTH_SEMANTICS = 'CHAR'; and it didn't help.

We're going to try setting BLANK_TRIMMING = 'TRUE' in init.ora to see if that makes any difference, but we're a little leery of messing up something else, since it's a global thing that affects everybody, not just the people who are having this expansion problem.

Other than that, we're at a loss. We don't think changing the character set from US7ASCII to AL32UTF8 is going to work either.

The indication we get from Oracle Support is that the TG4DB2 10.2.0.3 habit of expanding a DB2 CHAR column to 3 times its length when retrieving it from Oracle thru the Gateway is "Working As Designed" (Where have I heard that phrase before? Oh yes, from IBM), and that this particular expansion problem will not be fixed until TG4DRDA 11.2 comes out (with bug fix 4109506 applied). So now my client gets to sit and wait (at DB2 V8 CM) for TG4DRDA 11.2 to come out for the AIX platform. Which could take awhile.

Don't you just love it?

David Waugh
DSW Consulting & Services

---------- Original Message ----------
From: PUSCH Othmar <[login to unmask email]>
To: "[login to unmask email]" <[login to unmask email]>
Subject: AW: [DB2-L] DB2 z/OS V8 NFM and Oracle Transparent Gateway 10.2.0.3.0
Date: Thu, 3 Dec 2009 14:22:45 +0100

Hi dear David !
o) Thanks for your feedback. Puhh/Mnnn/Puuu from my only-mainframe-point-of-view. This patch_set 6138068 belongs to the B6682743 too ; this is definitely correct.
o) I just 'attached' my dec. oracle-guru's ... they've right now also no (new) idea in this "3_times_longer-area".
o) In google I found some info's about ORA-12899 & FRM-40831--> e.g. --> "Did you try to check value of "Data Length Semantics" property?" !
Sorry not more; kinds / Othmar

-----Ursprüngliche Nachricht-----
Von: IDUG DB2-L [mailto:[login to unmask email] Im Auftrag von David S Waugh
Gesendet: Donnerstag, 03. Dezember 2009 01:28
An: [login to unmask email]
Betreff: Re: [DB2-L] DB2 z/OS V8 NFM and Oracle Transparent Gateway 10.2.0.3.0
Thanks Othmar:
We applied Gateway-TG4DB2 10.2.0.3 patch 6682743 (plus another patch they said was needed - OSDI for z/OS: Patch 6138068), and set FDS_CLASS_VERSION='10.2.0.3.93' in ORA$ENV.
Still no dice - CHAR(6) columns are still coming across as 18 bytes. It's not mis-translating the data - that comes across from DB2 OK, it's just followed by 12 blanks.
Any other ideas? David Waugh DSW Consulting & Services

---------- Original Message ----------
From: PUSCH Othmar <[login to unmask email]>
To: [login to unmask email]
Subject: [DB2-L] AW: [DB2-L] DB2 z/OS V8 NFM and Oracle Transparent Gateway 10.2.0.3.0
Date: Thu, 26 Nov 2009 09:40:47 +0100
Hi David !
o) We've had code-page_problems in this area too ... similar to the prob's in your eMAIL-info.
o) We tried with ORACLE & IBM a lot (z/OS-1.10, DB2/V9 and OTGW/10.2.0.3.0 at problem-time) ... trace-level's (4,16, ..., 255), dump's etc. ...
o) Solution for us is/was ORACLE's Patch-Set B6682743 for OTGW-Mainframe ... since all our (historical) ccsid's of the different oracle_client's are correct supported. With this Mainframe-B6682743 ya bring up OTGW/Oracle-Part to 10.2.0.3.92 and the dyn. z/OS-subsystem OTGW/OSDI-Part to 1.2.1.4. Don't forget to code in DD-ORA$ENV-Parmlib of the Gateway-Part of OTGW a (new) FDS-Class_Statement ---> (FDS_CLASS_VERSION = '10.2.0.3.92')

Hope this helps a little bit / kinds - Othmar

-----Ursprüngliche Nachricht-----
Von: IDUG DB2-L [mailto:[login to unmask email] Im Auftrag von David S Waugh
Gesendet: Mittwoch, 25. November 2009 23:17
An: [login to unmask email]
Betreff: [DB2-L] DB2 z/OS V8 NFM and Oracle Transparent Gateway 10.2.0.3.0

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 10.2.0.3.0 (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!
http://thirdpartyoffers.juno.com/TGL2131/c?cp=zKeo13oR_b0pHhD5vr13TAAAJz2i9R_j_Y5ef6WP9NxqQ2yLAAQAAAAFAAAAAOgCjT4AAAMlAAAAAAAAAAAAAAAAAAAQRQAAAAA=

_____________________________________________________________________

* 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

_____________________________________________________________________

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

http://www.idug.org/events/index.html is your DB2 Events calendar! RUG meetings,
Webcasts, Conferences- what is going on next?
RUG leaders- get your events on the calendar today!
_____________________________________________________________________

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

____________________________________________________________
Hotel
Save on hotels. Click here to find and compare hotel deals.
http://thirdpartyoffers.juno.com/TGL2131/c?cp=Qx7GbgianMNx7gHE2vyjggAAJz2i9R_j_Y5ef6WP9NxqQ2yLAAYAAAAAAAAAAAAAAAAAAADNAAAAAAAAAAAAAAAAAAATRAAAAAA=

_____________________________________________________________________

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

http://www.idug.org/db2-content/index.html has THOUSANDS of free technical presentations!
DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI, Warehouses, - among
many more categories of help waiting for you!
Whether you are an old hand or a DB2 newbie, we have presentations for every level.
_____________________________________________________________________

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

____________________________________________________________
Nutrition
Improve your career health. Click now to study nutrition!
http://thirdpartyoffers.juno.com/TGL2131/c?cp=CM217zlN_cY3kRU3wH8SpwAAJz2i9R_j_Y5ef6WP9NxqQ2yLAAYAAAAAAAAAAAAAAAAAAADNAAAAAAAAAAAAAAAAAAASQwAAAAA=

_____________________________________________________________________

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

http://www.idug.org/db2-content/index.html has THOUSANDS of free technical presentations!
DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI, Warehouses, - among
many more categories of help waiting for you!
Whether you are an old hand or a DB2 newbie, we have presentations for every level.
_____________________________________________________________________

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

Jorg Lueke

Re: DB2 z/OS V8 NFM and Oracle Transparent Gateway 10.2.0.3.0
(in response to David S Waugh)
Just have them substring everything on the Oracle side :-)

_____________________________________________________________________

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

http://www.idug.org/solutions-journal.html - home of the IDUG Solutions Journal
Technical atricles from world famous authors in DB2's most prestigious, peer reviewed
magazine now on-line!
_____________________________________________________________________

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

David S Waugh

Re: DB2 z/OS V8 NFM and Oracle Transparent Gateway 10.2.0.3.0
(in response to Jorg Lueke)
Jorg:

Yep, that would work just fine (as would RTRIM). However, when we broached this subject with the Applications Developers, this was their response:

"We can’t change a million lines of interface code. There has to be another solution."

I kinda doubt if they'd have to change a million lines of code (probably more like thousands of lines), but it means searching through hundreds of interface programs, some of which they may or may not still have the source code for. Or so I'm told. I can't say I blame them for not wanting to change a ton of programs and then test all of them to make sure the changes doesn't mess something else up - that could take months, and they're busy with other higher-priority things right now.

That's why we're looking for another alternative... like some parameter we can set in Oracle or the OTG that will magically make the expansion problem go away. Dreaming? Probably, but one can always hope.

In the mean time, management seems content to sit at DB2 V8 CM until this new TG4DRDA 11.2 is announced for the AIX platform. My worry is that they'll be waiting a long, LONG time... and it may or may not work, or it may cause other problems (for one thing, it runs on a different platform than the OTG we have now). Who knows?

David Waugh
DSW Consulting & Services


---------- Original Message ----------
From: Jorg Lueke <[login to unmask email]>
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 z/OS V8 NFM and Oracle Transparent Gateway 10.2.0.3.0
Date: Fri, 4 Dec 2009 16:19:57 +0000

Just have them substring everything on the Oracle side :-)

_____________________________________________________________________

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

http://www.idug.org/solutions-journal.html - home of the IDUG Solutions Journal
Technical atricles from world famous authors in DB2's most prestigious, peer reviewed
magazine now on-line!
_____________________________________________________________________

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

____________________________________________________________
Love Spell
Click here to light up your life with a love spell!
http://thirdpartyoffers.juno.com/TGL2131/c?cp=xADde2mgeKM-Jl-rZlQ0BwAAJz2i9R_j_Y5ef6WP9NxqQ2yLAAYAAAAAAAAAAAAAAAAAAADNAAAAAAAAAAAAAAAAAAAARwAAAAA=

_____________________________________________________________________

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

http://www.idug.org/solutions-journal.html - home of the IDUG Solutions Journal
Technical atricles from world famous authors in DB2's most prestigious, peer reviewed
magazine now on-line!
_____________________________________________________________________

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

Jorg Lueke

Re: DB2 z/OS V8 NFM and Oracle Transparent Gateway 10.2.0.3.0
(in response to David S Waugh)
I wouldn't blame them either. My experience with the TG has been similar. It
works for some releases on some platforms but when you get some weird
issue it's unlikely to be fixed anytime soon. I take it it's too hard to replicate
the data as well?

_____________________________________________________________________

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

http://www.idug.org/solutions-journal.html - home of the IDUG Solutions Journal
Technical atricles from world famous authors in DB2's most prestigious, peer reviewed
magazine now on-line!
_____________________________________________________________________

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

David S Waugh

Re: DB2 z/OS V8 NFM and Oracle Transparent Gateway 10.2.0.3.0
(in response to Jorg Lueke)
Jorg:

What they're doing, essentially, is using application programs to try to keep certain "Legacy" data on DB2 in synch with data on Oracle. So data goes both directions. And there are hundreds of programs doing it, some near-realtime, some overnight, some on-demand...

Oddly enough, when they update DB2 data from an Oracle app via OTG, it doesn't have that same expansion problem - it's only when they try to select DB2 data from an Oracle app via OTG that the CHAR columns are getting tripled in length. Go figure.

This is all a part of a "get off the mainframe and onto an Oracle solution" project they started some years ago. Whenever they stop using an application on the mainframe and start using a similar application on Oracle, they write a bunch of "programmatic data replication" programs to keep the data on both sides in synch (because some other DB2 apps are still depending on the data being on the mainframe, and some Oracle apps still need some data that's being updated by other Legacy apps over on the mainframe).

This "programmatic data replication" is an "interim" solution until they can get everything off of DB2 and onto Oracle. Problem is, Oracle doesn't have application modules for some of the things they do here, so they're having to either (a) wait for Oracle to come up with a viable application module, (b) heavily modify an Oracle app that doesn't do quite what they need, or (c) write their own app.

But in the mean time, they have to live with their "interim" solution, which right now breaks if they try to go to DB2 V8 NFM. Oracle has already told them they won't be retrofitting the TG4DRDA V11.2 fix back to TG4DB2 10.2.0.3, so they're pretty much stuck. I was hoping I could get them unstuck so we can finish the DB2 V8 migration.

David



---------- Original Message ----------
From: Jorg Lueke <[login to unmask email]>
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 z/OS V8 NFM and Oracle Transparent Gateway 10.2.0.3.0
Date: Fri, 4 Dec 2009 18:58:49 +0000

I wouldn't blame them either. My experience with the TG has been similar. It
works for some releases on some platforms but when you get some weird
issue it's unlikely to be fixed anytime soon. I take it it's too hard to replicate
the data as well?

_____________________________________________________________________

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

http://www.idug.org/solutions-journal.html - home of the IDUG Solutions Journal
Technical atricles from world famous authors in DB2's most prestigious, peer reviewed
magazine now on-line!
_____________________________________________________________________

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

____________________________________________________________
Diet Help
Cheap Diet Help Tips. Click here.
http://thirdpartyoffers.juno.com/TGL2131/c?cp=YNgddlZ9mIS6vJQXV95eGwAAJz2i9R_j_Y5ef6WP9NxqQ2yLAAYAAAAAAAAAAAAAAAAAAADNAAAAAAAAAAAAAAAAAAAYQAAAAAA=

_____________________________________________________________________

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

http://www.idug.org/rug/index.html - with almost 150 IDUG Regional User Groups,
there is probably one near you!
Regional User Groups are your local connection to the Worldwide DB2 User Community
_____________________________________________________________________

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

Jorg Lueke

Re: DB2 z/OS V8 NFM and Oracle Transparent Gateway 10.2.0.3.0
(in response to David S Waugh)
David,

Interesting! Having been part of an Oracle to DB2 effort I recognize some
of the solutions. "Interim" indeed. The transparent gateway has never
struck me as an enterprise class solution there always seems to be something
that goes wrong.

_____________________________________________________________________

* 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

David S Waugh

Re: DB2 z/OS V8 NFM and Oracle Transparent Gateway 10.2.0.3.0
(in response to Jorg Lueke)
All:

Thought I should provide some followup, in case others run into this same problem down the road...

Oracle Metalink doc ID 374744.1 says:
------------------------------------------------------------------
Symptoms

Using any Oracle gateway up to release 11.1.0.7 with an Oracle unicode database character set will triple the varchar/char columns precision.

[snip]

Cause

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.

SOLUTION:

Move to Oracle Gateway release 11.2 and make sure to set the parameter:
HS_KEEP_REMOTE_COLUMN_SIZE=ALL

[snip]
-------------------------------------------------------------------

Apparently in 11.2, this new parameter HS_KEEP_REMOTE_COLUMN_SIZE=ALL is supposed to keep the DB2 column data from being expanded to 3 times the original length. How it accomplishes this, I don't know - the above doc didn't say.

My client's decision about our expansion problem has been to halt the DB2 Upgrade project and stay at DB2 V8 CM until TG4DRDA 11.2 is available for the AIX environment.

So, to be a little pro-active for when the time comes, I thought I would ask:

Is anyone out there using TG4DRDA 11.2 on one of the platforms it's currently available on (Solaris or Linux), and have you had any particular problems with using it to access DB2 V8 NFM data?

Given that they run on different platforms (TG4DB2 runs on z/OS and TG4DRDA runs on Unix - and Oracle apparently has no plans to support TG4DRDA 11.2 on z/OS), have you had any platform-related issues that we'll also need to address?

Any other "Gotchas" that we'll need to watch out for?

David Waugh
DSW Consulting & Services


____________________________________________________________
Weight Loss Program
Best Weight Loss Program - Click Here!
http://thirdpartyoffers.juno.com/TGL2131/c?cp=vtmMCjmFoPyea6g5bQqZQQAAJz2i9R_j_Y5ef6WP9NxqQ2yLAAYAAAAAAAAAAAAAAAAAAADNAAAAAAAAAAAAAAAAAAAEUgAAAAA=

_____________________________________________________________________

* 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