473,287 Members | 1,555 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,287 software developers and data experts.

SET Integrity Exception Table - How to use

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
4 15761
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: Jean-Marc Blaise | last post by:
Dear all, I have a table that contains a DATE in char(10) format. This table is LOADed. I put a check constraint on the column, some kind of CHECK (DATE(F) <= DATE('9999-12-31')) and I would...
1
by: Grant McLean | last post by:
Hi First a simple question ... I have a table "access_log" that has foreign keys "app_id" and "app_user_id" that reference the "application_type" and "app_user" tables. When I insert into...
7
by: Jimmie H. Apsey | last post by:
Referential Integrity on one of our production tables seems to have been lost. I am running Postgres 7.1.3 embedded within Red Hat kernel-2.4.9-e.49. Within that I have a table with referential...
6
by: heyvinay | last post by:
I have transaction table where the rows entered into the transaction can come a result of changes that take place if four different tables. So the situation is as follows: Transaction Table...
12
by: technocrat | last post by:
I am trying to laod 2 tables and set integrtiy on them, but the second tables fails ( both are related and SET INTEGRITY ion first table succeeds) saying constraint violation....is there a way to...
3
by: technocrat | last post by:
I was runing load and then set integrity with for exception clause, still the statement failed , there are some reecords in the exception table but the actual table is still in check pending...
16
by: Brian Tkatch | last post by:
Is there a way to check the order in which SET INTEGRITY needs to be applied? This would be for a script with a dynamic list of TABLEs. B.
1
by: Brian Tkatch | last post by:
A few questions about SET INTEGRITY. 1) Docs: The SET INTEGRITY statement is under transaction control. Using two sessions (different user, even) i tried the following. Session 1: UPDATE...
2
by: ApexData | last post by:
Access2000, using a continuous form. I’m getting a message that say “you cannot add or change a record because a related record is required in table Employee”. This occurs in all my combobox...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.