Monday, September 26, 2011

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.



17 comments:

  1. Hi Robert,
    If I have an NPI with piecesize of 4G on 60 parts partitioned tablespace with DSSIZE of 64G with pagesize of 4 Kb, what will be the maximum size of NPI ? is it 60*4GB or 2048*4GB?

    ReplyDelete
    Replies
    1. Sorry about the delay in responding.

      I believe in this case the maximum size of the NPI would be 2048x4GB. I'm not aware that NUMPARTS (assuming that this is a range-partitioned table space) affects the number of pieces into which an NPI can grow.

      Robert

      Delete
    2. This comment has been removed by the author.

      Delete
    3. Hi Robert,
      For the above mentioned question
      "
      If I have an NPI with piecesize of 4G on 60 parts partitioned tablespace with DSSIZE of 64G with pagesize of 4 Kb, what will be the maximum size of NPI ? is it 60*4GB or 2048*4GB?
      "
      as per the formula
      MIN(4096, 2^32/(table space partition size/table space page size))
      the maximum number of partition is 256. so the max size of NPI should be 256 * 4GB, right?

      Delete
  2. Hi Robert,

    In your text you say the following:

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

    However, in the IBM-documentation with the CREATE INDEX statement I read that such indexes only can have 32 pieces.

    Can you comment on this.

    ReplyDelete
    Replies
    1. Yes, I can comment on this.

      The specific information in the DB2 for z/OS SQL Reference, in the section on CREATE INDEX, is as follows:

      "Remember that 32 data sets is the limit if the underlying table space is not defined as LARGE or with a DSSIZE parameter and that the limit is 4096 for objects with greater than 254 parts."

      The phrase, "if the underlying table space is not defined as LARGE or with a DSSIZE parameter" means "if the underlying table space is not partitioned," as LARGE and DSSIZE are valid only for partitioned table spaces. So, the limit of 32 data sets applies only to an index on a non-partitioned table space (e.g., a segmented, non-universal table space).

      Robert

      Delete
  3. Hi Robert,
    Thank you for the detailed illustration.
    I am not sure if this is up to date, but in the V10 sql manual http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=%2Fcom.ibm.db2z10.doc.sqlref%2Fsrc%2Ftpc%2Fdb2z_sql_altertablespace.htm

    it says that for a non partitioned index the max number of pieces is 32 even for a partitioned large/dssize>4G tablespace.
    Xiang
    xiang.wang@cba.com.au

    ReplyDelete
  4. Sorry about the delay in responding to your comment, Xiang - I had to do some research to determine whether or not the information you've cited is correct. In fact, it is NOT correct. Yes, the latest version of the SQL Reference (updated in June of 2012 for DB2 V8 and DB2 10, and in May of 2012 for DB2 9) indicates, in a table in the section on CREATE INDEX, the following: if a partitioned table space is defined with a DSSIZE or LARGE specification, or with a NUMPARTS value that is equal to or greater than 65, the maximum number of pieces for an NPI (non-partitioned index) defined on the table in the table space is 32. This is NOT CORRECT. In fact, an NPI defined on such a table space can have as many pieces as the table space can have partitions. That maximum partitions value can be calculated via this formula:

    MIN(4096, 2^32/(table space partition size/table space page size))

    The DB2 for z/OS documentation team is aware of this situation, and I expect that the information will be corrected through a documentation update in the near future.

    ReplyDelete
    Replies
    1. Hi Robert, Thanks for theinformation about the number of datasets. I believe the default index size (4 GB)mentioned is wrong too. Isn't it?

      Delete
    2. Apologies for the much-delayed response, Sevugan.

      I believe that you may be right. The information you cite in the documentation regarding the default PIECE size for a non-partitioned index does indeed conflict with some other information that I have. What I don't know is which is correct - the documentation to which you've referred, or this other information I have (which I included in the blog entry above). I've been trying to get this question resolved, but I have not yet gotten a response to my inquiry. If I get an update I'll provide it in another comment to this blog entry.

      Robert

      Delete
  5. Hi Robert,

    I read over your post, and several others including the IBM manuals, but still cannot find the answer to a specific situation with the size of individual partitions of a partitioned index.

    We have a 10-part tablespace, and have defined a partitioned index on the table (also 10 parts by default). We cannot change the partitioning strategy on the table without significant programming changes.

    The table will grow to have about 12GB of data per partition, which can be addressed via UTS-PBR DSSIZE. For the index we have calculated that each part will be about 6GB is size.

    What we cannot determine from the documentation (or your post) is if each index part can grow to be 6GB+ in size. The DSSIZE and PIECESIZE parameters do not apply to partitioned indexes.

    Any comments on how big individual partitioned index parts can be in our case?

    Thank you,
    Richard

    ReplyDelete
    Replies
    1. Here is an excerpt from this blog entry, Richard:

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

      So, plug in the new DSSIZE value that you choose for the table space, and the table space's page size and the index's page size, and that should tell you how big a partition of a partitioned index on the table can be.

      Robert

      Delete
    2. Thank you very much Robert.

      I must confess I misread the comment "but how large can each index partition be" before the formula.

      Thank you for confirming!

      Cheers,
      Richard

      Delete
  6. Hi Robert
    We have a table with 11 columns and a column with varchar(1800) that there is unique index on it.
    Indexspace for this index has been reached 64G (32 datasets ) but tablespace's size is 40G.
    Would you please tell me why indexspace is grater than tablespace ? and how ?
    - The page size of index is 32K and table is 4K
    - Both of them (table and index) are compress yes
    - index created with NOT PADDED
    Thank you in advance

    ReplyDelete
    Replies
    1. Sorry about the delay in responduing.

      So, is the table essentially replicated in the index? That is, are all of the table's 11 columns in the index?

      Was DSN1COMP executed for the index before it was compressed? If so, what was the estimate of disk space savings achievable through compressing the index? If DSN1COMP was not executed before the index was compressed, I believe it could be executed for a full image copy of the index (if the index is defined with COPY YES).

      For the table space, what is the value in the PAGESAVE column in the SYSTABLEPART catalog table?

      Is the index key continuously ascending (i.e., do new entries always go to the end of the index), or do new entries go into the "middle" of the index?

      Has the index been reorganized recently?

      What is the type of the underlying table space? Traditional segmented? Simple? UTS PBG? UTS PBR? If traditional segmented or simple, is there more than one table in the table space?

      Robert

      Delete
  7. Is this still valid for Db2 11? SQL Reference says,for a NPI of a Partitioned TS:
    "16 TB for 4 KB pages"
    "32 TB for 8 KG pages", etc.
    And 16 TB for tablespaces defined as LARGE.

    ReplyDelete
    Replies
    1. Sorry about the delayed response.

      The information you cited in your comment, on maximum non-partitioned index size, was added to the Db2 10 for z/OS SQL Reference via (I believe) a documentation update to that manual that came out after Db2 10 became generally available. The full text of that informational item (and it's the same in the Db2 11 SQL Reference) is as follows:

      -------------------------

      Maximum size of a non-partitioned index for a partitioned table space:

      For 5-byte EA table spaces:
      16 TB for 4 KB pages
      32 TB for 8 KB pages
      64 TB for 16 KB pages
      128 TB for 32 KB pages
      For table spaces that are defined with LARGE:
      16 TB

      -------------------------

      Here is the link to the Db2 11 information in the IBM Db2 for z/OS Knowledge Center on the Web (look in Table 5 on the linked-to Web page): https://www.ibm.com/support/knowledgecenter/SSEPEK_11.0.0/sqlref/src/tpc/db2z_limits.html

      Robert

      Delete