Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2009
    Posts
    36

    Unanswered: Import Vs simple inserts

    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.

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    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
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Mar 2009
    Posts
    36
    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

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    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
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  5. #5
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    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
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  6. #6
    Join Date
    Mar 2009
    Posts
    36
    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

  7. #7
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Quote Originally Posted by db2pro View Post
    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 ...
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  8. #8
    Join Date
    Mar 2009
    Posts
    36
    That was really awesome!! thank you

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •