Calculate storage capacity of indexes in DB2 for z/OS

With the drastic growth of data stored in IBM® DB2® tables, index sizes are bound to increase. Most indexes are still uncompressed, so there's an urgent need to monitor indexes to avoid unforeseen outages related to index capacity. This article describes a process to calculate the capacity limit for various types of indexes. Once you know how to calculate the capacity of indexes in different situations, you can monitor their growth to avoid outages.

Share:

Sriram Lakshminarasimhan (SRIRAM.L1@in.ibm.com), IT Specialist - DB2 for z/OS, IBM India Pvt Ltd.

Sriram is an IBM Certified IT Specialist with close to 12 years of experience in DB2 database administration and application development on the z/OS platform. He is serving as the India SME of the mainframe DB2 DBA service line. Sriram is the point of contact for all DB2-related issues on the accounts worked on by India DC. He also guides the DBA teams on automation efforts.

12 June 2014

Also available in Russian

Introduction

We recently experienced an outage situation in a test region. The cause of the outage was an index that had grown to its limit; there was no more room for growth. We could not have afforded such an outage in production. Capacity limits of indexes is an area that is not given much attention and hence not fully understood. With the ever-increasing growth of data stored in DB2 tables, index sizes are bound to increase. Because most indexes are still uncompressed, the need to monitor indexes is more urgent than ever in order to avoid outages related to index capacity.

This article explains how to calculate the capacity limit for various types of indexes. Also included is a health check process that you can deploy in any DB2 for z/OS® environment. This article could benefit mid-level and senior DBAs and help them mitigate a potential risk.

Calculating index capacity

Table 1 shows the types of indexes that can be created on the various types of table spaces. The capacity of an index is determined based on the type of index and table space. (Discussion of XML and LOB table spaces is outside the scope of this article.)

Table 1. Table space and index combinations
Table space type Index type
Simple Simple
Segmented Simple
Classic partitioned table space Partitioned index
Non-partitioned index (NPI)
Data partitioned secondary index (DPSI)
Universal table space: Partition by range (UTS - PBR) Partitioned index
Non-partitioned index (NPI)
Data partitioned secondary index (DPSI)
Universal table space: Partition by growth (UTS - PBG) Non-partitioned index (NPI)

Simple or segmented table spaces

Simple table spaces can no longer be created as of DB2 V9. However, existing simple table spaces are supported. Simple table spaces are defined with no SEGSIZE or NUMPARTS or MAXPARTITIONS option on the table space specification.

Segmented table spaces are defined with a SEGSIZE option and have no NUMPARTS or MAXPARTITIONS specification.

The capacity of an index on a simple or segmented table space is:

`32 datasets * PIECESIZE`

The maximum value of PIECESIZE is 2G. As a result, the maximum capacity is limited to 64G. If PIECESIZE for an index is specified as 1G, then the capacity of that index is 32G.

Classic partitioned and universal table spaces

Classic partitioned table spaces are table spaces that have been defined with a NUMPARTS option but without a SEGSIZE specification. Classic partitioned table spaces are deprecated in DB2 10, though existing classic partitioned table spaces are supported.

There are two types of universal table spaces:

Partition by range
Created with a NUMPARTS option on the table space definition and an explicit or implicit SEGSIZE specification (an explicit SEGSIZE specification is not required in a DB2 10 or 11 system if a default SEGSIZE is set via the ZPARM parameter DBSEGSZ).
Partition by growth
Created with the MAXPARTITIONS option on the table space definition.

The capacity of an index on one of the table space types mentioned previously would depend on the following:

• Whether or not that table space was defined with a DSSIZE or the LARGE attribute.
• Whether the index is partitioned or non-partitioned, as discussed next.

Non-partitioned indexes

When LARGE or DSSIZE is not specified for the table space, maximum index capacity is equal to

`32 datasets * PIECESIZE`

When LARGE or DSSIZE is specified for the table space, maximum index capacity is equal to

`PIECESIZE  * number of partitions`

The number of partitions phrase refers to the theoretical maximum number of partitions that can be specified for a table space and not the value specified for NUMPARTS. The maximum number of partitions is based on the DSSIZE and the page size of the table space, as shown in Table 2.

Table 2. Partition limit based on DSSIZE and Pagesize
DSSIZE value 4K page size 8K page size 16K page size 32K page size
1 GB – 4 GB 4096 4096 4096 4096
8 GB 2048 4096 4096 4096
16 GB 1024 2048 4096 4096
32 GB 512 1024 2048 4096
64 GB 256 512 1024 2048
128 GB 128 256 512 1024
256 GB 64 128 256 512

If PIECESIZE is not specified, the default PIECESIZE is calculated as follows:

`PIECESIZE = MIN(X,2^32/(MIN(4096,2^32/(X/Y)))*Z)`

Where:
X is DSSIZE
Y is the pagesize of the table space
Z is the pagesize of the index

The order of computation is based on the rules of arithmetic, as in Figure 1.

Partitioned indexes (not applicable to PBG table spaces)

When LARGE or DSSIZE is specified in the table space definition, index partition size is equal to

``` MIN(X, 2^32
/(MIN(4096, 2^32 / (X / Y))) * Z)```

Where:
X is DSSIZE
Y is the pagesize of the table space
Z is the pagesize of the index

When LARGE or DSSIZE is not specified, DSSIZE is determined as follows:

• 4G, when NUMPARTS is between 1 and 16
• 2G, when NUMPARTS is between 17 and 32
• 1G, when NUMPARTS is between 33 and 64
• 4G, when NUMPARTS is between 65 and 254
• IF NUMPARTS > 254, DSSIZE is determined as follows:
• 4G, when the pagesize of the table space is 4K
• 8G, when the pagesize of the table space is 8K
• 16G, when the pagesize of the table space is 16K
• 32G, when the pagesize of the table space is 32K
`Index partition size = MIN(X, 2^32 /(MIN(4096, 2^32 / (X / Y))) * Z)`

Where:
X is DSSIZE
Y is the pagesize of the table space
Z is the pagesize of the index

Conclusion

In this article, you learned how to calculate the capacity of indexes in various situations. Now that you're equipped, it is recommended that you set up a health check process to monitor the growth of indexes to avoid unforeseen outages.

DescriptionNameSize
Health Check ProcessIndexHealthchecks.zip4KB

Resources

Discuss

• Get involved in the My developerWorks community. Connect with other developerWorks users while exploring the developer-driven blogs, forums, groups, and wikis.