Advanced DB2 index options: INCLUDE, PADDED, COPY and DEFER

Include columns and INCLUDE

INCLUDE (column-name, …) appends extra columns to a unique index. Those columns are not used to enforce uniqueness. They exist so a query that filters on the unique key and selects the extra columns can be index-only: Db2 reads the index and never visits the data page.

Think of a primary-key index on EMPNO. Reports always SELECT LASTNAME and WORKDEPT with WHERE EMPNO = ?. Without INCLUDE, that is unique-index probe plus table access. With INCLUDE (LASTNAME, WORKDEPT), the leaf contains everything the query needs.

1
2
3
4
5
6
7
CREATE UNIQUE INDEX HR.XEMP1
     ON HR.EMP (EMPNO)
     INCLUDE (LASTNAME, WORKDEPT, HIREDATE)
     USING STOGROUP HRSTO
     PRIQTY 48 SECQTY 48
     COPY YES
     CLOSE NO;

Rules from the SQL Reference:

• UNIQUE must be specified. INCLUDE is illegal on a non-unique index.
• Included names must be unqualified base-table columns, distinct from the unique key and from each other.
• They count toward the maximum of 64 columns and toward the maximum key-length budget.
• LOB and DECFLOAT (and distinct types based on them) cannot be included.
• Character/graphic INCLUDE columns must use the same encoding scheme as other string key columns.

On a data-partitioned secondary index, UNIQUE is allowed only if the index columns are a superset of the partitioning columns, and those partitioning columns must be real key columns — not INCLUDE columns. Do not try to hide the partition key in INCLUDE.

INCLUDE is not a substitute for a second index. If you search by LASTNAME alone, this EMPNO unique index will not help; you still want XEMP2 (LASTNAME, …). INCLUDE helps queries that already match the unique key (or a left prefix of it) and then need extra columns.

PADDED and NOT PADDED

Varying-length string columns in an index key can be stored two ways:

If the index has no varying-length string columns, PADDED/NOT PADDED is ignored (warning). Physically the index is still a padded-style fixed key. Indexes on auxiliary tables are always padded; NOT PADDED is ignored there. PADDED cannot be specified with XMLPATTERN or on VARBINARY columns.

Default when the index has at least one VARCHAR/VARGRAPHIC: the PADIX subsystem parameter (“PAD INDEXES BY DEFAULT” on DSNTIPE). NO means new indexes are NOT PADDED unless you write PADDED. YES means the opposite.

1
2
3
4
5
6
CREATE INDEX HR.XEMP2
     ON HR.EMP (LASTNAME, FIRSTNME)
     NOT PADDED
     USING STOGROUP HRSTO
     PRIQTY 120 SECQTY 120
     CLOSE NO;

Why care? NOT PADDED stores short names in fewer bytes, so more keys per leaf, fewer levels, less GETPAGE. Length bytes travel with the key. Predicate evaluation can use the real string, which is what you want for index-only access.

PADDED stores every VARCHAR at its maximum length with the default pad character (usually blank). Key length is predictable. The downside IBM documents: padded indexes can prevent index-only access because the index does not hold the column the way the table does. You may still see a data-page touch for a query that looked coverable.

Mixed collating and comparisons with PADDED keys also surprise beginners: trailing blanks in the index representation are not the same as a short VARCHAR in the table. Prefer NOT PADDED unless you have a measured reason (and document PADIX so the next CREATE INDEX does not silently flip).

COPY

COPY YES or COPY NO on CREATE INDEX (and ALTER INDEX) answers: may the COPY utility take an image copy of this index, and may RECOVER restore it?

• COPY NO — default. No full image or concurrent copies. You do not RECOVER the index from a copy; you REBUILD INDEX from the table (and logs as appropriate). Fine for small indexes.
• COPY YES — allows full image or concurrent COPY and RECOVER on the index. Use it when rebuild time after a volume loss would miss the SLA.

Do not specify COPY on an index on a declared global temporary table. COPY YES has a cost: extra COPY jobs in the backup cycle, extra tape/DASD, and you must keep the index copies in the same recovery scenario as the table space (CHECK INDEX after recover is still your friend).

1
2
3
4
5
6
7
8
9
10
CREATE UNIQUE INDEX DSN8C10.XEMP1
     ON DSN8C10.EMP
     (EMPNO ASC)
     USING STOGROUP DSN8G120
     PRIQTY 512 SECQTY 64
     ERASE NO
     BUFFERPOOL BP1
     CLOSE YES
     PIECESIZE 1048576 K
     COPY YES;

That sample is in IBM’s CREATE INDEX examples: COPY YES together with an explicit PIECESIZE. Recovery design: table space COPY + index COPY YES means you can RECOVER both; COPY NO indexes are rebuilt after the table is recovered.

DEFER

DEFER says whether CREATE INDEX builds the keys during the statement.

• DEFER NO — default. The index is built. If a utility already has control of the table space, CREATE INDEX with DEFER NO cannot run.
• DEFER YES — catalog description and index space are created; keys are not built. If the table has rows, the index is placed in rebuild-pending (RBDP), a warning is issued, and you must run REBUILD INDEX. If the table is empty, Db2 neither builds nor sets RBDP; later inserts maintain the index as usual.

Do not specify DEFER for an index on a declared temporary table or an auxiliary table.

1
2
3
4
5
6
7
8
CREATE INDEX HR.XEMP_DEPT
     ON HR.EMP (WORKDEPT)
     DEFER YES
     COPY NO
     CLOSE NO;
 
-- Later, after LOAD or when you can afford the CPU:
-- REBUILD INDEX (HR.XEMP_DEPT) SORTDEVT SYSDA

Classic load window: LOAD the table with indexes deferred or create secondary indexes DEFER YES, then REBUILD INDEX in parallel with SORTNUM. Unique indexes still have to be valid before you rely on uniqueness for application traffic — RBDP indexes are not usable for SELECT.

DEFER is not DEFINE NO. DEFINE NO delays VSAM data-set creation until first insert. DEFER YES creates the data sets (unless DEFINE NO too) but leaves them empty of keys. You can combine them; know which pending state you will see (RBDP vs implicit define).

How the four clauses work together

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE UNIQUE INDEX APP.XORD_PK
     ON APP.ORDER_HDR (ORD_ID)
     INCLUDE (CUSTNO, CHANGE_TS)
     NOT PADDED
     USING STOGROUP APPSTO
     PRIQTY 720 SECQTY 360
     FREEPAGE 10
     PCTFREE 20
     BUFFERPOOL BP8K0
     CLOSE NO
     COPY YES
     DEFER NO
     PIECESIZE 4 G;

Unique covering index, compact VARCHAR keys (if any), recoverable by COPY, built immediately, NPI piece cap 4 G. FREEPAGE/PCTFREE still apply as on the free-space page: they take effect when the index is built or reorganized.

Catalog: SYSIBM.SYSINDEXES holds UNIQUERULE, PADDED, COPY, DEFER-related state, INCLUDE via KEYCOUNT / associated SYSKEYS columns with a column type that marks include versus key. DISPLAY INDEX shows RBDP when DEFER YES left work to do.

Explain It Like I'm Five

An index is a table of contents. INCLUDE is extra words written next to each chapter number so you do not have to open the book (index-only). PADDED is writing every title on a full-width line with spaces at the end; NOT PADDED is writing the title only as long as it really is. COPY YES is photocopying the table of contents so you can replace it if the page tears; COPY NO means you rebuild the table of contents by reading the whole book again. DEFER YES is printing the cover of the table of contents now and filling in the page numbers later with a REBUILD.

Exercises

1. Write a UNIQUE index on CUSTOMER(CUSTNO) INCLUDE (NAME, ZIP). Name two queries that become index-only and one that still needs a second index.
2. Explain why INCLUDE is rejected on CREATE INDEX … ON EMP (LASTNAME) without UNIQUE.
3. PADIX is NO. You omit PADDED/NOT PADDED on a LASTNAME VARCHAR index. What do you get, and when would you override to PADDED?
4. A 200 GB unique index is COPY NO. Estimate why a volume failure is worse than COPY YES, and what you add to the backup job stream.
5. CREATE INDEX DEFER YES on a 50-million-row table. What state is the index in, and which utility clears it?