so my concern is unfounded. But I would appreciate if I can get someone
or Serge to confirm. Also unlike the question asked in the post below,
my question involves non-partitioned table loads.
I want to know if, in general, loading from cursor is slower than
loading from a file? I was thinking cursor would normally be faster,
because of DB2's superior buffer/prefetch management than that of OS's
(AIX).
Now to my specific question. I need to transfer huge amount of data
from one DB to another DB, both are DB2 8.2.4 running on the same
server. Would I be better off exporting data to files and then loading
from the files, or, will it be faster to define nicknames and load from
federated cursors, since it will avoid having to create files?
Thanks
P Adhia
From: Serge Rielau - view profile
Date: Wed, Jan 26 2005 10:31 am
Email: Serge Rielau <srie...@ca.ibm.com>
Groups: comp.databases.ibm-db2
Not yet rated
Rating:
show options
Joachim Klassen wrote:
Hi all, first apologies if this question looks the same as another one I recently
posted - its a different thing but for the same szenario:-). We are having performance problems when inserting/deleting rows from a large
table.
My scenario: Table (lets call it FACT1) with 1000 million rows distributed on 12
Partitions (3 physical hosts with 4 logical partitions each).
Overall size of table is 350 GB. Each night 1.5 Million new rows will be
added
--snipped--
I'm not privy of index maintenance internals, but could it be the 7
indexes cause a spill of some heap? Maybe sort heap? Have you checked
the snapshots?
Have you verified that the plans are good? You shouldn't see any TQs.
Also are you sure you don't have any other complicating factors (SQL
Functions, Triggers, check or RI constraints) (The plans will show). PPS: We are parallel investigating in MDC tables, using smaller tables (and
combining them with a UNION ALL view) and the use of LOAD FROM CURSOR
instead of INSERT
Be careful with LOAD FROM CURSOR, the cursor is a bottle neck. To do
that in a scalable fashion you would fire up concurrent LOADs on each
node filtering the source by DBPARTITION.
You shouldn't need UNION ALL.
Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab