I recently communicated with a Db2 for z/OS system administrator who had some concerns about non-universal table spaces in the context of his organization's planned migration from Db2 12 to Db2 13. This person asked important questions about the presence of non-universal table spaces in his Db2 environment and how those database objects might affect his team's plans for Db2 12-to-13 migration. Looking over that communications thread, I found myself thinking (as I often have over the years) that it could be the basis for a blog entry that would be helpful for people in the wider Db2 for z/OS user community; so, here we go.
Terminology: universal and non-universal table spaces
Logically speaking, Db2 for z/OS-managed data appears in tabular form - i.e., data records appear as rows in tables, and the data fields of a given set of records are columns of the associated table (that is kind of relational database 101 information). Db2 tables are physically instantiated in what are called table spaces (and table spaces map to VSAM data sets - VSAM being the primary file system of the z/OS operating system).
Table spaces fall into the realm of Db2 physical database design, which means they are basically invisible to application programs (a given Db2 table will look the same to a program, regardless of the type of table space in which it resides). DBAs decide on the type of table space that will be used for a given Db2 table.
For many years, there were three Db2 table space types: simple, segmented and range-partitioned (for the latter, partition ranges were initially defined by way of an index - starting with Db2 Version 8 for z/OS, range-partition specifications could be made at the table level). Db2 9 for z/OS (2007) introduced a new category of table space, known as universal. There are two universal table space types: partition-by-growth (PBG) and partition-by-range (PBR). For a PBG table space, a DBA determines an appropriate partition size (known as a data set size) and an appropriate maximum number of partitions (which can later be adjusted if need be), and when the first partition fills up then Db2 automatically allocates another partition, and when that one fills up another partition is allocated, and so on. For a PBR table space, a DBA determines the appropriate number of partitions and the partitioning key (comprised of one or more of tha associated table's columns), and the limit key value for each partition - Db2 then assigns rows to partitions accordingly.
Some noteworthy characteristics of universal table spaces:
- They are always partitioned (though a PBG table space might never grow beyond its first partition).
- They are segmented (referring to an aspect of management of the space within the table space - this is why I often use the phrase "traditional segmented" to refer to the segmented table space type that existed before universal table spaces were introduced).
- A given universal table space can hold one and only one table.
What does Db2 13 have to do with this?
For a Db2 12 system to be migrate-able to Db2 13, function level V12R1M510 has to be activated for the Db2 12 system. Activating function level N for a Db2 for z/OS system means that functionality associated with function levels prior to N will also be activated (if the prior function levels had not been explicitly activated previously). That means that activation of Db2 12 function level 510 will mean activation of the functionality of function level V12R1M504, and there's the rub - or so the aforementioned Db2 administrator thought. What this administrator knew - and knew rightly - is that, with function level V12R1M504 or higher activated, non-universal table spaces go into "deprecated" status (meaning that there will no longer be functional enhancements related to those non-universal table space types, and that the use of such table space types could eventually become not-possible - an eventuality likely to be far in the future and preceded by way-in-advance notice). Of more immediate concern to the Db2 administrator: he was under the impression that non-universal table spaces could not be created in a Db2 13 environment, and given that impression he was thinking that non-universal table spaces in his environment had to be converted to universal table spaces before Db2 12 systems could be migrated to Db2 13.
I had some good news for the Db2 administrator: non-universal table spaces CAN be created in a Db2 13 environment (this actually applies to traditional segmented table spaces - simple table spaces, while still usable even in a Db2 13 environment, have not been CREATE-able since Db2 9). To create a traditional segmented non-universal table space in a Db2 13 system (or in a Db2 12 system with function level V12R1M504 or higher activated), all one has to do is issue the CREATE TABLESPACE statement via a Db2 package executing with an application compatibility level of V12R1M503 or lower (as explained in an entry I posted to this blog a few years ago). The really important point here is that the presence of non-universal table spaces in your Db2 for z/OS environment should in no way impact your Db2 13 migration plans. Is it good to convert non-universal table spaces to the universal variety? Yes (as explained below); however, you do NOT need to complete that table space comversion work prior to migrating your Db2 12 systems to Db2 13 - you can continue that work in a Db2 13 environment.
Incentives for converting non-universal table spaces to universal table spaces
Avoiding use of deprecated table space types is a good reason to move away from simple and traditional segmented table spaces. There are, in addition, positive incentives for making this move, in the form of Db2 for z/OS features that apply only to universal table spaces (and tables therein):
- Pending DDL - Many changes to database objects can be accomplished via ALTER and a subsequent online REORG of the related table space.
- LOB in-lining - For a table with one or more LOB (large object) columns, this refers to the ability to store a portion of LOB values (up to a specified length for a given LOB column) in the base table, with the rest of the value (if any) going in the appropriate auxiliary table in a LOB table space.
- XML multi-versioning - For a table with one or more XML columns, this Db2 feature provides better concurrency for XML data access, and supports the XMLMODIFY built-in function, enabling changes that affect only part of a stored XML data value.
- ALTER TABLE with DROP COLUMN - This is a pending DDL change (see the first item in this list).
- Ability to insert a new partition in the middle of a range-partitioned table space (versus having to add the new partition at the end of the table space).
- The option of making ALTER COLUMN actions pending DDL changes (as opposed to requiring that these be immediate changes).
- Relative page numbering for range-partitioned table spaces.
How disruptive is it to go from a non-universal to a universal table space?
Answer: about as minimally disruptive as could be. It's an ALTER + online REORG action, so the only period of data unavailability related to a non-universal-to-universal table space conversion would be during the "switch" phase at the end of an online REORG, and that can be as brief as a few seconds. Dependent Db2 packages (related to programs that issue so-called static SQL statements targeting a table in a table space converted from non-universal to a universal type) will be invalidated by the online REORG that materializes the table space conversion - you would subsequently either rebind those invalidated packages with explicit REBIND PACKAGE commands, or you would let Db2 auto-bind them (auto-bind is triggered by the first request by a program to execute an invalidated package).
Converting a table space from non-universal to universal is really easy when the non-universal table space holds a single table. If the non-universal table space is of the simple or traditional segmented variety, the process is: 1) ALTER the table space with a MAXPARTITIONS value, and 2) execute an online REORG of the table space. Following completion of the online REORG, the table that had been in a simple or traditional segmented table space will be in a universal partition-by-growth table space. If the non-universal table space is of the range-partitioned type, the process is similarly simple: 1) ALTER the table space, this time with a SEGSIZE value, and 2) execute an online REORG of the table space. Following completion of the online REORG, the table that had been in a non-universal range-partitioned table space will be in a universal partition-by-range table space.
For a simple or traditional segmented table space that contains multiple tables, conversion to universal is a somewhat more involved process, but it still comes down basically to ALTER and online REORG. The process, introduced with Db2 12 function level 508, is described in an entry I posted to this blog not long ago.
After a table space has been converted from non-universal to a universal type, is it necessary to change the APPLCOMPAT value for Db2 packages that are dependent on the table space (or the table therein)?
Answer: NO. The requirement for a Db2 application compatibility level of V12R1M503 or lower is ONLY relevant for the package (maybe a SPUFI or a DSNTEP2 package, or a package associated with another program or tool used by Db2 DBAs) through which a CREATE TABLESPACE statement is issued, when there is a need to create a traditional segmented (i.e., non-universal) table space in a Db2 13 environment or a Db2 12 environment in which the activated function level is V12R1M504 or higher. Db2 packages having any supported APPLCOMPAT value can access a table in a universal table space (the lowest supported APPLCOMPAT value is V10R1, and the highest possible APPLCOMPAT value is equivalent to the current activated function level of the Db2 for z/OS system in which the package executes).
In a nutshell...
Converting non-universal table spaces to universal types is something you ought to be doing, but you DO NOT have to get that conversion work completed prior to migrating a Db2 12 system to Db2 13. The conversion process - available in Db2 12 and Db2 13 environments - varies somewhat depending on whether the non-universal table space is range-partitioned or not, and on whether the non-universal table space (if not range-partitioned) holds one table or multiple tables, but in any case it is pretty straightforward, minimally disruptive and application-transparent. So, yes, work on this, but at a pace that works for you and for your organization.
