Selective Partition Locking and NPI's

David Price

Selective Partition Locking and NPI's
When Selective Partition Locking is implemented for a
given partitioned tablespace, how does DB2 manage
locks and latches for the Non-Partitioning Indexes?

Specifically, what will happen in the following
situation:

1. Partitioned Tablespace with 10 partitions.
2. One Partitioning Index on the tablespace.
3. One Non-Paritioning Index on the same tablespace.

- The NPI has one attribute (ID)
- The attribute (ID) is defined as INTEGER
- All inserts, insert an ID which is one higher than
the previous ID.
4. The application is constructed so that all access
to any given partion occurs from one and only one
member of the Data Sharing Group

It would appear that there would be much cross system
read/write interest for the NPI, therefore the last
several leaf pages for the NPI should be stored in the
coupling facility. Is this the case?

If this is the case, is there a design for the NPI
which avoids the cross system read/write interest (and
the resulting coupling facility overhead)?

Thanks for your input.



Dave Price


All email sent to or from this address will be received or otherwise
recorded by the Charles Schwab corporate email system and is subject to
archival, monitoring or review by, and or disclosure to, someone other than
the recipient



Michael Hannan

Selective Partition Locking and NPI's
(in response to David Price)
David,

For a non-Data Sharing system there is no NPI problem due to no locking on
the NPI (only on data rows). Latches are brief and not a substantial problem.

However in Data Sharing your problem does exist.

Since all inserts to the NPI are at the end, the pages are in heavy use as
you say.
Each member wanting to update the page must get a page p-lock exclusive and
negotiate with other process already holding the page p-lock to relinquish
it and send latest copy of page to Coupling Facility.
This makes for a lot of inter member communications, and the sending of
updated page backward and forward between members and coupling facility.

Performance is degraded somewhat by .

So it is not a good idea for each member to share same ID number range.
Better for each member to use a different number range. Another possibility
is to add an extra column in front of ID to index which has a different
value for each member (effectively same soln.).

From: Michael Hannan
Just my personal opinions.
>From: "Price, David" <[login to unmask email]>
>Subject: Selective Partition Locking and NPI's
>To: [login to unmask email]
>
>When Selective Partition Locking is implemented for a
>given partitioned tablespace, how does DB2 manage
>locks and latches for the Non-Partitioning Indexes?
>
>Specifically, what will happen in the following
>situation:
>
>1. Partitioned Tablespace with 10 partitions.
>2. One Partitioning Index on the tablespace.
>3. One Non-Paritioning Index on the same tablespace.
>
> - The NPI has one attribute (ID)
> - The attribute (ID) is defined as INTEGER
> - All inserts, insert an ID which is one higher than
>the previous ID.
>4. The application is constructed so that all access
>to any given partion occurs from one and only one
>member of the Data Sharing Group
>
>It would appear that there would be much cross system
>read/write interest for the NPI, therefore the last
>several leaf pages for the NPI should be stored in the
>coupling facility. Is this the case?
>
>If this is the case, is there a design for the NPI
>which avoids the cross system read/write interest (and
>the resulting coupling facility overhead)?
>
>Thanks for your input.
>
>
>
> Dave Price
>
>
>All email sent to or from this address will be received or otherwise
>recorded by the Charles Schwab corporate email system and is subject to
>archival, monitoring or review by, and or disclosure to, someone other than
>the recipient
>
>
>


>



Chris Munson

Selective Partition Locking and NPI's
(in response to Michael Hannan)
Dave, As you probably know Selective Partition Locking (SPL) is a
tablespace keyword, only applicable to the partition tablespace and
therefore not a player for your NPI. If you have affinities of members to
the partitions and if using SPL this will avoid GBP dependency on the
tablespace partitions and will lessen coupling facility overheads for the
tablespace. The NPI on the other hand can't enjoy the same luxury. With
each member is inserting into different parts, they all are still inserting
into the one NPI and therefore it will be GBP dependent. There really
isn't much you can do about the NPI and GBP dependency. We do take page
p-locks on the index leaf pages and while a page p-lock is not expensive,
the negotiation of it is. This page p-lock is kind of our way of doing a
'global latch' (since indexes aren't locked). Therefore spreading out the
access as Michael mentions can make a difference. Using this approach the
index pages should be less likely to be found in the CF but I guess this is
a trade off for p-lock negotiation.

Chris Munson
DB2 Development, Santa Teresa Laboratory
-------------------------------------------------------------------------------------------------------------------
David,

For a non-Data Sharing system there is no NPI problem due to no locking on
the NPI (only on data rows). Latches are brief and not a substantial
problem.

However in Data Sharing your problem does exist.

Since all inserts to the NPI are at the end, the pages are in heavy use as
you say.
Each member wanting to update the page must get a page p-lock exclusive and
negotiate with other process already holding the page p-lock to relinquish
it and send latest copy of page to Coupling Facility.
This makes for a lot of inter member communications, and the sending of
updated page backward and forward between members and coupling facility.

Performance is degraded somewhat by .

So it is not a good idea for each member to share same ID number range.
Better for each member to use a different number range. Another possibility
is to add an extra column in front of ID to index which has a different
value for each member (effectively same soln.).

From: Michael Hannan
Just my personal opinions.
From: "Price, David" <[login to unmask email]>
Subject: Selective Partition Locking and NPI's
To: [login to unmask email]

When Selective Partition Locking is implemented for a
given partitioned tablespace, how does DB2 manage
locks and latches for the Non-Partitioning Indexes?

Specifically, what will happen in the following
situation:

1. Partitioned Tablespace with 10 partitions.
2. One Partitioning Index on the tablespace.
3. One Non-Paritioning Index on the same tablespace.

- The NPI has one attribute (ID)
- The attribute (ID) is defined as INTEGER
- All inserts, insert an ID which is one higher than
the previous ID.
4. The application is constructed so that all access
to any given partion occurs from one and only one
member of the Data Sharing Group

It would appear that there would be much cross system
read/write interest for the NPI, therefore the last
several leaf pages for the NPI should be stored in the
coupling facility. Is this the case?

If this is the case, is there a design for the NPI
which avoids the cross system read/write interest (and
the resulting coupling facility overhead)?

Thanks for your input.



Dave Price