Function level 503 (PH00506 - October 2018)

Function level 503 (V12R1M503) introduces support for replication of system-period temporal tables and generated expression columns, a change to temporal auditing support for temporal data, and a new console message that indicates when catalog levels or function levels change.

Contents

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

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

About Db2 AI for z/OS support:

Function level 503 originally introduced support for IBM Db2 AI for z/OS®, which leverages machine learning technology to empower the Db2 for z/OS optimizer to determine the best-performing query access paths, based on your workload characteristics.

However, with APAR PH05323 and IBM Db2 AI for z/OS Version 1.1.0.1 or later, Db2 12 supports IBM Db2 AI for z/OS at function level 500 or higher.

For more information see IBM Db2 AI for z/OS documentation.

Enablement for replication of system-period temporal tables and generated expression columns

Function level 503 provides support needed by replication programs to replicate Db2 for z/OS data in system-period temporal tables and in tables that contain generated expression columns.

A new built-in global variable, SYSIBMADM.REPLICATION_OVERRIDE, provides support needed to replicate system-period temporal tables or in tables that contain generated expression columns. The global variable is intended for use on the apply side of the replication process by products that enable replication of Db2 data. Applications that set the SYSIBMADM.REPLICATION_OVERRIDE global variable must be bound using APPLCOMPAT V12R1M503.

See REPLICATION_OVERRIDE for more information.

Console message for catalog level or function level change

Function level 503 introduces console message DSNG014I. It is issued to the console whenever the function level or catalog level of a Db2 subsystem or data sharing group changes. See DSNG014I.

Temporal auditing

When a system-period temporal table that contains one or more rows of data is altered to add a DATA CHANGE OPERATION generated expression column (also called an auditing column), Db2 assigns null values to the new column in the existing rows. If the table was also defined with the ON DELETE ADD EXTRA ROW attribute, prior to function level 503, temporal queries that were run on these types of tables did not return any rows that contained a null value that corresponds to the DATA CHANGE OPERATION column. As a result, the data that was returned did not include the historical data that was expected.

With function level 503, rows that contain null values in the history table column that corresponds to the DATA CHANGE OPERATION column will now be considered part of the intermediate result set for a system-period temporal query. However, rows that were added to a history table for the ON DELETE ADD EXTRA ROW attribute will still be excluded.

For information about the auditing capabilities of Db2, see Scenario for tracking auditing information.

APAR PI95480 delivered functional code to support the new temporal auditing capability.

Activation details for function level 503

Function level 503 has the following activation details:

Enabling APAR: PH00506
Minimum Db2 catalog level: V12R1M503
Catalog level V12R1M503 changes: The REPLICATION_OVERRIDE built-in global variable is added.

For a summary of changes in Db2 12, see Catalog changes in Db2 12.
Application compatibility control: Applications must run at application compatibility level V12R1M503 or higher to use the following new capabilities:
  • The SYSIBMADM.REPLICATION_OVERRIDE built-in global variable for replication of system-period temporal tables and generated expression columns.
  • The temporal query result change for system-period temporal tables defined with the ON DELETE ADD EXTRA ROW attribute that also contain a DATA CHANGE OPERATION column.

How to activate function level 503

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 503, 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 121503, apply the PTF for APAR PH00506 and any other required maintenance, so that all members indicate 121503.
    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 ===> V12R1M503. 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 DSNTIJTC job, or run the CATMAINT utility with LEVEL V12R1M503, 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. Check that Db2 is ready for function level activation by issuing the following ACTIVATE command with the TEST option:
    -ACTIVATE FUNCTION LEVEL (V12R1M503) TEST
    Db2 issues message DSNU757I to indicate 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 (V12R1M503)
  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 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 503 incompatible changes

Function level 503 introduces the following incompatible changes.

Result change for system-period temporal tables defined with ON DELETE ADD EXTRA ROW

A query has incompatible behavior at application compatibility level V12R1M503 if all of the following conditions are met:

  • It references a system-period temporal table (STT) with FOR SYSTEM_TIME FROM value1 TO value2 (or BETWEEN value1 AND value2) period specification. The STT can be directly referenced or indirectly referenced through views.
  • The STT is defined with the ON DELETE ADD EXTRA ROW clause.
  • The STT is defined with the DATA CHANGE OPERATION column.
  • During migration to use the temporal auditing feature, the altered table contains existing rows.
  • The DATA CHANGE OPERATION column is populated with the default NULL value for the existing rows. When the query is executed, the historical table contains records with a NULL value in DATA CHANGE OPERATION column.

Action required: Start a trace for IFCID 0376. In the trace output, function code 1215031 or 1202 identifies the affected queries.

The SET_MAINT_MODE_RECORD_NO_TEMPORALHISTORY stored procedure is not supported when APPLCOMPAT is V12R1M503 or higher

Users will not be able to invoke the SYSPROC.SET_MAINT_MODE_RECORD_NO_TEMPORALHISTORY stored procedure when running with APPLCOMPAT=V12R1M503 or higher. Existing applications that invoke the SYSPROC.SET_MAINT_MODE_RECORD_NO_TEMPORALHISTORY stored procedure will fail starting with APPLCOMPAT=V12R1M503 or higher.

Action required: Start a trace for IFCID 0376. In the trace output, function code 1215032 or 1203 identifies applications that use the SYSPROC.SET_MAINT_MODE_RECORD_NO_TEMPORALHISTORY stored procedure. Modify such applications to set value of the SYSIBMADM.REPLICATION_OVERRIDE built-in global variable to 'Y' instead. For more information, see SYSIBMADM.REPLICATION_OVERRIDE built-in global variable.

For all incompatible changes in Db2 12, see Incompatible changes in Db2 12