Max records

Vanitha Subramaniam

Max records
Hi all,



What is the maximum number of records the table can have based on the
following definitions.



AVGRECLEN - 138 from SYSTABLEPART



CREATE TABLESPACE TSBLNHST IN AFSDB

USING STOGROUP SGAFS

PRIQTY 200

SECQTY 60

ERASE NO

FREEPAGE 0

PCTFREE 0

BUFFERPOOL BP1

SEGSIZE 32



From the Admin Guide Ver 8, Chapter 7 I have calculated as follows.



Maximum space allocated = (Pqty + (sqty * 251))

= 200 + 60 * 251 =15260 KB

No of pages = 15260 / 4 = 3815

Records per page = 4056/138 = 29

No of records = 3815 * 29 = 1,10,635 max



Please let me know whether my approach is correct to find the maximum no
records.



The table now has reached 119 extents and have 14,10,515 records. So I
am not sure what I am missing here.



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

Michael Ebert

Re: Max records
(in response to Vanitha Subramaniam)
See my previous response and the chapter on CREATE TABLESPACE in the SQL
Reference, especially Rule 4 of the "Rules for primary and secondary space
allocation".

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] Max records





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 13:25
Hi all,

What is the maximum number of records the table can have based on the
following definitions.

AVGRECLEN - 138 from SYSTABLEPART

CREATE TABLESPACE TSBLNHST IN AFSDB
USING STOGROUP SGAFS
PRIQTY 200
SECQTY 60
ERASE NO
FREEPAGE 0
PCTFREE 0
BUFFERPOOL BP1
SEGSIZE 32

From the Admin Guide Ver 8, Chapter 7 I have calculated as follows.

Maximum space allocated = (Pqty + (sqty * 251))
= 200 + 60 * 251 =15260 KB
No of pages = 15260 / 4 = 3815
Records per page = 4056/138 = 29
No of records = 3815 * 29 = 1,10,635 max

Please let me know whether my approach is correct to find the maximum no
records.

The table now has reached 119 extents and have 14,10,515 records. So I am
not sure what I am missing here.

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

Chris Blaicher

Re: Max records
(in response to Michael Ebert)
You maybe are missing the effects of the SLIDING SCALE feature. DB2
will use a secondary allocation, in cylinders, equal to the greater of
the specified secondary amount and the extent number.



Thus, when you get to extent 100, your allocation will be for 100
cylinders.



Do a LISTCAT and see what the allocations are.



Chris Blaicher





________________________________

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Vanitha_Subramaniam
Sent: Wednesday, January 09, 2008 6:26 AM
To: [login to unmask email]
Subject: [DB2-L] Max records



From the Admin Guide Ver 8, Chapter 7 I have calculated as follows.



Maximum space allocated = (Pqty + (sqty * 251))

= 200 + 60 * 251 =15260 KB

No of pages = 15260 / 4 = 3815

Records per page = 4056/138 = 29

No of records = 3815 * 29 = 1,10,635 max



Please let me know whether my approach is correct to find the maximum no
records.



The table now has reached 119 extents and have 14,10,515 records. So I
am not sure what I am missing here.



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 www.idug.org
< 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
< http://www.idug.org/lsconf > , and much more.
If you have not yet signed up for Basic Membership in IDUG, available at
no cost, click on Member Services < 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

Michael Ebert

Re: Max records
(in response to Chris Blaicher)
It's not quite so simple. See the graph in the "Rules about primary and
secondary allocations" mentioned previously.

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




"Blaicher, Chris" <[login to unmask email]>
To
[login to unmask email]
cc

bcc

Subject
Re: [DB2-L] Max records





"Blaicher, Chris" <[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 17:55


You maybe are missing the effects of the SLIDING SCALE feature. DB2 will
use a secondary allocation, in cylinders, equal to the greater of the
specified secondary amount and the extent number.

Thus, when you get to extent 100, your allocation will be for 100
cylinders.

Do a LISTCAT and see what the allocations are.

Chris Blaicher




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

Vanitha Subramaniam

Re: Max records
(in response to Michael Ebert)
1. MGEXTSZ is NO (Referred SYS5.DB2.V810D6.SDSNSAMP(DSNTIJUZ)) .
Does it mean sliding space algorithm is not used.
2. SEGSIZE is 32. Will it make any difference.
3. How could this table can hold this 14,10,515 no of records with
less Pqty and Sqty.



Thanks,

Vanitha



________________________________

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Blaicher, Chris
Sent: Wednesday, January 09, 2008 10:25 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Max records



You maybe are missing the effects of the SLIDING SCALE feature. DB2
will use a secondary allocation, in cylinders, equal to the greater of
the specified secondary amount and the extent number.



Thus, when you get to extent 100, your allocation will be for 100
cylinders.



Do a LISTCAT and see what the allocations are.



Chris Blaicher



To: [login to unmask email]
Subject: [DB2-L] Max records



From the Admin Guide Ver 8, Chapter 7 I have calculated as follows.

Maximum space allocated = (Pqty + (sqty * 251))

= 200 + 60 * 251 =15260 KB

No of pages = 15260 / 4 = 3815

Records per page = 4056/138 = 29

No of records = 3815 * 29 = 1,10,635 max

The table now has reached 119 extents and have 14,10,515 records. So I
am not sure what I am missing here.

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 www.idug.org
< 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
< http://www.idug.org/lsconf > , and much more.
If you have not yet signed up for Basic Membership in IDUG, available at
no cost, click on Member Services < 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 www.idug.org
< 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
< http://www.idug.org/lsconf > , and much more.
If you have not yet signed up for Basic Membership in IDUG, available at
no cost, click on Member Services < http://www.idug.org/lsms >


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

Phil Grainger

Re: Max records
(in response to Vanitha Subramaniam)
Hi Vanitha

I know this sounds like an easy question, but actually it doesn't make
sense

What you are asking is "what is the maximum size of this tablespace and
how many rows will it hold"

Well, the maximum size is NOT defined by PRIQTY and SECQTY any more -
it's really bounded by whichever of:

Maximum number of extents
Maximum pageset size
Out of DASD space

you hit first!


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 12:26
To: [login to unmask email]
Subject: [DB2-L] Max records



Hi all,



What is the maximum number of records the table can have based on the
following definitions.



AVGRECLEN - 138 from SYSTABLEPART



CREATE TABLESPACE TSBLNHST IN AFSDB

USING STOGROUP SGAFS

PRIQTY 200

SECQTY 60

ERASE NO

FREEPAGE 0

PCTFREE 0

BUFFERPOOL BP1

SEGSIZE 32



From the Admin Guide Ver 8, Chapter 7 I have calculated as follows.



Maximum space allocated = (Pqty + (sqty * 251))

= 200 + 60 * 251 =15260 KB

No of pages = 15260 / 4 = 3815

Records per page = 4056/138 = 29

No of records = 3815 * 29 = 1,10,635 max



Please let me know whether my approach is correct to find the maximum no
records.



The table now has reached 119 extents and have 14,10,515 records. So I
am not sure what I am missing here.



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