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. There is at most one modification state index per table.

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.
  • An enforced primary or unique key is created.

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

A table might not have a modification state index under certain conditions:
  • If the table was created with Db2 11.1.2.2 or earlier.
  • If the DB2_EXTEND_COL_UNIQUE_INDEX_ACCESS registry variable was set to NO when a unique or primary key constraint was created on the table.

You can increase the number of index access plans that use your existing indexes for tables that don't have a modification state index. 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