How big can a DB2 for z/OS index be?

That looks like a pretty simple question, doesn't it? I mean, if I were asking about maximum table space size, you'd be able to answer, really quickly, with "64 GB for a non-partitioned table space, 128 TB for a non-LOB partitioned table space," right? Asking about maximum size for an index doesn't make the question that much harder, does it? Or does it? Do you know the answer? Read on, and you will.

Just last week, I got a question from a DBA at one of the DB2-using organizations that I support. She had a non-partitioned index (NPI) defined on a table in a big partitioned table space. The index had grown to 63.9 GB, and that gave the DBA a nagging sense of unease. She was concerned that something bad might happen when the index size hit 64 GB. For lots of mainframe DB2 people, 64 GB does ring a bell as a size limit for a non-partitioned object. Does that limit apply to a non-partitioned index?

Looking for the answer to that question, you could check the "Limits in DB2 for z/OS" section of the appendix in the SQL Reference (this manual and others in the DB2 for z/OS library are available online at https://www-304.ibm.com/support/docview.wss?uid=swg27019288). There, in the "DB2 system limits" table, you'd see how large a table space can be, but you wouldn't find information on index size limits. Hmmmm....

Time to end the suspense. The aforementioned DBA can relax (and I told her so), because the non-partitioned index in question had been defined with a piece size of 64 GB, and a non-partitioned index can have, of course, more than one piece (i.e., more than one data set). In fact, any index (partitioned or non-partitioned) can have more than one data set in its page set (the data sets that comprise a DB2 table space or index space are collectively known as a page set). So, the maximum size of an index would depend on two factors: 1) the maximum number of data sets that can be in the index's page set, and 2) the maximum size of a data set in the index's page set.

Both of those index-max-size factors have some dependency on the nature of the table space holding the table on which an index is defined. If that table space is non-partitioned (i.e., a simple table space or a segmented table space that is not a universal table space), the index can have up to 32 data sets in its page set, and each of those data sets can be up to 2 GB in size -- so, in that case the maximum size of the index would be 64 GB (32 data sets times 2 GB per data set), UNLESS the index was defined with a PIECESIZE value smaller than 2G (if PIECESIZE 1G had been specified for the index, its maximum size would be 32 GB). Note that the default value for PIECESIZE is 2G if the index is defined on a table in a non-partitioned table space.

When the table on which an index is defined is in a partitioned table space, the index's maximum size depends on 1) whether or not that table space was defined with a DSSIZE specification (or with the LARGE attribute), and 2) whether the index is partitioned or non-partitioned (syntactically speaking, DSSIZE is preferred over LARGE for specifying partition size). We'll consider non-partitioned indexes first. If LARGE or DSSIZE was not specified for the associated table space, a non-partitioned index can have 32 data sets in its page set, and each of these data sets can be up to 2 GB in size. That makes the index's maximum size 64 GB (as noted above for an index associated with a non-partitioned table space, an explicitly specified PIECESIZE value smaller than 2G would reduce the index's maximum size accordingly).

If the partitioned table space was defined with the LARGE or DSSIZE options, an associated non-partitioned index can have as many data sets as the table space can have partitions. How many is that? Well, it depends on the DSSIZE value (and a LARGE specification equates to a DSSIZE value of 4G) and the page size of the table space. Details on the relationship between DSSIZE, page size, and maximum number of partitions can be found in the CREATE TABLESPACE section of the SQL Reference, but suffice it to say that the maximum size of a non-partitioned index is 128 TB (same as the maximum size of a non-LOB table space). This maximum size could be reached in one of two ways: with a PIECESIZE of 32G and 4096 data sets, or a PIECESIZE of 64G and 2048 data sets (a 64G PIECESIZE specification is possible only if the DSSIZE of the associated table space is 64G or larger, and with a DSSIZE specification of 64G and a page size of 32 KB, a table space can have a maximum of 2048 partitions). If the non-partitioned index was defined without a PIECESIZE specification, it will have a default PIECESIZE as determined by the following formula:

Default PIECESIZE = MIN(x, 2^32 /(MIN(4096, 2^32 / (x / y))) * z)

Where:

x is the DSSIZE of the associated table space
y is the page size of the table space
z is the page size of the index

For example, if you created a non-partitioned index without a PIECESIZE specification, and the associated table space had a DSSIZE of 32G (the "x" value in the above formula) and a page size of 32 KB (the "y" value in the formula), and the page size of your index is 4 KB (the "z" value), the default PIECESIZE value would be 4G.

How about the max size of a partitioned index? Because that index will have one data set for each of the associated table space's partitions, the answer to the index size limit question will (as for non-partitioned indexes) depend on the maximum number of partitions for the table space and the size of each index partition. Here's how the maximum number of partitions for a table space breaks down:

• If the table space was defined without the LARGE or DSSIZE options, and with a NUMPARTS value of 64 or less, the maximum number of partitions is 64.
• If the table space was defined with the LARGE option, it can have up to 4096 partitions.
• If the table space was defined with a DSSIZE specification, the maximum number of partitions can be up to 4096, depending on the DSSIZE value and the table space's page size (information on maximum number of partitions for various combinations of DSSIZE and table space page size can be found in the CREATE TABLESPACE section of the SQL Reference).

So, the above maximum-number-of-partitions values indicate how many partitions a partitioned index can have (again, because you'll have one index partition per table space partition), but how large can each index partition be? You can't specify PIECESIZE for a partitioned index, but DB2 determines the index partition size via the formula referenced previously in this blog entry:

Index partition size = MIN(x, 2^32 /(MIN(4096, 2^32 / (x / y))) * z)

Where:

x is the DSSIZE of the associated table space
y is the page size of the table space
z is the page size of the index

To illustrate: if the table space's DSSIZE is 64G ("x" in the formula) and the table space's page size is 32 KB ("y" value) and the index's page size is 32 KB ("z" value), each index partition can be up to 64 GB in size, and the maximum size of the partitioned index is 128 TB (64 GB times 2048 partitions, which is the maximum number of partitions that a table space with a 64G DSSIZE and a 32 KB page size can have). In fact, 128 TB is as big as a partitioned index can get (an index could also reach this size if the underlying table space had a DSSIZE of 32G and a page size of 32 KB).

Can you use the above index partition-sizing formula when the partitioned table space associated with the index was defined without a DSSIZE specification? I believe that you can. For that purpose, note that a LARGE table space effectively has a DSSIZE of 4G. For a partitioned table space defined without the LARGE option and without a DSSIZE specification, the maximum size of a table space partition is:

• 4G, when NUMPARTS for the table space is between 1 and 16
• 2G, when NUMPARTS for the table space is between 17 and 32
• 1G, when NUMPARTS for the table space is between 33 and 64
• 4G, when NUMPARTS for the table space is between 65 and 254

You should now be able to respond to "How large can this index get to be?" questions. I hope that this information will be useful to you.