In january 2007 the IBM DB2 PTF PK34261/UK20793 introduced DSN1SMFP, a DB2-supplied application for processing DB2 trace data into reports that are useful for evaluating and auditing the DB2 environment for Common Criteria. This is very useful if you have no or restricted access to performance reporting tools like OMEGAMON XE for DB2 PE or BMC Performance Reporter.

DSN1SMFP was then available with DB2 9 for z/OS, but disappeared with DB2 10 for z/OS. Great news: IBM apparently will ship DSN1SMFP renewed with DB2 11 for z/OS.

For convenience, in earlier versions IBM provided the sample job DSNTEJCC with a basic JCL framework needed to execute DSN1SMFP. A JCL prolog directs to the customization needed at customer site.

DSN1SMFP accepts one or more SMF data sets in standard SMF format and produces from one to sixteen reports. All SMF record types are accepted but only type 101 (DB2 Accounting) and 102

(DB2 Performance) records generated by the DB2 release level of DSN1SMFP and having one of the following DB2 IFCIDs are used:

• 0003: Accounting – DDF Data by Location (security-relevant fields only)
• 0004: Trace Start
• 0005: Trace Stop
• 0023: Utility Start
• 0024: Utility Change
• 0025: Utility End
• 0106: System Parameters (security-relevant fields only)
• 0140: Audit Authorization Failures
• 0141: Audit DDL Grant/Revoke
• 0142: Audit DDL Create/Alter/Drop
• 0143: Audit First Write
• 0144: Audit First Read
• 0145: Audit DML Statement
• 0350: SQL Statement

Each such trace type is extracted and outputted in report format to a dedicated DD for that trace type. The output DD name indicates the report source: For example, the report for each IFCID 0003 trace record is written to the IFCID003 DD, the report for each IFCID 0106 trace record is written to the IFCID106 DD, and so on.

DSN1SMFP accepts SMF records from any release of DB2 but uses only those for the release in which it ships. You cannot, for example, use V8 DSN1SMFP to report on a V9 DB2.

DSN1SMFP also counts and reports the following values in the end-of-job summary, which is written to the SYSPRINT DD:

• Total SMF records read
• Total SMF type 101 records
• Total SMF type 102 records
• Separate totals for IFCIDs 0003, 0004, 0005, 0023, 0024, 0025, 0106, 0140, 0141, 0142, 0143, 0144, 0145, 0350, and „other IFCID“ records read
• Separate totals for formatted IFCID 0003, 0004, 0005, 0023, 0024, 0025, 0106, 0140, 0141, 0142, 0143, 0144, 0145, and 0350 records written
• Total formatted records written

The SYSPRINT DD is also the destination for diagnostic and warning messages generated by DSN1SMFP.

Links:

DSN1SMFP in DB2 11 for z/OS Library (former Info APAR PK34261 has been removed from the web).

http://www-01.ibm.com/support/docview.wss?uid=swg1PM94545