DB2 SQL Performance and Tuning

Course Objectives
The Course provides information relating to the coding of efficient SQL statements. The major part of the Course focuses on ensuring that SQL performs 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 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
• use new Version 8 performance enhancements

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
• DATA MANIPULATION LANGUAGE EFFICIENCY ISSUES
• 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
• 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

Course Format: SQL Explain 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.