Collecting and filtering SQL data for reporting

This section describes the options for collecting data in BMC AMI Apptune for Db2 and SQL Performance for Db2 and helps you use filter and summarization options to produce data that you want to examine in reports.

In BMC AMI Apptune for Db2 and SQL Performance for Db2, data collection, summarization, and filtering are specific to each Db2 subsystem.

Each Db2 subsystem that the product can monitor is associated with a filter option set in the DOMPLEX. Each filter’s collection options are defined once you can associate them with many Db2 subsystems.

The product associates each monitored Db2 subsystem with a filter option set in the DOMPLEX. Each filter’s collection options are defined once you can associate them with many Db2 subsystems.

You can use filter option sets to define filters that apply to specific combinations of programs, plans, users, correlation IDs, and Db2 subsystems. For each filter row in a filter option set, you can define a set of options that control how the the product collects and summarizes data and records exceptions. The following table describes these options:

Collection options

Options	Description
Collection options	You can choose the types of data that each Db2 subsystem collects. You can choose to collect any or all of the following types of data: Buffer pool Lock Number of SQL statements Object SQL text You can also choose not to collect any data.
Collection keys	You can choose to summarize data by the following values: Client application Client correlation token Client user ID Client workstation Collection ID Connection ID Connection type Consistency token Correlation ID Dynamic SQL detail Implicit qualifier Plan name Program name Requesting location Section number Statement number User ID
Host variables	You can choose whether to report host variable values for exceptions and SQL errors.
Resource-saving options	You can use the following techniques to reduce overhead: Ignore literal values for numbers and strings in dynamic SQL. Warning Important If you use this technique, all dynamic SQL statements that are the same (apart from dynamic SQL text literals) are treated as one SQL statement. Sample data instead of collecting all data, and extrapolate the results.
Thresholds for exceptions and efficient statements	For the following values, you can specify thresholds that trigger generation of exception records : CPU time Deadlocks Elapsed time Exception WTOs Number of getpages Number of synchronous I/Os Timeouts Thread cancels (SPE2010 ) You can specify efficiency filtering for static SQL that identify, count, and extrapolate: Number of times per interval By CPU By getpages By synchronous I/O
SQL errors	You can choose whether to do the following: Click here if you have not applied PTF BQU0737. Generate exception records and messages. Issue WTO codes. Capture details for specific negative SQL codes. Click here if you applied PTF BQU0737. Include positive SQL codes as errors. Report details for SQL errors. Issue WTO codes for SQL errors. Generate exception reports for SQL errors.

This section contains the following topics: