Excluding null keys from an index
To save index space and to improve INSERT and query performance, you can redefine an index so that it no longer contains null keys.
About this task
In this example, the index IXFGR2 was originally created with null keys.
Procedure
- On the DB2 Administration Menu (ADB2) panel, select option 1, and press Enter.
- On the System Catalog (ADB21) panel, select option X, and press Enter.
- On the Indexes (ADB21X) panel, issue the ALT line command next to the index that you want to redefine.
-
On the Redefine Index
(ADB21XAR) panel, type
YES in the Exclude Null Keys field and
press Enter:
Figure 1. Redefine Index (ADB21XAR) panel - Redefining Exclude Null Keys attribute ADB21XAR --------------- DD1A Redefine Index ---------------- Row 1 to 2 of 2 Command ===> Scroll ===> CSR Commands: NEXT ORIGINAL Line commands: nnn A|D - Sequence & order R - Remove the column I - Include A - Ascending D - Descending RA - Random U - Update expression/XML pattern ? - Show all line commands CREATE INDEX RIVERAF . IXFGR2 > ON RIVERAF.TBFGR2 Owner . . . . . . RIVERAG > Owner type . . . . . _ (U/R) Unique . . . . . . . YES Where Not Null . . . Cluster . . . . . NO Buffer Pool . . . . BP1 Close Rule . . . . . YES Copy Allowed . . NO Piece Size . . . . . 2097152 Define . . . . . . . YES Defer . . . . . . Partitioned . . . . Padded . . . . . . . Compress . . . . NO Exclude Null Keys . YES Select Column Name Col Type Length Scale N ColSeq Ord OldSeq Ord * * * * * * * * * ------ ------------------ -------- ----------- ------ - ------ --- ------ --- A INTEGER 4 0 N 1 A 1 A B CHAR 3 0 Y ******************************* END OF DB2 DATA *******************************
- Issue the NEXT primary command, and press Enter.
- On the Redefine Index - Space (ADB21XAS ) panel, issue NEXT again, and press Enter.
-
On the Alter Objects
(ADB27CA) panel, issue the
ALTOPT command, and press Enter:
Figure 2. Alter Objects (ADB27CA) panel ADB27CA n ---------------------- DD1A Alter Objects ---------- Row 1 to 1 of 1 Command ===> ALTOPT Scroll ===> CSR Commands: NEXT - Generate jobs ADD - Add objects ALTOPT - Change alter options Line commands: A - Alter object D - Delete S - Select object REL - Alter related FK - Add FK-affected tables RI - Add RI-related tables E - Edit view DDL ? - Show all line commands Object Object RI RI FK Sel Qual Name Ty Info 1 Info 2 Rels Add Add Operation * * * * * * * * * --- -------> -----------------> -- -------> -------> ------ --- --- ----------- RIVERAF IXFGR2 IX RIVERAF TBFGR2 NA NA MODIFY ******************************* END OF DB2 DATA *******************************
- On the ALTER Options Menu (ADBP7OP) panel, select option 1, and press Enter.
- On the ALTER Analysis Options (ADBP7P) panel, type YES in the Perform analysis in batch field.
- Exit back to the Alter Objects (ADB27CA) panel.
- Generate an ALT job.