469,609 Members | 2,182 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,609 developers. It's quick & easy.

How to remove constraint

I have two tables as shown below (only relevant columns shown). The
second table is dependent on the first one.

CREATE TABLE PARENTTABLE (
...
SERIAL CHAR(12) NOT NULL,
ENDDATE TIMESTAMP NOT NULL,
PKLINK INTEGER GENERATED ALWAYS AS IDENTITY (START WITH -2147483648,
INCREMENT BY 1),
PRIMARY KEY (PKLINK),
CONSTRAINT CONSTRAINT1 UNIQUE (ENDDATE,SERIAL)
) DATA CAPTURE NONE IN USERSPACE1;

CREATE TABLE CHILDTABLE (
...
PKLINK INTEGER NOT NULL,
PACKET SMALLINT NOT NULL,
PRIMARY KEY (PKLINK,PACKET),
FOREIGN KEY(PKLINK) REFERENCES PARENTTABLE(PKLINK) ON DELETE CASCADE
) DATA CAPTURE NONE IN USERSPACE1;
My problem is that when I do a "SELECT * FROM PARENTTABLE WHERE
ENDDATE BETWEEN ...", the query is very slow. It seems that the
constraint on this table is not work. I would like to remove the
constraint and create a unique index instead. Questions:

(1) How do I remove the constraint in the first table? I can't do an
explicit DROP on the constraint.

(2) If I need to recreate the table, I would have to export out the
data first and later import back the data into the new table (without
the contraint). Will the identity column (PKLINK) prevent me from
importing the data? I still want the old values of the identity column
because the second table depends on it. What can I do to successful
import the data?

Thanks in advance.

Yong Sing
Nov 12 '05 #1
2 9918
"yongsing" <oh********@yahoo.com.sg> wrote in message
news:59**************************@posting.google.c om...
I have two tables as shown below (only relevant columns shown). The
second table is dependent on the first one.

CREATE TABLE PARENTTABLE (
...
SERIAL CHAR(12) NOT NULL,
ENDDATE TIMESTAMP NOT NULL,
PKLINK INTEGER GENERATED ALWAYS AS IDENTITY (START WITH -2147483648,
INCREMENT BY 1),
PRIMARY KEY (PKLINK),
CONSTRAINT CONSTRAINT1 UNIQUE (ENDDATE,SERIAL)
) DATA CAPTURE NONE IN USERSPACE1;

CREATE TABLE CHILDTABLE (
...
PKLINK INTEGER NOT NULL,
PACKET SMALLINT NOT NULL,
PRIMARY KEY (PKLINK,PACKET),
FOREIGN KEY(PKLINK) REFERENCES PARENTTABLE(PKLINK) ON DELETE CASCADE
) DATA CAPTURE NONE IN USERSPACE1;
My problem is that when I do a "SELECT * FROM PARENTTABLE WHERE
ENDDATE BETWEEN ...", the query is very slow. It seems that the
constraint on this table is not work. I would like to remove the
constraint and create a unique index instead. Questions:

(1) How do I remove the constraint in the first table? I can't do an
explicit DROP on the constraint.

(2) If I need to recreate the table, I would have to export out the
data first and later import back the data into the new table (without
the contraint). Will the identity column (PKLINK) prevent me from
importing the data? I still want the old values of the identity column
because the second table depends on it. What can I do to successful
import the data?

Thanks in advance.

Yong Sing


To drop constraints, see the ALTER TABLE statement in the SQL reference.
However, before you do that, read below.

When you create a unique constraint, DB2 automatically creates a unique
index on the column(s) in the constraints, unless a unique index already
exists. Therefore, dropping the constraint and creating a unique index on
(ENDDATE,SERIAL) will not help you since there must already be a unique
index on these columns.

Please reorg the tables and indexes. Then execute the runstats command on
the table and the indexes and collect full statistics and column
distribution (see the Command Reference for details). Then try your query
again.

If that does not improve performance, there is something wrong (or unusual)
with your query. Please post the exact query you are having a problem with,
and be sure to include the entire WHERE clause next time.
Nov 12 '05 #2
AK
In addition to what MarkA has already posted,

in the range query

"SELECT * FROM PARENTTABLE WHERE
ENDDATE BETWEEN ...",

do you have parameter markers or do you specify exact values?

What is the cluster factor of the involved index (ENDDATE,SERIAL)?
For range queries like this it is essential to have detailed
statistics (cluster factor -1 indicates there are no detailed
statistics). Do you have them?
Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

26 posts views Thread by Agoston Bejo | last post: by
4 posts views Thread by wireless | last post: by
reply views Thread by Bob Weiner | last post: by
15 posts views Thread by Frank Swarbrick | last post: by
reply views Thread by Solution2021 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.