Design alternatives for Unload of Oracle data to pipe

Hello Oracle experts;
We are moving large amounts of data on a regularly scheduled basis from an Oracle (9i AIX) OLTP application to a multi-terabyte DB2 UDB (DB2 PE 8.1.5 - also AIX) database. We are very experienced data warehouse developers and have a fairly robust homegrown batch architecture for provisioning data from mainframe sources and federated DB2s on daily and weekly cycles.

This is our first major effort using Oracle as a data source and I'm struggling with the best way to officially integrate into our existing architecture. Here is what we have done so far:
* We are interested in a "pull" type process where the data warehouse
box kicks off and manages as much of the processing as possible, so we have installed the Oracle client on the DW box and plan to use as much of our existing control processes and scripts as possible - much of which is written in Perl (some C, some Korn).

• Due to the absence of an callable Export utility in Oracle, we "stole" some interesting code posted on this site to export to a pipe using sql plus and spooled output. Db2 picks up the pipe with its load utility and we're off to the races.
• This works very well, with acceptable performance. The issue we have is that the sql plus interface makes it difficult to pick up errors. When we encounter an error, it gets spooled along with the data to the pipe that DB2 tries to load. Not so elegant...

Questions:
* Is there any way for us to interrogate a SQLCA-like message area
using this method or should we pursue an alternate design?

• What other methods would you all suggest? A direct JDBC or Perl-DBI interface? How to drop the output into a pipe?

Am most interested in hearing your perspectives. Thanks in advance for any useful suggestions or code samples! Received on Wed Mar 23 2005 - 09:02:44 CST