Re: SQL*Loader and mainframe data

Neil Sauerwein (neils_at_ptd.net) wrote:
>I need to get mainframe data from both DB2 and VSAM to populate an
>Oracle database on AIX. Since there are over 200 tables,I was planning
>on using SQL*Loader by running the creates on DB2, generating the
>SQL*Loader control files using the DB2 unload utility, and downloading
>these files along with the data which will be formatted in table format
>by COBOL programs. Of course I found that after FTPing the data, the
>binary record lengths for the varchars had been rendered unusable. Does
>anyone have any experience with something like this? Is there an easier
>way? Should I forget SQL*Loader and deal with each table in JAVA and
>PL/SQL?
There are several ways to do this with or without SQL*Loader.

The least sophisticated approach is to unload the data in all-character form--all numbers converted to character representation and no binary lengths--and then ftp and load as in your first attempt. You can let ftp do the character translation or you can ftp binary and have SQL*Loader do it (CHARACTERSET option on the LOAD control statement).

If it's something you are going to do regularly and/or with a lot of data, you might want to license the Oracle client code on your MVS system. This will let you run SQL*Loader on MVS (where Loader understands all of the native S/390 number formats) and load via SQL*Net (TCP/IP or SNA LU6.2). Note that Loader is restartable if there is a failure partway through a load; ftp is not. You will still have to unload the DB2 data into a file that SQL*Loader can read (VSAM or "flat").

Finally, Oracle has gateway products that will let you copy DB2 tables directly into Oracle (with something like "CREATE TABEL foo AS SELECT blah FROM db2-table") as well as full heterogenous replication functions that will automatically propagate DB2 updates to Oracle servers.

I think I've listed these in increasing order of sophistication.   Your Oracle rep should be able to give you more details on the "MVS Client" package as well as the gateway and replication products. If not, you can email me.

/b

--
Bill Manry  -  IBM Products Division  -  Oracle Corporation
These are my opinions, not necessarily Oracle's.
Remove "." from "B.Manry" to email me.