DB2 for z/OS SQL Performance and Tuning

The course is designed to instruct delegates how to develop and maintain existing applications in an efficient manner, up to and including DB2 11.

IT personnel and developers who have been exposed to using DB2 under z/OS and wish to understand more of the SQL performance and tuning aspects of the product.

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

The course will look at existing SQL to see if it is performing poorly, and to identify why this is the case, and how to reconstruct it so that it can perform better. The course contains practical exercises to consolidate the theory sessions.

Introduction to DB2 Versions
System architecture & started tasks.
Stage 1/ Stage 2 predicates.
Data transfer.
Storage.
Database including default in DB2 11.
Tablespace.
Universal, Simple, Segmented, Partitioned.
Non logged table spaces.
Tables.
Cloning Tables.
Reordered Row Format.
Data types including XML BIGINT DECFLOAT.
Implicitly Hidden Columns.
UDT.
VIEWS.
MQT.
Catalog (Including latest changes).
HISTORY TABLES.
Directory.
Temporal Tables.

Practical Exercise

Advanced SQL
Sorting.
GROUP BY, ORDER BY, DISTINCT.
How to detect and how to avoid.
Small Sorts.
Consolidation of functions.
OPTIMIZE FOR.
Skip Lock Data.
Access to Committed data.
FETCH only.
Table joins.
SUBSELECTS.
Correlated SUBSELECTS.
UNIONS.
Vs 4 Joins.
Nested tables.
CTE’s.

Advanced SQL (Cont.)
EXCEPT/INTERSECT keyword.
TRUNCATE TABLE statement.
MERGE and SELECT FROM MERGE statements.
INSERT performance improvements.
Indexes and performance.
Page.
Record identifier.
Indexes.
Splits.
Indexes - changes.
Larger Index Page Sizes.
Last Used Index Flag.
Index Key Randomization.
Index Compression.
Index On Expression.
Index Page Split.
Index Lookaside.
Member Cluster.
Cluster Ratio.
Explain & Filter factor.
Optimizer.
EXPLAIN.
Catalog statistics.
Single column/ Multiple columns.
Filter factor.
Visual Explain.
Optimization Service Center.
Administration Tools.

DSN_STATEMNT Table
Third party tools.
Access paths.
Single table access.
Multiple table access.
Types of access paths.

Online Performance Guidelines
Coding techniques.
Efficient Browse.
Multiple columns indexes.
Online updates.
Batch Performance Guidelines.
Locking & Performance.
Understanding locking.
Contention.
Locking in a shared environment.
Global deadlocks.
Skip Locked Data. 
Isolation Level.
Multi row Fetch (Rowset).

Packages/Plans
Terms/terminology.
BIND OPTIONS.
Data structures.
Triggers.
Stored procedures.

Consolidation of Version 9/10/11 Differences

CTE’s and Recursive SQL

XML
Introduction.
Object Creation.
Xpath Functions.
Performance Issues.
XML in DB211.