Target Audience

This course provides advanced DB2 training, and is aimed at staff requiring a detailed knowledge of the issues involved in writing, monitoring and tuning DB2 SQL queries and applications to perform efficiently.

Pre-requisites

The student should be familiar with writing SQL statements SELECT, INSERT, UPDATE and DELETE.

Course Objectives

The course provides information relating to the coding of efficient SQL statements. The major part of the course focuses on ensuring that SQL performs well in a production environment. The DB2 Explain facility is used extensively to demonstrate how SQL performance may be monitored and tuned. On completion of this course the student will be able to:

•     understand the performance issues involved in constructing a system
•     implement an efficient tablespace, table and index design
•     understand the importance of RUNSTATS
•     understand the process of optimisation
•     use optimisation hints
•     describe the various access path techniques that DB2 is able to use
•     run and understand the Explain Facility
•     use Indexable and Stage 1 predicates to write efficient SQL statements
•     use new Version 8 performance enhancements

Course Environment

•     IBM Mainframe

Course Details

PHYSICAL DATABASE PERFORMANCE ISSUES

The Structure Of DB2 Objects

Database Definition

Tablespaces

Pages

Tablespace Organisation

Page And Row Organisation

The Simple Tablespace

The Segmented Tablespace

The Partitioned Tablespace

Maximum Number Of Partitions

The Lob Tablespace

Create Tablespace Parameters:

Alter Tablespace

Table Definition

DB2 Column Types

Design Tips for Columns

Null Values

User Defined Default Values

Nulls - Design Tips

Global Temporary Tables

Declared Temporary Tables

Declared Temporary Table Considerations

Declared Temporary Tables - Comparisons

Table-Controlled Partitioning

DB2 Indexes

Index Organisation - The B Tree Index

Backwards Index Scan

Index Clustering

Clustering With Partitioned Tables

Clustering Within Partition

Changing the Clustering Sequence

Non-Unique Indexes

Index Definition

Data Partitioned Secondary Indexes

Creating a DPSI

Design Considerations - DPSI Problems

Partitioning Indexes

Index Design Considerations

Altering Tables and Indexes

Data Type Changes

Alter Data Type - Performance Implications

Alter Data Type - Impact Upon Indexes

Alter Data Type - Index Availability

DATA MANIPULATION LANGUAGE EFFICIENCY ISSUES

Row Placement Rules

Select Statements

The Where Clause

Special Operators

Special Operators - Examples

Is Not Distinct From

SQL Built-In Column Functions

Column Function Performance Notes

Using 'Distinct'

Multiple Distinct

Group By Clause

Expressions / Functions in Group By

Having Clause

Order By Clause

Fetch First 'n' Rows Only Clause

The Update Statement

The Delete Statement

The Insert Statement

Select from Insert

Select from Insert Example

Result Table Rows from the Insert Statement

Select From Insert in a Cursor

Select From Insert - New Order By Option

Select From Insert - Error Processing

Scalar Functions

Function Examples

The Case Statement

Outer Joins

Outer Join Syntax

Outer Join - Joining More Than 2 Tables

SQL Union

Subqueries

Subqueries Using In

Exists

Common Table Expressions

Writing a Common Table Expression

Recursive SQL

Recursive SQL Example

Recursive SQL - Controlling Depth of Recursion

RUNSTATS

The Runstats Utility

Catalog Statistics Updated By Runstats

Runstats Considerations

Runstats - Distribution Statistics

Runstats - Other Enhancements

Volatile Tables

PREDICATE PROCESSING

Predicate Definition

Predicate Evaluation

Indexable Predicates

Stage 1 And Stage 2 Predicates

Predicate Evaluation Sequence

Order Within Each Stage

Use Of And / Or

Predicate Evaluation Table

OPTIMIZATION

Introduction

Default Catalog Statistics

The Optimize Statement

Fetch First 'n' Rows Only Clause

Fetch First vs Optimize For

Filter Factors

Filter Factors With Boolean Operations

Performance / Cost Estimation

Example Of Performance / Cost Estimation

Run-Time Reoptimization

Optimization Hints

Enabling Optimization Hints

Planning to use Optimization Hints

Creating The Hint

Step 1 - Plan_Table / SQL Statement Correlation

Step 2 - Set Opthint and Amend the Access Path

Step 3 - Ensure That The Program Uses The Hint

Optimization Hint Validation

ACCESS PATHS

Introduction

Tablespace Scan

Non-Matching Index Scan

Matching Index Scan

Multiple Index Access

Index Only Access

Prefetch Processing

List Sequential Prefetch

SQL In List Processing - Dynamic Prefetch

Sequential Detection

Query Parallelism Review

Parallel Sorting

Enabling Parallelism

Parallelism Terminology

Table Join Methods

Merge Scan Join

Nested Loop Join

Hybrid Join

Star Join

Star Join Example

Referential Integrity Access

Rowid - Direct Row Access

Direct Row Access - Example

Direct Row Access - New Plan Table Column

TUNING

The DB2 Explain Function

SQL Explain Statement

Plan Table Layout

Plan Table Column Definitions

Explaining the Statement Cache

SQL Tuning Considerations

Course Format

SQL Explain explain sessions make up a large part of the course, allowing delegates to demonstrate and reinforce the lectures given. During these sessions the delegate will run many explains on example queries and programs. Delegates are also invited to bring along their own SQL statements to be used as case studies during the course The comprehensive Student Guide supplied is fully indexed serving as a useful reference tool long after the course has finished. Delegates will also be able to access a free help-line with technical questions relating to topics covered on the course.