DB2 Index Monitoring - taming the tiger

DB2 Index Monitoring

Most DBAs would agree with the point that in most DBMS perform very well initially when a new database is created, or when a new schema is created. That happens because when and RDBMS create a schema objects, initially there are not many deletes/updates that will make storage perforated.

Performance degrades after few months. On the average first time about 6 months and later maybe between 3 months and 6 months. All this slowness depends upon the count of deletes/updates. Many databases (DBMS’s) have provisioned auto tunning type of facilities, still, DBAs usually struggle all the times. Few things that help it are UPDATE Statistics and REORG indexes.

?DB2, like others, is no exception. Bigger the volume of transactions, shorter is performance degradation period. As the saying goes “where there is a will, there is a way”, DBAs tend to find ways around it, or even sometimes shortcuts around it.

For such situation in one big data warehouse (62TB -> 92TB size, InfoSphere V10.5, AIX 6.1) following technique had been very successful. Not an attention to derail the DBAs from official way to analyze the performance with help of explain tables (db2expln), this course of action will definitely rule-out the possibility of subtle degradation of the query performance.

Steps:

1.??????Get list of top sql cost queries:

• a.??????Get SYSIBMADM.LONG_RUNNING_SQL list sorted desc on ELAPSED_TIME_MIN.
• b.??????Evaluate top 5 with index advisor tool (db2avis)
• c.??????Index advisor would suggest new index with projected improvement
• d.??????Filter the list with all index definitions that would reflect more than 35% improvement.

2.??????Hand over to App-DBA for creation of indexes:

• a.??????App-DBA should create one index at a time
• b.??????App-DBA should consult with DBA for results, DBA would repeat evaluations on this query again
• c.??????Repeat till the projected improvement falls below 35%.

3.??????Flush package cache at appropriate time:

• a.??????Flush package as the current session completes.

Schedule:

Make a cron-job that checks for slow running queries every hour. ?Select the time among appropriate periods where it can cover all queries that run in the database. You may schedule this distributed by odd-even dates.