IFCID descriptions
IFCID | (SPE2307)SMF Type | Description | Overhead and Performance Impact | How to Start Automatically |
|---|---|---|---|---|
1 – System Statistics | 100 | Displays accumulated totals for a Db2 subsystem | The overhead of IFCID 1 is dependent on | Specify STATISTICS TIME on panel DSNTIPN. |
2 – Database Statistics | 100 | Displays accumulated totals for a Db2 subsystem | A default trace with minimal impact on Db2 performance. | Specify class 1 in the SMFSTAT field of DSNTIPN. This can have the effect of starting traces for IFCIDs 1, 2, 105, 106, 202 and 225. |
3 – Accounting Counters | 101 | Displays accounting counters by Db2 subsystem | A default trace with minimal impact on Db2 performance. | Specify class 1 in the SMFACCT field of DSNTIPN. This can have the effect of starting traces for IFCIDs 3, 106, 200, and 239. |
23, 24 and 25 -- Utility Object Change and Utility Completion | 102 | Records the execution of Db2 utilities. Utility execution is important to audit because backup failures might be relevant to Sarbanes-Oxley compliance, and because in some cases utility access to Db2 tables is not recorded by other traces. | Low overhead because utilities are only run for tasks such as loading or copying databases, and only a small number of records are written for each utility job. | Specify class 8 in the AUDITST field of DSNTIPN. This can have the effect of starting traces for IFCIDs 23, 24, 25, 219 and 220. |
53 - End Describe | 102 | Records the END OF DESCRIBE SQL commit, SQL rollback, or an error condition occurred before SQL statement analyzed. | Warning: Might have a significant adverse effect on Db2 performance. | Not available. |
58 -- Completion of every SQL Operation | 102 | Audits the completion of every SQL operation. Error and warning conditions are indicated. The IFCID message might be correlated to other message numbers by means of the Token, Stmt#, and StmtID fields. You must also start the corresponding trace from the range 59 through 66. For example, to get the completion of SQL FETCH, you must start IFCIDs 58 and 59. | Warning: Might have a significant adverse effect on Db2 performance. | Not available. |
59 -- Start of SQL FETCH | 102 | Audits the start of every SQL FETCH. | Warning: Might have a significant adverse effect on Db2 performance. | Not available. |
60 -- Start of SQL SELECT | 102 | Audits the start of every SQL SELECT. | Warning: Might have a significant adverse effect on Db2 performance. | Not available. |
61 -- Start of SQL INSERT, UPDATE and DELETE | 102 | Audits the start of every SQL INSERT, UPDATE or DELETE. | Warning: Might have a significant adverse effect on Db2 performance. | Not available. |
62 – Execution of DDL | 102 | Audits the execution of DDL statements. The information provided by IFCID 62 is fairly bare-bones. | A very low overhead trace as it is invoked only for the execution of DDL. | Not available. |
63 – SQL Statement Text (Written During Bind of Static or Dynamic SQL) | 102 | Audits the SQL text for all SQL, not just for audited tables. 1 | Warning: Might have an adverse effect on Db2 performance. | Not available. |
64 -- Start of SQL Prepare | 102 | Audits the start of every SQL Prepare. | Warning: Might have a significant adverse effect on Db2 performance. | Not available. |
65 -- Start of SQL Cursor Open | 102 | Audits the start of every SQL Cursor Open. | Warning: Might have a significant adverse effect on Db2 performance. | Not available. |
66 -- Start of SQL Cursor Close | 102 | Audits the start of every SQL Cursor Close. | Warning: Might have a significant adverse effect on Db2 performance. | Not available. |
83 – Identify Exit | 102 | Records the ending of an identify request for an IMS, CICS, CAF, RRSAF, Utility, or TSO connection. | Usually a minimally intrusive trace with minimal impact on Db2 performance. | Specify class 7 in the AUDITST field of DSNTIPN. This can have the effect of starting traces for IFCIDs 55, 83, 87, 169 and 319. |
90 – DB2 Commands as Entered | 102 | Audits Db2 commands as they are entered. | A very low overhead trace as it is invoked only for the execution of commands, that typically are infrequently entered. | Not available. |
91 – DB2 Command Completions | 102 | Audits Db2 commands as they complete, with completion codes. An IFCID 91 message might be correlated back to its corresponding IFCID 90 by means of its Correlation ID. | A very low overhead trace as it is invoked only for the execution of commands, that typically are infrequently entered. | Not available. |
92 – Use of Access Method Services to Create System Level Objects | 102 | Audits Db2’s use of Access Method Services (AMS) commands to create and delete tablespaces and other system level objects. IFCID 92 is largely redundant with IFCID 97, that is recommended rather than IFCID 92. | A very low overhead trace as it is invoked only for the execution of AMS commands, that typically are infrequently executed. | Not available. |
97 – Use of Access Method Services to Create System Level Objects | 102 | Audits Db2’s use of Access Method Services (AMS) commands to create and delete tablespaces and other system level objects. Auditing the creation and deletion of system level objects is required by PCI DSS. | A very low overhead trace as it is invoked only for the execution of AMS commands, that typically are infrequently executed. | Not available. |
105 – Database ID and Object ID Mapping | 102 | Shows the correlation of database and object IDs with their corresponding names. Might or might not be useful in a particular installation. Warning: Might generate a very long syslog message, potentially longer than your SERVER MAXMSGLEN and longer than that can be accepted by most SIEM collectors. | Minimally intrusive, because it is written by Db2 only occasionally. | Specify class 1 in the SMFSTAT field of DSNTIPN. This can have the effect of starting traces for IFCIDs 1, 2, 105, 106, 202, and 225. |
106 – System Statistics | 102 | Records the system parameters in effect when a TRACE is started. | A default trace with minimal impact on Db2 performance. | This IFCID recording can be started through any of the following TRACE CLASSes:
|
107 – Open or Close of Any Table | 102 | Audits every table open and close, not just audited tables. IFCID 107 messages are also a source of correlation between DBID/PSID pairs and their corresponding database and table names. | Warning: Might have a significant adverse effect on Db2 performance. | Not available. |
140 – Invalid Logical Access Attempts | 102 | Real-time auditing of invalid logical access attempts. The PCI DSS standard requires that an organization “implement automated audit trails for … invalid logical access attempts.” | A very low overhead trace because it is only invoked for failed accesses, not every access. | Specify class 1 in the AUDITST field of DSNTIPN. This can start only the single IFCID trace. |
141 – Explicit GRANTs and REVOKEs | 102 | Audits explicit grants and revokes of Db2 object access permissions. | A low-overhead trace, as explicit GRANTs and REVOKEs are infrequent. | Specify class 2 in the AUDITST field of DSNTIPN. This can start only the single IFCID trace. |
142 – CREATEs, ALTERs and DROPS for audited or multi-level security tables | 102 | Audits CREATEs and ALTERs that specify AUDIT, and ALTERs and DROPS of tables with AUDIT previously specified. | A low-overhead trace, as CREATEs, ALTERs and DROPs are usually relatively infrequent. | Specify class 3 in the AUDITST field of DSNTIPN. This can start only the single IFCID trace. |
143 – First Write of an Audited Table within a Unit of Recovery | 102 | Audits the first write of an audited table within a Unit of Recovery. See Specifying-AUDIT-for-certain-tables | The overhead of IFCID 143 is dependent on the activity on the specific tables audited, and could be substantial. | Specify class 4 in the AUDITST field of DSNTIPN. This can start only the single trace. |
144 – First Read of an Audited Table within a Unit of Recovery | 102 | Audits the first read of an audited table within a Unit of Recovery. See Specifying-AUDIT-for-certain-tables. | The overhead of IFCID 144 is dependent on the activity on the specific tables audited, and could be substantial. | Specify class 5 in the AUDITST field of DSNTIPN. This can start only the single trace. |
145 – SQL Text for Audited Tables (Written During Bind of Static or Dynamic SQL) | 102 | Audits the text of every SQL statement for audited tables only. See Specifying-AUDIT-for-certain-tables. Also see IFCIDs 63 and 350. | Dependent on the number of dynamic SQL executions and static SQL binds for audited tables, but should not be too burdensome. | Specify class 6 in the AUDITST field of DSNTIPN. This can start only the single trace. |
197 – DB2 Console Messages | 102 | Audits the text of event-based Db2 console messages. Such as, messages issued in response to a ‑DISPLAY command are not audited | A very low-overhead trace that might help with diagnosing Db2 errors. | Not available. |
225 – System Storage Usage | 100 | Summarizes system storage usage. | A default trace with minimal impact on Db2 performance. | Specify class 1 in the SMFSTAT field of DSNTIPN. This can have the effect of starting traces for IFCIDs 1, 2, 105, 106, 202, and 225. |
233 -- Start and end of call to user routine | 102 | Records the start and end of every call to a Stored Procedure or User Defined Function | Warning: Might have an adverse effect on Db2 performance. | Not available. |
239 – Plan Usage by Collection and Program Name | 101 | Audits plan usage by collection and program name. | A default trace with minimal impact on Db2 performance. | Specify class 1 in the SMFACCT field of DSNTIPN. This can have the effect of starting traces for IFCIDs 3, 106, 200, and 239. |
247 – Input Host Variables Trace | 102 | Audits host input (program to Db2) variables for static SQL. | Warning: Might have a significant adverse effect on Db2 performance. | Not available. |
258 – Monitoring extend and space growth | 102 | Records dataset extend activities. An IFCID 258 record is written when a data set extend occurs. | Usually a minimally intrusive trace with minimal impact on Db2 performance. | Specify class 3 in SMFSTAT field of DSNTIPN. This can have the effect of starting traces for IFCIDs 172, 196, 250, 258, 261, 262, 313, 330 and 337 (some of these are fairly high overhead traces). |
319 -- Non-mainframe to mainframe identity mapping. | 102 | Audits the mapping of a distributed user to a mainframe userID. | Usually a minimally intrusive trace with minimal impact on Db2 performance. | Specify class 7 in the AUDITST field of DSNTIPN. This can have the effect of starting traces for IFCIDs 55, 83, 87, 169 and 319 |
350 – Full SQL Statement Text (Written During Bind of Static or Dynamic SQL) | 102 | Audits the SQL text for all SQL, not just audited tables. 1 | Warning: Might have an adverse effect on Db2 performance. | Not available. |
361 – Audits Administrative Authorities | 102 | Provides real-time auditing of all actions, such as select and insert, by all users unless restricted by a Db2 audit policy. Only valid for Db2 V10 and earlier. | Warning: This will have an adverse effect on CPU utilization by both the BMC AMI Datastream address space and Db2. To limit the generation of this record for users with SYSADM and DBADM authority, create a Db2 audit policy. | Specify class 11 in the AUDITST field of DSNTIPN. This can start only the single IFCID trace. |
376 – Serviceability trace | 102 | This is a serviceability trace. It can be used to identify SQL statements with potential incompatible changes when switching to the new application behavior (e.g V11R1). | Minimal impact on Db2 performance, as this trace record performs uniqueness checking, and is written once for each unique dynamic cached statement and static statement if it was bound on V10 NFM or later. For static statements that are bound before V10 NFM, this trace record will be externalized once per unique combination of plan, package ID, and statement number. | Not available. |
1. IFCID 63 and IFCID 350 audit the SQL text for all SQL requests, not just audited tables (dynamic SQL at execution time and static SQL at bind time). IFCID 63 is somewhat easier to parse than IFCID 350, but IFCID 63 messages truncate SQL statements at 5000 bytes, whereas one or more IFCID 350 messages contain the complete text of every SQL statement no matter how long (as do IFCID 145 messages) -- assuming your maximum syslog message length is great enough.