Db2 for z/OS: The Online Path from a Multi-Table Table Space to Universal Table Spaces

Back in 2014, I posted an entry to this blog on the topic of getting to universal table spaces from non-universal table spaces. In that entry, I noted that there was an online path (ALTER followed by online REORG) for getting from a “classic” partitioned table space to a universal partition-by-range (PBR) table space, and for getting from a single-table simple table space or traditional segmented table space to a universal partition-by-growth (PBG) table space. I also pointed out that no such online path to universal table spaces existed for a multi-table table space: “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 wait for the hoped-for Db2 enhancement ended in October of 2020, when Db2 12 function level 508 became available (via the fix for APAR PH29392). In this blog entry, I will describe how a capability introduced with Db2 12 function level 508 enables online migration of tables from multi-table table spaces to universal PBG table spaces.

For illustrative purposes, let’s say that you have a traditional segmented table space containing four tables (I say, “traditional segmented table space” because universal table spaces are also segmented). The tables are named T1, T2, T3 and T4. You have function level 508 (or later) activated on your Db2 12 system (or you have a Db2 13 system). How do you get tables T1, T2, T3 and T4 from the traditional segmented table space into universal PBG table spaces, in an online way? Here’s how:

• First, create a table space for each of the tables T1, T2 and T3 (I’ll get to T4 momentarily). Here’s the form of the CREATE TABLESPACE statement you should use for this purpose (let’s assume that the new table spaces will be named TS1, TS2, etc.):

CREATE TABLESPACE TS1
  IN dbname
  …
  MAXPARTITIONS 1
  DEFINE NO
  DSSIZE xxx;

Note: the database for each of these new PBG table spaces will need to be the same as the database of multi-table table space of interest (similarly, the CCSID of each of the new table spaces will have to be the same as the CCSID of the multi-table table space of interest). Also, MAXPARTITIONS 1 is required, at least initially (you can change the MAXPARTITIONS value later if desired). DEFINE NO is also required (the table space data sets will be created by Db2 later). DSSIZE can be any value appropriate for the tables that will be moved to the new table spaces (consider that 64G would work for any table, since a traditional segmented table space cannot exceed 64 GB in size).

• Next, issue the following ALTER statement for the multi-table traditional segmented table space (the new ALTER TABLESPACE option introduced with Db2 12 function level 508 is highlighted in green):

ALTER TABLESPACE dbname.source-table-space-name
  MOVE TABLE T1 TO TABLESPACE dbname.TS1;

Note that this is a pending change for the source table space – the actual table move will be effected via a subsequent online REORG of the source table space, as explained below. The statement above would be executed as well for tables T2 and T3 (I haven’t forgotten about table T4 – I’ll get to that). Keep in mind that, starting with Db2 12, the APPLCOMPAT package bind specification applies to DDL as well as DML statements. What does that mean for the ALTER TABLESPACE statement shown above? It means that the package through which the ALTER is issued (e.g., a DSNTEP2 package, or a SPUFI package) needs to have an APPLCOMPAT value of V12R1M508 or higher.

• OK, online REORG time. An online REORG executed for the source table space (the one in which tables T1, T2, T3 and T4 had been located) will cause each table for which an ALTER TABLESPACE with MOVE TABLE has been executed to be relocated to its designated PBG table space. When the online REORG has completed, each relocated table will be ready to use – it will have its indexes and everything.

So, what about table T4? You have a choice here. One option would be to do for table T4 what you did for tables T1, T2 and T3: create a new PBG table space for the table, and execute an ALTER TABLESPACE with MOVE TABLE T4. And the other option? Well, consider the situation after you’ve moved tables T1, T2 and T3 to their respective PBG table spaces. The source table space, which formerly held four tables, now holds only one table: T4. What does that mean? It means that you can alter the source table space with a MAXPARTITIONS value and then online REORG it to convert it to a PBG table space – you’ve been able to do that for a single-table traditional segmented table space or a single-table simple table space since Db2 10 for z/OS.

Here are a few things to keep in mind with regard to online migration of tables from a multi-table table table space to PBG table spaces:

• Moving tables from multi-table table spaces to PBG table spaces is likely to mean an increase in the number of data sets for the Db2 system. Given that a table’s database can’t change when it goes from a multi-table table space to a PBG table space, you’ll want to make sure that the number of OBIDs (object identifiers) for the database in question will not exceed the limit of 32,767. A page in the Db2 for z/OS documentation contains a query that you can issue to identify databases that might be getting close to the OBID limit.
• More data sets could also lead you to increase the size of the DBD cache in the EDM pool (the associated ZPARM parameter is EDMDBDC), and/or to increase the Db2 subsystem’s DSMAX value. Regarding the DBD cache, you generally want the ratio of “DBD requests” to “DBD not found” (referring to fields in a Db2 monitor-generated statistics long report, or in an online display of EDM pool activity) to be at least in the tens of thousands to one. As for DSMAX, you usually want that value to be sufficiently high so as to either not be reached (per your Db2 monitor) or so that only a few data sets per hour are closed as a result of hitting the DSMAX limit (again, per your Db2 monitor).
• An online REORG that materializes a MOVE TABLE pending change will invalidate packages that depend on the table or tables being moved, so plan for rebinding those packages (the SYSPACKDEP catalog table contains information to identify dependent packages).
• Additionally, an online REORG that materializes a MOVE TABLE pending change will operate on both the source and target table spaces; accordingly, inline image copies will be created for those table spaces. These will establish a recovery base for the objects, but note that after execution of the table-moving online REORG you will not be able to recover the source table space to a point in time prior to the table-moving online REORG.
• Don’t worry if a source table space holds hundreds of tables (as some do in the real world) – there’s no requirement that all the tables be moved to PBG table spaces in one fell swoop. You can move a few at a time, no problem. Just keep in mind that an online REORG of the source table space will move every table that has not already been moved and for which there is a pending MOVE TABLE change.

There you have it. Universal table spaces are what you want, and you now have an online way to get there for your multi-table table spaces. Happy moving.

Originally published on Roberts Db2 Blog.