# Calculate storage capacity of indexes in DB2 for z/OS

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.

##### Figure 1. Calculating piecesize

#### 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.

#### Downloadable resources

- PDF of this content
- Health Check Process (IndexHealthchecks.zip | 4KB)

#### Related topics

- The DB2 10 (or 11) for z/OS SQL Reference (SC19-2983-11) has information about using various table space and index parameters.
- The DB2 10 (or 11) for z/OS Administration Guide (SC19-2968-10) has more information about index usage.
- The blog entry "How big can a DB2 for z/OS index be?" was an inspiration for this article.