Input for SQL Activity reports

Each Db2 trace type and trace class contains one or more Instrumentation Facility Component identifiers (IFCIDs). The SQL Activity report set uses Db2 trace class and IFCID information to register specific SQL activity events.

SQL activity events are classified as follows:

  • Accounting
  • Base
  • Data capture
  • Exit
  • I/O
  • Locking
  • Scan
  • Sort
  • Workload highlights
  • Host variables data

An SQL activity event is normally characterized by a start record and an end record. When you select event records as input for your reports, you must include the paired end event, where applicable. When you use the ISPF monitor or the Collect Report Data function of the Performance Expert Client to manage Db2 traces, the trace generator automatically includes the correct end record.

The following table shows the IFCIDs used in SQL Activity reporting, together with the associated Db2 trace classes and end pair IFCIDs, where appropriate.
Table 1. IFCIDs used for SQL Activity reports
Start IFCID Db2 trace class & SQL Activity event type End IFCID
3 Accounting
  • 1 Accounting data
  • 2 DB2 times
  • 3 Suspension, system events
  • 5 Package in DB2 time
 None
6 Read I/O Start 4 I/O 7 Read I/O Stop
8 Write I/O Synch begin 4 I/O 9 Write I/O end
11 Validate Exit 13 Exit  
12 Edit Exit to Encode 13 Exit  
15 Index Scan Begin 8 Scan 18 Scan End
16 Insert Scan Begin 8 Scan 18 Scan End
17 Sequential Scan Begin 8 Scan 18 Scan End
19 Edit Exit to Decode 13 Exit  
20 Lock Summary 6 Locking None
22 Minibind 3 Base None
28 Sort Phase Detail 9 Sort None
44 Lock Suspend 6 Locking 45 Lock Resume
53 SQL Describe, Commit, Rollback, or Remote statement 3 Base None
55 Set SQLID 3 Base None
59 Fetch Start 3 Base 58 End SQL
60 Select Start 3 Base 58 End SQL
61 Insert, Update, or Delete Start 3 Base 58 End SQL
62 DDL Start 3 Base 58 End SQL
63 SQL Statement 3 Base None
64 Prepare Start 3 Base 58 End SQL
65 Open Cursor 3 Base 58 End SQL
66 Close Cursor 3 Base 58 End SQL
68 Rollback Start 3 Base 69 Rollback End
70 Commit Phase 2 Start 3 Base 71 Commit Phase 2 End
72 Create Thread Start 3 Base 73 Create Thread End
74 Terminate Thread Start 3 Base 75 Terminate Thread End
84 Prepare Start 3 Base 85 Prepare End
86 Signon Start 3 Base 87 Signon End
88 Synch Start 3 Base 89 Synch End
92 AMS Command Start 3 Workload highlight 97 AMS Command End
95 Sort Start 3 Sort 96 Sort End
105 DBID/OBID Translation 10 Base  
106 System Parameters 8 Scan 18 Scan End
107 Open/Close 10 Base  
108 Bind Start 10 Base 109 Bind End
125 RID Pool Processing 8 Scan 18 Scan End
157 DRDS RDS Interface 16 Base None
159 DRDS Req Site Data 16 Base None
160 DC Requester 16 Base None
161 DC Server 16 Base None
162 DTM Request 16 Base None
163 DTM Respond 16 Base None
174 Arch Log CMD Sus Start 3 Base 175 Arch Log CMD Sus End
177 Package Allocation 3 Base None
183 DRDS RDS/SCC Interface 16 Base 183 DRDS RDS/SCC Interface return
185 READs Data Capture Start   188 READs Data Capture End
213 Drain Lock Wait Start 6 Locking 214 Drain Lock Wait End
215 Claim Count 0 Wait Start 6 Locking 216 Claim Count 0 Wait End
218 Lock Avoidance Summary 6 Locking None
221 Parallel Group Execution 8 Scan 18 Scan End
222 Parallel Group Elapsed Time 8 Scan 18 Scan End
226 Page Latch Contention Start 4 Locking 227 Page Latch Contention End
231 Parallel Group Task Time 8 Scan 18 Scan End
233 Call User Routine 3 Base 233 Call User Routine end
237 Set Current Degree 3 Base 58 End SQL
239 Overflow Package/DBRM 7 Accounting None
247 SQLDA Data and Input Host Variable Data 5 Host variables data None
272 Associate Locators 3 Base 58 End SQL
273 Allocate Cursor 3 Base 58 End SQL
305 Table Check Constraint 8 Scan 18 Scan End
324 Function Resolution 3 Base 58 End SQL
325 Trigger Activation 3 Base 58 End SQL