Function level 505 (PH09191 - June 2019)

Function level 505 (V12R1M505) introduces improved concurrency for rebind of executing packages, automatic page sample by default for RUNSTATS, improved DECFLOAT data type support, built-in functions for encryption using key labels, and temporal and archive transparency for WHEN clauses on triggers.

Contents

Improved Hybrid Transactional Analytical Processing performance

Rebind phase-in for packages that are being used for execution

New built-in functions for encryption and decryption with key labels

Improved support for DECFLOAT columns

Improved RUNSTATS performance with automatic page sampling by default

Temporal and archive transparency support for WHEN clause on triggers

Activation details for function level 505

How to activate function level 505

Function level 505 incompatible changes

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

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

Improved Hybrid Transactional Analytical Processing performance

Performance improvements address the perceived latency between Db2 for z/OS® and IBM® Db2 Analytics Accelerator in Hybrid Transactional Analytical Processing (HTAP) environments. APAR PI98630 delivered the functional code.

Rebind phase-in for packages that are being used for execution

Function level 505 introduces support for rebind phase-in, an enhancement that allows Db2 to rebind a package concurrently with execution of the package. To improve concurrency, a rebind operation now creates a new copy of the package. When the rebind operation finishes, new threads can use the new package copy immediately, and existing threads can continue to use the copy that was in use prior to the rebind (the phased-out copy) without disruption.

Db2 waits for the duration specified by the IRLMRWT subsystem parameter before it creates package copies for the rebind phase-in operation.

This enhancement also enables switching to previous access paths and runtime structures gradually (the switch to the previous access path is phased-in), which allows regression recovery without incurring an application outage.

For more information, see the following topics:

APAR PH12186 delivered the functional code for new rebind phase-in capability.
Tip: APAR PH28693 (January 2021) improves concurrency for REBIND commands in Db2 12 at function level 505 or higher. With this APAR, a REBIND command now always obtains a U lock, allowing subsequent transactions that are executing a package to run in parallel. For more information, see Improved transaction execution times and concurrency for REBIND PACKAGE

New built-in functions for encryption and decryption with key labels

Function level 505 introduces new built-in encrypt and decrypt data key functions to simplify data protection. The new built-in function ENCRYPT_DATAKEY converts a block of plain text to a block of encrypted text using a specified algorithm and key label. The new built-in DECRYPT_DATAKEY functions return the block of encrypted text from ENCRYPT_DATAKEY as a block of the datatype specified by the function used.

Depending on your security configuration, Db2 might create an ACEE for the primary authorization ID to check for key label access during execution of ENCRYPT_DATAKEY or DECRYPT_DATAKEY_type built-in functions. If you are using external security for authorization, be aware that Db2 might also start providing this ACEE for other subsequent authorization checks, which can result in a different return codes from the exit. For more information, see When Db2 creates an ACEE and Example 5: Deferring to Db2 (missing ACEE).

APAR PH09506 delivered the functional code for the new built-in encryption and decryption functions.

Improved support for DECFLOAT columns

Function level 505 introduces improvements to the support for DECFLOAT columns to improve performance of mission critical applications. These improvements include the ability to specify DECFLOAT columns in an index and as a key in a primary or unique key.

For more information about using DECFLOAT columns in primary and unique keys, see CREATE TABLE and ALTER TABLE.

For more information about using DECFLOAT columns in indexes, including changes to the maximum length of an index key, see CREATE INDEX and ALTER INDEX.

APAR PH09797 delivered the functional code for the improved support of DECFLOAT columns.

Improved RUNSTATS performance with automatic page sampling by default

Function level 505 changes the meaning of the default value of the STATPGSAMP subsystem parameter to mean the same as YES, which means that RUNSTATS uses page-level sampling by default for universal table spaces. STATPGSAMP was introduced in Db2 12 by APAR PH07220. The default value is SYSTEM.

In function level 504 and lower, SYSTEM has the same meaning as NO. That is, RUNSTATS does not use page sampling by default. Any sampling is determined by the options that the RUNSTATS statement specifies.

As a result of this enhancement, RUNSTATS no longer updates real-time statistics by default. For possible actions to take, see Function level 505 incompatible changes.

For more information, see PAGE-LEVEL SAMPLING field (STATPGSAMP subsystem parameter) and RUNSTATS TABLESPACE syntax and options.

APAR PH07220 delivered the functional code for the new page default page sampling behavior.

Temporal and archive transparency support for WHEN clause on triggers

Function level 505 allows system-period temporal tables and archive-enabled tables to be referenced in WHEN clauses of both basic and advanced triggers, regardless of the settings of the SYSTIMESENSITIVE and ARCHIVESENSITIVE bind options. Time machine and transparent archive data retrieval are fully supported in the WHEN clause.

APAR PH09794 delivered the functional code for the temporal and archive transparency support for WHEN clause on triggers.

Activation details for function level 505

Function level 505 has the following activation details:

Enabling APAR: PH09191.
Minimum Db2 catalog level: V12R1M505 1
Catalog level V12R1M505 changes:

A new COPYID column is added in the following tables:

  • SYSIBM.SYSPACKDEP
  • SYSIBM.SYSPACKSTMT
Application compatibility control: Applications must run at application compatibility level V12R1M505 or higher to use the following new capabilities:
  • All built-in functions described in New built-in functions for encryption and decryption with key labels.
  • Creating a new index with a DECFLOAT column as a key, or adding a DECFLOAT column to an existing index.
  • Defining a primary or unique constraint with a DECFLOAT column.
  • Temporal and archive transparency support for WHEN clause on triggers.
.
Notes:
  1. Before tailoring the Db2 catalog for catalog level V12R1M505, apply the PTFs for the following APARs: PH19720 and PH15258.

How to activate function level 505

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 505, 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 121505, apply the PTF for APAR PH09191 and any other required maintenance, so that all members indicate 121505.
    Tip: You can apply any PTF at any function level. It is best to run Db2 at this code level or higher for some time before you proceed with activating 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 you activate a function level.
  4. Verify that the PTFs for the following APARs are applied: PH19720 and PH15258.
  5. 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 ===> V12R1M505. The Db2 installation 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 Db2 installation CLIST panels, and wait for the Db2 installation CLIST to tailor the jobs for the activation process. The output data set contains the tailored jobs for the activation process. For more information, see The Db2 installation CLIST panel session.
  6. Run the DSNTIJIC job to take an image copy of the Db2 catalog and directory.
  7. Run the DSNTIJTC job, or run the CATMAINT utility with LEVEL V12R1M505, to update to 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.
  8. Check that Db2 is ready for function level activation by issuing the following ACTIVATE command with the TEST option:
    -ACTIVATE FUNCTION LEVEL (V12R1M505) TEST
    Db2 issues message DSNU757I to indicate the results. For more information, see Testing Db2 function level activation.
  9. Run the tailored DSNTIJAF job, or issue the following ACTIVATE command:
    -ACTIVATE FUNCTION LEVEL (V12R1M505)
  10. 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 the Db2 application compatibility level.
    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 changes online.
    3. Run DSNTIJUA job to modify the Db2 data-only application defaults module with the SQLLEVEL value that was specified on panel DSNTIP00.

Function level 505 incompatible changes

Function level 505 introduces the following incompatible changes:

RUNSTATS does not update real-time statistics by default

Starting in function level 505, the RUNSTATS utility no longer updates real-time statistics (RTS) by default.

Action required: If you want RUNSTATS to update RTS, take one of the following actions:
  • In the RUNSTATS statement, specify TABLESAMPLE SYSTEM NONE and do not specify SAMPLE.
  • Set the STATPGSAMP subsystem parameter to NO and do not specify SAMPLE in the RUNSTATS statement.
Newly supported user-defined functions

If your environment contains existing user-defined functions with the same names and signatures as the new built-in functions, applications with unqualified references to the existing user-defined functions might start invoking the built-in function instead of the user-defined function in certain situations.

Action required: For information about avoiding such situations, see Ensuring that Db2 executes the intended user-defined function.