DB2 for Z/OS, alternatives in REORG for OFFPOSLIMIT, INDREFLIMIT & LEAFDISTLIMIT

Jo Dautzenberg

DB2 for Z/OS, alternatives in REORG for OFFPOSLIMIT, INDREFLIMIT & LEAFDISTLIMIT

OFFPOSLIMIT, INDREFLIMIT & LEAFDISTLIMIT options are deprecated and will not work anymore in new releases.

IBM says the alternative is running the stored procedure DSNACCOX.

BUT DSNACCOX is only a REPORT, so ONLY the Alternative for the the REPORTONLY option.

I want my Reorg to run automatically and only when needed without studying reports and spending to much time.

What is the best alternative for:

LISTDEF TS2REORG INCLUDE TABLESPACES TABLESPACE MYDBASE.*
LISTDEF IX2REORG INCLUDE INDEXSPACES INDEXSPACE MYDBASE.*
REORG TABLESPACE LIST TS2REORG LOG NO SHRLEVEL CHANGE
STATISTICS TABLE ALL INDEX ALL
UPDATE ALL
OFFPOSLIMIT 10
INDREFLIMIT 10
REORG INDEX LIST IX2REORG SHRLEVEL CHANGE
STATISTICS UPDATE ALL
LEAFDISTLIMIT 200

Myron Miller

DB2 for Z/OS, alternatives in REORG for OFFPOSLIMIT, INDREFLIMIT & LEAFDISTLIMIT
(in response to Jo Dautzenberg)
It isn't that difficult to setup something like a Rexx or COBOL program to call DSNACCOX and receive a list of tablespaces that should be reorg'd based upon input criteria and to format that list into a set of control cards for a reorg. I even use an exception table to have it ignore special tables that never should be considered, such as tables that are constantly loaded, deleted and reloaded, super small tables that would always be picked up in any reorg test and other types of tables that we wanted to ignore for various reasons (especially LOB and XML tables).


I even split the indexes and tablespaces into two parts so that each are reorg'd separately as separate jobs. I suppose I could have built one reorg job for both, but it was easier to make it fully generic and have separate steps for indexes and tablespaces. We look at about 3000 tablespaces each week and do between 10 and 30 tablespaces and indexes a week for reorgs. We do less now than when we were using OFFPOSLIMIT, etc as we can refine the requirements and needs better with DSNACCOX.


Next step is waiting to see how much the new index fixing stuff in V12 does in elimination of need of reorg's. Thinking it might eliminate over 80+% of our current index reorgs.


Thanks Myron W. Miller


________________________________
From: Jo Dautzenberg <[login to unmask email]>
Sent: Wednesday, August 23, 2017 8:30 AM
To: [login to unmask email]
Subject: [DB2-L] - DB2 for Z/OS, alternatives in REORG for OFFPOSLIMIT, INDREFLIMIT & LEAFDISTLIMIT


OFFPOSLIMIT, INDREFLIMIT & LEAFDISTLIMIT options are deprecated and will not work anymore in new releases.

IBM says the alternative is running the stored procedure DSNACCOX.

BUT DSNACCOX is only a REPORT, so ONLY the Alternative for the the REPORTONLY option.

I want my Reorg to run automatically and only when needed without studying reports and spending to much time.

What is the best alternative for:

LISTDEF TS2REORG INCLUDE TABLESPACES TABLESPACE MYDBASE.*
LISTDEF IX2REORG INCLUDE INDEXSPACES INDEXSPACE MYDBASE.*
REORG TABLESPACE LIST TS2REORG LOG NO SHRLEVEL CHANGE
STATISTICS TABLE ALL INDEX ALL
UPDATE ALL
OFFPOSLIMIT 10
INDREFLIMIT 10
REORG INDEX LIST IX2REORG SHRLEVEL CHANGE
STATISTICS UPDATE ALL
LEAFDISTLIMIT 200

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

Joe Geller

RE: DB2 for Z/OS, alternatives in REORG for OFFPOSLIMIT, INDREFLIMIT & LEAFDISTLIMIT
(in response to Myron Miller)

I think you will also find that you can have much better control of identifying the tables that need reorg by using the metrics from DSNACCOX.  The problem with the Reorg conditional tests is that they are based on percentages, which is actually not very useful for large tables. 

For example, INDREFLIMIT is the % of rows that have been moved to another page because an Update increased the size of the row and there was not enough room on the original page.  An anchor is left on the original page and the indexes still point to that location.  Far Indirect References are very bad for tablespace scan performance.  But it is the number of indirect references that affect the performance, not the percentage.  With a very large table, if you reorged last week (and eliminated all indirect references), then even if you created a large number this week, the % of the table may be a small %.  INDREFLIMIT is an integer, so the smallest number you can specify is 1% (10% is the default).  I've had many cases where the % of rows is less than .1% but performance has been affected and a reorg is needed.

Joe

In Reply to Myron Miller:

It isn't that difficult to setup something like a Rexx or COBOL program to call DSNACCOX and receive a list of tablespaces that should be reorg'd based upon input criteria and to format that list into a set of control cards for a reorg. I even use an exception table to have it ignore special tables that never should be considered, such as tables that are constantly loaded, deleted and reloaded, super small tables that would always be picked up in any reorg test and other types of tables that we wanted to ignore for various reasons (especially LOB and XML tables).


I even split the indexes and tablespaces into two parts so that each are reorg'd separately as separate jobs. I suppose I could have built one reorg job for both, but it was easier to make it fully generic and have separate steps for indexes and tablespaces. We look at about 3000 tablespaces each week and do between 10 and 30 tablespaces and indexes a week for reorgs. We do less now than when we were using OFFPOSLIMIT, etc as we can refine the requirements and needs better with DSNACCOX.


Next step is waiting to see how much the new index fixing stuff in V12 does in elimination of need of reorg's. Thinking it might eliminate over 80+% of our current index reorgs.


Thanks Myron W. Miller


________________________________
From: Jo Dautzenberg <[login to unmask email]>
Sent: Wednesday, August 23, 2017 8:30 AM
To: [login to unmask email]
Subject: [DB2-L] - DB2 for Z/OS, alternatives in REORG for OFFPOSLIMIT, INDREFLIMIT & LEAFDISTLIMIT


OFFPOSLIMIT, INDREFLIMIT & LEAFDISTLIMIT options are deprecated and will not work anymore in new releases.

IBM says the alternative is running the stored procedure DSNACCOX.

BUT DSNACCOX is only a REPORT, so ONLY the Alternative for the the REPORTONLY option.

I want my Reorg to run automatically and only when needed without studying reports and spending to much time.

What is the best alternative for:

LISTDEF TS2REORG INCLUDE TABLESPACES TABLESPACE MYDBASE.*
LISTDEF IX2REORG INCLUDE INDEXSPACES INDEXSPACE MYDBASE.*
REORG TABLESPACE LIST TS2REORG LOG NO SHRLEVEL CHANGE
STATISTICS TABLE ALL INDEX ALL
UPDATE ALL
OFFPOSLIMIT 10
INDREFLIMIT 10
REORG INDEX LIST IX2REORG SHRLEVEL CHANGE
STATISTICS UPDATE ALL
LEAFDISTLIMIT 200

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

Jo Dautzenberg

DB2 for Z/OS, alternatives in REORG for OFFPOSLIMIT, INDREFLIMIT & LEAFDISTLIMIT
(in response to Joe Geller)
We are a small team of 4, managing over 60 DB2 subsystems (installing and maintaining) with all kinds of related software,
IDAA, IBM Data Studio, DB2 Admin tool, IMS, CA Insight, CA SYSVIEW, File Aid, BMC Mainview, BMC DB2 and BMC IMS, etc etc etc.
Within a couple of months we get a new customer with again over 20 DB2 subsystems and all kinds of software we have never worked with nor installed.
Most of our customers don’t have any DBA people anymore and we do not have time, but we are somehow responsible for their DB2 Housekeeping (backup and reorgs).
So I want something that works, and not cost any effort nor time. These online reorgs with OFFPOSLIMIT, INDREFLIMIT & LEAFDISTLIMIT, introduced 3-4 years ago and I never had to look in them anymore.
But now I need to because they get deprecated probably next year when we are going to install V12.
Again we are no DBA and have no time to look into nor changing with settings and adjusting time over time.


From: Joe Geller [mailto:[login to unmask email]
Sent: Wednesday, August 23, 2017 4:55 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: DB2 for Z/OS, alternatives in REORG for OFFPOSLIMIT, INDREFLIMIT & LEAFDISTLIMIT


I think you will also find that you can have much better control of identifying the tables that need reorg by using the metrics from DSNACCOX. The problem with the Reorg conditional tests is that they are based on percentages, which is actually not very useful for large tables.

For example, INDREFLIMIT is the % of rows that have been moved to another page because an Update increased the size of the row and there was not enough room on the original page. An anchor is left on the original page and the indexes still point to that location. Far Indirect References are very bad for tablespace scan performance. But it is the number of indirect references that affect the performance, not the percentage. With a very large table, if you reorged last week (and eliminated all indirect references), then even if you created a large number this week, the % of the table may be a small %. INDREFLIMIT is an integer, so the smallest number you can specify is 1% (10% is the default). I've had many cases where the % of rows is less than .1% but performance has been affected and a reorg is needed.

Joe

In Reply to Myron Miller:
It isn't that difficult to setup something like a Rexx or COBOL program to call DSNACCOX and receive a list of tablespaces that should be reorg'd based upon input criteria and to format that list into a set of control cards for a reorg. I even use an exception table to have it ignore special tables that never should be considered, such as tables that are constantly loaded, deleted and reloaded, super small tables that would always be picked up in any reorg test and other types of tables that we wanted to ignore for various reasons (especially LOB and XML tables).


I even split the indexes and tablespaces into two parts so that each are reorg'd separately as separate jobs. I suppose I could have built one reorg job for both, but it was easier to make it fully generic and have separate steps for indexes and tablespaces. We look at about 3000 tablespaces each week and do between 10 and 30 tablespaces and indexes a week for reorgs. We do less now than when we were using OFFPOSLIMIT, etc as we can refine the requirements and needs better with DSNACCOX.


Next step is waiting to see how much the new index fixing stuff in V12 does in elimination of need of reorg's. Thinking it might eliminate over 80+% of our current index reorgs.


Thanks Myron W. Miller


________________________________
From: Jo Dautzenberg
Sent: Wednesday, August 23, 2017 8:30 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - DB2 for Z/OS, alternatives in REORG for OFFPOSLIMIT, INDREFLIMIT & LEAFDISTLIMIT


OFFPOSLIMIT, INDREFLIMIT & LEAFDISTLIMIT options are deprecated and will not work anymore in new releases.

IBM says the alternative is running the stored procedure DSNACCOX.

BUT DSNACCOX is only a REPORT, so ONLY the Alternative for the the REPORTONLY option.

I want my Reorg to run automatically and only when needed without studying reports and spending to much time.

What is the best alternative for:

LISTDEF TS2REORG INCLUDE TABLESPACES TABLESPACE MYDBASE.*
LISTDEF IX2REORG INCLUDE INDEXSPACES INDEXSPACE MYDBASE.*
REORG TABLESPACE LIST TS2REORG LOG NO SHRLEVEL CHANGE
STATISTICS TABLE ALL INDEX ALL
UPDATE ALL
OFFPOSLIMIT 10
INDREFLIMIT 10
REORG INDEX LIST IX2REORG SHRLEVEL CHANGE
STATISTICS UPDATE ALL
LEAFDISTLIMIT 200

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

-----End Original Message-----
This e-mail and the documents attached are confidential and intended solely for the addressee; it may also be privileged. If you receive this e-mail in error, please notify the sender immediately and destroy it. As its integrity cannot be secured on the Internet, Atos’ liability cannot be triggered for the message content. Although the sender endeavours to maintain a computer virus-free network, the sender does not warrant that this transmission is virus-free and will not be liable for any damages resulting from any virus transmitted. On all offers and agreements under which Atos Nederland B.V. supplies goods and/or services of whatever nature, the Terms of Delivery from Atos Nederland B.V. exclusively apply. The Terms of Delivery shall be promptly submitted to you on your request.

J&#248;rn Thyssen

RE: DB2 for Z/OS, alternatives in REORG for OFFPOSLIMIT, INDREFLIMIT & LEAFDISTLIMIT
(in response to Jo Dautzenberg)

Hi Jo,

As mentioned you will need to write your own REXX routines that call DSNACCOX and generate utility statements and JCL based on the recommendations. I have seen this implemented at many customer sites. 

If you don't want to code and maintain home-written programs then all the Db2 tools vendors have solutions that can automate this task, such that when it has been set up you can forget about it the next three years :) 

 

Best regards,

Jørn Thyssen

Rocket Software
77 Fourth Avenue • Waltham, MA • 02451 • USA
E: [login to unmask email] • W: www.rocketsoftware.com 

Views are personal. 

John Bucaria

DB2 for Z/OS, alternatives in REORG for OFFPOSLIMIT, INDREFLIMIT & LEAFDISTLIMIT
(in response to Myron Miller)
Is there a way to execute DSNACCOX without writing a program? Perhaps one of the IBM supplied DSN programs?

Thanks,
John

From: Myron Miller [mailto:[login to unmask email]
Sent: Wednesday, August 23, 2017 9:54 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: DB2 for Z/OS, alternatives in REORG for OFFPOSLIMIT, INDREFLIMIT & LEAFDISTLIMIT


It isn't that difficult to setup something like a Rexx or COBOL program to call DSNACCOX and receive a list of tablespaces that should be reorg'd based upon input criteria and to format that list into a set of control cards for a reorg. I even use an exception table to have it ignore special tables that never should be considered, such as tables that are constantly loaded, deleted and reloaded, super small tables that would always be picked up in any reorg test and other types of tables that we wanted to ignore for various reasons (especially LOB and XML tables).



I even split the indexes and tablespaces into two parts so that each are reorg'd separately as separate jobs. I suppose I could have built one reorg job for both, but it was easier to make it fully generic and have separate steps for indexes and tablespaces. We look at about 3000 tablespaces each week and do between 10 and 30 tablespaces and indexes a week for reorgs. We do less now than when we were using OFFPOSLIMIT, etc as we can refine the requirements and needs better with DSNACCOX.



Next step is waiting to see how much the new index fixing stuff in V12 does in elimination of need of reorg's. Thinking it might eliminate over 80+% of our current index reorgs.


Thanks Myron W. Miller

________________________________
From: Jo Dautzenberg <[login to unmask email]<mailto:[login to unmask email]>>
Sent: Wednesday, August 23, 2017 8:30 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - DB2 for Z/OS, alternatives in REORG for OFFPOSLIMIT, INDREFLIMIT & LEAFDISTLIMIT


OFFPOSLIMIT, INDREFLIMIT & LEAFDISTLIMIT options are deprecated and will not work anymore in new releases.

IBM says the alternative is running the stored procedure DSNACCOX.

BUT DSNACCOX is only a REPORT, so ONLY the Alternative for the the REPORTONLY option.

I want my Reorg to run automatically and only when needed without studying reports and spending to much time.

What is the best alternative for:

LISTDEF TS2REORG INCLUDE TABLESPACES TABLESPACE MYDBASE.*
LISTDEF IX2REORG INCLUDE INDEXSPACES INDEXSPACE MYDBASE.*
REORG TABLESPACE LIST TS2REORG LOG NO SHRLEVEL CHANGE
STATISTICS TABLE ALL INDEX ALL
UPDATE ALL
OFFPOSLIMIT 10
INDREFLIMIT 10
REORG INDEX LIST IX2REORG SHRLEVEL CHANGE
STATISTICS UPDATE ALL
LEAFDISTLIMIT 200

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

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

Russell Peters

RE: DB2 for Z/OS, alternatives in REORG for OFFPOSLIMIT, INDREFLIMIT & LEAFDISTLIMIT
(in response to Jo Dautzenberg)

We're on DB2 11 NFM and still use these reorg parameters. I've been told that although these were deprecated long ago there are no plans to remove them and they will still work in DB2 12. You might want to check with IBM to be sure about that as it's been a while since I asked. I also would like to use DSNACCOX instead but would need programming time to use the report to generate listdefs. I've asked my IBM contacts to consider incorporating DSNACCOX into the db2 utilities if that's the replacement for the old parameters. I'd like to eventually use DSNACCOX because it will also find all structures in reorg pending so I can get those reorganized along with the ones needing a reorg for performance reasons.

J&#248;rn Thyssen

RE: DB2 for Z/OS, alternatives in REORG for OFFPOSLIMIT, INDREFLIMIT & LEAFDISTLIMIT
(in response to Russell Peters)

Hi Russell,

I would not set my hopes too high. 

As I understand it the direction is that the core Db2 utilities runs the requested utility unconditionally. Any logic to figure out what utility to run on which objects is outside the scope of the core utilities. This is why the "conditional" options have been deprecated for REORG and COPY. 

You either have to hope that the deprecated options are never removed or eventually write your own routines based on DSNACCOX, alternatively buy a solution from IBM or one of the other vendors.  As someone else mentioned in the thread DSNACCOX will also recommended REORG and COPY of objects in advisory states. The vendor programs allow for even more complex logic. 

Best regards,

Jørn Thyssen

Rocket Software
77 Fourth Avenue • Waltham, MA • 02451 • USA
E: [login to unmask email] • W: www.rocketsoftware.com 

Views are personal. 

Phil Grainger

DB2 for Z/OS, alternatives in REORG for OFFPOSLIMIT, INDREFLIMIT & LEAFDISTLIMIT
(in response to Jørn Thyssen)
And you have to wonder what is going on

While “the rest of us” build logic INTO the utilities to enable them to make runtime decisions, IBM seem to be going the other way

Or perhaps they are encouraging people to look at buying the Utility Automation Tool?
________________________________

Phil Grainger

Lead Product 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: Jørn Thyssen [mailto:[login to unmask email]
Sent: 24 August 2017 19:09
To: [login to unmask email]
Subject: [DB2-L] - RE: DB2 for Z/OS, alternatives in REORG for OFFPOSLIMIT, INDREFLIMIT & LEAFDISTLIMIT


Hi Russell,

I would not set my hopes too high.

As I understand it the direction is that the core Db2 utilities runs the requested utility unconditionally. Any logic to figure out what utility to run on which objects is outside the scope of the core utilities. This is why the "conditional" options have been deprecated for REORG and COPY.

You either have to hope that the deprecated options are never removed or eventually write your own routines based on DSNACCOX, alternatively buy a solution from IBM or one of the other vendors. As someone else mentioned in the thread DSNACCOX will also recommended REORG and COPY of objects in advisory states. The vendor programs allow for even more complex logic.

Best regards,

Jørn Thyssen

Rocket Software
77 Fourth Avenue • Waltham, MA • 02451 • USA
E: [login to unmask email]<mailto:[login to unmask email]> • W: www.rocketsoftware.com http://www.rocketsoftware.com

Views are personal.

-----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)
  • image004.png (5.9k)

Philip Sevetson

DB2 for Z/OS, alternatives in REORG for OFFPOSLIMIT, INDREFLIMIT & LEAFDISTLIMIT
(in response to Phil Grainger)
**please note my email address change**
Phil,

They might, in fact, be encouraging people to buy the utility. On the other hand, it might be that they’re focusing on giving the *execution* of the utility all of the performance options and outage-minimization which they can. They seem to be focused on implementing performance and outage options as command-line (?) parameters, so that anyone can write generator code based on any criteria which they like, using tools like DSNACCOX or their third-party or roll-your-own (or a combination of those).

I’m not saying that this is necessarily what they’re thinking, but it is a real consequence. Those of us who roll-our-own on the utility control benefit from that alongside the people who use existing conditional execution rules, so their development benefits a larger community.

I don’t have any problem at all with semantically uncoupling the utility execution command from the conditions which determine whether the utility will run. In fact, I think it’s a pretty good idea.

Philip Sevetson
Computer Systems Manager
5 Manhattan West (33rd St at 10th Ave)
New York, NY 10001-2632
212-857-1688 w
917-991-7052 c
212-857-1659 f
[cid:[login to unmask email]

From: Grainger, Phil [mailto:[login to unmask email]
Sent: Thursday, August 31, 2017 10:54 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: DB2 for Z/OS, alternatives in REORG for OFFPOSLIMIT, INDREFLIMIT & LEAFDISTLIMIT

And you have to wonder what is going on

While “the rest of us” build logic INTO the utilities to enable them to make runtime decisions, IBM seem to be going the other way

Or perhaps they are encouraging people to look at buying the Utility Automation Tool?
-----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.
-----End Original Message-----


**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**
Attachments

  • image001.png (3.3k)