Db2 for z/OS: Index Design & Implementation

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.

This course is available 'on demand' (minimum 2 students) for public presentations or for one-company, on-site presentations.

Virtual Classroom Environment dates - click to book!

UK Start Times

20 January 2025

What is a 'Virtual Classroom Environment'?

What do I need?

webcam
headphones with microphone
sufficient bandwidth, at least 1.5 Mb/s in each direction.

What you will learn

On successful completion of this course you 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.

Who Should Attend

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

Fee (per attendee)

£1200 (ex VAT)

This includes free online 24/7 access to course notes.

Hard copy course notes are available on request from rsmshop@rsm.co.uk

at £50.00 plus carriage per set.

Course Code

DIDI

Introduction to Indexes

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

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; etc.

Index Design & Creation

Which columns; column cardinality; Filter factors; composite keys; composite key sequence; CREATE INDEX options; defer creation; etc.

Changing Index Options

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

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; etc.