Michigan DB2 Users Group Chris Skelnik Sr' DBA TransUnion, LLC cskelniktransunion'com

1
Jumping Into DB2 Traces and Performance Monitors
Michigan DB2 Users GroupChris SkelnikSr.
DBATransUnion, LLCcskelnik_at_transunion.com
2
What Well Cover

• Introduction to DB2 Traces
• Performance Monitors 101
• Application Tuning Using Traces and Monitors
• System Tuning Using Traces and Monitors
• Putting Together a Monitoring Strategy

3
The Four Ws of DB2 Traces

• What?
• Mechanism to collect information about DB2 events
• When?
• Activated at DB2 startup or START TRACE command
• Where?
• Trace information written to SMF, GTP, or OPx
• Why?
• Help us understand what is happening underneath
  the covers of DB2

4
DB2 Trace Types, Classes, IFCIDs
MONITOR
PERFORMANCE
AUDIT
STATISTICS
ACCOUNTING
CLASS 1
CLASS 2
CLASS 3
IFCID 45
IFCID 44
IFCID 33
IFCID 9
IFCID 32
IFCID 8
IFCID 7
IFCIDS
IFCID 6
TRACE CLASSES
TRACE TYPES
DB2 TRACES
5
Accounting Trace
PACKAGES
CPU TIME
STOREDPROCEDURES
DATA SHARING
SYNC. I/O
ELAPSED TIME
WAIT TIMES
GETPAGES
LOCKS
FETCHES
DB2 Accounting Trace
6
Accounting Trace Classes

• Class 1 General Thread Information
• SQL Counts, Getpages, BP I/O
• Class 2 In-DB2 Times
• DB2 CPU and Elapsed
• Class 3 Wait Times
• I/O, Lock, Other Various Wait Times
• Class 7 Package In-DB2 Times
• Same as Class 2 but at Package Level
• Class 8 Package Wait Times
• Same as Class 3 but at Package Level

7
Statistics Trace
DDL
DDF
TRIGGERS
CPU TIMES
BUFFER POOLS
UDFS
LOGGING
LOCKS
OPEN/CLOSE
EDM POOL
DCL
DML
DB2 Statistics Trace
8
Statistics Trace Classes

• Class 1 System Services and Database Statistics
• Class 3 Deadlocks and Timeouts
• Class 4 Exception Conditions
• Class 5 Data Sharing
• Class 6 DBM1 Storage Statistics
• Class 8 Dataset I/O Statistics

Statistics trace written at user-defined interval
9
Audit Trace

• Collects information related to DB2 security
  controls
• Various trace classes for
• Authorization Errors
• Explicit Grants and Revokes
• Authid Changes
• Utilities
• Actions on Audited Tables

10
Performance Trace

• Gives you the down and dirty details
• Performance trace classes include
• Specific SQL Events
• BP Reads and Writes
• Summary and Detail Lock Information
• Sort Information
• Normally used to get detailed information about a
  specific DB2 event

Performance traces can be expensive use
cautiously!
11
Monitor Trace

• Collects information for use by online monitor
• Can include information from various other traces
  as well as information from DB2 control blocks
• Online monitors automatically start these traces
  to allow the product to get the necessary
  information
• Output destination is OPx buffer

12
Starting Traces

• DB2 Startup
• Most shops automatically start Accounting and
  Statistics
• Usually have SMF as destination
• -START TRACE command

Constrain by Plan Class AuthID IFCID
Location
13
Managing Traces

• -DISPLAY TRACE command
• Shows traces currently running
• Default shows all traces
• Can qualify display by type and/or constraint
• -STOP TRACE Command
• Like START DISPLAY, can qualify by
  type/constraint
• If multiple traces active, STOP must relate to
  START
• -START TRACE (PERFM) CLASS(1,2)
• -START TRACE (PERFM) CLASS(1)
• -STOP TRACE (PERFM) CLASS(1,2) only stops
  1st trace

14
Viewing Trace Information

• Performance Monitors
• No built-in viewer within DB2 base product
• Can write your own program(s)
• Can purchase IBM or 3rd-party Performance Monitor
• Online Monitoring
• Realtime monitoring of threads and system
  activity
• Historical component
• Batch Monitoring
• Flexible reporting summary, grouping, trending

15
Performance Monitor Overview

• Performance monitors provide a wealth of
  information
• Accounting trace and Statistics trace information
• Various DB2 control blocks
• Can start other traces directly through the
  monitor
• Allow you to qualify trace and set duration of
  trace
• View trace results online
• Data may not be available to batch reporting
• Can view additional DB2 information
• ZPARMS, History, Exceptions, Explain SQL,
  CICS/MVS

16
Online Viewing vs. Batch Reporting

• Online
• Real-time viewing of whats happening now
• Needed for on-the-spot troubleshooting or
  diagnosis
• Use historical component to view recent
  information
• Batch
• Show information after the fact
• Allows for summarization and grouping of
  information
• Some report writers are very flexible

Online viewing is real-time, but lots of bouncing
around between screens.
Batch reporting shows lots of information, but
access to data is delayed.
Samples in this presentation will use various
online and batch displays from different PMs
17
Viewing Thread Details - Online
18
Viewing Thread Details - Batch
---- IDENTIFICATION ------------------------------
--------------------------------------------------
------------------------- ACCT TSTAMP
01/26/2006 140730.2378 PRIMAUTH G2S9CICX
CORR ID ENTRTU620003 NETWORK TU0
CICS NET TU0 BEGIN TIME
01/26/2006 140725.9251 ORIGAUTH G2S9CICX
CONNECT G2PRDA1 LUNAME BSYSDBA2
CICS LU SIMLU651 END TIME
01/26/2006 140730.2375 PLANNAME PROD WLM
CLS ........ INSTANCE BE458AE1A0FD CICS
TKN 458D03FF4991 LOCATION DBA0
REC TYPE CHLDTSK 0
SEQ NUM 0001 ACCT FLG 003F



APPLICATION DB2 TIMES PCT IFI/DATA CAPT
PCT SUSPENSIONS PCT
TIMES/EVENTS (CLASS 1)
(CLASS 2) INDB2 (CLASS 5) INDB2 (CLASS 3)
EVENTS ELAPSED TIME INDB2 HIGHLIGHTS
------------ ------------ ------------
----- ------------- ----- ------------ ------
------------ ----- -------------------------
ELAPSED TIME 4.312411 3.962497
N/A LOCK/LATCH 0 0.000000
0.0 NORMAL RESIGNON UDF
0.000000 0.000000 N/A
SYNC I/O 199 0.907316 22.8 THD
TYPE ALLIED/NOT DIST STORED PRC
0.000000 0.000000 N/A
OTHER READ 3 0.048884 1.2 AVG IO
TIME(SEC) 0.0045 TRIGGERS N/A
0.000000 N/A OTHER WRITE
0 0.000000 0.0 INDB2 TIME OF TOT
91.9 TRIG-ENCLV N/A 0.000000
N/A SERVICE TSK 2
0.006452 0.2 COMMIT 2
UDF WT TCB 0.000000 ( 0 EVENTS)
QUIESCE LOG 0 0.000000
0.0 ABORT 0 WAIT S.P.
0.000000 ( 0 EVENTS)
ARCHIVE RD 0 0.000000 0.0 MAX LOB
STORAGE (KB) 0 CPU TIME 0.053371
0.045657 1.2 N/A DRAIN LOCK
6 0.200641 5.0 LOG RECORDS WRITTEN
0 TCB TIME 0.053371 0.045657 1.2
CLAIM RLSE 0
0.000000 0.0
TCB-STPROC 0.000000 0.000000 0.0
N/A PAGE LATCH 0 0.000000
0.0 RESOURCE LIMIT FACILITY TCB-UDF
0.000000 0.000000 0.0 N/A
NOTIFY MSGS. 0 0.000000 0.0
------------------------- TRIGGERS
N/A 0.000000 0.0 N/A
GLOBAL CONT. 1 0.000946 0.0 NOT
ACTIVE TRIG-ENCLV
N/A 0.000000 0.0 N/A LOG
WRITE IO 0 0.000000 0.0 TABLE ID
.. DCAPT DESCRB N/A
N/A OPN/CLS/HSM
10 2.715023 68.4 SRVCE UNITS 0
LOG EXTRACT N/A N/A
SYSLGRNG UPD 6
0.024936 0.6 CPU SECONDS 0.000
OTHER DB2 N/A 0.003315 0.0
N/A DATA MGR SRV 0 0.000000
0.0 MAX CPU SEC 0.000

OTHER EX-U-S 4 0.009321 0.2
DB2 ENTRY/EXIT EVENTS
786 N/A FORCE-AT-CMT
0 0.000000 0.0
STORPROC ENTRY/EXIT EVENTS 0
N/A ASYNC IXL RQ 0
0.000000 0.0 UDF
ENTRY/EXIT EVENTS 0
N/A GBL CHLD L-LK 0 0.000000 0.0

GBL
OTHR L-LK 0 0.000000 0.0
SQL DML TOTAL SQL DCL
TOTAL SQL DDL CREATE DROP ALTR GBL PSET P-LK
0 0.000000 0.0 DRAIN/CLAIM TOTAL
-------- -------- ---------- -------
---------- ------ ---- ---- GBL PAGE P-LK 0
0.000000 0.0 ----------------- -------
SELECT 35 LOCK TABLE 0 TABLE
0 0 0 GBL OTHR P-LK 0 0.000000
0.0 DRAIN REQUESTS 0 INSERT
18 GRANT 0 INDEX 0 0 0
DRAIN
FAILED 0 UPDATE 0 REVOKE
0 TABLESPACE 0 0 0
CLAIM REQUESTS
173 DELETE 0 SET SQLID 0
STOGROUP 0 0 0
CLAIM FAILED 0
SET H VAR 1 DATABASE
0 0 0
DESCRIBE 0
SET DEGREE 0 SYNONYM 0 0
LOCK ACTIVITY TOTAL GLOBAL
LOCKING TOTAL DESC TBL 0 CONNECT 1
0 VIEW 0 0
---------------- ------- -----------------
------- PREPARE 0 CONNECT 2 0
ALIAS 0 0
TIMEOUTS 0 LOCK REQUESTS
24 OPEN 44 RELEASE 0
PACKAGE 0
DEADLOCKS 0 UNLOCK REQUESTS
0 CLOSE 44 SET CONNEC 0 GBL TMP
TBL 0 ESCALATION
(SHR) 0 CHANGE REQUESTS 4 FETCH
247 SET RULES 0 AUX TABLE 0
ESCALATION (EXC) 0
LOCK - XES 75
REOPT VAR 0 TRIGGER 0 0
MAX PAGE LOCKS 10 UNLOCK - XES
45 SQL OTHER TOTAL ASSOC LOC
0 FUNCTION 0 0 0
SUSPENDS (LOCK) 0 CHANGE - XES
4 ----------- ----- ALLOC CSR 0
PROCEDURE 0 0 0
SUSPENDS (LATCH) 0 CONTENTIONS-IRLM
0 INCR. BINDS 0 HOLD LOC 0
DISTINCT 0 0
SUSPENDS (OTHER) 0 -XES
0 COMMENT ON 0 FREE LOC 0
TOT SUSPENSIONS
0 -FALSE 7 LABEL ON
0 SET PATH 0 SAVEPOINTS
TOTAL LOCK REQUESTS 136
REQUESTS DENIED 0 RENAME TBL 0
SET PREC 0 ------------------ --------
UNLOCK REQUESTS 80 NOTIFY MSGS
SENT 0 DCL GBL TMP
0 NUMBER OF REQUESTS 0
QUERY REQUESTS 0
RELEASE
REQUESTS 0 CHANGE REQUESTS
0
ROLLBACK REQUESTS
0 OTHER REQUESTS 0
19
Application Tuning The Basics

• Accounting trace information is a good starting
  point
• Effective application tuning means you need to
• Understand what information is available
• Understand what that information means
• Use Performance traces to drill-down on specifics
• Commonly reviewed sections
• Class 1 Times vs. Class 2 Times
• Class 3 Times (Wait Times)
• Buffer Pool Activity
• SQL Counts
• Package Activity

20
Application Tuning Class 1 vs. Class 2

• Class 1 time Application time
• Tracks time from 1st SQL statement until end of
  thread
• Class 2 time In-DB2 time
• Tracks time only when DB2 is doing work

21
Application Tuning Class 1 vs. Class 2
APPLICATION DB2 TIMES PCT
TIMES/EVENTS (CLASS 1) (CLASS 2) INDB2
------------ ------------ ------------ -----
ELAPSED TIME 4.312411 3.962497
UDF 0.000000 0.000000
STORED PRC 0.000000 0.000000
TRIGGERS N/A 0.000000
TRIG-ENCLV N/A 0.000000
UDF WT TCB 0.000000 ( 0 EVENTS)
WAIT S.P. 0.000000 ( 0 EVENTS) CPU
TIME 0.053371 0.045657 1.2 TCB
TIME 0.053371 0.045657 1.2
TCB-STPROC 0.000000 0.000000 0.0
TCB-UDF 0.000000 0.000000 0.0
TRIGGERS N/A 0.000000 0.0
TRIG-ENCLV N/A 0.000000 0.0
DCAPT DESCRB N/A N/A
LOG EXTRACT N/A N/A
OTHER DB2 N/A 0.003315 0.0
If Class 1 Elapsed significantly greater than
Class 2 Elapsed, then non-DB2 activity should be
investigated.
Note that elapsed and CPU times are shown for
Stored Procedures, UDFs, and Triggers.
With DDF, this value could be high, as it
includes the the time the requesting thread
waited for resources from the server.
22
Application Tuning Class 3 Time


CLASS 3
SUSPENSIONS ELAPSED TIME EVENTS -------------
------- ------------ -------- LOCK/LATCH(DB2IRL
M) 24.827289 51 SYNCHRON. I/O
1.905238 547 DATABASE I/O
1.905238 547 LOG WRITE I/O
0.000000 0 OTHER READ I/O
2.652763 123 OTHER WRTE I/O
0.045746 1 SER.TASK SWTCH
7.417763 80 UPDATE COMMIT
0.000000 0 OPEN/CLOSE
6.838266 44 SYSLGRNG REC
0.035358 6 EXT/DEL/DEF
0.531816 20 OTHER SERVICE
0.012324 10 ARC.LOG(QUIES)
0.000000 0 ARC.LOG READ
0.000000 0 STOR.PRC SCHED
0.000000 0 UDF SCHEDULE
0.000000 0 DRAIN LOCK
0.000000 0 CLAIM RELEASE
0.000000 0 PAGE LATCH
0.000000 0 NOTIFY MSGS
0.000000 0 GLOBAL CONT.
0.000000 0 FORCE-AT-COMMIT
0.000000 0 ASYNCH IXL REQUESTS
0.000000 0 TOTAL CLASS 3
36.848800 802
Lock and Latch (not including group-level locking)
Database Synchronous ReadsCan calculate average
I/O response time
Asynchronous Reads (prefetch)
Data set Open, Close, or HSM Recall
Data set Extend, Delete, or Define
Drains and Claims
Global Lock Contention
23
Application Tuning Buffer Pools
Read Efficiency (aka Hit Ratio) Various PMs
calculate this field differently. Some us a
ratio (as shown above), some use a
percentage. Also, some PMs only use Sync Reads to
calculate the value, others use Sync Reads plus
Async Reads.
Prefetch Requests You can use these to determine
whether prefetch is happening, and also whether
that is a good thing or not. You can also
calculate the efficiency of prefetch by using the
Prefetch Pages Read.

• Buffer Updates
• Caused by
• SQL Insert, Update, Delete
• Merge Scan Join
• Internal Sort (workfiles)

Getpage Failures Only valid for queries
processing in parallel. Failure occurs when I/O
was in progress or page was not found in BP. If
that happens, requesting agent will issue sync
I/O to get the page.

• Immediate Writes
• May occasionally see gt 0
• Checkpoint processing
• Data set close

24
Application Tuning SQL
SQL DML TOTAL SQL DCL TOTAL SQL DDL
CREATE DROP ALTR -------- -------- ----------
------- ---------- ------ ---- ---- SELECT
12427 LOCK TABLE 0 TABLE 0 0
0 INSERT 15872 GRANT 0 INDEX
0 0 0 UPDATE 8222 REVOKE
0 TABLESPACE 0 0 0 DELETE
230 SET SQLID 0 STOGROUP 0 0
0 SET H VAR 0 DATABASE
0 0 0 DESCRIBE 91 SET DEGREE
0 SYNONYM 0 0 DESC TBL 0
CONNECT 1 0 VIEW 0 0
PREPARE 1132 CONNECT 2 0 ALIAS
0 0 OPEN 7222 RELEASE
0 PACKAGE 0 CLOSE 0
SET CONNEC 0 GBL TMP TBL 0
FETCH 7234 SET RULES 0 AUX TABLE
0 REOPT VAR
0 TRIGGER 0 0 SQL OTHER TOTAL
ASSOC LOC 0 FUNCTION 0 0
0 ----------- ----- ALLOC CSR 0 PROCEDURE
0 0 0 INCR. BINDS 0 HOLD LOC
0 DISTINCT 0 0 COMMENT ON 0
FREE LOC 0
LABEL ON 0 SET PATH 0 SAVEPOINTS
TOTAL RENAME TBL 0 SET PREC
0 ------------------ --------
DCL GBL TMP 0 NUMBER OF REQUESTS 7171
RELEASE
REQUESTS 5843
ROLLBACK REQUESTS 4
Lots ofSQL-related counts listed in this
section. The only way to know if these counts are
good or bad is to know what the application
should be doing.
Can calcFetches perCursor
Describes and Prepares Dynamic SQL
Incremental Binds can cause thread overhead
25
Application Tuning Package Activity

• Package activity information is similar to Class
  2 and Class 3, only at the package level.
• Cannot see individual SQL counts, only total.

Online monitors only show currently executing
package. Need to use online historical component
or batch report to see all packages.
26
Application Tuning Other Aids

• Commonly available sections
• Data sharing
• Locking
• Query parallelism
• Other traceable sections
• I/O by pageset
• Lock details
• Sorts
• SQL trace

27
Viewing System Details - Online
28
Viewing System Details - Batch
LOCATION DBA0 SUBSYS DBA1 GROUP
DSNDBA0 MBR DBA1



EDM POOL STATSTICS

LOCK MANAGER STATISTICS
-----------------------------------------------
-----------------------------------
---------------------------------
PAGES... PRIMARY DATASPACE
REQUESTS LOADS REQUESTS DEADLOCKS
4 TOTAL
75000 225000 DBD 35677287
0 0.0 TIMEOUTS
1 MIN FREE 68189
223823 CT 68533 1
0.0 LOCK ESCAL SHR 0
MAX DBD 1177 PT
147056210 7 0.0 LOCK
ESCAL EXC 0 MAX
SKCT 126 DYN CACHE
6300364 99725 1.6 SUSPENDS LOCK
2816 MAX CT
27
SUSPENDS LATCH
655708 MAX SKPT 1703
FULL POOL FAIL 0
SUSPENDS OTHER 39956
MAX PT 5012 FULL
D.S. FAIL 0 LOCK
CALLS 339854K MAX DYN
CACHE 0
UNLOCK CALLS
82288197

LOCK QUERIES 0
SQL STATISTICS

LOCK CHANGES 26791470
--------------------------------------------------
---------------------------- IRLM OTHER
CALLS 0 SELECTS
57436K LOCK TABLE 0
CREATE DROP ALTER CLAIM REQUESTS
214710K INSERTS 55792K
GRANTS 0 STO GROUP 0 0
0 CLAIM FAILURES 90
UPDATES 35286K REVOKES
0 DATABASE 0 0 0
DRAIN REQUESTS 17812
DELETES 1560882 SET SQL ID 49
TABLESPACE 0 0 0 DRAIN
FAILURES 326 PREPARES
6211270 SET HOST VR 8 TABLE
0 0 0
FETCHES 97764K
INCR BIND 0 INDEX 0 0
0 LIST PREFETCH STATISTICS
OPEN CSR 95632K COMMENT ON
0 SYNONYM 0 0
---------------------------------
CLOSE CSR 44283 LABEL ON 0 VIEW
0 0 SUCCESSFUL
3679793 DESCRIBES
62741 CONN TYPE 1 966 ALIAS
0 0 FAILED NO STORAGE
0 DESCR TBL 9 CONN
TYPE 2 42 PACKAGE 0
FAILED OVER LIMIT 2
RELEASE 0
GBL TMP TB 0
SIUD
150075K SET CONNECT 0 AUX TABLE
0 RID LIST STATISTICS
TOTAL DML 349789K
SET DEGREE 0 TRIGGER 0 0
---------------------------------
SET RULES
0 FUNCTION 0 0 0
RIDLIST RDS LIMIT 2
HOLD LOC 0 SQL CALL 1
PROCEDURE 0 0 0
RIDLIST DM LIMIT 0
FREE LOC 0 RENAME TBL 0
DISTINCT 0 0
RIDLIST MAX BLOCKS 98
SET PATH 0 ASSOC LOC 0
RIDLIST AVG
BLOCKS 9 SET PREC
0 ALLOC CSR 0 TOTAL DDL 0
RIDLIST NO STORAGE
0 DCL GBL TMP 0 TOTAL
DCL 1066 TOTAL SQL 349790K
RIDLIST PROC LIMIT 0


SERVICE
CONTROLLER STATISTICS
PARALLELISM
------------------------
--------------------------------------------------
---------- ---------------------------------
PLAN PACKAGE
AVERAGE OPEN DATASETS 12813
PARALLEL GROUPS EXECUTED 0
AUTOBIND ATTEMPTS 0 0 MAXIMUM
DATASETS CONCURRENTLY OPEN 13283
GROUPS EXECUTED AS PLANNED 0
AUTOBINDS 0 0 MAX DATASETS
OPEN, BUT NOT IN USE 2476 MAX DEGREE
OF PARALLEL IO 0 BIND ADD
SUBCMDS 0 0 MAX PAGESETS AVAILABLE
TO DRAIN 12726 GROUPS W/ REDUCED
DEGREE 0 BIND REPL SUBCMDS
0 0 DATASETS CLOSED BY DRAIN
0 GROUPS FAILED - CURSOR 0
TEST BINDS 0 N/A
DATASET OPENS BYPASSED DUE TO DRAIN 55469206
GROUPS FAILED - ESA SORT 0
BINDS 0 0 PAGESETS
CONVERTED TO READ ONLY 13183 GROUPS
FAILED - STORAGE/BP 0 REBIND
SUBCMDS 0 0 INVALID SELECT
PROCEDURES 0 GROUPS
FAILED - ENCLAVE 0 REBIND
ATTEMPTS 0 0
PLAN PACKAGE ROUTINE EXEC SINGLE DB2
COORDNO 0 REBINDS
0 0 AUTHORIZATION CHECKS 16178K
16036K N/A EXEC SINGLE DB2 ISORR/RS
0 FREE SUBCOMMANDS 0 0
SUCCESSFUL AUTH CHECKS 16178K N/A N/A
INTENDED GROUPS 0
FREE ATTEMPTS 0 0 CACHE AUTH
CHECKS 34346 16034K 1 MEMBERS
BYPASSED BP SHORT 0 FREES
0 0 PUBLIC AUTH CHECKS
60 617 1 ACCESS PATH REDONE
CONFIG 0 ALLOCATE ATTEMPTS
36153 16317K OVERWROTE AUTH IN CACHE N/A
0 0 ACCESS PATH REDONE BP
0 ALLOCATIONS 36153 16316K
OVERWROTE ENTRY IN CACHE N/A 0 0
EXEC SINGLE DB2 DCL TMP TBL 0
INVALID RSRCE IDS 0 N/A ADD CACHE FAIL
N/A N/A 0

CACHE NOT USED
N/A N/A 0 LOB STORAGE


---------------------------------

MAX LOB
STORAGE (MB) 0
29
System Tuning The Basics

• Statistics trace information is a good starting
  point
• Effective system tuning means you need to
• Understand what information is available and what
  it means
• Filter the amount of data
• Analyze information over time
• Best done using batch reports use online for
  fire-fighting
• Statistics trace contains information about

• Buffer Pool Activity
• Data Sharing Locking
• DB2 Commands
• DRDA

• EDM Pool Activity
• GBP Activity
• Latch and Lock Activity
• Log Activity

• Data Set Open/Close
• Storage and Memory
• SQL
• and much more

30
System Tuning Buffer Pools
BUFFER POOL STATISTICS FOR BP8

GENERAL
READ OPERATIONS
WRITE OPERATIONS
-------------------------------
------ -------------------------------------
------------------------------------
AVG NON-STEALABLE BUFFERS 5
GETPAGE REQUESTS 13308117 BUFFER
UPDATES 700
BUFFER POOL FULL 0
SEQUENTIAL ACCESS GETPAGES 2676920 PAGES
WRITTEN 269
RANDOM
ACCESS GETPAGES 10631197 BUFFER UPD/PAGES
WRITTEN 2.6 DATASETS
OPENED 0


SYNCHRONOUS READS
3254 ASYNCHRONOUS WRITES 92
AVG BUFS ALLOCATED - VPOOL 3000
SEQ ACCESS SYNCHRONOUS READS 3141
SYNCHRONOUS WRITES 93
AVG BUFS ALLOCATED - HPOOL 0
RANDOM ACCESS SYNC READS 113 PAGES
WRITTEN/WRITE I/O 1.5
AVG HPOOL BUFFERS BACKED 0
GETPAGES/SYNCHRONOUS READS 4089.8 PAGE-INS
REQUIRED - WRT I/O 0
PAGE-INS
REQUIRED - READ I/O 0
MIGRATED
DATASETS OPENED 0
AVG DEFERRED WR THRESHOLD
0 DFHSM RECALL TIMEOUTS
0 ASYNCHRONOUS RDS
5423 AVG DS DEFERED WR THRESHOLD 0

DATA
MANAGEMENT THRESHOLD 0
VPOOL SIZE CHANGES 0
SEQUENTIAL PREFETCH REQUESTS 28011 WRITE
ENGINE NOT AVAILABLE 0
HPOOL SIZE CHANGES 0
SEQUENTIAL PREFETCH READS 5220

EXPANSION FAILURES 0
SEQUENTIAL PREFETCH PAGES 157112
SYNCHRONOUS HPOOL WRITES 0

SEQUENTIAL PREFETCH PAGES/RD 30.1 ASYNC
HPOOL WRTS W/OUT ADMF 0
MAX CONCUR PREF I/O STREAMS 0
HPOOL WRT W/OUT
ADMF FAILED 0 PARALLEL
QUERY REQUESTS 0 LIST PREFETCH
REQUESTS 0 ASYNC HPOOL WRTS WITH
ADMF 0 PARALLEL I/O
DEGREES REDUCED 0 LIST PREFETCH READS
0 HPOOL WRT WITH ADMF FAILED
0 REDUCED DEGREE PARALLEL I/OS
0 LIST PREFETCH PAGES 0

AVERAGE DEGREE REDUCTION 0.0 LIST
PREFETCH PAGES/READ 0.0 SORT/MERGE
PREF
QUANTITY REDUCED TO 1/2 0
-------------------------
----------- PREF QUANTITY
REDUCED TO 1/4 0 DYNAMIC PREFETCH
REQUESTS 4478 SORT MERGE PASSES
REQUESTED 0
DYNAMIC PREFETCH READS
203 INEFFICIENT PASSES-LOW BUF
0
DYNAMIC PREFETCH PAGES 5988
WKFLS REQUESTED ALL PASSES 0

DYNAMIC PREFETCH PAGES/RD 29.5 MAX
WORKFILES ALLOCATED 0

WRKFL REQ
DENIED-LOW BUFFER 0
PREFETCH
DISABLED-NO BUFFER 0 WRKFL NOT
CREATED-NO BUFFER 0
PREFETCH
DISABLED-NO RD ENG 0 WORKFILE PREFETCH
ABORTED 0

DESTRUCTIVE READ PAGES
0
MAX PARALLEL I/O PRF STREAMS 0
PG WRT BYPASSED-DESTRUCT RD 0

CONDITIONAL GETPAGE FAILURES 0

31
System Tuning Other Aids

• System tuning cant be done in a vacuum
• Need input and consultation from other areas
• MVS, CICS/IMS, Storage/DASD, Capacity Planning
• Use other tools to help
• RMF, other non-DB2 monitors
• Need to understand the applications
• Subsystem needs to be tuned based on how the apps
  run
• Having historical data is key
• Need to be able to track and trend
• Problems, growth, new changes

32
Monitoring Strategies

• Set exceptions in online monitors
• Identify criteria and have monitor flag
  exceptions
• Send email/pages for exceptions in critical
  systems
• Run batch reports on a predetermined basis
• Run summary reports on daily basis
• Run summary/detailed reports weekly or monthly
• Extract data and load into DB2 tables
• Allows for very flexible reporting
• Great for trending and forecasting

33
Tuning Strategy

• Start wide and drill down
• Run summary reports to identify who had problems
• Run detailed reports for specific timeframes
• Use performance monitor traces
• Need to understand how to turn on these traces
• Get inget outthen analyze the data
• Critical systems need involvement from all
• Applications, systems, DBAs, capacity, DASD, etc.

34
Summary

• Traces give us the data to diagnose and solve
  problems
• Performance monitors bundle that data in an easy
  to read format
• Traces and performance monitors should be your
  new best friend