Re: Oracle Gateway for DB2/400

Hi Bernard:

We use Oracle Transparent Gateway for DB2/400 (Oracle7.3.4 and Oracle8.0.4, OS/400 V3R2) and Oracle Access Manager for AS/400 (same versioning). OTG lets you create database links that point to an AS/400 rather than another Oracle instance, making the AS/400 *look like* another Oracle instance, with some limitations:

1. Performance is not great with OS/400 V3R2 (the AS/400 native SQL processing stinks in earlier versions of OS/400). I *think* you'll get better results with OS/400 V4Rx. There are a few tricks you can use to improve performance:
   • The ORACLE subsystem on the AS/400 is created by default at a run priority of 30. You can re-create the subsystem class definition with a higher run priority (get you AS/400 sys admins to help, it requires a backup of the library).
   • In the TNSNAMES.ora member and the connecting Oracle server, set the SDU (session data unit) parameter to a larger size (up to 32K). This will reduce network round trips.
2. Commits are handled a little differently between DB2 and Oracle. On a commit, Oracle closes write cursors but leaves read cursors open; DB2 closes both, unless you specify a COMMIT WITH HOLD. You obviously can't specify COMMIT WITH HOLD in Oracle, so this problem will show up if you create stored procedures that open a CURSOR loop against files on the AS/400. When you perform a COMMIT in the stored procedure, the AS/400 will close the reading cursor; on the next fetch, you'll get a "FETCH OUT OF SEQUENCE" error back from the AS/400. The work-around is to not do any COMMIT's until you've finished FETCHing.
3. Reliability is pretty good. The ORACLE subsystem on the AS/400 is actually just a TNS listener process, so as long as that is running reliably, you shouldn't have any problems.

Good luck,

--
David C. Sisk
The Unofficial ORACLE on NT site
http://www.ipass.net/~davesisk/oont.htm

bernard_lambert_at_ibm.net wrote in message <3777FCC7.9C3D9451_at_ibm.net>...
>
>Hello everyone,
>
>We are currently planning to buy Oracle Gateway for DB2/400. We want to
>exchange data between a DB2/400 database running on OS 400 4.4 and an
>Oracle 7.3.4.2 database running on HP/UX 10.20. Since we want to avoid
>using "ODBC" type of products that would require an extra NT box in the
>middle, the Oracle Gateway seems to be the only serious candidate.
>
>I did a little bit of search on the net and the performance seems to
>surface as the major issue but there are some workarounds.
>
>Is there anyone out there that has experience with the product? Any
>possible pitfalls or roadblocks? How robust the product really is? Does
>the product require a lot of maintenance at the AS/400 and at the Oracle
>level? Are there any conditions under which you would not recommend the
>use of Oracle gateway?
>
>Thanks,
>
>Bernard Lambert
>DBA
>
>
>
Received on Mon Jun 28 1999 - 22:02:38 CDT