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. 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 129. 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 'I' - Unique Index violation (See Note) 'L' - DATALINK load violation
3	Length of constraint/index ID a/ DLVDESC b	5 characters	Right justified padded with '0'
4	Constraint name/index ID a/ DLVDESC b	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 'I' - Unique Index violation 'L' - DATALINK load violation
7	Length of constraint/index name/ DLVDESC	5 characters	Right justified padded with '0'
8	Constraint name/ Index name/ DLVDESC	length from the previous field
.....	.....	.....	Repeat Field 5 through 8 for each violation
Note: In DB2 Version 6, 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 and foreign key violations in the exception tables in DB2 Version 6. a 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. bDLVDESC is a DATALINK Load Violation DESCriptor described below.

Table 130. 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.

[ DB2 List of Books | Search the DB2 Books ]