Use case/business impact.
All indexes reorged on Sunday.
Run daily process to identify any index needing reorged by interrogating RTS.
Since NPSI organization can rapidly deteriorate, on Monday, Tuesday, Wednesday, Thursday, and Friday, it is desired to ignore any NPSIs reorged on the previous Sunday.
When REORGLASTTIME is used to ignore NPSIs reorged on the previous Sunday, NPSIs that got reorged as the result of a partial TS reorg with SORTNPSI NO and are disorganized after the reorg, erroneously get ignored for needing a reorg.
What is needed is an easy method to distinguish between a NPSI reorg (traditional NPSI reorg or partial TS reorg with SORTNPSI YES) and a NPSI reorg done via a partial TS reorg with SORTNPSI NO.
Why is it useful?
|Who would benefit from this IDEA?||DBAs would benefit because it would be easy to identify NPSIs that were not sorted during the last partial TS reorg.|
How should it work?
Please add a column to SYSIBM.SYSINDEXSPACESTATS:
Column name SORTNPSI_USED CHAR(1).
Indicates if SORTNPSI was used during a Partial TS Reorg.
Null not applicable
Y NPSI Sorted Because Of SORTNPSI YES
A NPSI Sorted Because Of SORTNPSI AUTO
N NPSI Not Sorted
When SYSIBM.SYSINDEXSPACESTATS REORGLASTTIME is populated,
populate SORTNPSI as follows:
Type Event SORTNPSI_USED
----- ----------------------------------- ----------------------
Any Reorg Index Null
Any Full TS Reorg Null
PI Partial TS Reorg Null
NPSI Partial TS Reorg Y
NPSI Sorted because
of SORTNPSI YES
NPSI Partial TS Reorg A
NPSI Sorted because
of SORTNPSI AUTO
NPSI Partial TS Reorg N
NPSI Not Sorted
|Priority Justification||I have been pursuing a solution for this challege for over two years.|
|Customer Name||Stephen Dilts|
NOTICE TO EU RESIDENTS: per EU Data Protection Policy, if you wish to remove your personal information from the IBM ideas portal, please login to the ideas portal using your previously registered information then change your email to "email@example.com" and first name to "anonymous" and last name to "anonymous". This will ensure that IBM will not send any emails to you about all idea submissions