IFCID descriptions

The IFCIDs highlighted in green are recommended and default as indicated previously under IFCID Defaults under SMF-Db2-statement.

IFCID	Description	Overhead and Performance Impact	How to Start Automatically
2 – Database Statistics	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	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	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	Records the END OF DESCRIBE SQL commit, SQL rollback, or an error condition occurred before SQL statement analyzed.	Caution – might have a significant adverse effect on DB2 performance.	Not available.
58 -- Completion of every SQL Operation	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. Such as, to get the completion of SQL FETCH, you must start IFCIDs 58 and 59.	Caution – might have a significant adverse effect on DB2 performance.	Not available.
59 -- Start of SQL FETCH	Audits the start of every SQL FETCH.	Caution – might have a significant adverse effect on DB2 performance.	Not available.
60 -- Start of SQL SELECT	Audits the start of every SQL SELECT.	Caution – might have a significant adverse effect on DB2 performance.	Not available.
61 -- Start of SQL INSERT, UPDATE and DELETE	Audits the start of every SQL INSERT, UPDATE or DELETE.	Caution – might have a significant adverse effect on DB2 performance.	Not available.
62 – Execution of DDL	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)	Audits the SQL text for all SQL, not just for audited tables.⁵⁰	Caution – might have a somewhat adverse effect on DB2 performance.	Not available.
64 -- Start of SQL Prepare	Audits the start of every SQL Prepare.	Caution – might have a significant adverse effect on DB2 performance.	Not available.
65 -- Start of SQL Cursor Open	Audits the start of every SQL Cursor Open.	Caution – might have a significant adverse effect on DB2 performance.	Not available.
66 -- Start of SQL Cursor Close	Audits the start of every SQL Cursor Close.	Caution – might have a significant adverse effect on DB2 performance.	Not available.
83 – Identify Exit	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	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	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	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	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	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.
107 – Open or Close of Any Table	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.	Caution – might have a very adverse effect on DB2 performance.	Not available.
140 – Invalid Logical Access Attempts	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	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	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	IFCID 143 audits the first write of an audited table within a Unit of Recovery. See Specifying AUDIT for Certain Tables above.	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	IFCID 144 audits the first read of an audited table within a Unit of Recovery. See Specifying-AUDIT-for-certain-tables above.	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)	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	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.
233 -- Start and end of call to user routine	Records the start and end of every call to a Stored Procedure or User Defined Function	Caution – might have a somewhat adverse effect on DB2 performance.	Not available.
239 – Plan Usage by Collection and Program Name	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	Audits host input (program to DB2) variables for static SQL.	Caution – might have a very adverse effect on DB2 performance.	Not available.
258 – Monitoring extend and space growth	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.	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)	Audits the SQL text for all SQL, not just audited tables.⁵⁰	Caution – might have a somewhat adverse effect on DB2 performance.	Not available.
361 – Audits Administrative Authorities	Provides real-time auditing of all actions using administrative authority (such as DBADM) The PCI DSS standard (10.2) requires automated audit trails for all actions taken by any individual with root or administrative privileges. Only valid for DB2 V10 and earlier.	A minimally intrusive trace type as it is only activated for administrative actions, not routine database accesses.	Specify class 11 in the AUDITST field of DSNTIPN. This can start only the single IFCID trace.

^{{{id name="IFCIDdescriptions-anchor50"/}}50 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.}

IFCID descriptions

On this page