Tom,

I'm looking for a way to decrease the total run time for a set of ETL processes that, by one measure, operate on about 60 GB of information in each, notionally daily, processing cycle. The source system is a billing application on an IBM z/OS "mainframe," and the billing information is not in a relational database, but in files organized for index access that are known to users of IBM mainframes as VSAM files. The destination system is an Oracle 10gR2 database on an AIX host.

Our existing ETL processes involve extracting from the source system to "flat" files and transferring those files to the database's host by FTP. We present the files to the database as external tables and accomplish the initial transformation and load tasks with multi-table INSERTs from the external tables. Formerly, we were able to write our extract files once, rather than twice, because we made the files available to the Oracle database via NFS. However, we've had to abandon NFS and adopt FTP in something of a rush, because the mainframe's NFS server took a large share of CPU resources that have somewhat suddenly become much more scarce and valuable.

I'd like to find and consider ways to load directly into the Oracle database across the network, without having to read and write the extract files an extra time in the course of transferring them by FTP. At first, putting an Oracle client home on the mainframe and using SQL*Loader seemed to be a possibility worth looking into. However, I was brought up short when I learned that Oracle Database 10gR2 is the terminal release of the Oracle Database on the z/OS platform. I doubt I could persuade anyone (including myself) that using an Oracle 10gR2 client home on z/OS would be a good idea, while we move on to Oracle 11g, "12h", and "13i". I'm hoping you have information that will get me started off in a more fruitful direction.

Thanks.