Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004
    Posts
    34

    Unanswered: DB2 - Database Performance Problem

    DB2 ver 6.1 UDB
    Win NT 4.0

    There are two database on the same server.
    (1) Test and (2) Training

    Test database is working fine but as soon as user load the data on Training Database - performance gets slow down with that Training Database.

    I don't know much DB2 but as my visitor consultant gave me the script to run on these database on frequently basis.

    These script covers, REORG and Update Statistics on tables. Nothing else in this script.

    After this data load - if I run this script, database is comming to normal mode.

    But manager is asking me, why we need to run this script as soon as they do data load ? Data load may be of 1 GB.

    They also follow the same practice with Test database on the same server but they don't need to run these script everytime.

    There is sufficient RAM - There is sufficient harddisk space - there is no container which shows 100 % usage - i increased this size too.

    How to come out from this problem ?

    How to find the diff. between Test Database & Training Database ?

    Any Advice / help please ?

  2. #2
    Join Date
    Apr 2003
    Location
    Singapore
    Posts
    59
    how are ur indexes? Too many or too little also make quite a differnce.
    You can use the db2advis tool which comes shipped with db2 on the CLP to check whether ur query needs any indexes..

  3. #3
    Join Date
    Jul 2003
    Location
    Austin, TX, USA
    Posts
    278

    Possibilities

    Hi,

    What do you mean by performance degradation?

    Taking a clue from your post I am attempting to answer the issue with respect to sql performance.

    When you have an active data load (here i consider a lot of inserts/deletes/updates on all/certian tables) happening on the database there is a great possibilty that the statistics available in the db2 catalog tables get outdated. So the next subsequent time a user executes a query db2 engine/optimiser does not get the right picture and hence the access plan is generated based on the old statistics and there is every possibility the query will take longer to execute.

    Now the script provided to you (reorg and runstats) will be just going ahead and updating the statisitics on these tables hence now the optimizer has a clear and right picture and hence the query performance improves.

    Please post additonal information on what else you need.
    HTH

    Nitin

    Ask the experienced rather than the learned

Posting Permissions

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