Training

 

 

Popular Courses

Browse Our Free Resources

  • whitepapers
  • whitepapers
  • webinars
  • blogs

Our Locations

Training Centres

Vancouver, BC
Calgary, AB
Edmonton, AB
Toronto, ON
Ottawa, ON
Montreal, QC
Hunt Valley
Columbia

locations map

Calgary

550 6th Av SW
Suite 475
Calgary, AB
T2P 0S2

Toronto

821A Bloor Street West
Toronto, ON
M6G 1M1

Vancouver

409 Granville St
Suite 902
Vancouver, BC
V6C 1T2

U.S. Office

436 York Road
Suite 1
Jenkintown, PA
19046

Other Locations

Dallas, TX
Miami, FL

Home > Training > DB2 > DB2 for z/OS: Index Design & Implementation Training

DB2 for z/OS: Index Design & Implementation Training

Course#: WA2275

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 WA2275 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.
*Your name:

*Your e-mail:

*Phone:

*Company name:

Additional notes:

We have received your message. A sales representative will contact you soon.

Thank you!.

more details
buy this course

Register for a courseware sample

It's simple, and free.

 

Thank You!

You will receive an email shortly containing a link to download the requested sample of the labs for this course.