Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2005
    Posts
    24

    Unanswered: What to do for better performance?

    Dear All,

    May I know what should I do to get better preformance for DB2 table?
    I have been done the following steps but still not improve the performance.
    1.Reorganize the table
    2.runstats the table
    3.rebind

    Is there any other steps I still need?
    I would like to get the faster performance when accessing my table.
    Help me pls.

    Thnaks in advance,

  2. #2
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,
    have you tried creating some indexes?
    Look into select statements that are running slow and the for "where columns" create indexes, just like:
    db2 "create index yourschema.yourindexname on yourshema.yourtable (column1, column2, etc)"

    Hope this helps,
    Grofaty

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Take your workload and run it through the Design Advisor. Indexes is one important component; MQTs or MDCs may also be helpful.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  4. #4
    Join Date
    Aug 2005
    Posts
    24
    Dear All,

    I already have indexes. So it is not an index problem.
    What I would like to know now is the factors other than indexes such as runstats and so on.

    Is there any idea please?


    Thanks in advance,

  5. #5
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Please run your workload through the Design Advisor.

    As for what would be important or not: it really depends on your overall system. If you have only a single hard drive, for instance, then you don't have to worry about parallel I/O. If you have a lot of disks, it would be a completely different scenario. Thus, it's very hard to give you specific advise here.

    The general rule is to have the proper indexes and to make sure that statistics are up-to-date. Placement of data and logs on disks is also important. Everything else are more "advanced" things. You should first be very familiar with the respective concepts - or let DB2 figure things out for you.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  6. #6
    Join Date
    Jul 2005
    Posts
    47

    Improving Performance by changing DB Config Parameters

    Hi,

    You will be able to get better performance if you change the following DB CFG parameters.

    Default query optimization class (DFT_QUERYOPT)
    SQL statement heap (4KB) (STMTHEAP)
    Try keeping them low.This worked out for me.

    Good Luck.

  7. #7
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Keeping those settings low may work for you - but it may not work for others. I know of one scenario where a query run more than 30 hours. Increasing the optimization level and statement heap resulted in a significant increase of compilation time (from some 10 minutes to 1.5 hours), but the overall query execution time came down to single-digit hours.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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