DB2 Tuning and Performance - Topical Outline

Introduction

DB2 Architecture

DB2 Objects

Physical Storage Structures

DB2 Catalog And Directory

Address Spaces

Connecting to DB2: Threads

Logging, The Boot Strap Data Set, and Checkpoints

Pools: Buffer, Hiper, And EDM

Computer Exercise: Course Setup

The DB2 Sample Application

Application Programming Tuning

Access Strategies Overview

EXPLAIN and PLAN_TABLE

Computer Exercise: Access Strategies and EXPLAIN

Access Strategies Details

Application of Predicates

Indexable, Stage 1, and Stage 2 Predicates

Catalog Tables and Access Strategies

Filter Factors

Catalog Table Modification

Computer Exercise: Access Strategies in Aggregated Queries

Join Access Strategies

Star Joins

Computer Exercise: Access Strategies in Joins

Subquery Access Strategies

View and UNION

Query Parallelism

Computer Exercise: Access Strategies in Subqueries

EXPLAINing Plans and Packages

Optimization Hints

DSN_STATEMNT_TABLE and DSN_FUNCTION_TABLE

Predictive Governing

DSN_STATEMENT_CACHE_TABLE

Computer Exercise: Hints and Costs

BIND Parameter Performance Considerations

Concurrency and Locking

Claim/Drain Processing

Lock Avoidance: Commit LSN and PUNC Bits

Computer Exercise: Bind parameters and locking

Other Programming Considerations

Sequential and List Prefetch

Dynamic SQL

Batch Issues

Computer Exercise: Plans, Indexes, and Views

Security and Performance

CICS Security

IMS Security

Table Access Issues

Authorization Exits

Security Strategies

Visual Explain

Computer Exercise: Visual Explain

Attachment Facilities and Performance

CICS Attachment

IMS Attachment

TSO Attachment

Call Attachment

Recoverable Resource Services Manager Attachment

Computer Exercise: Attachment Facility Parameters

Data Base Design Considerations

Referential Integrity and Normalizing

Data Bases, Storage Groups, Table Spaces, Tables, and Indexes

Creating new Indexes

REORG Implications

Computer Exercise: Index Support Activities

Utility Considerations

Utility Partition Independence

LOAD

COPY, MERGECOPY, and RECOVER

Partial Recovery

REORG, RUNSTATS, and QUIESCE

Computer Exercise: A Study in Partial Recovery

Other Topics

Bufferpools and Hiperpools

Distributed Processing Issues

DB2 System Parameters

DB2 Instrumentation Facility

z/OS Issues

Computer Exercise: The Real World

G580 / 5 Days

These Materials © Copyright 2013 by Hunter Cobb

Materials version: V3.x

Course Objectives
Course Description
Home page

This page last updated: 1 August, 2014
Copyright © 2014 by Hunter's Training Associates, LLC