Input for Record Trace reports

Record Trace reports use IFCIDs from several Db2 trace types and trace classes.

Input to the Record Trace report set consists of all types of DB2 instrumentation data. The Db2 trace types and classes used by Record Trace reports are in the following list:
  • Db2 trace type - Statistics
    Table 1. Db2 trace type - Statistics: IFCIDs used for Record Trace reports
    Db2 trace class DB2 IFCIDs used Class description
    1 (Default Class) 1, 2, 105, 106, 202, 225 Information about system services, database statistics, statistics for the DBM1 address space, and information about the system parameters that were in effect when the trace was started. This default class is also activated when you omit the CLASS keyword from the START TRACE command when you start the statistics trace.
    2 152 Installation-defined statistics record.
    3 172, 196, 250, 258, 261, 262, 313, 330, 337 Deadlock, lock escalation, group buffer pool, data set extension information, and indications of long-running uncommitted reads, and active log space shortages.
    4 173, 191, 192, 193, 194, 195, 203, 204, 205, 206, 207, 208, 209, 210, 235, 236, 238, 267, 268 DB2 exceptional conditions.
    5 230 DB2 data sharing statistics record.
    6 225 Storage statistics for the DB2 subsystem.
    7 365 DRDA location statistics.
    8 199 Data set I/O statistics.
    9 369 Aggregate CPU and wait time statistics by connection type.
  • Db2 trace type - Accounting
    Table 2. Db2 trace type - Accounting: IFCIDs used for Record Trace reports
    Db2 trace class DB2 IFCIDs used Class description
    1 (Default Class) 3, 106, 239 Standard accounting data. This default class is also activated when you omit the CLASS keyword from the START TRACE command when you start the accounting trace.
    2 200, 232 Entry or exit from DB2 event signalling.
    3 6, 7, 8, 9, 32, 33, 44, 45, 117, 118, 127, 128, 170, 171, 174, 175, 213, 214, 215, 216, 226, 227, 242, 243, 321, 322, 329, 378, 379, 382, 383 Elapsed wait time in DB2.
    4 151 Installation-defined accounting record.
    5 187 Time spent processing IFI requests.
    7 200, 232, 240 Entry or exit from DB2 event signalling for package and DBRM accounting.
    8 6, 7, 8, 9, 32, 33, 44, 45, 117, 118, 127, 128, 170, 171, 174, 175, 213, 214, 215, 216, 226, 227, 239, 241 242, 243, 321, 322, 378, 379, 382, 383 Wait time for a package.
    10 239 Package detail.
  • Db2 trace type - Audit
    Table 3. Db2 trace type - Audit: IFCIDs used for Record Trace reports
    Db2 trace class DB2 IFCIDs used Class description
    1 (Default Class) 140 Access attempts denied due to inadequate authorization. This default class is also activated when you omit the CLASS keyword from the START TRACE command when you start the audit trace.
    2 141 Explicit GRANT and REVOKE.
    3 142 CREATE, ALTER, and DROP operations against audited tables.
    4 143 First change of audited object.
    5 144 First read of audited object.
    6 145 Bind time information about SQL statements that involve audited objects.
    7 55, 83, 87, 169, 319 Assignment or change of authorization ID.
    8 23, 24, 25, 219, 220 Utilities.
    9 146 Installation-defined audit record.
    10 269, 270 Trusted context information.
    11 361 Audit administrative authorities.
  • Db2 trace type - Monitor
    Table 4. Db2 trace type - Monitor: IFCIDs used for Record Trace reports
    Db2 trace class DB2 IFCIDs used Class description
    1 (Default Class) 200 Standard accounting data. This default class is also activated when you omit the CLASS keyword from the START TRACE command when you start the monitor trace.
    2 232 Entry or exit from DB2 event signalling. The information can be obtained by issuing a READS request for IFCID 147 or 148. In addition, monitor trace class 2 information is available in IFCID 3 in the accounting record. Monitor class 2 is equivalent to accounting class 2 and results in equivalent overhead. Monitor class 2 times appear in IFCIDs 147, 148, and 3 if either monitor trace class 2 or accounting class 2 is active.
    3 6, 7, 8, 9, 32, 33, 44, 45, 117, 118, 127, 128, 170, 171, 174, 175, 213, 214, 215, 216, 226, 227, 242, 243, 321, 322, 378, 379, 382, 383 DB2 wait time for I/O, locks; resource usage information. The information can be obtained by issuing a READS request for IFCID 147 or 148. In addition, monitor trace class 3 information is available in the accounting record, IFCID 3. As with monitor class 2, monitor class 3 overhead is equivalent to accounting class 3 overhead. When monitor trace class 3 is active, DB2 can calculate the duration of a class 3 event, such as when an agent is suspended due to an unavailable lock. Monitor class 3 times appear in IFCIDs 147, 148, and 3, if either monitor class 3 or accounting class 3 is active.
    4 155 Installation-defined monitor record.
    5 187 Time spent processing IFI requests.
    6 185 Changes to tables created with DATA CAPTURE CHANGES.
    7 200, 232, 240 Entry or exit from DB2 event signalling for package accounting. The data traces the amount of time an agent spent in DB2 to process each package. If monitor trace class 2 is active, activating class 7 has minimal performance impact. Class 7 enables the IFCID 239 to be externalized.
    8 6, 7, 8, 9, 32, 33, 44, 45, 51, 52, 56, 57, 117, 118, 127, 128, 170, 171, 174, 175, 213, 214, 215, 216, 226, 227, 239, 241, 242, 243, 321, 322, 378, 379, 382, 383 Wait time for a package. If monitor trace class 3 is active, activating class 8 has minimal performance impact. Class 8 enables the IFCID 239 to be externalized.
    9 124 Enables statement level accounting. Provides information about statement details in IFCID 148. Monitor records do not contain class 10, but it shows up in IFCID 3 in the accounting record.
    10 239 Package detail for buffer manager, lock manager and SQL statistics. It contains the same information as accounting class 10. Monitor records do not include class 10, but it shows up in IFCID 3 in the accounting record. Information from class 10 is written in additional sections of IFCID 239. However, monitor class 7 or 8 must be activated for IFCID 239 to be written. One of the following trace must also be activated before the IFCID 239 records are written:
    • Accounting class 7
    • Accounting class 8
    • Monitoring class 7
    • Monitoring class 8
    29 316, 318, 400, 401 Controls the subsystem-wide collection of statistics for SQL statements. Monitor class 29 must be activated for IFCID 316 records to be written for dynamic SQL statements and IFCID 401 records to be written for static sql statements.
  • Db2 trace type - Performance
    Table 5. Db2 trace type - Performance: IFCIDs used for Record Trace reports
    Db2 trace class DB2 IFCIDs used Class description
    1 (Default Class) 1, 2, 31, 42, 43, 76, 77, 78, 79, 102, 103, 105, 106, 107, 153 Background events. This default class is also activated when you omit the CLASS keyword from the START TRACE command when you start the performance trace.
    2 (Default Class) 3, 68, 69, 70, 71, 72, 73, 74, 75, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 106, 174, 175 Subsystem events. This default class is also activated when you omit the CLASS keyword from the START TRACE command when you start the performance trace.
    3 (Default Class) 22, 53, 55, 58, 59, 60, 61, 62, 63, 64, 65, 66, 92, 95, 96, 97, 106, 112, 173, 177, 233, 237, 250, 272, 273, 325 SQL events. This default class is also activated when you omit the CLASS keyword from the START TRACE command when you start the performance trace.
    4 6, 7, 8, 9, 10, 29, 30, 105, 106, 107, 127, 128, 226, 227, 321, 322 Reads to and writes from the buffer and EDM pools.
    5 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 104, 106, 114, 115, 116, 117, 118, 119, 120, 228, 229 Write to log; archive log.
    6 20, 44, 45, 105, 106, 107, 172, 196, 213, 214, 218, 337 Summary lock information.
    7 21, 105, 106, 107, 223 Detailed lock information.
    8 13, 14, 15, 16, 17, 18, 105, 106, 107, 125, 221, 222, 231, 305, 311, 363 Data scanning detail.
    9 26, 27, 28, 95, 96, 106 Sort detail.
    10 23, 24, 25, 90, 91, 105, 106, 107, 108, 109, 110, 111, 201, 256 BIND, commands, and utilities detail.
    11 46, 47, 48, 49, 50, 51, 52, 56, 57, 93, 94, 106, 113 Execution unit switch and latch contentions.
    12 98, 99, 100, 101, 106 Storage manager.
    13 11, 12, 19, 105, 106, 107 Edit and validation exits.
    14 67, 106, 121, 122 Entry from and exit to an application.
    15 154 Installation-defined performance record.
    16 157, 158, 159, 160, 161, 162, 163, 167, 183 Distributed processing.
    17 211, 212, 213, 214, 215, 216 Claim and drain information.
    18 197 Event-based console messages.
    19 370, 371 Data set open and close activity.
    20 249, 250, 251, 256, 257, 261, 262, 267, 268 Data sharing coherency summary.
    21 255, 259, 263 Data sharing coherency detail.
    22 314 Authorization exit parameters.
    23 327 Language environment runtime diagnostics.
    24 380, 499 Stored procedure detail.
    29 - Reserved! (not used anymore)
  • Db2 trace type - Global
    Table 6. Db2 trace type - Global: IFCIDs used for Record Trace reports
    Db2 trace class DB2 IFCIDs used
    1 (Default Class) 106, (132, 134, 138)
    2 106, (131, 133, 139)
    3 0, 38, 46, 47, 48, 49, 50, 51, 52, 56, 57, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 93, 94, 106, 114, 115, 116, 117, 174, 175, 228, 229, 252, 260, 265, 266, 267, 268
    4 106, (130)
    5 190, 249 (135, 136, 137,) (247, 248)
    6 156
    7 164, 165, 166
    8 168
    9 180, 181, 182