Db2 Coding - Understanding and Reviewing Db2 Access Paths - Expert Videos
Duration
50 minutes
Overview
These expert videos begin by reviewing the role of the Db2 Optimizer and how it is used to satisfy SQL requests. You will then explore the various options for accessing data in a single table, and then take a look at the methods that Db2 can use to combine data when more than one table is accessed in your SQL code. Focus will then turn to filter factors, describing what they are, how you can calculate them for your data and queries, and how Db2 uses them to estimate the amount of data that it needs to access. Finally, methods that you can use to examine the access paths chosen by Db2 for your SQL queries will be discussed.
Audience
This is aimed at individuals whose job role is to analyze and improve the performance of their Db2 database programs.
Prerequisites
Successful completion of the Interskill, Db2 Coding - Coding for Concurrent Access - Expert Videos, or equivalent knowledge
Objectives
After completing this course, the student should be able to:
- Describe how the Db2 Optimizer Determines the Most Efficient Query Access Plan
- Explain How Table Joins Affect SQL Performance
- Describe How Filter Factors are Used
- Use EXPLAIN to Determine Access Path Selection
Course Content
Single Table Access Paths
Db2 Optimizer Concepts
Types of Access Paths
Table Space Scan
Sequential Prefetch Benefits
Indexed Access
Direct Row Access
Multi-table Access Paths
Joining Tables
Inner and Outer Joins
Nested Loop Join
Merge Scan Join
Hybrid Join
Join Best Practice
Filter Factors
Filter Factor Concepts
Filter Factor Formulas
Calculating a Filter Factor
RUNSTATS Statistics
Access Paths and EXPLAIN
EXPLAIN Command Use
Creating EXPLAIN Tables
Running EXPLAIN
Querying the PLAN_TABLE
Visual Representation of the PLAN_TABLE