Is Suboptimal SQL Impacting your Database?

After DBM and DB CFG settings have been set properly, whether through the use of automatic settings and STMM or from setting them manually, suboptimal SQL is likely to be your number one performance problem. Db2 contains a plethora of internal tools and facilities to assist you with identifying and tuning suboptimal SQL. Prior to the introduction of the MONREPORT reporting tool, I used a custom script to capture and rank the top 10 SQL in terms of CPU usage, rows read, number of executions, etc. Now I use the MONREPORT CURRENTSQL and PKGCACHE reports to capture suboptimal SQL. I then use either db2exfmt, db2expln or the EXPLAIN_FROM_SECTION stored procedure to review suboptimal SQL. In fact, the EXPLAIN FROM SECTION SP is often my first choice. I then feed the output to db2exfmt to explain the SQL. As a matter of routine, I then use DB2 Design Advisor to see if there are any index recommendations which would provide a significant improvement. I then review existing indexes on tables involved to determine if there are already too many indexes on tables involved, review index recommendations for redundancy and then consider application usage and possible SQL rewrite before applying any recommended indexes. I hope this discussion helps you to develop your own procedures for capturing, identifying and tuning suboptimal SQL.