DB2 traces allow you to trace and record subsystem data and events and determine problems:
Five different types of traces exist, and DB2 traces can record six types of data: statistics, accounting, performance, audit, monitor, and global. The tables later in this chapter indicate which Instrumentation Facility IDs (IFCIDs) are activated for the various types of traces, the classes within those trace types, and the information each IFCID returns. The trace records are written using Generalized Trace Facility (GTF) or System Management Facility (SMF) records. We now take a look at the types of data DB2 collects for each trace class.
The data collected in the statistics trace allows you to conduct DB2 capacity planning and to tune the entire set of DB2 programs. The statistics trace reports information about how much the DB2 system services and database services are used. This systemwide trace should not be used for charge-back accounting. Use the information the statistics trace provides to plan DB2 capacity or to tune the entire set of active DB2 programs.
Statistics trace classes 1, 3, 4, and 5 are the default classes for the statistics trace if YES is specified in panel DSNTIPN. If the statistics trace is started using the START TRACE command, class 1 is the default class.
The accounting trace provides data that allows you to assign DB2 costs to individual authorization IDs and to tune individual programs. The DB2 accounting trace provides information related to application programs, including such things as
Accounting times are usually the prime indicator of performance problems, and most often should be the starting point for analysis. DB2 times are classified as follows.
DB2 trace begins collecting this data at successful thread allocations to DB2 and writes a completed record when the thread terminates or when the authorization ID changes. Accounting data for class 1, the default, is accumulated by several DB2 components during normal execution. This data, collected at the end of the accounting period, does not involve as much overhead as individual event tracing.
On the other hand, when you start class 2, 3, 7, or 8, many additional trace points are activated. Every occurrence of these events is traced internally by DB2 trace, but these traces are not written to any external destination. Rather, the accounting facility uses these traces to compute the additional total statistics that appear in the accounting record, IFCID 003, when class 2 or class 3 is activated. Accounting class 1 must be active to externalize the information.
To turn on accounting for packages and DBRMs, accounting trace classes 1 and 7 must be active. Although you can turn on class 7 while a plan is being executed, accounting trace information is gathered only for packages or DBRMs executed after class 7 is activated. Activate accounting trace class 8 with class 1 to collect information about the amount of time an agent was suspended in DB2 for each executed package. If accounting trace classes 2 and 3 are activated, additional performance cost for activating accounting trace classes 7 and 8 is minimal.
If you want information from either accounting class 2 or 3 or both, be sure to activate classes 2 and/or 3 before your application starts. If these classes are activated during the application, the times gathered by DB2 trace are only from the time the class was activated.
Accounting trace class 5 provides information on the amount of elapsed time and task control block (TCB) time that an agent spent in DB2 processing Instrumentation Facility Interface requests. If an agent did not issue any IFI requests, these fields are not included in the accounting record.
The performance trace provides information about a variety of DB2 events, including events related to distributed data processing. You can use this information to further identify a suspected problem or to tune DB2 programs and resources for individual users or for DB2 as a whole. To trace performance data, you must use the START TRACE(PERFM) command. Performance traces cannot be automatically started.
This trace provides data that can be used to monitor DB2 security and access to data to ensure that data access is allowed only for authorized purposes. On the CREATE TABLE or ALTER TABLE statement, you can specify whether a table is to be audited and in what manner; you can also audit security information, such as any access denials, grants, or revokes for the table. The default causes no auditing to take place. If you specified yes for AUDIT TRACE on the tracing panel, audit trace class 1 starts automatically when you start DB2. By default, DB2 sends audit data to SMF.
The monitor trace records data for online monitoring with user-written programs. This trace type has several predefined classes; the following are used explicitly for monitoring.
Traces can be started automatically via the DB2 install panels or can be stopped and started dynamically by using the trACE commands, as follows.
It is important to have the appropriate classes always gathering information about your DB2 subsystem and its activity. It is a general recommendation that you have SMF accounting classes 1, 2, and 3 and SMF statistics classes 1, 3, and 41 and 3 at a minimumselected during normal execution. Any other trace classes should not be run constantly, because they cause excessive overhead if run for long periods of time.
When executing performance traces, it is wise to limit the trace to only the IFCIDs necessary for the appropriate performance analysis or problem diagnosis. These traces also should be run for only short periods of time. Following is an example of limiting a trace.
This example limits a performance trace to class 8 with IFCID 221, which is used to view the degree of parallelism at runtime.
Tables 18-1 through 18-5 show the various IFCIDs that are started for each type of class within a trace.
Table 18-1. Accounting Trace
Class | IFCID | Description |
---|
1 | 3 | All accounting |
106 | System parameters in effect |
239 | Package accounting |
2 | 200 | UDF entry/exit signal |
232 | DB2 thread entry/exit signal |
3 | 6 | Beginning of a read I/O operation |
7 | CC after read I/O operation |
8 | Beginning of synchronous write I/O |
9 | CC of synchronous or asynchronous write I/O |
32 | Beginning of wait for log manager |
33 | End of wait for log manager |
44 | Lock suspend or identify call IRLM |
45 | Lock resume |
51 | Shared latch resume; serviceability |
52 | Shared latch wait; serviceability |
56 | Exclusive latch wait; serviceability |
57 | Exclusive latch resume; serviceability |
117 | Begin thread wait time for log I/O |
118 | End thread wait time for log I/O |
127 | Agent ready to suspend page wait |
128 | Page requester resumed by I/O initialization |
170 | Suspend for synchronous EXEC.N unit switch |
171 | Resume agent waiting DB2 service task |
174 | Begin archive log mode (QUIESCE) |
175 | End archive log mode (QUIESCE) |
213 | Beginning of wait for claim request |
214 | End of wait for claim request |
215 | Beginning of wait for drain request |
216 | End of wait for drain request |
226 | Beginning of suspend for page latch |
227 | End of suspend for page latch |
242 | Begin wait for scheduled stored procedure |
243 | End wait for scheduled stored procedure |
313 | Messages for long-running URs |
4 | 151 | User-defined accounting trace |
5 | 187 | Entry to and exit from IFI |
7 | 232 | DB2 thread entry/exit signal |
232 | For package-level or DBRM-level accounting |
240 | Event signal for package accounting |
8 | 6 | Beginning of a read I/O operation |
7 | CC after read I/O operation |
8 | Beginning of synchronous write I/O |
9 | CC of sync or async write I/O |
32 | Beginning of wait for log manager |
33 | End of wait for log manager |
44 | Lock suspend or identify call IRLM |
45 | Lock resume |
51 | Shared latch resume; serviceability |
52 | Shared latch wait; serviceability |
56 | Exclusive latch wait; serviceability |
57 | Exclusive latch resume; serviceability |
117 | Begin thread wait time for log I/O |
118 | End thread wait time for log I/O |
127 | Agent ready to suspend page wait |
128 | Page requester resumed by I/O initialization |
170 | Suspend for synchronous EXEC.N unit switch |
171 | Resume agent waiting DB2 service task |
174 | Begin archive log mode (QUIESCE) |
175 | End archive log mode (QUIESCE) |
213 | Beginning of wait for claim request |
214 | End of wait for claim request |
215 | Beginning of wait for drain request |
216 | End of wait for drain request |
226 | Beginning of suspend for page latch |
227 | End of suspend for page latch |
241 | Begin/end suspension of package or DBRM |
242 | Begin wait for scheduled stored procedure |
243 | End wait for scheduled stored procedure |
329 | Asynchronous GBP requests |
Table 18-3. Monitor Trace
Class | IFCID | Description |
---|
1 | 1 | System services |
2 | Database services |
106 | System parameters in effect |
124 | Current SQL statement |
129 | VSAM CIsDB2 recover log |
147 | Summary-thread status record |
148 | Detailed thread status record |
149 | Lock information for a resource |
150 | Lock information for an agent IFCID |
202 | System parameters |
230 | Data sharing global statistics |
254 | Group buffer pool usage |
306 | Log record retrieval |
316 | Prepared-statement cache statistics |
317 | Prepared-statement cache statement text |
2 | 232 | DB2 thread-entry exit signal |
3 | 6 | Beginning of a read I/O operation |
7 | CC after read I/O operation |
8 | Beginning of synchronous write I/O |
9 | CC of synchronous or asynchronous write I/O |
32 | Beginning of wait for log manager |
33 | End of wait for log manager |
44 | Lock suspend or identify call IRLM |
45 | Lock resume |
51 | Shared latch resume; serviceability |
52 | Shared latch wait; serviceability |
56 | Exclusive latch wait; serviceability |
57 | Exclusive latch resume; serviceability |
117 | Begin thread wait time for log I/O |
118 | End thread wait time for log I/O |
127 | Agent ready to suspend page wait |
128 | Page requester resumed by I/O initialization |
170 | Suspend for synchronous EXEC unit switch |
171 | Resume agent waiting DB2 service task |
174 | Begin archive log mode (QUIESCE) |
175 | End archive log mode (QUIESCE) |
213 | Beginning of wait for claim request |
214 | End of wait for claim request |
215 | Beginning of wait for drain request |
216 | End of wait for drain request |
226 | Beginning of suspend for page latch |
227 | End of suspend for page latch |
242 | Begin wait for scheduled stored procedure |
243 | End wait for scheduled stored procedure |
329 | Asynchronous GBP requests |
4 | 155 | User-defined monitor trace |
5 | 187 | Entry or exit to IFI |
6 | 185 | Data-capture information |
7 | 232 | DB2 thread entry/exit signal |
232 | For package-level or DBRM-level accounting |
240 | Event signal for package accounting |
8 | 6 | Beginning of a read I/O operation |
7 | CC after read I/O operation |
8 | Beginning of synchronous write I/O |
9 | CC of sync or async write I/O |
32 | Begin of wait for log manager |
33 | End of wait for log manager |
44 | Lock suspend or identify call IRLM |
45 | Lock resume |
51 | Shared latch resume; serviceability |
52 | Shared latch wait; serviceability |
56 | Exclusive latch wait; serviceability |
57 | Exclusive latch resume; serviceability |
117 | Begin thread wait time for log I/O |
118 | End thread wait time for log I/O |
127 | Agent ready to suspend page wait |
128 | Page requester resumed by I/O initialization |
170 | Suspend for synchronous EXEC unit switch |
171 | Resume agent waiting DB2 service task |
174 | Begin archive log mode (QUIESCE) |
175 | End archive log mode (QUIESCE) |
213 | Beginning of wait for claim request |
214 | End of wait for claim request |
215 | Beginning of wait for drain request |
216 | End of wait for drain request |
226 | Beginning of suspend for page latch |
227 | End of suspend for page latch |
241 | Begin/end suspension of package/DBRM |
242 | Begin wait for scheduled stored procedure |
243 | End wait for scheduled stored procedure |
Table 18-4. Performance Trace
Class | IFCID | Description |
---|
1 | 1 | System services |
2 | Database services |
31 | EDM pool-full condition |
42 | A checkpoint started |
43 | A checkpoint ended |
76 | Beginning of an end-memory request |
77 | End of an end-memory request |
78 | Beginning of an end-task request |
79 | End of an end-task request |
102 | Detection of short on storage |
103 | Setting off of short on storage |
105 | Internal DBID OBID to DB/TS |
106 | System parameters in effect |
107 | Data set open/close information |
153 | User-defined except-condition trace |
2 | 3 | All accounting |
68 | Beginning of a rollback request |
69 | End of a rollback request |
70 | Begin commit phase 2 request |
71 | End commit phase 2 request |
72 | Beginning of create-thread request |
73 | End of a create-thread request |
74 | Beginning of terminate-thread request |
75 | End of a terminate-thread request |
80 | Beginning of an establish-exit request |
81 | End of an establish exit request |
82 | Begin identify request |
83 | End identify request |
84 | Begin phase 1 commit request |
85 | End phase 1 commit request |
86 | Beginning of sign-on request |
87 | End of sign-on request |
88 | Beginning of synchronous request |
89 | Ending of synchronous request |
106 | System parameters in effect |
174 | Begin archive log mode (QUIESCE) |
175 | End archive log mode (QUIESCE) |
3 | 22 | Miniplans generated |
53 | End of descr, commit, rollback, or error |
55 | Issuance of set current SQLID |
58 | End of SQL statement execution |
59 | Start of FETCH SQL statement execution |
60 | Start of SELECT SQL statement execution |
61 | Start of INSERT, UPDATE, DELETE SQL |
62 | Start of DDL statement execution |
63 | SQL statement to be parsed |
64 | Start PREPARE SQL statement execution |
65 | Start open cursor static or dynamic SQL |
66 | Start close cursor static or dynamic SQL |
92 | Start access method services |
95 | Sort started |
96 | Sort ended |
97 | Access method services cmd compl |
106 | System parameters in effect |
112 | Attributes plan after thread allocation |
177 | Successful package allocation |
233 | Start/end call to user routine |
237 | Set current degree information |
272 | Associate locators information |
273 | Allocate cursor information |
324 | Function resolution information |
325 | Start/end trigger activation |
350 | Complete SQL statement |
4 | 6 | Beginning of a read I/O operation |
7 | Completion code after read I/O |
8 | Beginning of synchronous write I/O |
9 | CC of synchronous or asynchronous write I/O |
10 | Beginning of asynchronous write I/O |
29 | Start EDM I/O request; load DBD or CT |
30 | End of EDM I/O request |
105 | Internal DBID OBID to DB/TS |
106 | System parameters in effect |
107 | Data set open/close information |
127 | Agent ready to suspend page wait |
128 | Page requester resumed by I/O initialization |
226 | Begin of suspend for page latch |
227 | End of suspend for page latch |
5 | 32 | Begin of wait for log manager |
33 | End of wait for log manager |
34 | Log manager wait for read I/O begin |
35 | Log manager wait for read I/O end |
36 | Log manager wait for non-I/O begin |
37 | Log manager wait for non-I/O end |
38 | Log manager wait active log write begin |
39 | Log manager wait active log write I/O end |
40 | Log manager archive write I/O begin |
41 | Log manager archive write I/O end |
104 | Log data set mapping |
106 | System parameters in effect |
114 | Start archive read I/O wait |
115 | End read archive I/O wait on DASD |
116 | End read archive I/O wait on tape |
117 | Begin archive read |
118 | End archive read |
119 | BSDS write I/O beginning |
120 | BSDS write I/O end |
228 | Start archive allocation wait |
229 | End archive allocation wait |
6 | 20 | Locking summary |
44 | Lock suspend or an ID; call to IRLM |
45 | Lock resume |
105 | Internal DBID OBID to DB/TS |
106 | System parameters in effect |
107 | Data set open/close information |
172 | Units of work involved in deadlock |
196 | Lock timeout details |
213 | Beginning of wait for drain lock |
214 | End of wait for drain lock |
218 | Summary of lock-avoidance technique |
337 | Lock escalation occurred |
7 | 21 | Detail lock request on return from IRLM |
105 | Internal DBID OBID to DB/TS |
106 | System parameters in effect |
107 | Data set open/close information |
199 | Buffer pool data set statistics |
223 | Detail of lock-avoidance technique |
8 | 13 | Input to hash scan |
14 | End of hash scan |
15 | Input matching- or nonmatching-index scan |
16 | Input to the first insert |
17 | Input to sequential scan |
18 | End index scan, insert, sequential scan |
105 | Internal DBID OBID to DB/TS |
106 | System parameters in effect |
107 | Data set open/close information |
125 | RID list processing usage |
221 | Parallel degree for parallel group |
222 | Parallel group elapsed time |
231 | Parallel group completion |
305 | Table-check constraints |
311 | Temporary tables |
9 | 26 | Work file obtained for sort |
27 | Number of ordered records sort run |
28 | Detailed sort information |
95 | Sort started |
96 | Sort ended |
106 | System parameters in effect |
10 | 23 | Utility start information |
24 | Utility object or phase change |
25 | Utility end information |
90 | Command text of entered DB2 command |
91 | Completion status of a DB2 command |
105 | Internal DBID OBID to DB/TS |
106 | System parameters in effect |
107 | Data set open/close information |
108 | Beginning of bind/rebind |
109 | End of bind/rebind |
110 | Beginning of free plan |
111 | End of free plan |
201 | Status before/after alter buffer pool |
219 | Listdef data set information |
220 | Utility-output data set information |
256 | Attributes before/after alter buffer pool |
11 | 46 | Agent begin execution unit switch |
47 | New service request block execution unit started |
48 | New service request block execution unit completed |
49 | Begin new TCB |
50 | End new TCB |
51 | Shared-latch resume |
52 | Shared-latch wait |
56 | Exclusive latch wait |
57 | Exclusive latch resume |
93 | Suspend was called |
94 | Event resumed |
106 | System parameters in effect |
113 | Attributes plan after agent allocation |
12 | 98 | Begin getmain/freemain (nonpool) |
99 | End getmain/freemain (nonpool) |
100 | Begin getmain/freemain (pool) |
101 | End getmain/freemain (pool) |
106 | System parameters in effect |
13 | 11 | Results of a validation exit call |
12 | Results edit exit call encode record |
19 | Results edit exit call decode a row |
105 | Internal DBID OBID to DB/TS |
106 | System parameters in effect |
107 | Data set open/close information |
14 | 67 | Start of accounting collection |
106 | System parameters in effect |
121 | Entry allocating DB2 connection |
122 | Exit allocating DB2 connection |
15 | 154 | User-defined routine cond perf |
16 | 157 | DRDS interface with RDS RDI call types |
158 | DRDS interface with conversation manager |
159 | DRDS requesting location data |
160 | Requesting agent data |
161 | Serving agent data |
162 | Distributed transaction manager request agent data |
163 | Distributed transaction manager response agent data |
167 | Conversation allocation request queued |
183 | DRDS RDS/SCC interface data |
17 | 211 | Information about claims |
212 | Information about drains |
213 | Beginning of wait for drain lock |
214 | End of wait for drain lock |
215 | Beginning of wait of claim count to 0 |
216 | End of claim count to go to 0 |
20 | 249 | EDM pool DBD invalidation |
250 | Group buffer pool continued/discontinued |
251 | P-lock operations |
256 | Alter buffer pool command |
257 | Details of IRLM notify request |
261 | Group buffer pool checkpoint |
262 | Group buffer pool cast-out threshold processing |
267 | Begin CF structure rebuild/expand/contract |
268 | End CF structure rebuild/expand/contract |
329 | Asynchronous GBP request |
21 | 255 | Buffer refresh due to XI |
259 | P-lock request/negotiation request |
263 | Page set and partition cast-out data |
314 | Authorization exit parameters |
327 | Language environment runtime information |
A lot of the information you can get via the accounting and statistics reports can also be obtained using the DISPLAY command. The DISPLAY command can show details about current activity in the subsystem. Following are the various types of DISPLAY commands:
This type of information can also be viewed in the DB2 Control Center.