Audience
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.
Prerequisites
Those attending the course should have used DB2 in the past, and preferably attended our DB2 SQL Programming course.
Duration
3 days. Hands on.
Course Objectives
The course is designed to instruct delegates how to develop and maintain existing applications in an efficient manner, up to and including DB2 10. 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.
Course Content
Introduction to DB2 versions
System architecture & started tasks
Stage 1/ Stage 2 predicates
Data transfer
Storage
Database including default in DB2 10
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 changes in 10)
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 DB2 10
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 8/9/10 differences
CTE’s and Recursive SQL
XML
Introduction
Object Creation
Xpath Functions
Performance Issues
XML Version 10