SQL Reference

Rules for Creating an Exception Table

The rules for creating an exception table are as follows:

1. The first "n" columns of the exception table are the same as the columns of the table being checked. All column attributes including name, type and length should be identical.
2. All the columns of the exception table must be free of any constraints and triggers. Constraints include referential integrity, check constraints as well as unique index constraints that could cause errors on insert.
3. The "(n+1)" column of the exception table is an optional TIMESTAMP column. This serves to identify successive invocations of checking by the SET INTEGRITY statement on the same table, if the rows within the exception table have not been deleted before issuing the SET INTEGRITY statement to check the data.
4. The "(n+2)" column should be of type CLOB(32K) or larger. This column is optional but recommended, and will be used to give the names of the constraints that the data within the row violates. If this column is not provided (as could be warranted if, for example, the original table had the maximum number of columns allowed), then only the row where the constraint violation was detected is copied.
5. The exception table should be created with both "(n+1)" and the "(n+2)" columns.
6. There is no enforcement of any particular name for the above additional columns. However, the type specification must be exactly followed.
7. No additional columns are allowed.
8. If the original table has DATALINK columns, the corresponding columns in the exception table should specify NO LINK CONTROL. This ensures that a file is not linked when a row (with DATALINK column) is inserted and an access token is not generated when rows are selected from the exception table.
9. If the original table has generated columns (including the IDENTITY property), the corresponding columns in the exception table should not specify the generated property.
10. It should also be noted that users invoking SET INTEGRITY to check the data must have INSERT privilege on the exception tables.

The information in the "message" column will be according to the following structure:

Table 145. Exception Table Message Column Structure

Field number	Contents	Size	Comments
1	Number of constraint violations	5 characters	Right justified padded with '0'
2	Type of first constraint violation	1 character	'K' - Check Constraint violation 'F' - Foreign Key violation 'G' - Generated Column violation 'I' - Unique Index violationa 'L' - DATALINK load violation
3	Length of constraint/columnb /index IDc/DLVDESCd	5 characters	Right justified padded with '0'
4	Constraint name/Column nameb/index IDc/DLVDESCd	length from the previous field
5	Separator	3 characters	<space><colon><space>
6	Type of next constraint violation	1 character	'K' - Check Constraint violation 'F' - Foreign Key violation 'G' - Generated Column violation 'I' - Unique Index violation 'L' - DATALINK load violation
7	Length of constraint/column/index ID/ DLVDESC	5 characters	Right justified padded with '0'
8	Constraint name/Column name/Index ID/ DLVDESC	length from the previous field
.....	.....	.....	Repeat Field 5 through 8 for each violation
a Unique index violations will not occur with checking using SET INTEGRITY. This will be reported, however, when running LOAD if the FOR EXCEPTION option is chosen. LOAD, on the other hand, will not report check constraint, generated column, and foreign key violations in the exception tables. b To retrieve the expression of a generated column from the catalog views, use a select statement. For example, if field 4 is MYSCHEMA.MYTABLE.GEN_1, then SELECT SUBSTR(TEXT, 1, 50) FROM SYSCAT.COLUMNS WHERE TABSCHEMA='MYSCHEMA' AND TABNAME='MYNAME' AND COLNAME='GEN_1'; will return the first fifty characters of the expression, in the form "AS (<expression>)" c To retrieve an index ID from the catalog views, use a select statement. For example, if field 4 is 1234, then SELECT INDSCHEMA, INDNAME FROM SYSCAT.INDEXES WHERE IID=1234. dDLVDESC is a DATALINK Load Violation DESCriptor described below.

Table 146. DATALINK Load Violation DESCriptor (DLVDESC)

Field number	Contents	Size	Comments
1	Number of violating DATALINK columns	4 characters	Right justified padded with '0'
2	DATALINK column number of the first violating column	4 characters	Right justified padded with '0'
2	DATALINK column number of the second violating column	4 characters	Right justified padded with '0'
.....	.....	.....	Repeat for each violating column number
Note: DATALINK column number is COLNO in SYSCAT.COLUMNS for the appropriate table.