Friday, August 28, 2020

Db2 12 for z/OS RPN Table Spaces: New Possibilities for Range-Partitioned Tables

Sometimes, I assume that I've blogged about some Db2 for z/OS-related topic when in fact I have not. RPN table spaces is one such topic. In thinking about something to post this month, I briefly considered RPN table spaces and thought, "Nah. I've probably already blogged on that subject, given that it's a big deal." Lo and behold, I haven't blogged about RPN table spaces. I've spoken of them numerous times in presentations over the past few years, but written nothing about them in this blog (save for a brief mention in a 2017 response to a comment associated with an entry on universal table spaces). Time to rectify that situation.

RPN table spaces (I'll explain the acronym in a moment) were introduced with Db2 12 for z/OS (with function level 500 or higher activated). RPN is essentially a new type of universal range-partitioned table space. Before getting into the details, I'll provide a few examples of gripes and concerns that Db2 DBAs have regarding traditional universal partition-by-range (PBR) table spaces:

  • "I have a table in a PBR table space that is really big and getting bigger. I'm starting to sweat about only being able to put 16 TB of data in the table. Yeah, I could get up to 128 TB of data in the table if I went with a page size of 32K, but we had good reason to go with 4K pages for this table space."
  • "I don't like the fact that my choice for DSSIZE for a PBR table space impacts the maximum number of partitions for the table space. 256G is the DSSIZE we want for a particular PBR table space, but the limit on partitions for the table space would then be 512 - and that only if we go with 32K for the page size (if we go with 4K for the page size and 256G for DSSIZE, I can only have 64 partitions)."
  • "Ugh. I have a big table with 1000 partitions in a PBR table space. DSSIZE is 8G, and that has been just fine for 999 of the partitions. One partition is about to hit the 8G size limit. I'm going to have to go to 16G for DSSIZE for THE WHOLE TABLE SPACE, even though ONLY ONE PARTITION NEEDS THE LARGER DSSIZE VALUE."

The RPN table space feature of Db2 12 addresses all of those issues, and more. It does that by way of a very important change versus traditional PBR table spaces. This change pertains to the numbering of pages in a range-partitioned table space. In a traditional PBR table space, every page of the table space has a unique number. In an RPN table space, we number the pages in partition 1, and then for partition 2 we start over again with regard to numbering pages. For partition 3, same thing - start over again with page numbering. Same for partition 4, partition 5, and so on. What this means: if I have an RPN table space with 1000 partitions, the first page page in each and every partition has the same page number (referring to the number stored in the data page itself). You read that right: in the table space, there will be 1000 first-in-the-partition pages that all have the same page number. There will also be 1000 second-in-the-partition pages that all have the same page number (assuming that each partition has at least 2 pages). Does this sound like something that would lead to chaos? In fact there is no chaos because in an RPN table space it is still very much possible to uniquely identify a page. The twist, versus a traditional PBR table space, is that in an RPN table space a page is uniquely identified by the combination of its page number and its partition number. That page numbering scheme is called relative page numbering, and that gives us the acronym RPN (the type of page numbering used for a traditional PBR table space is called absolute page numbering).

OK, so what can I do with an RPN table space that I can't do with a traditional PBR table space? Plenty:

  • You can put up to 4 petabytes (that's 4096 terabytes) of data into one table in an RPN table space, and that's true for any page size you choose for the table space.
  • Row-capacity-wise, you can put up to 280 trillion rows (yes, that's "trillion," with a "t") into one table in an RPN table space, if you use 4K pages for the table space (if you go with 32K pages, you can put "only" 35 trillion rows into a table in an RPN table space).
  • With an RPN table space, you can have up to 4096 partitions of any allowable size - in other words, the limit on the number of table space partitions does not go down as DSSIZE goes up; AND, the maximum DSSIZE is 1024G (versus 256G for a traditional PBR table space); AND, DSSIZE for an RPN table space can be nG, with "n" being any integer between 1 and 1024 (in other words, "n" no longer has to be a power of 2, as is the case for a traditional PBR table space - if you want to go with a DSSIZE of, for example, 131G for an RPN table space, go for it).
  • Different partitions of one RPN table space can have different DSSIZE specifications. Consider the scenario I described previously, in which a DSSIZE of 8G is fine for all but one partition in a 1000-partition table space. Let's say the one partition about to hit the 8G size limit is partition 327, and a DBA wants to take DSSIZE for that partition to 17G. With an RPN table space (which makes 17G a valid value for DSSIZE, as noted above), the DBA alters only partition 327 to have DSSIZE 17G; and, get this: that ALTER is an immediate change - partition 327 can immediately get larger than its former 8G DSSIZE, without even a REORG of the partition required to put the DSSIZE change into effect. [Note: when an ALTER TABLESPACE statement is issued for an RPN table space, and that ALTER includes a partition-level DSSIZE specification, the DSSIZE value specified must be equal to or greater than the current DSSIZE value for the partition in question. If a smaller DSSIZE value is to be specified, that has to be done at the table space level, and then it's a pending change and an online REORG of the entire table space will be required to put the smaller DSSIZE value in effect for all of the table space's partitions.]
  • The DSSIZE flexibility provided by an RPN table space extends to partitioned indexes defined on the table in the table space: DSSIZE for partitions of such an index can be nG, with "n" being any integer between 1 and 1024, and different DSSIZE values can be used for different partitions of an index. Speaking of indexes, note that an index on a table in an RPN table space will be slightly larger than an index defined on the same table in a traditional PBR table space. That is so because the size of a RID goes from 5 bytes to 7 bytes when data is in an RPN table space versus a traditional PBR table space.

That's some good stuff, eh? Wanna know how you can have RPN table spaces in your Db2 12 system? I'll tell you. First, for a new table space, in the CREATE TABLESPACE statement you can include the option PAGENUM RELATIVE. You can also create an RPN table space without even specifying PAGENUM RELATIVE. How? By setting the new (with Db2 12) ZPARM parameter PAGESET_PAGENUM to RELATIVE. Doing that will make RPN the default for a new universal PBR table space created in the system (whether PAGESET_PAGENUM is set to RELATIVE or to its default value of ABSOLUTE, that specification can be overridden at the individual table space level via use of the PAGENUM option of CREATE TABLESPACE).

How about an existing universal PBR table space of the traditional variety? Can it be changed to an RPN table space? Yep - just ALTER the table space with PAGENUM RELATIVE, and then do an online REORG of the table space to materialize the change (yes, you have to REORG the table space in its entirety to get to RPN, but the good news is that once the conversion is done you should have fewer situations that would necessitate a REORG of the whole table space - that's part of the RPN value proposition).

Can a "classic" range-partitioned table space be converted to RPN ("classic" referring to a table space that is not universal and is associated with a table that uses table-controlled partitioning versus index-controlled partitioning)? Yes. First, you ALTER the table space with a SEGSIZE specification to indicate that you want to convert it from non-universal to universal PBR (a value of 64 for SEGSIZE is generally appropriate). After that, issue a second ALTER for the table space, this time specifying PAGENUM RELATIVE. After both of those ALTER TABLESPACE statements have been executed, a single online REORG will make the table space both universal and RPN.

If I were administering a Db2 12 system, I think I'd want to make extensive use of relative page numbering for my range-partitioned table spaces, for the DSSIZE flexibility (and the ability to immediately increase a partition's maximum size) and for enhanced scalability. I encourage you to put this new Db2 feature to work for the benefit of your organization.

39 comments:

  1. Hi Rob,
    I'm very much interested to learn on the utlities in db2 z/os.I know its vast topic,but i think we have so many articles/pgs/posts for other topics in db2.but its really rare that everyone of us would have come across some detailed understanding on utilites.I request you to share your knowledge on db2 utilites,which will be very useful.

    ReplyDelete
    Replies
    1. That's certainly something I could do. As you noted, Db2 utilities is a broad topic. Are there particular aspects of the IBM Db2 utilities and/or usage of same that you'd like to see covered in a blog entry?

      Robert

      Delete
    2. As far as i know,i think it will be useful to cover-what each phases really do/how it really works,parms that is really required/will be useful to know,new features,things to be noted/take care when job abends in each phase

      Delete
    3. That would be too much to cover in this blog. When I write an entry that has to do with Db2 for z/OS utilities, I am typically addressing a particular aspect of a particular utility. Broader-scope information on IBM Db2 for z/OS utilities can be found online in the IBM Db2 for z/OS Knowledge Center (https://www.ibm.com/support/knowledgecenter/SSEPEK_12.0.0/ugref/src/tpc/db2z_introutilities.html) and in IBM "redbooks" such as "Db2 for z/OS Utilities in Practice" (download-able from http://www.redbooks.ibm.com/abstracts/redp5503.html?Open).

      Robert

      Delete
  2. The above estimation "Row-capacity-wise, you can put up to 280 trillion rows" is based on a TS without an NPI. An NPI with a unique key would limit the total number of rows in the TS to less than 1 trillion for a 4K page NPI and less than 8 trillion for a 32k page NPI.
    The NPI is really due for a redesign.

    ReplyDelete
    Replies
    1. True statement, as the size of an NPI is limited to 16 TB for an index with 4 KB-sized pages, and 128 TB for an index with 32 KB-sized pages. It will be interesting to see if the NPI design gets an overhaul in the future to expand capacity.

      Robert

      Delete
  3. After altering Tablespace to PAGENUM RELATIVE to Defer alter materialized by Part level inline Reorgs . Do all the subsequent reorg tablespace has to be part level Inline copy Reorg ?

    ReplyDelete
    Replies
    1. Not sure that I understand your question. If you alter a range-partitioned table space with PAGENUM RELATIVE, only a REORG of the entire table space will materialize that change - a partition-level REORG cannot be used to effect a change to relative page numbering from absolute page numbering.

      Robert

      Delete
  4. Hi Rob,I'm confused with difference between partitioned index & DPSI.By the definition & index statement both looks same to me.
    Both :
    1)Defined with PARTITIONED keyword
    2)has as many partitions as the number of partitions in the table space
    I'm finding for an example index statement to really know how this differs & used.
    Can you help me in understanding this

    ReplyDelete
    Replies
    1. A partitioned index defined on a table in a table space that uses table-controlled partitioning (either a universal partition-by-range table space or a "classic" partitioned table space of the type introduced with Db2 V8 for z/OS) will be either a partitioning partitioned index or a data-partitioned secondary index. If the index key is equal to, or begins with, the column(s) of the underlying table's partitioning key, it is a partitioning partitioned index; otherwise, is a data-partitioned secondary index. For example, if a table is partitioned on (C1, C2), a partitioned index on (C1, C2) is partitioning. So is a partitioned index on (C1, C2, C3). A partitioned index on (C4) is a DPSI. So is a partitioned index on (C2, C1), the key of which contains the columns of the table's partitioning key, but in the wrong order.

      A good explanation of partitioned indexes - both the partitioning and the data-partitioned secondary kind - can be found in sections 3.7 and 3.8 of the IBM redbook titled, "DB2 UDB for z/OS Version 8: Everything You Ever Wanted to Know, ... and More." That redbook can be downloaded from the Web page at http://www.redbooks.ibm.com/abstracts/sg246079.html?Open.

      Note: the above-referenced redbook states that a DPSI cannot be defined as UNIQUE. Since Db2 9 for z/OS, a DPSI can be defined as UNIQUE if the columns of its key are a super-set of the columns of the underlying table's partitioning key.

      Robert

      Delete
    2. Wow!!!that clarifies it .thankyou Rob

      Delete
    3. I believe partitioned partitioning index & partitioning index are different.partitioning index-must have same columns in same order as that of partitioning key defined in table.

      Delete
    4. Sorry typo.I meant to be partitioning partitioned index & partitioning index

      Delete
    5. A partitioned index is one that id defined with a PARTITIONED specification. A partitioning index is one whose key either is the underlying table's partitioning key, or whose key begins with the underlying table's partitioning key; thus, the only difference between a partitioned partitioning index and a non-partitioned partitioning index is the PARTITIONED specification. Frankly, I do not see why a partitioning index would not also be defined as PARTITIONED.

      Robert

      Delete
  5. Hi Robert
    good topics are covered.
    I request to have db2 recovery topic on table space level which is having much deletes/updates happened after a full image copy

    ReplyDelete
    Replies
    1. Sorry about the delay in responding.

      Are you requesting that I post an entry to this blog on the topic of recovering a table space? Db2 for z/OS data backup and recovery is covered quite thoroughly in the product documentation - see https://www.ibm.com/docs/en/db2-for-zos/12?topic=recovery-backing-up-recovering-your-data.

      More specific information on table space recovery can be found on this page in the product documentation: https://www.ibm.com/docs/en/db2-for-zos/12?topic=data-preparation-recovery-scenario.

      Robert

      Delete
  6. Hi Rob,
    For changing the Existing PBR Tablespaces its three step approach
    1. ALTER TABLE SPACE with PAGENUM RELATIVE attribute
    2.Reorg
    3.Alter DSSIZE for required partition alone WITHOUT reorg.

    ReplyDelete
    Replies
    1. To go from an existing PBR table space that uses absolute page numbering to an RPN table space, two actions are required: 1) ALTER TABLESPACE with PAGENUM RELATIVE, and 2) and online REORG to materialize the pending change.

      After the table space has been changed to use RPN, an individual partition's DSSIZE can be enlarged with an ALTER, and that will be an immediate change - the partition can grow beyond its former DSSIZE with no need to REORG the partition.

      Robert

      Delete
  7. Thanks Rob... Iniitial misunderstanding for us was ,PAGENUM RELATIVE changes will implicitly allow the growth of the tablespace upto 1 TB. Then realized explicit DSSIZE changes are required.

    We considered REBIND also post the above changes .

    ReplyDelete
    Replies
    1. Yes, for an RPN table space an enlargement of a partition's DSSIZE value must be done explicitly via ALTER TABLESPACE with ALTER PARTITION. Max DSSIZE value for an RPN table space is 1024G (equal to 1 TB). Max size for an RPN table space as a whole is 4096 TB.

      Yes, when an online REORG is executed to materialize a change to RPN for a table space, dependent packages will be invalidated at the conclusion of the REORG.

      Robert

      Delete
  8. Thanks a lot Robert for sharing more Technical information. That really helped a lot !!

    ReplyDelete
    Replies
    1. I'm glad that the information has been helpful for you.

      Robert

      Delete
  9. Thanks Robert,
    I have one question.
    In the absolute page numbering scheme , can you please elaborate how big is the RID, is it 5 bytes(4bytes for the page number and 1 byte for the row number with in the page) ?
    Thanks for ur time.

    ReplyDelete
    Replies
    1. Yes, when absolute page numbering is in effect, a RID value will be a 5-byte field. The first 4 bytes are used for the page number, and the last byte holds the page ID map entry number associated with the particular row.

      Robert

      Delete
  10. Is DSSIZE is an immediate change for LOB PBR RPN ?

    ReplyDelete
    Replies
    1. There is no such thing as a LOB PBR RPN table space. There are LOB table spaces, and there are PBR RPN table spaces. A PBR RPN table space can have a LOB column (or columns), and if it does then there will be a LOB table space for each LOB column of each partition of the PBR RPN table space, but the PBR RPN table space and its associated LOB table spaces are physically distinct and different database objects. A DSSIZE change will be an immediate change only if the target table space is PBR RPN and the new DSSIZE is larger than the previous DSSIZE. For a LOB table space a DSSIZE change will be a pending change, materialized by way of a subsequent online REORG of the LOB table space.

      Robert

      Delete
    2. Thank you for your prompt reply. I tried altering (increasing) DSSIZE at the partition level of the base tablespace (type=R) of a LOB Table but it would not. Is that an anomaly or maybe I am doing something wrong. It lets me do it as pending change at the base tablespace level.

      Delete
    3. You cannot increase the DSSIZE of a LOB table space through an alter of the associated base table space - you have to directly alter the LOB table space in question. And yes, that ALTER of the LOB table space will be a pending change, and will be materialized (i.e., put into effect) through an online REORG of the LOB table space following execution of the DSSIZE-changing ALTER of the LOB table space.

      Delete
  11. Hi Robert. To convert to RPN we have to reorg the entire tablespace at once, and there is an image copy created for each partition. Many of our tablespaces have 254 partitions. We don't have 254 tape drives, and 254 image copy files on DASD would be a lot of DASD (and how long to keep those for recovery)... I see the new parms for the REORG TABLESPACE utility called ICLIMIT_DASD and ICLIMIT_TAPE. Would those parms help in this situation? Any other hints/tips you can suggest?
    Thanks in advance,
    Steve

    ReplyDelete
    Replies
    1. Hello, Steve. Apologies for the delay in responding.

      The situation of concern that you've brought up was addressed by Db2 for z/OS APAR PI75518 (see https://www.ibm.com/support/pages/apar/PI75518). This APAR removed the requirement that each partition of a table space being converted to RPN be image copied to its own data set, and introduced two new ZPARMs (and associated REORG keywords that can be used to override the ZPARM-specified values) that serve to limit the number of disk or tape image copy data sets that REORG can allocate.

      The fix for APAR PI75518 came out around the end of June in 2021.

      Robert

      Delete
    2. Robert,
      Thanks for the quick reply and the great information. I had not seen that APAR but that is great news and appears to alleviate my concerns.

      Thank you!
      Steve

      Delete
  12. Hi. We have several candidates for conversion to RPN. I was wondering what the gotchas are. For example, do you know how much additional space the new RPN tablespace would use initially? Is there any difference in the amount of logging generated by the RPN? Is it worth it to convert smaller PBRs to RPN as maybe eventually all PBR tablespaces will be RPN by default in the future? Any other gotchas that we should be aware of before embarking on this journey?

    ReplyDelete
    Replies
    1. There really aren't any "gotchas" to speak of with regard to going from absolute to relative page numbering for a universal range-partitioned table space. In a Db2 13 for z/OS environment, the default value for the PAGESET_PAGENUM parameter in ZPARM goes from ABSOLUTE to RELATIVE - that makes RPN the default page numbering scheme for new PBR table spaces in a Db2 13 system. Additionally, when you take advantage of online conversion of a PBG table space to PBR in a Db2 13 system (an enhancement available when function level V13R1M500 is activated), the new PBR table space will use relative page numbering.

      I would not expect a difference in table space size as a result of going from absolute to relative page numbering. Indexes on a PBR table space would get a little larger as a result of changing to RPN for the table space, as a result of row IDs (RIDs) going to 7 bytes apiece from 5 bytes apiece.

      Robert

      Delete
    2. Hi Robert,
      we are planning to go from a huge PBR table into a RPN table, by altering to relative page numbering and do a full reorg. I understood that the earlier ImageCopies are invalidated once the Reorg has finished, but we can create an online IC during REORG. Are there any risks in doing it by this sequence of actions, whereas I am a bit afraid of having no fall-back scenario ..

      Delete
    3. Actually, it's an "inline" - not an "online" - image copy that is taken when you run an online REORG. I do not see taking an inline image copy of a table space as being a risky thing. You HAVE to take an inline image copy when REORG TABLESPACE is executed with SHRLEVEL CHANGE or SHRLEVEL REFERENCE.

      Robert

      Delete
    4. Robert, thanks for your reply. The risk is indeed not in taking the inline IC, but the fact that all previous IC's are invalidated because of the change to RPN. The actual reorg will take about 3-4 days to complete for the whole table.

      Delete
    5. Understood. Keep in mind that if an online REORG fails for some reason, the "original" objects (i.e., the target table space and associated objects such as indexes) are still available for use, because they have not been affected by the online REORG that failed to complete (that is one of the great things about online REORG, versus execution of REORG with SHRLEVEL NONE). In that case, older image copies (associated with absolute page numbering for the table space) are of course still usable. The new image copy (the inline image copy generated during the REORG that is executed to implement relative page numbering) will only be needed if the online REORG completes successfully, and those image copies (assuming you have REORG generate both a primary and a backup inline image copy) will be available following successful execution of REORG, because they are generated well before the final phase of REORG (the SWITCH phase).

      Robert

      Delete