Deferring constraints in DB2

[Marek Berkan]

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...m%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 (sr*****@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]

Marek Berkan wrote:

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...m%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 (sr*****@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.

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]

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]

Marek Berkan wrote:

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.

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.
Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab