Function level 508 (activation enabled by APAR PH29392 - October 2020)

Function level 508 (V12R1M508) introduces support for moving tables from deprecated multi-table simple and multi-table segmented table spaces to partition-by-growth universal table spaces (UTS), without taking an outage, to improve application and database operations and performance.

Contents

Finding function level 508 changes: To find the new and changed content for this function level, try searching for "FL 508" from any page. You'll see a list of new and changed topics for function level 508.

Throughout the Db2 12 information, when you see the link FL 508, the adjacent content was changed for function level 508, and you can click the link to see the page that you are currently reading.

Migration of multi-table table spaces to partition-by-growth universal table spaces (UTS)

Function level 508 enhances the ALTER TABLESPACE statement with a new MOVE TABLE option, which can be used to move a table from a source table space to a target table space. With this enhancement, you can now easily move tables from multi-table simple or multi-table segmented (non-UTS) table spaces, which are deprecated, to partition-by-growth UTSs. This new capability provides an efficient way to migrate existing data from deprecated table spaces to supported table spaces to take advantage of new functionality.

If the data sets of the source table space are already created, the MOVE TABLE operation becomes a pending definition change that must be materialized by running the REORG utility on the source table space.

For more information, see:

The following APARS delivered the functional code for the migration of multi-table table spaces to PBG UTS: PH27370, PH27658, PH27659, PH27660, PH27661, PH27662, and PH27663.

FTB support for non-unique indexes

With APAR PH30978 applied, function level 508 also extends the fast index traversal (often called "FTB" for "fast traverse blocks") feature introduced with the original release of Db2 12 to non-unique indexes.

FTBs provide valuable Db2 transaction performance improvements without the need for application changes. FTBs use a cache-friendly optimized in-memory structure to accelerate index-tree traversals, which can significantly reduce get page operations and CPU consumption. They are enabled by default in Db2 12 by the INDEX_MEMORY_CONTROL subsystem parameter setting and can take effect as soon as function level 100.

To enable use of FTBs for non-unique indexes, you set the value of the FTB_NON_UNIQUE_INDEX subsystem parameter to YES. The default value is NO.

For more information, see:

Activation details for function level 508

Table 1. Function level 508 activation details
Enabling APAR: APAR PH29392
Minimum Db2 catalog level: V12R1M507
Catalog changes: None
Application compatibility control: Application compatibility level V12R1M508 or higher must be in effect for use of the new MOVE TABLE keyword of the ALTER TABLESPACE statement.

How to activate function level 508

The following steps summarize the process for activating this function level. To learn more about how to activate and control the adoption of new capabilities available for use in your Db2 12 environment and continuous delivery in general, see Adopting new capabilities in Db2 12 continuous delivery.

To activate Db2 12 function level 508, complete the following steps:
  1. If Db2 12 is still at function level 100, activate function level 500 or 501 first. For more information, see Activating Db2 12 new function at migration.
  2. Verify that any incompatible changes are resolved, including for any lower function levels not yet activated. See Incompatible changes summary for function levels 501 and higher.
  3. Check that Db2 is at a sufficient code level by issuing a DISPLAY GROUP command, as described in Determining the Db2 code level, catalog level, and function level. The DSN7100I message indicates the Db2 code level for under DB2 LVL in the member details. If DB2 LVL for any member is lower than 121508, apply the PTF for APAR PH29392 and any other required maintenance, so that all members indicate 121508.
    Tip: You can apply any PTF at any function level, and it is best to run Db2 at this code level or higher for a period of time before proceeding with activation of the function level. Db2 cannot run at any lower code level after you activate a function level, so you cannot remove any of the required PTFs after activating a function level.
  4. Tailor the CATMAINT and function level activation jobs by running the installation CLIST:
    1. In panel DSNTIPA1, specify INSTALL TYPE ===> ACTIVATE. Then specify the name of the output member from the previous function level activation (or migration) in the INPUT MEMBER field, and specify a new member name in the OUTPUT MEMBER field.
    2. In panel DSNTIP00, specify TARGET FUNCTION LEVEL ===> V12R1M508. The CLIST uses this value when it tailors the ACTIVATE command in the DSNTIJAF job and the CATMAINT utility control statement in the DSNTIJTC job.
    3. Proceed through the remaining CLIST panels, and wait for the CLIST to tailor the jobs for the activation process. The output data set contains the tailored jobs for the activation process.
  5. Run the DSNTIJIC job to take an image copy of the Db2 catalog and directory.
  6. Run the tailored DSNTIJTC job, or run the CATMAINT utility with LEVEL V12R1M508, to update the appropriate catalog level. If multiple catalog updates are required, the CATMAINT job processes each update in sequential order. If a later update in the sequence fails, the previous successful updates do not roll back, and the catalog level remains at the highest level reached. If that occurs, you can correct the reason for the failure and resubmit the same CATMAINT job.
  7. Optionally, check that Db2 is ready for function level activation by issuing the following ACTIVATE command with the TEST option:
    -ACTIVATE FUNCTION LEVEL (V12R1M508) TEST
    Message DSNU757I indicates the results. For more information, see Testing Db2 function level activation.
  8. Run the tailored DSNTIJAF job, or issue the following ACTIVATE command:
    -ACTIVATE FUNCTION LEVEL (V12R1M508)
  9. If you are ready for applications to use new capabilities in this function level, rebind them at the corresponding application compatibility level. For more information, see Controlling Db2 application compatibility.
    Optionally, when you are ready for all applications to use the new capabilities of the target function level, you can run the following jobs:
    1. Run DSNTIJUZ to modify the subsystem parameter module with the APPLCOMPAT value that was specified on panel DSNTIP00.
    2. Run DSNTIJOZ job to issue SET SYSPARM command to bring the APPLCOMPAT subsystem parameter change online.
    3. Run DSNTIJUA job to modify the Db2 data-only application defaults module with the SQLLEVEL value that was specified on panel DSNITP00.

Function level 508 incompatible changes

Function level 508 introduces no incompatible changes.