DB2 hands-on: create an index

What you are building

Assume the previous hands-on lessons created TRAINING.EMPLOYEE and inserted sample rows. It contains EMPNO, FIRSTNAME, LASTNAME, WORKDEPT, HIREDATE, and SALARY. A common report filters employees by department, then displays surnames in order. Without a suitable index, Db2 may scan the table and sort the qualifying rows. We will create an index whose leading key is WORKDEPT and whose second key is LASTNAME.

An index is a separate B-tree structure containing ordered keys and row identifiers. Db2 starts near the root, follows branches, reaches leaf pages, and uses row identifiers to retrieve table rows. That can replace a large table scan with a smaller search. However, every index also consumes storage and must be maintained by INSERT, UPDATE, and DELETE. The right question is not “Can I index this column?” but “Does this index save enough read work to justify its write and operational cost?”

Prerequisites and authority

Complete the table and sample-data lessons first, or substitute an equivalent table in a development subsystem. Confirm the exact qualified table name and column definitions. Test DDL outside production. Creating an index on a populated large table can perform substantial work, consume storage, interact with concurrent access, and require a controlled utility or change window.

Authorization is installation-dependent. The executing authorization ID needs authority to create the index in the chosen schema and authority associated with the target table or database. Depending on ownership and security design, that can come from object privileges such as INDEX, database authority, schema authority, or an administrative authority. Do not assume that SELECT privilege permits DDL. Ask the security or DBA team for the least privilege required by your site, and let the Db2 diagnostic identify a missing privilege rather than requesting broad SYSADM access.

CREATE INDEX syntax

The portable core of the statement is the index name, target table, and ordered key list. Db2 for z/OS supports many additional storage and operational clauses. This tutorial starts with a deliberately small definition so the design intent remains clear.

1
2
3
CREATE INDEX TRAINING.IX_EMP_DEPT_NAME
  ON TRAINING.EMPLOYEE
     (WORKDEPT ASC, LASTNAME ASC);

Use a qualified index name. The qualifier is the schema, not the table name, although many shops use the same schema for related objects. The name must be unique where Db2 requires it, fit the identifier rules for your subsystem, and follow local naming standards. A descriptive name such as IX_EMP_DEPT_NAME communicates object type, table, and key purpose. Avoid names such as INDEX1 that become meaningless during incident response.

General statement shape

1
2
3
4
5
6
7
CREATE [UNIQUE] INDEX schema.index_name
  ON schema.table_name
     (key_column [ASC|DESC], ...)
  [INCLUDE (included_column, ...)]
  [CLUSTER]
  [PARTITIONED]
  [other storage and operational options];

Brackets in the example describe optional syntax; do not type the brackets. Exact options vary with the Db2 release, function level, table-space design, and site standards. For production DDL, use the IBM syntax for your installed level and a DBA-approved template for storage group, buffer pool, COPY, FREEPAGE, PCTFREE, and related choices.

Unique versus nonunique

The department-and-name index is intentionally nonunique because several employees can share the same department and surname. Adding UNIQUE merely because it sounds faster would encode a false business rule and reject legitimate rows. Use UNIQUE only when the complete key must not repeat, such as a trusted employee number or another candidate key.

1
2
CREATE UNIQUE INDEX TRAINING.UX_EMP_EMPNO
  ON TRAINING.EMPLOYEE (EMPNO ASC);

A unique index and a PRIMARY KEY or UNIQUE constraint are related but are not identical concepts. The constraint expresses the relational business rule; an index can be the physical structure that supports enforcement. Coordinate with the table owner before creating an independent unique index where a formal constraint should be defined.

Choose index keys and order deliberately

In a composite index, the first column is the leading key. The tree for (WORKDEPT, LASTNAME) groups rows by department and then sorts names inside each department. It naturally supports WORKDEPT = ? and can continue matching LASTNAME. It can also help return department results in surname order. A predicate on LASTNAME alone does not supply the leading key, so this index is usually less effective for that access pattern.

1
2
3
4
5
6
7
8
9
10
-- Good fit for IX_EMP_DEPT_NAME
SELECT EMPNO, LASTNAME, WORKDEPT
  FROM TRAINING.EMPLOYEE
 WHERE WORKDEPT = 'A00'
 ORDER BY LASTNAME;
 
-- Different access pattern: do not assume the same index is ideal
SELECT EMPNO, LASTNAME
  FROM TRAINING.EMPLOYEE
 WHERE LASTNAME = 'SMITH';

Put columns with equality predicates before a range column when that arrangement matches the important workload, but do not rely on a slogan such as “most selective first.” Join predicates, ordering, grouping, range scans, screening, data distribution, and existing indexes all matter. ASC and DESC define key order. Db2 can sometimes scan an index backward, so a descending declaration is not automatically required for every descending query. Use EXPLAIN with realistic statements and parameter values.

Cover a query with INCLUDE

A unique index can carry extra columns for index-only access without making those columns part of its unique key. Suppose EMPNO is unique, while frequent lookups also return LASTNAME and WORKDEPT. INCLUDE can place those values in the leaf structure.

1
2
3
4
5
6
7
CREATE UNIQUE INDEX TRAINING.UX_EMP_EMPNO_COVER
  ON TRAINING.EMPLOYEE (EMPNO)
  INCLUDE (LASTNAME, WORKDEPT);
 
SELECT EMPNO, LASTNAME, WORKDEPT
  FROM TRAINING.EMPLOYEE
 WHERE EMPNO = '000010';

The uniqueness test still applies only to EMPNO. LASTNAME and WORKDEPT are payload for coverage, not additional search-key positions. If EXPLAIN reports index-only access, Db2 may answer without reading a table data page. Coverage is not free: wider leaf rows increase index size, reduce the number of entries per page, and add maintenance work. Include only columns supported by measured, important queries.

Clustering at a practical level

CLUSTER identifies the one index whose key order Db2 prefers for physical row placement. A subsequent REORG TABLESPACE can arrange existing rows in clustering order, which helps range access read nearby rows from nearby pages. Creating or changing a clustering index does not instantly rewrite every existing data page. Inserts are influenced by the clustering choice, while established data normally needs REORG to become well clustered.

1
2
3
CREATE INDEX TRAINING.IX_EMP_DEPT_NAME
  ON TRAINING.EMPLOYEE (WORKDEPT, LASTNAME)
  CLUSTER;

Do not add CLUSTER casually. A table can have only one clustering index, so the choice should represent the dominant range or sequential access pattern. Clustering does not imply uniqueness, and a primary-key index does not have to be the clustering index. Changing the choice affects REORG planning and can shift performance among workloads.

Partitioning at a conceptual level

On a partitioned table, a data-partitioned secondary index has index partitions aligned with table partitions. This design can improve partition independence for utilities and operations, but a lookup may need to examine multiple index partitions when its predicates do not identify a table partition. A nonpartitioned secondary index spans the table's partitions and can support direct lookup by a key unrelated to the partitioning key, but it introduces a shared object that affects partition-level utility independence.

The PARTITIONED choice therefore belongs to the complete table-space, partitioning, query, availability, and utility design. Do not copy it into this small exercise without a partitioned target table. On a real system, have the DBA compare DPSI and NPSI behavior for the predicates and maintenance model before selecting either form.

Run the DDL and collect RUNSTATS

Execute the approved CREATE INDEX through SPUFI, DSNTEP2, or your site's DDL deployment process. Record the SQLCODE and messages. A zero completion code means the statement succeeded, but it does not prove that the index improves the target query. On a populated table, building the index can be significant work; schedule it according to size and availability requirements.

After creation, collect representative optimizer statistics. RUNSTATS syntax and control cards are commonly managed by DBAs, and production shops may use profiles or automation. The following illustrates intent rather than a universal job card.

1
2
3
4
5
RUNSTATS TABLESPACE database-name.tablespace-name
  TABLE(ALL)
  INDEX(ALL)
  SHRLEVEL CHANGE
  REPORT YES

Statistics describe row counts, distinct values, key cardinality, clustering, and other properties used in cost estimates. Collecting INDEX(ALL) may be broader than necessary on a large production object, so use your site's approved scope. Stale or default values can lead Db2 to underestimate or overestimate the index's benefit.

Verify the catalog definition

Query the catalog to prove that the name, table relationship, uniqueness, clustering indicator, and key sequence match the design. Catalog columns differ across releases, so select only the fields available at your installed level or use your site's standard catalog query.

1
2
3
4
5
6
7
8
9
10
SELECT CREATOR, NAME, TBCREATOR, TBNAME, UNIQUERULE, CLUSTERING
  FROM SYSIBM.SYSINDEXES
 WHERE CREATOR = 'TRAINING'
   AND NAME = 'IX_EMP_DEPT_NAME';
 
SELECT IXCREATOR, IXNAME, COLNAME, COLSEQ, ORDERING
  FROM SYSIBM.SYSKEYS
 WHERE IXCREATOR = 'TRAINING'
   AND IXNAME = 'IX_EMP_DEPT_NAME'
 ORDER BY COLSEQ;

Verify usage with EXPLAIN

EXPLAIN the representative query before and after the change when possible. In PLAN_TABLE, ACCESSNAME commonly identifies the chosen index. ACCESSTYPE describes the access method, MATCHCOLS indicates how many leading key columns match, and INDEXONLY indicates whether table data access can be avoided. Field meanings depend on release and tooling, so interpret the complete explained step rather than treating one value as a pass/fail flag.

1
2
3
4
5
6
7
8
9
10
11
EXPLAIN PLAN SET QUERYNO = 4101 FOR
SELECT EMPNO, LASTNAME, WORKDEPT
  FROM TRAINING.EMPLOYEE
 WHERE WORKDEPT = 'A00'
 ORDER BY LASTNAME;
 
SELECT QUERYNO, QBLOCKNO, PLANNO, ACCESSTYPE,
       ACCESSCREATOR, ACCESSNAME, MATCHCOLS, INDEXONLY
  FROM PLAN_TABLE
 WHERE QUERYNO = 4101
 ORDER BY QBLOCKNO, PLANNO;

If the optimizer still chooses a table-space scan, that is evidence to investigate, not proof that Db2 is wrong. The table may be small, the predicate may retrieve most rows, statistics may be stale, the leading key may not match, another index may cost less, or the sort avoidance benefit may be too small. Compare estimated cost and actual workload behavior before changing the index again.

Common errors and how to respond

Duplicate keys

Creating a UNIQUE index over existing duplicate keys fails because Db2 cannot enforce the rule. Later INSERT or UPDATE statements that duplicate an established unique key commonly receive SQLCODE -803. Find duplicates with GROUP BY and HAVING, determine which rows are valid, correct the data through an approved process, and confirm that the proposed key is truly unique.

1
2
3
4
SELECT EMPNO, COUNT(*) AS DUPLICATE_COUNT
  FROM TRAINING.EMPLOYEE
 GROUP BY EMPNO
HAVING COUNT(*) > 1;

Naming and qualification errors

A duplicate object name can produce SQLCODE -601, while an unresolved table or other referenced object can produce SQLCODE -204. Check spelling, schema qualification, current SQLID assumptions, identifier length, and whether a deployment already created the index. Do not solve a naming collision by repeatedly adding random suffixes; first learn whether the existing object is the intended index.

Authority and definition errors

Authorization failures often appear as SQLCODE -551 or a related security diagnostic. Give the exact authorization ID, statement, object, and diagnostic to the security team. Undefined columns, incompatible key data types, excessive key length, unsupported clauses, unavailable objects, and invalid combinations produce different diagnostics. Read the full message and reason code for your Db2 level instead of guessing from SQLCODE alone.

• Qualify the table and index names explicitly.
• Check SYSINDEXES before creating a possibly duplicate access path.
• Validate source data before requesting UNIQUE.
• Use the exact IBM message text and reason code to diagnose failed DDL.
• Do not drop an existing index until dependencies and access-path effects are known.

Explain it like I'm 5

Imagine a large box of employee cards. Without an index, you may inspect every card to find everyone in department A00. An index is a smaller list arranged first by department and then by surname. You jump to A00 and read only that section. UNIQUE is a rule saying two cards cannot have the same special number. INCLUDE copies a few useful facts onto the small list so you do not need to open the big box. CLUSTER tries to arrange the cards in the big box like the list. RUNSTATS tells Db2 how the cards are distributed, and EXPLAIN shows which list Db2 plans to use.

Exercises

1. Create IX_EMP_DEPT_NAME on (WORKDEPT, LASTNAME), then query SYSIBM.SYSKEYS and confirm that WORKDEPT has COLSEQ 1.
2. EXPLAIN a query that filters WORKDEPT and orders by LASTNAME. Record ACCESSNAME, MATCHCOLS, and whether a sort is required.
3. Explain why the same index is not necessarily ideal for LASTNAME = 'SMITH' without a WORKDEPT predicate.
4. Write a duplicate-detection query for EMPNO before proposing a unique employee-number index. Describe how you would resolve any duplicates safely.
5. Design a unique EMPNO index that includes LASTNAME and WORKDEPT. Explain which columns enforce uniqueness and which columns exist only for coverage.
6. Compare a DPSI and an NPSI for a table partitioned by year but frequently searched by employee number. List one query benefit and one utility trade-off for each.