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

DateLocationDurationCostEnrolEnquiry
2022-06-13Virtual2 Days £1000.00 (plus VAT)
Enquire
2022-08-16Virtual2 Days £1000.00 (plus VAT)
Enquire
2022-10-18Virtual2 Days £1000.00 (plus VAT)
Enquire

Course Description

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 Enquiry