How LOAD builds indexes while loading data
LOAD builds all the indexes that are defined for any table that is being loaded unless INDEXDEFER is specified or used by default. If INDEXDEFER is used, the specified indexes (ALL, NPI, or NONUNIQUE) are not built and placed in RBDP status.
If LOAD REPLACE SHRLEVEL REFERENCE PART is specified, INDEXDEFER NPI is used by default. In this case, the affected part-level non-partitioning index is placedin RBDP status on successful LOAD completion.
At
the same time the indexes are being built, LOAD checks for duplicate values of any
unique index key. If LOAD finds any duplicate values, the behavior depends on the
SHRLEVEL value: - For SHRLEVEL NONE or SHRLEVEL REFERENCE, none of the corresponding rows are loaded. When LOAD checks for key violations, key comparisons are done on adjacent keys before inserting into the table. If a duplicate key violation is found, all keys and their associated records involved in the violation are discarded. The exception is index keys that are defined with BUSINESS_TIME WITHOUT OVERLAPS. In this case, violation checking is done after the initial keys are inserted into the index and can involve two or more non-adjacent keys. Therefore, only the latter keys and records that cause the overlap violations are discarded.
- For SHRLEVEL CHANGE, the first record is inserted and subsequent records in violation are rejected.
For unique indexes, any two null values are assumed to be equal, unless the index was created with the UNIQUE WHERE NOT NULL clause. In that case, if the key is a single column, it can contain any number of null values, although its other values must be unique.
Neither the loaded table nor its indexes contain any of the records that might have produced an error. Using the error messages, you can identify faulty input records, correct them, and load them again. If you use a discard data set, you can correct the records there and add them to the table with LOAD RESUME.