Results 1 to 4 of 4
  1. #1
    Join Date
    May 2012
    Location
    Canberra, Australia
    Posts
    176
    Provided Answers: 6

    Unanswered: Index creation time

    10.5.5 on Linux
    We are repeatedly running a database migration process to refine it in preparation for the introduction of a new application and have noticed that the time for creation of three indexes on a particular table has blown out significantly (from about 10 mins to 1 hour) even though the table data has only increased a small amount (20%). The db size is about 130Gb and the VM has 90Gb of memory so there is plenty of memory space for temp tablespace buffer pools. There is no compression on this particular table at the time of the index creation. What I'm trying to work out is what could be causing the increase in the index creation time. Correct me if I'm wrong but I believe that the index creation would involve a tablescan and sort for each of the indexes in which case I would expect that the only place for the time cost would be in the sort. And, curiously, it almost seems like it's become worse since we've added more memory!
    We've tried to catch this particular activity in db2top but it usually occurs at an inconvenient time. We will attempt to check the bufferpool hit ratios at the time of index creation. Any suggestions as to where we should look?
    I should have added that any db cfg parm that can be auto is set to auto
    Andy

    "All parts should go together without forcing. You must remember that the parts you are reassembling were disassembled by you. Therefore, if you can’t get them together again, there must be a reason. By all means, do not use hammer.” — IBM maintenance manual, 1975 "

  2. #2
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    It might be enough SORTHEAP for your indexes previously and now your sorts are spilled. Check the sort overflows for your statements / application in the package cache.
    Starting from fp5 you have an ability to monitor sorts for the particular application with:
    db2pd -sort apphdl=myAppHandle
    Regards,
    Mark.

  3. #3
    Join Date
    May 2012
    Location
    Canberra, Australia
    Posts
    176
    Provided Answers: 6
    Thanks Mark
    Andy

    "All parts should go together without forcing. You must remember that the parts you are reassembling were disassembled by you. Therefore, if you can’t get them together again, there must be a reason. By all means, do not use hammer.” — IBM maintenance manual, 1975 "

  4. #4
    Join Date
    May 2012
    Location
    Canberra, Australia
    Posts
    176
    Provided Answers: 6
    The curious part is that we can drop and create and the index after the trial migration is complete and it runs in about five minutes as opposed to 30 minutes. We'll have to catch it in flight to see if we can determine what is happening. Very odd
    Andy

    "All parts should go together without forcing. You must remember that the parts you are reassembling were disassembled by you. Therefore, if you can’t get them together again, there must be a reason. By all means, do not use hammer.” — IBM maintenance manual, 1975 "

Posting Permissions

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