book
Article ID: KB0094600
calendar_today
Updated On:
Description
Resolution:
Customers are worried that this message indicates a performance issue due to not using the secondary index on table access.
Environment:
============
o All mainframe versions of Object Service Broker
Symptoms:
=========
The message "S6BBP134W SECONDARY INDEX INVALIDATED" appears in pointer checker output.
Cause:
======
A secondary index (SIX) is a table. The fields on the table are -
secondary key - which is the primary key of the SIX.
primary key - from the base table.,
page number - of the page containing the data row.
When you access a row via a validated index by secondary key the Data Object Broker (DOB) finds the SIX row for the secondary key using the SIX index and reads the data from the page number given in the six.
When the secondary index is invalidated the DOB finds the SIX row for the secondary key, then scans the primary index for the page number, then reads the data page.
So with an invalidated SIX you get an extra scan of the primary index. As the primary index is often in storage the change in response times is normally minimal.
We tell people they can ignore the S6BBP134W and there is even a pointer checker option to suppress the messages.
To give you an idea why this happens.
When a SIX is built it is validated. Problems happen when you perform a page split/merge as a result of insert/delete/replace activity.
If an insert into a table causes a page to be split into 2 pages you have to update the previous page or next page to keep the data level pointers correct.
You will also have to update the primary index to add a new row, for the new page, to the primary index.
If the primary index low level is full you will have to update the next level up in the primary index.
If you have a SIX or SIXes defined on the table then you need to insert a new row into each of the SIXes.
The big impact however is on the rows that got moved to a different page as a result of the page split.
If the page being split contains, for example, 100 rows, then 50 of these will be moved to the new page.
You will have to update 50 Index rows, potentially on 50 different pages, to change the page number to point to the new page.
In summary an insert page split on a 'normal' table can update normally 5 or 6 pages.
If that table has SIX, or multiple SIXes, then you can add 50 or so pages per SIX to this total.
If the row size is smaller or many fields are empty, i.e. you can fit more rows on a page, then the number of pages updated per SIX goes up.
If there are 2 or 3 such inserts in a commit then it's not hard to update several hundred pages.
The same happens if a replace enlarges a row so it no longer fits in the page where it was before.
Something very similar happens if a delete, or an replace of a smaller row, causes a page merge.
There are DOB parameters, WORKINGSET & DATAPAGELIMIT, that set the maximum number of pages that can be changed in a single transaction.
If your commit, containing multiple inserts/deletes/etc exceeds the WORKINGSET value then the transaction is not allowed.
In order to get round this issue instead of updating the SIX we simply mark it as invalidated, which normally brings the size of the commit within the WORKINGSET limit.
As said previously this should have minimal impact on response times due to the primary index being in the RESIDENTPAGES pool.
Resolution:
===========
You have 2 choices,
1) Ignore the messages, set the parms of S6BBRPTR to ignore invalidated indexes.
2) Set WORKINGSET & DATAPAGELIMIT both to 999 which says keep the SIX validated regardless of the number of pages to be updated.
This processing is really a hangover from the old days when virtual and real storage was constrained by the hardware and OS.
For modern systems, with up to 31TB of resident storage at R5.2, RESIDENTPAGES parameter, this cancelling of large updates isn't required.
The recommendation in the parameters manual for all supported releases, and a few earlier ones, is that you set both WORKINGSET & DATAPAGELIMIT to 999.
You may also want to look at increasing RESIDENTPAGES to support this larger number of page changes in a single commit.
References:
===========
TIBCO® Object Service Broker - Parameters
TIBCO® Object Service Broker for z/OS - Utilities
Issue/Introduction
S6BBP134W SECONDARY INDEX INVALIDATED appearing in pointer checker output.