Cascade Delete Locks

Al Heiden

Cascade Delete Locks
I have searched through the IBM Manuals, IBM Redbooks and the DB2L
Archives, and I can't find the answers to my questions on this subject.
I'm hoping one of the esteemed listers will be able to help me with this.

We are running DB2 UDB V7 for OS/390.

We have a Child Table defined with a Referential Integrity Constraint of
Cascade Delete to the Parent Table. The Parent Table has 10,000,000 rows
and the Child Table has 200,000 rows. The Child Table was added to the
database sometime after the Parent Table was added. When this was done,
we had to rebind all packages that inserted, updated or deleted rows from
the Parent Table.

Occasionally (10-15 times a week or more), when we are attempting to
delete a row from the Parent Table, we are receiving a SQL Code of -911
due to a Timeout on the Child Tablespace (The DB2 Master Log shows the -
911 is a Timeout on the Tablespace of the Child Table).

What I cannot determine is what type of Lock is DB2 taking on the Child
Table's Tablespace in the RI Constraint when there is no row on the Child
Table for the Parent Row being deleted. I can understand DB2 taking an
Intent eXclusive (IX) lock on the tablespace when there is a row on the
Child Table, but is DB2 doing the same thing when there is no row on the
Child Table to delete?

Does DB2 place locks on the Child Table's Tablespace whenever the Parent
Table in the Cascade Delete RI Constraint has a row inserted or updated?

Our DB2 Monitor is not showing me the locks that are being taken on the
tablespaces involved.

Does anyone know where this information would be documented?

Thank you for your help.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Roger Miller

Re: Cascade Delete Locks
(in response to Al Heiden)
I don't think these specifics are documented. But what is documented is
how to determine the specifics of your locking situation, and what we
recommend doing about it. Access to check for a deletion can still
require a lock. Is your isolation level CS? What is CURRENTDATA? Is lock
avoidance working, if permitted? What are the specifics of the timeout
situation?

The timeout should result in IFCID 0196 trace records, with specifics of
the conflict, as long as statistics class 3 is on. Just in case it's a
deadlock, I'd tend to get the IFCID 0172 records too, since -911 can be
either one. Can your DB2 monitor format these records? The
Administration Guide section on Using the statistics and accounting traces
to monitor locking goes through the next steps.

Roger Miller

On Mon, 22 Dec 2003 16:08:44 -0600, Al Heiden
<[login to unmask email]> wrote:

>I have searched through the IBM Manuals, IBM Redbooks and the DB2L
>Archives, and I can't find the answers to my questions on this subject.
>I'm hoping one of the esteemed listers will be able to help me with this.
>
>We are running DB2 UDB V7 for OS/390.
>
>We have a Child Table defined with a Referential Integrity Constraint of
>Cascade Delete to the Parent Table. The Parent Table has 10,000,000 rows
>and the Child Table has 200,000 rows. The Child Table was added to the
>database sometime after the Parent Table was added. When this was done,
>we had to rebind all packages that inserted, updated or deleted rows from
>the Parent Table.
>
>Occasionally (10-15 times a week or more), when we are attempting to
>delete a row from the Parent Table, we are receiving a SQL Code of -911
>due to a Timeout on the Child Tablespace (The DB2 Master Log shows the -
>911 is a Timeout on the Tablespace of the Child Table).
>
>What I cannot determine is what type of Lock is DB2 taking on the Child
>Table's Tablespace in the RI Constraint when there is no row on the Child
>Table for the Parent Row being deleted. I can understand DB2 taking an
>Intent eXclusive (IX) lock on the tablespace when there is a row on the
>Child Table, but is DB2 doing the same thing when there is no row on the
>Child Table to delete?
>
>Does DB2 place locks on the Child Table's Tablespace whenever the Parent
>Table in the Cascade Delete RI Constraint has a row inserted or updated?
>
>Our DB2 Monitor is not showing me the locks that are being taken on the
>tablespaces involved.
>
>Does anyone know where this information would be documented?
>
>Thank you for your help.
>

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm