MDC and ITC considerations when loading data
- 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 or ITC tables. If a load is executed into an MDC or ITC table without theanyorder
modifier, it will be explicitly enabled by the utility.
- If the table included a unique key before 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 before 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 with more than one dimension, 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 or ITC load operations always have a build phase since all MDC and ITC 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 and ITC 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. ITC tables only have one cell and use a 2-byte 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.