Friday, June 13, 2014

DB2 for z/OS: Getting to Universal Table Spaces

Often, there is a bit of a time lag between the introduction of a DB2 for z/OS feature and the widespread adoption of the new technology. Take universal table spaces, for example. These were introduced with DB2 9 for z/OS (almost 7 years ago), but some organizations are only now beginning to convert non-universal table spaces to the universal variety. In this blog post I want to go over the incentives for undertaking a conversion to universal table spaces, highlight the important way in which DB2 10 eased the conversion process, and raise some matters one should consider in the course of effecting table space conversions.

Why universal?

As I see it, their are two main reasons to convert non-universal table spaces to the universal kind. First, it's the only way in which you can leverage the benefits of partition-by-growth table spaces, one of two varieties of universal table space (the other being partition-by-range). A partition-by-growth (PBG) table space -- as the name implies -- is one that is partitioned as needed to accommodate a table's growth; so, if in creating a table space (or altering an existing table space) one specifies a DSSIZE (data set size) of 2G (2 gigabytes) then upon the table reaching 2 GB in size DB2 will add a second partition to the table space. If that partition fills up (i.e, when it reaches 2 GB in size), a third partition will be added to the table space, and so on up to the maximum number of partitions specified for the table space (that being MAXPARTITIONS, an alterable value).

What's good about this? Well, first and foremost it eliminates the 64 GB size limit that previously existed for table spaces that are not range-partitioned -- a PBG table space, like a range-partitioned table space, can reach a size of 128 TB (and that's for the non-LOB data in the table -- with LOB data the data capacity of a table space can far exceed 128 TB). Of course, you might think of a table in your DB2 for z/OS environment that would never approach 64 GB in size, and wonder, "Why should I convert THAT table's table space to universal PBG?" That would be a good question, if size were the only incentive for converting a non-universal table space to universal. There are, in fact, quite a few non-size-related reasons for getting these conversions done. I'll get to these presently, but first I want to clear up a misconception. Some folks think that PBG table spaces are not appropriate for small tables because of the "P" (for partition) in PBG: we've historically thought of partitioning as a means of getting more than 64 GB of data in a table, and so we equate "partition" with "big" and write off PBG for smaller tables. Time to change that thinking. Is PBG a good choice for a table that will never hold more than, say, 20 KB of data (this could be a reference or code table), even with 1G being the smallest allowable DSSIZE value? Sure it is. Will that little table's table space be 1 GB in size, with 20 KB of the space used and the rest wasted? Of course not. The table space's physical size will be determined by its PRIQTY and SECQTY specifications (primary and secondary space allocation, respectively). If those specifications are chosen appropriately, the table with 20 KB of data will occupy 20 KB of disk space. The 1G DSSIZE specification means that IF the table space size reaches 1 GB then DB2 will add another partition to the table space. If the table space size never reaches 1 GB then the table space will stay at one partition. Got it?

OK, on to the non-size related incentives for going universal. A growing list of DB2 features can ONLY be used in conjunction with universal table spaces. These include (and I've indicated the DB2 release through which these features were introduced):
It is likely that future releases of DB2 for z/OS will introduce more features with a universal table space requirement.

Getting there got a lot easier starting with DB2 10

In my universal table space incentive list above I mentioned pending DDL. This is a capability that became available with DB2 10 running in new-function mode. Big picture-wise, what pending DDL made possible was the non-disruptive alteration of a number of aspects of the definition of a table space or table or index. Want to change the SEGSIZE of a table space? The page size of an index? The DSSIZE (data set size) of a table space? No problem. Thanks to pending DDL, you just issue the appropriate ALTER statement and then materialize the change by way of an online REORG. Between the ALTER and the online REORG, is application access to the target object compromised? No. The table space or index affected is placed in the new (with DB2 10) and non-restrictive AREOR state (which basically means that a pending DDL change has been issued for the object but has not yet been materialized via online REORG).

So, what does this have to do with conversion of non-universal to universal table spaces? Well, it so happens that the only pending DDL change possible for a non-universal table space is a change that would, when materialized, result in the non-universal table space becoming universal. For a simple or segmented table space containing a single table, that change is an ALTER TABLESPACE that adds a MAXPARTITIONS specification to the object's definition. Issue such an ALTER, and after a follow-on online REORG the table space will be a universal partition-by-growth (PBG) table space. For a table-controlled partitioned table space, the change is an ALTER TABLESPACE that adds a SEGSIZE specification to the object's definition. Run an online REORG for the object after the ALTER, and voila -- you have a universal partition-by-range (PBR) table space. Easy.

Some considerations

As you plan for the conversion of your non-universal table spaces to the universal variety, there are things to which you should give some thought:
  • The non-disruptive process described above for converting simple and segmented table spaces to PBG universal table spaces (ALTER with MAXPARTITIONS, followed by online REORG) is available, as I mentioned, only for single-table simple and segmented table spaces. For a simple or segmented table space containing multiple tables, you'd have to either go the unload/drop/create/re-load route (with one PBG universal table space created for each table in the multi-table simple or segmented table space), or wait and see if a future release of DB2 provides a non-disruptive universal table space conversion process for multi-table simple and segmented table spaces (this is a known requirement).
  • The non-disruptive process for converting traditional range-partitioned table spaces to PBR universal table spaces (ALTER with SEGSIZE, followed by online REORG) is available, as I mentioned, only for table-controlled partitioned table spaces. For an index-controlled partitioned table space, you'll first need to accomplish the conversion to table-controlled partitioning. That's most easily done via issuance of an ALTER INDEX statement with NOT CLUSTER for an index-controlled partitioned table space's partitioning index (as described in a blog post I wrote a couple of years ago).
  • Materialization of the change to universal table space from non-universal (via online REORG after the appropriate ALTER statement) will invalidate packages that depend on a given table space. These packages will be automatically rebound (by default) when the associated programs are next executed, or you can rebind them explicitly. By default, plan management should be active on your system (i.e., the value of the PLANMGMT parameter in ZPARM should be EXTENDED or BASIC). That being the case, on the off chance that a package rebind operation leads to performance degradation (performance will typically be the same or better following a package rebind), you can very quickly switch back to the previous instance of a package via a REBIND PACKAGE command with the SWITCH(PREVIOUS) option.

There you have it. If you've not already started the process of converting your non-universal table spaces to universal PBG or PBR table spaces, it's time to get that effort going. Your organization will benefit from your actions.

    29 comments:

    1. Hi Robert,
      Is there a way to identify all the dependent packages that are to be Rebinded for a classic partitioned tablespace which is going to be converted into a UTS ?

      ReplyDelete
      Replies
      1. Yes, this can be easily done. Just issue a SELECT against the catalog table SYSIBM.SYSPACKDEP. For example, if you were converting table space TS123 in database DB456 from classic partitioned to range-partitioned universal via ALTER TABLESPACE (with SEGSIZE) and online REORG (in a DB2 10 NFM or later environment), you could identify dependent packages ahead of time with a query like this one:

        SELECT DCOLLID, DNAME, DCONTOKEN
        FROM SYSIBM.SYSPACKDEP
        WHERE BNAME = 'TS123'
        AND BQUALIFIER = 'DB456'
        AND BTYPE = 'R'
        ORDER BY 1,2,3;

        Robert

        Delete
    2. Well done, Robert. You made it easy to understand.

      ReplyDelete
      Replies
      1. Thanks. Glad that the blog entry was helpful.

        Robert

        Delete
    3. Hi Robert,

      We are planning to convert partition table space (say XYZ123) to Universal Tablespace (with Partition by growt).
      The partition Tablespace XYZ123 has the partition table created in it.Now our task is to convert the partition table to non partition table in Universal Tablespace.

      Our approach is unload/drop/create/re-load route. Could you please let me know if you have better way of doing above task.

      Thanks
      Khalinder

      ReplyDelete
      Replies
      1. Sorry about the delayed response. Two questions:

        1) Does the table space in question (XYZ123) currently use index-controlled partitioning or table-controlled partitioning?

        2) Why do you want to convert this range-partitioned table space to a partition-by-growth table space? Why not just convert it to a universal partition-by-range table space? That could be done with an ALTER and an online REORG.

        Robert

        Delete
      2. Thanks for the favor.

        XYZ123 tablespace is currently use table controlled partition.

        The data is not evenly distributed, 99.99 percent data is stored in single partition. So planning to convert to universal tablespace partition.by.growth.

        Delete
      3. If 99.9% of the data is in one of the table's partitions, is that partition the "last" partition of a table partitioned by a continuously-ascending value such as date? If so (and assuming that you have not yet reached the maximum number of partitions for a table, which is 4096 unless DSSIZE is quire large), could that situation have been avoided by periodically (e.g., every week or month or whatever) issuing an ALTER TABLE statement with the ADD PARTITION clause?

        In any case, to get from non-universal range-partitioned to universal partition-by-growth, I believe that you will in fact need to go the unload/drop/re-create/re-load route. To minimize the associated period of data unavailability, some organizations do the following: 1) unload from range-partitioned table X and load into table Y in a partition-by-growth table space; 2) assuming that table X was being updated while table Y was being loaded, use a DB2 log analysis tool to get the data-changing SQL that updated table X while table Y was being loaded, and apply those SQL statements to table Y to bring it into sync with table X (if table X was open for update during that sync-up operation, you may need to perform an additional sync-up -- in essence, you're duplicating what online REORG with SHRLEVEL CHANGE does); 3) when tables X and Y are finally in sync, stop all access to table X, change its name to z, and change the name of table Y to X (that renaming will invalidate packages dependent on table X, so you'll need to rebind those).

        A more involved process, yes, but one that has been used to keep data unavailability to a minimum, when that is of overriding importance to an organization.

        Robert

        Delete
      4. Hi Robert,
        I am not clear about PBR. If I have partitions defined as PBR and 64G DSIZE, but when the particular ranged partition is almost filling 64G size, should I issue alter DSIZE statement to before it reaches 64G or it will extent automatically since it SMS managed data set. If I have to issue ALTER statement, can I alter the DSIZE when the dataset is in use?

        It is great help if you can clarify this for me?

        Thanks a lot.

        Delete
      5. What DB2 for z/OS version are we talking about here, and is it in conversion or new-function mode? How many partitions does the table have? What is the page size for the table space? What is the PIECESIZE for any non-partitioned secondary indexes defined on the table space?

        Robert

        Delete
      6. Hi Robert,

        Sorry for not clear enough for my question. It is DB2 z/OS v10 new function mode. There are 3 partitions which were partitioned by range. We are still in the 1st partition, then ran into 'partition is full' error. Page size is 32K. DSSIZE 64G Segment size 32. There is no non-partitioned secondary indexes.

        Thanks, Robert.


        Delete
      7. Sorry about the delayed response.

        You can change the DSSIZE to a larger value (e.g., 128G) via an ALTER TABLESPACE statement, and in a DB2 10 NFM system this will not impact data availability. The change will be treated as a pending DDL change (pending DDL is a feature that was introduced with DB2 10 for z/OS). That means the table space will be placed in AREOR status (which does not impact data accessibility) and information about the change will be placed in the new (with DB2 10 NFM) SYSPENDINGDDL table in the DB2 catalog. A subsequent online REORG of the table space (the entire table space) will apply the pending DDL change (the increase in DSSIZE).

        Longer term, I would recommend changing the table space's partition limit key values, and adding partitions (three partitions is an exceedingly small number for a range-partitioned table space). With DB2 11 in new-function mode, alteration of partition limit key values is treated as a pending DDL change, and so can be accomplished in an online fashion (issue ALTER, then run online REORG to materialize the change). I'd also give consideration to adding partitions to the table.

        Robert

        Delete
      8. Hi Robert,

        No problem. I figured out how to handle it before you got the chance to answer the question. I issued an ALTER TABLESPACE xxx DSSIZE 128G and it resolved the issue of 64 G limitation and without impacting data availability. I did reorg to remove AREOR status afterwards. Additional partitions were added. Thanks very much for taking time to answer the question. You are always very helpful.


        Delete
    4. Thanks Robert for the wonderful article.

      ReplyDelete
    5. Hello Robert,
      Thanks for the great work.

      Question : Can I convert segmented TS to UTS PBR, than dropping/ recreating the tablespaces ?

      ReplyDelete
      Replies
      1. No. A table-controlled partitioned table space can be converted in a non-disruptive fashion to universal partition-by-range, but this is not true for a traditional segmented table space (I say "traditional" because universal table spaces are segmented as well as partitioned). You can convert a traditional segmented table space to universal partition-by-growth in a non-disruptive way (starting with DB2 10 in new-function mode), but if you want to make the traditional segmented table space a partition-by-range universal table space, you will have to go the unload/drop/re-create/re-load route.

        Robert

        Delete
    6. Hi Robert,
      The blog's content is excellent for learners !!!
      I have converted a lot of table spaces to UTS PBG and PBR. But recently, I found an issue during the conversion and was not able to figure it out.
      Could you help me?

      I have to convert a Index controlled Classic partitioned table space to UTS PBR. For this, have altered a tablespace by altering its index from NOT CLUSTER, COMMIT and then back to CLUSTER. Later, I saw the last partition of the table space went into REORP status. The same table space remained in RW when I performed the same change in another subsystem.

      What could be the possible cause for this?

      ReplyDelete
      Replies
      1. Sorry about the delayed response.

        I recommend contacting the IBM Support Center regarding this issue. I would not have expected this result. It is possible that in the index-controlled partitioned table space, some rows in the last partition had a partitioning key value greater than the partition's limit key value (in some cases, the limit key value of an index-controlled partitioned table space's last partition is not strictly enforced -- a row with a partitioning key value greater than the limit key value of the last partition is simply placed in the last partition, versus being rejected in insert); however, my expectation would be that on being converted to table-controlled partitioning, the table space's last partition would have its limit key value changed by DB2 to its highest possible value, so that any row that had technically not belonged in that partition (due to having a partitioning key value larger than the last partition's limit key value) would be OK in the new table-controlled partitioned table space (the limit key value is strictly enforced for inserts into a table-controlled partitioned table space). If that adjustment of the last partition's limit key value to its highest possible value occurred when you converted the table space to use table-controlled partitioning, the partition shouldn't have gone into REORP status (i.e., "hard" REORG-pending status). Similarly, if the index controlled partitioned table space did not have any rows in the last partition with a partitioning key value larger than the partition's limit key value, the last partition should not have gone into REORP status.

        Yours is a strange result, and I can't tell you why it occurred. Perhaps the IBM Support Center can figure out what happened.

        Robert

        Delete
    7. Very well written and really helpful. Thanks Robert.

      ReplyDelete
    8. Hi Robert, really enjoy your blogs. However can you please explain how you can switch to a previous version of a package when that version has been marked as Invalid by the reorg to materialize the UTS change.

      I've tried this and as expected it doesn't work, it fails with a DSNT269I message - Previous version of the package is invalid.

      Thanks, Mark

      ReplyDelete
      Replies
      1. Somehow, I overlooked this comment. Thus the two-months-late response. Very sorry about that. Anyway, REBIND SWITCH is not a solution for dealing with an invalidated package. The intent of REBIND SWITCH (and of plan management, through which the previous instance of a package is retained when a new instance is generated via REBIND) is to provide a means of quickly going back to the previous instance of a package when a new instance, generated via REBIND, delivers less-good performance because of an access path change. When an online REORG is run to materialize a pending DDL change, packages dependent on the object affected by the pending DDL change are invalidated and the only way froward is a rebind of the package (not a REBIND SWITCH) -- either an explicit rebind or the automatic rebind that will otherwise occur when the invalidated package is next executed.

        Robert

        Delete
    9. Hi Robert,
      What makes a DBA prefer a PBG over a PBR ?
      Is it true that if mainly working at partition level (i.e. Utilities like REORG PART) then PBR tablespaces are preferable ?
      Thanks!

      ReplyDelete
      Replies
      1. I wrote a blog entry on this topic a few months ago: http://robertsdb2blog.blogspot.com/2015/05/for-large-db2-for-zos-table-should-you.html. Check it out and see if it answers your question.

        Robert

        Delete
    10. what is the way to covert UTS PBG and PBR and vice versa ?

      ReplyDelete
      Replies
      1. Apologies for the delayed response.

        The way to get from UTS PBG to PBR and vice versa is unload/DROP/re-create/re-load. It cannot be accomplished via ALTER and REORG.

        What some organizations do to mitigate the data availability impact of a change requiring DROP and re-create (suppose you want to change table TAB_A from PBG to PBR):

        1) Create a PBR table, TAB_B, that is logically equivalent to TAB_A (same columns, same order, etc.), and create indexes as needed on TAB_B.
        2) Unload from TAB_A, and load TAB_B.
        3) Use a log analysis tool (available from IBM and other vendors) to extract from the DB2 log all changes made to TAB_A since it was unloaded to populate TAB_B, and "replay" those changes for TAB_B, to bring TAB_B data up to currency with respect to TAB_A (TAB_A must be in read-only mode at this time, so that data in TAB_A won't be changed while data in TAB_B is being brought to currency).
        4) Rename TAB_A to TAB_X, and rename TAB_B to TAB_A.
        5) Rebind packages made invalid by the above RENAME TABLE action.

        Robert

        Delete
    11. derek.flux@travelport.comJanuary 25, 2017 at 12:33 PM

      Robert, I am converting a tablespace from LARGE (DSSIZE 4 GB) to DSSIZE 16 GB. The tablespace is a universal partitioned by range one (after converting the index based partitioning table to table partitioning and SEGSIZE 64 instead of 0.

      I have 64 partitions in the table and the total tablespace size is over 200 GB. I thought I could split up the REORG jobs to reorg by multiple partitions in groups (say 8 parts per sequential job).

      That would make the amount of resources needed for sorts for each REORG less than one big REORG. However, IBM stated that since the DSSIZE is specified at the TS level I must do one REORG job for the entire tablespace.

      I would need to calculate space and determine if we have those resources. I was hoping I could do the REORGs by partition. Is what IBM stated correct?

      The REORGs are done with SHRLEVEL REFERENCE and no updates will occur during the maintenance. The application can still read data while reorg'ing.

      Thanks!

      ReplyDelete
      Replies
      1. Hello, Derek.

        As an IBMer myself, I can state definitively that, yes, what IBM told you is correct: to change the table space's DSSIZE, you will have to issue the ALTER TABLESPACE statement (to specify the new DSSIZE) and then execute an online REORG of the entire table space to put the change into effect.

        That changes with DB2 12 for z/OS, which became generally available a few months ago. In a DB2 12 environment, range-partitioned table spaces (universal PBR or "classic" partitioned - the latter term referring to a non-universal table space for which table-controlled partitioning is in effect) can utilize a new physical database design option called relative page numbering, or RPN. An RPN range-partitioned table space can be created, or an existing range-partitioned table space can be changed to RPN via an ALTER TABLESPACE with PAGENUM RELATIVE, followed by an online REORG of the entire table space. When a range-partitioned table space uses relative page numbering, new and useful options concerning DSSIZE are available. For one thing, DSSIZE for an RPN table space can be specified at the partition level. And, different partitions of a given RPN table space can have different DSSIZE specifications. And, allowable DSSIZE specifications no longer depend on the table space's page size and the number of the partitions associated with the table space. And, a DSSIZE change no longer requires an online REORG to become effective - it is an immediate change (unless the change decreases the DSSIZE value).

        You can find more information about RPN table spaces in the IBM redbook titled, "IBM DB2 12 for z/OS Technical Overview." That document can be downloaded from this URL: http://www.redbooks.ibm.com/abstracts/sg248383.html?Open.

        Robert

        Delete
      2. A clarification: an RPN table space must also be a universal table space. That said, if you have a classic range-partitioned table space (in other words, a non-universal table space that uses table-based, as opposed to index-based, range partitioning) then you can alter the table space with a SEGSIZE value (to get it to universal partition-by-range) and alter it again with PAGENUM RELATIVE, and the same online REORG that converts the table space from classic partitioned to universal PBR will also convert the table space to use relative page numbering.

        Robert

        Delete