Db2 indexes

What an index is for

Each index is based on the values in one or more columns of a table. The main purpose is to improve performance for data access. With a good index, Db2 can locate a department by DEPTNO without scanning every row of DEPT. Another purpose is to ensure uniqueness—a unique index on employee number prevents two employees from sharing the same id.

An index is stored separately from the table data, in its own index space (usually in the same database as the table). When you CREATE INDEX, Db2 builds and maintains the structure. You still REORG or RECOVER indexes when operations require it. Most users never name the index in SQL—the optimizer decides whether to use it.

1
2
3
4
-- Predicate on an indexed column; Db2 may choose index access
SELECT DEPTNAME
  FROM DEPT
 WHERE DEPTNO = 'A00';

Simple vs extended indexes (awareness)

Db2 supports simple indexes and extended forms such as expression-based, spatial, and XML indexes. Beginners start with column indexes; specialized types appear when data or queries demand them.

Primary vs secondary indexes (intro)

A primary index is the unique index whose key matches the table’s primary key. Db2 uses it to enforce that no two rows share the same primary key. When you define PRIMARY KEY on CREATE TABLE, Db2 typically ensures a supporting unique index exists (created explicitly or implicitly depending on how you define the constraint).

A secondary index is, in introductory IBM language, an index that is not the primary index (and, when discussing partitioned tables, not the partitioning index). Secondary indexes support alternate access paths—for example an index on LASTNAME for name search while EMPNO remains the primary key.

1
2
3
4
5
CREATE UNIQUE INDEX HR.XEMP1
  ON HR.EMPLOYEE (EMPNO);
 
CREATE INDEX HR.XEMP2
  ON HR.EMPLOYEE (LASTNAME);

XEMP1 looks like a primary-style unique index on EMPNO. XEMP2 is a non-unique secondary index for lookups by last name. Exact naming and CLUSTER/PARTITION options vary by design; the mental split is identity/uniqueness versus extra access paths.

Keys vs indexes

A key is a logical uniqueness or relationship rule. An index is the physical structure that often implements that rule and speeds access. You can have indexes without calling them “keys,” and you talk about foreign keys even though the child’s foreign-key columns may or may not be indexed (indexing them is often wise for joins and deletes).

Index trade-offs: read speed vs write cost

IBM is explicit: more indexes can improve access for some transactions and require additional processing for insert, update, and delete of index keys. Every modifying statement that touches indexed columns may update those indexes.

• Reads — equality and range predicates, joins, and ORDER BY can benefit when stats and design line up
• Writes — INSERT always maintains indexes; UPDATE maintains indexes whose key columns change; DELETE removes keys
• Space and ops — indexes consume disk; COPY/REORG/RECOVER cover index spaces too
• Wrong indexes — unused indexes still cost writes; missing indexes cost scans

Design indexes from workload, not from “index every column.” Start with primary / unique needs, add secondary indexes that EXPLAIN and monitoring prove useful, and drop indexes that only slow overnight batch loads.

Simple CREATE INDEX mental model

Checklist when you create an index:

• On which table? — base table, not a view
• Which columns (or expression)? — left-to-right order matters for composite keys
• UNIQUE? — reject duplicates when required
• CLUSTER? — influence data organization (one clustering index concepts later)
• Where? — index space in the table’s database; storage group / buffer pool via defaults or clauses

1
2
3
4
5
6
CREATE INDEX HR.XEMP_DEPT
  ON HR.EMPLOYEE (WORKDEPT)
  USING STOGROUP APPSTO
  PRIQTY 40 SECQTY 20
  ERASE NO
  FREEPAGE 10 PCTFREE 5;

You do not need every clause on day one. The core idea is: name the index, name the table and key columns, decide uniqueness, and let Db2 place the index space (or guide it with storage options). After creation, RUNSTATS keeps the optimizer informed so it can choose the index wisely.

What CREATE INDEX is not

It is not a guarantee the optimizer will always use the index. Skewed data, bad stats, or a query that selects most of the table can make a tablespace scan cheaper. It is also not a substitute for good predicates—functions wrapped around columns can prevent index matching until you use expression-based indexes deliberately.

Explain It Like I'm Five

A huge toy box is hard to search. An index is a little notebook that says “red cars → shelf 3.” Finding a red car is fast. But every time you add, move, or throw away a car, you must update the notebook. Too many notebooks make cleaning day slow. The special notebook that says each toy has its own number is like a primary index; extra notebooks for color or size are like secondary indexes.

Exercises

1. Write CREATE INDEX for LASTNAME on EMPLOYEE (non-unique) and EMPNO (unique).
2. Explain one batch job that might run slower after you add five secondary indexes.
3. Why can’t you CREATE INDEX on a view that joins EMP and DEPT?
4. In your own words, how is a primary key different from a primary index?
5. Give a query that would likely benefit from an index on WORKDEPT.