Hi All,

We recently set up 2 DB2 9.5 Servers, 1 for production and 1 for Testing with similar configuration.

When we execute a query on Production server, It gets executed in milli seconds thru command editor.

Where as the same query on Testing server, it takes more that 3-4 seconds. One difference while setting up Testing server is, the memory was upgraded after installing DB2.

The Memory usage is around 4 GB out of the 16 GB RAM on testing server. And CPU usage is very minimum.

On both Database, we have setup Maintainence for Reindexing & Reorg to run every night.

I need some pointers to check & isolate the issue on Testing Server as why it is taking 3-4 seconds to execute a simple query.

Any help from DB2 experts is highly appreciated.

Thanks

What are the sizes of the bufferpools?
Different indexes on Tables?
Check Statistics for the Tables/Indexes. Are they the same (Numbers of Rows, ...)?
dbm and db config

Are the access plans are the identical on both systems?

The numer of rows are infact less in Production Server. I have updated the statistics manually also. The tables & indexes are same in both the Databases.

Size of the bufferpool is same.

Different amount of rows may change the access plans.
Have you checked the access plans in Prod and Test?

hiya,

Can you please share EXPLAIN (preferably db2exfmt output) for SQL from both production and test environments?

Hi,

Are you sure that the databases in test env are already activated when you execute the query?

Activate means that there is at least one established connection, or the activate db command has been issued.

Also, take a look at the access plan, and compare the timeruns between both envs. Probably, the statisques are not the same, thus the time is different in both envs.

Thanks for all you suggestions. I will try to get the access plans for both Production & Testing Server.

Hi all its very helpfull information to all of us


Thanks & Regards
Siddhu