-1 -1 PQTY SQTY - ZPARMS

ROGER SMITH

-1 -1 PQTY SQTY - ZPARMS
Hello

Do you know what zparms are associated with using -1 -1 ?

I am proposing to management that we implement -1 -1 for PQTY SQTY tablespace allocations.
The main justification is to reclaim a ton of space from over allocations.
Yes - many shops have already gone there but at least we are using SMS defined Stogroups.

The question came back:

‘Need to review and ensure all zparms are set appropriately for -1, -1 usage to work as designed.
I know MGEXTSZ was one

________________________________

The information contained in this e-mail, and any attachment, is confidential and is intended solely for the use of the intended recipient. Access, copying or re-use of the e-mail or any attachment, or any information contained therein, by any other person is not authorized. If you are not the intended recipient please return the e-mail to the sender and delete it from your computer. Although we attempt to sweep e-mail and attachments for viruses, we do not guarantee that either are virus-free and accept no liability for any damage sustained as a result of viruses.

Please refer to https://disclaimer.bnymellon.com/eu.htm for certain disclosures relating to European legal entities. We take our data protection and privacy responsibilities seriously and our privacy notice explains how we collect, use and share personal information in the course of our business activities. It can be accessed at the privacy section of www.bnymellon.com.

Michael Hannan

RE: -1 -1 PQTY SQTY - ZPARMS
(in response to ROGER SMITH)

See Redbook "DB2 9 for z/OS and Storage Management". Parms TSQTY and IXQTY had relevance, but not my area of expertise.

Sites can have extra time taken for backup and restore of various environments, and extra disk space taken, when a very large number of very small tables are grossly over allocated, e.g 1 cylinder minimum space for all tables.  That is one scenario  I saw recently.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Roy Boxwell

-1 -1 PQTY SQTY - ZPARMS
(in response to ROGER SMITH)
Rule 4 on this KC page is the bit about MGEXTSZ



https://www.ibm.com/support/knowledgecenter/SSEPEK_12.0.0/intro/src/tpc/db2z_primaryspaceallocation.html



I am a great fan of using a positive integer for PRIQTY and not using SECQTY. Then you get the “best of both worlds” as you can move datasets around without worry too much but still get the maximum possible secondary’s using the sliding scale.



Roy Boxwell

SOFTWARE ENGINEERING GmbH and SEGUS Inc.
-Product Development-

Heinrichstrasse 83-85
40239 Duesseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: <mailto:[login to unmask email]> [login to unmask email]
Web http://www.seg.de http://www.seg.de

https://www.seg.de/corporate/rechtliche-hinweise/datenschutz Link zur Datenschutzerklärung


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



From: Smith, Roger [mailto:[login to unmask email]
Sent: Monday, January 7, 2019 8:07 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: -1 -1 PQTY SQTY - ZPARMS



Hello



Do you know what zparms are associated with using -1 -1 ?



I am proposing to management that we implement -1 -1 for PQTY SQTY tablespace allocations.

The main justification is to reclaim a ton of space from over allocations.

Yes - many shops have already gone there but at least we are using SMS defined Stogroups.



The question came back:



‘Need to review and ensure all zparms are set appropriately for -1, -1 usage to work as designed.

I know MGEXTSZ was one



_____


The information contained in this e-mail, and any attachment, is confidential and is intended solely for the use of the intended recipient. Access, copying or re-use of the e-mail or any attachment, or any information contained therein, by any other person is not authorized. If you are not the intended recipient please return the e-mail to the sender and delete it from your computer. Although we attempt to sweep e-mail and attachments for viruses, we do not guarantee that either are virus-free and accept no liability for any damage sustained as a result of viruses.

Please refer to https://disclaimer.bnymellon.com/eu.htm for certain disclosures relating to European legal entities. We take our data protection and privacy responsibilities seriously and our privacy notice explains how we collect, use and share personal information in the course of our business activities. It can be accessed at the privacy section of www.bnymellon.com.

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

Attachments

  • smime.p7s (5.1k)

Peter Vanroose

Re: -1 -1 PQTY SQTY - ZPARMS
(in response to Roy Boxwell)

Roy,

I suppose it's indeed good to have an explicit PRIQTY.

But if you have a well-chosen PRIQTY from the moment of the tablespace creation, and you have never revisited (and gradually incremented) that value (with growing table content), you'll run into exactly the same (and sometimes even a slightly worse) situation as with -1, since Db2 will again be taking larger and larger secondary extents (maybe just 2 or 3) so you end up again in too largely allocated tablespaces.

Ideally you should be looking regualry at the RTS (real time statistics) data for setting an "optimal" PRIQTY (ideally before each REORG).

In Reply to Roy Boxwell:

I am a great fan of using a positive integer for PRIQTY and not using SECQTY. Then you get the “best of both worlds” as you can move datasets around without worry too much but still get the maximum possible secondary’s using the sliding scale.

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

Roy Boxwell

-1 -1 PQTY SQTY - ZPARMS
(in response to Peter Vanroose)
Yep! It really is “it depends”... For some people -1 -1 is the best way (No usage of DSN1COPY etc.) for others the sliding scale with a good initial is enough for yet others set both...



That’s one of the things I love about Db2 – Whatever problem you have – there is probably a solution!



Roy Boxwell

SOFTWARE ENGINEERING GmbH and SEGUS Inc.
-Product Development-

Heinrichstrasse 83-85
40239 Duesseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: <mailto:[login to unmask email]> [login to unmask email]
Web http://www.seg.de http://www.seg.de

https://www.seg.de/corporate/rechtliche-hinweise/datenschutz Link zur Datenschutzerklärung


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



From: Peter Vanroose [mailto:[login to unmask email]
Sent: Tuesday, January 8, 2019 12:44 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: -1 -1 PQTY SQTY - ZPARMS



Roy,

I suppose it's indeed good to have an explicit PRIQTY.

But if you have a well-chosen PRIQTY from the moment of the tablespace creation, and you have never revisited (and gradually incremented) that value (with growing table content), you'll run into exactly the same (and sometimes even a slightly worse) situation as with -1, since Db2 will again be taking larger and larger secondary extents (maybe just 2 or 3) so you end up again in too largely allocated tablespaces.

Ideally you should be looking regualry at the RTS (real time statistics) data for setting an "optimal" PRIQTY (ideally before each REORG).

In Reply to Roy Boxwell:

I am a great fan of using a positive integer for PRIQTY and not using SECQTY. Then you get the “best of both worlds” as you can move datasets around without worry too much but still get the maximum possible secondary’s using the sliding scale.

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



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

Attachments

  • smime.p7s (5.1k)

Phil Grainger

-1 -1 PQTY SQTY - ZPARMS
(in response to Roy Boxwell)
“Whatever problem you have – there is always more than one solution!” perhaps puts it better

________________________________




[BMC Exchange 2019 - Global Event Series - REGISTER] https://www.bmc.com/ami


Phil Grainger
Principal Enablement Manager

Direct

+44 1189 218 000

Mobile

+44 7808 643 479

Email

[login to unmask email]

E2, Eskdale Road
Winnersh
Berkshire
United Kingdom
RG41 5TS
[http://media.cms.bmc.com/images/federal_email_signature_logo.jpg][https://acclaim-production-app.s3.amazonaws.com/images/2429c3cd-a1de-44fc-b4f3-bc762bb2f963/IBM%2BChampion%2B-%2BAnalytics%2B2018.png][https://acclaim-production-app.s3.amazonaws.com/images/2429c3cd-a1de-44fc-b4f3-bc762bb2f963/IBM%2BChampion%2B-%2BAnalytics%2B2018.png]






















From: Boxwell, Roy [mailto:[login to unmask email]
Sent: 08 January 2019 12:09
To: [login to unmask email]
Subject: [DB2-L] - RE: -1 -1 PQTY SQTY - ZPARMS

Yep! It really is “it depends”... For some people -1 -1 is the best way (No usage of DSN1COPY etc.) for others the sliding scale with a good initial is enough for yet others set both...

That’s one of the things I love about Db2 – Whatever problem you have – there is probably a solution!

Roy Boxwell

SOFTWARE ENGINEERING GmbH and SEGUS Inc.
-Product Development-

Heinrichstrasse 83-85
40239 Duesseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]<mailto:[login to unmask email]>
Web http://www.seg.de https://urldefense.proofpoint.com/v2/url?u=http-3A__www.seg.de_&d=DwMFaQ&c=UrUhmHsiTVT5qkaA4d_oSzcamb9hmamiCDMzBAEwC7E&r=EAGrd_qzLADPfI8dgytr8sbCG7_U9QfXwQMLgK1Zo30&m=AJ_TPO_8JU_epBTXYM4UF7AWyaItyCnYAc04VabdOvU&s=l6PHD7khQk23ZBkw67gl4AlB4ZGSf9Vne5SxS9u_FS8&e=
Link zur Datenschutzerklärung https://urldefense.proofpoint.com/v2/url?u=https-3A__www.seg.de_corporate_rechtliche-2Dhinweise_datenschutz_&d=DwMFaQ&c=UrUhmHsiTVT5qkaA4d_oSzcamb9hmamiCDMzBAEwC7E&r=EAGrd_qzLADPfI8dgytr8sbCG7_U9QfXwQMLgK1Zo30&m=AJ_TPO_8JU_epBTXYM4UF7AWyaItyCnYAc04VabdOvU&s=gLo-9UuziL3ueZeeu4MJx38Ew7i4gZDUJp6la_n3KT4&e=

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

From: Peter Vanroose [mailto:[login to unmask email]
Sent: Tuesday, January 8, 2019 12:44 PM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: -1 -1 PQTY SQTY - ZPARMS


Roy,

I suppose it's indeed good to have an explicit PRIQTY.

But if you have a well-chosen PRIQTY from the moment of the tablespace creation, and you have never revisited (and gradually incremented) that value (with growing table content), you'll run into exactly the same (and sometimes even a slightly worse) situation as with -1, since Db2 will again be taking larger and larger secondary extents (maybe just 2 or 3) so you end up again in too largely allocated tablespaces.

Ideally you should be looking regualry at the RTS (real time statistics) data for setting an "optimal" PRIQTY (ideally before each REORG).

In Reply to Roy Boxwell:
I am a great fan of using a positive integer for PRIQTY and not using SECQTY. Then you get the “best of both worlds” as you can move datasets around without worry too much but still get the maximum possible secondary’s using the sliding scale.

-- Peter Vanroose
ABIS Training & Consulting,
Leuven, Belgium.
https://www.abis.be/ https://urldefense.proofpoint.com/v2/url?u=https-3A__www.abis.be_html_enDB2Calendar.html&d=DwMFaQ&c=UrUhmHsiTVT5qkaA4d_oSzcamb9hmamiCDMzBAEwC7E&r=EAGrd_qzLADPfI8dgytr8sbCG7_U9QfXwQMLgK1Zo30&m=AJ_TPO_8JU_epBTXYM4UF7AWyaItyCnYAc04VabdOvU&s=lnM-mRNdWLNcDBjo8iWjD3BhtLfNbBW1KuSbYdwu6zY&e=

-----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 (49.7k)
  • image002.jpg (1.6k)
  • image003.png (<1k)
  • image004.png (3.3k)

ROGER SMITH

-1 -1 PQTY SQTY - ZPARMS
(in response to Roy Boxwell)
Thank you – in this shop a rule was given couple years back to allocate 1 gig of PQTY for all new object in Dev , QA , PROD.

Once done most DBA’s never revisits PQTY. Time went by and now we have many empty or almost empty tablespaces with this 1 gig allocated.
Lots of wasted space in over allocations. Especially true in Dev where objects are replicated many times with different qualifiers.

Pasted LISTCAT is from sample non-part TS that was defined with -1 -1.
Data was delivered via Load and eventually 26 VSAM datasets were allocated.

Is my reading correct:
Primary is 1 cyl for YDBA24.DSNDBD.DBNAME.TSNAME.I0001.A001
Then each extent is a varying size based on formula.
At a certain size/extents YDBA24.DSNDBD.DBNAME.TSNAME.I0001.A002 is created on different volume and process repeats

Do you have link to How extent allocations are determined when using -1 -1?


1IDCAMS SYSTEM SERVICES TIME: 07:58:02 01/08/19 PAGE 1
0
LISTCAT ENTRIES(YDBA24.DSNDBD.DBNAME.TSNAME.I0001.A001) ALL
0DATA ---------- YDBA24.DSNDBD.DBNAME.TSNAME.I0001.A001
IN-CAT --- CATD00
HISTORY
DATASET-OWNER-----(NULL) CREATION--------2018.352
RELEASE----------------2 EXPIRATION------0000.000
ACCOUNT-INFO-----------------------------------(NULL)
PROTECTION-PSWD-----(NULL) RACF----------------(NO)
ASSOCIATIONS
CLUSTER--YDBA24.DSNDBC.DBNAME.TSNAME.I0001.A001
ATTRIBUTES
KEYLEN-----------------0 AVGLRECL---------------0 BUFSPACE------------8192 CISIZE--------------4096
RKP--------------------0 MAXLRECL---------------0 EXCPEXIT----------(NULL) CI/CA----------------180
SHROPTNS(3,3) RECOVERY UNIQUE NOERASE LINEAR NOWRITECHK UNORDERED REUSE
NONSPANNED
STATISTICS
REC-TOTAL--------------0 SPLITS-CI--------------0 EXCPS------------------0
REC-DELETED------------0 SPLITS-CA--------------0 EXTENTS---------------21
REC-INSERTED-----------0 FREESPACE-%CI----------0 SYSTEM-TIMESTAMP:
REC-UPDATED------------0 FREESPACE-%CA----------0 X'0000000000000000'
REC-RETRIEVED----------0 FREESPC----------------0
ALLOCATION
SPACE-TYPE------CYLINDER HI-A-RBA------2147696640
SPACE-PRI--------------1 HI-U-RBA------2147483648
SPACE-SEC--------------1
VOLUME
VOLSER------------DP9763 PHYREC-SIZE---------4096 HI-A-RBA------2147696640 EXTENT-NUMBER---------21
DEVTYPE------X'3010200F' PHYRECS/TRK-----------12 HI-U-RBA------2147483648 EXTENT-TYPE--------X'40'
VOLFLAG------------PRIME TRACKS/CA-------------15
EXTENTS:
LOW-CCHH-----X'016D0000' LOW-RBA----------------0 TRACKS---------------990
HIGH-CCHH----X'01AE000E' HIGH-RBA--------48660479
LOW-CCHH-----X'01CB0000' LOW-RBA---------48660480 TRACKS--------------1860
HIGH-CCHH----X'0246000E' HIGH-RBA-------140083199
LOW-CCHH-----X'03D80000' LOW-RBA--------140083200 TRACKS---------------300
HIGH-CCHH----X'03EB000E' HIGH-RBA-------154828799
LOW-CCHH-----X'045A0000' LOW-RBA--------154828800 TRACKS--------------2115
HIGH-CCHH----X'04E6000E' HIGH-RBA-------258785279
LOW-CCHH-----X'05A10000' LOW-RBA--------258785280 TRACKS--------------1710
HIGH-CCHH----X'0612000E' HIGH-RBA-------342835199
LOW-CCHH-----X'06280000' LOW-RBA--------342835200 TRACKS--------------1950
HIGH-CCHH----X'06A9000E' HIGH-RBA-------438681599
LOW-CCHH-----X'070D0000' LOW-RBA--------438681600 TRACKS---------------525
HIGH-CCHH----X'072F000E' HIGH-RBA-------464486399
LOW-CCHH-----X'07680000' LOW-RBA--------464486400 TRACKS--------------1665
HIGH-CCHH----X'07D6000E' HIGH-RBA-------546324479
LOW-CCHH-----X'08C10000' LOW-RBA--------546324480 TRACKS--------------1800
HIGH-CCHH----X'0938000E' HIGH-RBA-------634798079
LOW-CCHH-----X'095D0000' LOW-RBA--------634798080 TRACKS---------------630
1IDCAMS SYSTEM SERVICES TIME: 07:58:02 01/08/19 PAGE 2
0 HIGH-CCHH----X'0986000E' HIGH-RBA-------665763839
LOW-CCHH-----X'09910000' LOW-RBA--------665763840 TRACKS--------------1305
HIGH-CCHH----X'09E7000E' HIGH-RBA-------729907199
LOW-CCHH-----X'0A7A0000' LOW-RBA--------729907200 TRACKS--------------1365
HIGH-CCHH----X'0AD4000E' HIGH-RBA-------796999679
LOW-CCHH-----X'0B820000' LOW-RBA--------796999680 TRACKS--------------3675
HIGH-CCHH----X'0C76000E' HIGH-RBA-------977633279
LOW-CCHH-----X'0E780000' LOW-RBA--------977633280 TRACKS--------------7560
HIGH-CCHH----X'106F000E' HIGH-RBA------1349222399
LOW-CCHH-----X'20050000' LOW-RBA-------1349222400 TRACKS---------------915
HIGH-CCHH----X'2041000E' HIGH-RBA------1394196479
LOW-CCHH-----X'23F10000' LOW-RBA-------1394196480 TRACKS--------------1875
HIGH-CCHH----X'246D000E' HIGH-RBA------1486356479
LOW-CCHH-----X'259C0000' LOW-RBA-------1486356480 TRACKS--------------2925
HIGH-CCHH----X'265E000E' HIGH-RBA------1630126079
LOW-CCHH-----X'26D90000' LOW-RBA-------1630126080 TRACKS--------------1005
HIGH-CCHH----X'271B000E' HIGH-RBA------1679523839
LOW-CCHH-----X'2E250000' LOW-RBA-------1679523840 TRACKS--------------7455
HIGH-CCHH----X'3015000E' HIGH-RBA------2045951999
LOW-CCHH-----X'33B20000' LOW-RBA-------2045952000 TRACKS--------------1125
HIGH-CCHH----X'33FC000E' HIGH-RBA------2101247999
LOW-CCHH-----X'37450000' LOW-RBA-------2101248000 TRACKS---------------945
HIGH-CCHH----X'3783000E' HIGH-RBA------2147696639
1IDCAMS SYSTEM SERVICES TIME: 07:58:02 01/08/19 PAGE 3



Command - Enter "/" to select action , Message , Volume
-------------------------------------------------------------------------------
YDBA24.DSNDBD.DBNAME.TSNAME.I0001.A001 , , DP9763
YDBA24.DSNDBD.DBNAME.TSNAME.I0001.A002 , , DP2079+
YDBA24.DSNDBD.DBNAME.TSNAME.I0001.A003 , , DP9661
YDBA24.DSNDBD.DBNAME.TSNAME.I0001.A004 , , DP302B+
YDBA24.DSNDBD.DBNAME.TSNAME.I0001.A005 , , DP9294+
YDBA24.DSNDBD.DBNAME.TSNAME.I0001.A006 , , MIGRAT1
YDBA24.DSNDBD.DBNAME.TSNAME.I0001.A007 , , DP2004
YDBA24.DSNDBD.DBNAME.TSNAME.I0001.A008 , , DP9749
YDBA24.DSNDBD.DBNAME.TSNAME.I0001.A009 , , MIGRAT1
YDBA24.DSNDBD.DBNAME.TSNAME.I0001.A010 , , MIGRAT1
YDBA24.DSNDBD.DBNAME.TSNAME.I0001.A011 , , DP3211
YDBA24.DSNDBD.DBNAME.TSNAME.I0001.A012 , , DP9076
YDBA24.DSNDBD.DBNAME.TSNAME.I0001.A013 , , DP9028
YDBA24.DSNDBD.DBNAME.TSNAME.I0001.A014 , , DP9372+
YDBA24.DSNDBD.DBNAME.TSNAME.I0001.A015 , , DP9671
YDBA24.DSNDBD.DBNAME.TSNAME.I0001.A016 , , DP9663

From: Boxwell, Roy <[login to unmask email]>
Sent: Tuesday, January 8, 2019 7:09 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: -1 -1 PQTY SQTY - ZPARMS

Yep! It really is “it depends”... For some people -1 -1 is the best way (No usage of DSN1COPY etc.) for others the sliding scale with a good initial is enough for yet others set both...

That’s one of the things I love about Db2 – Whatever problem you have – there is probably a solution!

Roy Boxwell

SOFTWARE ENGINEERING GmbH and SEGUS Inc.
-Product Development-

Heinrichstrasse 83-85
40239 Duesseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]<mailto:[login to unmask email]>
Web http://www.seg.de http://www.seg.de
Link zur Datenschutzerklärung https://www.seg.de/corporate/rechtliche-hinweise/datenschutz

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

From: Peter Vanroose [mailto:[login to unmask email]
Sent: Tuesday, January 8, 2019 12:44 PM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: -1 -1 PQTY SQTY - ZPARMS


Roy,

I suppose it's indeed good to have an explicit PRIQTY.

But if you have a well-chosen PRIQTY from the moment of the tablespace creation, and you have never revisited (and gradually incremented) that value (with growing table content), you'll run into exactly the same (and sometimes even a slightly worse) situation as with -1, since Db2 will again be taking larger and larger secondary extents (maybe just 2 or 3) so you end up again in too largely allocated tablespaces.

Ideally you should be looking regualry at the RTS (real time statistics) data for setting an "optimal" PRIQTY (ideally before each REORG).

In Reply to Roy Boxwell:
I am a great fan of using a positive integer for PRIQTY and not using SECQTY. Then you get the “best of both worlds” as you can move datasets around without worry too much but still get the maximum possible secondary’s using the sliding scale.

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

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

The information contained in this e-mail, and any attachment, is confidential and is intended solely for the use of the intended recipient. Access, copying or re-use of the e-mail or any attachment, or any information contained therein, by any other person is not authorized. If you are not the intended recipient please return the e-mail to the sender and delete it from your computer. Although we attempt to sweep e-mail and attachments for viruses, we do not guarantee that either are virus-free and accept no liability for any damage sustained as a result of viruses.

Please refer to https://disclaimer.bnymellon.com/eu.htm for certain disclosures relating to European legal entities. We take our data protection and privacy responsibilities seriously and our privacy notice explains how we collect, use and share personal information in the course of our business activities. It can be accessed at the privacy section of www.bnymellon.com.

Russell Peters

RE: -1 -1 PQTY SQTY - ZPARMS
(in response to ROGER SMITH)

We have both IXQYT and TSQTY set to 100 and have MGEXTSZ=YES. We used to have issues with space but haven't had a single space issue in db2 since changing these zparms. That's been a number of years ago. I purposely set them low to avoid wasting space. The extent management in db2 works great. I still do sometimes set very small reference tables to a lower number, and on purchased software that has a number of unused tables I set primary and secondary very low on the unused tables.

Roy Boxwell

-1 -1 PQTY SQTY - ZPARMS
(in response to ROGER SMITH)
https://www.ibm.com/support/knowledgecenter/SSEPEK_12.0.0/intro/src/tpc/db2z_primaryspaceallocation.html





This details how the sliding scale works. The ZPARMs:



TABLE SPACE ALLOCATION field (TSQTY subsystem parameter)

The TSQTY subsystem parameter specifies the amount of space in KB that is to be allocated for primary and secondary spaces. This parameter applies to Db2-defined data sets for table spaces that are created without the PRIQTY and SECQTY clauses.

Acceptable values: 0 - 4194304

Default: 0

Update: option 14 on panel DSNTIPB

DSNZPxxx: DSN6SYSP TSQTY

A value of 0 indicates that Db2 is to use a default value of 1 cylinder for a non-LOB table space or 10 cylinders for a LOB table space.

In a data sharing environment, this parameter has group scope.



INDEX SPACE ALLOCATION field (IXQTY subsystem parameter)

The IXQTY subsystem parameter controls the amount of space in KB that is to be allocated for primary and secondary spaces. This parameter applies to Db2-defined data sets for index spaces that are created without the PRIQTY and SECQTY clauses.

Acceptable values: 0 - 4194304

Default: 0

Update: option 14 on panel DSNTIPB

DSNZPxxx: DSN6SYSP IXQTY

A value of 0 indicates that Db2 is to use a default allocation of one cylinder.

In a data sharing environment, this parameter has group scope.



So basically 1 cylinder if at default 0 values.



If you create a table space implicitly, Db2 uses defaults for the space allocation attributes. The default values of PRIQTY and SECQTY specify the space allocation for the table space. If the value of the TSQTY subsystem parameter is nonzero, it determines the default values for PRIQTY and SECQTY. If the value of TSQTY is zero, the default values for PRIQTY and SECQTY are determined as described in the CREATE TABLESPACE statement.



PRIQTY is controlled at TS create:



PRIQTY integer

Specifies the minimum primary space allocation for a Db2-managed data set. integer must be a positive integer, or -1. In general, when you specify PRIQTY with a positive integer value, the primary space allocation is at least n kilobytes, where n is the value of integer. However, the following exceptions exist:

* For 4KB page sizes, if integer is greater than 0 and less than 12, n is 12.

* For 8KB page sizes, if integer is greater than 0 and less than 24, n is 24.

* For 16KB page sizes, if integer is greater than 0 and less than 48, n is 48.

* For 32KB page sizes, if integer is greater than 0 and less than 96, n is 96.

* For any page size, if integer is greater than 67108864, n is 67108864.

If you do not specify PRIQTY, or specify PRIQTY with a value of -1, Db2 uses a default value for the primary space allocation; for information on how Db2 determines the default value, see Rules for primary and secondary space allocation https://www.ibm.com/support/knowledgecenter/SSEPEK_12.0.0/sqlref/src/tpc/db2z_sql_createtablespace.html?view=kc#db2z_sql_createtablespace__rpsalloc . (see earlier link!)

I hope that helps!

As I mentioned, I use a combination of these but I also monitor space usage daily and have rules to REORG when % Active drops, DELETES, MASSDELETES, EXTENTS > 1000 etc. if you do not have a tool then it can be a bit of work to keep checking how your space is being used or not used!





Roy Boxwell

SOFTWARE ENGINEERING GmbH and SEGUS Inc.
-Product Development-

Heinrichstrasse 83-85
40239 Duesseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: <mailto:[login to unmask email]> [login to unmask email]
Web http://www.seg.de http://www.seg.de

https://www.seg.de/corporate/rechtliche-hinweise/datenschutz Link zur Datenschutzerklärung


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



From: Smith, Roger [mailto:[login to unmask email]
Sent: Tuesday, January 8, 2019 2:40 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: -1 -1 PQTY SQTY - ZPARMS



Thank you – in this shop a rule was given couple years back to allocate 1 gig of PQTY for all new object in Dev , QA , PROD.



Once done most DBA’s never revisits PQTY. Time went by and now we have many empty or almost empty tablespaces with this 1 gig allocated.

Lots of wasted space in over allocations. Especially true in Dev where objects are replicated many times with different qualifiers.



Pasted LISTCAT is from sample non-part TS that was defined with -1 -1.

Data was delivered via Load and eventually 26 VSAM datasets were allocated.



Is my reading correct:

Primary is 1 cyl for YDBA24.DSNDBD.DBNAME.TSNAME.I0001.A001

Then each extent is a varying size based on formula.

At a certain size/extents YDBA24.DSNDBD.DBNAME.TSNAME.I0001.A002 is created on different volume and process repeats



Do you have link to How extent allocations are determined when using -1 -1?





1IDCAMS SYSTEM SERVICES TIME: 07:58:02 01/08/19 PAGE 1

0

LISTCAT ENTRIES(YDBA24.DSNDBD.DBNAME.TSNAME.I0001.A001) ALL

0DATA ---------- YDBA24.DSNDBD.DBNAME.TSNAME.I0001.A001

IN-CAT --- CATD00

HISTORY

DATASET-OWNER-----(NULL) CREATION--------2018.352

RELEASE----------------2 EXPIRATION------0000.000

ACCOUNT-INFO-----------------------------------(NULL)

PROTECTION-PSWD-----(NULL) RACF----------------(NO)

ASSOCIATIONS

CLUSTER--YDBA24.DSNDBC.DBNAME.TSNAME.I0001.A001

ATTRIBUTES

KEYLEN-----------------0 AVGLRECL---------------0 BUFSPACE------------8192 CISIZE--------------4096

RKP--------------------0 MAXLRECL---------------0 EXCPEXIT----------(NULL) CI/CA----------------180

SHROPTNS(3,3) RECOVERY UNIQUE NOERASE LINEAR NOWRITECHK UNORDERED REUSE

NONSPANNED

STATISTICS

REC-TOTAL--------------0 SPLITS-CI--------------0 EXCPS------------------0

REC-DELETED------------0 SPLITS-CA--------------0 EXTENTS---------------21

REC-INSERTED-----------0 FREESPACE-%CI----------0 SYSTEM-TIMESTAMP:

REC-UPDATED------------0 FREESPACE-%CA----------0 X'0000000000000000'

REC-RETRIEVED----------0 FREESPC----------------0

ALLOCATION

SPACE-TYPE------CYLINDER HI-A-RBA------2147696640

SPACE-PRI--------------1 HI-U-RBA------2147483648

SPACE-SEC--------------1

VOLUME

VOLSER------------DP9763 PHYREC-SIZE---------4096 HI-A-RBA------2147696640 EXTENT-NUMBER---------21

DEVTYPE------X'3010200F' PHYRECS/TRK-----------12 HI-U-RBA------2147483648 EXTENT-TYPE--------X'40'

VOLFLAG------------PRIME TRACKS/CA-------------15

EXTENTS:

LOW-CCHH-----X'016D0000' LOW-RBA----------------0 TRACKS---------------990

HIGH-CCHH----X'01AE000E' HIGH-RBA--------48660479

LOW-CCHH-----X'01CB0000' LOW-RBA---------48660480 TRACKS--------------1860

HIGH-CCHH----X'0246000E' HIGH-RBA-------140083199

LOW-CCHH-----X'03D80000' LOW-RBA--------140083200 TRACKS---------------300

HIGH-CCHH----X'03EB000E' HIGH-RBA-------154828799

LOW-CCHH-----X'045A0000' LOW-RBA--------154828800 TRACKS--------------2115

HIGH-CCHH----X'04E6000E' HIGH-RBA-------258785279

LOW-CCHH-----X'05A10000' LOW-RBA--------258785280 TRACKS--------------1710

HIGH-CCHH----X'0612000E' HIGH-RBA-------342835199

LOW-CCHH-----X'06280000' LOW-RBA--------342835200 TRACKS--------------1950

HIGH-CCHH----X'06A9000E' HIGH-RBA-------438681599

LOW-CCHH-----X'070D0000' LOW-RBA--------438681600 TRACKS---------------525

HIGH-CCHH----X'072F000E' HIGH-RBA-------464486399

LOW-CCHH-----X'07680000' LOW-RBA--------464486400 TRACKS--------------1665

HIGH-CCHH----X'07D6000E' HIGH-RBA-------546324479

LOW-CCHH-----X'08C10000' LOW-RBA--------546324480 TRACKS--------------1800

HIGH-CCHH----X'0938000E' HIGH-RBA-------634798079

LOW-CCHH-----X'095D0000' LOW-RBA--------634798080 TRACKS---------------630

1IDCAMS SYSTEM SERVICES TIME: 07:58:02 01/08/19 PAGE 2

0 HIGH-CCHH----X'0986000E' HIGH-RBA-------665763839

LOW-CCHH-----X'09910000' LOW-RBA--------665763840 TRACKS--------------1305

HIGH-CCHH----X'09E7000E' HIGH-RBA-------729907199

LOW-CCHH-----X'0A7A0000' LOW-RBA--------729907200 TRACKS--------------1365

HIGH-CCHH----X'0AD4000E' HIGH-RBA-------796999679

LOW-CCHH-----X'0B820000' LOW-RBA--------796999680 TRACKS--------------3675

HIGH-CCHH----X'0C76000E' HIGH-RBA-------977633279

LOW-CCHH-----X'0E780000' LOW-RBA--------977633280 TRACKS--------------7560

HIGH-CCHH----X'106F000E' HIGH-RBA------1349222399

LOW-CCHH-----X'20050000' LOW-RBA-------1349222400 TRACKS---------------915

HIGH-CCHH----X'2041000E' HIGH-RBA------1394196479

LOW-CCHH-----X'23F10000' LOW-RBA-------1394196480 TRACKS--------------1875

HIGH-CCHH----X'246D000E' HIGH-RBA------1486356479

LOW-CCHH-----X'259C0000' LOW-RBA-------1486356480 TRACKS--------------2925

HIGH-CCHH----X'265E000E' HIGH-RBA------1630126079

LOW-CCHH-----X'26D90000' LOW-RBA-------1630126080 TRACKS--------------1005

HIGH-CCHH----X'271B000E' HIGH-RBA------1679523839

LOW-CCHH-----X'2E250000' LOW-RBA-------1679523840 TRACKS--------------7455

HIGH-CCHH----X'3015000E' HIGH-RBA------2045951999

LOW-CCHH-----X'33B20000' LOW-RBA-------2045952000 TRACKS--------------1125

HIGH-CCHH----X'33FC000E' HIGH-RBA------2101247999

LOW-CCHH-----X'37450000' LOW-RBA-------2101248000 TRACKS---------------945

HIGH-CCHH----X'3783000E' HIGH-RBA------2147696639

1IDCAMS SYSTEM SERVICES TIME: 07:58:02 01/08/19 PAGE 3







Command - Enter "/" to select action , Message , Volume

-------------------------------------------------------------------------------

YDBA24.DSNDBD.DBNAME.TSNAME.I0001.A001 , , DP9763

YDBA24.DSNDBD.DBNAME.TSNAME.I0001.A002 , , DP2079+

YDBA24.DSNDBD.DBNAME.TSNAME.I0001.A003 , , DP9661

YDBA24.DSNDBD.DBNAME.TSNAME.I0001.A004 , , DP302B+

YDBA24.DSNDBD.DBNAME.TSNAME.I0001.A005 , , DP9294+

YDBA24.DSNDBD.DBNAME.TSNAME.I0001.A006 , , MIGRAT1

YDBA24.DSNDBD.DBNAME.TSNAME.I0001.A007 , , DP2004

YDBA24.DSNDBD.DBNAME.TSNAME.I0001.A008 , , DP9749

YDBA24.DSNDBD.DBNAME.TSNAME.I0001.A009 , , MIGRAT1

YDBA24.DSNDBD.DBNAME.TSNAME.I0001.A010 , , MIGRAT1

YDBA24.DSNDBD.DBNAME.TSNAME.I0001.A011 , , DP3211

YDBA24.DSNDBD.DBNAME.TSNAME.I0001.A012 , , DP9076

YDBA24.DSNDBD.DBNAME.TSNAME.I0001.A013 , , DP9028

YDBA24.DSNDBD.DBNAME.TSNAME.I0001.A014 , , DP9372+

YDBA24.DSNDBD.DBNAME.TSNAME.I0001.A015 , , DP9671

YDBA24.DSNDBD.DBNAME.TSNAME.I0001.A016 , , DP9663



From: Boxwell, Roy <[login to unmask email]>
Sent: Tuesday, January 8, 2019 7:09 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: -1 -1 PQTY SQTY - ZPARMS



Yep! It really is “it depends”... For some people -1 -1 is the best way (No usage of DSN1COPY etc.) for others the sliding scale with a good initial is enough for yet others set both...



That’s one of the things I love about Db2 – Whatever problem you have – there is probably a solution!



Roy Boxwell

SOFTWARE ENGINEERING GmbH and SEGUS Inc.
-Product Development-

Heinrichstrasse 83-85
40239 Duesseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: <mailto:[login to unmask email]> [login to unmask email]
Web http://www.seg.de http://www.seg.de

https://www.seg.de/corporate/rechtliche-hinweise/datenschutz Link zur Datenschutzerklärung


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



From: Peter Vanroose [mailto:[login to unmask email]
Sent: Tuesday, January 8, 2019 12:44 PM
To: [login to unmask email] <mailto:[login to unmask email]>
Subject: [DB2-L] - RE: -1 -1 PQTY SQTY - ZPARMS



Roy,

I suppose it's indeed good to have an explicit PRIQTY.

But if you have a well-chosen PRIQTY from the moment of the tablespace creation, and you have never revisited (and gradually incremented) that value (with growing table content), you'll run into exactly the same (and sometimes even a slightly worse) situation as with -1, since Db2 will again be taking larger and larger secondary extents (maybe just 2 or 3) so you end up again in too largely allocated tablespaces.

Ideally you should be looking regualry at the RTS (real time statistics) data for setting an "optimal" PRIQTY (ideally before each REORG).

In Reply to Roy Boxwell:

I am a great fan of using a positive integer for PRIQTY and not using SECQTY. Then you get the “best of both worlds” as you can move datasets around without worry too much but still get the maximum possible secondary’s using the sliding scale.

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



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


The information contained in this e-mail, and any attachment, is confidential and is intended solely for the use of the intended recipient. Access, copying or re-use of the e-mail or any attachment, or any information contained therein, by any other person is not authorized. If you are not the intended recipient please return the e-mail to the sender and delete it from your computer. Although we attempt to sweep e-mail and attachments for viruses, we do not guarantee that either are virus-free and accept no liability for any damage sustained as a result of viruses.

Please refer to https://disclaimer.bnymellon.com/eu.htm for certain disclosures relating to European legal entities. We take our data protection and privacy responsibilities seriously and our privacy notice explains how we collect, use and share personal information in the course of our business activities. It can be accessed at the privacy section of www.bnymellon.com.

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

Attachments

  • smime.p7s (5.1k)

Michael Hannan

RE: -1 -1 PQTY SQTY - ZPARMS
(in response to Roy Boxwell)

For those concerned about over allocation for large numbers of trivial tables that do not grow, I would suggest SEGSIZE 4, and consider a space needs a Header Page, Spacemap page, and one segment, assuming not compressed (no dictionary), so could have as little as six 4K pages perhaps. An Index could have even less pages, due to not segmented and needing at least a root page and leaf page in addition to Hdr and Spacemap.

Calculating Spacemap page quantities obviously gets more complicated for larger objects, Paritioned, Member Cluster, etc. Easier to calculate desired data pages with freespace and add a safety percentage to cover spacemaps etc. 

Good idea to explicitly specify PRIQTY for all very small tables and very small indexes. Defaulting to 1 Cylinder can eat up a lot of space if large number of envs and large number of objects. 

Others will say" Who cares?" Not a performance issue and DASD is cheap.

PRIQTY -1 is O.K. if don't know the size but I like what Roy says to specify it if possible. Can be altered before a Reorg as already pointed out.

Allowing SECQTY to default seems O.K. to me and use the sliding scale.

N.B. Segsize 4 only for small objects. Otherwise use the biggest SEGSIZE is better. Don't see a need for in between Segsizes really.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Edited By:
Michael Hannan[Organization Members] @ Jan 09, 2019 - 09:42 AM (Europe/Berlin)
Michael Hannan[Organization Members] @ Jan 09, 2019 - 09:45 AM (Europe/Berlin)

James Campbell

-1 -1 PQTY SQTY - ZPARMS
(in response to ROGER SMITH)
I notice extent sizes of 990, 1860, 300, 2115, 1710, 1950, 525, etc.

What is happening is that Db2 requests an allocation of 'x' tracks, VSAM decides that it can
allocate those tracks immediately after the last extent and does so. But instead of creating a
new extent to hold the allocation, it increases the size of the last extent.

This repeats until there is insufficient free space after the last extent for a new allocation -
when a new extent will be created. And, if there is sufficient free space after it, additional
allocations increase the size of that extent.

Consequently the number and size of extents is not an accurate indication of the number or
size of allocations requests. Only SMF records will tell (don't know which.)

It appears you have a DSSIZE of 2GB - hence when each cluster has reached that size, the
next will be created. If you reach the limit in the number of extents there will be some error
condition.

James Campbell

On 8 Jan 2019 at 13:40, Smith, Roger wrote:

>
> Thank you - in this shop a rule was given couple years back to allocate 1 gig of
> PQTY for all new object in Dev , QA , PROD.
>  
> Once done most DBA´s never revisits PQTY. Time went by  and now we have
> many  empty or almost empty tablespaces with this 1 gig allocated.
> Lots of wasted space in over allocations.  Especially true in Dev where objects are
> replicated many  times with different qualifiers.
>  
> Pasted LISTCAT is from sample non-part TS that was defined with -1 -1.  
> Data was delivered via Load and eventually 26 VSAM datasets were allocated.
>  
> Is my reading correct:
> Primary is 1 cyl  for YDBA24.DSNDBD.DBNAME.TSNAME.I0001.A001
> Then each extent is a varying size based on formula.
> At a certain size/extents YDBA24.DSNDBD.DBNAME.TSNAME.I0001.A002 is
> created on different volume and process repeats
>  
> Do you have link to How extent allocations are determined when using -1 -1?
>  
>  
> 1IDCAMS  SYSTEM SERVICES                                           TIME: 07:58:02        01/08/19     PAGE      1
> 0
>    LISTCAT ENTRIES(YDBA24.DSNDBD.DBNAME.TSNAME.I0001.A001) ALL
> 0DATA ---------- YDBA24.DSNDBD.DBNAME.TSNAME.I0001.A001
>       IN-CAT --- CATD00
>       HISTORY
>         DATASET-OWNER-----(NULL)     CREATION--------2018.352
>         RELEASE----------------2     EXPIRATION------0000.000
>         ACCOUNT-INFO-----------------------------------(NULL)
>       PROTECTION-PSWD-----(NULL)     RACF----------------(NO)
>       ASSOCIATIONS
>         CLUSTER--YDBA24.DSNDBC.DBNAME.TSNAME.I0001.A001
>       ATTRIBUTES
>         KEYLEN-----------------0     AVGLRECL---------------0     BUFSPACE------------8192     CISIZE--------------4096
>         RKP--------------------0     MAXLRECL---------------0     EXCPEXIT----------(NULL)     CI/CA----------------180
>         SHROPTNS(3,3)   RECOVERY     UNIQUE           NOERASE     LINEAR        NOWRITECHK     UNORDERED          REUSE
>         NONSPANNED
>       STATISTICS
>         REC-TOTAL--------------0     SPLITS-CI--------------0     EXCPS------------------0
>         REC-DELETED------------0     SPLITS-CA--------------0     EXTENTS---------------21
>         REC-INSERTED-----------0     FREESPACE-%CI----------0     SYSTEM-TIMESTAMP:
>         REC-UPDATED------------0     FREESPACE-%CA----------0          X'0000000000000000'
>         REC-RETRIEVED----------0     FREESPC----------------0
>       ALLOCATION
>         SPACE-TYPE------CYLINDER     HI-A-RBA------2147696640
>         SPACE-PRI--------------1     HI-U-RBA------2147483648
>         SPACE-SEC--------------1
>       VOLUME
>         VOLSER------------DP9763     PHYREC-SIZE---------4096     HI-A-RBA------2147696640    
> EXTENT-NUMBER---------21
>         DEVTYPE------X'3010200F'     PHYRECS/TRK-----------12     HI-U-RBA------2147483648    
> EXTENT-TYPE--------X'40'
>         VOLFLAG------------PRIME     TRACKS/CA-------------15
>         EXTENTS:
>         LOW-CCHH-----X'016D0000'     LOW-RBA----------------0     TRACKS---------------990
>         HIGH-CCHH----X'01AE000E'     HIGH-RBA--------48660479
>         LOW-CCHH-----X'01CB0000'     LOW-RBA---------48660480     TRACKS--------------1860
>         HIGH-CCHH----X'0246000E'     HIGH-RBA-------140083199
>         LOW-CCHH-----X'03D80000'     LOW-RBA--------140083200     TRACKS---------------300
>         HIGH-CCHH----X'03EB000E'     HIGH-RBA-------154828799
>         LOW-CCHH-----X'045A0000'     LOW-RBA--------154828800     TRACKS--------------2115
>         HIGH-CCHH----X'04E6000E'     HIGH-RBA-------258785279
>         LOW-CCHH-----X'05A10000'     LOW-RBA--------258785280     TRACKS--------------1710
>         HIGH-CCHH----X'0612000E'     HIGH-RBA-------342835199
>         LOW-CCHH-----X'06280000'     LOW-RBA--------342835200     TRACKS--------------1950
>         HIGH-CCHH----X'06A9000E'     HIGH-RBA-------438681599
>         LOW-CCHH-----X'070D0000'     LOW-RBA--------438681600     TRACKS---------------525
>         HIGH-CCHH----X'072F000E'     HIGH-RBA-------464486399
>         LOW-CCHH-----X'07680000'     LOW-RBA--------464486400     TRACKS--------------1665
>         HIGH-CCHH----X'07D6000E'     HIGH-RBA-------546324479
>         LOW-CCHH-----X'08C10000'     LOW-RBA--------546324480     TRACKS--------------1800
>         HIGH-CCHH----X'0938000E'     HIGH-RBA-------634798079
>         LOW-CCHH-----X'095D0000'     LOW-RBA--------634798080     TRACKS---------------630
> 1IDCAMS  SYSTEM SERVICES                                           TIME: 07:58:02        01/08/19     PAGE      2
> 0       HIGH-CCHH----X'0986000E'     HIGH-RBA-------665763839
>         LOW-CCHH-----X'09910000'     LOW-RBA--------665763840     TRACKS--------------1305
>         HIGH-CCHH----X'09E7000E'     HIGH-RBA-------729907199
>         LOW-CCHH-----X'0A7A0000'     LOW-RBA--------729907200     TRACKS--------------1365
>         HIGH-CCHH----X'0AD4000E'     HIGH-RBA-------796999679
>         LOW-CCHH-----X'0B820000'     LOW-RBA--------796999680     TRACKS--------------3675
>         HIGH-CCHH----X'0C76000E'     HIGH-RBA-------977633279
>         LOW-CCHH-----X'0E780000'     LOW-RBA--------977633280     TRACKS--------------7560
>         HIGH-CCHH----X'106F000E'     HIGH-RBA------1349222399
>         LOW-CCHH-----X'20050000'     LOW-RBA-------1349222400     TRACKS---------------915
>         HIGH-CCHH----X'2041000E'     HIGH-RBA------1394196479
>         LOW-CCHH-----X'23F10000'     LOW-RBA-------1394196480     TRACKS--------------1875
>         HIGH-CCHH----X'246D000E'     HIGH-RBA------1486356479
>         LOW-CCHH-----X'259C0000'     LOW-RBA-------1486356480     TRACKS--------------2925
>         HIGH-CCHH----X'265E000E'     HIGH-RBA------1630126079
>         LOW-CCHH-----X'26D90000'     LOW-RBA-------1630126080     TRACKS--------------1005
>         HIGH-CCHH----X'271B000E'     HIGH-RBA------1679523839
>         LOW-CCHH-----X'2E250000'     LOW-RBA-------1679523840     TRACKS--------------7455
>         HIGH-CCHH----X'3015000E'     HIGH-RBA------2045951999
>         LOW-CCHH-----X'33B20000'     LOW-RBA-------2045952000     TRACKS--------------1125
>         HIGH-CCHH----X'33FC000E'     HIGH-RBA------2101247999
>         LOW-CCHH-----X'37450000'     LOW-RBA-------2101248000     TRACKS---------------945
>         HIGH-CCHH----X'3783000E'     HIGH-RBA------2147696639
> 1IDCAMS  SYSTEM SERVICES                                                         TIME: 07:58:02        01/08/19     PAGE      3
>  
>  
>  
> Command - Enter "/" to select action            ,     Message         , Volume
> -------------------------------------------------------------------------------
>          YDBA24.DSNDBD.DBNAME.TSNAME.I0001.A001  ,                , DP9763
>          YDBA24.DSNDBD.DBNAME.TSNAME.I0001.A002  ,                , DP2079+
>          YDBA24.DSNDBD.DBNAME.TSNAME.I0001.A003  ,                , DP9661
>          YDBA24.DSNDBD.DBNAME.TSNAME.I0001.A004  ,                , DP302B+
>          YDBA24.DSNDBD.DBNAME.TSNAME.I0001.A005  ,                , DP9294+
>          YDBA24.DSNDBD.DBNAME.TSNAME.I0001.A006  ,                , MIGRAT1
>          YDBA24.DSNDBD.DBNAME.TSNAME.I0001.A007  ,                , DP2004
>          YDBA24.DSNDBD.DBNAME.TSNAME.I0001.A008  ,                , DP9749
>          YDBA24.DSNDBD.DBNAME.TSNAME.I0001.A009  ,                , MIGRAT1
>          YDBA24.DSNDBD.DBNAME.TSNAME.I0001.A010  ,                , MIGRAT1
>          YDBA24.DSNDBD.DBNAME.TSNAME.I0001.A011  ,                , DP3211
>          YDBA24.DSNDBD.DBNAME.TSNAME.I0001.A012  ,                , DP9076
>          YDBA24.DSNDBD.DBNAME.TSNAME.I0001.A013  ,                , DP9028
>          YDBA24.DSNDBD.DBNAME.TSNAME.I0001.A014  ,                , DP9372+
>          YDBA24.DSNDBD.DBNAME.TSNAME.I0001.A015  ,                , DP9671
>          YDBA24.DSNDBD.DBNAME.TSNAME.I0001.A016  ,                , DP9663
>  
> From: Boxwell, Roy <[login to unmask email]>
> Sent: Tuesday, January 8, 2019 7:09 AM
> To: [login to unmask email]
> Subject: [DB2-L] - RE: -1 -1 PQTY SQTY - ZPARMS
>  
> Yep! It really is "it depends"... For some people -1 -1 is the best way (No usage of
> DSN1COPY etc.) for others the sliding scale with a good initial is enough for yet others
> set both...
>  
> That´s one of the things I love about Db2 - Whatever problem you have - there is
> probably a solution!
>  
> Roy Boxwell
>
> SOFTWARE ENGINEERING GmbH and SEGUS Inc.
> -Product Development-
>
> Heinrichstrasse 83-85
> 40239 Duesseldorf/Germany
> Tel. +49 (0)211 96149-675
> Fax +49 (0)211 96149-32
> Email: [login to unmask email]
> Web   http://www.seg.de
> Link zur Datenschutzerklärung
>
> Software Engineering GmbH
> Amtsgericht Düsseldorf, HRB 37894
> Geschäftsführung: Gerhard Schubert, Ulf Heinrich
>  
> From: Peter Vanroose [mailto:[login to unmask email] ]
> Sent: Tuesday, January 8, 2019 12:44 PM
> To: [login to unmask email]
> Subject: [DB2-L] - RE: -1 -1 PQTY SQTY - ZPARMS
>  
> Roy,
> I suppose it's indeed good to have an explicit PRIQTY.
> But if you have a well-chosen PRIQTY from the moment of the tablespace creation, and
> you have never revisited (and gradually incremented) that value (with growing table
> content), you'll run into exactly the same (and sometimes even a slightly worse) situation
> as with -1, since Db2 will again be taking larger and larger secondary extents (maybe just
> 2 or 3) so you end up again in too largely allocated tablespaces.
> Ideally you should be looking regualry at the RTS (real time statistics) data for setting an
> "optimal" PRIQTY (ideally before each REORG).
>
> In Reply to Roy Boxwell:
> I am a great fan of using a positive integer for PRIQTY and not using SECQTY. Then
> you get the "best of both worlds" as you can move datasets around without worry too
> much but still get the maximum possible secondary´s using the sliding scale.
> --      Peter Vanroose
>         ABIS Training &Consulting,
>         Leuven, Belgium.
>         https://www.abis.be/
>  


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

Phil Grainger

-1 -1 PQTY SQTY - ZPARMS
(in response to James Campbell)
"Consequently the number and size of extents is not an accurate indication of the number or size of allocations requests. Only SMF records will tell (don't know which.)"

There is an IFCID that tracks Access Method Services requests (92 traces the START of a command and contains the command itself and 97 traces the end and shows the return code) - so if you track these, you can see what's actually being requested

James also gives me the chance to remind everyone that the size of a Db2 LDS USED to be (1 x priqty + ((n-1) * secqty) - where "n" is the number of extents). This has not been true for some time

ALSO, everyone should be aware that if you reorg (or otherwise unload/reload with a delete/define - such as a drop/recreate schema change) there is now ALWAYS the possibility that Db2 and AMS will recreate the dataset and consolidate extents differently. Worst case scenario is that the data you unloaded fails to fit back in the dataset!

________________________________




[BMC Exchange 2019 - Global Event Series - REGISTER] https://www.bmc.com/ami


Phil Grainger
Principal Enablement Manager

Direct

+44 1189 218 000

Mobile

+44 7808 643 479

Email

[login to unmask email]

E2, Eskdale Road
Winnersh
Berkshire
United Kingdom
RG41 5TS
[http://media.cms.bmc.com/images/federal_email_signature_logo.jpg][https://acclaim-production-app.s3.amazonaws.com/images/2429c3cd-a1de-44fc-b4f3-bc762bb2f963/IBM%2BChampion%2B-%2BAnalytics%2B2018.png][https://acclaim-production-app.s3.amazonaws.com/images/2429c3cd-a1de-44fc-b4f3-bc762bb2f963/IBM%2BChampion%2B-%2BAnalytics%2B2018.png]






















From: James Campbell [mailto:[login to unmask email]
Sent: 09 January 2019 11:17
To: [login to unmask email]
Subject: [DB2-L] - RE: -1 -1 PQTY SQTY - ZPARMS

I notice extent sizes of 990, 1860, 300, 2115, 1710, 1950, 525, etc.

What is happening is that Db2 requests an allocation of 'x' tracks, VSAM decides that it can allocate those tracks immediately after the last extent and does so. But instead of creating a new extent to hold the allocation, it increases the size of the last extent.

This repeats until there is insufficient free space after the last extent for a new allocation - when a new extent will be created. And, if there is sufficient free space after it, additional allocations increase the size of that extent.

Consequently the number and size of extents is not an accurate indication of the number or size of allocations requests. Only SMF records will tell (don't know which.)

It appears you have a DSSIZE of 2GB - hence when each cluster has reached that size, the next will be created. If you reach the limit in the number of extents there will be some error condition.

James Campbell

On 8 Jan 2019 at 13:40, Smith, Roger wrote:

>
> Thank you - in this shop a rule was given couple years back to allocate 1 gig of
> PQTY for all new object in Dev , QA , PROD.
>
> Once done most DBA's never revisits PQTY. Time went by and now we have
> many empty or almost empty tablespaces with this 1 gig allocated.
> Lots of wasted space in over allocations. Especially true in Dev where objects are
> replicated many times with different qualifiers.
>
> Pasted LISTCAT is from sample non-part TS that was defined with -1 -1.
> Data was delivered via Load and eventually 26 VSAM datasets were allocated.
>
> Is my reading correct:
> Primary is 1 cyl for YDBA24.DSNDBD.DBNAME.TSNAME.I0001.A001
> Then each extent is a varying size based on formula.
> At a certain size/extents YDBA24.DSNDBD.DBNAME.TSNAME.I0001.A002 is
> created on different volume and process repeats
>
> Do you have link to How extent allocations are determined when using -1 -1?
>
>
> 1IDCAMS SYSTEM SERVICES TIME: 07:58:02 01/08/19 PAGE 1
> 0
> LISTCAT ENTRIES(YDBA24.DSNDBD.DBNAME.TSNAME.I0001.A001) ALL
> 0DATA ---------- YDBA24.DSNDBD.DBNAME.TSNAME.I0001.A001
> IN-CAT --- CATD00
> HISTORY
> DATASET-OWNER-----(NULL) CREATION--------2018.352
> RELEASE----------------2 EXPIRATION------0000.000
> ACCOUNT-INFO-----------------------------------(NULL)
> PROTECTION-PSWD-----(NULL) RACF----------------(NO)
> ASSOCIATIONS
> CLUSTER--YDBA24.DSNDBC.DBNAME.TSNAME.I0001.A001
> ATTRIBUTES
> KEYLEN-----------------0 AVGLRECL---------------0 BUFSPACE------------8192 CISIZE--------------4096
> RKP--------------------0 MAXLRECL---------------0 EXCPEXIT----------(NULL) CI/CA----------------180
> SHROPTNS(3,3) RECOVERY UNIQUE NOERASE LINEAR NOWRITECHK UNORDERED REUSE
> NONSPANNED
> STATISTICS
> REC-TOTAL--------------0 SPLITS-CI--------------0 EXCPS------------------0
> REC-DELETED------------0 SPLITS-CA--------------0 EXTENTS---------------21
> REC-INSERTED-----------0 FREESPACE-%CI----------0 SYSTEM-TIMESTAMP:
> REC-UPDATED------------0 FREESPACE-%CA----------0 X'0000000000000000'
> REC-RETRIEVED----------0 FREESPC----------------0
> ALLOCATION
> SPACE-TYPE------CYLINDER HI-A-RBA------2147696640
> SPACE-PRI--------------1 HI-U-RBA------2147483648
> SPACE-SEC--------------1
> VOLUME
> VOLSER------------DP9763 PHYREC-SIZE---------4096 HI-A-RBA------2147696640
> EXTENT-NUMBER---------21
> DEVTYPE------X'3010200F' PHYRECS/TRK-----------12 HI-U-RBA------2147483648
> EXTENT-TYPE--------X'40'
> VOLFLAG------------PRIME TRACKS/CA-------------15
> EXTENTS:
> LOW-CCHH-----X'016D0000' LOW-RBA----------------0 TRACKS---------------990
> HIGH-CCHH----X'01AE000E' HIGH-RBA--------48660479
> LOW-CCHH-----X'01CB0000' LOW-RBA---------48660480 TRACKS--------------1860
> HIGH-CCHH----X'0246000E' HIGH-RBA-------140083199
> LOW-CCHH-----X'03D80000' LOW-RBA--------140083200 TRACKS---------------300
> HIGH-CCHH----X'03EB000E' HIGH-RBA-------154828799
> LOW-CCHH-----X'045A0000' LOW-RBA--------154828800 TRACKS--------------2115
> HIGH-CCHH----X'04E6000E' HIGH-RBA-------258785279
> LOW-CCHH-----X'05A10000' LOW-RBA--------258785280 TRACKS--------------1710
> HIGH-CCHH----X'0612000E' HIGH-RBA-------342835199
> LOW-CCHH-----X'06280000' LOW-RBA--------342835200 TRACKS--------------1950
> HIGH-CCHH----X'06A9000E' HIGH-RBA-------438681599
> LOW-CCHH-----X'070D0000' LOW-RBA--------438681600 TRACKS---------------525
> HIGH-CCHH----X'072F000E' HIGH-RBA-------464486399
> LOW-CCHH-----X'07680000' LOW-RBA--------464486400 TRACKS--------------1665
> HIGH-CCHH----X'07D6000E' HIGH-RBA-------546324479
> LOW-CCHH-----X'08C10000' LOW-RBA--------546324480 TRACKS--------------1800
> HIGH-CCHH----X'0938000E' HIGH-RBA-------634798079
> LOW-CCHH-----X'095D0000' LOW-RBA--------634798080 TRACKS---------------630
> 1IDCAMS SYSTEM SERVICES TIME: 07:58:02 01/08/19 PAGE 2
> 0 HIGH-CCHH----X'0986000E' HIGH-RBA-------665763839
> LOW-CCHH-----X'09910000' LOW-RBA--------665763840 TRACKS--------------1305
> HIGH-CCHH----X'09E7000E' HIGH-RBA-------729907199
> LOW-CCHH-----X'0A7A0000' LOW-RBA--------729907200 TRACKS--------------1365
> HIGH-CCHH----X'0AD4000E' HIGH-RBA-------796999679
> LOW-CCHH-----X'0B820000' LOW-RBA--------796999680 TRACKS--------------3675
> HIGH-CCHH----X'0C76000E' HIGH-RBA-------977633279
> LOW-CCHH-----X'0E780000' LOW-RBA--------977633280 TRACKS--------------7560
> HIGH-CCHH----X'106F000E' HIGH-RBA------1349222399
> LOW-CCHH-----X'20050000' LOW-RBA-------1349222400 TRACKS---------------915
> HIGH-CCHH----X'2041000E' HIGH-RBA------1394196479
> LOW-CCHH-----X'23F10000' LOW-RBA-------1394196480 TRACKS--------------1875
> HIGH-CCHH----X'246D000E' HIGH-RBA------1486356479
> LOW-CCHH-----X'259C0000' LOW-RBA-------1486356480 TRACKS--------------2925
> HIGH-CCHH----X'265E000E' HIGH-RBA------1630126079
> LOW-CCHH-----X'26D90000' LOW-RBA-------1630126080 TRACKS--------------1005
> HIGH-CCHH----X'271B000E' HIGH-RBA------1679523839
> LOW-CCHH-----X'2E250000' LOW-RBA-------1679523840 TRACKS--------------7455
> HIGH-CCHH----X'3015000E' HIGH-RBA------2045951999
> LOW-CCHH-----X'33B20000' LOW-RBA-------2045952000 TRACKS--------------1125
> HIGH-CCHH----X'33FC000E' HIGH-RBA------2101247999
> LOW-CCHH-----X'37450000' LOW-RBA-------2101248000 TRACKS---------------945
> HIGH-CCHH----X'3783000E' HIGH-RBA------2147696639
> 1IDCAMS SYSTEM SERVICES TIME: 07:58:02 01/08/19 PAGE 3
>
>
>
> Command - Enter "/" to select action , Message , Volume
> -------------------------------------------------------------------------------
> YDBA24.DSNDBD.DBNAME.TSNAME.I0001.A001 , , DP9763
> YDBA24.DSNDBD.DBNAME.TSNAME.I0001.A002 , , DP2079+
> YDBA24.DSNDBD.DBNAME.TSNAME.I0001.A003 , , DP9661
> YDBA24.DSNDBD.DBNAME.TSNAME.I0001.A004 , , DP302B+
> YDBA24.DSNDBD.DBNAME.TSNAME.I0001.A005 , , DP9294+
> YDBA24.DSNDBD.DBNAME.TSNAME.I0001.A006 , , MIGRAT1
> YDBA24.DSNDBD.DBNAME.TSNAME.I0001.A007 , , DP2004
> YDBA24.DSNDBD.DBNAME.TSNAME.I0001.A008 , , DP9749
> YDBA24.DSNDBD.DBNAME.TSNAME.I0001.A009 , , MIGRAT1
> YDBA24.DSNDBD.DBNAME.TSNAME.I0001.A010 , , MIGRAT1
> YDBA24.DSNDBD.DBNAME.TSNAME.I0001.A011 , , DP3211
> YDBA24.DSNDBD.DBNAME.TSNAME.I0001.A012 , , DP9076
> YDBA24.DSNDBD.DBNAME.TSNAME.I0001.A013 , , DP9028
> YDBA24.DSNDBD.DBNAME.TSNAME.I0001.A014 , , DP9372+
> YDBA24.DSNDBD.DBNAME.TSNAME.I0001.A015 , , DP9671
> YDBA24.DSNDBD.DBNAME.TSNAME.I0001.A016 , , DP9663
>
> From: Boxwell, Roy <[login to unmask email]<mailto:[login to unmask email]>>
> Sent: Tuesday, January 8, 2019 7:09 AM
> To: [login to unmask email]<mailto:[login to unmask email]>
> Subject: [DB2-L] - RE: -1 -1 PQTY SQTY - ZPARMS
>
> Yep! It really is "it depends"... For some people -1 -1 is the best way (No usage of
> DSN1COPY etc.) for others the sliding scale with a good initial is enough for yet others
> set both...
>
> That's one of the things I love about Db2 - Whatever problem you have - there is
> probably a solution!
>
> Roy Boxwell
>
> SOFTWARE ENGINEERING GmbH and SEGUS Inc.
> -Product Development-
>
> Heinrichstrasse 83-85
> 40239 Duesseldorf/Germany
> Tel. +49 (0)211 96149-675
> Fax +49 (0)211 96149-32
> Email: [login to unmask email]<mailto:[login to unmask email]>
> Web http://www.seg.de
> Link zur Datenschutzerklärung
>
> Software Engineering GmbH
> Amtsgericht Düsseldorf, HRB 37894
> Geschäftsführung: Gerhard Schubert, Ulf Heinrich
>
> From: Peter Vanroose [mailto:[login to unmask email] <mailto:[login to unmask email]%20> ]
> Sent: Tuesday, January 8, 2019 12:44 PM
> To: [login to unmask email]<mailto:[login to unmask email]>
> Subject: [DB2-L] - RE: -1 -1 PQTY SQTY - ZPARMS
>
> Roy,
> I suppose it's indeed good to have an explicit PRIQTY.
> But if you have a well-chosen PRIQTY from the moment of the tablespace creation, and
> you have never revisited (and gradually incremented) that value (with growing table
> content), you'll run into exactly the same (and sometimes even a slightly worse) situation
> as with -1, since Db2 will again be taking larger and larger secondary extents (maybe just
> 2 or 3) so you end up again in too largely allocated tablespaces.
> Ideally you should be looking regualry at the RTS (real time statistics) data for setting an
> "optimal" PRIQTY (ideally before each REORG).
>
> In Reply to Roy Boxwell:
> I am a great fan of using a positive integer for PRIQTY and not using SECQTY. Then
> you get the "best of both worlds" as you can move datasets around without worry too
> much but still get the maximum possible secondary's using the sliding scale.
> -- Peter Vanroose
> ABIS Training &Consulting,
> Leuven, Belgium.
> https://www.abis.be/
>

[https://ipmcdn.avast.com/images/icons/icon-envelope-tick-green-avg-v1.png] https://urldefense.proofpoint.com/v2/url?u=http-3A__www.avg.com_email-2Dsignature-3Futm-5Fmedium-3Demail-26utm-5Fsource-3Dlink-26utm-5Fcampaign-3Dsig-2Demail-26utm-5Fcontent-3Demailclient&d=DwMCAw&c=UrUhmHsiTVT5qkaA4d_oSzcamb9hmamiCDMzBAEwC7E&r=EAGrd_qzLADPfI8dgytr8sbCG7_U9QfXwQMLgK1Zo30&m=ixKh3EtL4aXWip41NIigEQdXE1we5WD0Blryyh6ntmE&s=_e254oz8U0TuyR_JaW5cbzFhVEKWghbC1Z38xFXqMzc&e=

Virus-free. www.avg.com https://urldefense.proofpoint.com/v2/url?u=http-3A__www.avg.com_email-2Dsignature-3Futm-5Fmedium-3Demail-26utm-5Fsource-3Dlink-26utm-5Fcampaign-3Dsig-2Demail-26utm-5Fcontent-3Demailclient&d=DwMCAw&c=UrUhmHsiTVT5qkaA4d_oSzcamb9hmamiCDMzBAEwC7E&r=EAGrd_qzLADPfI8dgytr8sbCG7_U9QfXwQMLgK1Zo30&m=ixKh3EtL4aXWip41NIigEQdXE1we5WD0Blryyh6ntmE&s=_e254oz8U0TuyR_JaW5cbzFhVEKWghbC1Z38xFXqMzc&e=


-----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 (49.7k)
  • image002.jpg (1.6k)
  • image003.png (<1k)
  • image004.png (3.3k)