DB2 UDB/390 Performance and Tuning

Course:   DB2PT
Duration:   3 Days
Level:   I
On our website at:   http://www.verhoef-training.com/courses/DB2PT.html
 
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.

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

Directory

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.

Related Courses
Code Course Title Duration Level
DB2AU
DB2 Administration for Linux, Unix and Windows
5 Days
I
Details
DB2M
Advanced DB2 Application Programming
3 Days
II
Details
DB2M9
DB2 UDB/390 Version 9 Transition
2 Days
II
Details

Every student attending a Verhoef Training class will receive a certificate good for $100 toward their next public class taken within a year.

You can also buy "Verhoef Vouchers" to get a discounted rate for a single student in any of our public or web-based classes. Contact your account manager or our sales office for details.