DB2 Version 9.7 for Linux, UNIX, and Windows

Multidimensional clustering considerations when loading data

The following restrictions apply when loading data into multidimensional clustering (MDC) tables:

The SAVECOUNT option of the LOAD command is not supported.
The totalfreespace file type modifier is not supported since these tables manage their own free space.
The anyorder file type modifier is required for MDC tables. If a load is executed into an MDC table without the anyorder modifier, it will be explicitly enabled by the utility.

When using the LOAD command with an MDC table, violations of unique constraints are be handled as follows:

If the table included a unique key prior to the load operation and duplicate records are loaded into the table, the original record remains and the new records are deleted during the delete phase.
If the table did not include a unique key prior to the load operation and both a unique key and duplicate records are loaded into the table, only one of the records with the unique key is loaded and the others are deleted during the delete phase.
Note: There is no explicit technique for determining which record is loaded and which is deleted.

Performance Considerations

To improve the performance of the load utility when loading MDC tables, the util_heap_sz database configuration parameter value should be increased. The mdc-load algorithm performs significantly better when more memory is available to the utility. This reduces disk I/O during the clustering of data that is performed during the load phase. Beginning in version 9.5, the value of the DATA BUFFER option of the LOAD command can temporarily exceed util_heap_sz if more memory is available in the system. .

MDC load operations always have a build phase since all MDC tables have block indexes.

During the load phase, extra logging for the maintenance of the block map is performed. There are approximately two extra log records per extent allocated. To ensure good performance, the logbufsz database configuration parameter should be set to a value that takes this into account.

A system temporary table with an index is used to load data into MDC tables. The size of the table is proportional to the number of distinct cells loaded. The size of each row in the table is proportional to the size of the MDC dimension key. To minimize disk I/O caused by the manipulation of this table during a load operation, ensure that the buffer pool for the temporary table space is large enough.