Tech Tip — Explain_From_Section is Your Friend

When troubleshooting SQL performance problems, the Db2 provided procedure, Explain_From_Section is your friend. Simply obtain the executable ID from the MONREPORT.PKGCACHE report and pass it to the Explain_From_Section procedure. Then, use db2exfmt to format the explain tables and the explain will be generated for further review. Of course you could feed the SQL as input to the Design Advisor to see if lack of indexes are the problem, else possible rewrite required. Note that if the Explain_From_Section procedure fails to find the SQL associated with the executable ID because it is no longer in the package cache, you can use the MON_GET_ACTIVITY table function in conjunction with the MON_GET_PKG_CACHE_STMT and MON_GET_SECTION table functions for detailed information.