The exception table has 2 extra columns:
SQL Reference
Rules for Creating an Exception Table
The rules for creating an exception table are as follows:
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.
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.
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.
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.
The exception table should be created with both "(n+1)" and the "(n+2)"
columns.
There is no enforcement of any particular name for the above additional
columns. However, the type specification must be exactly followed.
No additional columns are allowed.
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.
If the original table has generated columns (including the IDENTITY
property), the corresponding columns in the exception table should not
specify the generated property.
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.
maricel wrote:
I forgot to mention, I tried to use the exception table with the LOAD
statement but still failed:
C:\temp>DB2 LOAD FROM T1.TXT OF DEL REPLACE INTO ADMINISTRATOR.T1 FOR
EXCEPTION
ADMINISTRATOR.T1_except
SQL3604N Exception table "T1_EXCEPT" corresponding to table "T1" in the SET
INTEGRITY statement or LOAD utility does not have the proper structure, has
been defined with unique indexes, constraints, generated columns, or
triggers,
or is in the check pending state itself.
"maricel" <ma*****@xtra.co.nz> wrote in message
news:3%********************@news.xtra.co.nz...
I have the following base table structure - DDL:
CREATE TABLE "ADMINISTRATOR"."T1" (
"C1" INTEGER NOT NULL )
IN "TEST_TS" ;
ALTER TABLE "ADMINISTRATOR"."T1"
ADD PRIMARY KEY
("C1");
What I am trying to achieved here is to get those referential integrity
violations after LOAD.
Before running the load statemet, I created first an exception table.
CREATE TABLE T1_EXCEPT LIKE T1
ALTER TABLE T1_EXCEPT
ADD RID CHAR(4)
ALTER TABLE T1_EXCEPT
ADD TIMESTAMP TIMESTAMP
And then run LOAD command: -- the T1.TXT contains duplicate values
C:\temp>DB2 LOAD FROM T1.TXT OF DEL REPLACE INTO ADMINISTRATOR.T1
When I try to run the SET INTEGRITY (command below), I got the following
error.
C:\temp>db2 set integrity for administrator.t1 immediate checked for
exception in administrator.t1 use administrator.t1_except
DB21034E The command was processed as an SQL statement because it was not
a
valid Command Line Processor command. During SQL processing it returned:
SQL3604N Exception table "ADMINISTRATOR.T1_EXCEPT" corresponding to table
"ADMINISTRATOR.T1" in the SET INTEGRITY statement or LOAD utility does not
have the proper structure, has been defined with unique indexes,
constraints,
generated columns, or triggers, or is in the check pending state itself.
SQLSTATE=428A5
Note:
I have tried creating the exception table like this but still got the same
error above:
CREATE TABLE T1_EXCEPT LIKE T1 (without extra columns)
Anybody knew what i am missing here?
Regards,
maricel
--
Anton Versteeg
IBM Certified DB2 Specialist
IBM Netherlands