Tracing Problems in DB2

Team-Fly

---

 

	DB2 Universal Database for OS/390 v7.1 Application Certification Guide By Susan Lawson
	Table of Contents
	Chapter 17.  Application Performance and Optimization

---

Tracing Problems in DB2

These traces can be used for problem determination:

• DB2 trace

• IMS attachment facility trace

• CICS trace

• Three TSO attachment facility traces

• CAF trace stream

• OS/390 RRS trace stream

• MVS component trace used for IRLM

In this section we look at DB2 traces. For information about other trace capabilities, refer to the appropriate product documentation.

DB2 Traces

DB2 trace allows you to trace and record subsystem data and events. There are five different types of trace, and DB2 trace can record six types of data: statistics, accounting, audit, performance, monitor, and global. The tables later in this chapter indicate which instrumentation facility IDs (IFCIDs) are activated for the different types of trace, 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.

Statistics

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. It is a systemwide trace and 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 statistics is specified YES in panel DSNTIPN. If the statistics trace is started using the START TRACE command, then class 1 is the default class.

• Class 1 provides information about system services and database statistics. It also includes the system parameters that were in effect when the trace was started.

• Class 3 provides information about deadlocks and timeouts.

• Class 4 provides information about exceptional conditions.

• Class 5 provides information about data sharing.

If you specified YES in the SMF STATISTICS field on the Tracing Panel (DSNTIPN), the statistics trace starts automatically when you start DB2, sending class 1, 3, 4, and 5 statistics data to SMF. SMF records statistics data in both SMF type 100 and 102 records. IFCIDs 0001, 0002, 0202, and 0230 are of SMF type 100. All other IFCIDs in statistics trace classes are of SMF type 102. From panel DSNTIPN, you can also control the statistics collection interval (STATISTICS TIME field). The statistics trace is written on an interval basis, and you can control the exact time that statistics traces are taken.

Accounting

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

• Start and stop times

• Number of commits and aborts

• The number of times certain SQL statements are issued

• Number of buffer pool requests

• Counts of certain locking events

• Processor resources consumed

• Thread wait times for various events

• RID pool processing

• Distributed processing

• Resource limit facility statistics

Accounting times are usually the prime indicator of a performance problems, and most often should be the starting point for analysis. DB2 times are classified as follows :

• Class 1: This time shows the time the application spent since connecting to DB2, including time spent outside DB2.

• Class 2: This shows the elapsed time spent in DB2. It is divided into CPU time and waiting time.

• Class 3: This elapsed time is divided into various waits, such as the duration of suspensions due to waits for locks and latches or waits for I/O.

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 is then collected at the end of the accounting period; it 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. Though you can turn on class 7 while a plan is being executed, accounting trace information is only gathered 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, there is minimal additional performance cost for activating accounting trace classes 7 and 8.

If you want information from either or both accounting class 2 and 3, 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 (IFI) requests. If an agent did not issue any IFI requests, these fields are not included in the accounting record.

If you specified yes for SMF ACCOUNTING on the Tracing Panel, the accounting trace starts automatically when you start DB2 and sends IFCIDs that are of SMF type 100 to SMF. The accounting record IFCID 0003 is of SMF type 101.

Performance

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.

Audit

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 statements, you can specify whether or not 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.

Monitor

The monitor trace records data for online monitoring with user -written programs. This trace type has several predefined classes; those that are used explicitly for monitoring are listed here:

• Class 1 (the default) allows any application program to issue an IFI READS request to the IFI facility. If monitor class 1 is inactive, a READS request is denied . Activating class 1 has a minimal impact on performance.

• Class 2 collects processor and elapsed time information. The information can be obtained by issuing a READS request for IFCID 0147 or 0148. In addition, monitor trace class 2 information is available in the accounting record, IFCID 0003. Monitor class 2 is equivalent to accounting class 2 and results in equivalent overhead. Monitor class 2 times appear in IFCIDs 0147, 0148, and 0003 if either monitor trace class 2 or accounting class 2 is active.

• Class 3 activates DB2 wait timing and saves information about the resource causing the wait. The information can be obtained by issuing a READS request for IFCID 0147 or 0148. In addition, monitor trace class 3 information is available in the accounting record, IFCID 0003. As with monitor class 2, monitor class 3 overhead is equivalent to accounting class 3 overhead. When monitor trace class 3 is active, DB2 can calculate the duration of a class 3 event, such as when an agent is suspended due to an unavailable lock. Monitor class 3 times appear in IFCIDs 0147, 0148, and 0003 if either monitor class 3 or accounting class 3 is active.

• Class 5 traces the amount of time spent processing IFI requests.

• Class 7 traces the amount of time an agent spent in DB2 to process each package. If monitor trace class 2 is active, activating class 7 has minimal performance impact.

• Class 8 traces the amount of time an agent was suspended in DB2 for each package executed. If monitor trace class 3 is active, activating class 8 has minimal impact.

Invoking Traces

Traces can be started automatically via the DB2 install panels, or they can be stopped and started dynamically by using the TRACE commands. The trace commands include

• START TRACE: Invokes one or more types of trace.

• DISPLAY TRACE: Displays the trace options that are in effect.

• STOP TRACE: Stops any trace that was started by either the START TRACE command or the parameters specified when installing or migrating.

• MODIFY TRACE: Changes the trace events (IFCIDs) being traced for a specified active trace. Several parameters can be specified to further qualify the scope of a trace. Specific events within a trace type as well as events within specific DB2 plans, authorization IDs, resource manager IDs, and locations, can be traced. The destination to which trace data is sent can also be controlled.

Trace Classes

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 class 1, 2, and 3, and SMF statistics class 1, 3, and 4 (1 and 3 at a minimum) selected 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 other traces, it is wise to time the trace to only the IFCIDs necessary for the appropriate performance analysis or problem diagnosis. These traces also should only be run for short periods of time. Following is an example of limiting a trace.

 -START TRACE(PERFTC1) CLASS(8) IFCID(0221) PLANNAME(CERTPLA) 

In the above example, this limits a performance trace to class 8 with IFCID 221, which is used to view the actual degree of parallelism at runtime.

IFCIDs

Tables 17-5 through 17-9 show the various IFCIDs that are started for each different type of class within a trace.

Table 17-5. Accounting Trace

Class	IFCID	Description
1	3	All accounting
	106	System parameters in effect
	239	Overflow for 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 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 requestor resumed by I/O initialization
	170	Suspend for sync 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 requestor resumed by I/O initialization
	170	Suspend for sync 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

Table 17-6. Audit Trace

Class	IFCID	Description
1	140	Authorization failures
2	141	Explicit grants and revokes
3	142	Creates, alters, drops audit
4	143	First attempted write audited object
5	144	First attempted read audited object
6	145	Audit log record of some SQL statements
7	55	Issuance of set current SQLID
	83	End identify request
	87	Ending of sign-on request
	169	Distributed authorization ID translation
	312	DCE security
8	23	Utility start information
	24	Utility object or phase change
	25	Utility end information
9	146	User-defined audit trace

Table 17-7. Monitor Trace

Class	IFCID	Description
1	1	System services
	2	Database services
	106	System parameters in effect
	124	Current SQL statement
	129	Virtual storage access method (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 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 requestor resumed by I/O initialization
	170	Suspend for sync 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
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 requestor resumed by I/O initialization
	170	Suspend for sync 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 pack/DBRM
	242	Begin wait for scheduled stored procedure
	243	End wait for scheduled stored procedure

Table 17-8. 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	Dataset 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 sync request
	89	Ending of sync 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 exec
	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
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 sync or async write I/O
	10	Beginning of async 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	Dataset open/close information
	127	Agent ready to suspend page wait
	128	Page requestor 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 act log write begin
	39	Log mgr wait act log write I/O end
	40	Log manager archive write I/O begin
	41	Log manager archive write I/O end
	104	Log dataset 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
7	21	Detail lock request on return from IRLM
	105	Internal DBID OBID to DB/TS
	106	System parameters in effect
	107	Dataset open/close information
	199	Buffer pool dataset 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	Dataset 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	Dataset 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 BEF/aft alter buffer pool
	256	Attributes BEF/aft alter buffer pool
11	46	Agent begin execution unit switch
	47	New SRB execution unit started
	48	New SRB 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	Dataset 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 struct rebuild/expand/contract
	268	End CF structure rebuild/expand/contract
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

Table 17-9. Statistics Trace

Class	IFCID	Description
1	1	System services
	2	Database services
	105	Internal DBID OBID to DB/TS
	106	System parameters in effect
	202	Buffer pool attributes
2	152	User-defined statistics trace
3	172	Units of work involved in deadlock
	196	Lock timeout details
	250	Connect/disconnect from GBP
	258	Dataset extend information
	330	Active log shortage
4	191	Data capture for DDIS errors
	192	DDM-level 6a header errors
	193	UOW disposition/sqlcode mismatch
	194	Invalid SNA FMH-5 received
	195	First failure data capture for DRDS
	203	Heuristic decision occurred
	204	Partner cold start detected
	205	Incorrect logname/sync parameters
	206	SNA compare states protocol error
	207	Heuristic damage occurred
	208	SNA sync point protocol error
	209	Sync point communication failure
	210	Log name changed on warm start
	235	Conditional restart data loss
	236	Exchange log names protocol error
	238	DB2 restart error
	267	Start of CF structure rebuild
	268	End of CF structure rebuild
5	230	Data sharing global statistics
7	326	Workload manager delay monitor support

---

Team-Fly

Top