Import Vs simple inserts

db2pro · #1 (**permalink**) 02-24-10, 13:18

Hello Experts,

I need your inputs on the following issues.

I have to move data from one table to another once every month both tables having same table def and partitioned by same key.No of records we are looking at is 1.5 billion.Load is not a option here as it locks the the target table.Options i am left with is direct inserts and import.I know import utility takes care of transaction log fill situation and commits the data as and when needed which is not the case with inserts and one has to find out the transaction log size and commit accordingly.My questions

1) Which method is better direct inserts or import what are the pitfalls??

2) I can do a export based on partitions and then import them.If i do this way will all the imports go through co ordinator node?? Is there a way in which this can be done parallely into each partition???

both tables we are looking at is partitioned and has 3.5 million records per partition.

Thanks,
Looking forward.

sathyaram_s · #2 (**permalink**) 02-24-10, 13:50

What db2 version and platform???

Given the choice of your words, I have assumed it is DPF on LUW ..

Have a look at the LOAD option ALLOW READ ACCESS

With this volume of data, i would try to do this in relatively smaller chunks, say 20m each time ...

Have you considered range partitioning your table based on month (in addition to your hash partitioning) ? In this case you can load the data in small chunks every night into a new table and ATTACH it to the master table at the end of the month ... Just a thought ... Well, this not just to make this load job easier, but it is very likely to support your queries too

Cheers
Sathyaram

db2pro · #3 (**permalink**) 02-24-10, 13:57

Thanks Sathyaram,

Yes its is DPF.Load with allow read access doesnot work.As this option looks for ZLOCK while commiting.There will be other application running simultaneously on the target table which cannot be disrupted.I tested all the load options and it is not a option.Based on my envirmont i can commit 1 billion rows in one go.That is not a problem.I want to know which is faster and better approach...Select insert from one table to another or export into a file based on each partition and then import??

Range partition is an option we are not going with right now.We want to come up with a solution without it.

We are on 9.5 Linux

sathyaram_s · #4 (**permalink**) 02-24-10, 14:33

Well, right ... load will need z-lock at some point in time ...

You can commit 1B rows in one go, ie, two-thirds of the data ... so,two-thirds of 3.5m, ie, 2m+ rows on each partition .. is your locklist and maxlocks big enough to support row locks on this many records? if not, then the rows locks will be escalated to table locks that may prevent the other appl working on the target table to fail (unless it is using UR) ..

What will be the state of the source table at the time of this load ??? will it be 100% accessible ?
if so, in your scenario, INSERT ... SELECT will be better as it will be a co-located set based operation rather than thousands of single row operation in case of IMPORT ...

if your source table access will cause issues, then the option left is to export the data (preferably using db2_all) so that the data is stored in the same partition and IMPORTing it back using db2_all ...

Cheers
Sathyaram

sathyaram_s · #5 (**permalink**) 02-24-10, 14:43

Two things to add :

a) if you decide to use IMPORT, then you may want to look at ALLOW WRITE ACCESS option in IMPORT command... Avoid ALLOW WRITE ACCESS if the 'other appl' does UR

b) if the 'other appl' accessing the target table is doing UR, and you are using INSERT, then before starting the Xn acquire a table lock using LOCK TABLE stmt ... this will avoid the overhead of the engine taking row level locks and escalating at a later point in time

db2pro · #6 (**permalink**) 02-24-10, 14:45

Cool..

Total rows will be inserted is around 1.5 billion about 3.5 million per partition.All the applications running are using UR and the source table 100 % accessible.

The secnario goes like this

Target table has data for OCT & NOV ..Now applications will be accessing this data and we have to load Dec data simultaneously into this target table from source table.This load process should be insert or a import is the dilema i am in.

Looks like form your opinion Inserts are the best way to go in my situation

sathyaram_s · #7 (**permalink**) 02-24-10, 15:22

Quote:

Originally Posted by db2pro

Looks like form your opinion Inserts are the best way to go in my situation

Yep .. A small test on db2 9.7 express edition

import of 20K records :

Code:

 Number of executions               = 20000
 Number of compilations             = 1
 Worst preparation time (ms)        = 0
 Best preparation time (ms)         = 0
 Internal rows deleted              = 0
 Internal rows inserted             = 0
 Rows read                          = 0
 Internal rows updated              = 0
 Rows written                       = 20000
 Statement sorts                    = 0
 Statement sort overflows           = 0
 Total sort time                    = 0
 Buffer pool data logical reads     = 20149
 Buffer pool data physical reads    = 51
 Buffer pool temporary data logical reads   = 0
 Buffer pool temporary data physical reads  = 0
 Buffer pool index logical reads    = 0
 Buffer pool index physical reads   = 0
 Buffer pool temporary index logical reads  = 0
 Buffer pool temporary index physical reads = 0
 Buffer pool xda logical reads      = 0
 Buffer pool xda physical reads     = 0
 Buffer pool temporary xda logical reads    = 0
 Buffer pool temporary xda physical reads   = 0
 Total execution time (sec.microsec)= 1.720240
 Total user cpu time (sec.microsec) = 1.411987
 Total system cpu time (sec.microsec)= 0.000000
 Total statistic fabrication time (milliseconds) = 0
 Total synchronous runstats time  (milliseconds) = 0
 Statement text                     = INSERT INTO summ_order_table ("PROD_ID","QTY") VALUES (CAST (? AS  INT)                         ,CAST (? AS  INT)
                   )

insert select of 20K records

Code:

 Number of executions               = 1
 Number of compilations             = 1
 Worst preparation time (ms)        = 37
 Best preparation time (ms)         = 37
 Internal rows deleted              = 0
 Internal rows inserted             = 0
 Rows read                          = 20005
 Internal rows updated              = 0
 Rows written                       = 20000
 Statement sorts                    = 0
 Statement sort overflows           = 0
 Total sort time                    = 0
 Buffer pool data logical reads     = 40208
 Buffer pool data physical reads    = 105
 Buffer pool temporary data logical reads   = 0
 Buffer pool temporary data physical reads  = 0
 Buffer pool index logical reads    = 4
 Buffer pool index physical reads   = 3
 Buffer pool temporary index logical reads  = 0
 Buffer pool temporary index physical reads = 0
 Buffer pool xda logical reads      = 0
 Buffer pool xda physical reads     = 0
 Buffer pool temporary xda logical reads    = 0
 Buffer pool temporary xda physical reads   = 0
 Total execution time (sec.microsec)= 0.312067
 Total user cpu time (sec.microsec) = 0.074799
 Total system cpu time (sec.microsec)= 0.000000
 Total statistic fabrication time (milliseconds) = 0
 Total synchronous runstats time  (milliseconds) = 0
 Statement text                     = insert into summ_order_TABLE select * from order_table fetch first 20000 rows only

summ_order_table has no indexes ... indexes will skew the results in favour of insert...select further ...

db2pro · #8 (**permalink**) 02-24-10, 16:22

That was really awesome!! thank you

Internet Services

Web Development

Digital Marketing

Consumer Tech