473,327 Members | 2,025 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,327 software developers and data experts.

I-Series DB2 Self-referential constraint issue causing locks

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?

Nov 12 '05 #1
1 3067
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
"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?


Nov 12 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Whitney | last post by:
On the left side of the main webpage I have some menu options that have a drop-down feature that is controlled with javascript. You click on the main heading and the links for that section show up....
7
by: John Bailo | last post by:
I'm seeing some odd locking behavior when using an DB2400 database and running an ado.net transaction. My code -- in simplified form, appears at the bottom. I want to run several INSERT...
2
by: Mike | last post by:
Hellos again, I seem to be having a weird issue. Whenever DB2 Health monitor seems to run, all connections to the database seem to just lock up. For example: (and this seems consistent) In list...
17
by: Sulu's Beard | last post by:
Hey gang, I'm evaluating the CA ERWin product for an upcoming data warehousing project. I'm most excited about the reverse engineering aspect of this system. I've sucessfully tested it on a...
0
by: John Bailo | last post by:
I'm seeing some odd locking behavior when using an DB2400 database and running an ado.net transaction. My code -- in simplified form, appears at the bottom. I want to run several INSERT...
2
by: Ben Rush | last post by:
Hello World, Okay, I have spent the day browsing the newsgroups and reading up on article after article concerning ViewState corruption and so forth, and I have a couple questions. We...
10
by: shsandeep | last post by:
The ETL application loaded around 3000 rows in 14 seconds in a Development database while it took 2 hours to load in a UAT database. UAT db is partitioned. Dev db is not partitioned. the...
2
by: Ben.Combrink | last post by:
Hi I'm using DB2 V9.1.0 on SUSE Linux with kernel version 2.6.5. In the system I'm working on, there are several stored procedures which perform simple selects through different views. ...
0
by: maheshmohta | last post by:
Background Often while remodeling legacy application, one of the important tasks for the architects is to have an optimum usage of storage capabilities of database. Most of the legacy applications...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
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: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
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: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

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.