[AD] When to REORG on Db2 for z/OS

Daniel Luksetich

[AD] When to REORG on Db2 for z/OS
Folks,

I have a new article on my blog about how to determine when to REORG on Db2
for z/OS. Please check it out here:

https://www.db2expert.com/db2expert/when-to-reorg-on-db2-for-z-os/

I hope you find it useful!



Also, please register for the IDUG Virtual Conference. It's only $200USD and
you get a ton of on demand and live content, including my presentation "Db2
for z/OS Ultra High Performance and Tuning". If your organization won't
cover the cost maybe you can treat yourself. The return on investment is
well worth it!



Cheers and I hope for everyone's health and safety. Have a pleasant weekend!

Dan



+--------------------------------------+------------------------------------
-----------------------+

| Daniel L Luksetich | IBM Certified Advanced Database
Administrator - |

| IBM GOLD Consultant | Db2 10.1 for Linux UNIX and
Windows |

| IDUG Content Committee Past-Chairman | IBM Certified Database Adminstrator
- Db2 12 for z/OS |

| IDUG DB2-L Administrator | IBM Certified System Administrator
- Db2 11 for z/OS |

| URL: https://db2expert.com https://db2expert.com | IBM
Certified Application Developer - Db2 11 for z/OS |

+--------------------------------------+------------------------------------
-----------------------+





Attachments

  • image001.png (9.6k)
  • image002.png (14k)
  • image003.png (10.4k)
  • image004.png (11.7k)
  • image005.png (13.7k)
  • image006.png (13.1k)
  • image007.png (14.2k)
  • image008.png (7.5k)

Michael Hannan

RE: [AD] When to REORG on Db2 for z/OS
(in response to Daniel Luksetich)

Dan,

There could be a simple answer, but not simple to implement. Reorg a Table or index when its disorganisation is adversely affecting performance, or when forced to. Reorging never does harm unless we distribute too much freespace. Reorg not often enough is a common cause of degrading performance.  When is the clustering bad? All sorts of different figures are used. Even below 99% can be poor for some objects (i don't like to see less than 98% for objects with cluster index scanning). Most sites think one threshold value fits all, or more commonly don't worry about confusing CLUSTERRATIOF at all. DATAREPEATFACTORF is easier to understand (for some of us).

Unclustered Inserts measure unfortunately is tending to look at whether next row in scan (with index used) is nearby or not. Unfortunately does not count slightly misplaced rows (Near rows) that can blowout the Getpages used by a scan (which is estimated/measured by DATAREPEATFACTORF). So I check for badly degraded DATAREPEATFACTORF values also (when they have been collected that is). Hope it is rare to have lots of near misplaced rows but few far misplaced rows.

Clearly some objects need Reorg frequently and most don't. 

A very interesting and controversial topic. I like a bit of controversy. I go off on a Runstats tangent because it was dominating the topic perhaps a little too much. Stats can measure disorganisation though.

To me the Topic of Runstats is a rather different one, and even more controversial, and could well be handled separately. I am certainly strongly against the "3 Rs". The idea of set stats once and forget goes totally against Stats Feedback reporting Stale Stats, yet it has some merit.

I say fix Stats when they are causing the Optimizer to make much worse estimates than normal. Requires understanding the problems caused by the misrepresetative Stats unfortunately, which most sites don't. Collecting Stats after Reorg, could be one way to make the Optimizer miss-estimate the typical level of table and index disorganisation, assuming objects get badly disorganised for a long time before Reorg (clustering index under 98% can be bad already) It makes the CLUSTERRATIOF and DATAREPEATFACTORF look too good, after Table Reorg, if new Stats captured. The Optimizer is already far too optimistic without us making the table organisation look perfect.

Suppose one collects Stats after an index Reorg. The Reorg itself does not affect the CLUSTERRATIOF, DATAREPEATFACTORF, FIRSTKEYCARDF, FULLKEYCARDF,  nor the KEYCARD Stats in SYSCOLDIST. No point to update them. One could check this by trying running Index Stats both before and after the Reorg. Which Stats used by the optimizer are changed by the Reorg? Only NLEAF and NLEVELS I think. These could be repopulated with Update when desired, not necessarily after reorg, using SYSINDEXSPACESTATS values (FULLKEYCARDF too), also NPAGESF and CARDF for tables could be ammended using SYSTABLESPACESTATS data, and other Stats columns could be resynced with those values, like KEYCARDs (Syscoldist type C). 

If an Index is reorged, I strongly recommend DO NOT collect Inline Stats unless you will also collect Stats for the other indexes, to make the comparison of CLUSTERRATIOFs and DATAREPEATFACTORFs between the indexes, a fair one. The collected CLUSTERRATIOF could be either worse or better than the previous time collected, more likely worse. Strange that Reorg an index could make it look worse for performance in comparison to other indexes if Stats are collected. Ha ha.   Sites get a bad shock when REBIND switched access paths from a Matchcols 2 index to a Matchcols 1 index purely because of a very slightly superior CLUSTERRATIOF (according to Stats collected at possibly different dates) and overly optimistic filtering estimate host variables and for skewed data.

Reorging an index can't do harm. Runstating it can.

Actual RUNSTATS might be needed quite rarely to fix Column Stats e.g. when HIGH2KEY gets to be quite wrong, or when Freq Value Stats become unrepresentative, or rows expected in a partition is far lower than reality.  COLCARDF can be reduced to make filter factors better, especially fro skewed columns and for range predicates.

If we keep the Stats History tables we can look at how Stats change over time when collected regularly, and might lead us to collect some Stats (in non History) less frequently. Keeping Stats History can help with understanding change. Updates to main tables can also be backed out if previous stats are kept, when new stats were actually not desirable Stats, or only update History.

There are many things that could make one lean towards not collecting Runstats when running a Reorg. Hence I regard them as totally separate topics. Naturally no REBIND as well, after Reorg. Improved performance comes from Reorg without REBIND.

On another topic, does Reorg improve performance after a Mass Delete? I did not think this was a reason to Reorg but I could be wrong. What does it achieve? Further improve the Spacemap pages somehow?

I certainly don't want to see Stats recaptured when an object is temporarily empty, or even when temporarily reduced in size. That will lead to very bad access paths, perhaps saved by NPGTHRSH. I would say recapture Stats maybe after significant growth percentage but not after shrinkage. Do we need to drop the size of objects after mass delete? probably not. Maybe if they will never grow again. 

Michael Hannan,

DB2 Application Performance Specialist

Edited By:
Michael Hannan[Organization Members] @ Jul 11, 2020 - 02:36 PM (Australia/Melbourne)
Michael Hannan[Organization Members] @ Jul 11, 2020 - 02:37 PM (Australia/Melbourne)
Michael Hannan[Organization Members] @ Jul 11, 2020 - 02:41 PM (Australia/Melbourne)
Michael Hannan[Organization Members] @ Jul 11, 2020 - 03:04 PM (Australia/Melbourne)
Michael Hannan[Organization Members] @ Jul 11, 2020 - 03:16 PM (Australia/Melbourne)

Peter Vanroose

RE: [AD] When to REORG on Db2 for z/OS
(in response to Michael Hannan)

In Reply to Michael Hannan:

[...]. Hope it is rare to have lots of near misplaced rows but few far misplaced rows. [...]

That could easily be the case for tablespaces where you have set FREEPAGE instead of PCTFREE ...

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

Bill Gallagher

[External] [AD] When to REORG on Db2 for z/OS
(in response to Michael Hannan)
“On another topic, does Reorg improve performance after a Mass Delete? I did not think this was a reason to Reorg but I could be wrong. What does it achieve?”

A REORG after a mass delete will reclaim allocated but now unused space, thus making the tablespace smaller. Any queries that perform a tablespace scan will benefit in performance.

Bill Gallagher | Senior Systems Engineer, DBA

From: Michael Hannan <[login to unmask email]>
Sent: Saturday, July 11, 2020 12:34 AM
To: [login to unmask email]
Subject: [External] [DB2-L] - RE: [AD] When to REORG on Db2 for z/OS

***External Sender - Please Exercise Caution***


Dan,

There could be a simple answer, but not simple to implement. Reorg a Table or index when its disorganisation is adversely affecting performance, or when forced to.

Clearly some objects need Reorg frequently and most don't.

A very interesting and controversial topic. I like a bit of controversy. We go off on a Runstats tangent because it was dominating the topic perhaps a little too much.

To me the Topic of Runstats is a rather different one, and even more controversial, and could well be handled separately. I am certainly strongly against the "3 Rs". The idea of set stats once and forget goes totally against Stats Feedback reporting Stale Stats, yet it has some merit.

I say fix Stats when they are causing the Optimizer to make much worse estimates than normal. Requires understanding the problems caused by the misrepresetative Stats unfortunately, which most sites don't. Collecting Stats after Reorg, could be one way to make the Optimizer miss-estimate the typical level of table and index disorganisation, assuming objects get badly disorganised for a long time before Reorg (clustering index under 98% can be bad already) It makes the CLUSTERRATIOF and DATAREPEATFACTORF look too good, after Table Reorg, if new Stats captured. The Optimizer is already far too optimistic without us making the table organisation look perfect.

Suppose one collects Stats after an index Reorg. The Reorg itself does not affect the CLUSTERRATIOF, DATAREPEATFACTORF, FIRSTKEYCARDF, FULLKEYCARDF, nor the KEYCARD Stats in SYSCOLDIST. No point to update them. One could check this by trying running Index Stats both before and after the Reorg. Which Stats used by the optimizer are changed by the Reorg? Only NLEAF and NLEVELS I think. These could be repopulated with Update when desired, not necessarily after reorg, using SYSINDEXSPACESTATS values (FULLKEYCARDF too), also NPAGESF and CARDF for tables could be ammended using SYSTABLESPACESTATS data, and other Stats columns could be resynced with those values, like KEYCARDs (Syscoldist type C).

Actual RUNSTATS might be needed quite rarely to fix Column Stats e.g. when HIGH2KEY gets to be quite wrong, or when Freq Value Stats become unrepresentative, or rows expected in a partition is far lower than reality. COLCARDF can be reduced to make filter factors better, especially fro skewed columns and for range predicates.

If we keep the Stats History tables we can look at how Stats change over time when collected regularly, and might lead us to collect some Stats less frequently.

There are many things that could make one lean towards not collecting Runstats when running a Reorg. Hence I regard them as totally separate topics. Naturally no REBIND as well, after Reorg. Improved performance comes from Reorg without REBIND.

On another topic, does Reorg improve performance after a Mass Delete? I did not think this was a reason to Reorg but I could be wrong. What does it achieve? I certainly don't want to see Stats recaptured when an object is temporarily empty, or even when temporarily reduced in size. That will lead to very bad access paths, perhaps saved by NPGTHRSH.

Michael Hannan,

DB2 Application Performance Specialist

-----End Original Message-----
________________________________
This message (including any attachments) may contain confidential, proprietary, privileged and/or private information. The information is intended to be for the use of the individual or entity designated above. If you are not the intended recipient of this message, please notify the sender immediately, and delete the message and any attachments. Any disclosure, reproduction, distribution or other use of this message or any attachments by an individual or entity other than the intended recipient is prohibited.

TRVDiscDefault::1201

Daniel Luksetich

[External] [AD] When to REORG on Db2 for z/OS
(in response to Bill Gallagher)
Thank you Bill, Michael, and Peter for your comments!

Cheers,

Dan



+--------------------------------------+-----------------------------------------------------------+

| Daniel L Luksetich | IBM Certified Advanced Database Administrator – |

| IBM GOLD Consultant | Db2 10.1 for Linux UNIX and Windows |

| IDUG Content Committee Past-Chairman | IBM Certified Database Adminstrator – Db2 12 for z/OS |

| IDUG DB2-L Administrator | IBM Certified System Administrator – Db2 11 for z/OS |

| URL: https://db2expert.com https://db2expert.com | IBM Certified Application Developer – Db2 11 for z/OS |

+--------------------------------------+-----------------------------------------------------------+





From: Gallagher, Bill R <[login to unmask email]>
Sent: Monday, July 13, 2020 6:04 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: [External] [AD] When to REORG on Db2 for z/OS



“On another topic, does Reorg improve performance after a Mass Delete? I did not think this was a reason to Reorg but I could be wrong. What does it achieve?”



A REORG after a mass delete will reclaim allocated but now unused space, thus making the tablespace smaller. Any queries that perform a tablespace scan will benefit in performance.



Bill Gallagher | Senior Systems Engineer, DBA



From: Michael Hannan <[login to unmask email] <mailto:[login to unmask email]> >
Sent: Saturday, July 11, 2020 12:34 AM
To: [login to unmask email] <mailto:[login to unmask email]>
Subject: [External] [DB2-L] - RE: [AD] When to REORG on Db2 for z/OS




***External Sender - Please Exercise Caution***

Dan,

There could be a simple answer, but not simple to implement. Reorg a Table or index when its disorganisation is adversely affecting performance, or when forced to.

Clearly some objects need Reorg frequently and most don't.

A very interesting and controversial topic. I like a bit of controversy. We go off on a Runstats tangent because it was dominating the topic perhaps a little too much.

To me the Topic of Runstats is a rather different one, and even more controversial, and could well be handled separately. I am certainly strongly against the "3 Rs". The idea of set stats once and forget goes totally against Stats Feedback reporting Stale Stats, yet it has some merit.

I say fix Stats when they are causing the Optimizer to make much worse estimates than normal. Requires understanding the problems caused by the misrepresetative Stats unfortunately, which most sites don't. Collecting Stats after Reorg, could be one way to make the Optimizer miss-estimate the typical level of table and index disorganisation, assuming objects get badly disorganised for a long time before Reorg (clustering index under 98% can be bad already) It makes the CLUSTERRATIOF and DATAREPEATFACTORF look too good, after Table Reorg, if new Stats captured. The Optimizer is already far too optimistic without us making the table organisation look perfect.

Suppose one collects Stats after an index Reorg. The Reorg itself does not affect the CLUSTERRATIOF, DATAREPEATFACTORF, FIRSTKEYCARDF, FULLKEYCARDF, nor the KEYCARD Stats in SYSCOLDIST. No point to update them. One could check this by trying running Index Stats both before and after the Reorg. Which Stats used by the optimizer are changed by the Reorg? Only NLEAF and NLEVELS I think. These could be repopulated with Update when desired, not necessarily after reorg, using SYSINDEXSPACESTATS values (FULLKEYCARDF too), also NPAGESF and CARDF for tables could be ammended using SYSTABLESPACESTATS data, and other Stats columns could be resynced with those values, like KEYCARDs (Syscoldist type C).

Actual RUNSTATS might be needed quite rarely to fix Column Stats e.g. when HIGH2KEY gets to be quite wrong, or when Freq Value Stats become unrepresentative, or rows expected in a partition is far lower than reality. COLCARDF can be reduced to make filter factors better, especially fro skewed columns and for range predicates.

If we keep the Stats History tables we can look at how Stats change over time when collected regularly, and might lead us to collect some Stats less frequently.

There are many things that could make one lean towards not collecting Runstats when running a Reorg. Hence I regard them as totally separate topics. Naturally no REBIND as well, after Reorg. Improved performance comes from Reorg without REBIND.

On another topic, does Reorg improve performance after a Mass Delete? I did not think this was a reason to Reorg but I could be wrong. What does it achieve? I certainly don't want to see Stats recaptured when an object is temporarily empty, or even when temporarily reduced in size. That will lead to very bad access paths, perhaps saved by NPGTHRSH.

Michael Hannan,

DB2 Application Performance Specialist



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

_____

This message (including any attachments) may contain confidential, proprietary, privileged and/or private information. The information is intended to be for the use of the individual or entity designated above. If you are not the intended recipient of this message, please notify the sender immediately, and delete the message and any attachments. Any disclosure, reproduction, distribution or other use of this message or any attachments by an individual or entity other than the intended recipient is prohibited.

TRVDiscDefault::1201

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

Attachments

  • image001.png (9.6k)
  • image002.png (14k)
  • image003.png (10.4k)
  • image004.png (11.7k)
  • image005.png (13.7k)
  • image006.png (13.1k)
  • image007.png (14.2k)
  • image008.png (7.5k)

Michael Hannan

RE: [External] [AD] When to REORG on Db2 for z/OS
(in response to Bill Gallagher)

In Reply to Bill Gallagher:

“On another topic, does Reorg improve performance after a Mass Delete? I did not think this was a reason to Reorg but I could be wrong. What does it achieve?”

A REORG after a mass delete will reclaim allocated but now unused space, thus making the tablespace smaller. Any queries that perform a tablespace scan will benefit in performance.

Bill Gallagher | Senior Systems Engineer, DBA

Bill,

I am skeptical, so I guess I will have to test it.  I thought Jim Teng (Distinguished Engineer) had taught me many years ago that in fact TS Scan does not scan all the pages, but uses the Spacemap to decide what segments need to be scanned, and so was capable of scanning used segments.

If you had a TS containing multiple tables, I believed that the spacemap would tell DB2 which parts of the TS to scan for select for a given table.

I hope I did not misunderstand all those years. I am not clear if would apply to both Segmented and Partitioned. It should be quite easy for me to test it, when I get a spare moment.  It is possible that what Jim taught me was more for Dynamic Prefetch where Getpages were driving the prefetch, but does DB2 want to complete a Getpage, if the spacemap says the page is not in play?   It might on TS Scan access path, so I better check it to be sure.

Even TS Scan Prefetch surely has to be triggered by Getpage. It won't continue to scan the entire TS, if we don't issue a Getpage and fetch some rows.  So the question becomes does a TS Scan access path blindly keep issuing Getpages to pages shown to be empty by the spacemap or is it smarter? I have to think genius guys like Jim Teng designed it to be smarter if was at all possible, and he did tell me about some of the smarts in there.

Michael Hannan,
DB2 Application Performance Specialist

Edited By:
Michael Hannan[Organization Members] @ Jul 13, 2020 - 11:57 PM (Australia/Melbourne)

Michael Hannan

RE: [AD] When to REORG on Db2 for z/OS
(in response to Peter Vanroose)

In Reply to Peter Vanroose:

In Reply to Michael Hannan:

[...]. Hope it is rare to have lots of near misplaced rows but few far misplaced rows. [...]

That could easily be the case for tablespaces where you have set FREEPAGE instead of PCTFREE ...

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

Peter,

I always disliked FREEPAGE concept myself, never thinking that nearby misplaced rows were much better than far misplaced, although far ones could be more Sync I/O. Near enough is not good enough!

I don't think many of the experts are recommending using FREEPAGE in general, perhaps for very special cases only.  

 

Michael Hannan,
DB2 Application Performance Specialist

Daniel Luksetich

[AD] When to REORG on Db2 for z/OS
(in response to Michael Hannan)
I am shocked by the number of table spaces I have seen with FREEPAGE set. It seems like someone a long time ago had some generic table space settings and then they were simply copied as additional table spaces were made, disregarding any purpose or function.

Dan



+--------------------------------------+-----------------------------------------------------------+

| Daniel L Luksetich | IBM Certified Advanced Database Administrator – |

| IBM GOLD Consultant | Db2 10.1 for Linux UNIX and Windows |

| IDUG Content Committee Past-Chairman | IBM Certified Database Adminstrator – Db2 12 for z/OS |

| IDUG DB2-L Administrator | IBM Certified System Administrator – Db2 11 for z/OS |

| URL: https://db2expert.com https://db2expert.com | IBM Certified Application Developer – Db2 11 for z/OS |

+--------------------------------------+-----------------------------------------------------------+





From: Michael Hannan <[login to unmask email]>
Sent: Monday, July 13, 2020 9:02 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: [AD] When to REORG on Db2 for z/OS



In Reply to Peter Vanroose:

In Reply to Michael Hannan:

[...]. Hope it is rare to have lots of near misplaced rows but few far misplaced rows. [...]

That could easily be the case for tablespaces where you have set FREEPAGE instead of PCTFREE ...

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

Peter,

I always disliked FREEPAGE concept myself, never thinking that nearby misplaced rows were much better than far misplaced, although far ones could be more Sync I/O. Near enough is not good enough!

I don't think many of the experts are recommending using FREEPAGE in general, perhaps for very special cases only.



Michael Hannan,
DB2 Application Performance Specialist



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

Attachments

  • image001.png (9.6k)
  • image002.png (14k)
  • image003.png (10.4k)
  • image004.png (11.7k)
  • image005.png (13.7k)
  • image006.png (13.1k)
  • image007.png (14.2k)
  • image008.png (7.5k)

Michael Hannan

RE: [External] [AD] When to REORG on Db2 for z/OS
(in response to Bill Gallagher)

In Reply to Bill Gallagher:

“On another topic, does Reorg improve performance after a Mass Delete? I did not think this was a reason to Reorg but I could be wrong. What does it achieve?”

A REORG after a mass delete will reclaim allocated but now unused space, thus making the tablespace smaller. Any queries that perform a tablespace scan will benefit in performance.

Bill Gallagher | Senior Systems Engineer, DBA

Bill so I did a test, at least on a Segmented Tablespace containing 146 tables and 80K pages.

I did SELECT * from one of these small tables (8 rows) first checking the access path which was TS Scan.

Then I ran the query for real, quick as a flash for 8 rows of course. Total Getpages was 2!   I/Os was actually zero. Possible the 2 pages I needed were in the Bufferpool for some reason. Ah yes, I had a peek at the table using Catalog browser first, so for my measured query, no Prefetch I/Os were triggered at all, yet it had to find the right pages. Only way is via Spacemap or via an index. Access path was TS Scan though. 

So when doing a TS Scan, Db2 does not scan all the pages, it uses the Spacemap. 
Now Mass Delete for Segmented just resets the Spacemap. It does not actaully go through all the pages removing rows. So again the only way a TS Scan can know which pages actually have rows, is to use the spacemap, otherwise every page would need to be updated to empty.

Now Partitioned tables work slightly differently, and I have not proved the same for them. It is unclear if Mass Delete on partitioned is really a "Mass Delete" at all. its merely an unqualified DELETE that as to hit all the rows. Spacemaps for partitioned may not make it absolutely clear whether each page has any rows or not (I forget). So Reorg of Partitioned after all rows removed, may be more effective, for improving TS Scan performance. 

TS Scan of segmented and Universal are more efficient than Partitioned. No Reorg required after Mass Delete unless you are desperate to reclaim space. Old Segments can be reused.

Too many Extents is also NOT a reason to Reorg , mostly. What is the maximum extents now? Some huge number over 500 I think. Hopefully most of your pages are in Buffer Pool. Even if they are not they are often in smart disk cache. I cost to look an extent is not significant.  I have never been particularly concerned about extents other than if a risk of blowing a limit. Even in old days when Reorg was a bit slow, other types of Copy of pages could recombine Extents without doing Reorg sorts, index rebuilds, etc. Do we want to Reorg massive objects because in a lot of Extents? Absolutely not.

So many shops were Reorging for extents as the major criteria, while letting other spaces degrade to very poorly clustered, like 80% clustered is horrific for a cluster index, 98% is already poor (for me). This was laughable stuff for me, all those years ago. Industry "best practice" ain't best practice at all. 

IBM told us: "Don't ask us how it works, tell us your problem and we will tell you how to solve it"
What absolute bad advice!
We really gotta try to understand what goes on and what really matters, and why. So we can do it right ourselves.
Not easy of course.

Above all, we want to understand when and why we Reorg, and not just follow some rules of thumb, which may apply sometimes, but not always. If we don't measure performance, before and after Reorg, will we know if our strategy is right, or did we perhaps wait for too much degradation before we triggered the action?

Michael Hannan,
DB2 Application Performance Specialist

Edited By:
Michael Hannan[Organization Members] @ Jul 16, 2020 - 09:43 PM (Australia/Melbourne)