Re: Design alternatives for Unload of Oracle data to pipe

peteh wrote:

> 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!

As your data warehouse appears to be home grown my suggestion would be to upgrade it to 10g for several reasons such as 8.1.5 being desupported and 10g having a far superior optimizer and performance.

If you did that you could use transportable tablespaces, streams, and the new DBMS_SCHEDULER package not to mention getting your resume current to this millenium.

I would expect the improvements will be substantial in all areas.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)