O'Reilly logo

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Subsystem and Transaction Monitoring and Tuning with DB2 11 for z/OS

Subsystem and Transaction Monitoring and Tuning with DB2 11 for z/OS

by Yasuhiro Ohmori, Cathy Drummond, Adrian Burke, Felipe Bortoletto, Paolo Bruni
Publisher: IBM Redbooks
Release Date: February 2014
ISBN: 0738439126
View table of contents
Start reading

Book Description

This IBM® Redbooks® publication discusses in detail the facilities of DB2® for z/OS®, which allow complete monitoring of a DB2 environment. It focuses on the use of the DB2 instrumentation facility component (IFC) to provide monitoring of DB2 data and events and includes suggestions for related tuning.

We discuss the collection of statistics for the verification of performance of the various components of the DB2 system and accounting for tracking the behavior of the applications.

We have intentionally omitted considerations for query optimization; they are worth a separate document.

Use this book to activate the right traces to help you monitor the performance of your DB2 system and to tune the various aspects of subsystem and application performance.

Table of Contents

  1. Front cover
  2. Examples
  3. Figures
  4. Tables
  5. Notices
    1. Trademarks
  6. Preface
    1. Authors
    2. Now you can become a published author, too!
    3. Comments welcome
    4. Stay connected to IBM Redbooks
  7. Part 1 Introduction to DB2 monitoring
    1. Chapter 1. Introduction to monitoring and tuning
      1. 1.1 Overview of tuning: The four steps
        1. 1.1.1 Identifying
        2. 1.1.2 Diagnosing
        3. 1.1.3 Solving
        4. 1.1.4 Preventing
      2. 1.2 DB2 Performance Solution Pack
    2. Chapter 2. DB2 traces
      1. 2.1 Trace types
      2. 2.2 Trace destinations
      3. 2.3 Managing DB2 traces
        1. 2.3.1 Start trace
        2. 2.3.2 Display trace
        3. 2.3.3 Modify trace
        4. 2.3.4 Stop trace
        5. 2.3.5 Loading IFCID field descriptions into a table
        6. 2.3.6 Reporting performance data
      4. 2.4 Compressing your trace records
        1. 2.4.1 Software compression
        2. 2.4.2 Hardware compression
    3. Chapter 3. System z related information
      1. 3.1 Workload Manager
      2. 3.2 The DB2 subsystem
        1. 3.2.1 z/OS factors helping DB2
        2. 3.2.2 DB2 subsystem consequences
      3. 3.3 Thread level
        1. 3.3.1 In DB2
        2. 3.3.2 Regarding WLM resource group capping
        3. 3.3.3 Transaction level goal adjustment
      4. 3.4 General WLM best practices
      5. 3.5 Looking at RMF data
        1. 3.5.1 Overview records
  8. Part 2 Subsystem monitoring
    1. Chapter 4. System address space CPU time
      1. 4.1 What contributes to address space CPU time
      2. 4.2 How to look at the numbers
    2. Chapter 5. EDM pools
      1. 5.1 EDM pools overview
      2. 5.2 Plan and package storage and skeleton pool
      3. 5.3 DBD pool
      4. 5.4 DB2 statement caching
        1. 5.4.1 Behavior of executing dynamic SQL without statement caching
        2. 5.4.2 Global dynamic statement caching
        3. 5.4.3 Use of local dynamic statement cache
        4. 5.4.4 Dynamic SQL literal replacement
        5. 5.4.5 EXPLAIN MODE special register
        6. 5.4.6 Capturing data from statement cache
        7. 5.4.7 Monitoring statement cache
    3. Chapter 6. Data set open and close
      1. 6.1 Open and close data sets
        1. 6.1.1 Pseudo close mechanism
        2. 6.1.2 CLOSE specification for the table spaces
      2. 6.2 Controlling number of open data sets
        1. 6.2.1 Monitoring open and close activity
        2. 6.2.2 Managing and collecting additional information for open and close data sets
    4. Chapter 7. Log activity
      1. 7.1 Log activity
      2. 7.2 Log write activity
      3. 7.3 Log read activity
      4. 7.4 Log I/O tuning possibilities
    5. Chapter 8. IRLM, locking, and latching
      1. 8.1 DB2 and the internal resource lock manager (IRLM)
        1. 8.1.1 IRLM startup procedure options
        2. 8.1.2 z/OS commands on IRLM
        3. 8.1.3 Accounting and tracing IRLM
      2. 8.2 DSNZPARMs related to locking
      3. 8.3 Lock avoidance
        1. 8.3.1 IRLM latch contention
      4. 8.4 Data sharing locking
        1. 8.4.1 Global contentions
        2. 8.4.2 P-lock contention negotiations
        3. 8.4.3 Data sharing lock tuning
      5. 8.5 Internal DB2 latch contention
    6. Chapter 9. The zIIP engine and DB2
      1. 9.1 What a zIIP is
        1. 9.1.1 What drives customers to purchase zIIPs
      2. 9.2 What work is zIIP eligible
        1. 9.2.1 zIIP eligibility from DB2 V8 until now
        2. 9.2.2 System agents
        3. 9.2.3 Native stored procedures
        4. 9.2.4 zAAP on zIIP
      3. 9.3 How many zIIPs are enough
        1. 9.3.1 Enough zIIP for the business
        2. 9.3.2 Enough zIIP for the workload
      4. 9.4 Avoiding self-imposed bottlenecks
        1. 9.4.1 IEAOPTxx parameters
        2. 9.4.2 zIIP capacity in DB2 10 and DB2 11
        3. 9.4.3 Conservative zIIP utilization
      5. 9.5 Getting more zIIP eligibility
    7. Chapter 10. Buffer pools and group buffer pools
      1. 10.1 Local buffer pools
        1. 10.1.1 Basic placement and settings
        2. 10.1.2 Thresholds
        3. 10.1.3 Tuning
        4. 10.1.4 DB2 11
      2. 10.2 Group buffer pools
        1. 10.2.1 Best practices
        2. 10.2.2 What to look out for
        3. 10.2.3 Rough sizings
        4. 10.2.4 Group buffer pools and DB2 11
    8. Chapter 11. DDF activity
      1. 11.1 DDF and distributed environments
        1. 11.1.1 Database access thread
        2. 11.1.2 Modes of DBATs and inactive connections
        3. 11.1.3 High Performance DBATs
        4. 11.1.4 Sysplex workload balancing and connection queue redirect
      2. 11.2 Monitoring DDF activity
        1. 11.2.1 DDF activity report
        2. 11.2.2 Statistics Global DDF Activity
        3. 11.2.3 Statistics DRDA remote locations
        4. 11.2.4 Monitoring using system profiling
        5. 11.2.5 Client information and workload management
    9. Chapter 12. Workfiles, RID, and sort pools
      1. 12.1 The workfile evolution
        1. 12.1.1 Tuning the workfiles
      2. 12.2 Sorting
        1. 12.2.1 Buffer pool settings
        2. 12.2.2 DB2 11
      3. 12.3 RID list processing
    10. Chapter 13. Virtual and real storage
      1. 13.1 Storage concerns
        1. 13.1.1 Real storage concerns
        2. 13.1.2 Virtual storage concerns
      2. 13.2 Monitoring real storage usage
        1. 13.2.1 Maintaining control over the system use of real storage
        2. 13.2.2 64-bit storage
        3. 13.2.3 LFAREA
        4. 13.2.4 New in DB2 11
      3. 13.3 Monitoring virtual storage usage
  9. Part 3 Transaction monitoring
    1. Chapter 14. Accounting trace overview
      1. 14.1 Response time factors
      2. 14.2 Response time scope
      3. 14.3 DB2 accounting data
        1. 14.3.1 When DB2 accounting data is written
        2. 14.3.2 Accounting class 1 data
        3. 14.3.3 Accounting class 2 data
        4. 14.3.4 Accounting class 3 data
        5. 14.3.5 Accounting class 1,2,3
    2. Chapter 15. Analyzing accounting data: CPU and suspension time
      1. 15.1 Top-down analysis
        1. 15.1.1 Analyzing thread activity time
      2. 15.2 Time outside of DB2 versus time in DB2
        1. 15.2.1 Time spent outside of DB2 is bigger than time in-DB2
        2. 15.2.2 Time spent inside of DB2 is bigger than time outside of DB2
      3. 15.3 In-DB2 CPU time versus elapsed time
        1. 15.3.1 CPU time
        2. 15.3.2 Suspension time
        3. 15.3.3 What is left: NOT ACCOUNT time
    3. Chapter 16. I/O suspensions
      1. 16.1 Synchronous I/O suspensions: Large number
        1. 16.1.1 Synchronous database I/O suspensions
        2. 16.1.2 Tuning the number of synchronous I/O suspensions
        3. 16.1.3 Tuning I/O wait time
      2. 16.2 Log read suspensions
        1. 16.2.1 Archive Log Mode (Quiesce) suspensions
      3. 16.3 Synchronous log write I/O suspensions
      4. 16.4 Suspensions for reads by other agents
      5. 16.5 Suspensions for writes by other agents
    4. Chapter 17. Locking, latching, and buffer pool accounting counters
      1. 17.1 Lock and latch
      2. 17.2 Locks
        1. 17.2.1 Locking information in accounting at plan level
        2. 17.2.2 Data sharing locking
        3. 17.2.3 Global lock suspensions
      3. 17.3 Latches
        1. 17.3.1 Latch suspensions
        2. 17.3.2 Page latch suspensions
      4. 17.4 Buffer pool information in accounting at plan level
      5. 17.5 Group buffer pool information
    5. Chapter 18. Service task suspension
      1. 18.1 Synchronous execution unit switch suspensions
      2. 18.2 Large suspensions for synchronous EU switches
        1. 18.2.1 Service task wait: Commit related timers
        2. 18.2.2 Data set open/close
        3. 18.2.3 SYSLGRNG REC
        4. 18.2.4 Data set Extend/Define/Delete
        5. 18.2.5 OTHER SERVICE tasks
      3. 18.3 How to find which service task is invoked
    6. Chapter 19. Stored procedures, user defined functions, and triggers
      1. 19.1 Stored procedures
        1. 19.1.1 Nested activity for WITH RETURN processing
      2. 19.2 User defined functions
      3. 19.3 Triggers
      4. 19.4 Nested activity accounting
      5. 19.5 IFCIDs for nested activity
    7. Chapter 20. DRDA, parallelism, and statement cache
      1. 20.1 Accounting for DDF work
      2. 20.2 Setting extensions to client information
      3. 20.3 Accounting for parallel tasks
      4. 20.4 Capturing performance data with the statement cache
      5. 20.5 Performance traces for SQL tuning
  10. Part 4 Appendixes
    1. Appendix A. Production modeling
      1. A.1 Functional requirements
      2. A.2 Simulating production
      3. A.3 Data sharing members on dissimilar hardware
      4. A.4 Generating input data for spreadsheets
    2. Appendix B. IBM OMEGAMON XE for DB2 performance database
      1. B.1 Introduction
      2. B.2 Creating the performance database
      3. B.3 Extracting, transforming, and loading accounting and statistics data
      4. B.4 Sample query for application profiling
      5. B.5 Using the UDF for application profiling
      6. B.6 Additional information
      7. B.7 MEMU2 versus Performance Database fields
  11. Related publications
    1. IBM Redbooks publications
    2. Other publications
    3. Online resources
    4. Help from IBM
  12. Back cover