PQTY, SQTY and SPACE

Vanitha Subramaniam

PQTY, SQTY and SPACE
Hi all,



I have read many links related to calculating the primary and secondary
quantity used and the statistics from one of our tables. Bufferpool is
BP1, So I assume it as 4K pages. Reorg and runstats ran yesterday.



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

SELECT PQTY , SQTY , SECQTYI , EXTENTS , SPACE , AVGROWLEN , CARD

FROM SYSIBM.SYSTABLEPART

WHERE DBNAME = 'AFSDB'

AND TSNAME = 'TSBLNHST';

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

PQTY SQTY SECQTYI EXTENTS SPACE AVGROWLEN
CARD

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

50 15 15 119
198624 138 1410515




Space = (PQTY + SQTY * (EXTENTS - 1 ) ) * 4

= ( 50 + 15 * 118) * 4 = 7280 KB



Space = AVGROWLEN * CARD

= 138 * 1410515 = 194651070 Bytes = 194651KB



Space calculation from VSAM = No of tracks * 48 KB

= 4138 * 48 KB = 198624 KB

VSAM LISTcat shows



Enter "/" to select action Tracks %Used XT
Device

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

DB2NYBT.DSNDBD.AFSDB.TSBLNHST.I0001.A001 4138 ? 119 3390





Continued in next mail



Thanks,

Vanitha





DISCLAIMER:
This email (including any attachments) is intended for the sole use of the intended recipient/s and may contain material that is CONFIDENTIAL AND PRIVATE COMPANY INFORMATION. Any review or reliance by others or copying or distribution or forwarding of any or all of the contents in this message is STRICTLY PROHIBITED. If you are not the intended recipient, please contact the sender by email and delete all copies; your cooperation in this regard is appreciated.

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Vanitha Subramaniam

PQTY, SQTY and SPACE
(in response to Vanitha Subramaniam)
Continuation of my previous mail.

ALLOCATION

SPACE-TYPE---------TRACK HI-A-RBA-------203390976

SPACE-PRI--------------6 HI-U-RBA-------203390976

SPACE-SEC--------------2

EXTENTS:

LOW-CCHH-----X'00510007' LOW-RBA----------------0 TRACKS--------8

HIGH-CCHH----X'0051000E' HIGH-RBA----------393215

LOW-CCHH-----X'00490004' LOW-RBA-----------393216 TRACKS--------2

HIGH-CCHH----X'00490005' HIGH-RBA----------491519

LOW-CCHH-----X'00490009' LOW-RBA-----------491520 TRACKS--------2

HIGH-CCHH----X'0049000A' HIGH-RBA----------589823

LOW-CCHH-----X'006B000B' LOW-RBA-----------589824 TRACKS-------10

HIGH-CCHH----X'006C0005' HIGH-RBA---------1081343

.

LOW-CCHH-----X'03460000' LOW-RBA--------119537664 TRACKS----32

HIGH-CCHH----X'03480001' HIGH-RBA-------121110527

LOW-CCHH-----X'03480007' LOW-RBA--------121110528 TRACKS---128

HIGH-CCHH----X'0350000E' HIGH-RBA-------127401983

LOW-CCHH-----X'037D0000' LOW-RBA--------127401984 TRACKS---220

1. How 7280 KB calculated from PQTY, SQTY is enough to store 198624
KB (Space column) value?
2. Why it is not abending with "Resource unavailable " ? ( since
the Pqty and Sqty are not enough)
3. In this how many primary extents are there.

Priqty = 50 * 4 KB = 200 KB 1 track has 56 KB and
48KB is used by DB2

200/48 = 4.16 track = approximately 5 tracks

Should it have 5 tracks or lesser than that for the first extent.

4. Secqty = 15 * 4 KB = 60 KB

60 KB / 48 = 1.25 = 2 tracks

All the extents should have 2 tracks or lesser than that. Please let me
know whether my understanding on this is correct.

Thanks,

VAnitha





DISCLAIMER:
This email (including any attachments) is intended for the sole use of the intended recipient/s and may contain material that is CONFIDENTIAL AND PRIVATE COMPANY INFORMATION. Any review or reliance by others or copying or distribution or forwarding of any or all of the contents in this message is STRICTLY PROHIBITED. If you are not the intended recipient, please contact the sender by email and delete all copies; your cooperation in this regard is appreciated.

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Vanitha Subramaniam

Re: PQTY, SQTY and SPACE
(in response to Vanitha Subramaniam)
Hi Ram,

I have used these calculations and the Primary and secondary quantity
are 50, 15 (from SYSTABLEPART) 4K pages respectively. AVGRECLEN is 138.
PCTFREE and FREEPAGE are 0.

Based on the above, what is the maximum number of records the table can
have?

Thanks,

Vanitha

________________________________

From: Ramachandran A [mailto:[login to unmask email]
Sent: Wednesday, January 09, 2008 3:10 PM
To: Vanitha_Subramaniam
Subject: Re: [DB2-L] PQTY, SQTY and SPACE

Hi,
PQRT and SQTY in SYSTABLESPART are in 4Kb blocks, that are pages



for example,

CREATE TABLESPACE WMQT4 IN WMQDB USING STOGROUP WMQSTG
PRIQTY 100
SECQTY 40



If you divide 100 Kb by 4kb pages you have a total of 25 pages and same
applies to SECQTY 50 kb divided by 4kb you get 12.5 page (rouunded to 13
because DB2 cannot allocate half a page)



So you can see from SYSIBM.SYSTABLEPART the PQTY and SQTY will be 25
and 13



One track is 56664 bytes but DB2 can only use 49152 byte that is 48 Kb,
this means that if I allocate a tablespace in multiples of 48 I
basically allocate in tracks. So use multiples of 48 to allocate PRIQTY
and SECQTY, each 48Kb is equivalent to one track.



A 3390 track contains 56664 bytes and as you can see above for each
track we will have at least one space map page. Each page is 4096 byte
therefore each track can contains only 56664 - 4096 = 52568 bytes of
data pages. As a consequence you can only have 12 data pages per 3390
track because 52568 / 4096 = 12.833

Now if you want to translate these 12 pages (that is what you can fit in
one track in term of DB2 data pages) in Kb, you have to perform the
following clculation:4096 * 12 / 1024 = 48 for more info, realted to
tracks size refer DFSMS manuals.



Thanks & Regards,

Ram.

9/08, Vanitha_Subramaniam <[login to unmask email]> wrote:

Continuation of my previous mail.





DISCLAIMER:
This email (including any attachments) is intended for the sole use of the intended recipient/s and may contain material that is CONFIDENTIAL AND PRIVATE COMPANY INFORMATION. Any review or reliance by others or copying or distribution or forwarding of any or all of the contents in this message is STRICTLY PROHIBITED. If you are not the intended recipient, please contact the sender by email and delete all copies; your cooperation in this regard is appreciated.

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Michael Ebert

Re: PQTY, SQTY and SPACE
(in response to Vanitha Subramaniam)
Something on the way disk space is allocated for DB2, which I found out a
couple of years ago (I don't think it will have changed):

First, DB2 rounds the specified values of PRIQTY and SECQTY up to a
multiple of PAGESIZE. These new values (PQ, SQ) are handed to IDCAMS.
IDCAMS allocates VSAM files in CA sizes. The CA (control area I think) is
the minimum of PQ and SQ, rounded up to tracks but never more than one
cylinder. If the CA size is less than one cylinder, IDCAMS allocates the
file in TRKs, otherwise in CYLs. You'd get an actual allocation of 6
tracks PRI and 2 tracks SEC, as actually reported by IDCAMS.

Concerning the catalog info (especially EXTENTS), there's obviously
something wrong. I seem to recall that new versions of MVS will
automatically merge adjacent extents (this would explain why the first
extent actually has 8 tracks) and that DB2 from V8 on will automatically
increase SECQTY depending on the current # of extents to prevent
out-of-extent-abends. DB2 might increase the extent counter whenever it
requests a new extent, whether or not IDCAMS will merge extents (this more
or less assumes you've been running REORG with Inline Stats as a
standalone RUNSTATS wouldn't have that info). Since DB2 is still V7 here
(and dying), I do not have actual experience with any of that.

Dr. Michael Ebert
DB2 & Oracle Database Administrator
aMaDEUS Data Processing
Erding / Munich, Germany




Vanitha_Subramaniam <[login to unmask email]>
To
[login to unmask email]
cc

bcc

Subject
[DB2-L] PQTY, SQTY and SPACE





Vanitha_Subramaniam <[login to unmask email]>
Please respond to : DB2 Database Discussion list at IDUG
<[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>
09-01-08 10:07

Hi all,

I have read many links related to calculating the primary and secondary
quantity used and the statistics from one of our tables. Bufferpool is
BP1, So I assume it as 4K pages. Reorg and runstats ran yesterday.

---------+---------+---------+---------+---------+---------+---------+-
SELECT PQTY , SQTY , SECQTYI , EXTENTS , SPACE , AVGROWLEN , CARD
FROM SYSIBM.SYSTABLEPART
WHERE DBNAME = 'AFSDB'
AND TSNAME = 'TSBLNHST';
---------+---------+---------+---------+---------+---------+---------+-
PQTY SQTY SECQTYI EXTENTS SPACE AVGROWLEN CARD
---------+---------+---------+---------+---------+---------+---------+-
50 15 15 119 198624 138 1410515


Space = (PQTY + SQTY * (EXTENTS ? 1 ) ) * 4
= ( 50 + 15 * 118) * 4 = 7280 KB

Space = AVGROWLEN * CARD
= 138 * 1410515 = 194651070 Bytes = 194651KB

Space calculation from VSAM = No of tracks * 48 KB
= 4138 * 48 KB = 198624 KB
VSAM LISTcat shows

Enter "/" to select action Tracks %Used XT Device
--------------------------------------------------------------------
DB2NYBT.DSNDBD.AFSDB.TSBLNHST.I0001.A001 4138 ? 119 3390


ALLOCATION
SPACE-TYPE---------TRACK HI-A-RBA-------203390976
SPACE-PRI--------------6 HI-U-RBA-------203390976
SPACE-SEC--------------2
EXTENTS:
LOW-CCHH-----X'00510007' LOW-RBA----------------0 TRACKS--------8
HIGH-CCHH----X'0051000E' HIGH-RBA----------393215
LOW-CCHH-----X'00490004' LOW-RBA-----------393216 TRACKS--------2
HIGH-CCHH----X'00490005' HIGH-RBA----------491519
LOW-CCHH-----X'00490009' LOW-RBA-----------491520 TRACKS--------2
HIGH-CCHH----X'0049000A' HIGH-RBA----------589823
LOW-CCHH-----X'006B000B' LOW-RBA-----------589824 TRACKS-------10
HIGH-CCHH----X'006C0005' HIGH-RBA---------1081343
LOW-CCHH-----X'03460000' LOW-RBA--------119537664 TRACKS----32
HIGH-CCHH----X'03480001' HIGH-RBA-------121110527
LOW-CCHH-----X'03480007' LOW-RBA--------121110528 TRACKS---128
HIGH-CCHH----X'0350000E' HIGH-RBA-------127401983
LOW-CCHH-----X'037D0000' LOW-RBA--------127401984 TRACKS---220

1. How 7280 KB calculated from PQTY, SQTY is enough to store 198624
KB (Space column) value?
2. Why it is not abending with ?Resource unavailable ? ? ( since the
Pqty and Sqty are not enough)
3. In this how many primary extents are there.
Priqty = 50 * 4 KB = 200 KB 1 track has 56 KB and 48KB
is used by DB2
200/48 = 4.16 track = approximately 5 tracks
Should it have 5 tracks or lesser than that for the first extent.
4. Secqty = 15 * 4 KB = 60 KB
60 KB / 48 = 1.25 = 2 tracks
All the extents should have 2 tracks or lesser than that. Please let me
know whether my understanding on this is correct.
Thanks,
VAnitha




IMPORTANT - CONFIDENTIALITY NOTICE - This e-mail is intended only for
the use of the individual or entity shown above as addressees . It may
contain information which is privileged, confidential or otherwise
protected from disclosure under applicable laws . If the reader of this
transmission is not the intended recipient, you are hereby notified that
any dissemination, printing, distribution, copying, disclosure or the
taking of any action in reliance on the contents of this information is
strictly prohibited. If you have received this transmission in error,
please immediately notify us by reply e-mail or using the address below
and delete the message and any attachments from your system .

Amadeus Data Processing GmbH
Geschäftsführer: Eberhard Haag
Sitz der Gesellschaft: Erding
HR München 48 199
Berghamer Strasse 6
85435 Erding
Germany

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Douwe van Sluis

Re: PQTY, SQTY and SPACE
(in response to Michael Ebert)
Vanitha,

You need to understand the difference between allocation and extent.
Allocation is the amount of diskstorage requested by DB2, either PRIQTY
or SECQTY. How this is allocated on disk depends on a number of things.
First, how fragmentized is you disk. If DB2 requests 10 tracks as PRIQTY
and the largest extent available on disk is 8, than 2 extents are used
to allocate the PRIQTY (8 + 2).
Second, what z/OS version are you using. Looking at your extent sizes
below, "SMS Extent Consolidation" is in effect. This is the function
where SMS (since 1.5) will consolidate multiple allocations into 1
extent. So, continuing the first example. If thereafter a SECQTY of 5
tracks was requested by DB2 than this might look as (8+2) for the
primary and now another 5, could result in 1 extent of 8 tracks and a
second extent of 7 tracks. The extent consolidation is only done if the
next extent is physically next to the previous extent (HIGH- and
LOW-CCHH differ 1).
So, back to your numbers. The first extent is 8 tracks. LISTCAT shows
SPACE-PRI as 6 tracks and SPACE-SEC as 2 tracks. We leave the 6 tracks
for what it is for now... The first extent is a consolidation of 1
PRIQTY and 1 SECQTY requested by DB2. The last extent is made up of a
consolidation of 44 SECQTY's, so it seems....
Physically you've got only 7 extents. DB2 tells you in the EXTENTS
column that it has taken 119 allocations.
Total vsam size is 402 tracks, 6 tracts for PRIQTY leaves 396 tracks
allocated by SECQTY. This would mean 396/2=198 allocation. This does not
match your 119, which makes me conclude that you must be using "Sliding
scale". See if your zparm MGEXTSZ is YES.
The PRIQTY should be, as you say, 5 tracks. I remember there has been a
PTF out that corrects a wrong calculation of the first allocation.
Furthermore it looks like you did not give us the complete output of the
LISTCAT. HI-U-RBA = 203390976 and the last HIGH-RBA is lower than that.

Answers (hopefully):
1) The 119 extents from SYSTABLEPART are allocation, not extents and I
think Sliding Scale is active and you did not give the complete LISTCAT
output?, so we cannot calculate the real VSAM size.
2) See 1.
3) Allocation is not equal to extent, could be less, could be more.
4) See 3.

Want to know more, take a look at Redpiece 4187 "Disk Storage access
with DB2 for z/OS".


Kind regards,
Douwe van Sluis


-----Oorspronkelijk bericht-----
Van: DB2 Data Base Discussion List [mailto:[login to unmask email] Namens
Vanitha_Subramaniam
Verzonden: woensdag 9 januari 2008 10:07
Aan: [login to unmask email]
Onderwerp: [DB2-L] PQTY, SQTY and SPACE
Continuation of my previous mail.
ALLOCATION
SPACE-TYPE---------TRACK HI-A-RBA-------203390976
SPACE-PRI--------------6 HI-U-RBA-------203390976
SPACE-SEC--------------2
EXTENTS:
LOW-CCHH-----X'00510007' LOW-RBA----------------0 TRACKS--------8
HIGH-CCHH----X'0051000E' HIGH-RBA----------393215
LOW-CCHH-----X'00490004' LOW-RBA-----------393216 TRACKS--------2
HIGH-CCHH----X'00490005' HIGH-RBA----------491519
LOW-CCHH-----X'00490009' LOW-RBA-----------491520 TRACKS--------2
HIGH-CCHH----X'0049000A' HIGH-RBA----------589823
LOW-CCHH-----X'006B000B' LOW-RBA-----------589824 TRACKS-------10
HIGH-CCHH----X'006C0005' HIGH-RBA---------1081343
.
LOW-CCHH-----X'03460000' LOW-RBA--------119537664 TRACKS----32
HIGH-CCHH----X'03480001' HIGH-RBA-------121110527
LOW-CCHH-----X'03480007' LOW-RBA--------121110528 TRACKS---128
HIGH-CCHH----X'0350000E' HIGH-RBA-------127401983
LOW-CCHH-----X'037D0000' LOW-RBA--------127401984 TRACKS---220
How 7280 KB calculated from PQTY, SQTY is enough to store 198624 KB
(Space column) value?
Why it is not abending with "Resource unavailable " ? ( since the Pqty
and Sqty are not enough)
In this how many primary extents are there.
Priqty = 50 * 4 KB = 200 KB 1 track has 56 KB and
48KB is used by DB2
200/48 = 4.16 track = approximately 5 tracks
Should it have 5 tracks or lesser than that for the first extent.
Secqty = 15 * 4 KB = 60 KB
60 KB / 48 = 1.25 = 2 tracks
All the extents should have 2 tracks or lesser than that. Please let me
know whether my understanding on this is correct.

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Michael Ebert

Re: PQTY, SQTY and SPACE
(in response to Douwe van Sluis)
As mentioned, 6 tracks as primary VSAM allocation is correct IMHO. Without
the PTF (which applied only to an MVS version a couple of years back) it
would have been 7 tracks.

Dr. Michael Ebert
DB2 & Oracle Database Administrator
aMaDEUS Data Processing
Erding / Munich, Germany




Douwe van Sluis <[login to unmask email]>
To
[login to unmask email]
cc

bcc

Subject
Re: [DB2-L] PQTY, SQTY and SPACE





Douwe van Sluis <[login to unmask email]>
Please respond to : DB2 Database Discussion list at IDUG
<[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>
09-01-08 13:42


Vanitha,

You need to understand the difference between allocation and extent.
Allocation is the amount of diskstorage requested by DB2, either PRIQTY
or SECQTY. How this is allocated on disk depends on a number of things.
First, how fragmentized is you disk. If DB2 requests 10 tracks as PRIQTY
and the largest extent available on disk is 8, than 2 extents are used
to allocate the PRIQTY (8 + 2).
Second, what z/OS version are you using. Looking at your extent sizes
below, "SMS Extent Consolidation" is in effect. This is the function
where SMS (since 1.5) will consolidate multiple allocations into 1
extent. So, continuing the first example. If thereafter a SECQTY of 5
tracks was requested by DB2 than this might look as (8+2) for the
primary and now another 5, could result in 1 extent of 8 tracks and a
second extent of 7 tracks. The extent consolidation is only done if the
next extent is physically next to the previous extent (HIGH- and
LOW-CCHH differ 1).
So, back to your numbers. The first extent is 8 tracks. LISTCAT shows
SPACE-PRI as 6 tracks and SPACE-SEC as 2 tracks. We leave the 6 tracks
for what it is for now... The first extent is a consolidation of 1
PRIQTY and 1 SECQTY requested by DB2. The last extent is made up of a
consolidation of 44 SECQTY's, so it seems....
Physically you've got only 7 extents. DB2 tells you in the EXTENTS
column that it has taken 119 allocations.
Total vsam size is 402 tracks, 6 tracts for PRIQTY leaves 396 tracks
allocated by SECQTY. This would mean 396/2=198 allocation. This does not
match your 119, which makes me conclude that you must be using "Sliding
scale". See if your zparm MGEXTSZ is YES.
The PRIQTY should be, as you say, 5 tracks. I remember there has been a
PTF out that corrects a wrong calculation of the first allocation.
Furthermore it looks like you did not give us the complete output of the
LISTCAT. HI-U-RBA = 203390976 and the last HIGH-RBA is lower than that.

Answers (hopefully):
1) The 119 extents from SYSTABLEPART are allocation, not extents and I
think Sliding Scale is active and you did not give the complete LISTCAT
output?, so we cannot calculate the real VSAM size.
2) See 1.
3) Allocation is not equal to extent, could be less, could be more.
4) See 3.

Want to know more, take a look at Redpiece 4187 "Disk Storage access
with DB2 for z/OS".


Kind regards,
Douwe van Sluis




IMPORTANT - CONFIDENTIALITY NOTICE - This e-mail is intended only for
the use of the individual or entity shown above as addressees . It may
contain information which is privileged, confidential or otherwise
protected from disclosure under applicable laws . If the reader of this
transmission is not the intended recipient, you are hereby notified that
any dissemination, printing, distribution, copying, disclosure or the
taking of any action in reliance on the contents of this information is
strictly prohibited. If you have received this transmission in error,
please immediately notify us by reply e-mail or using the address below
and delete the message and any attachments from your system .

Amadeus Data Processing GmbH
Geschäftsführer: Eberhard Haag
Sitz der Gesellschaft: Erding
HR München 48 199
Berghamer Strasse 6
85435 Erding
Germany

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Douwe van Sluis

Re: PQTY, SQTY and SPACE
(in response to Michael Ebert)
But why 6 tracks and not 5?
PRIQTY is 50 4k pages = 200k. 48k per track ==> 200/48 = 5 Tracks.
Where does the extra track come from?


Kind regards,
Douwe van Sluis

-----Oorspronkelijk bericht-----
Van: DB2 Data Base Discussion List [mailto:[login to unmask email] Namens
Michael Ebert
Verzonden: woensdag 9 januari 2008 14:18
Aan: [login to unmask email]
Onderwerp: Re: [DB2-L] PQTY, SQTY and SPACE



As mentioned, 6 tracks as primary VSAM allocation is correct IMHO.
Without the PTF (which applied only to an MVS version a couple of years
back) it would have been 7 tracks.

Dr. Michael Ebert
DB2 & Oracle Database Administrator
aMaDEUS Data Processing
Erding / Munich, Germany



Douwe van Sluis <[login to unmask email]> [login to unmask email]
cc
bcc
SubjectRe: [DB2-L] PQTY, SQTY and SPACE



Douwe van Sluis <[login to unmask email]>
Please respond to : DB2 Database Discussion list at IDUG
<[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>
09-01-08 13:42



Vanitha,

You need to understand the difference between allocation and extent.
Allocation is the amount of diskstorage requested by DB2, either PRIQTY
or SECQTY. How this is allocated on disk depends on a number of things.
First, how fragmentized is you disk. If DB2 requests 10 tracks as PRIQTY
and the largest extent available on disk is 8, than 2 extents are used
to allocate the PRIQTY (8 + 2).
Second, what z/OS version are you using. Looking at your extent sizes
below, "SMS Extent Consolidation" is in effect. This is the function
where SMS (since 1.5) will consolidate multiple allocations into 1
extent. So, continuing the first example. If thereafter a SECQTY of 5
tracks was requested by DB2 than this might look as (8+2) for the
primary and now another 5, could result in 1 extent of 8 tracks and a
second extent of 7 tracks. The extent consolidation is only done if the
next extent is physically next to the previous extent (HIGH- and
LOW-CCHH differ 1).
So, back to your numbers. The first extent is 8 tracks. LISTCAT shows
SPACE-PRI as 6 tracks and SPACE-SEC as 2 tracks. We leave the 6 tracks
for what it is for now... The first extent is a consolidation of 1
PRIQTY and 1 SECQTY requested by DB2. The last extent is made up of a
consolidation of 44 SECQTY's, so it seems....
Physically you've got only 7 extents. DB2 tells you in the EXTENTS
column that it has taken 119 allocations.
Total vsam size is 402 tracks, 6 tracts for PRIQTY leaves 396 tracks
allocated by SECQTY. This would mean 396/2=198 allocation. This does not
match your 119, which makes me conclude that you must be using "Sliding
scale". See if your zparm MGEXTSZ is YES.
The PRIQTY should be, as you say, 5 tracks. I remember there has been a
PTF out that corrects a wrong calculation of the first allocation.
Furthermore it looks like you did not give us the complete output of the
LISTCAT. HI-U-RBA = 203390976 and the last HIGH-RBA is lower than that.

Answers (hopefully):
1) The 119 extents from SYSTABLEPART are allocation, not extents and I
think Sliding Scale is active and you did not give the complete LISTCAT
output?, so we cannot calculate the real VSAM size.
2) See 1.
3) Allocation is not equal to extent, could be less, could be more.
4) See 3.

Want to know more, take a look at Redpiece 4187 "Disk Storage access
with DB2 for z/OS".


Kind regards,
Douwe van Sluis




IMPORTANT - CONFIDENTIALITY NOTICE - This e-mail is intended only
for the use of the individual or entity shown above as addressees . It
may contain information which is privileged, confidential or otherwise
protected from disclosure under applicable laws . If the reader of this
transmission is not the intended recipient, you are hereby notified that
any dissemination, printing, distribution, copying, disclosure or the
taking of any action in reliance on the contents of this information is
strictly prohibited. If you have received this transmission in error,
please immediately notify us by reply e-mail or using the address below
and delete the message and any attachments from your system .

Amadeus Data Processing GmbH
Geschäftsführer: Eberhard Haag
Sitz der Gesellschaft: Erding
HR München 48 199
Berghamer Strasse 6
85435 Erding
Germany
The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L
list archives, the FAQ, and delivery preferences are at www.idug.org
under the Listserv tab. While at the site, you can also access the IDUG
Online Learning Center, Tech Library and Code Place, see the latest IDUG
conference information, and much more.
If you have not yet signed up for Basic Membership in IDUG, available at
no cost, click on Member Services

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Michael Ebert

Re: PQTY, SQTY and SPACE
(in response to Douwe van Sluis)
Quote from previous email:

"IDCAMS allocates VSAM files in CA sizes. The CA (control area I think) is
the minimum of PQ and SQ, rounded up to tracks but never more than one
cylinder. If the CA size is less than one cylinder, IDCAMS allocates the
file in TRKs, otherwise in CYLs."

The CA size in this case is 2 tracks (=SQ), so the PQ is rounded up to 6
tracks. I found this out a couple of years ago when I noticed that my
automation kept reorging the same small tablespace over and over again
(trying to get rid of the extra track, which was unused by DB2).

Dr. Michael Ebert
DB2 & Oracle Database Administrator
aMaDEUS Data Processing
Erding / Munich, Germany




Douwe van Sluis <[login to unmask email]>
To
[login to unmask email]
cc

bcc

Subject
Re: [DB2-L] PQTY, SQTY and SPACE





Douwe van Sluis <[login to unmask email]>
Please respond to : DB2 Database Discussion list at IDUG
<[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>
09-01-08 14:36


But why 6 tracks and not 5?
PRIQTY is 50 4k pages = 200k. 48k per track ==> 200/48 = 5 Tracks.
Where does the extra track come from?


Kind regards,
Douwe van Sluis

-----Oorspronkelijk bericht-----
Van: DB2 Data Base Discussion List [mailto:[login to unmask email] Namens
Michael Ebert
Verzonden: woensdag 9 januari 2008 14:18
Aan: [login to unmask email]
Onderwerp: Re: [DB2-L] PQTY, SQTY and SPACE



As mentioned, 6 tracks as primary VSAM allocation is correct IMHO.
Without the PTF (which applied only to an MVS version a couple of years
back) it would have been 7 tracks.

Dr. Michael Ebert
DB2 & Oracle Database Administrator
aMaDEUS Data Processing
Erding / Munich, Germany




IMPORTANT - CONFIDENTIALITY NOTICE - This e-mail is intended only for
the use of the individual or entity shown above as addressees . It may
contain information which is privileged, confidential or otherwise
protected from disclosure under applicable laws . If the reader of this
transmission is not the intended recipient, you are hereby notified that
any dissemination, printing, distribution, copying, disclosure or the
taking of any action in reliance on the contents of this information is
strictly prohibited. If you have received this transmission in error,
please immediately notify us by reply e-mail or using the address below
and delete the message and any attachments from your system .

Amadeus Data Processing GmbH
Geschäftsführer: Eberhard Haag
Sitz der Gesellschaft: Erding
HR München 48 199
Berghamer Strasse 6
85435 Erding
Germany

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Dale Smock

Re: PQTY, SQTY and SPACE
(in response to Michael Ebert)
Another possible explanation if this is a segmented tablespace: DB2 will
allocate at least enough space for the segsize. If the segsize is 64, DB2
will allocate 6 tracks to hold the first 64 pages plus the header pages
(64/12=5.3333 rounded up to 6).

Dale Smock
Random House

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of
Douwe van Sluis
Sent: Wednesday, January 09, 2008 8:37 AM
To: [login to unmask email]
Subject: Re: [DB2-L] PQTY, SQTY and SPACE

But why 6 tracks and not 5?
PRIQTY is 50 4k pages = 200k. 48k per track ==> 200/48 = 5 Tracks.
Where does the extra track come from?


Kind regards,
Douwe van Sluis

-----Oorspronkelijk bericht-----
Van: DB2 Data Base Discussion List [mailto:[login to unmask email] Namens
Michael Ebert
Verzonden: woensdag 9 januari 2008 14:18
Aan: [login to unmask email]
Onderwerp: Re: [DB2-L] PQTY, SQTY and SPACE



As mentioned, 6 tracks as primary VSAM allocation is correct IMHO.
Without the PTF (which applied only to an MVS version a couple of years
back) it would have been 7 tracks.

Dr. Michael Ebert
DB2 & Oracle Database Administrator
aMaDEUS Data Processing
Erding / Munich, Germany



Douwe van Sluis <[login to unmask email]> [login to unmask email]
cc
bcc
SubjectRe: [DB2-L] PQTY, SQTY and SPACE



Douwe van Sluis <[login to unmask email]>
Please respond to : DB2 Database Discussion list at IDUG
<[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>
09-01-08 13:42



Vanitha,

You need to understand the difference between allocation and extent.
Allocation is the amount of diskstorage requested by DB2, either PRIQTY
or SECQTY. How this is allocated on disk depends on a number of things.
First, how fragmentized is you disk. If DB2 requests 10 tracks as PRIQTY
and the largest extent available on disk is 8, than 2 extents are used
to allocate the PRIQTY (8 + 2).
Second, what z/OS version are you using. Looking at your extent sizes
below, "SMS Extent Consolidation" is in effect. This is the function
where SMS (since 1.5) will consolidate multiple allocations into 1
extent. So, continuing the first example. If thereafter a SECQTY of 5
tracks was requested by DB2 than this might look as (8+2) for the
primary and now another 5, could result in 1 extent of 8 tracks and a
second extent of 7 tracks. The extent consolidation is only done if the
next extent is physically next to the previous extent (HIGH- and
LOW-CCHH differ 1).
So, back to your numbers. The first extent is 8 tracks. LISTCAT shows
SPACE-PRI as 6 tracks and SPACE-SEC as 2 tracks. We leave the 6 tracks
for what it is for now... The first extent is a consolidation of 1
PRIQTY and 1 SECQTY requested by DB2. The last extent is made up of a
consolidation of 44 SECQTY's, so it seems....
Physically you've got only 7 extents. DB2 tells you in the EXTENTS
column that it has taken 119 allocations.
Total vsam size is 402 tracks, 6 tracts for PRIQTY leaves 396 tracks
allocated by SECQTY. This would mean 396/2=198 allocation. This does not
match your 119, which makes me conclude that you must be using "Sliding
scale". See if your zparm MGEXTSZ is YES.
The PRIQTY should be, as you say, 5 tracks. I remember there has been a
PTF out that corrects a wrong calculation of the first allocation.
Furthermore it looks like you did not give us the complete output of the
LISTCAT. HI-U-RBA = 203390976 and the last HIGH-RBA is lower than that.

Answers (hopefully):
1) The 119 extents from SYSTABLEPART are allocation, not extents and I
think Sliding Scale is active and you did not give the complete LISTCAT
output?, so we cannot calculate the real VSAM size.
2) See 1.
3) Allocation is not equal to extent, could be less, could be more.
4) See 3.

Want to know more, take a look at Redpiece 4187 "Disk Storage access
with DB2 for z/OS".


Kind regards,
Douwe van Sluis




IMPORTANT - CONFIDENTIALITY NOTICE - This e-mail is intended only
for the use of the individual or entity shown above as addressees . It
may contain information which is privileged, confidential or otherwise
protected from disclosure under applicable laws . If the reader of this
transmission is not the intended recipient, you are hereby notified that
any dissemination, printing, distribution, copying, disclosure or the
taking of any action in reliance on the contents of this information is
strictly prohibited. If you have received this transmission in error,
please immediately notify us by reply e-mail or using the address below
and delete the message and any attachments from your system .

Amadeus Data Processing GmbH
Geschäftsführer: Eberhard Haag
Sitz der Gesellschaft: Erding
HR München 48 199
Berghamer Strasse 6
85435 Erding
Germany
The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L
list archives, the FAQ, and delivery preferences are at www.idug.org
under the Listserv tab. While at the site, you can also access the IDUG
Online Learning Center, Tech Library and Code Place, see the latest IDUG
conference information, and much more.
If you have not yet signed up for Basic Membership in IDUG, available at
no cost, click on Member Services

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list
archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug
under the Listserv tab. While at the site, you can also access the IDUG
Online Learning Center, Tech Library and Code Place, see the latest IDUG
conference information, and much more. If you have not yet signed up for
Basic Membership in IDUG, available at no cost, click on Member Services at
http://www.idug.org/lsms





The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Phil Grainger

Re: PQTY, SQTY and SPACE
(in response to Dale Smock)
You have a high used RBA of 203,390,976 bytes

Or (about 203Mb)

Your average row length is 138, so that's approx 29 rows per 4K page.
One 3390 track can contain (as Ram says) 12 pages, so each track can
hold about 12 x 29 = 348 rows

Adding up your track allocations, we have 402 tracks, which could
contain something like 348 x 402 = (about) 140,000 rows

PS See the extent sizes in your listcat? They are all in tracks and go
8, 2, 2, 10, 32, 128, 220 so you've either got some amazing extent
consolidation going on OR you are getting the "benefit" of DB2s
autonomic space management - so you have 402 tracks or 402 * 12 DB2
pages (4,824 pages) of 4K each - which is 19.8Kb. Ooh - that's the same
number as your "How 7280 KB calculated from PQTY, SQTY is enough to
store 198624 KB (Space column) value? "!!

You have a LOT more space than 7280Kb in use :)

Hope this helps


Phil Grainger
CA
Product Management Director
Phone: +44 (0)1753 577 733
Mobile: +44 (0)7970 125 752
eMail: [login to unmask email]

Ditton Park
Riding Court Road
Datchet
Slough
SL3 9LL

CA plc a company registered in England and Wales under company
registration number 1282495 with its registered office at the address
set out above. VAT number 697904179.


________________________________

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Vanitha_Subramaniam
Sent: 09 January 2008 10:05
To: [login to unmask email]
Subject: Re: [DB2-L] PQTY, SQTY and SPACE



Hi Ram,

I have used these calculations and the Primary and secondary quantity
are 50, 15 (from SYSTABLEPART) 4K pages respectively. AVGRECLEN is 138.
PCTFREE and FREEPAGE are 0.

Based on the above, what is the maximum number of records the table can
have?

Thanks,

Vanitha


The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Phil Grainger

Re: PQTY, SQTY and SPACE
(in response to Phil Grainger)
AND, one thing I forgot from my previous mail, you can also now get
extent consolidation where although DB2 takes multiple ALLOCATIONS (such
as 1 primary and 5 secondaries) you can still end up with only ONE
extent if each of the allocations is placed physically adjacent to the
prior one on DASD

To put this in simple terms

PRIQTY and SECQTY and ALLOCATION amounts - amounts that DB2 will request
from DFP for allocation and extension of datasets

EXTENTS are physical pieces of the dataset as it ends up on DASD

PRIMARY ALLOCATIONS/SECONDARY ALLOCATIONS and EXTENTS and NOT synonymous
- they are DIFFERENT THINGS and cannot be used interchangeably

Phil Grainger
CA
Product Management Director
Phone: +44 (0)1753 577 733
Mobile: +44 (0)7970 125 752
eMail: [login to unmask email]

Ditton Park
Riding Court Road
Datchet
Slough
SL3 9LL

CA plc a company registered in England and Wales under company
registration number 1282495 with its registered office at the address
set out above. VAT number 697904179.


________________________________

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Vanitha_Subramaniam
Sent: 09 January 2008 09:07
To: [login to unmask email]
Subject: [DB2-L] PQTY, SQTY and SPACE



Continuation of my previous mail.

ALLOCATION

SPACE-TYPE---------TRACK HI-A-RBA-------203390976

SPACE-PRI--------------6 HI-U-RBA-------203390976

SPACE-SEC--------------2

EXTENTS:

LOW-CCHH-----X'00510007' LOW-RBA----------------0 TRACKS--------8

HIGH-CCHH----X'0051000E' HIGH-RBA----------393215

LOW-CCHH-----X'00490004' LOW-RBA-----------393216 TRACKS--------2

HIGH-CCHH----X'00490005' HIGH-RBA----------491519

LOW-CCHH-----X'00490009' LOW-RBA-----------491520 TRACKS--------2

HIGH-CCHH----X'0049000A' HIGH-RBA----------589823

LOW-CCHH-----X'006B000B' LOW-RBA-----------589824 TRACKS-------10

HIGH-CCHH----X'006C0005' HIGH-RBA---------1081343

.

LOW-CCHH-----X'03460000' LOW-RBA--------119537664 TRACKS----32

HIGH-CCHH----X'03480001' HIGH-RBA-------121110527

LOW-CCHH-----X'03480007' LOW-RBA--------121110528 TRACKS---128

HIGH-CCHH----X'0350000E' HIGH-RBA-------127401983

LOW-CCHH-----X'037D0000' LOW-RBA--------127401984 TRACKS---220

1. How 7280 KB calculated from PQTY, SQTY is enough to store 198624
KB (Space column) value?
2. Why it is not abending with "Resource unavailable " ? ( since
the Pqty and Sqty are not enough)
3. In this how many primary extents are there.

Priqty = 50 * 4 KB = 200 KB 1 track has 56 KB and
48KB is used by DB2

200/48 = 4.16 track = approximately 5 tracks

Should it have 5 tracks or lesser than that for the first extent.

4. Secqty = 15 * 4 KB = 60 KB

60 KB / 48 = 1.25 = 2 tracks

All the extents should have 2 tracks or lesser than that. Please let me
know whether my understanding on this is correct.

Thanks,

VAnitha




The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Phil Grainger

Re: PQTY, SQTY and SPACE
(in response to Phil Grainger)
I know you haven't got to your actual question yet, but when you say

"Space = (PQTY + SQTY * (EXTENTS - 1 ) ) * 4

= ( 50 + 15 * 118) * 4 = 7280 KB"



That's NOT TRUE



You're assuming that the number of extents = "1 for the primary and the
rest are 1 secondary for each extent"



Extents are not that simple



e.g.



The primary ALLOCATION can be made up of a number of EXTENTS (used to be
up to 4, not sure if it still is)

Similarly, each secondary ALLOCATION can also be made up of a number of
EXTENTS



Also, any secondary EXTENT does NOT have to be created with a size of
SECQTY - it can sometimes be smaller (sometimes MUCH smaller)



Sorry to jump in, but this is a very common misconception (and might
even answer your eventual question!)


Phil Grainger
CA
Product Management Director
Phone: +44 (0)1753 577 733
Mobile: +44 (0)7970 125 752
eMail: [login to unmask email]


________________________________

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Vanitha_Subramaniam
Sent: 09 January 2008 08:30
To: [login to unmask email]
Subject: [DB2-L] PQTY, SQTY and SPACE



Hi all,



I have read many links related to calculating the primary and secondary
quantity used and the statistics from one of our tables. Bufferpool is
BP1, So I assume it as 4K pages. Reorg and runstats ran yesterday.



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

SELECT PQTY , SQTY , SECQTYI , EXTENTS , SPACE , AVGROWLEN , CARD

FROM SYSIBM.SYSTABLEPART

WHERE DBNAME = 'AFSDB'

AND TSNAME = 'TSBLNHST';

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

PQTY SQTY SECQTYI EXTENTS SPACE AVGROWLEN
CARD

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

50 15 15 119
198624 138 1410515




Space = (PQTY + SQTY * (EXTENTS - 1 ) ) * 4

= ( 50 + 15 * 118) * 4 = 7280 KB



Space = AVGROWLEN * CARD

= 138 * 1410515 = 194651070 Bytes = 194651KB



Space calculation from VSAM = No of tracks * 48 KB

= 4138 * 48 KB = 198624 KB

VSAM LISTcat shows



Enter "/" to select action Tracks %Used XT
Device

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

DB2NYBT.DSNDBD.AFSDB.TSBLNHST.I0001.A001 4138 ? 119 3390





Continued in next mail


The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms