DB2 Application Performance and Tuning

Course Objectives
The Course provides information relating to the coding of efficient SQL statements together with the wider issues that affect the performance of DB2 z/OS applications. The major part of the Course focuses on ensuring that applications perform well in a production environment. The DB2 Explain facility is used extensively to demonstrate how SQL performance may be monitored and tuned. On completion of this Course the student will be able to:

• understand the performance issues involved in constructing a system
• implement an efficient tablespace, table and index design
• understand DB2 locking issues
• choose the correct locking strategy
• design and use Referential Integrity
• design and use Materialized Query Tables
• understand how Cursors are processed
• understand and use Plans and Packages effectively
• understand the importance of RUNSTATS
• understand the process of optimisation
• use optimisation hints
• describe the various access path techniques that DB2 is able to use
• run and understand the Explain Facility
• use Indexable and Stage 1 predicates to write efficient SQL statements

Course Details

• PHYSICAL DATABASE PERFORMANCE ISSUES
• The Structure Of DB2 Objects
• Database Definition
• Tablespaces
• Pages
• Tablespace Organisation
• Page And Row Organisation
• The Simple Tablespace
• The Segmented Tablespace
• The Partitioned Tablespace
• Maximum Number Of Partitions
• The Lob Tablespace
• Create Tablespace Parameters:
• Alter Tablespace
• Table Definition
• DB2 Column Types
• Design Tips for Columns
• Null Values
• User Defined Default Values
• Nulls - Design Tips
• Global Temporary Tables
• Declared Temporary Tables
• Declared Temporary Table Considerations
• Declared Temporary Tables - Comparisons
• Table-Controlled Partitioning
• DB2 Indexes
• Index Organisation - The B Tree Index
• Backwards Index Scan
• Index Clustering
• Clustering With Partitioned Tables
• Clustering Within Partition
• Changing the Clustering Sequence
• Non-Unique Indexes
• Index Definition
• Data Partitioned Secondary Indexes
• Creating a DPSI
• Design Considerations - DPSI Problems
• Partitioning Indexes
• Index Design Considerations
• Altering Tables and Indexes
• Data Type Changes
• Alter Data Type - Performance Implications
• Alter Data Type - Impact Upon Indexes
• Alter Data Type - Index Availability
• DML PERFORMANCE REVIEW
• Row Placement Rules
• Select Statements
• The Where Clause
• Special Operators
• Special Operators - Examples
• Is Not Distinct From
• SQL Built-In Column Functions
• Column Function Performance Notes
• Using 'Distinct'
• Multiple Distinct
• Group By Clause
• Expressions / Functions in Group By
• Having Clause
• Order By Clause
• Fetch First 'n' Rows Only Clause
• The Update Statement
• The Delete Statement
• The Insert Statement
• Select from Insert
• Select from Insert Example
• Result Table Rows from the Insert Statement
• Select From Insert in a Cursor
• Select From Insert - New Order By Option
• Select From Insert - Error Processing
• Scalar Functions
• Function Examples
• The Case Statement
• Outer Joins
• Outer Join Syntax
• Outer Join - Joining More Than 2 Tables
• SQL Union
• Subqueries
• Subqueries Using In
• Exists
• Common Table Expressions
• Writing a Common Table Expression
• Recursive SQL
• Recursive SQL Example
• Recursive SQL - Controlling Depth of Recursion
• IDENTITY COLUMNS AND SEQUENCES
• Identity Column Review
• Identity Columns - Examples
• Identity Column Enhancements
• Altering Identity Columns
• Identity Columns - New Version 8 Parameters
• Identity Columns - Retrieving the Generated Number
• Identity Columns - Data Sharing Implications
• Using Identity Columns with the Load Utility
• Sequences
• Create Sequence Syntax
• Sequence Ordering
• Altering Sequences
• Dropping Sequences
• Sequence Authorities
• Using Sequences in Applications
• Sequences - Considerations and Restrictions
• Sequence Application Examples
• Consumed Values / Gaps in a Sequence
• Duplicate Sequence Values
• Sequence Cycle Considerations
• Defining a Constant Sequence
• Cache Considerations
• Sequences and Identity Columns Comparison
• PROGRAMMING FOR PERFORMANCE
• Development Cycle With DB2
• DB2 Bind
• Rebind
• Packages Overview
• Explanation Of Packages
• Advantages Of Using Packages
• Bind Parameters
• Binding Using Jcl
• Identifying A Collection Within A Program
• Cursor Operations
• Declare Cursor
• Open Cursor
• Fetch A Row
• Row Update
• Row Deletion
• Close Cursor
• With Hold Option
• The Optimize Statement
• Fetch First 'n' Rows Only Clause
• Fetch First vs Optimize For
• Multi-row Fetch
• New Syntax for Declare Cursor
• Fetching Rowsets
• Fetch Examples
• Host Variable Arrays
• Catering for Update and Delete Holes
• Partial Rowsets
• Rowsets - SQLCA
• Positioned Update
• Positioned Delete
• Multi-row Insert
• Multi-row Insert Syntax
• Multi-Row Insert Example
• Get Diagnostics
• Get Diagnostics - Statement Information
• Get Diagnostics - Examples
• Diagnostic Information for Multi-Row Fetch
• Get Diagnostics Fetch Example
• Diagnostic Information for Multi-Row Insert
• Get Diagnostics Insert Example
• Retrieving System Registers
• SCROLLABLE CURSORS
• Scrollable Cursors
• Declaring a Scrollable Cursor
• Insensitive Cursor
• Sensitive Static Cursor with Insensitive Fetch
• Sensitive Static Cursor with Sensitive Fetch
• Sensitive Dynamic Cursor
• Asensitive Cursor
• Scrollable vs Non-scrollable Cursors
• Updatable Cursors
• Declaring a Scrollable Cursor
• Fetching from a Scrollable Cursor
• Absolute Fetching Examples
• Relative Fetching Examples
• Sensitive Dynamic Cursors - Fetching Rows
• Sensitive Fetches - Update and Delete Holes
• Positioned Updates Using A Sensitive Cursor
• Scrollable Cursor Considerations
• LOCKING
• Implications Of Concurrent Processing
• Ims Resource Lock Manager (IRLM)
• DB2 Locking Methods
• The Lock Table Statement
• Lock Modes
• When Locks Are Acquired
• When Locks Are Released
• Isolation Levels
• Controlling The Isolation Level At SQL Level
• Keep Update Locks
• Unit Of Work In TSO
• Commit And Rollback
• Declaring Cursors With Hold
• CICS Issues
• IMS Issues
• Savepoints
• Savepoint Definition
• Savepoints - Considerations and Restrictions
• Transaction Deadlocks
• Design Considerations
• MATERIALIZED QUERY TABLES
• What Are Materialized Query Tables?
• MQT Features
• Creating an MQT
• Create MQT Example
• Altering an MQT
• Alter MQT Example
• MQT Fullselect Features / Restrictions
• Refresh Table
• Populating User Maintained MQTs
• Automatic Query Rewrite using MQT
• Enabling Automatic Query Rewrite
• Enabling Automatic Query Rewrite - DDL Options
• Enabling Automatic Query Rewrite - Special Registers
• AQR - Using Both Registers
• Enabling Automatic Query Rewrite - System Properties
• Enabling Automatic Query Rewrite - Query Properties
• AQR Examples
• Determining if Query Rewrite Occurred
• MQTs and Referential Integrity
• MQTs and RI - Informational Constraints
• REFERENTIAL INTEGRITY
• What Is Referential Integrity?
• Parent And Dependent Tables
• The Primary Key
• The Foreign Key
• Referential Constraint Rules
• Constraint Names
• More Complex Referential Structures
• Check Pending Status
• Resetting Check Pending Status
• Referential Integrity Access
• Design Considerations
• RUNSTATS
• The Runstats Utility
• Catalog Statistics Updated By Runstats
• Runstats Considerations
• Runstats - Distribution Statistics
• Runstats - Other Enhancements
• Volatile Tables
• PREDICATE PROCESSING
• Predicate Definition
• Predicate Evaluation
• Indexable Predicates
• Stage 1 And Stage 2 Predicates
• Predicate Evaluation Sequence
• Order Within Each Stage
• Use Of And / Or
• Predicate Evaluation Table
• OPTIMIZATION
• Introduction
• Default Catalog Statistics
• The Optimize Statement
• Fetch First 'n' Rows Only Clause
• Fetch First vs Optimize For
• Filter Factors
• Filter Factors With Boolean Operations
• Performance / Cost Estimation
• Example Of Performance / Cost Estimation
• Run-Time Reoptimization
• Optimization Hints
• Enabling Optimization Hints
• Planning to use Optimization Hints
• Creating The Hint
• Step 1 - Plan_Table / SQL Statement Correlation
• Step 2 - Set Opthint and Amend the Access Path
• Step 3 - Ensure That The Program Uses The Hint
• Optimization Hint Validation
• ACCESS PATHS
• Introduction
• Tablespace Scan
• Non-Matching Index Scan
• Matching Index Scan
• Multiple Index Access
• Index Only Access
• Prefetch Processing
• List Sequential Prefetch
• SQL In List Processing - Dynamic Prefetch
• Sequential Detection
• Query Parallelism Review
• Parallel Sorting
• Enabling Parallelism
• Parallelism Terminology
• Table Join Methods
• Merge Scan Join
• Nested Loop Join
• Hybrid Join
• Star Join
• Star Join Example
• Referential Integrity Access
• Rowid - Direct Row Access
• Direct Row Access - Example
• Direct Row Access - New Plan Table Column
• TUNING
• The DB2 Explain Function
• SQL Explain Statement
• Plan Table Layout
• Plan Table Column Definitions
• Explaining the Statement Cache
• SQL Tuning Considerations

Course Environment The Course runs in the following environment:

• IBM Mainframe

Target Audience
This course provides advanced DB2 training, and is aimed at staff requiring a detailed knowledge of the issues involved in writing, monitoring and tuning DB2 SQL queries and applications to perform efficiently. In addition, the course covers some wider areas that can affect application performance, such as Cursor Operations, Locking and Referential Integrity.

Course Format: Practical database set-up and SQL explain sessions make up a large part of the course, allowing delegates to demonstrate and reinforce the lectures given. During these sessions the delegate will run many explains on example queries and programs. Delegates are also invited to bring along their own SQL statements to be used as case studies during the course The comprehensive Student Guide supplied is fully indexed serving as a useful reference tool long after the course has finished. Delegates will also be able to access a free help-line with technical questions relating to topics covered on the course.