DB2 INSERT’s Performance and What is Newly Added

In DB2, performance affects on the size of the index. When you create an index, it is useful to exclude one or more values from being indexed – such as values that will never be used in a SQL query. For example NULL, blank, and 0.

Improvement in Performance

DB2 11 NFM can improve insert performance of NULL entries by the option of excluding NULL rows from indexes.

The CREATE INDEX statement is changed to state EXCLUDE NULL KEYS, and the RUNSTATS utility collect statistics only on non-NULL value.

All table statistics derived from an index are adjusted by the number of excluded NULL values. Therefore the table statistics will be the same whether they were derived from a table scan, an EXCLUDE NULL KEYS index, or a non-EXCLUDE NULL KEYS index (or INCLUDE NULL KEYS index).

After converting existing indexes to EXCLUDE NULL indexes, monitor application performance. Insert performance should improve and query performance difference should be minimal.

Related post

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.