DB2 Server for VSE & VM: Performance Tuning Handbook

Table of Contents

About This Manual

  • Who Should Use This Manual
  • Organization
  • Prerequisite Reading
  • How to Send Your Comments
  • Syntax Notation Conventions
  • SQL Reserved Words
  • Conventions Used for Highlighting Examples

    • Summary of Changes

  • Summary of Changes for DB2 Version 7 Release 1
  • Enhancements, New Functions, and New Capabilities
  • TCP/IP Support for DB2 Server for VSE
  • DRDA RUOW Application Requester for VSE (Batch)
  • Stored Procedures Application Requester
  • Simplified DB2 Server for VSE Installation/Migration
  • New Code Page and Euro Symbol Code Page Support
  • Control Center for VM Enhancements
  • Control Center for VSE Enhancements
  • QMF for VSE & VM Optional Feature
  • QMF for Windows(R) Optional Feature
  • Reliability, Availability, and Serviceability Improvements
  • DBNAME Directory Restructuring
  • Migration Considerations
  • Library Enhancements

    • Chapter 1. Improving Performance

  • Elements of Performance
  • Tuning Guidelines
  • Performance Improvement Process
  • How Much Can a System be Tuned?
  • Workload
  • Performance Indicators
  • Establishing Performance Objectives
  • Response Time
  • Components of Response Time
  • Throughput
  • Availability
  • A Less Formal Approach
  • Monitoring Performance
  • Creating a Monitoring Plan
  • Monitoring Interval
  • Cost of Monitoring
  • Measurements
  • Tools
  • VM Tools
  • VSE Tools
  • CICS Tools
  • DB2 Server for VSE & VM Tools
  • Factors Affecting Performance
  • Resources
  • Processor
  • Storage
  • Overhead
  • Concurrency
  • Locking
  • Recovery
  • Choosing Between Tuning Trade-offs

    • Chapter 2. Measuring Performance

  • Understanding Performance Measurements
  • Relative Measurements
  • Load
  • Effective Use
  • Sampling Interval
  • Operating System Measurements
  • Processor (CPU) Load
  • Real and Virtual Storage Load
  • System Paging DASD Load
  • Machine or Partition DASD I/O Load
  • Individual Device Utilization
  • Translating Performance Measurements to Indicators
  • CP INDICATE USER and QUERY TIME Commands
  • CICS Monitoring (CICSPARS for VSE)
  • DB2 Server for VSE & VM Tools
  • Physical Data Locations
  • Disk Locations (VM)
  • Data Set Placement (VSE)
  • Initialization Parameters
  • CIRD Transaction (CICS)
  • COUNTER Operator Command
  • DB2 VM Data Spaces Support
  • SHOW Commands
  • Storage
  • Concurrency
  • VSE SHOW CONNECT
  • Locking
  • Database catalog
  • SYSTEM.SYSCATALOG
  • SYSTEM.SYSCOLUMNS
  • SYSTEM.SYSDBSPACES
  • SYSTEM.SYSINDEXES

    • Chapter 3. Managing Storage and Configuring the Operating System

  • Real and Virtual Storage
  • Virtual Addressing
  • Pages
  • Auxiliary Storage
  • Storage Queues
  • Address Space Size
  • Storage Above 16MB (31 Bit Addressing)
  • Saved Segments (VM Only)
  • Virtual Disk Support for VSE/ESA for Internal Dbspaces
  • Virtual Disk Support for VM/ESA for Internal Dbspaces
  • DASD Storage
  • In VSE
  • In VM
  • Mapping of Dbspaces to DASD
  • Logical To Physical Page Relationships
  • Storage Pools
  • Managing Storage Pool Space
  • Short on Storage Cushion
  • Types of Pages
  • Number of Header Pages
  • Proportion of Index to Data and Header Pages
  • Free Space in Data Pages
  • Free Space in Index Pages
  • Shadow Pages
  • Running out of Dbspace Pages
  • Data Clustering
  • Clustered Indexes
  • The Clustering Index
  • How Indexes Become Unclustered
  • Identifying Unclustered Indexes
  • Reorganizing Data
  • Reorganizing a Single Table
  • Reorganizing all the Tables in a Dbspace
  • Changing the Clustering Index without Dropping Indexes
  • Index Fragmentation
  • Reorganizing Fragmented Indexes
  • Invalid Indexes
  • Transient Indexes
  • Reorganizing an Invalid Index
  • DASD Balancing
  • Evenly Distributing Workload across Physical Volumes
  • Moving Dbextents
  • Reassigning Dbspaces
  • General Considerations
  • VM Specifics
  • Fair Share Scheduling
  • VSE Specifics
  • Dispatching Priority
  • Fast CCW Translation
  • Virtual Addressability Extension (VAE)
  • 31 Bit Addressing
  • Compile Partition Size
  • CICS Specifics
  • AMXT/MXT
  • ISQL
  • Transaction Name
  • Number of Concurrent Users
  • Temporary storage
  • Auxiliary versus Main
  • Guest Sharing with VSE under VM
  • Distributed Configuration Considerations
  • DB2 Server for non-DRDA Requestors can access:
  • DB2 Server for VM non-DRDA Servers can be accessed by:
  • DB2 Server for VM DRDA Requestors can access:
  • DB2 Server for VM DRDA Servers can be accessed by:
  • DB2 Server for VSE non-DRDA Requestors can access:
  • DB2 Server for VSE non-DRDA Servers can be accessed by:
  • DB2 Server for VSE DRDA Online (CICS) Requestors can access:
  • DB2 Server for VSE DRDA Batch Requestors can access:
  • DB2 Server for VSE DRDA Servers can be accessed by:
  • Performance Implications
  • Applications Planning
  • Fetch and Insert Blocking
  • Hold File
  • Local Copy
  • Stored Procedures

    • Chapter 4. Configuring the Application Server and Requester

  • Database Manager Storage
  • Database I/O
  • Tuning Parameters
  • Performance Indicator
  • Using Virtual Disks
  • Package Cache
  • Tuning Parameters
  • Concurrency
  • Agents
  • Allocating Users to Agent Structures
  • Tuning Parameters (NCUSERS)
  • Performance Indicator
  • CICS
  • Tuning Parameter (CIRB)
  • Performance Indicator
  • Pseudo-Agents
  • Tuning Parameter
  • Privileged Remote DRDA User (VSE Server)
  • Performance Indicator (SHOW USERS)
  • Dispatching Agents
  • Prioritization
  • Fair Share Auditing
  • Tuning Parameter (DISPBIAS)
  • Performance Indicator
  • Startup Mode
  • Locking
  • Locking Contention
  • Locking Hierarchy
  • Lock Modes
  • Lock Duration
  • Lock Compatibility
  • Number of Concurrent Users
  • Minimum Lock Level
  • Indexes
  • Access Path
  • Logical Unit of Work
  • Isolation Level
  • Catalog Tables
  • Performance Indicator
  • Lock Escalation
  • Deadlock
  • Tuning Parameters
  • Performance Indicator
  • Recovery
  • Logical Units of Work
  • CMS Work Units (VM)
  • Checkpoints
  • What occurs during the Checkpoint Process?
  • When do Checkpoints Occur?
  • Performance Implications
  • Choosing the Checkpoint Interval
  • Logging and Archiving
  • Log
  • Archive
  • Dual Logs
  • Choosing a Logmode
  • Tuning Parameters
  • Performance Indicator (SHOW LOG)
  • Communications
  • DRDA Performance Considerations (VM)
  • PROTOCOL Performance Considerations
  • Fetch and Insert Blocking
  • Implementing Blocking
  • Suppressed Blocking
  • Synchronous Communications (VM)
  • Considerations for ISQL and Adhoc Queries
  • AUTOCOMMIT
  • Isolation Levels
  • Temporary Tables
  • Views
  • DBS Utility Considerations
  • Automatic Statistics Collection
  • Suppressing Automatic Statistics Collection
  • TAPE Blocking
  • Lock Escalation
  • DATALOAD and RELOAD Locking Considerations
  • SELECT, DATAUNLOAD, and UNLOAD Locking Considerations
  • UNLOAD and RELOAD PACKAGE Considerations

    • Chapter 5. Improving Data Access Performance

  • Access Paths and Indexes
  • Dbspace Scans
  • Index Scans
  • Nonselective Index Scans
  • Selective Index Scans
  • Index-Only Access Scans
  • Clustering Index
  • Examples of Index only Access
  • Creating Indexes
  • Unique Index with Key Matching Predicate(s)
  • Indexes for Sorting
  • Recommendations for Indexes
  • Disadvantages of Indexes
  • Placing Tables into Dbspaces
  • Organizing Referential Structures
  • Predicate Processing
  • Column Attributes
  • Key-matching Predicates
  • Form of Key-matching Predicates
  • Suitable Index for Key-matching Predicate
  • Sargable and Residual Predicates
  • Example
  • Join Predicates
  • Search Conditions and Their Processing Characteristics
  • Filter Factors
  • Examples of Predicate Processing
  • Impact of CCSIDs on Sargability
  • Tuning Queries with Several Tables
  • Methods of Joining Two or More Tables
  • Nested Loop Join (Type 1)
  • Merge Scan Join (Type 2)
  • Choosing an Access Method
  • Nested Loop, with EMPLOYEE as Outer Table
  • Nested Loop, with PROJECT as Outer Table
  • Merge Scan, with EMPLOYEE as Outer Table
  • Merge Scan, with PROJECT as Outer Table
  • Multiple Joins
  • Keeping Database Statistics Current
  • Using Catalog Statistics
  • Modelling your Production System
  • A Warning about Updating Statistics
  • Determining the Cost of Access Methods
  • Processing Cost
  • I/O Cost
  • Using Explanation Tables to Evaluate Performance
  • Explain Processing
  • Using the EXPLAIN Statement
  • Using the EXPLAIN Option
  • Comparing Implicit and Explicit Explain Processing
  • Using Explanation Tables
  • Estimating Sizes of Responses
  • Using EXPLAIN for Database Design
  • Modifying Table Designs to Enhance Performance

    • Chapter 6. Data Spaces Support for VM/ESA

  • Improving DB2 Server for VM Performance
  • Understanding VM Data Spaces
  • Standard Virtual Machine Storage
  • Data Spaces Storage
  • Understanding how VMDSS uses Data Spaces
  • Reading Pages
  • Releasing Pages
  • Modifying Pages
  • Checkpoints
  • Storage Pools
  • Internal Dbspaces
  • Mapped Internal Dbspaces
  • Unmapped Internal Dbspaces
  • Directory
  • With Data Spaces Support off
  • With Data Spaces Support on
  • Managing Main and Expanded Storage
  • Target Working Storage Size Parameter
  • Working Storage Residency Priorities
  • The Checkpoint Interval
  • The Save Interval
  • Striping
  • With striping switched off
  • With striping switched on
  • Performance Counters
  • Planning Structure by Storage Pool
  • Logical and Physical Mapping
  • Logical Mapping
  • Physical Mapping
  • VSE Guest Sharing
  • Enabling Requirements
  • Operating System Overview
  • Operating in Non-XC Mode
  • Virtual Machine Overview
  • MAINT Machine
  • SQLMACH Database Machine
  • Software Requirements
  • Virtual Storage Requirements
  • MAINT Machine
  • SQLMACH Database Machine
  • Real Storage Requirements
  • DASD Storage Requirements
  • Fixed Block Storage Devices
  • VM/ESA Paging DASD
  • SQLMACH Database Machine
  • Hardware Requirements
  • Before Enabling
  • Program Directory for DB2 Server for VM
  • Preventive Service Planning
  • Corrective Service
  • Enabling Options
  • Using in a Production System
  • Disabling Data Spaces Support
  • Resaving DB2 Server for VM in Saved Segments
  • Enabling
  • Pre-Enable Checklist
  • Enable Checklist
  • Backing Up, Configuring and Enabling Your Database Machine
  • Step 1: Log onto the MAINT Machine
  • Step 2: Update the CP Directory
  • Step 3: Log off the MAINT Machine
  • Step 4: Log onto the SQLMACH Machine
  • Step 5: Archive your Database
  • Step 6: Activate VMDSS
  • Step 7: Log off the SQLMACH Machine
  • Step 8: Log onto the DB2 for VM Installation User ID (5697F42S)
  • Step 9: Link-Edit the Load Library
  • Step 10: Resave the DBSS Saved Segment
  • Step 11: Log off the DB2 for VM Installation User ID
  • Step 12: Log onto the SQLMACH Machine
  • Step 13: Verify the Installation
  • Step 13A: Verify non-XC Mode Installation
  • Step 13B: Verify XC Mode Installation
  • Step 14: Optional System Activities
  • Disabling VMDSS
  • Disable Step 1: Archive your Database
  • Disable Step 2: Access the Service Disk or Directory
  • Disable Step 3: Reblock the Directory Disk
  • Disable Step 4: Remove the VMDSS Files
  • Disable Step 5: Link-Edit the Load Library
  • Disable Step 6: Restart the Application Server
  • Operating
  • Storage Pool Specifications
  • Changing Storage Pool Specifications at Startup
  • Specifying Either Data Spaces Support or Standard DASD I/O
  • Specifying Storage Residence Priorities
  • Turning Striping On and Off
  • Checking Your Current Storage Pool Specifications
  • Changing Storage Pool Specifications Dynamically
  • Using Data Spaces with Internal Dbspaces
  • Unmapped Internal Dbspaces
  • Mapped Internal Dbspaces
  • Using Data Spaces with the Directory
  • Reblocking the Database Directory
  • Example
  • Using Data Spaces Support with a New Database

    • Chapter 7. Tuning Performance for Data Spaces Support

  • Deciding When to Use Data Spaces
  • Advantages
  • Shorter Path Length
  • Asynchronous Page Fault Processing
  • Striping
  • Blocking and Prefetching
  • Dynamic Working Storage Size Management
  • More Asynchronous Writes
  • Storage Pool
  • Internal Dbspaces
  • Directory
  • Managing Your Working Storage Size
  • Choosing the Target Working Storage Size
  • Choosing Storage Residence Priorities
  • Unmapped Internal Dbspaces
  • Managing Checkpoints
  • Choosing the Checkpoint Interval
  • Setting the Time Between Checkpoints
  • Choosing the Save Interval
  • Using Striping
  •  
  • With One Dbextent Per Pool
  • Choosing Logical or Physical Mapping
  • Real Storage Requirements for Data Spaces

    • Appendix A. Storage Pool Specification File Format

  • File Format
  • Data Line Syntax
  • Ordering Data Lines
  • Specification File Example

    • Appendix B. Determining Number of Data Spaces

  • Maximum Number of Data Spaces
  • Logical Mapping
  • Example
  • Physical Mapping
  • Example
  • Maximum Total Size
  • Displaying Current Data Spaces

    • Appendix C. Why is the TARGETWS Value Frequently Exceeded?

  • VMDSS Usage Scenario

    • Notices

  • Trademarks

    • Bibliography

    Index

    [ Top of Page | Previous Page | Next Page | Table of Contents | Index ]