How to remove constraint

[yongsing]

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(PKL INK) 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

[Mark A]

"yongsing" <oh********@yah oo.com.sg> wrote in message
news:59******** *************** ***@posting.goo gle.com...

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(PKL INK) 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.

[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?