Data load/unload

[l_na...@yahoo.com]

Hello all, I am trying to create a datawarehouse ( 15 GB in size) by
exporting data out of our production database (60 GB in size). One of the
tables has about 15 million rows and when I try to load into the warehouse
using 'insert into DW_TAB1 SELECT * from PROD_TAB2;' the transaction is
aborted. I cannot do a partitioned select statement to reduce the the result
set. Also, I tried to unload the data from the tables into a ascii file and
load it into the datawarehouse. But, this process is excruciatingly slow. I
need to load the data everyday. After the first full import is completed, is
there any way to do a incremental unload from the production database and an
incremental import into the datawarehouse???

TIA
Nadella

-----------== Posted via Deja News, The Discussion Network ==----------
http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own

[Chuck Renaud]

> Hello all, I am trying to create a datawarehouse ( 15 GB in size) by
> exporting data out of our production database (60 GB in size). One of the
> tables has about 15 million rows and when I try to load into the warehouse
> using 'insert into DW_TAB1 SELECT * from PROD_TAB2;' the transaction is
> aborted. I cannot do a partitioned select statement to reduce the the
> result set. Also, I tried to unload the data from the tables into a ascii
> file and load it into the datawarehouse. But, this process is
> excruciatingly slow. I need to load the data everyday. After the first
> full import is completed, is there any way to do a incremental unload from
> the production database and an incremental import into the
> datawarehouse???

It'd be helpful if you provided more information...what version of IDS,
what platform, etc...

You say the transaction is aborted...what error message(s) are you
getting?

Have you tried specifying columns instead of using "select *"? I
thought that was a requirement, but maybe not...

Your "data warehouse" appears to just be a table in the same
database...are they separate databases? Are they in the same
server instance or in different instances?

You really need to provide more info...you can also look at using
HPL to unload from one database and load into another...

[Obnoxio The Clown]

From: l_na...@yahoo.com

>
>Hello all, I am trying to create a datawarehouse ( 15 GB in size) by
>exporting data out of our production database (60 GB in size). One of
the
>tables has about 15 million rows and when I try to load into the
warehouse
>using 'insert into DW_TAB1 SELECT * from PROD_TAB2;' the transaction
is
>aborted. I cannot do a partitioned select statement to reduce the the
result
>set. Also, I tried to unload the data from the tables into a ascii
file and
>load it into the datawarehouse. But, this process is excruciatingly
slow. I
>need to load the data everyday. After the first full import is
completed, is
>there any way to do a incremental unload from the production database
and an
>incremental import into the datawarehouse???

A couple of things here:

1. WARNING: Your *really* shouldn't build a data warehouse on the same
box as your OLTP system - the tuning requirements are mutually
exclusive. It is possible to indulge in a lot of jiggery-pokery, but I
really don't advise it.

2. A schema of the table you're extracting from would be useful. If
you have a "date inserted" column in PROD_TAB1, then the incremental
extract should be "trivial". Otherwise you're going to have to do
something horrid like: get key values from DW_TAB1 into temp table,
insert into DW_TAB1 select * from PROD_TAB1 where key value not in
(select key value from temp table)

3. The error you get when your INSERT fails would be useful. I'm
guessing it's a LONG TRANSACTION ABORTED, so you probably need to add
more logs.

That's about all I can say with the information presented.

HTH.

______________________________________________________
Get Your Private, Free Email at http://www.hotmail.com