Broadcom - Interskill Learning Mainframe Training

[email protected]

The Online Mainframe Training Specialists
Search   Course Catalog  
Return to Catalog Home     Return to Curriculum Listing

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


Search our catalog