Knowledge Article
BMC AMI SQL Explorer for Db2 - Improvements to BMC Db2 Common Explain
SPE2010 introduced an enhancement to BMC AMI SQL Explorer for Db2 that manages the amount of data returned to the user during the explain process. This short article will highlight the impact of the improvement when doing an on-line explain.
BMC AMI SQL Explorer for Db2
Common Explain component
BMC AMI SQL Explorer for Db2, BMC AMI Apptune, BMC AMI Catalog Manager, BMC AMI OPS for Db2
When doing an explain, part of the data returned for detailed analysis is object information. With the growing use of column distribution statistics, the amount of data returned from an explain can be extremely voluminous when the SQL references many Db2 tables and indexes. With BQU3147 and BQU3148 (SPE2010) applied, the behavior of the common explain is changed. A new parameter, DISSTAT is introduced to control the display of column distribution statistics. The user cam specify one of the following values: NONE - (Default) No Distribution Statistics CDIST - Column Distribution Statistics KTDIST - Key Target Distribution Statistics BOTH - Display both Column Distribution statistics and Key Target Distribution Statistics. While this parameter is only applicable to BATCH explains, it does affect the online version of the common explain as well in that the default for column distribution statistics is set to none and no column distribution statistics are initially displayed. To display the column distribution statistics during an online explain session the user can use the action code of K or S next to the index name to display the data. The advantage of this approach is that on large explains (SQL text with many tables and indexes), the user will avoid getting error messages like; Should this error message be encountered during an online explain session, another option is to increase the amount of memory for the user's TSO address space at logon time. The current zOS limit for TSO address space memory is 2 megabytes. |
Attachment(s):