Integrity Constraint Violation Messages

LOAD TABLE allows you to control load behavior when integrity constraints are violated and to selectively log information about the violations.

In Fast Projection (FP) indexes, continuous NBit dictionary compresion replaces FP(1), FP(2), and FP(3) byte dictionary compression. FP(1), FP(2), and FP(3) indexes roll over to NBit(8),NBit(16), and NBit(24) respectively. All data types except LOB (both character and binary) and BIT data types may be NBit columns.
If FP_NBIT_IQ15_COMPATIBILITY is OFF, IQ UNIQUE determines whether the column loads as Flat FP or NBit. Setting IQ UNIQUE to 0 loads the column as Flat FP. Columns without an IQ UNIQUE constraint load as NBit up to the NBit auto-sizing limits.
New tiered HG index structure decouples load performance from HG index size. In 15.x, load throughput could degrade as the amount of data in an HG index increased. As the index grew, loading the same amount of data could take more time. The new tiered structure decouples load performance from the HG index size to increase throughput.
The CREATE_HG_WITH_EXACT_DISTINCTS option determines whether newly created HG indexes are tiered or non-tiered. If this option is ON, all new HG indexes are non-tiered. To take advantage of the new structure, set this option to OFF. Use sp_iqrebuildindex to convert non-tiered HG indexes to tiered HG and vice-versa.

Using the MESSAGE LOG ... ROW LOG option with the ONLY LOG clause, you can direct the load to log information about specific types of integrity constraint violations both per violation in a message log file and per row in a row log file. If you do not specify the ONLY LOG clause, only the timestamps indicating the start and completion of the load are logged in these files.

The message log and row files for integrity constraint violations are distinct from the IQ message log file (.iqmsg).

You can specify whether to ignore UNIQUE, NULL, DATA VALUE, and FOREIGN KEY constraint violations that occur during a load and the maximum number of violations to ignore before initiating a rollback. You can also direct the load to log information about specific types of integrity constraint violations both per violation in a message log and per row in a row log.

MESSAGE LOG Contents and Format
The MESSAGE LOG file contains row and column information for each integrity constraint violation logged.
ROW LOG Contents and Format
The ROW LOG file contains row ID and data values for each row on which logged integrity constraint violations occurred.
MESSAGE LOG and ROW LOG Example
An illustration of the contents and format of the MESSAGE LOG and ROW LOG files.

Parent topic: Bulk Loads with the LOAD TABLE Statement

Related concepts

Loads That Specify Input Data Format

Direct Loading of Data from Clients

Considerations for Partitioned Table Loads

Load and Insert Messages