Modification state indexes

A modification state index is a system-generated index that is used in the implementation of currently committed semantics for index scans on column-organized tables.

A modification state index is created as a result of one of the following actions on a column-organized table:
  • An index is explicitly created by using the CREATE INDEX statement.
  • An attempt is made to create an index that already exists. In this case, warning message SQL0605W is returned, but the modification state index is created.
  • A primary or unique key is created and the DB2_EXTEND_COL_UNIQUE_INDEX_ACCESS registry variable is set to 'ON'.

The modification state index is created when the first CREATE INDEX statement is run on a column-organized table.

By default, the modification state index is not created when you create a unique or primary key constraint. If the registry variable DB2_EXTEND_COL_UNIQUE_INDEX_ACCESS is set to 'ON', then the modification state index is generated when you create an enforced primary key or unique constraint.

You can increase the number of index access plans that use your existing indexes. To do this, create the modification state index by issuing a CREATE INDEX statement with a definition that matches your primary or unique index. An example of this procedure is provided in the usage scenario. See Explain information for column-organized tables for details about the new types of index access plans that are possible.

Usage scenario

In this usage scenario, a modification state index is generated when you attempt to create an index that matches the index of a primary key or unique constraint that already exists. Follow this procedure to have your existing indexes used by more types of SQL statements.
  1. Get the name of the index and verify that an index with type MDST does not exist.
    db2 "select substr(indname,1,25) indname, indextype from syscat.indexes
         where tabschema='TEST1' and tabname = 'T1' and indextype in ('REG','MDST')"
    This query returns the following output:
    INDNAME                   INDEXTYPE
    ------------------------- ---------
    SQL171120105642950        REG
  2. Get the column names in the index.
    db2 "select substr(indname,1,35) indname, substr(colname, 1, 10) colname, COLSEQ, COLORDER
         from syscat.indexcoluse where indname = 'SQL171120105642950'"
    
    This query returns the following output:
    INDNAME                             COLNAME    COLSEQ COLORDER
    ----------------------------------- ---------- ------ --------
    SQL171120105642950                  COL2            1 A
    SQL171120105642950                  COL1            2 A
    
  3. Create an index that uses the same columns as the primary key or unique constraint.
    db2 "create index I1 on TEST1.T1 (col2,col1)"
    
    SQL0605W  The index was not created because an index
    "SYSIBM.SQL171120105642950" with a matching definition already exists.
    SQLSTATE=01550
  4. Verify that the modification state index was created, but no regular index was created.
    db2 "select substr(indname,1,25) indname, indextype from syscat.indexes
         where tabschema='TEST1' and tabname = 'T1' and indextype in ('REG','MDST')"
    
    This query returns the following output:
    INDNAME                   INDEXTYPE
    ------------------------- ---------
    SQL171120105642950        REG
    SQL171120105649725388     MDST