Using Traces to Solve Problems in DB2


DB2 Traces

DB2 traces allow you to trace and record subsystem data and events and determine problems:

  • DB2 trace

  • IMS attachment facility trace

  • CICS trace

  • Three TSO attachment facility traces

  • CAF trace stream

  • z/OS RRS trace stream

  • MVS component trace used for IRLM

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.

Trace Classes
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. 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.

  • 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 type 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

  • 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 performance problems, and most often should be the starting point for analysis. DB2 times are classified as follows.

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

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

  • Class 3, elapsed time, is divided into various waits, such as the duration of suspensions owing 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, 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.

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 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.

Monitor

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.

  • 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 because of 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 can be stopped and started dynamically by using the trACE commands, as follows.

  • 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 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.

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

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

IFCIDs

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-5. 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

Data set extend information

330

Active log shortage

335

Stalled system event notification

337

Lock escalation occurred

4

191

Data capture for DDIS errors

192

DDM-level 6a header errors

193

UOW disposition/SQLCODE mismatch

194

Invalid SNA FMH5 received

195

First failure data capture for DRDS

203

Heuristic decision occurred

204

Partner cold start detected

205

Incorrect log name/synchronous parameters

206

SNA compare-states protocol error

207

Heuristic damage occurred

208

SNA synchronous point protocol error

209

Synchronous 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


Table 18-2. 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

Distributed Computing Environment Security

319

Audit trail for security processing

8

23

Utility start information

24

Utility object or phase change

25

Utility end information

219

Listdef data set information

220

Utility-output data set information

9

146

User-defined audit trace


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


Using DISPLAY Commands

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:

  • DISPLAY ARCHIVE

  • DISPLAY BUFFERPOOL

  • DISPLAY DATABASE

  • DISPLAY DDF

  • DISPLAY FUNCTION SPECIFIC

  • DISPLAY GROUP

  • DISPLAY GROUPBUFFERPOOL

  • DISPLAY LOCATION

  • DISPLAY LOG

  • DISPLAY PROCEDURE

  • DISPLAY RLIMIT

  • DISPLAY THREAD

  • DISPLAY TRACE

  • DISPLAY UTILITY

NOTE

This type of information can also be viewed in the DB2 Control Center.


For more information on all the options on the DISPLAY command, refer to the IBM DB2 UDB Version 8 for z/OS Command Reference. Examples of the DISPLAY command can also be seen in Chapters 2 and 7.



DB2 for z. OS Version 8 DBA Certification Guide
DB2 for z/OS Version 8 DBA Certification Guide
ISBN: 0131491202
EAN: 2147483647
Year: 2003
Pages: 175
Authors: Susan Lawson

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net