Starting in function level 509 in Db2 13, you can convert all remaining table spaces for Db2 catalog and directory tables from deprecated non-UTS types to partition-by-growth universal table spaces (PBG UTS). These conversions simplify administration, align Db2 system objects with modern storage architectures, improve performance, and help establish a new foundation for future innovation in Db2 for z/OS. These conversions will also be required before you can activate function level 511 in Db2 13, which is not yet released at the time of this writing.
The evolution of UTS for Db2 system objects
The UTS table space types were first introduced in version 9 of Db2 for z/OS, and many tables in the Db2 catalog and directory were converted to PBG UTS during the enabling-new function mode (ENFM) processing for migration to version 10 and version 11. PBG UTS table spaces have also been used for all new catalog tables for many Db2 releases now. However, nineteen Db2 catalog table spaces and two directory table spaces still need to be converted to UTS after you activate function level 509 in Db2 13.
As Haakon Roberts mentioned in his recent blog entry, various types of deprecated objects will not be supported in the next version of Db2, including non-UTS table spaces. The system objects in the Db2 catalog and directory are not an exception to this rule.
The conversion capability for the remaining objects was delivered in several phases, with some of the groundwork already laid for these conversions before function level 509. For example:
- The PTF for APAR PH66270 removes limitations on specifying the names of various system objects in the INCLUDE and EXCLUDE clauses of LISTEDEF utility control statements.
- The PTF for APAR PH67554 introduces the UTSCONVERSION keyword for the DISPLAY GROUP command.
- The CATMAINT job for catalog level V13R1M508 introduces some of the catalog changes, including creation of new table spaces for the catalog and directory UTS conversions as DEFINE NO objects.
With the availability of function level 509, the conversion process is fully realized, and all remaining non-UTS table spaces in the Db2 catalog and directory are eligible for conversion to UTS. You can convert remaining non-UTS catalog and directory objects in any order and at any time when you are ready. In the future though, the UTS conversions will be required before you can activate function level 511 in Db2 13.
A new sample job named DSNTIJCR is also provided for converting all of the remaining non-UTS catalog and directory objects.
Identifying the objects that require UTS conversion with DISPLAY GROUP
You can use the DISPLAY GROUP command to identify objects that are eligible for conversion to UTS. A new dedicated keyword, UTSCONVERSION, is added to the command syntax for this purpose. For example, you can issue the following command:
-DISPLAY GROUP UTSCONVERSION
As mentioned above, the new syntax is enabled when you apply the PTF for APAR PH67554 in your Db2 environment. The report identifies whether each of the relevant objects is eligible for conversion or already converted. When you issue this command before starting any conversions, the output includes a UTS CONVERSION REPORT section that indicates the status of the UTS conversions.
DSN7100I -DB2A DSN7GCMD
*** BEGIN DISPLAY OF GROUP(........) CATALOG LEVEL(V13R1M509)
CURRENT FUNCTION LEVEL(V13R1M509)
HIGHEST ACTIVATED FUNCTION LEVEL(V13R1M509)
HIGHEST POSSIBLE FUNCTION LEVEL(V13R1M509)
PROTOCOL LEVEL(2)
GROUP ATTACH NAME(....)
---------------------------------------------------------------------
DB2 SUB DB2 SYSTEM IRLM
MEMBER ID SYS CMDPREF STATUS LVL NAME SUBSYS IRLMPROC
-------- --- ---- -------- -------- ------ -------- ---- --------
........ 0 DB2A -DB2A ACTIVE 131509 UTEC203 PR21 PRLMPR21
---------------------------------------------------------------------
UTS CONVERSION REPORT
--------------------------
TABLE UTS
SPACE CONVERSION ACTION REQUIRED
-------- ---------- ----------------
SCT02 ELIGIBLE
SYSUTILX ELIGIBLE
SYSALTER ELIGIBLE
SYSCONTX ELIGIBLE
SYSDDF ELIGIBLE
SYSEBCDC ELIGIBLE
SYSGPAUT ELIGIBLE
SYSGRTNS ELIGIBLE
SYSHIST ELIGIBLE
SYSJAVA ELIGIBLE
SYSROLES ELIGIBLE
SYSSEQ ELIGIBLE
SYSSEQ2 ELIGIBLE
SYSSTATS ELIGIBLE
SYSTARG ELIGIBLE
SYSTSASC ELIGIBLE
SYSTSUNI ELIGIBLE
SYSTSXTM ELIGIBLE
SYSTSXTS ELIGIBLE
SYSUSER ELIGIBLE
SYSXML ELIGIBLE
---------------------------------------------------------------------
SPT01 INLINE LENGTH: 32138
*** END DISPLAY OF GROUP(........)
DSN9022I -DB2A DSN7GCMD 'DISPLAY GROUP ' NORMAL COMPLETION
***
You can also find a list of the objects that are candidates for the UTS conversion in the Db2 product documentation. It's worth noting that several of the existing non-UTS catalog and directory table spaces contain multiple tables, and since a UTS can contain only one table, many more table spaces are used for these catalog and directory objects after they are converted to UTS.
For example, the non-UTS SYSDDF catalog table space contains 8 catalog tables. A CONVERTUTS REORG of the SYSDDF catalog table space moves each of the 8 tables into their own UTS table spaces.
For all the details, see the "Results" section in Converting Db2 catalog and directory objects to UTS.
Converting the non-UTS directory and catalog objects with REORG TABLESPACE
You can use the REORG TABLESPACE utility with the new CONVERTUTS keyword to complete the conversions. The CONVERTUTS keyword is only valid on catalog or directory table spaces that are candidates for UTS conversion, and it can be used only when SHRLEVEL REFERENCE or SHRLEVEL CHANGE is also specified.
Using the LISTDEF utility for the directory and catalog UTS conversions
The LISTDEF utility is changed to allow catalog and directory table names to be specified, eliminating the DSNU1002I messages that were previously issued if a system table name was specified. Pattern matching is not supported by LISTDEF for catalog and directory objects, so fully qualified table names must be specified.
LISTDEF also allows the SYSUTILX table space and its two tables, SYSUTIL and SYSUTILX, to be specified. However, the SYSUTILX objects cannot be specified with any other, non-SYSUTILX, objects in the same LISTDEF list.
Results of the conversion
After a successful conversion to UTS, the REORG TABLESPACE CONVERTUTS processing drops the original non-UTS table space. As mentioned above, many new table spaces are used after the conversions, and you can find their names in the Db2 product documentation.
Important: You must replace the original non-UTS table space names in your existing JCL and jobs with the new UTS names.
If any original non-UTS table spaces are somehow not deleted as part of the UTS conversion, you can run the following CATMAINT job to drop them:
CATMAINT UPDATE UNLDDN CLEANUP
In that situation, the DISPLAY GROUP command with the UTSCONVERSION option report output displays CATMAINT CLEANUP in the ACTION REQUIRED column.
It is worth noting that if a DB2 catalog table is empty when its table space is converted to UTS, the data sets for the new UTS remain undefined after the conversion. For the directory, the UTSs for DSNDB01.SYSTSUTM and DSNDB01.SYSTSUTX are always defined by REORG, even if the tables were empty, and the other directory table space that needs to be converted is SCT02, which is very unlikely to be empty.
Considerations for other Db2 utilities
The UTS conversions have implications for a few other Db2 utilities.
COPY utility considerations
Before the UTS conversion, the SYSUTILX directory table space contains two tables: SYSUTIL and SYSUTILX. The UTS conversion process moves these tables into separate UTS table spaces: SYSTSUTM and SYSTSUTX.
These two table spaces can be copied together in a single COPY utility statement. A LISTDEF list or a hardcoded list of names can be specified. The indexes associated with these two tables can also be specified in the same COPY statement, but no other objects are allowed in the same COPY statement.
RECOVER utility considerations
Recovery of a subset of the converted catalog or directory table spaces to a point in time that precedes its conversion to UTS is not possible. If this is attempted, the RECOVER utility issues the existing DSNU510I message.
The CATMAINT utility with the UPDATE UTILX RESET options can be used to reinitialize the SYSUTILX objects instead of a point-in-time recovery.
The new directory table spaces, DSNDB01.SYSTSUTM and DSNDB01.SYSTSUTX, can be recovered together in a single RECOVER utility statement. A LISTDEF list or a hardcoded list of names can be specified. The indexes associated with these two table spaces can also be specified in the same RECOVER statement, but no other objects are allowed in the same statement.
In an extreme case when point-in-time recovery is needed, the catalog, directory, and all user objects should be recovered to the same point of consistency as documented in Point-in-time recovery of the catalog, directory, and all user objects. You should also contact IBM Support before starting any such recovery.
QUIESCE utility considerations
The new directory table spaces, DSNDB01.SYSTSUTM and DSNDB01.SYSTSUTX, can be processed together in a single QUIESCE utility statement. A LISTDEF list or a hardcoded list of names can be specified. No other objects are allowed in the same statement.
Conclusions and next steps
With function level 509, Db2 13 completes an important step in modernizing the Db2 catalog and directory by enabling the conversion of all remaining non-UTS system table spaces to PBG UTS.
By converting the remaining non-UTS catalog and directory objects, you can retire deprecated structures, simplify ongoing administration, and prepare your environment for the next generation of Db2 support requirements. Converting the remaining non-UTS catalog and directory objects aligns your subsystem with the future direction of Db2 for z/OS. It also helps ensure that your Db2 catalog and directory are ready for continued innovation. To get started with converting the remaining non-UTS catalog and directory objects to UTS, see Converting Db2 catalog and directory objects to UTS.
Converting other non-UTS objects
All of the methods described previously in this post are specific to converting non-UTS objects in the Db2 catalog and directory, and they are not applicable to your user tables, including the various types of Db2-supplied user tables. However, this milestone is also another reminder that you should be planning conversions for your non-UTS user and application objects by using the capabilities documented in Converting deprecated table spaces to the UTS types.
Thank you to John Lyle and Laura Kunioka-Weis for their contributions to this post.
#Db2forz/OS