---

The Transparent Gateway for DB2 requires a local (Oracle) view of each mainframe DB2 table it accesses. All the views used by the transparent gateway are owned by the Oracle proxy user "ORA2DB2". So, whereas all tables in our DB2 applications are owned by the proxy user "UW", all gateway views of that data are owned by the Oracle proxy "ORA2DB2".

Don't be misled by the term "view". The ORA2DB2 views are fully updateable. You can issue INSERT, UPDATE and DELETE (and of course SELECT) statements against them. (Occasionally the ORA2DB2 view may be "a view of a [DB2] view". If the underlying DB2 is not updatable, then, of course, the ORA2DB2 view cannot be updatable either.)

The ORA2DB2 views will connect

test Oracle to test DB2, and

production Oracle to production DB2.

This is an absolutely firm rule. UWT1 cannot talk to production DB2. And UWP1 cannot talk to test DB2.

Two steps are required to make a test DB2 table available as an ORA2DB2 view:

1. The ORA2DB2.view must be created
2. Privileges to use the view must be granted to an Oracle proxy (maybe to several proxies).

To create a TG4DB2 view, you can run the ora2db2.makeview stored procedure, naming the target DB2 table. Say, for example, you've got a DB2 table called "UW.STUDENT_COURSES" that needs an ORA2DB2 view. If you have the SQL Studio development tool, open a SQL window and run the procedure like this:

begin

ora2db2.makeview( 'student_courses' ) ;

end ;

If you're using SQL*Plus, run the procedure with an execute statement:

execute ora2db2.makeview ( 'student_courses' ) ;

In either case, execution will take a little while... maybe up to a minute. On completion, there will be an Oracle view (e.g. ORA2DB2.STUDENT_COURSES) of the mainframe DB2 table, and -- assuming some Oracle schema has been identified as a remote DBADM for that DB2 database -- some Oracle schema will have SELECT, INSERT, UPDATE and DELETE privileges on the view.

What in the world does that last phrase mean?

It means your subject area proxy must be authorized to use the view. As you no doubt know, DoIT DB2 developers inherit most of their powers to use test DB2 tables by virtue of being DBADM(s) on one or more DB2 databases (where a DB2 "database" is just a logical grouping of related tables). No DB2 developer has access to every DB2 database, however. This remains true when accessing DB2 via the transparent gateway.

Group managers have the authority to designate one or more Oracle proxy users as essentially "remote DBADMs" for their test DB2 databases. These pre-designated assignments are documented in the Oracle table ORA2DB2.DBADM_MAP. This table is readable by anyone. If you're having trouble finding an ora2db2 view that you just created, maybe there's an authorization missing.

If the mapping were defined as above, then every table in the "RG" test database would be available (SELECT, INSERT, UPDATE and DELETE) to the test Oracle schema "RG", and every table in either the "PY" or "IA" database would be available to the Oracle schema "PYUWBA".

If you run the ora2db2.makeview procedure on a DB2 table for which no mapping exists (in table ORA2DB2.DBADM_MAP) -- say, for example, on a DB2 table in the "SB" database -- then the ora2db2.view will be created... but no Oracle schema will be authorized to use it! To register a mapping in the ora2db2.dbadm_map table, DoIT group managers should complete the form "Distributed DBADM Map" available from DRMT.

Note: These Distributed DBADM mappings are only appropriate for very closely-associated groupings of data -- all under the management of a single DoIT group manager. If you need Oracle access to for a DB2 table owned by another development group -- say, you're in human resources and you need access to an accounting DB2 table -- first get approval from the DoIT accounting group manager (Diane Mann), note the type of access (probably read-only), and email the approval to Jeff Lange. He'll get the correct authorization set up for the ora2db2 view.

What do we know about Performance?

In a recent meeting, Neal Deunk asked about relative performance of the Oracle Transparent Gateway to DB2 (TG4DB2). I said I thought I could run some comparisons, and I have done so. I wrote 3 tiny programs doing similar database lookups. Each program performs a series of random lookups based on the row's primary key. The program reports how many of these operations can be performed per second. And the averages are... 970, 19, and 868!

• 970 retrievals per second (rps) is the "all-Oracle" average. Here a PL/SQL package reads an Oracle table all on the same machine (uwt1/Axle).

• 868 rps is the COBOL/DB2 average. Here a mainframe COBOL program reads a DB2 table on the mainframe. It uses a static DB2 plan.

• 19 rps is the gateway average. Here a PL/SQL package (on uwt1/Axle) reads the DB2 table on the mainframe via the TG4DB2.

What do I think I learned?

• Both Oracle and DB2 perform a simple, local, key-based lookup with screaming speed.

• Fetching remote data over the gateway is significantly slower than getting it locally -- on the order of 50 times slower.

• I would not be concerned about performance using the gateway in an on-line transaction. It gives sub-second response that will not be noticeable by a human.

• I would be concerned by a batch application running on Oracle and randomly retrieving lots of DB2 rows. A million such reads would take at least 15 hours elapsed time. A hundred thousand reads would take an hour-and-a-half. (If the data were local, those one hundred thousand fetches would take less than 2 minutes.)

Jeff Lange

Send comments, corrections, requests regarding this document