Default language.

Space announcement The Using section of the MainView for DB2 documentation is now available in Japanese. The displayed language is dependent on your browser language. You can switch languages from the Language menu.

Finding problems with dynamic SQL

The BTHDASUM, BTHDADTL, THSQLDYN and THSQLDYS reports can help you find dynamic SQL that is causing a problem.

The reports run against historical data and are most effective when used together. BTHDASUM provides summary thread information, which can help you determine when a problem occurred. You can then run BTHDADTL for that time period to get detailed thread information. Similarly, THSQLDYN provides summary dynamic SQL and miniplan information, and then THSQLDYS provides more detailed information.

Running the detail reports (BTHDADTL and THSQLDYS) against a long time period of data can result in excessively long and cumbersome reports. To avoid this situation, be sure to run the summary reports first to determine how to best qualify the detail reports. For example, you might run the reports in the following sequence.

  • Run the BTHDASUM report to determine the interval in which a problem occurred.
  • Run the BTHDADTL detail report against the problem interval to determine which thread might be causing the problem.
  • Run the THSQLDYN report to obtain SQL/miniplan summary information.
  • Run the THSQLDYS report to identify the SQL and to expand the report to display the SQL text.

THSQLDYN can also be run online from the EZDEVENT view Dynamic Sql/Mini Plan option. From the online report, you can zoom on the SQL to explain it in even greater detail.

Note

Before running the THSQLDYN and THSQLDYS batch reports, a DB2 trace of IFCIDs 22 and 63 must be activated as described in Activating-optional-IFCIDs-for-batch-reporting.

When THSQLDYN is run online from the EZDEVENT view, IFCIDs 22 and 63 are started automatically.

For samples of these reports, see Sample-Data-Collector-reports.

Related topic

Data-Collector-reporting-facilities



 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*