IBM - DB2 9 for z/OS Index Modeling and Design

KURSZIEL
IBM - DB2 9 for z/OS Index Modeling and Design: This advanced course is for DB2 for z/OS application developers, DB2 for z/OS DBAs, and anyone who is responsible for designing the best indexes for queries in a DB2 for z/OS environment.Kursziele

• Describe different index formats and reasons for using indexes
• Design indexes for matching, screening, index only, clustering, list prefetch, partitioning, and avoiding unnecessary sorts
• Use RUNSTATS options related to indexes
• Model the best indexes for Star Schema
• Recognize superfluous indexes and eliminate them
• Evaluate benefits of index compression
• Run the operational utilities for indexes

_x000D_

_x000D_

ZIELGRUPPE
This advanced course is for DB2 for z/OS application developers, DB2 for z/OS DBAs, and anyone who is responsible for designing the best indexes for queries in a DB2 for z/OS environment.Kursziele

• Describe different index formats and reasons for using indexes
• Design indexes for matching, screening, index only, clustering, list prefetch, partitioning, and avoiding unnecessary sorts
• Use RUNSTATS options related to indexes
• Model the best indexes for Star Schema
• Recognize superfluous indexes and eliminate them
• Evaluate benefits of index compression
• Run the operational utilities for indexes

_x000D_

_x000D_

KURSINHALT
This course is designed to teach you how to design the right indexes for improving query performance, both for single table access and for multiple table access. _x000D_

_x000D_

All of the index utilization possibilities are illustrated as well as the desired column sequence of a multiple column index. Pros and cons of DPSI and NPSI indexes are explained together with the lesser known concepts like index lookaside, implicit uniqueness, sparse indexes, and combination of index screening with list prefetch. The course also clarifies the meaning of many DB2 catalog columns pertaining to the indexes and shows how to correctly use the different RUNSTATS options related to indexes.Themenübersicht
Generalities

• Describe the general structure of DB2 indexes
• Discuss the advantages of Type 2 indexes used in DB2

Different Index Forms and Distinct Reasons for Using Indexes

• List many kinds of indexes that can be created
• Differentiate between index matching and index screening
• Discuss advantages and pitfalls of list prefetch access
• Understand the meaning of multiple index access
• Create indexes for index only access and for avoiding sort
• Highlight the importance of clustering index
• Distinguish between partitioning and partitioned indexes

Index Enhancements in DB2 V9

• Discuss the indexing enhancements introduced in DB2 V9
  • Uniqueness and parallelism for Data Partitioned Secondary indexes
  • Index on expression
  • Automatic creation of indexes
  • Asymmetrical split of index pages
  • Detection of unused indexes
  • Renaming an index
  • RANDOM index keys
  • Index page size larger than 4 KB
  • Index compression
  • Page range screening enhancements
  • Online REBUILD INDEX

Indexes for Joins

• Create indexes to enable joins to perform efficiently
• Create indexes to enable subqueries to perform efficiently
• Appreciate the importance of clustering and impact on joins

Index Versioning

• Discuss index versioning
• Understand when new index versions are created

Less Known Index Features

• Discuss some of the less known index features
• Differentiate between creating index with DEFINE NO and DEFER YES options
• Explain index look aside technique and when it is used
• Understand the importance of system parameter NPGTHRUSH prior to DB2 V8
• Understand the significance of volatile tables since DB2 V8

Operational Utilities for Indexes

• Describe the operational considerations for indexes
• Enable indexes for image copies
• Differentiate between RECOVER INDEX and REBUILD INDEX

Index Cost

• Perform the cost-benefit analysis of indexes
• Take steps to reduce index costs without compromising on performance

Indexes for Star Schema

• Create indexes on FACT table and Dimension tables
• Highlight enhancements introduced in DB2 V9 for Star Joins

Agenda
Day 1

• Welcome
• Unit 1 - Generalities
• Unit 2 - Different index formats and distinct reasons for using indexes

Day 2

• Unit 2 - Different index formats and distinct reasons for using indexes
• Unit 3 - Indexing enhancements in DB2 V9

Day 3

• Unit 4 - Indexes for join
• Unit 5 - Index versioning
• Unit 6 - Less known index features
• Unit 7 - Operational utilities for indexes
• Unit 8 - Index cost
• Unit 9 - Indexes for Star Schema

Hinweise
Course material will be in English.
_x000D_

VORRAUSSETZUNG
You should have:

• Familiarity with DB2 for z/OS queries andapplication programming.