IBM DB2 performance expert for z/OS version 2 / [Paolo Bruni ... et al.].

Format:

Book

Contributor:

Bruni, Paolo.

International Business Machines Corporation. International Technical Support Organization.

Series:

IBM redbooks.

IBM redbooks

Language:

English

Subjects (All):

Database management.

IBM Database 2.

z/OS.

Physical Description:

xxviii, 432 p. : ill.

Edition:

2nd ed.

Place of Publication:

[S.l.] : IBM, International Technical Support Organization, c2004.

Language Note:

English

Contents:

Front cover

Contents

Figures

Tables

Examples

Notices

Trademarks

Preface

The team that wrote this redbook

Become a published author

Comments welcome

Summary of changes

June 2004, Second Edition

Chapter 1. Introducing IBM DB2 Performance Expert

1.1 Overview

1.2 DB2 Performance Expert main features

1.3 DB2 Performance Expert general structure

1.4 DB2 Performance Expert for z/OS structure

1.4.1 DB2 PE Performance Monitor

1.4.2 DB2 PE reporting

1.4.3 Interfacing with the outside world

1.4.4 Exception processing

1.4.5 History information

1.4.6 DB2 PE Performance Database

1.4.7 DB2 PE Performance Warehouse

1.4.8 DB2 Connect monitoring

1.4.9 DB2 PE Buffer Pool Analysis

1.5 DB2 Performance Expert for Multiplatforms

Chapter 2. Installing the components

2.1 Installing DB2 PE

2.2 Customizing the DB2 PE setup

2.3 Installing the DB2 PE workstation client (GUI interface)

2.3.1 Download and install the workstation code (FPEKWSNT)

2.3.2 Set up TCP/IP on your workstation

2.4 Customizing the Performance Warehouse

2.4.1 Setting parameters

2.4.2 Storage groups and buffer pool definition

2.4.3 ODBC Universal Driver setup

2.4.4 Performance Warehouse database objects

2.5 Installing the Performance Warehouse on the workstation

2.5.1 Software requirements

2.5.2 Configuring the DB2 Performance Expert Client for the PWH

2.5.3 Using the Client Configuration Assistant

2.5.4 Catalog the database manually

2.6 Installing the PE Agent on DB2 Connect Gateway

2.7 Interfacing with Visual Explain

Chapter 3. Defining a DB2 target system

3.1 Customizing a new DB2 subsystem

3.1.1 Add a new DB2 subsystem

3.1.2 Add a new DB2 Connect gateway

3.1.3 Configure your workstation client

3.1.4 Export a system configuration.

3.1.5 Import DB2 systems

3.1.6 Folder functionality

Chapter 4. Snapshot monitoring

4.1 Overview

4.1.1 Class 1 timer

4.1.2 Class 2 timer

4.1.3 Class 3 timer

4.2 Where is my performance issue?

4.3 Using the ISPF interface

4.3.1 The DB2 PE Administrator Dialog

4.4 Using the GUI interface

4.4.1 Starting DB2 PE

4.4.2 DB2 commands

4.4.3 Thread Summary

4.4.4 Statistics details

4.4.5 Threads in lock conflicts

4.4.6 Locking conflicts

4.4.7 System parameters

4.4.8 Trace activation

4.5 History monitoring

4.5.1 Using DB2 PE workstation monitor

4.5.2 Using DB2 PE ISPF monitor

4.5.3 Summary of historical monitoring

Chapter 5. Checking DB2's health

5.1 Using the System Health window

5.1.1 Extended design

5.1.2 Classic design

5.2 Managing data groups

5.2.1 Creating a data group

5.2.2 Other data group options

5.2.3 Creating a data view

5.2.4 Managing a data view

5.2.5 Conclusion for managing data groups and system health

5.3 Performance Warehouse

5.4 Performance Database

5.4.1 The Performance Database tables

5.4.2 Recommendations

5.5 Buffer pool analysis

5.6 Exception processing

5.6.1 Manage exception events and periodic exceptions from the workstation

5.6.2 Define a periodic exception using the ISPF interface

5.6.3 Start exception processing

5.6.4 Examine exception messages

5.6.5 Examine the periodic exceptions list

5.6.6 Stop the exception processing

5.6.7 Exception processing exit

5.6.8 Batch exception reporting

5.7 Continuous data collection

5.7.1 Starting the trace

5.7.2 Trace output destinations

5.8 Batch component - Analysis

5.8.1 Batch report

5.8.2 DB2 PE report commands

5.8.3 Accounting report set

5.8.4 Statistics report set

5.8.5 I/O activity report set

5.8.6 Locking report set.

5.8.7 SQL activity report set

5.8.8 Utility activity report set

5.8.9 Record trace report set

5.8.10 Audit report set

5.9 Explain processing

5.10 DB2 storage statistics

5.10.1 DBM1 address space

5.10.2 IFCIDs 0225 and 0217

5.10.3 Storage statistics report

Chapter 6. Performance Warehouse

6.1 Overview

6.2 Performance Warehouse in a typical environment

6.3 Performance Warehouse basics

6.3.1 DB2 objects

6.3.2 Processes

6.3.3 Rules-of-thumb

6.3.4 SQL queries

6.4 Working from the workstation

6.4.1 Starting the Performance Warehouse

6.4.2 Process definition

6.4.3 Run a process, track its execution and view the results

6.4.4 Collecting and loading trace data

6.4.5 Deleting data in PWH

6.4.6 Working with ROTs

6.4.7 Queries

6.5 Analyzing performance data using ROTs

6.6 Using queries and Expert Analysis

6.6.1 Using accounting queries

6.6.2 Expert Analysis

Chapter 7. Optimizing buffer pool resources

7.1 Overview of Buffer Pool Analysis

7.2 Components of Buffer Pool Analysis

7.3 Benefits of using Buffer Pool Analysis

7.4 Fundamentals of buffer pool tuning

7.5 Understanding the GETPAGE behavior

7.6 Buffer pool hit ratio

7.7 Number of I/Os per second

7.8 Understanding the thresholds

7.8.1 Fixed thresholds

7.8.2 Variable thresholds

7.9 Grouping the objects

7.10 Long term page fix

7.11 Trace data collection with Buffer Pool Analysis

7.12 Buffer Pool Analysis reporting

7.12.1 Creating reports and FILE data sets

7.12.2 Buffer pool activity reports

7.12.3 Buffer Pool Analysis workstation reporting

7.13 Long term analysis

7.14 Buffer pool expert analysis - Sizing and object placement

7.14.1 Determine storage available for buffer pools

7.14.2 Define object placement patterns.

7.14.3 Adjust object placement and buffer pool settings

7.14.4 Perform buffer pool and page set changes

7.15 Buffer pool simulation

7.15.1 Setup of buffer pool simulation

7.15.2 Interpretation of simulation reports

7.16 Typical buffer pool scenarios

7.16.1 Initial object assignment and buffer pool sizing

7.16.2 Permanent buffer pool monitoring

7.16.3 Peak buffer pool monitoring

7.16.4 Addition of new application

7.16.5 Disruptive checkpoint processing

7.16.6 Performance of sort intensive SQL queries not satisfactory

7.17 Summary

Chapter 8. DB2 Connect monitoring

8.1 Overview of DB2 Connect monitoring

8.2 Essential components

8.3 Command line interface - e2e

8.4 Statistics Details

8.4.1 Connection pooling

8.4.2 Connection concentrator

8.4.3 Number of busy connections

8.4.4 CPU and working set size

8.5 Monitoring from Thread Details window

8.6 Summary

Chapter 9. Data sharing considerations

9.1 Installation considerations

9.1.1 Defining PE Servers with data sharing

9.1.2 Define a PE Server for all members?

9.1.3 Recommendation

9.2 Workstation online monitor

9.2.1 Thread summary

9.2.2 Thread details

9.2.3 Statistics details

9.2.4 Locking conflicts

9.3 ISPF monitor

9.4 Exception processing

9.5 Collect report data

9.6 Data views

9.7 Buffer pool analysis

9.8 Batch reports

9.8.1 Statistics report

9.8.2 Accounting report

Chapter 10. Enhancements for DB2 for z/OS V8 support

10.1 Supporting DB2 UDB for z/OS Version 8

10.2 General batch changes

10.3 Trace record changes

10.3.1 Accounting trace changes

10.3.2 Statistics trace changes

10.4 Longer names

10.5 64 bit and UNICODE

10.6 Longer SQL statements

10.6.1 IFCID 0063 - SQL statement to be parsed

10.6.2 IFCID 0350 - SQL statement.

10.7 Statement number precision

10.8 IFCID 0342 - Agent level workfile tracing

Abbreviations and acronyms

Related publications

IBM Redbooks

Other publications

Online resources

How to get IBM Redbooks

Help from IBM

Index

Back cover.

Notes:

Bibliographic Level Mode of Issuance: Monograph

Includes bibliographical references and index.

OCLC:

137342235