Function level 509 (activation enabled by APAR 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.
Contents
Support for tamper-proof audit policies
High availability for accelerator-only tables
Specify a compression algorithm at the table, table space, or partition level
Temporal RI allows UPDATE or DELETE on the parent table
Activation details 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
Function 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.
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
- 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
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:
|
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.
- 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.
- 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.
- 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.
- Tailor the CATMAINT and function level activation jobs by running the installation CLIST:
- 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.
- 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.
- 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.
- Run the DSNTIJIC job to take an image copy of the Db2 catalog and directory.
- 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.
- 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.-ACTIVATE FUNCTION LEVEL (V12R1M509) TEST - Run the tailored DSNTIJAF job, or issue the following ACTIVATE command:
-ACTIVATE FUNCTION LEVEL (V12R1M509) - 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:
- Run DSNTIJUZ to modify the subsystem parameter module with the APPLCOMPAT value that was specified on panel DSNTIP00.
- Run DSNTIJOZ job to issue SET SYSPARM command to bring the APPLCOMPAT subsystem parameter changes online.
- 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.