CREATE INDEX in DB2 for z/OS

What an INDEX is

Table data lives in the table space. An index lives in its own index space (a linear page set). Internally it is a B-tree: you start at a root page, walk non-leaf pages, and land on a leaf page that holds keys and pointers (RIDs) to table rows. SQL almost never names the index. You write WHERE LASTNAME = 'HAAS'; the optimizer may choose the index.

CREATE INDEX

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE UNIQUE INDEX HR.EMPLOYEE_PK
  ON HR.EMPLOYEE (EMPNO)
  CLUSTER
  USING STOGROUP HRSTO
  PRIQTY 48 SECQTY 48
  ERASE NO
  COPY YES
  BUFFERPOOL BP1
  CLOSE YES;
 
CREATE INDEX HR.EMP_DEPT_IX
  ON HR.EMPLOYEE (WORKDEPT, LASTNAME)
  USING STOGROUP HRSTO
  PRIQTY 24 SECQTY 24
  COPY YES;

Core pieces:

• Index name — qualified, unique in the schema
• ON table (key columns) — order matters; leading columns must be in predicates for matching
• ASC / DESC / RANDOM — key order; RANDOM can reduce hot-page contention on sequential keys
• USING STOGROUP — Db2-managed data sets (or VCAT for user-managed)
• COPY YES — allows image copies of the index (recovery)
• DEFER YES — define the index but build it later with REBUILD INDEX (useful for load jobs)
• PIECESIZE — max size of each data set piece for non-partitioned indexes

FREEPAGE and PCTFREE leave holes for later inserts in leaf pages so the index splits less often. PADDED versus NOT PADDED controls whether VARCHAR key values are padded to maximum length in the index. NOT PADDED saves space and is common on modern systems; some older access paths assumed PADDED.

Unique and non-unique indexes

UNIQUE rejects a second row with the same key. Unique indexes implement PRIMARY KEY and UNIQUE constraints. Nulls in unique indexes are treated as equal for uniqueness unless the index is UNIQUE WHERE NOT NULL (one null key allowed when that clause is used).

Non-unique indexes exist to find rows faster. Duplicate WORKDEPT values are expected. The leaf page holds multiple RIDs per key. Too many duplicates make the index less selective; RUNSTATS COLCARDF tells the optimizer.

UNIQUE INCLUDE (column, …) lets uniqueness apply to the key while extra columns sit in the index for index-only access without being part of the uniqueness test.

Clustering indexes

A table has at most one clustering index. CREATE INDEX … CLUSTER (or ALTER INDEX … CLUSTER) names it. Inserts try to place new rows near the same key. REORG TABLESPACE reclusters data in that key order. Clustering is about data page order, not about uniqueness. The primary key index is often also CLUSTER, but it need not be—sometimes you cluster on (WORKDEPT, LASTNAME) because department reports scan ranges.

ALTER INDEX … CLUSTER takes effect for subsequent inserts immediately. Existing data stays in the old order until REORG. ALTER INDEX … NOT CLUSTER stops using that index as clustering for new inserts once another index is CLUSTER, but REORG may still follow the old clustering index until you switch explicitly—read the ALTER INDEX rules when you swap clustering.

Partitioned and non-partitioned indexes

On a partitioned table:

• Partitioning index — historically the index that held limit keys (index-controlled partitioning). Modern designs use table-controlled partitioning instead.
• DPSI (data-partitioned secondary index) — CREATE INDEX … PARTITIONED. Each table partition has a matching index partition. Partition-level utilities and SQL against one partition contend less with other partitions.
• NPSI / NPI (non-partitioned secondary index) — omit PARTITIONED. One index covers all partitions. Great for lookups by a key that is not the partitioning key; less great for partition-level REORG/LOAD independence because the NPI is shared.

Choose DPSI when queries always include the partitioning key (or utilities must be partition-independent). Choose NPSI when you look up by employee number on a table partitioned by hire year. You will have both kinds in a real schema.

Index-only access

If the index contains every column the query needs, Db2 can skip data pages: index-only (index covering). That cuts I/O. INCLUDE COLUMN adds non-key columns to unique indexes for this reason.

1
2
3
4
5
6
7
8
CREATE UNIQUE INDEX HR.EMP_PK_COVER
  ON HR.EMPLOYEE (EMPNO)
  INCLUDE (LASTNAME, WORKDEPT);
 
-- Index-only possible:
SELECT EMPNO, LASTNAME, WORKDEPT
  FROM HR.EMPLOYEE
 WHERE EMPNO = '000010';

EXPLAIN shows INDEXONLY YES (or equivalent) when this happens. Adding INCLUDE columns widens leaf pages; do not cover entire rows “just in case.”

XML indexes

XML columns are not ordinary VARCHAR keys. CREATE INDEX … GENERATE KEY USING XMLPATTERN (XML index) extracts values from XML documents so XMLEXISTS and XMLQUERY can use an index. XML indexes are not partitioned like DPSIs in the same way; they have their own pattern and data-type clauses. You do not CREATE INDEX (XMLCOL) as if it were CHAR. Use the XML index syntax when the workload searches inside documents.

Index compression

COMPRESS YES on CREATE INDEX or ALTER INDEX compresses index pages. The buffer pool for a compressed index must be 8 KB, 16 KB, or 32 KB, not 4 KB. Compression trades CPU for disk and sometimes for buffer-pool density. Measure with real key distributions; random unique integers compress worse than repeating department codes. ALTER INDEX COMPRESS YES is often pending until REORG INDEX.

ALTER INDEX

ALTER INDEX can change BUFFERPOOL, CLOSE YES/NO, COPY YES/NO, PIECESIZE, DSSIZE (for relative page numbering partitioned indexes), CLUSTER / NOT CLUSTER, PADDED / NOT PADDED, COMPRESS, ADD COLUMN to the key, and INCLUDE COLUMN. Partition-level USING, FREEPAGE, and GBP cache options exist for partitioned indexes.

1
2
3
4
5
6
ALTER INDEX HR.EMP_DEPT_IX
  COPY YES
  COMPRESS YES;
 
ALTER INDEX HR.EMP_DEPT_IX
  CLUSTER;

REGENERATE rebuilds the index definition for certain versioning situations. Pending alters on indexes follow the same “REORG INDEX or REBUILD to materialize” story as tables.

Index rebuild, leaf pages, levels, pseudo-deletes

REBUILD INDEX reads the table and reconstructs the index from scratch. Use it after DEFER YES, after a recovery that left the index unusable, or when CHECK INDEX fails. REORG INDEX reorganizes the existing index, reclaims space, and can CLEANUP pseudo-deleted entries.

A leaf page is the bottom of the B-tree: keys and RIDs. When a leaf fills, it splits; that costs CPU and can unbalance page fill. FREEPAGE/PCTFREE and clustering of inserts reduce splits. Upper pages are non-leaf. The catalog column NLEVELS (SYSIBM.SYSINDEXES) is the current height. One extra level means another page touch per lookup. Huge indexes with tiny keys still stay at 3–4 levels; huge keys and low fanout grow taller.

When you DELETE a row, Db2 may pseudo-delete the index entry: the key is marked deleted rather than immediately removed. Readers with old claims can still behave correctly. Too many pseudo-deleted entries waste leaf space and slow scans. Index cleanup (automatic in later Db2) and REORG INDEX CLEANUP / CLEANUP ALL reclaim them. If an index looks bloated after mass DELETE, do not assume you need a wider PIECESIZE until you check pseudo-delete counts in RTS (real-time statistics).

Indexes also have COPY, recover, and CHECK INDEX utilities. COPY YES is cheap insurance. An index without copies may force REBUILD INDEX from the table after media failure, which is slower than RECOVER INDEX from an image copy plus logs.

When not to create another index

Every INSERT, UPDATE of a key column, and DELETE maintains each index. A table with twelve secondary indexes can make a simple INSERT twelve extra B-tree walks. Create an index when EXPLAIN shows a tablespace scan you cannot afford, when a unique business rule needs enforcement, or when clustering would cut I/O for a dominant range query. Do not create an index for a report that runs once a year on a small table.

Duplicate indexes (same leading columns) waste write cost. If you already have (EMPNO) unique, a second (EMPNO, LASTNAME) non-unique index is only justified if index-only access on LASTNAME is proven—and INCLUDE on the unique index may be cheaper. RUNSTATS and EXPLAIN beat folklore.

After CREATE INDEX on a populated table, Db2 builds the index (unless DEFER YES). That build can be long and can drain the table. DEFER YES plus REBUILD INDEX in a utility window is the batch-friendly pattern. CREATE INDEX on an empty table before LOAD SHRLEVEL NONE, or LOAD then REBUILD, according to your load recipe. Mixing COPY NO indexes with a recovery strategy that assumed COPY YES leaves you rebuilding from the table after a disk failure.

Buffer pools for indexes are often separate from data (BP1 versus BP2 in many shops) so index pages are not flushed by sequential table scans. ALTER INDEX BUFFERPOOL moves the index; you still need enough VPSIZE. Compression requires 8 KB or larger pools. Putting a compressed index in BP0 (4 KB) fails.

CLOSE YES versus CLOSE NO controls whether Db2 closes the underlying data set when the index is not in use. CLOSE NO keeps frequently used indexes open (pseudo-close behavior still applies under DSNZPARM). COPY YES is independent: it means the COPY utility is allowed. New recovery-critical unique indexes should be COPY YES from day one.

Explain It Like I'm Five

The table is a big box of papers. An index is the card catalog: each card has a name and a drawer number (RID). UNIQUE means two cards cannot have the same name. CLUSTER means you try to stuff papers into the box in card-catalog order so a range of names is one handful of paper. Partitioned catalogs (DPSI) are one mini-catalog per drawer; non-partitioned (NPSI) is one giant catalog for the whole library. Index-only means you answered the question from the cards without opening the papers. Sometimes a card is marked “thrown away” but still sits in the drawer (pseudo-delete) until cleanup day (REORG).

Exercises

1. Write CREATE UNIQUE INDEX on EMPNO with CLUSTER and COPY YES.
2. Write a non-unique index on (WORKDEPT, LASTNAME).
3. Explain when you want a DPSI versus an NPSI.
4. Add INCLUDE (LASTNAME) to a unique EMPNO index and say which SELECT can be index-only.
5. Name the utility that rebuilds an index from the table versus the one that reorganizes the index and cleans pseudo-deletes.