Hi All,
I have an interesting requirement and am hoping that I can obtain a
solution here.
I have the need to get several billion rows from multiple Oracle tables
to tables on a "Netezza" database. I have tried several ways to get the
data over to Netezza using Perl DBI by opening two database connections
and doing "SELECT" on one side and "INSERT" on the other. However,
Netezza is not designed to perform single row INSERTs and UPDATEs. It
is more for bulk INSERTs and bulk UPDATEs.
So we have also tried the Netezza tool using EXTERNAL table method where
we create EXTERNAL tables (which are text files) then doing SELECT INTO
from the EXTERNAL tables into the actual database tables. This method
is VERY fast, but requires dumping out the data into files (EXTERNAL
tables). When dealing with such large tables, it gets ugly with files.
Our most recent plan is to NFS mounting the Netezza file system (Linux)
to the Oracle server (Solaris). Then creating named pipes to write the
SELECTed data from Oracle and have Netezza side read in the data
directly into the database using Netezza's bulk load method. Here is
how you would create an EXTERNAL table:
CREATE external TABLE ex_table (
dlvrb_gid bigint,
prc_rel_gid bigint,
prdct_gid bigint,
bil_pymt_typ_cde character(1),
prjtd_trx_cnt numeric(10,0)
)
USING (
DataObject ('/SOME_FILE_NAME')
DELIMITER '|'
DATEDELIM '-'
TIMEDELIM ':'
DATESTYLE 'YMD'
NULLVALUE ''
TRUNCSTRING ON
CTRLCHARS ON
FILLRECORD ON
LOGDIR '/PATH_TO_THE_LOG_DIRECTORY'
MAXERRORS 1
);
Then you would execute the statement "insert into real_table select *
from ex_table".
The problem I am having is, when I execute "INSERT INTO real_table
SELECT * FROM ex_table" before I start writing to the named pipe, this
process immediately stops because there is no data to SELECT. But if I
start writing to the named pipe before starting the "INSERT INTO", I may
miss some data.
I was wondering if anyone might have an answer as to how I can sync the
start up of the write and INSERT INTO at the same time or perhaps some
other method.
Thanking you in advance.
Peter
This E-mail message is for the sole use of the intended recipient(s) and may
contain confidential and privileged information. Any unauthorized review, use,
disclosure or distribution is prohibited. If you are not the intended
recipient, please contact the sender by reply E-mail, and destroy all copies of
the original message.
