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.
- 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
- 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
- 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
- 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
- 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