Db2 instrumentation data

Understand how Db2 instrumentation data is grouped and how these groups relate to OMEGAMON for Db2 PE report sets. With this information you can correctly specify the data to be collected with the Db2 START TRACE command.

Each Db2 event is recorded by its Instrumentation Facility Component (IFC) as trace record. These trace records have unique IFC identifiers (IFCIDs). To have these IFCIDs externalized, appropriate trace types must be started.

Most IFCIDs are grouped into trace classes. A trace class defines a certain group of events or data within a trace type. Each class consists of one or more IFCIDs. You can limit the amount of data to be collected by specifying only certain classes for a type.

The instrumentation data types used as input to OMEGAMON XE for Db2 PE reporting facility are:

  • Statistics data shows to what extend the Db2 system services and database services are used. You can use this information to plan Db2 capacity and to tune an entire set of Db2 programs. Statistics data also contains information about deadlocks, timeouts, and DDF exception events.

    The Statistics trace is written at specified intervals. You can control the Statistics interval by using STATISTICS TIME in the DSNTIPB installation panel.

  • Accounting data provides information related to application programs and packages.

    A Db2 Accounting trace record starts when a thread is allocated to Db2 and ends when the thread terminates, is reused, or becomes inactive. You can use Accounting data for program-related tuning and to assess Db2 usage for cost charging.

  • Performance data provides information for performance analysis, performance tuning, and includes records of specific events in the system.

    When you start a Performance trace, specify what you want to report, for example, I/O only or SQL only.

  • Audit data provides information about Db2 security controls. You can use the data to ensure that data access is allowed only for authorized purposes.

    Tables are the only auditable objects. To audit a table, include the AUDIT clause in the CREATE TABLE or ALTER TABLE statement.

Statistics and Accounting data are used for the continuous or periodic monitoring of Db2, whereas Performance data is usually recorded only when you need to examine specific performance problems. Audit data is collected to monitor access to data.

The following table lists trace types, trace classes, and IFCIDs relevant to reports. Use the table to see which Db2 trace data is used as input for each report set.

Table 1. Input for OMEGAMON for Db2 PE report sets
Report set Db2 trace type Db2 trace class Description of Db2 trace class Db2 IFCIDs used
Accounting Accounting 1 Accounting data 3, 239
2 In Db2 time Additional information for 3
3 Wait time in Db2 Additional information for 3
5 Time spent processing IFI requests Additional information for 3
7 Package information - in Db2 time Additional information for 3, 239
8 Package information - wait time in Db2 Additional information for 3, 239
10 Package Details 239
Audit Audit 1 Authorization failures 140
2 Explicit GRANT or REVOKE 141
3 CREATE, ALTER, and DROP operations against audited tables 105, 107, 142
4 First change of audited object 105, 107, 143
5 First read of audited object 105, 107, 144
6 SQL statement at bind 105, 107, 145
7 Change in authorization for audited object 55, 83, 87, 169, 319
8 Utility access to any object 24, 105, 107
10 Established trusted connections 269, 270
I/O Activity Performance 4 Buffer manager I/O and EDM pool requests 6, 7, 8, 9, 10, 29, 30, 107
5 Log manager 34, 35, 36, 37, 38, 39, 40, 41, 114, 115, 116, 119, 120
21 Data sharing 107, 255
Locking Statistics 3 Deadlock and timeout information 172, 196
Performance 4 Buffer manager I/O and EDM pool requests 226, 227
6 Locking information 20, 44, 45, 172, 196, 213, 214, 218
7 Detailed locking information 21, 105, 107, 223
17 Drain and claim 211, 212, 213, 214, 215, 216
20 Data sharing 251, 257
21 Data sharing 259
Record Trace All All All types, classes, and IFCIDs can be used as input All
SQL Activity Accounting 1 Accounting data 3
2 In Db2 time 3
3 Suspensions 3
5 IFI and data capture events 3
7 Package information - in Db2 time 239
8 Package information - wait time in Db2 239
SQL Activity Performance 2 Subsystem-related events 68, 69, 70, 71, 72, 73, 74, 75, 84, 85, 86, 87, 88, 89, 106, 174, 175
3 SQL-related events 22, 53, 55, 58, 59, 60, 61, 62, 63, 64, 65, 66, 92, 95, 96, 97, 177, 233, 237, 272, 273
4 Buffer manager I/O and EDM pool requests 6, 7, 8, 9, 226, 227
6 Locking information 20, 44, 45, 213, 214, 218
8 Data Manager detail 15, 16, 17, 18, 106, 125, 221, 222, 231, 305, 325
9 Sort detail 28, 95, 96
10 Autobind 105, 106, 107, 108, 109
13 Edit and validation exits 11, 12, 19
16 Distributed activity 157, 159, 160, 162, 163, 183
17 Drain and claim detail 213, 214, 215, 216
30, 31, 32 Installation-defined trace classes 188, 324
Statistics Statistics 1 Statistics data 1, 2
6 Storage manager pool summary statistics 225
8 Buffer pool data set statistics 199
9 Aggregated accounting statistics 369
System parameters Performance Any These IFCIDs are available in all trace classes 106, 201, 202, 256
Statistics 5 Data sharing global information 230
Utility Accounting 1 Accounting data 3
Performance 3 SQL-related events 22, 63, 177
4 Buffer manager I/O and EDM pool requests 6, 7, 8, 9, 226, 227
6 Locking information 20, 44, 45, 213, 214, 218
10 Bind and utilities 23, 24, 25, 108, 109, 110, 111
13 Edit and validation exits 11, 12, 19
16 Distributed activity 183
17 Drain and claim detail 213, 214, 215, 216

OMEGAMONĀ® for Db2 PE gathers input for Explain by connecting to an active Db2 subsystem.

If you want to see a count of the input trace records used in a job, you can produce an IFCID frequency distribution log.