Function level 507 (PH24371 - June 2020)

Function level 507 (V12R1M507) introduces application granularity for locking limits, deletion of old statistics when using profiles, CREATE OR REPLACE PROCEDURE for external and native SQL procedures, and newly supported passthrough-only expressions with IBM® Db2 Analytics Accelerator.

Contents

Application granularity for locking limits (NUMLKUS and NUMLKTS)

Deletion of old statistics when using profiles

CREATE OR REPLACE for procedures

Newly supported passthrough-only expressions with IBM Db2 Analytics Accelerator

Activation details for function level 507

How to activate function level 507

Function level 507 incompatible changes

Application granularity for locking limits (NUMLKUS and NUMLKTS)

Two new built-in global variables are introduced to support application granularity for locking limits, which were previously specific only by the NUMLKUS and NUMLKTS subsystem parameters:

  • SYSIBMADM.MAX_LOCKS_PER_TABLESPACE contains an integer value for the maximum number of page, row, or LOB locks that the application can hold simultaneously in a table space. If the application exceeds the maximum number of locks in a single table space, lock escalation occurs. MAX_LOCKS_PER_TABLESPACE corresponds to the existing NUMLKTS subsystem parameter.
  • SYSIBMADM.MAX_LOCKS_PER_USER contains an integer value integer value that specifies the maximum number of page, row, or LOB locks that a single application can concurrently hold for all table spaces. The limit applies to all table spaces that are defined with the LOCKSIZE PAGE, LOCKSIZE ROW, or LOCKSIZE ANY options. MAX_LOCKS_PER_USER corresponds to the existing NUMLKUS subsystem parameter.

For more information, see:

APAR PH15342 delivered the functional code to support the application granularity for locking limits.

Deletion of old statistics when using profiles

When you run the RUNSTATS utility with the USE PROFILE option, Db2 collects only those statistics that are included in the specified profile. Beginning in function level 507, Db2 now also deletes existing statistics that are not part of the profile. All frequency, key cardinality, and histogram statistics that are not included in the profile are deleted from the catalog for the target object. This new behavior provides a way for you to remove stale distribution statistics without impacting concurrently running dynamic SQL.

This new capability also applies when profiles are used to gather inline statistics by the REORG TABLESPACE and LOAD utilities.

For more information, see:

APAR PH16345 delivered the functional code to support the deletion of old statistics when using profiles.

CREATE OR REPLACE for procedures

You can now manage stored procedures in complex application environments more easily. The CREATE PROCEDURE statement (for external procedures and native SQL procedures) is extended to allow a new OR REPLACE clause. By adding the OR REPLACE clause to the CREATE PROCEDURE statement, you can reuse your original statement, make some changes to it, and reissue it to change the definition of an existing procedure. In many cases, this eliminates the need to first drop the existing procedure and then recreate it with the original CREATE statement that has been modified as needed.

Additionally, for native SQL procedures, you can use the OR REPLACE clause on a CREATE PROCEDURE statement in combination with a VERSION clause to replace an existing version of the procedure, or to add a new version of the procedure. When you reuse a CREATE statement with the OR REPLACE clause to replace an existing version or to add a new version of a native SQL procedure, the result is similar to using an ALTER PROCEDURE statement with the REPLACE VERSION or ADD VERSION clause. If the OR REPLACE clause is specified on a CREATE statement and a procedure with the specified name does not yet exist, the clause is ignored and a new procedure is still created.

APAR PH24324 delivered the functional code to support the new CREATE OR REPLACE PROCEDURE syntax.

Newly supported passthrough-only expressions with IBM Db2 Analytics Accelerator

The following built-in functions are now supported as passthrough-only expressions, which are passed through from Db2 for z/OS® to IBM Db2 Analytics Accelerator: For passthrough-only expressions, Db2 for z/OS only verifies that the data types of the parameters are valid for the functions. The accelerator engine does all other function resolution processing and validation. For more information, see Accelerating queries with passthrough-only expressions.

APAR PH23042 delivered the functional code for the newly supported passthrough-only expressions.

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

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

Activation details for function level 507

Function level 507 has the following activation details:

Enabling APAR: PH24371.
Minimum Db2 catalog level: V12R1M507
Catalog changes: The following built-in global variables are introduced via a CATMAINT job:
  • MAX_LOCKS_PER_TABLE_SPACE
  • MAX_LOCKS_PER_USER
Application compatibility control: Applications must run at application compatibility level V12R1M507 or higher to use the following new capabilities:
  • The SYSIBMADM.MAX_LOCKS_PER_TABLESPACE built-in global variable for overriding the NUMLKTS subsystem parameter.
  • The SYSIBMADM.MAX_LOCKS_PER_USER built-in global variable for overriding the NUMLKUS subsystem parameter.
  • CREATE PROCEDURE with the OR REPLACE and SPECIFIC clauses for external procedures and native SQL procedures.
  • Newly supported passthrough-only expressions.

How to activate function level 507

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 507, 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 the highest function level that can be activated is lower than V12R1M507, apply the PTF for APAR PH24371 and any other required maintenance, so that all members indicate 121507 or higher for DB2 LVL in the DSN7100 message.
    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 ===> V12R1M507. 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 V12R1M507, 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:
    -ACTIVATE FUNCTION LEVEL (V12R1M507) 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 (V12R1M507)
  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 507 incompatible changes

Function level 507 introduces no incompatible changes.