Blog
DB2utor

Categories

February 02, 2010

Unique Data-Partitioned Secondary Indexes

The DB2 Version 8 Administration Guide (page 54) introduces and defines the different DB2 index types.

As of DB2 9 for z/OS, the explanation of index types was moved from the administration guide to the "Introduction to DB2 for z/OS" guide. I believe the information and visual examples are vastly improved in this guide.

The many different index types are defined based on table type. I'll focus here on those indexes that are on partitioned tables.

Any table can have these index types:
•    unique/non-unique
•    clustering (one per table)/non-clustering
•    Padded / not-padded

A partitioned table can have these index types:
•    partitioning
•    secondary

When a partitioned table contains a secondary index, that's when the confusion comes into play. For starters, a secondary index can be:
•    Non-partitioned secondary index (NPSI)
•    Data-partitioned secondary index (DPSI)

The differences between partitioning and partitioned indexes can be even more confusing.

•    Partitioning index: A partitioning index is defined where the left-most columns are the partitioning column of the table; the index can, but need not, be partitioned.

•    Partitioned index: When an index is created with the "partition" keyword, that index is physically partitioned based on the partitioning columns. Both partitioning and secondary indexes can be partitioned. 

Secondary Index
Indexes on a partitioned table can be either partitioning or secondary. A secondary index can be partitioned (DPSI) or non-partitioned (NPSI). Page 214 of the "Introduction to DB2 for z/OS" guide has a very good visual representation of this.

The main source of confusion centers on the question of whether you can create a unique DPSI.

With DB2 8, if your create index statement includes both PARTITIONED and UNIQUE, you'll receive DB2 sqlcode -628. The error text is:

UNIQUE and UNIQUE WHERE NOT NULL cannot be specified with PARTITIONED on CREATE INDEX.

This restriction is removed in DB2 9 -- provided the create index statement contains the entire set of columns used for partitioning. If it doesn't, you'll receive sqlcode -628 with this text:

UNIQUE or UNIQUE WHERE NOT NULL were specified with PARTITIONED on CREATE INDEX. You cannot specify UNIQUE or UNIQUE WHERE NOT NULL with PARTITIONED on CREATE INDEX unless the index includes all the partitioning columns.

Data-Partitioned Secondary Index
Given the following create table statement with table partitioning, note that the create index column order isn't the same as in the partitioning columns:

CREATE  TABLE COLTR05.T1      
        ( COL1 CHAR ( 10 )    
        , COL2 INTEGER        
        , COL3 CHAR ( 5 )     
        , COL4 CHAR ( 25 )    
        )                     
        IN dbname.tsname                
PARTITION BY                  
        ( COL1, COL2 )                     
        ( PARTITION 1          ENDING AT ( 'A', 1 )
        , PARTITION 2          ENDING AT ( 'B', 2 )
        , PARTITION 3          ENDING AT ( 'C', 3 )
        );

CREATE  UNIQUE INDEX COLTR05."T1_X1_4231"  
        ON COLTR05.T1                      
        ( COL4 ASC, COL2 ASC, COL3 ASC, COL1 ASC )                                
           PARTITIONED                     
;

As long as you have all of the columns defined from the partition by clause in the create unique index, the create will be successful. However, leaving one column out will trigger sqlcode -628.