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

  1. On the DB2 Administration Menu (ADB2) panel, select option 1, and press Enter.
  2. On the System Catalog (ADB21) panel, select option X, and press Enter.
  3. On the Indexes (ADB21X) panel, issue the ALT line command next to the index that you want to redefine.
  4. On the Redefine Index (ADB21XAR) panel, type YES in the Exclude Null Keys field and press Enter:
    Start of change
    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 *******************************
    End of change
  5. Issue the NEXT primary command, and press Enter.
  6. On the Redefine Index - Space (ADB21XAS ) panel, issue NEXT again, and press Enter.
  7. 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 *******************************
  8. On the ALTER Options Menu (ADBP7OP) panel, select option 1, and press Enter.
  9. On the ALTER Analysis Options (ADBP7P) panel, type YES in the Perform analysis in batch field.
  10. Exit back to the Alter Objects (ADB27CA) panel.
  11. Generate an ALT job.