1 More Addition to Improve DB2 Insert Performance

Change Career-BIg Data-How?
Change Career-BIg Data-How?

1 More Addition in DB2 V11 to improve insert performance:

Having to index every data row affects performance and the size of the index. When creating an index, it is useful to exclude one or more values from being indexed, such as values that will never be used in a query, for example NULL, blank, and 0.

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.

Author: Srini

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