DB2 for z/OS: Index Design & Implementation Training

Course #:WA2275

DB2 for z/OS: Index Design & Implementation Training

This course describes how the correct design of indexes can improve query performance for both single and multiple table access. It covers the different methods by which an index may be utilised by DB2, such as matching and non-matching index scans, index screening, index lookaside and use of multiple indexes and list prefetch. The factors involved in determining the best index to use to satisfy a given statement are also discussed. Different types of index are covered, as well as many of the design options available (such as the column sequence of a multiple column index) and the options available when the index is defined. Interpreting catalog columns and running relevant utilities to maintain index performance are also explained.

Objectives

On successful completion of this course attendees will be able to:

  • describe the reasons for using indexes
  • list the different ways in which DB2 may use an index
  • explain the considerations in selecting the correct column sequence for multi-column indexes
  • describe and utilise the options available to design indexes for clustering, partitioning, matching and avoiding unnecessary sorts
  • use the RUNSTATS options related to indexes
  • describe and use the operational utilities for indexes.

Audience

This course is suitable for DB2 database administrators and application developers who are responsible for index design for queries in a DB2 for z/OS environment.

Prerequisites

A good understanding of DB2 and SQL queries.

Duration

2 days

Outline of DB2 for z/OS: Index Design & Implementation Training

1. Introduction to Indexes

  • Reasons for indexing
  • index structure
  • access paths
  • when to index
  • other considerations
  • overview of catalog entries for indexes.

2. Types of Index

  • Unique and non-unique indexes
  • index key sequence
  • index key randomisation
  • clustering indexes
  • padded and non-padded
  • compressed indexes
  • partitioned and partitioning indexes
  • non-partitioned secondary indexes (NPSI)
  • data-partitioned secondary indexes (DPSI)
  • indexes on expressions
  • including non-unique columns in unique index

3. Index Design & Creation

  • Which columns
  • column cardinality
  • Filter factors
  • composite keys
  • composite key sequence
  • CREATE INDEX options
  • defer creation

4. Changing Index Options

  • ALTER INDEX options
  • index versioning
  • pending definition changes
  • renaming indexes
  • other implications of changing index.

5. Managing & Maintaining Indexes

  • Catalog statistics
  • I/O estimations
  • distribution statistics
  • column grouping statistics
  • column correlation
  • histogram statistics
  • using RUNSTATS
  • other utilities for indexes
  • determining the need for, and reorganising indexes
  • index backup and recovery
  • index integrity
We regularly offer classes in these and other cities. Atlanta, Austin, Baltimore, Calgary, Chicago, Cleveland, Dallas, Denver, Detroit, Houston, Jacksonville, Miami, Montreal, New York City, Orlando, Ottawa, Philadelphia, Phoenix, Pittsburgh, Seattle, Toronto, Vancouver, Washington DC.