06-22-04, 19:33 #1Registered User
- Join Date
- Feb 2004
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 ?
06-22-04, 23:41 #2Registered User
- Join Date
- Apr 2003
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..
06-23-04, 07:55 #3Registered User
- Join Date
- Jul 2003
- Austin, TX, USA
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
Ask the experienced rather than the learned