Database-Specific Parameters for DB2 for Linux, Unix, and Windows

Row Compression

Use

Data row compression aims to save disk storage space. It can also lead to disk I/O savings. However, there is an associated cost by means of extra CPU cycles that are needed to compress and decompress the data.

Row compression is preferably used for large or very large tables.

Description

Data row compression uses a static dictionary-based compression algorithm to compress data by means of rows. Compressing data at row level allows replacing the repeated patterns that span multiple column values within a row with shorter symbol strings.

By default, row compression is disabled.

Syntax

<rowcompression>true</rowcompression> - enables row compression
<rowcompression>false</rowcompression> - disables row compression

Volatile

Use

A volatile table is defined as a table with content that can vary from empty to very large at runtime.

Description

The volatility (extreme changeability) of tables makes the statistics collected by RUNSTATS inaccurate. Statistics are gathered at, and only reflect, a point of time. Generating an access plan that uses a volatile table can result in an incorrect or poorly performing plan.

By declaring a table volatile, the optimizer considers using index scan rather than table scan. The access plans that use declared volatile tables do not depend on the existing statistics for these tables.

By default, table volatility is disabled.

Syntax

<volatile>true</volatile> - makes a table volatile
<volatile>false</volatile>

Multi-Dimensional Clustering (MDC)

Use

MDC can significantly improve query performance and reduce the overhead of data maintenance such as reorganization and index maintenance during INSERT , UPDATE and DELETE operations.

MDC is primarily intended for data warehousing and large database environments.

Description

MDC provides an elegant method for clustering table data along multiple dimensions in a flexible, continuous and automatic way. Pages of the table are arranged in blocks of equal sizes, which is the extent size of the table space, and all rows of each block contain the same combination of dimension values. A clustering block index is automatically maintained for each specified dimension. A block index consisting of all columns used in the clause is maintained if none of the clustering block indexes includes these columns. The set of columns used in the ORGANIZE BY clause must follow the rules for the CREATE INDEX statement.

As a restriction, Java Dictionary supports only single columns as dimensions. Column groups are not supported.

Syntax

                              <mdc-dimensions>
        <columns>
                <column name="COL1">
                </column>
                <column name="COL2">
                </column>
        </columns>
</mdc-dimensions>

Database Partitioning

Use

A “database partition” is a part of a database that consists of its own data, indexes, configuration files, and transaction logs. A “partitioned database” is a database with two or more partitions.

Tables can then be located in one or more database partitions. Processors which are associated with each database partition are used to execute table requests. Data retrieval and update requests are decomposed automatically into subrequests, and executed in parallel among the applicable database partitions.

Description

The partitioning-key element enables the use of the default hashing function on the columns specified. It is also called a “distribution key” , as it constitutes the distribution method across database partitions. Columns that are used as parts of a distribution key must have a data type LONG VARCHAR , LONG VARGRAPHIC , BLOB , CLOB , DBCLOB , XML , or any type based on one of these, or a structured type.

Any unique or primary key constraint that is defined on the table must be a superset of the distribution key.

Syntax

                              <partitioning-key>
        <columns>
                <column name="COL1">
                </column>
                <column name="COL2">
                </column>
        </columns>
</partitioning-key>

Clustered Index

Use

Only one clustering index may exist for a table. Therefore, a clustered index cannot be specified if it has already been used in the definition of any existing index of the table.

This setting applies to primary and secondary indexes.

Description

While data is inserted into the associated table, the cluster factor of a clustering index is improved dynamically. This happens by means of attempts to insert new rows that are physically close to the rows, for which the key values of the clustering index are in the same range.

Syntax

<is-clustered>true</is-clustered> - enables clustering for the particular table index
<is-clustered>false</is-clustered> - disables clustering for the particular table index

Include Columns

Use

This element appends additional columns to the set of index key columns. These included columns can improve the performance of some queries through index access.

The setting applies to primary and secondary indexes.

Description

The columns included in this element are not used to enforce uniqueness. Uniqueness must be specified explicitly.

Syntax

                              <include-columns>
        <columns>
                <column name="COL1">
                </column>
                <column name="COL2">
                </column>
        </columns>
</include-columns>