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 for DB2 for z/OS: Index Design & Implementation

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