Articles & Content


A Caveman's Perspective: Hunting down unused indexes and other db2pd features (D01)

Topic: 2009 NA

Subtopic: DB2 for LUW

DATE: 2009-5-12 (09:45 AM - 10:45 AM)
SPEAKERS: Brad Price (Werner Corporation)

First came walking upright, stone tools and fire. Come learn how to use some important features of one of the latest caveman tools - db2pd - and advance yourself along the DBA evolutionary path. Unused indexes are one of the silent killers of database performance. They are like a parasite, doing nothing good for the database, but instead slowing down insert/update/delete statements, reorgs/runstats and bloating the size of your database so that more disk is required and backups take longer.However, in today's world where dynamic sql dominates, how do you tell if an index is truly not being used?? Come learn how to use db2pd to help drive unused indexes to extinction in your database. In addition, several other useful features of db2pd will be explored.

EXP. LEVEL: Beginner,Intermediate,Advanced


Understand the purpose of indexes and what constitutes good index design

Explore the consequences of unused indexes and how they negatively affect your database

Introduction to the db2pd command and identify how db2pd can identify unused indexes for a given table

Learn how to build a script that will expand the power of db2pd to scan an entire schema or database for unused indexes. Explore other methods for attempting to find unused indexes and discuss pros/cons.

See several other uses of the db2pd command to help you manage your database. (identify table scans, determine ISO level being used by SQL statements, examine contents of bufferpools, and more...)

Click Here to Download

NOTE: These are only open to members of IDUG. If you are not a member, please CLICK HERE for more information.