Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Deferring constraints in DB2

533 views
Skip to first unread message

Marek Berkan

unread,
Apr 14, 2005, 11:31:51 AM4/14/05
to
Hi,

I have a problem with deffering constraints with db2. It was explained
five year ago at this same group
(http://groups.google.pl/groups?hl=pl&lr=&threadm=06e44e80.a0717b90%40usw-ex0101-006.remarq.com&rnum=4&prev=/groups%3Fq%3Ddb2%2Bconstraint%2Binitially%2Bdeferred%26hl%3Dpl%26lr%3D%26selm%3D06e44e80.a0717b90%2540usw-ex0101-006.remarq.com%26rnum%3D4):

"I have DB2 v 6.1, fixpak 3 and following problem:
On Oracle I can create foreign key constraints DEFERRABLE
INITIALLY
DEFERRED or I can SET CONSTRAINTS ALL DEFERRED

On informix I can SET CONSTRAINTS ALL DEFERRED too.

In both cases, when transaction commits, all the constraints will
be
checked.

Can I do this on DB2?
I've tried SET INTEGRITY FOR table OFF, but this will set table
in the
check pending state, and I cannot SELCT or UPDATE the table.

SET INTEGRITY FOR table FOREIGN KEY IMMEDIATE UNCHECKED
should defer integrity checking, but it doesn't seem to work. The
constraint is still checked.

Even if it would work, do I have to SET INTEGRITY FOR table
IMMEDIATE
CHECKED explicitly before transaction commit?"

Mr Serge Rielau (sri...@ca.ibm.com) wrote anwer:

"DB2 currently doesn't support this feature." ;)

My question is: if DB2 v.8.2. still doesn't support this feature?

Regards,
Mark.

Serge Rielau

unread,
Apr 14, 2005, 11:56:54 AM4/14/05
to
No changes on that front. It's still a known requirement.
What you can do is is toggle enforcement of the constraint using ALTER
TABLE. Note that this switch is NOT session level. When you toggle back
to enforced DB2 will verify integrity on the table.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Marek Berkan

unread,
Apr 15, 2005, 3:23:47 AM4/15/05
to
Hi,

Serge Rielau napisał(a):


> No changes on that front. It's still a known requirement.
> What you can do is is toggle enforcement of the constraint using ALTER
> TABLE. Note that this switch is NOT session level. When you toggle back
> to enforced DB2 will verify integrity on the table.

Thank you for your answer. So, this method is good for administration
tasks but is not acceptable in "normal" application :(

Regards,
Mark.

Serge Rielau

unread,
Apr 15, 2005, 8:49:25 AM4/15/05
to
Correct - unless you run "uncommited read" isolation to begin with, in
which case you are already in fuzzy land to begin with.
That's why I stated it's _still_ a requirement.
0 new messages