I-Series DB2 Self-referential constraint issue causing locks

[Darius]

I was having an issue inserting data into a table in an I-Series DB2
database. The Insert statement itself is very simple:

Insert into Table1
select * from Table2

These two tables have identical structures.

What happens is that the process gets locked and one cannot kill it
regardless of what one does. The only solution seems to be to IPL the
machine.

The above Insert works fine for all the other tables. What is special
about this table layout is that it has two self-referential
constraints. The "bulk" select/insert contains records that would
reference each other after they were all inserted.

Here is a simplified layout:

CREATE TABLE testtable
(
personId DEC(9,0) NOT NULL,

.... various fields ...

fatherId DEC(9,0) NOT NULL,
motherId DEC(9,0) NOT NULL,
CONSTRAINT pk001 PRIMARY KEY (personId)
)
;

CREATE UNIQUE INDEX idx001 ON testtable (personId ASC)
;
ALTER TABLE testtable ADD CONSTRAINT fk001 FOREIGN KEY (
fatherId
)
REFERENCES testtable (
personId
)
;

ALTER TABLE testtable ADD CONSTRAINT fk002 FOREIGN KEY (
motherId
)
REFERENCES testtable (
personId
)
;

As things stand, I cannot change the layout.
Since the "bulk" select/insert does not work, I have changed to a
"programmat ic" record-at-a-time approach. (I guess I could also
unload/load). But, I'm curious is anyone has encountered this issue.

I would think that even if the data is bad or the data cannot be
handled, it ought to end with some kind of SQL error rather than
locking up and "hanging".
Has anyone encountered and/or solved this issue?

[Philip Sherman]

I'm not sure what is happening but I offer the following thoughts:

1. If table2 is not updated because it is populated from table1; drop
the relational constraint on table 2.
2. Try loading without the relational constraint then altering the table
to add it.
3. check if the "NOT ENFORCED" clause of a constraint definition can be
used to allow loading and then reactivate the constraint.

None of there addresses the underlying question but they may provide a
workaround.

Philip Sherman

Darius wrote:

I was having an issue inserting data into a table in an I-Series DB2
database. The Insert statement itself is very simple:

Insert into Table1
select * from Table2

These two tables have identical structures.

What happens is that the process gets locked and one cannot kill it
regardless of what one does. The only solution seems to be to IPL the
machine.

The above Insert works fine for all the other tables. What is special
about this table layout is that it has two self-referential
constraints. The "bulk" select/insert contains records that would
reference each other after they were all inserted.

Here is a simplified layout:

CREATE TABLE testtable
(
personId DEC(9,0) NOT NULL,

... various fields ...

fatherId DEC(9,0) NOT NULL,
motherId DEC(9,0) NOT NULL,
CONSTRAINT pk001 PRIMARY KEY (personId)
)
;

CREATE UNIQUE INDEX idx001 ON testtable (personId ASC)
;
ALTER TABLE testtable ADD CONSTRAINT fk001 FOREIGN KEY (
fatherId
)
REFERENCES testtable (
personId
)
;

ALTER TABLE testtable ADD CONSTRAINT fk002 FOREIGN KEY (
motherId
)
REFERENCES testtable (
personId
)
;

As things stand, I cannot change the layout.
Since the "bulk" select/insert does not work, I have changed to a
"programmat ic" record-at-a-time approach. (I guess I could also
unload/load). But, I'm curious is anyone has encountered this issue.

I would think that even if the data is bad or the data cannot be
handled, it ought to end with some kind of SQL error rather than
locking up and "hanging".
Has anyone encountered and/or solved this issue?