Db2 13 for z/OS SQL Performance and Tuning

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.

Those attending the course should have used Db2 in the past, and preferably attended our Db2 SQL Application Programming course.

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 performance enhancements

• DB2 OBJECTS
  • The Structure Of Db2 Objects
  • Database Definition
  • Tablespaces
    • Tablespace Creation Syntax
    • Pages
    • Tablespace Organisation
    • Page And Row Organisation
    • The Simple Tablespace
    • The Segmented Tablespace
    • The Partitioned Tablespace
    • Partition-By-Growth Tablespace
    • Partition-By-Range Tablespace
    • Maximum Number Of Partitions
    • The Lob Tablespace
  • Table Definition
  • Db2 Column Types
  • Design Tips for Columns
  • Null Values
    • Nulls - Design Tips
  • Implicitly Hidden Columns
  • Row Change Timestamps
  • Partition Management
  • Db2 Indexes
    • Index Definition
    • Create Index Parameters
    • Index Organisation - The B Tree Index
    • Backwards Index Scan
    • Non-Unique Indexes
    • Index Clustering
      • Clustering With Partitioned Tables
      • Clustering Within Partition
      • Changing the Clustering Sequence
    • Partitioned Indexes
      • Creating a DPSI
      • Design Considerations - DPSI Problems
    • Index On Expression
• DML PERFORMANCE REVIEW
  • Select Statement - Review
  • The Where Clause - Review
  • Special Operators - Examples
  • Sql Built-In Column Functions
  • Column Function Performance Notes
  • Using '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
  • The Merge Statement
    • Merge Statement Restrictions
  • Select from Insert
    • Select from Insert Example
    • Select From Insert in a Cursor
    • Select From Insert - Order By Option
  • Select from Update
  • Select from Delete
  • Select from Merge
  • Scalar Functions
  • The Case Statement
  • Inner Joins
  • Outer Joins
  • Join Examples
  • Union, Intersect and Except
    • Union / Intersect / Except Examples
  • Subqueries
    • Subqueries Using In
    • Exists
  • Common Table Expressions
    • Common Table Expressions - A Complex Example
  • Recursive SQL
    • Recursive SQL Example
    • Recursive SQL - Controlling Depth of Recursion
  • Improved Insert Performance - The Append Parameter
  • The Truncate Statement
    • Truncate Examples
    • Truncate Restrictions
  • Utility Performance - Row Placement Rules
• PREDICATE PROCESSING
  • Predicate Definition
  • Predicate Evaluation
    • Predicate Evaluation Table
    • Predicate Evaluation Table - Notes
  • Indexable Predicates
  • Stage 1 And Stage 2 Predicates
  • Predicate Evaluation Sequence
  • Use Of And / Or
• RUNSTATS
  • The Runstats Utility
  • Catalog Statistics Updated By Runstats
  • Runstats Considerations
  • Runstats - Distribution Statistics
  • Runstats - Historical Statistics
  • Volatile Tables
• OPTIMIZATION
  • The Optimizer
  • The Optimize Statement
  • Fetch First 'n' Rows Only Clause
  • Filter Factors
    • Filter Factors With Boolean Operations
  • Performance / Cost Estimation
    • Example Of Performance / Cost Estimation
  • Run-Time Reoptimization
  • Optimization Hints
    • Planning to use Optimization Hints
• TUNING
  • Db2 Explain
  • The Explain Statement
  • Plan Table Layout
  • Plan Table Column Definitions
  • The Dsn_Statemnt_Table
  • Explaining the Statement Cache
  • Interpreting Explain Output
  • 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 Techniques
    • Enabling Parallelism
  • Table Join Methods
    • Merge Scan Join
    • Nested Loop Join
    • Hybrid Join
    • Star Join
      • Star Join Example