DB2 UDB/390 Performance and Tuning

Course: DB2PT

Duration: 3 Days

Level: I

Course Summary

The Course is designed to instruct those 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 it could perform poorly, and how to reconstruct so that it could perform better.

The course will contain practical exercises to consolidate the theory sessions.

« Hide The Details

Topics Covered In This Course

Introduction to DB2 versions

System architecture & started tasks

Stage 1/ Stage 2 predicates

Data transfer

Storage

Database including default in DB2 9
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

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
- EXCEPT/INTERSECT keyword
- TRUNCATE TABLE statement
- MERGE and SELECT FROM MERGE statements
- INSERT performance improvements DB2 10

Indexes and performance

Page
Record identifier
Indexes
- Type 2
- Splits
- Indexes version 9 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 Center

Administration Tools

DSN_STATEMENT 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

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
Objective Creation
Xpath Functions
Performance Issues
XML Version 10

Recommended Prerequisites

Previous DB2 knowledge is required.