Sample Report from SMF 102, IFCID 144 Records
DB2 Audit Report -- Users Accessing Selected Tables
It's easy to report on SMF data!
We have a low-cost 4GL report writer especially for SMF files. It's called Spectrum SMF Writer.
Spectrum SMF Writer handles the difficult SMF record parsing for you automatically. You just specify which fields you want to see.
Spectrum SMF Writer also converts the arcane date and time fields and reformats them into an attractive report.
Plus Spectrum SMF Writer even exports SMF data as comma delimited files to use on your PC.
The sample SMF report below was created with Spectrum SMF Writer,
the low-cost 4GL SMF report writer.
In this report, the primary input file is the SMF file. We select just the type 102 "DB2 Audit" records with IFCID 144 ("ATTEMPTED ACCESS (READ) OF AN AUDITED OBJECT"). (See SMF 102 record layout.) We normalize the 144 data section, in order to report on all occurances of that segment within each SMF record.
We select the records for the timeframe and the tables we are interested in. (Alternately, we could have selected on Operator ID, to see all tables accessed by a given user.)
This example also illustrates Spectrum SMF Writer's DB2 Option. The SMF record itself does not contain the text name of the tables being accessed. It just contains halfword codes for: Database ID, Page Space ID and Object ID. However, we were able to use those codes to "select" rows from two DB2 system tables and expand those codes into full names. Thus we were able to show the actual table names in our report.
All of this with just a few lines of code!
Why not install a Spectrum SMF Writer trial right now and start making your own SMF reports!
These Spectrum SMF Writer Statements:
OPTION: DB2SUBSYS('DB2T') NOGRANDTOTALS
* THE MAIN INPUT FILE IS THE SMF FILE. WE NORMALIZE TO GET ALL SEGMENTS
INPUT: SMF102V9 LIST(YES)
NORMWHEN(SM102RTY=102 AND QWHSIID = 144 AND QWT02R1N > 0)
* LOOK UP SPACE NAME IN IBM SYSTEM DB2 TABLE
READ: SPACE SHOWFLDS(YES)
WHERE(DBID = QW0144DB AND PSID = QW0144PS)
* LOOK UP TABLE NAME IN IBM SYSTEM DB2 TABLE
READ: TABLE SHOWFLDS(YES)
WHERE(TABLE.DBID = QW0144DB AND TABLE.OBID = QW0144OB)
* SELECT THE RECORDS WE WANT FOR THE REPORT
INCLUDEIF: SM102RTY = 102
AND QWHSIID = 144
AND SM102DTE = 4/11/10
AND SM102TME >= 18:00:00 AND < 19:00:00
AND SPACE.NAME : 'FLOAT'
AND QWHCOPID = 'TTP011A'
* SELECT THE DATA FIELDS TO PRINT IN THE REPORT
* SPECIFY REPORT TITLES
TITLE: 'DB2 AUDIT REPORT FROM SMF 102 RECORDS'
TITLE: 'ACCESS TO TABLES BEGINNING WITH "FLOAT"'
Produce This SMF Report:
DB2 AUDIT REPORT FROM SMF 102 RECORDS
ACCESS TO TABLES BEGINNING WITH "FLOAT"
REC IFC SMF SMF CONNECT PLAN DBID OBID
TYP ID LOG DATE LOG TIME NAME NAME OPERATOR/ID DBID NAME PSID OBID NAME
___ ____ ________ ___________ ________ ________ ___________ ____ ________________ ____ ____ ________________
102 144 04/11/10 18:37:40.13 TSO ADB TTP011A 291 FLOATACC1 2 3 FLOATAAC1
102 144 04/11/10 18:37:54.06 TSO ADB TTP011A 291 FLOATACC1 2 3 FLOATAAC1
102 144 04/11/10 18:38:09.56 TSO ADB TTP011A 291 FLOATACC1 2 3 FLOATAAC1
102 144 04/11/10 18:38:19.11 TSO ADB TTP011A 292 FLOATTEMP 2 3 FLOATTEMP
102 144 04/11/10 18:38:31.80 TSO ADB TTP011A 292 FLOATTEMP 2 3 FLOATTEMP
102 144 04/11/10 18:48:35.58 DB2CALL SPECT303 TTP011A 292 FLOATTEMP 2 3 FLOATTEMP
102 144 04/11/10 18:49:29.27 DB2CALL SPECT303 TTP011A 291 FLOATACC1 2 3 FLOATAAC1