By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,594 Members | 3,552 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,594 IT Pros & Developers. It's quick & easy.

SET Integrity Exception Table - How to use

P: n/a
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
Nov 12 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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

Nov 12 '05 #2

P: n/a
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
Nov 12 '05 #3

P: n/a
Ian
maricel wrote:

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


And then run LOAD command: -- the T1.TXT contains duplicate values


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! =-----
Nov 12 '05 #4

P: n/a
Many thanks for all those who replied. I got it working now.
maricel

"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

Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.