DB02 - DB2 z/OS SQL Performance and Tuning - 2 Days

Course Description

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.

Pre-requisites

The student should be familiar with writing SQL statements SELECT, INSERT, UPDATE and DELETE.

  • 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

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:

Environment

The course runs in the following environment:

  • IBM Mainframe
  • DB2 for z/OS Versions 10, 11 or 12

Customisation

For on-site courses (i.e. at your premises), we are more than happy to tailor the course agenda to suit your exact requirements. In many cases, we are able to build your in-house standards and naming conventions into the delivered course.

Course Details

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

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.

Course Enquiry