Configuration Best Practices › DB2 Configuration Best Practices › Setting the Granularity of SQL Statements

Previous Topic: Extracting SQL Statement Text from the DB2 Catalog

Next Topic: Defining the Size of the Internal Table

Setting the Granularity of SQL Statements

We recommend setting the values for DB2HVLOC, DB2HVIID, and DB2HVTHD to NO unless detail information is needed (for example, for measuring DDF address spaces). The parameters control the granularity of SQL statement information that is detected by the DB2 Harvester. If the parameters are set to YES, the location, operator, and thread IDs are collected and multiple entries for the same SQL statement with different values for these metrics are created. This configuration can result in a high number of different entries in the CA Mainframe Application Tuner DB2 view. If you set the value of these parameters to NO, no detail is provided.

Business Value:

This best practice enables you to see the source of your statements in one place, which saves you the time it would otherwise take to locate the source information.

Displaying Information about SQL Statements by Transaction

We recommend setting DB2HVCOR to YES to provide detailed information about the use of the SQL statement by transaction. In this context, the correlation ID is the transaction name.

Business Value:

This configuration provides additional granularity and is useful for measuring online regions. Implementing this feature correlates the execution of SQL statement metrics to transactions. Having additional granularity in your reports enables you to perform more detailed analysis on your SQL statements.