Function level 509 (PH33015 - February 2021)

Function level 509 (V12R1M509) introduces support for tamper-proof audit policies, high availability for accelerator-only tables, the specification of a compression algorithm at the object level, and a temporal RI enhancement to allow UPDATE or DELETE on a parent table.


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

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

Support for tamper-proof audit policies

Start of changeFunction level 509 introduces support for tamper-proof audit policies, which cannot be modified or stopped unless the user is authorized to access the Db2 audit policy profile by a z/OS® security product, such as RACF®, that is external to Db2. A z/OS system security administrator must perform a special task in the external security product to permit a Db2 user access to update, delete, or stop a tamper-proof audit policy. This new audit capability prevents users with advanced Db2 privileges from unnecessarily modifying or stopping the audit policy, minimizing the possibility of loss of audit information.End of change

You can create a new tamper-proof audit policy by inserting an audit policy record into the SYSIBM.SYSAUDITPOLICIES catalog table with a DB2START value of 'T'. Any STOP TRACE commands on this record require additional RACF authorization in function level 509. UPDATE and DELETE statements on this record require additional RACF authorization in function level 509, regardless of the application compatibility level.

APAR PH31180 delivered the functional code to support tamper-proof audit policies.

High availability for accelerator-only tables

With function level 509, you can now define an accelerator-only table in more than one accelerator. This capability enhances accelerator-only tables with high availability and workload balancing, including the following features:
  • Rerouting a query to an available accelerator if the target accelerator is not available
  • Using the accelerator workload balancing algorithm to distribute queries based on the queue length on each accelerator
To create an accelerator-only table that is defined in multiple accelerators, specify a location alias that represents multiple accelerators in the IN ACCELERATOR clause of the CREATE TABLE statement. The accelerator-only table is defined in all accelerators that are associated with the location alias.

APAR PH30574 delivered the functional code for high availability for accelerator-only tables.

Specify a compression algorithm at the table, table space, or partition level

Function level 509 introduces the capability to explicitly specify either the fixed-length or Huffman compression algorithm at the table, table space, or partition level using the CREATE TABLE, CREATE TABLESPACE, and ALTER TABLESPACE statements. The Db2 catalog is updated to indicate the compression algorithm used for each object.

APARs PH31729, PH31730, PH31731 delivered the functional code for specifying a compression algorithm at the table, table space, or partition level.

Temporal RI allows UPDATE or DELETE on the parent table

The UPDATE and DELETE statements are enhanced by removing restrictions related to the temporal referential integrity (RI) support that was introduced in the original Db2 12 release.

At application compatibility level V12R1M509 or higher, when an UPDATE statement with a FOR PORTION OF clause attempts to update the parent table in a temporal RI relationship, the update is allowed as long as the rules of temporal RI are not violated. Likewise, when a DELETE statement with a FOR PORTION OF clause attempts to delete from the parent table in a temporal RI relationship, the deletion is allowed, as long as the rules of temporal RI are not violated.

At any lower application compatibility level, such UPDATE or DELETE statements for a parent table in an RI relationship are restricted with SQLCODE -4736.

APAR PH30208 delivered the functional code for the temporal RI enhancement.

Activation details for function level 509

The following activation details apply to function level 509:

Enabling APAR: PH33015
Minimum Db2 catalog level: V12R1M509
Catalog changes: A new COMPRESS_USED column is added in the SYSIBM.SYSTABLEPART catalog table, and new values 'F', and 'H' are added for the COMPRESS column in the following catalog tables:
Application compatibility control: The following new capabilities require that application runs at application compatibility level V12R1M509 or higher:
  • UPDATE or DELETE from the parent table in a temporal RI relationship.

How to activate function level 509

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 509, 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 121509, apply the PTF for APAR PH33015 and any other required maintenance, so that all members indicate 121509.
    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. 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 ===> V12R1M509. 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.
  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 V12R1M509, to update the catalog 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.
  7. If the CATMAINT utility jobs from the previous step placed any altered Db2 catalog objects in REORG-pending (AREO*) advisory status, run the REORG utility for those objects.
  8. Check that Db2 is ready for function level activation by issuing the following ACTIVATE command with the TEST option:
    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:
  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 509 incompatible changes

Function level 509 introduces no incompatible changes.