Db2 13 – Optimizing Db2 Application Performance
Duration
5 hours
Overview
The Db2 13 – Optimize Db2 Application Performance course describes the methods used by Db2 when processing application programs containing SQL, and provides details of the tools and utilities that can be used to measure and analyze their effectiveness.
Audience
Programmers and DBAs responsible for managing the performance of SQL processing.
Prerequisites
Successful completion of all other courses in this curriculum, or equivalent knowledge.
Objectives
After completing this course, the student should be able to:
- Describe how locks affect Db2 performance
- Identify the information that the EXPLAIN facility creates in the PLAN_TABLE
- Code more efficient SQL
- Describe the factors that influence Db2 access paths
- Identify tools and utilities used to measure Db2 performance
Course Content
Creating, Managing, and Monitoring Db2 Locks
Db2’s Data Locking Strategy
Suspensions, Timeouts and Deadlocks
Locking Rows, Pages, Tables and Table Spaces
Lock Modes
Lock Duration
Attributes of Locks
Analyzing Lock Statistics and Reports
SQL Statement Performance and Optimization
Need for Continual SQL Tuning
Tools for Measuring SQL Performance
SQL Tuning Using IBM Data Studio
SELECT Statement Use
Sort Performance
Predicate Processing
JOIN Efficiency
Db2 EXPLAIN Facility
How the EXPLAIN Facility Captures SQL Performance Data
Tuning for Production
RUNSTATS Utility
EXPLAIN Tables
Creating and Populating the PLAN_TABLE
Interrogating the PLAN_TABLE
Using IBM Data Studio for Tuning
Db2 Access Paths
Items Influencing the Db2 Access Path
PLAN_TABLE and its Access Path-Related Information
Access Paths When Joining Data