Working with Alters on DB2 z/OS

This section provides an overview and discusses working with Alters on DB2 z/OS.

PeopleTools Data Administration tools support native DB2 zOS alter syntax (known as DB2 Online Schema Evolution) for altering tables. For example:

ALTER TABLE <table name> ALTER COLUMN <column name> SET DATA TYPE <new definition>

Use of this Alter syntax provides the capability to make structural changes to a table without the requirement to drop and recreate it. The data remains available for both inquiry and update processing. This means that when possible, PeopleTools use "Alter In Place" through this native alter syntax, rather than "Alter By Table Rename" for specific additional use cases.

As a result of this Alter In Place syntax, existing data rows are not immediately reformatted at the time that the alter is committed to DB2. Instead, as a result of the use of the ALTER TABLE <table name> ALTER COLUMN <column name> SET DATA TYPE <new definition> syntax, DB2 does the following:

  • Places the corresponding tablespace or indices in either an Advisory Reorg Pending (AREO*), or Rebuild Pending (RBDP) status depending on the nature of the change (alter) made to a particular table.

  • Creates a new version of the tablespace which reflects the format of the desired change. Despite the pending status, the data continues to be available for inquiry and update processing, and rows are subsequently materialized in the post-alter format (the current tablespace version) as they are retrieved.

The following types of changes will cause tablespaces and indices to be placed in Advisory Reorg Pending (AREO*) status:

DB2 z/OS version

Changes

9.1 (and later)

  • Changes made to the length of character or vargraphic (Unicode) columns will cause the tablespace that contains the table to be placed in AREO*

  • Changes between compatible numeric data types will cause the tablespace that contains the table to be placed in AREO*

  • Altering a table to add a new column if the tablespace that contains the table is currently at version 0 (see tablespace versioning below), or, adding a new column and issuing DML (insert, update, or delete) across the commit scope (also see APAR PK54341 for more details).

The following types of changes will cause indices to be placed in Rebuild Pending (RBDP) status:

DB2 z/OS version

Changes

9.1 and later

Changes between compatible numeric data types will place any index that contains the affected column in RBDP.

To find objects in a pending status, run the display database command using the DB2 Interactive Command Processor (DB2I), DSN session under TSO, or a z/OS console session. As an example, the following command displays all tablespaces in the Advisory Reorg Pending (AREO*) status and all indices in the Rebuild Pending status for a database called Q51802R1:

-DIS DB(Q51802R1) SPACENAM(*) ADVISORY(AREO*) RESTRICT(RBDP)

DSNT360I  -5A ***********************************
DSNT361I  -5A *  DISPLAY DATABASE SUMMARY
              *    RESTRICTED ADVISORY
DSNT360I  -5A ***********************************
DSNT362I  -5A     DATABASE = Q51802R1  STATUS = RW
                  DBD LENGTH = 2996486
DSNT397I  -5A
NAME     TYPE PART  STATUS            PHYERRLO PHYERRHI CATALOG  PIECE
-------- ---- ----- ----------------- -------- -------- -------- -----
PTTBLZZ  TS         RW,AREO*
IDX1R9   IX         RW,RBDP
 ******* DISPLAY OF DATABASE Q51802R1 ENDED      **********************

Tablespace PTTBLZZ is in Read, Write and Advisory Reorg Pending status.

Index IDX1R9 is in Read, Write and Rebuild Pending status.

In most circumstances, a committed alter will cause DB2 to create a new version of the tablespace, which reflects the format of the desired alter. Rows are subsequently materialized in the post-alter format as they are retrieved. DB2 can store up to a maximum of 256 active versions of a tablespace, numbered 0 to 255. Version 0 indicates a tablespace that has never been altered, and version 0 is never reused. All rows are formally converted to the format determined by the latest tablespace version when the tablespace is reorganized.

When a tablespace reaches the maximum number of versions, it is important to note that subsequent attempts to alter any table contained in the tablespace will fail with SQL Code -4702. At this point, the DB2 Reorg Tablespace and Modify Recovery utilities must be executed to:

  • Reformat the data as dictated by the latest version of the tablespace (by running the Reorg Tablespace utility).

  • Recycle the version numbers so that DB2 can reuse all version numbers other than the active version of the tablespace (by running the Modify Recovery utility).

Note: The Reorg Tablespace utility with Index(All) option also removes the AREO* and RBDP statuses.

Important! When executing a significant number of Alters In Place (such as during the Upgrade Alter Without Deletes step or when applying a Maintenance Pack), the potential exists to create the maximum number of tablespace versions, particularly when running against shared tablespaces.

To mitigate the risk of encountering the -4702 SQLCode, PeopleTools Development recommends that you query the DB2 zOS Catalog to determine if there are any tablespaces in your PeopleSoft database that are close to the maximum version limit, and then execute the Reorg and Modify Recovery utilities accordingly to recycle version numbers before beginning any step that executes a significant number of Alters In Place.

To determine which tablespaces may require recycling of version numbers prior to beginning the Alter Without Deletes step, review the OLDEST_VERSION and CURRENT_VERSION columns of SYSIBM.SYSTABLESPACE for each tablespace in your PeopleSoft database as described in this section.

Use the following query as a guide to list the oldest and current version numbers for all PeopleSoft tablespaces in your environment:

SELECT NAME, DBNAME, OLDEST_VERSION, CURRENT_VERSION FROM
SYSIBM.SYSTABLESPACE 
WHERE CREATOR = '<authid of the owner of the tablespace>'
AND CURRENT_VERSION > 0;

Recycling tablespace version numbers is mandatory when all version numbers are currently in use. All tablespace versions are currently in use when one of the following conditions is true:

  • The value of the CURRENT_VERSION column is 255 (SYSIBM.SYSTABLESPACE).

  • The value of the CURRENT_VERSION column is one less than the value of the OLDEST_VERSION column.

Example: The value of the CURRENT_VERSION column is 255 (SYSIBM.SYSTABLESPACE)

All versions for tablespace PTTLRG0M are now in use, and the version numbers must be recycled.

DBNAME       NAME           OLDEST_VERSION  CURRENT_VERSION
------------ -------------  --------------  ---------------
CEBC0003     PTTLRG0M                    0              255

No subsequent Alters to objects in this tablespace will be allowed, and any attempt to do so will result in a SQLCode of -4702.

After executing the Reorg Tablespace and Modify Recovery utilities, CURRENT_VERSION and OLDEST_VERSION are equal:

DBNAME     NAME       OLDEST_VERSION    CURRENT_VERSION
---------- ---------  ----------------  ----------------
CEBC0003   PTTLRG0M                255               255

The following is the result of another Alter committed against an object in tablespace PTTLRG0M after the Reorg Tablespace and Modify Recovery utilities were executed to recycle the version numbers:

DBNAME     NAME       OLDEST_VERSION    CURRENT_VERSION
---------- ---------  ----------------  ----------------
CEBC0003   PTTLRG0M                255                 1

The value of CURRENT_VERSION for tablespace PTTLRG0M will now continue to increment from 1 to 254 as Alters In Place are committed. When the value of CURRENT_VERSION reaches 254, version numbers must again be recycled.

Example: The Value of the CURRENT_VERSION Column Is One Less Than The Value of the OLDEST_VERSION Column

For the following example, assume that shared tablespace PTAMSG01 was at its initial version (version 0), and that several Alters In Place were committed against multiple tables contained therein:

Initial version (version 0) of PTAMSG01:

DBNAME     NAME        OLDEST_VERSION  CURRENT_VERSION
---------- ----------  --------------  ---------------
CEBC0003   PTAMSG01                 0                0

After 108 committed Alters In Place to various tables within PTAMSG01:

DBNAME     NAME        OLDEST_VERSION  CURRENT_VERSION
---------- ----------  --------------  ---------------
CEBC0003   PTAMSG01                 0               108

Now assume that the Reorg Tablespace and Modify Recovery utilities were executed against tablespace PTAMSG01 when the CURRENT_VERSION was 108--prior to reaching the maximum of 255.

As a result of executing these utilities, the values for OLDEST_VERSION and CURRENT_VERSION were both set to 108: 

DBNAME       NAME      OLDEST_VERSION CURRENT_VERSION
------------ --------- --------------  --------------
CEBC0003     PTAMSG01             108             108

The value of CURRENT_VERSION for this tablespace then continued to increment from 108 to 255--and then onward to 107 (below).

At a value of 107, the value of CURRENT_VERSION was one less than the OLDEST_VERSION and execution of the Reorg and Modify Recovery utilities was mandatory.

DBNAME       NAME       OLDEST_VERSION   CURRENT_VERSION
------------ ---------  ---------------  --------------- 
CEBC0003     PTAMSG01               108               107

Any attempt to alter another table in tablespace PTAMSG01 while at a CURRENT_VERSION of 107 would have resulted in SQLCode -4702 because the value of CURRENT_VERSION was one less than the value of OLDEST_VERSION.

This section covers these key issues related to DB2 tablespace versioning and PeopleSoft upgrades:

  • Avoiding SQL code -4702.

  • Ensuring optimal performance for Data Conversion steps.

Avoiding SQL Code -4702

An Alter Without Deletes script executes a significant number of alters. Although the PeopleTools alter processing for DB2 zOS was designed to prevent DB2 from creating an excessive number of tablespace versions by carefully controlling the manner in which table alters are committed per tablespace, it is possible that DB2 may still create the maximum number of tablespace versions when running the Alter Without Deletes script if there are shared tablespaces already close to the maximum 255 version numbers.

To mitigate the possibility of the Alter Without Deletes script stopping due to SQL code -4702, run the following query prior to the Alter Without Deletes step, and run the Reorg Tablespace and Modify Recovery utilities accordingly for any tablespaces that may be close to the maximum allowed version number (either the CURRENT_VERSION is equal to 255, or CURRENT_VERSION is one less than OLDEST_VERSION, as previously explained).

SELECT NAME, DBNAME, OLDEST_VERSION, CURRENT_VERSION FROM 
SYSIBM.SYSTABLESPACE 
WHERE CREATOR = '<authid of the owner of the tablespace>'
AND CURRENT_VERSION > 0;

Then continue with the Alter Without Deletes script as documented.

If you run the Alter Without Deletes script manually (outside of the Change Assistant) using a tool, such as the DB2 Command Line Processor, Command Editor, SPUFI, and so on, disable the auto-commit feature. Change Assistant disables auto-commit when it invokes the Command Line Processor.

Ensuring Optimal Performance For Data Conversion Steps

While the PeopleTools alter processing for DB2 zOS was designed to prevent DB2 from creating excessive tablespace versions, you should still expect some shared tablespaces in your environment to become multi-versioned as a result of the normal execution of the Alter Without Deletes step. Until you reorganize, you may notice:

  • performance of dynamic SQL statements executed against a tablespace with multiple versions may suffer.

  • any indices in RBDP may be ignored by the DB2 optimizer.

To ensure optimal performance, we strongly recommend that you run the Reorg Tablespace (Index All) utility and the Modify Recovery utility to reformat data rows into the format described by the most current tablespace version for any tablespaces with several versions, after the Alter steps (Alter With/Without Deletes) have completed and prior to beginning the Upgrade Data Conversion steps.

For more details regarding the use of the DB2 zOS Reorg Tablespace and Modify Recovery utilities, refer to your IBM DB2 documentation.

See IBM DB2 UDB for z/OS Utility Guide and Reference