IFCID changes in Db2 12

Db2 12 introduces changes to various instrumentation facility component identifier (IFCID) trace fields.

Important: Db2 12 introduces continuous delivery of new capabilities and enhancements in function levels. Most new capabilities become available only after activation of the Db2 12 function level that introduces them, or when applications run with the corresponding application compatibility level. For more information, see Activating Db2 12 function levels.
Tip: Start of changeIFCID numbers are often presented in Db2 and its documentation with three or four digits and leading zeros. For example, you might find: "IFCID 1," "IFCID 001," or "IFCID 0001." However, you can assume that these references each have the same meaning.End of change

New trace records

Begin program-specific programming interface information.

The following table gives an overview of new IFCIDs. Serviceability trace records are not included.

Table 1. New IFCIDs
IFCID Trace type and class
Mapping macro
 Description
382
Accounting class 3
Accounting class 8
Monitor class 3
Monitor class 8
Accounting, Monitor		 DSNDQW05 Records the beginning of a suspend for parallel task synchronization
383
Accounting class 3
Accounting class 8
Monitor class 3
Monitor class 8
 DSNDQW05 Records the end of a suspend for parallel task synchronization
393 Performance class 3 DSNDQW05 Records information about phased-out package copies
389 Statistics class 8 DSNDQW05 Records information about indexes that have structures allocated for fast index traversal
411 Statistics class 10 DSNDQW05 Records detailed statistics about remote applications that connect to a local Db2 subsystem, based on the CURRENT CLIENT_APPLNAME special register
412 Statistics class 11 DSNDQW05 Records statistics about the client user IDs that are associated with remote connections to a local Db2 subsystem, based on the CURRENT CLIENT_USERID special register
413
Accounting class 3
Accounting class 8
Monitor class 3
Monitor class 8
 DSNDQW05 Records the beginning of a wait for a pipe for an INSERT statement that is using insert algorithm 2
414
Accounting class 3
Accounting class 8
Monitor class 3
Monitor class 8
 DSNDQW05 Records the end of a wait for a pipe for an INSERT statement that is using insert algorithm 2
477 Performance class 4 DSNDQW05 Records allocation and deallocation of structures for fast index traversal
End program-specific programming interface information.

Changes to selected trace records

Begin program-specific programming interface information.

The following table gives an overview of changes to specific IFCIDs. Changes to IFCID 0106, the system parameters record, and changes to serviceability trace records are not included.

Table 2. Changed IFCIDs
Enhancement IFCID Description of changes
Adaptive index processing 2, 125 Fields are added to record:
  • RID list processing statistics for adaptive index processing
  • Details about adaptive the index processing that was performed
Autonomous procedures 3 Fields are added to record accounting information for autonomous procedures.
Start of changeDb2 exploitation of asynchronous cross-invalidation for coupling facility cache structures (APAR PH05193)End of change Start of change2End of change Start of changeFields are added to record:
  • The number of IXLCACHE (cache services) requests with asynchronous cross-invalidation
  • The number of IXLAXISN (coupling facility asynchronous cross-invalidation service) calls
  • The number of suspensions of the IXLAXISN service that occurred during waits for asynchronous cross-invalidation operations to complete.
End of change
Start of change End of change Start of change3End of change Start of changeA field is added to record the number of suspensions of the IXLAXISN service that occurred during waits for cross-invalidation operations to complete.End of change
Dynamic SQL plan stability 2, 21, 29, 30, 316 Fields are added to record:
  • The number of requests and satisfied requests for stabilized dynamic SQL statements
  • Locking for dynamic plan stability operations
  • Start and end of an EDM request for the SYSIBM.SYSDYNQRY table
  • Information on stabilized dynamic SQL statements in the dynamic statement cache
Start of changeFast index traversalEnd of change Start of change2End of change Start of changeFields are added to record information about when fast index traversal is used.End of change
Improved detection and removal of incomplete connections (APAR PH44270) 1 Field QLSTCNVT is updated to include the number of connections to a server that were terminated as incomplete.
Information on conditional lock failures on a partition (APAR PH29098) 2 Fields are added to record:
  • The number of conditional lock failures on a partition during insert operations
  • The number of unconditional lock retries that occurred after a conditional lock request on a partition failed

These counters can be used to diagnose issues like these in applications with insert operations:

  • Insert operations encounter a partition full message (reason code 00C9009C), but the table space is not full.
  • Insert operations fail with a lock timeout, but the table space is not full.
  • Insert operations have slow performance.
Integrated Accelerator for Z Sort use for GROUP BY or ORDER BY (APAR PH31684) 2, 96 Fields are added to record information about the use of the Integrated Accelerator for Z Sort coprocessor by Db2 sort operations for GROUP BY or ORDER BY.
Insert algorithm 2 2, 3, 18, 58, 239, 316, 401 Fields are added to record:
  • Wait times for pipes for insert algorithm 2 INSERT statements
  • Information about the use of pipes for insert algorithm 2 INSERT statements
Enhanced monitoring support for insert algorithm 2 (APAR PI81731) 2, 3, 148 Fields are added to record:
  • The number times that insert algorithm 2 was used.
  • The number of times that the basic insert algorithm (insert algorithm 1) is used.
  • The number attempts and successful attempts to re-enable pipes for insert algorithm 2 since Db2 restart
  • Whether query parallelism was disabled by the resource limit facility for at least one dynamic SELECT statement in a thread.
Start of changeIBM zHyperLink exploitation for database synchronous read I/O (APAR PI87072) and active log write I/O (APAR PH05030)End of change Start of change1End of change Start of changeFields are added to record:
  • The number of eligible active log write I/O requests with successful IBM zHyperLink processing.
  • The number of eligible active log write I/O requests with unsuccessful IBM zHyperLink processing.
End of change
  2 Fields are added to record:
  • The number of successful synchronous read I/O operations that were performed using IBM zHyperLink processing.
  • The number of synchronous read I/O operations that resulted in disk cache hits, and did not use IBM zHyperLink processing.
  3, 148 Fields are added to record:
  • Wait time for synchronous read I/O operations that resulted in disk cache hits
  • The number of synchronous read I/O operations that resulted in disk cache hits.
  3, 148, 239 Fields are added to record:
  • The number of synchronous read I/O operations that used IBM zHyperlink processing.
  • The number of synchronous read I/O operations that resulted in disk cache hits.
  • The amount of CPU time for successful synchronous read I/O operations that used IBM zHyperlink processing.
  6 A flag is added to indicate whether a synchronous read I/O request occurred with or without IBM zHyperLink processing.
  7 A flag is added to indicate whether a synchronous read I/O request with IBM zHyperLink processing was successful.
  199 Fields are added to record the average and maximum delay and total number of pages read with synchronous read I/O and IBM zHyperLink processing.
Improved IFCID 0402 reset behavior (PH50729) 402 When the RESET_IFCID402 option is specified for the DDF_COMPATIBILITY subsystem parameter, the values of the following fields are reset to zero when a new profile is enabled, or an existing profile's warning or exception threshold is changed:
  • QW0402TE
  • QW0402TQ
  • QW0402TF
  • QW0402TW
  • QW0402CE
  • QW0402CW
  • QW0402OE
  • QW0402OW
Lift partition limits 6, 7, 21, 44, 124, 127, 0128, 150, 172, 196, 198, 223, 226, 227, 255, 0259, 305 Fields are added to:
  • Indicate whether pages in range-partitioned table spaces use absolute or relative page numbering
  • Hold relative page numbers
Location statistics enhancements (APAR PH16111) 1, 365 Fields are added to record additional statistics that describe the behavior of connections between distributed locations and a local Db2 server. See PH16111 for details.
Processing improvements for data sets that are used by utilities (PH27493) 2 Fields are added to record:
  • The number of infrequently accessed data sets that were physically closed
  • The number of utility-access-only data sets that were physically closed
Real-time monitoring of CICS attachment facility calls (PH31447) 3 Fields are added to record CICS origin adapter data.
Start of changeRebind phase-in for packages that are being used for execution (APAR PH05989)End of change Start of change239End of change Start of changeA field is added to record the ID for the current package copy.End of change
Serviceability improvements to provide more information for insert operations that fail with reason codes 00C90090 or 00C9009C (PH29098) 2 Fields are added to record:
  • The number of failed conditional partition lock requests during insert operations
  • The number of times that a conditional partition lock request failed, and the lock request was retried with an unconditional lock request
Start of changeStatement-level monitoring for accelerated Hybrid Transactional and Analytical Processing (HTAP) support (APAR PH00574, PH15534)End of change Start of change3, 316, 401End of change Start of changeFields are added to record:
  • The amount of time in milliseconds that statements waited for execution on an accelerator because the statements needed changes that were not yet applied to a referenced table.
  • The number of times that statement execution did not complete because changes were not applied to a referenced table before the WAITFORDATA delay protocol time period expired.
End of change
Statistics and accounting enhancements for incremental updates to IBM® Db2 Analytics Accelerator that are processed using the Integrated Synchronization method (APAR PH18334 and PH26681) 2 Fields are added to record:
  • Accumulated CPU time, since Db2 startup, that was consumed on standard processors by all asynchronous log reader tasks.
  • Accumulated CPU time, since Db2 startup, that was consumed on an IBM z Integrated Information Processor (zIIP) by all asynchronous log reader tasks.
  • Accumulated CPU time, since Db2 startup, that was consumed on standard processors by all asynchronous log reader tasks, and those tasks were eligible for execution on a zIIP.
  • Accumulated CPU time, not including CPU time on a zIIP, that was consumed by all asynchronous log reader tasks that ran on behalf of an accelerator.
  • Accumulated CPU time on a zIIP that was consumed by all asynchronous log reader tasks that ran on behalf of an accelerator.
  • Accumulated CPU time that was consumed on standard processors by all asynchronous log reader tasks that ran on behalf of an accelerator, and those tasks were eligible for execution on a zIIP.
  3 A new product identifier, LRT, is added to identify threads that connect to Db2 to retrieve log records that were collected by asynchronous log reader tasks.
Statistics class 1 includes IFCID 0369 (APAR PH43916) 369 When -START TRACE (STAT) is issued without the CLASS parameter, or with the CLASS(1) parameter, IFCID 0369 is activated by default. For compatibility with previous Db2 releases, statistics class 9, which contains IFCID 0369, is still available.
Start of changeSystem profile monitoring profile search improvements (APAR PH12041)End of change Start of change402End of change Start of changeFields are added to record:
  • The current number of active threads
  • The current number of suspended threads
  • The highest number of threads since DDF start
  • The current number of connections
  • The highest number of connections since DDF start
End of change
Transfer ownership 2, 62, 140, 361 Fields are added to record use of the TRANSFER OWNERSHIP statement.
Miscellaneous changes 2 Fields are added to record:
  • Details about buffer pool overflow
  • The number of times that RID list processing was not used.

Fields that are related to the EDM statement pool are deleted.
  2 Changes to the maximum number of repeated sections in certain IFCID 2 records (PH55153):

The following changes are made:

  • More than one IFCID 2 record can be generated for accelerator services statistics. The maximum number of QWS10RBO sections (accelerator services system statistics sections) in the initial IFCID 2 record is two. If additional Q8ST data exists, it is written in an additional IFCID 2 record that contains only data section 11 Q8ST data.
  • The maximum number of QWS10R7O sections (group buffer pool statistics sections) in the initial IFCID 2 record is decreased from 25 to 15.
  2, 3 Fields are added to record:
  • Additional IRLM system information
  53 A field is added to record the section number of the SQL statement.
  58 A field is added to record the section number of the SQL statement.
  199 Fields are added to record I/O for read and castout, in microseconds.
  316 Fields are added to record:
  • Accumulated wait time due to global contention for L-locks and P-locks
  • Wait times that are at the same level of granularity as the wait times in class 3 accounting data
  401 Fields are added to record accumulated wait time due to global contention for L-locks and P-locks.
  All A field is added to the correlation header with the batch job step name, to correlate an accounting record with a given job step.

Deleted trace records

IFCID 0366 is no longer supported.

An additional location for the IFCID flat file (DSNWMSGS)

You can now find DSNWMSGS in either of the following locations:
  • The most current version of DSNWMSGS is available only for clients who have Db2 12 for z/OS® licenses. The information is in a PDF file. To locate this information, see Db2 12 for z/OS IFCID flat file (DSNWMSGS).
  • An older version of DSNWMSGS is available in the prefix.SDSNIVPD(DSNWMSGS) data set. You can use the TSO or ISPF browse function to look at the field descriptions in prefix.SDSNIVPD(DSNWMSGS), even when Db2 is down. If you prefer to look at the descriptions in printed form, you can use ISPF to print a listing of the data set.
End program-specific programming interface information.