Reliability of DB2 zOS RTS Indexspacestats LASTUSED column

John SooHoo

Reliability of DB2 zOS RTS Indexspacestats LASTUSED column

Dear Esteemed Listers,

On Db2 for z/OS (we are at V11 NFM), the Real Time Statistics (RTS) table SYSIBM.SYSINDEXSPACESTATS has a LASTUSED column.

Per the SQL Reference, it is defined as: 

The date when the index is used for SELECT, FETCH, searched UPDATE, searched DELETE, or used to enforce referential integrity constraints. For a data-partitioned secondary index, this column is only updated for one partition, even though more than one partition is accessed.  The default value is NULL. 

I see some production indexes which, based on LASTUSED appear to be unused.

I'm wondering how reliable this column is, as one would like to drop unused indexes, but of course would like to avoid a performance degradation.

I've read that there is the possibility that when DB2 externalizes the RTS data to disk that if it is unable to do so, some data could be lost.  I think this would be a relatively rare occurrence.

I also see there is a column in the same table named REORGINDEXACCESS.

This field is defined as:

The number of times the index was used for SELECT, FETCH, searched UPDATE, searched DELETE, or used to enforce referential integrity constraints, or since the object was created. For hash overflow indexes, this is the number of times DB2 has used the hash overflow index.  A null value indicates that the number of times the index has been used is unknown.

Excluding partitioned indexes for now, if I find an index that has an old LASTUSED date (e.g. 6 or 12 months old or more) or a NULL LASTUSED, AND I find a REORGINDEXACCESS value of zero for the same index, could I be reasonably assured that this index could be dropped?

Thanks in advance for any assistance.

John Soohoo 

Roy Boxwell

Reliability of DB2 zOS RTS Indexspacestats LASTUSED column
(in response to John SooHoo)
Yes indeed but be careful of unique indexes that might not be used except for stopping duplicates and for access path changes. I have seen lots of cases where the data gathered for one index tells the optimizer to use another - when you drop the original it then chooses another, wrong, index.
Best thing to do is watch out with unique indexes and use Virtual Indexes to virtually drop and reEXPLAIN all access paths that use the table to sed if you have this problem.

Have fun!

Roy Boxwell
SOFTWARE ENGINEERING GmbH and SEGUS Inc.
-Product Development-
Heinrichstrasse 83-85
40239 Düsseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]<mailto:[login to unmask email]>
http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert, Ulf Heinrich

On 21 May 2018, at 01:39, John SooHoo <[login to unmask email]<mailto:[login to unmask email]>> wrote:


Dear Esteemed Listers,

On Db2 for z/OS (we are at V11 NFM), the Real Time Statistics (RTS) table SYSIBM.SYSINDEXSPACESTATS has a LASTUSED column.

Per the SQL Reference, it is defined as:

The date when the index is used for SELECT, FETCH, searched UPDATE, searched DELETE, or used to enforce referential integrity constraints. For a data-partitioned secondary index, this column is only updated for one partition, even though more than one partition is accessed. The default value is NULL.

I see some production indexes which, based on LASTUSED appear to be unused.

I'm wondering how reliable this column is, as one would like to drop unused indexes, but of course would like to avoid a performance degradation.

I've read that there is the possibility that when DB2 externalizes the RTS data to disk that if it is unable to do so, some data could be lost. I think this would be a relatively rare occurrence.

I also see there is a column in the same table named REORGINDEXACCESS.

This field is defined as:

The number of times the index was used for SELECT, FETCH, searched UPDATE, searched DELETE, or used to enforce referential integrity constraints, or since the object was created. For hash overflow indexes, this is the number of times DB2 has used the hash overflow index. A null value indicates that the number of times the index has been used is unknown.

Excluding partitioned indexes for now, if I find an index that has an old LASTUSED date (e.g. 6 or 12 months old or more) or a NULL LASTUSED, AND I find a REORGINDEXACCESS value of zero for the same index, could I be reasonably assured that this index could be dropped?

Thanks in advance for any assistance.

John Soohoo

-----End Original Message-----

Venkat Srinivasan

RE: Reliability of DB2 zOS RTS Indexspacestats LASTUSED column
(in response to John SooHoo)

How can we tell there isn't a program which runs once every year or two years or every n years? In the same way dropping when it is null is equally bad as someone might have just created and awaiting application access but the drop would come in and sweep it. Perhaps you can compare the create date in your consideration to drop the index.

Roy already mentioned unique indexes.

If the date is present it can be used to do something about it. Certain abnormal terms wont extern RTS but that shouldn't affect what you want to do.

Someone once wanted to cleanup nullid packages like this and when I objected and pointed that we will be dropping v11 connect packages that were also qualified, based on a predicate of lastused, the effort was stopped.

You need to do more thinking and that will be installation dependent.

Save the DDL and drop a few at a time, if you want to do it. 

I am not dissuading you from "the drop" at all. 

Venkat

Peter Vanroose

RE: Reliability of DB2 zOS RTS Indexspacestats LASTUSED column
(in response to Venkat Srinivasan)

If an application runs just once a year, and uses a particular (otherwise unused index), it *could* often make sense to drop that index, as it might cost more (CPU & I/O to maintain it) than it saves (once a year).

B.T.W: you can tell whether a program is using a certain index (that is: a statically bound one): look in SYSIBM.SYSPACKDEP for rows with BTYPE='I' and (BQUALIFIER,BNAME)=<your index name>

And indeed, as already said: don't drop UNIQUE indexes.

 

In Reply to Venkat Srinivasan:

How can we tell there isn't a program which runs once every year or two years or every n years? [...]

--      Peter Vanroose
        ABIS Training & Consulting,
        Leuven, Belgium.
        https://www.abis.be/

Venkat Srinivasan

RE: Reliability of DB2 zOS RTS Indexspacestats LASTUSED column
(in response to Peter Vanroose)

And that Partial analysis is exactly the problem. The reliability is not with lastused column but with our ability to analyze the impact. Static sql and cobol is not the only programming language. What you see in your installation need not be the same everywhere else.

Venkat

Michael Hannan

RE: Reliability of DB2 zOS RTS Indexspacestats LASTUSED column
(in response to Peter Vanroose)

To be more clear,

Most tables have a "primary key" to enforce uniqueness, and sometimes more than one alternate key to be enforced Unique.

Other indexes that just happen to be also unique (contain a superset of the unique key columns enforced by another unique index), but are not required to enforce uniqueness, can be dropped, if not needed by important access paths. I nearly forgot that Index can be used to enforce Foreign Key constraint for referential integrity as well, when delete a row from parent table.
 
In Reply to Peter Vanroose:

And indeed, as already said: don't drop UNIQUE indexes.

 
 

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Edited By:
Michael Hannan[Organization Members] @ May 24, 2018 - 12:10 PM (Europe/Berlin)

Phil Grainger

Reliability of DB2 zOS RTS Indexspacestats LASTUSED column
(in response to Michael Hannan)
And remember you can now INCLUDE non-unique columns in an otherwise-unique index – may help to reduce the number of indexes in some cases

________________________________

Phil Grainger

Enablement Manager

[login to unmask email]

Direct



+44 (0)118 921 8000

Mobile



+44(0)7808 643 479


E2, Eskdale Road
Winnersh
Berkshire
RG41 5TS


[http://media.cms.bmc.com/images/corp_signature_bmclogo_2014.jpg] http://www.bmc.com

[cid:[login to unmask email]






From: Michael Hannan [mailto:[login to unmask email]
Sent: 24 May 2018 08:51
To: [login to unmask email]
Subject: [DB2-L] - RE: Reliability of DB2 zOS RTS Indexspacestats LASTUSED column


To be more clear,

Most tables have a "primary key" to enforce uniqueness, and sometimes more than one alternate key to be enforced Unique.

Other indexes that just happen to be also unique (contain a superset of the unique key columns enforced by another unique index), but are not required to enforce uniqueness, can be dropped, if not needed by important access paths.

In Reply to Peter Vanroose:

And indeed, as already said: don't drop UNIQUE indexes.




Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

-----End Original Message-----
BMC Software Limited Registered Office: Building E2, Eskdale Road, Winnersh, Wokingham, Berkshire, United Kingdom, RG41 5TS Registered in England No. 1927903 The content of this email is confidential. If you are not the addressee, you may not distribute, copy or disclose any part of it. If you receive this message in error, please delete this from your system and notify the sender immediately.
Attachments

  • image001.jpg (8k)
  • image002.png (5.9k)

John SooHoo

RE: Reliability of DB2 zOS RTS Indexspacestats LASTUSED column
(in response to Phil Grainger)

Thank you, Roy, Venkat, Peter, Michael and Phil for your insightful responses.

I admit that I was hoping to get a simple confirmation with perhaps one or two caveats.

It does appear that it will not be that simple.  Cest la vie.

I have over a hundred non-unique indexes identified in this way (due, I believe, to a conversion of the application that took place last year) and we have a lot of distributed (Dynamic) SQL access that precludes relying on statically bound packages in the catalog.  I really wasn't looking to spend a lot of time with the dynamic statement cache at this time.

Well, now I have a better idea of what I am up against.

Thanks again.

John Soohoo

P.S. I *am* surprised that using an index to check for uniqueness is not considered a, "use."

Edited By:
John SooHoo[Organization Members] @ May 25, 2018 - 01:28 PM (America/Eastern)

Peter Vanroose

Re: Reliability of DB2 zOS RTS Indexspacestats LASTUSED column
(in response to John SooHoo)

In Reply to John SooHoo:

P.S. I *am* surprised that using an index to check for uniqueness is not considered a, "use."

Indeed it isn't, and that's because of the (unique?) way Db2 implements its uniqueness check:

  • When an INSERT is attempted with a duplicate value for (say) column P, having a unique index IXP,
    Db2 just does the insert (i.e., a new row/record in the tablespace)
  • Next, Db2 starts updating all indexes on the table, i.e., inserting their new entry.
  • When doing this for index IXP, the following problem occurs: the value is already there, with a RID pointer to a different tablespace record. Unique indexes cannot store RID lists per value, just single RIDs (since their internal structure, being simpler than that of duplicate indexes, does not support this); so index IXP signals a problem.
  • The whole transaction, including the record insert (and the other index inserts that already took place) is rolled back.

So the unique index is not really "used" in this process, it's just (attempted to be) made up-to-date.

Note that, in step 1, an (other) index is being used for obtaining an "optimal" physical location for the new record to be placed; and that's the cluster index of the table.

So don't forget to consider the "clustering" use of otherwise unused indexes! I'm not sure whether this kind of use will update the LASTUSED column; would be worth finding this out by just doing a single insert into a table...

--      Peter Vanroose
        ABIS Training & Consulting,
        Leuven, Belgium.
        https://www.abis.be/

Edited By:
Peter Vanroose[Organization Members] @ May 26, 2018 - 06:43 PM (Europe/Brussels)

Peter Vanroose

Re: Reliability of DB2 zOS RTS Indexspacestats LASTUSED column
(in response to Peter Vanroose)

Quick follow-up:

I just did a minimal test: 1-column table, with cluster index on it.

Apparently, an INSERT does not show up as LASTUSED in sysibm.sysindexspacestats, but a REORG does.

In Follow-up to my earlier post:

[...] I'm not sure whether this kind of use will update the LASTUSED column; would be worth finding this out [...]

--      Peter Vanroose
        ABIS Training & Consulting,
        Leuven, Belgium.
        https://www.abis.be/

James Campbell

Reliability of DB2 zOS RTS Indexspacestats LASTUSED column
(in response to Peter Vanroose)
The documented meaning of LASTUSED is "The date when the index was last used in an
access path for a SELECT, FETCH, searched UPDATE, or searched DELETE statement, or
was used to enforce referential integrity constraints."

INSERT isn't mentioned.

James Campbell


On 26 May 2018 at 9:55, Peter Vanroose wrote:

>
> Quick follow-up:
> I just did a minimal test: 1-column table, with cluster index on it.
> Apparently, an INSERT does not show up as LASTUSED in sysibm.sysindexspacestats, but a
> REORG does.
>
> In Follow-up to my earlier post:
> [...] I'm not sure whether this kind of use will update the LASTUSED column; would be worth
> finding this out [...]
> --      Peter Vanroose
>         ABIS Training &Consulting,
>         Leuven, Belgium.
>         https://www.abis.be/
>
>


---
This email has been checked for viruses by AVG.
https://www.avg.com

Peter Vanroose

RE: Reliability of DB2 zOS RTS Indexspacestats LASTUSED column
(in response to James Campbell)

Well, REORG isn't mentioned neither, and still it triggered the LASTUSED for the cluster index.

In Reply to James Campbell:

The documented meaning of LASTUSED is "The date when the index was last used in an
access path for a SELECT, FETCH, searched UPDATE, or searched DELETE statement, or
was used to enforce referential integrity constraints."
INSERT isn't mentioned.

--      Peter Vanroose
        ABIS Training & Consulting,
        Leuven, Belgium.
        https://www.abis.be/

John SooHoo

Re: Reliability of DB2 zOS RTS Indexspacestats LASTUSED column
(in response to Peter Vanroose)

If that was a SORTDATA NO type of reorg, selecting data using the clustering index instead of sorting, that *might* explain it.

John

Michael Hannan

Re: Reliability of DB2 zOS RTS Indexspacestats LASTUSED column
(in response to Peter Vanroose)

 In Reply to Peter Vanroose:

  • The whole transaction, including the record insert (and the other index inserts that already took place) is rolled back. 

Ha ha, it is a little bit surprising that the "whole transaction" is rolled back, to me at least. Hopefully I am not the only one who did not know that. That does not seem to be the case when an error occurs for an UPDATE where only the Update's work is undone (I believe). It seems the full "rollback" for INSERT can be avoided using clause NOT ATOMIC CONTINUE ON SQLEXCEPTION.

I found an example of an index last used in 2014, yet the table was Reorged 176 days ago. It was also the cluster index. So I assume it means LASTUSED being updated may depend how the Reorg was done as others mentioned. Maybe some Reorg access is considered and "access path" use.

I agree that Insert, Update and Delete need not change the Last Used, unless Index was an access path index or RI check index. Otherwise there would be no point at all in having the column. It is useful for finding unused indexes, even by Dynamic SQLs.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Venkat Srinivasan

RE: Reliability of DB2 zOS RTS Indexspacestats LASTUSED column
(in response to Peter Vanroose)

Peter, No matter what I do I cant seem to reproduce the scenario where REORG updates lastused. I tried sharelevel change and played around sortdata and recluster combinations for a variety of pagesets and single column data as well as multi column data. i have also tried all combinations on statistics to see if that wd have any effect. i used access db to force the rts externalization as well as waited several hours during the tests. In my tests it is always null for the lone clustering index.

Will you please share the reorg syntax and what version of db2 i must test this scenario if at all this is version dependent? Have you considered a Q&A ETR with support. 

Venkat

Peter Vanroose

RE: Reliability of DB2 zOS RTS Indexspacestats LASTUSED column
(in response to Venkat Srinivasan)

Venkat,

It was a REORG almost without options, i.e.: just all defaults used:

   TEMPLATE DDUNLOAD                             
DSN TBISTSO.&US..REORG.&DB.
DISP(MOD,DELETE,CATLG)
UNIT SYSDA
LISTDEF X INCLUDE TABLESPACES TABLE TEST.TEST
REORG TABLESPACE LIST X
UNLDDN DDUNLOAD

DDL was:

CREATE TABLE TEST.TEST (A INTEGER  NOT NULL );
CREATE INDEX TEST.I_A ON TEST.TEST (A) CLUSTER; 

So also the DDL was the minimal possible (hence we have an implicit universal tablespace).
I ran this on a Db2 10 system.  The REORG was run after some (about 100) initial INSERTs.

To be very sure, I just re-did the reorg today, and closely monitored the RTS table.
A new value (current timestamp) appeared immediately in column UPDATESTATSTIME of sysibm.sysindexspacestats (no stats synch wait time, apparently). But no change to the LASTUSED column (yet).

Turned out I had to wait 15 minutes (of course). And then indeed LASTUSED shows up as "today".

FYI, this is the output of the REORG job:

 DSNU3340I   151 03:21:39.40 DSNUGSRT - UTILITY PERFORMS DYNAMIC ALLOCATION OF SORT DISK SPACE
DSNU251I 151 03:21:39.54 DSNURULD - UNLOAD PHASE STATISTICS - NUMBER OF RECORDS UNLOADED=96 FOR TABLESPACE
DSN04162.TEST PART 1
DSNU252I 151 03:21:39.54 DSNURULD - UNLOAD PHASE STATISTICS - NUMBER OF RECORDS UNLOADED=96 FOR TABLESPACE
DSN04162.TEST
DSNU250I 151 03:21:39.54 DSNURULD - UNLOAD PHASE COMPLETE, ELAPSED TIME=00:00:00
DSNU3340I 151 03:21:40.19 DSNUGSOR - UTILITY PERFORMS DYNAMIC ALLOCATION OF SORT DISK SPACE
DSNU303I @ 151 03:21:40.30 DSNURWT - (RE)LOAD PHASE STATISTICS - NUMBER OF RECORDS=96 FOR TABLE TEST.TEST PART=1
DSNU304I @ 151 03:21:40.30 DSNURWT - (RE)LOAD PHASE STATISTICS - NUMBER OF RECORDS=96 FOR TABLE TEST.TEST
DSNU302I 151 03:21:40.32 DSNURILD - (RE)LOAD PHASE STATISTICS - NUMBER OF INPUT RECORDS PROCESSED=96
DSNU300I 151 03:21:40.32 DSNURILD - (RE)LOAD PHASE COMPLETE, ELAPSED TIME=00:00:00
DSNU042I 151 03:21:40.33 DSNUGSOR - SORT PHASE STATISTICS -
NUMBER OF RECORDS=96
ELAPSED TIME=00:00:00
DSNU349I @ 151 03:21:40.41 DSNURBXA - BUILD PHASE STATISTICS - NUMBER OF KEYS=96 FOR INDEX TEST.I_A
DSNU258I 151 03:21:40.41 DSNURBXD - BUILD PHASE STATISTICS - NUMBER OF INDEXES=1
DSNU259I 151 03:21:40.41 DSNURBXD - BUILD PHASE COMPLETE, ELAPSED TIME=00:00:00

 

In Reply to Venkat Srinivasan:

Peter, No matter what I do I cant seem to reproduce the scenario where REORG updates lastused. I tried sharelevel change and played around sortdata and recluster combinations for a variety of pagesets and single column data as well as multi column data. i have also tried all combinations on statistics to see if that wd have any effect. i used access db to force the rts externalization as well as waited several hours during the tests. In my tests it is always null for the lone clustering index.

Will you please share the reorg syntax and what version of db2 i must test this scenario if at all this is version dependent?

--      Peter Vanroose
        ABIS Training & Consulting,
        Leuven, Belgium.
        https://www.abis.be/

Michael Hannan

RE: Reliability of DB2 zOS RTS Indexspacestats LASTUSED column
(in response to Peter Vanroose)

Peter,

Do you have any theory as to why the index was "Used"? The SORT Phase sorts index keys in order to REBUILD the index. The Unload phase sorts data if you have a cluster index (yes you do) and you don't specify SORTDATA NO, right?

No! Just checking the DB2 V10 manual, the rule was different. Unload did a sort if SORTDATA was specified or SHRLEVEL Change was specified.  So I am speculating that if you specified SORTDATA, DB2 might not have "Used" the index. You could try that.

Does Unload have messages to indicate that a sort occurred? If not it would have to "use" the Index to get rows in order. Still bad idea to register that as a LASTUSED though! Seems to be a fault (or a feature). Maybe the issue was already addressed in a later DB2 Release? I would not know and am not in a position to test it right now. Maybe one of the V12 sites guys will volunteer ? (Hint LOL )

I do really rely on LASTUSED having good info for indexes, so would be nice if we could trust it.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Edited By:
Michael Hannan[Organization Members] @ May 31, 2018 - 11:38 AM (Europe/Berlin)

Peter Vanroose

Re: Reliability of DB2 zOS RTS Indexspacestats LASTUSED column
(in response to Michael Hannan)

In Reply to Michael Hannan:

[...] Just checking the DB2 V10 manual, the rule was different. Unload did a sort if SORTDATA was specified or SHRLEVEL Change was specified.  So I am speculating that if you specified SORTDATA, DB2 might not have "Used" the index. You could try that.

Does Unload have messages to indicate that a sort occurred? If not it would have to "use" the Index to get rows in order.

Actually, also in Db2 10, according to the Utility Reference Guide, SORTDATA is the default, also in this case.
And indeed, adding SORTDATA explicitly did not change the behaviour: LASTUSED still updated; also, the REORGINDEXACCESS column is incremented.

As to your question about unload messages: they indeed state (for all my REORG runs up to now ;-) that DFSORT was called, so it's not UNLOAD which was using the clustering index:

 ICE201I 0 RECORD TYPE IS V - DATA STARTS IN POSITION 5
ICE000I 0 - CONTROL STATEMENTS FOR 5650-ZOS, Z/OS DFSORT V2R1 - 03:30 ON THU MAY 31, 2018 -
SORT FIELDS=(00005.0,00004.0,A),FORMAT=BI,FILSZ=U000000000000098
RECORD TYPE=V,LENGTH=(00022,00022,00022)
OPTION MSGPRT=ALL,MSGDDN=UTPRINT,AVGRLEN=00022,MAINSIZE=MAX,USEWKDD,SOR*
TDD=SORT
(etc.)


--      Peter Vanroose
        ABIS Training & Consulting,
        Leuven, Belgium.
        https://www.abis.be/

Venkat Srinivasan

RE: Reliability of DB2 zOS RTS Indexspacestats LASTUSED column
(in response to Peter Vanroose)

Peter, I can't reproduce this behavior in V11.

Venkat 

Peter Vanroose

RE: Reliability of DB2 zOS RTS Indexspacestats LASTUSED column
(in response to Venkat Srinivasan)

Well, that's a good thing: Let's call it a bug fix.
This makes the LASTUSED column more useful.

In Reply to Venkat Srinivasan:

Peter, I can't reproduce this behavior in V11.

--      Peter Vanroose
        ABIS Training & Consulting,
        Leuven, Belgium.
        https://www.abis.be/

Michael Hannan

Re: Reliability of DB2 zOS RTS Indexspacestats LASTUSED column
(in response to Peter Vanroose)



In Reply to Peter Vanroose:

Actually, also in Db2 10, according to the Utility Reference Guide, SORTDATA is the default, also in this case.
And indeed, adding SORTDATA explicitly did not change the behaviour: LASTUSED still updated; also, the REORGINDEXACCESS column is incremented. 

Aha, I was clearly confused by the manual wording: "UNLOAD Unloads the table space and sorts data if a clustering index exists and the utility job includes either the SORTDATA or SHRLEVEL CHANGE options." Maybe that is why they changed the wording in the V11 manual. I forgot to notice SORTDATA was the default,

I recall in old old days seeing some Reorgs with no Cluster Index defined, put the data back in same sequence it was in, in the disorganised tablespace. SORTDATA did not happen. Clusterratios of indexes did not change. LOL Some would say, its O.K. the Cluster defaults to the oldest index. Ha ha! However that was actually for the Insert row placement, but not for the Reorg.  Memories of old classic mistakes. In the modern day, do rows get sorted, if there is no cluster index? Just curious if something changed there, or Reorg tablespace would be a waste of time.

Many sites still fail to choose the right index to make the cluster to suit scans that are not Index Only. Not much use on an index only used for single row probes (full equals unique match) unless we can guarantee they are not random.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd