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
"programmatic" 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?