A SQL that used to run in four seconds now taking 5.5 seconds. The query response time started increasing gradually like 4.1 second on day 1, 4.3 seconds on day 2, 4.4 seconds on day 3...and now it is running for 5.5 seconds in two weeks. I would like to find out the reason for the increase. I need help in where to start looking.

There was no change in hardware, data volume, system config, instance config, db config,etc. I have history of snapshots (dynamic sql, bufferpool, database, etc.) data available in a seperate database, so, I can look back.

Any pointers on where to start looking are warmly welcomed! Thanks!!

Try reorging the table(s) involved in the query.

Thanks for the reply Marcus, Reorg and Runstat did not help, still seeing the same pattern. Any other thing I can look? Thanks.

Did the access plan for the query change?

Also, how do you measure query execution time? If it is end-to-end from the client application or directly in DB2? In e2e scenarios, you may have changes in the application causing delays or maybe network issues.

Thanks stolze!
Comparision of Explain plans between before and after the issue shows no difference in the path. Path showed index scans only before and after. This SQL is used as benchmark SQL, so, I collect all the information on this SQL.

Query execution time is from dynamic sql snapshot, so, it not the client application value (so, I can eliminate network factor)

Did you have any statements being executed concurrently? If you have the full snapshot, you can compare the detailed numbers to see where time is being spent more now.

I am observing weird pattern lately, slow query occurs only during specific days. Also, during the slow response days, query response time increases as the day progress.

For example,
Every week Thursday the query response time gradually increases from 4.1 to 4.2, 4.3,4.4,...till 6 seconds as the day progresses. And, every week Wednesday, the query response time is well below threshold (eg. below 3 seconds) all day.

I would like to get suggestion from this forum, I am sure some of you might have come across this situation.

Thanks for inputs.

You wouldn't happen to be stopping and starting DB2 on Tuesday night Wednesday morning would you? Is there som process that only runs on the slow days?
Dave

no, DB2 is running the whole week, db2stop and db2start happens once in a week on Sunday.

There is no difference is code between slow vs regular days, and there is no special jobs run either

Thanks for reply