DB2 for z/OS - is setting PRIQTY/SECQTY to multiples of 720 still valid?

kapil mathur

DB2 for z/OS - is setting PRIQTY/SECQTY to multiples of 720 still valid?

Hi DB2-L members,


                  I had a very generic question about DB2 DASD allocations. I was always told that while allocating PRIQTY & SECQTY for tablespaces and indexspaces, one should always use either 720 Kb or multiples thereof, since we want the allocations in whole cylinders because that is more efficient (and that the amount of DB2-usable space in a Model 3390 DASD is always 720Kbytes).


   Is the above guideline still valid in the era of SANs and EMC VMAX Enterprise DASD?
Does it make any difference in retrieval efficiency if the disk space for all extents ends on a cylinder boundary (or not)?
Does the setting of ZPARM ALCUNIT still have any role in DB2 object disk allocations?  

Thanks in advance.   

Daniel Luksetich

DB2 for z/OS - is setting PRIQTY/SECQTY to multiples of 720 still valid?
(in response to kapil mathur)
Well call me new-fashioned, but I almost always use -1/-1 and forget about it! DBA’s shouldn’t be worrying about DASD anymore. Just my 2 cents…

Dan



Daniel L Luksetich

DanL Database Consulting



IBM GOLD Consultant

IBM Champion for Analytics

IDUG Content Committee Past-Chairman

IDUG DB2-L Administrator

IBM Certified Database Adminstrator – DB2 11 DBA for z/OS

IBM Certified System Administrator – DB2 11 for z/OS

IBM Certified Application Developer – DB2 11 for z/OS

IBM Certified Advanced Database Administrator – DB2 10.1 for Linux UNIX and Windows



From: kapil mathur <[login to unmask email]>
Sent: Thursday, May 24, 2018 12:17 PM
To: [login to unmask email]
Subject: [DB2-L] - DB2 for z/OS - is setting PRIQTY/SECQTY to multiples of 720 still valid?



Hi DB2-L members,


I had a very generic question about DB2 DASD allocations. I was always told that while allocating PRIQTY & SECQTY for tablespaces and indexspaces, one should always use either 720 Kb or multiples thereof, since we want the allocations in whole cylinders because that is more efficient (and that the amount of DB2-usable space in a Model 3390 DASD is always 720Kbytes).


Is the above guideline still valid in the era of SANs and EMC VMAX Enterprise DASD?
Does it make any difference in retrieval efficiency if the disk space for all extents ends on a cylinder boundary (or not)?
Does the setting of ZPARM ALCUNIT still have any role in DB2 object disk allocations?

Thanks in advance.



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

Kai Stroh

RE: DB2 for z/OS - is setting PRIQTY/SECQTY to multiples of 720 still valid?
(in response to kapil mathur)

Also keep in mind that, provided that MGEXTSZ is set to YES in your ZPARMs (which is the default), Db2 will always manage the secondary extent sizes using its sliding scale algorithm for any object that has a non-zero SECQTY.

Db2 has been doing this for a while now, at least since version 10:

https://www.ibm.com/support/knowledgecenter/en/SSEPEK_10.0.0/inst/src/tpc/db2z_ipf_mgextsz.html

Therefore, the way I see it, fine tuning the PRIQTY/SECQTY values does not really do anything other than set the initial size of your object. The only exception is if you set SECQTY to 0, then you get an object that can never ever grow. Not sure where this could be useful though.

--
Kai Stroh
UBS Hainer
Fast, efficient Db2 z/OS data migrations and renewals. That’s BCV5.
Learn how the Test Data Management Field Guide can help you to improve your own process.

Roy Boxwell

DB2 for z/OS - is setting PRIQTY/SECQTY to multiples of 720 still valid?
(in response to kapil mathur)
Set -1 and let db2 do it UNLESS you are using an old way of copying data... then -1 can bite ya!

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 24 May 2018, at 19:16, kapil mathur <[login to unmask email]<mailto:[login to unmask email]>> wrote:


Hi DB2-L members,

I had a very generic question about DB2 DASD allocations. I was always told that while allocating PRIQTY & SECQTY for tablespaces and indexspaces, one should always use either 720 Kb or multiples thereof, since we want the allocations in whole cylinders because that is more efficient (and that the amount of DB2-usable space in a Model 3390 DASD is always 720Kbytes).

Is the above guideline still valid in the era of SANs and EMC VMAX Enterprise DASD?
Does it make any difference in retrieval efficiency if the disk space for all extents ends on a cylinder boundary (or not)?
Does the setting of ZPARM ALCUNIT still have any role in DB2 object disk allocations?

Thanks in advance.

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

Roy Boxwell

DB2 for z/OS - is setting PRIQTY/SECQTY to multiples of 720 still valid?
(in response to Kai Stroh)
User spaces in QMF to “contain” run away users is a good reason for 0 secqty!

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 24 May 2018, at 22:35, Kai Stroh <[login to unmask email]<mailto:[login to unmask email]>> wrote:


Also keep in mind that, provided that MGEXTSZ is set to YES in your ZPARMs (which is the default), Db2 will always manage the secondary extent sizes using its sliding scale algorithm for any object that has a non-zero SECQTY.

Db2 has been doing this for a while now, at least since version 10:

https://www.ibm.com/support/knowledgecenter/en/SSEPEK_10.0.0/inst/src/tpc/db2z_ipf_mgextsz.html

Therefore, the way I see it, fine tuning the PRIQTY/SECQTY values does not really do anything other than set the initial size of your object. The only exception is if you set SECQTY to 0, then you get an object that can never ever grow. Not sure where this could be useful though.

--
Kai Stroh
UBS Hainer
Fast, efficient Db2 z/OS data migrations and renewals. That’s BCV5 https://www.ubs-hainer.com/solutions/bcv5 .
Learn how the Test Data Management Field Guide http://tdm.ubs-hainer.com can help you to improve your own process.

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

kapil mathur

RE: DB2 for z/OS - is setting PRIQTY/SECQTY to multiples of 720 still valid?
(in response to Roy Boxwell)

Thanks for all the responses ...  the consensus appears to be to use PRIQTY/SECQTY of -1/-1 .

As far as SECQTY 0 is concerned, I have seen it used in most of the IT shops for the tablespaces that make up the SORT WORK database(s) (DSNDB07 in non-data sharing terms)  - to prevent one bad SQL from chewing-up all available free DASD for SORT WORK in the entire shop.        

Jim Tonchick

DB2 for z/OS - is setting PRIQTY/SECQTY to multiples of 720 still valid?
(in response to Daniel Luksetich)

I agree with Daniel. Pretend you are the old TV pitch man, Ron Popiel, and "Set it and forget it.".

Back when this first became available (V7?), I tested it on some OEM vendor DB2 SMF data history tables. After several months of watching the objects grow REORG by REORG without having to adjust PRIQTY and SECQTY, I altered all the other tablespaces and indexspaces I controlled.

Jim Tonchick
<div>
-----Original Message-----
From: Daniel L Luksetich <[login to unmask email]>
To: db2-l <[login to unmask email]>
Sent: Thu, May 24, 2018 12:32 PM
Subject: [DB2-L] - RE: DB2 for z/OS - is setting PRIQTY/SECQTY to multiples of 720 still valid?



<div id="AOLMsgPart_2_aa26571e-3b5a-4ee6-9584-fbe8ac4c1df3">
<style scoped="">#AOLMsgPart_2_aa26571e-3b5a-4ee6-9584-fbe8ac4c1df3 td{color: black;} @font-face {font-family:"Cambria Math"; panose-1:2 4 5 3 5 4 6 3 2 4;}@font-face {font-family:Calibri; panose-1:2 15 5 2 2 2 4 3 2 4;}.aolReplacedBody p.aolmail_MsoNormal,.aolReplacedBody li.aolmail_MsoNormal,.aolReplacedBody div.aolmail_MsoNormal {margin:0in; margin-bottom:.0001pt; font-size:11.0pt; font-family:"Calibri",sans-serif;}.aolReplacedBody a:link,.aolReplacedBody span.aolmail_MsoHyperlink {mso-style-priority:99; color:blue; text-decoration:underline;}.aolReplacedBody a:visited,.aolReplacedBody span.aolmail_MsoHyperlinkFollowed {mso-style-priority:99; color:purple; text-decoration:underline;}.aolReplacedBody p.aolmail_msonormal0,.aolReplacedBody li.aolmail_msonormal0,.aolReplacedBody div.aolmail_msonormal0 {mso-style-name:msonormal; mso-margin-top-alt:auto; margin-right:0in; mso-margin-bottom-alt:auto; margin-left:0in; font-size:11.0pt; font-family:"Calibri",sans-serif;}.aolReplacedBody span.aolmail_EmailStyle20 {mso-style-type:personal-reply; font-family:"Calibri",sans-serif; color:windowtext;}.aolReplacedBody .aolmail_MsoChpDefault {mso-style-type:export-only; font-family:"Calibri",sans-serif;}@page WordSection1 {size:8.5in 11.0in; margin:1.0in 1.0in 1.0in 1.0in;}.aolReplacedBody div.aolmail_WordSection1 {page:WordSection1;}</style><div lang="EN-US" class="aolReplacedBody"><div class="aolmail_WordSection1"><p class="aolmail_MsoNormal">Well call me new-fashioned, but I almost always use -1/-1 and forget about it! DBA’s shouldn’t be worrying about DASD anymore. Just my 2 cents…</p><p class="aolmail_MsoNormal">Dan</p><p class="aolmail_MsoNormal"> </p><p class="aolmail_MsoNormal">Daniel L Luksetich</p><p class="aolmail_MsoNormal">DanL Database Consulting</p><p class="aolmail_MsoNormal"> </p><p class="aolmail_MsoNormal">IBM GOLD Consultant</p><p class="aolmail_MsoNormal">IBM Champion for Analytics</p><p class="aolmail_MsoNormal">IDUG Content Committee Past-Chairman</p><p class="aolmail_MsoNormal">IDUG DB2-L Administrator</p><p class="aolmail_MsoNormal">IBM Certified Database Adminstrator – DB2 11 DBA for z/OS</p><p class="aolmail_MsoNormal">IBM Certified System Administrator – DB2 11 for z/OS</p><p class="aolmail_MsoNormal">IBM Certified Application Developer – DB2 11 for z/OS</p><p class="aolmail_MsoNormal">IBM Certified Advanced Database Administrator – DB2 10.1 for Linux UNIX and Windows</p><p class="aolmail_MsoNormal"> </p><p class="aolmail_MsoNormal"><b>From:</b> kapil mathur <db2-<a href="mailto:[login to unmask email]">[login to unmask email]</a>>
<b>Sent:</b> Thursday, May 24, 2018 12:17 PM
<b>To:</b> DB2-<a href="mailto:[login to unmask email]">[login to unmask email]</a>
<b>Subject:</b> [DB2-L] - DB2 for z/OS - is setting PRIQTY/SECQTY to multiples of 720 still valid?</p><p class="aolmail_MsoNormal"> </p><p>Hi DB2-L members,</p><p>
I had a very generic question about DB2 DASD allocations. I was always told that while allocating PRIQTY & SECQTY for tablespaces and indexspaces, one should always use either 720 Kb or multiples thereof, since we want the allocations in whole cylinders because that is more efficient (and that the amount of DB2-usable space in a Model 3390 DASD is always 720Kbytes).</p><p>
Is the above guideline still valid in the era of SANs and EMC VMAX Enterprise DASD?
Does it make any difference in retrieval efficiency if the disk space for all extents ends on a cylinder boundary (or not)?
Does the setting of ZPARM ALCUNIT still have any role in DB2 object disk allocations? </p><p>Thanks in advance. </p><p class="aolmail_MsoNormal"> </p><div class="aolmail_MsoNormal" align="center" style="text-align:center"><div align="left">-----End Original Message-----</div></div>
<hr size="1" style="color:#ccc"><div id="aolmail_socfooter" style="font-size:80%"><span style="font-weight:bold">Site Links: </span>
<a target="_blank" rel="noopener noreferrer" href="https://www.idug.org/p/fo/st/?post=185921&anc=p185921#p185921">View post online</a>
<a target="_blank" rel="noopener noreferrer" href="https://www.idug.org/p/fo/si/?topic=19">View mailing list online</a>
<a target="_blank" rel="noopener noreferrer" href="mailto:[login to unmask email]">Start new thread via email</a>
<a target="_blank" rel="noopener noreferrer" href="mailto:[login to unmask email]?Subject=Unsubscribe">Unsubscribe from this mailing list</a>
<a target="_blank" rel="noopener noreferrer" href="https://www.idug.org/p/us/to/">Manage your subscription</a>


This email has been sent to: <a href="mailto:[login to unmask email]">[login to unmask email]</a>
<p>
Faster data refresh is here! The long waits and babysitting of unload/load jobs is over. Contact

ESAi to learn about BCV5 & XDM. Be a hero to users with fast on-demand test/QA data provisioning.See

<a target="_blank" rel="noopener noreferrer" href="http://www.ESAIGroup.com/idug">http://www.ESAIGroup.com/idug</a>

</p>
<p>
Use of this email content is governed by the terms of service at:
<a target="_blank" rel="noopener noreferrer" href="http://www.idug.org/p/cm/ld/fid=2">http://www.idug.org/p/cm/ld/fid=2</a></p>

</div><hr size="1" style="color:#ccc"></div>
</div>
</div></div>

Venkat Srinivasan

RE: DB2 for z/OS - is setting PRIQTY/SECQTY to multiples of 720 still valid?
(in response to kapil mathur)

The ZPARM ALCUNIT is for archive datasets. Where do you see it is applicable for pageset allocation?.

In my limited knowledge, there is no advantage to cylinder boundary in "modern" DISKs in terms of performance. But the behavior of space release when the dataset is closed will change based on track vs cylinder vs blocks. However that is not for DB2 but more for JCL allocations where you code a RLSE parm. So a storage person will have a different perspective of this question. 

DB2 will choose the secondary based on an algorithm. Here is something you may want to know for small tables. If you set primary as 12kb and secondary as 12 kb you will get 1 track for primary but for the first secondary you will get 30 tracks and thereafter 15 tracks but not one track. When secqty is not coded, and with a primary of 12kb (a track), the sliding secondary kicks in at about 8th extent if sliding secondary is enabled. You can try this out for yourself in a little science project.

-1 , -1 for pritqty and secqty makes more sense and works but keep in mind packaged vendors are notorious in delivering a large number of objects with less than a track data. Most objects in home grown design will have one cylinder worth of data anyway.

The more extents you have the more the open time. But you shouldn't be setting up dsmax low enough to be opening for every sql reference on that table.  

Venkat

Jorg Lueke

RE: DB2 for z/OS - is setting PRIQTY/SECQTY to multiples of 720 still valid?
(in response to Jim Tonchick)

I did the same, though only with SECQTY -1. Back then that is. Now, there really isn't a reason to create new objects with any other allocation. Disk is just a giant, magical, black box now.
 
In Reply to Jim Tonchick:


I agree with Daniel. Pretend you are the old TV pitch man, Ron Popiel, and "Set it and forget it.".

Back when this first became available (V7?), I tested it on some OEM vendor DB2 SMF data history tables. After several months of watching the objects grow REORG by REORG without having to adjust PRIQTY and SECQTY, I altered all the other tablespaces and indexspaces I controlled.

Jim Tonchick

Michael Hannan

RE: DB2 for z/OS - is setting PRIQTY/SECQTY to multiples of 720 still valid?
(in response to kapil mathur)



In Reply to kapil mathur:

I had a very generic question about DB2 DASD allocations. I was always told that while allocating PRIQTY & SECQTY for tablespaces and indexspaces, one should always use either 720 Kb or multiples thereof, since we want the allocations in whole cylinders because that is more efficient (and that the amount of DB2-usable space in a Model 3390 DASD is always 720Kbytes).

Others gave very good answers, re letting DB do it. Even in old times, the recommendation did not make a lot of sense. No big problem to allocate large objects in multiples of a Cylinder, but I never did it, even as a dinosaur. After all we have buffer pools, and I did not want to put a 10 row table in one Cylinder. 

Similarly Segsize 64 is recommended in general, but makes little sense for very small rows tables, without doing a lot of harm either. So I would recommend Segsize 64 for tables (partitions) that could grow beyond  64 pages. For small tables Segsize 4 is quite OK. Never saw a need for in between Segsizes really. Too complicated. Segsize 4 is definitely bad for large tables with high Insert rates. It makes the spacemaps inefficient.

I have been to a site that used a lot more than double the disk space allocation that they needed for DB2 tables.  The reason was the smallest allocation was 720. However they had numerous environments, being a complete set of application tables (with different Authids). The also had very numerous small tables and indexes of just one page or so. To always round up the space allocation to a Cylinder boundary meant that a very large number of tables were massively over allocated totalling to a quite large number of wasted cylinders.

Then we heard that they were having to remove a lot of logical Unload copies of the data used for backups at different stages of the batch processing, in testing environments because they were short on disk space and the administrators were not keen to order a lot more disk space. LOL. So I immediately noticed that 80 to 90% of the tables were heavily over allocated, due to the 720 rule. So I thought about the possibility to suggest generating space ALTERs for all the tables and indexes, so that future Reorgs, or Load Replace restores of tables from logical Unloads, would start getting the space back. Did not happen but was a brief thought. Better if they did not allocate by 720 in the first place. Believe it not, this was not many years ago.

Amusing thing when disk is relatively cheap these days.

From a performance point of view, over allocation does not matter too much. The tablespace spacemap shows which pages have data to be scanned in a "tablespace scan".  Very high PCTFREE in pages does have a performance downside for scans, due to more Getpages, and more I/Os. For indexes high PCTFREE can cause your indexes to go to more levels, impacting random access probe Getpages a little bit.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Edited By:
Michael Hannan[Organization Members] @ May 31, 2018 - 10:57 AM (Europe/Berlin)
Michael Hannan[Organization Members] @ May 31, 2018 - 11:06 AM (Europe/Berlin)
Michael Hannan[Organization Members] @ May 31, 2018 - 11:07 AM (Europe/Berlin)
Michael Hannan[Organization Members] @ May 31, 2018 - 11:08 AM (Europe/Berlin)