Max index size (Db2 11 for z/OS)

Horacio Villa

Max index size (Db2 11 for z/OS)
How can I calculate the maximum size of:a) NPI on a partitioned TS  (PBR)b) a partition of a partitioned tablespace (PBG)c) an index on a PBG TS I don't get quite sure what it says in SQL Reference.Does PIECESIZE have an influence? (may it restringe, if too low, the number or VSAMs it can have?)I believe it cant' be more than 4096 VSAMs data sets because of the naming scheme. Horacio Villa

Roy Boxwell

Max index size (Db2 11 for z/OS)
(in response to Horacio Villa)
I use our help panel in ISPF for this sort of thing as I have worked this out years ago....

Object type: TABLESPACE ! Maximum number of data sets
-----------------------------+----------------------------
LOB tablespaces ! 254
-----------------------------+----------------------------
Non-partitioned tablespaces ! 32
-----------------------------+----------------------------
Partitioned tablespaces ! 1 (Percent used check)
-----------------------------+----------------------------
Partitioned By Growth ! MAXPARTITIONS. LPS check if
tablespaces ! more than one. If on last
! partition then percent used.
-----------------------------+----------------------------
Object type: INDEX ! Maximum number of data sets
-----------------------------+----------------------------
Non-partitioned indexes on ! MIN ( 4096 , 2 power 32 /
tablespace with LARGE, ! ( DSSIZE / TS PGSIZE))
tablespace with LARGE, ! ( DSSIZE / TS PGSIZE))
DSSIZE, or more than 64 ! Eg: 128 GB DSSIZE with
Partitions ! 8 KB Tablespace Page
! gives 256 Pieces (datasets)
! Or 4 GB DSSIZE with
! 4 KB Tablespace Page
! gives 4096 Pieces (datasets)
-----------------------------+----------------------------
Non-partitioned indexes ! 32
otherwise !
-----------------------------+----------------------------
Partitioned indexes ! 1 (Percent used check)
-----------------------------+----------------------------

From the number of datasets you can then easily compute the maximum sitzes...enjoy!


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]>
http://www.seg.de http://www.seg.de

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

From: Horacio Villa [mailto:[login to unmask email]
Sent: Saturday, December 2, 2017 12:29 AM
To: [login to unmask email]
Subject: [DB2-L] - Max index size (Db2 11 for z/OS)

How can I calculate the maximum size of:
a) NPI on a partitioned TS (PBR)
b) a partition of a partitioned tablespace (PBG)
c) an index on a PBG TS

I don't get quite sure what it says in SQL Reference.
Does PIECESIZE have an influence? (may it restringe, if too low, the number or VSAMs it can have?)
I believe it cant' be more than 4096 VSAMs data sets because of the naming scheme.

Horacio Villa

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

Horacio Villa

Max index size (Db2 11 for z/OS)
(in response to Horacio Villa)
Hi Roy, thanks for this.Just to be sure, after "Object type:  INDEX" there are two rows saying:tablespace with LARGE,       !      ( DSSIZE / TS PGSIZE))I suppose there should be only one, right? Horacio Villa ----- Original message -----
From: "Boxwell, Roy" <[login to unmask email]>
To: "[login to unmask email]" <[login to unmask email]>
Cc:
Subject: [DB2-L] - RE: Max index size (Db2 11 for z/OS)
Date: Wed, Dec 6, 2017 8:49 AM


I use our help panel in ISPF for this sort of thing as I have worked this out years ago....

Object type: TABLESPACE      ! Maximum number of data sets
-----------------------------+----------------------------
LOB tablespaces              ! 254                       
-----------------------------+----------------------------
Non-partitioned tablespaces  ! 32                        
-----------------------------+----------------------------
Partitioned tablespaces      ! 1 (Percent used check)    
-----------------------------+----------------------------
Partitioned By Growth        ! MAXPARTITIONS. LPS check if
tablespaces                  ! more than one. If on last 
                             ! partition then percent used.
-----------------------------+----------------------------
Object type: INDEX           ! Maximum number of data sets
-----------------------------+----------------------------
Non-partitioned indexes on   ! MIN ( 4096 , 2 power 32 / 
tablespace with LARGE,       !      ( DSSIZE / TS PGSIZE))
tablespace with LARGE,       !      ( DSSIZE / TS PGSIZE))
DSSIZE, or more than 64      ! Eg: 128 GB DSSIZE with     
Partitions                   !       8 KB Tablespace Page 
                             ! gives 256 Pieces (datasets)
                             ! Or    4 GB DSSIZE with     
                             !       4 KB Tablespace Page 
                             ! gives 4096 Pieces (datasets)
-----------------------------+----------------------------
Non-partitioned indexes      ! 32                         
otherwise                    !                            
-----------------------------+----------------------------
Partitioned indexes          ! 1 (Percent used check)     
-----------------------------+----------------------------

From the number of datasets you can then easily compute the maximum sitzes...enjoy!

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]
http://www.seg.de

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

From: Horacio Villa [mailto:[login to unmask email]
Sent: Saturday, December 2, 2017 12:29 AM
To: [login to unmask email]
Subject: [DB2-L] - Max index size (Db2 11 for z/OS)

How can I calculate the maximum size of:
a) NPI on a partitioned TS  (PBR)
b) a partition of a partitioned tablespace (PBG)
c) an index on a PBG TS

I don't get quite sure what it says in SQL Reference.
Does PIECESIZE have an influence? (may it restringe, if too low, the number or VSAMs it can have?)
I believe it cant' be more than 4096 VSAMs data sets because of the naming scheme.

Horacio Villa -----End Original Message----- Site Links: View post online   View mailing list online   Start new thread via email   Unsubscribe from this mailing list   Manage your subscription  

This email has been sent to: [login to unmask email]
Setup a data refresh task in less time than it takes to make a cup of coffee + save up to 90% in CPU
ESAi's BCV5 & XDM fast data refresh & Test Data Mgmt products will make you a hero to users. See
http://www.ESAIGroup.com/idug

Use of this email content is governed by the terms of service at:
http://www.idug.org/p/cm/ld/fid=2

Roy Boxwell

Max index size (Db2 11 for z/OS)
(in response to Horacio Villa)
Correct ! Two screens cut and pasted...

Roy Boxwell
SOFTWARE ENGINEERING GmbH and SEGUS Inc.
-Product Development-
Heinrichstrasse 83-85
40239 Düsseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]<mailto:[login to unmask email]>
http://www.seg.de

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

On 6 Dec 2017, at 20:21, Horacio Villa <[login to unmask email]<mailto:[login to unmask email]>> wrote:

Hi Roy,

thanks for this.
Just to be sure, after "Object type: INDEX" there are two rows saying:
tablespace with LARGE, ! ( DSSIZE / TS PGSIZE))
I suppose there should be only one, right?

Horacio Villa


----- Original message -----
From: "Boxwell, Roy" <[login to unmask email]<mailto:[login to unmask email]>>
To: "[login to unmask email]<mailto:[login to unmask email]>" <[login to unmask email]<mailto:[login to unmask email]>>
Cc:
Subject: [DB2-L] - RE: Max index size (Db2 11 for z/OS)
Date: Wed, Dec 6, 2017 8:49 AM


I use our help panel in ISPF for this sort of thing as I have worked this out years ago....



Object type: TABLESPACE ! Maximum number of data sets

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

LOB tablespaces ! 254

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

Non-partitioned tablespaces ! 32

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

Partitioned tablespaces ! 1 (Percent used check)

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

Partitioned By Growth ! MAXPARTITIONS. LPS check if

tablespaces ! more than one. If on last

! partition then percent used.

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

Object type: INDEX ! Maximum number of data sets

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

Non-partitioned indexes on ! MIN ( 4096 , 2 power 32 /

tablespace with LARGE, ! ( DSSIZE / TS PGSIZE))

tablespace with LARGE, ! ( DSSIZE / TS PGSIZE))

DSSIZE, or more than 64 ! Eg: 128 GB DSSIZE with

Partitions ! 8 KB Tablespace Page

! gives 256 Pieces (datasets)

! Or 4 GB DSSIZE with

! 4 KB Tablespace Page

! gives 4096 Pieces (datasets)

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

Non-partitioned indexes ! 32

otherwise !

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

Partitioned indexes ! 1 (Percent used check)

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



From the number of datasets you can then easily compute the maximum sitzes...enjoy!





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]>
http://www.seg.de

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



From: Horacio Villa [mailto:[login to unmask email]
Sent: Saturday, December 2, 2017 12:29 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - Max index size (Db2 11 for z/OS)



How can I calculate the maximum size of:

a) NPI on a partitioned TS (PBR)

b) a partition of a partitioned tablespace (PBG)

c) an index on a PBG TS



I don't get quite sure what it says in SQL Reference.

Does PIECESIZE have an influence? (may it restringe, if too low, the number or VSAMs it can have?)

I believe it cant' be more than 4096 VSAMs data sets because of the naming scheme.



Horacio Villa



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

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



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