maricel wrote:
[color=blue]
> 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[/color]
Your exception table is not in a valid format.
Exception tables are defined in the SQL Reference (the following is
copied from the doc):
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 “(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.
[color=blue]
>
> And then run LOAD command: -- the T1.TXT contains duplicate values[/color]
The duplicate values (i.e. those that violate the primary key constraint
on the table) will be deleted during the LOAD, not during SET INTEGRITY.
Therefore, if you want to capture the duplicate records, you must specify
the exception table during the LOAD operation.
Good luck,
-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----
