Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    May 2012
    Posts
    7

    Question Unanswered: Load (from cursor) commit taking WAYYYY too long

    I've not been able to find anything on the web that explains the following. I am working on rebuilding some tables. As such I am loading the data from the old table into the new table using load from cursor. The actual load and subsequent index build actually goes very quickly. What's taking too long is the "commit" at the end once all of the indexes are done. Here's an example. I loaded 620 million rows from the old table to the new. The load itself only took about 20 minutes or so. The index build took about an hour. The "commit" portion afterwards took almost 3 HOURS!!!!

    I've been told that it could be the history file that is causing this, but our history file is only 15MB large.

    Does anyone have any explanations and/or solutions? I have several more tables to do (all of them MUCH larger than this table)

  2. #2
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    Always post your DB2-server version and Fixpack and Operating-System Name when posting.

    Is this BCU / DPF environment?

  3. #3
    Join Date
    May 2012
    Posts
    7
    Running DB2 9.7 FP4(i think) on AIX and this is a DPF environment. All of the tables I am loading are also range partitioned.

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Are you indexes partitioned or global? You can tell if you do a db2look.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    How do you determine the duration of what you call a commit portion?

  6. #6
    Join Date
    May 2012
    Posts
    7
    To answer Marcus_A's question first... most of the indexes are partitioned... but that's not really an issue here. The index build portion of the load goes fast. When you look at the output of the "list utilities show detail" over the course of the load the index build finishes relatively fast. Its that stretch of time after the index build is finished that the load seems to just sit there and stare at me.

    So n_i to answer your question I would determine the duration of what I am calling the commit portion as that time between when the index build is completed and the actual load finally completes. There are three phases to a load that I can actively watch: SETUP, LOAD and BUILD. Its that time after the BUILD phase is done that i'm trying understand what is happening and if there is a way to speed up.

  7. #7
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    There is a DELETE phase as well.

    Is your LOAD successful or do you have a number of duplicate PK/unique index records ?

    Check your load output file


    ==
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  8. #8
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    In additon to sathyaram_s's question:

    Is your target table enabled for compression? Do you see any messages in the db2diag.log after the build phase is done? What does "list utilities show detail" or "db2pd -util" show during this "commit time"? Any lock contention in the snapshots?

  9. #9
    Join Date
    May 2012
    Posts
    7
    my loads are successful and my loads have never done any deletes. There's no constraints on the new tables save for the PK. The data is coming from tables currently in production and have all the uniqueness handled. All of my load output statements show 0 rows deleted. As I stated earlier, the load and index build time isn't the issue here... its that time after the index build(s) is done that seems to take forever. Is it trying commit something some place in the system catalog or update a history file or something that I'm just not seeing?

  10. #10
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    Please post the full set of options you gave to the LOAD .

    Please post the number of partitions, and the number of ranges for one impacted table , and the total data size and total index sizes, so that we can understand the scale.

    Please post the size of bufferpool for the system temporary tablespace relative to the size of the indexes.

    It may be that the index-copy phase is the issue, but there must be some indications in the db2diags and also in snapshots.

  11. #11
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    After the index build , check the output of
    db2pd -utilities

    and also check if there are any latches

    db2pd -latches
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  12. #12
    Join Date
    May 2012
    Posts
    7
    db2girl yes my tables are enabled for compression and there is nothing in the diag log that seems out of place. If you were able to watch the list utilities in real time you would see the index build phase build all the indexes and then just sit there not doing anything for a while (depends on how big the load is as to how long it sits there). I have not seen any lock contention anywhere.

  13. #13
    Join Date
    May 2012
    Posts
    7
    Sathyaram_s, I had to run db2_all db2pd -utilities and I pretty much what list utilities shows me on in a much neater format (thanks for that by the way).

    When I ran the -latches and didn't get anything returned for nodes 1-14 but on nodes 15 and 16 I got the following:


    Database Partition 15 -- Active -- Up 0 days 14:47:22 -- Date 05/30/2012 08:50:40

    Latches:
    Address Holder Waiter Filename LOC LatchType HoldCount
    0x070000002147AED0 20279 0 sqlbbuffers.C 6518 SQLO_LT_SQLB_BufferPool__prevBPDCachingLatch 1
    0x07000001926C6508 20279 0 sqlbpacc.C 2459 SQLO_LT_SQLB_POOL_CB__writeLatch 1
    0x07000001926C6580 20279 0 sqlbilatch.C 1407 SQLO_LT_SQLB_POOL_MAP_CB__range_latch 1
    0x07000000213EB640 20279 0 sqlpsvpt.C 177 SQLO_LT_SQLP_SAVEPOINTS__spLatch 1
    dwdmp4x: db2pd -latches completed ok


    Database Partition 16 -- Active -- Up 0 days 14:46:12 -- Date 05/30/2012 08:50:40

    Latches:
    Address Holder Waiter Filename LOC LatchType HoldCount
    0x070000002147AED0 18986 0 sqlbbuffers.C 6518 SQLO_LT_SQLB_BufferPool__prevBPDCachingLatch 1
    0x07000001926C6508 18986 0 sqlbpacc.C 2459 SQLO_LT_SQLB_POOL_CB__writeLatch 1
    0x07000001926C6580 18986 0 sqlbilatch.C 1407 SQLO_LT_SQLB_POOL_MAP_CB__range_latch 1
    0x07000000213E3E40 18986 0 sqlpsvpt.C 177 SQLO_LT_SQLP_SAVEPOINTS__spLatch 1
    dwdmp4x: db2pd -latches completed ok


    Don't let the "Up 0 days" throw you by the way. We had a server restart last night.

  14. #14
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Do, db2pd -edus and find what 18986 and 20279 are ?
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  15. #15
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    A similar issue discussed here ...

    http://www.dbforums.com/db2/1652165-...ive-state.html
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

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