Db2 for z/OS and its ecosystem

By Paul McWilliams and Jennie Chang.

Db2 12 for z/OS  introduced continuous delivery of new capabilities in function levels that you activate when you are ready to use the new capabilities.  After you apply the the PTF for APAR PH24371, you can now activate function level 507

Function level 507 introduces the following new capabilities to Db2 12:

Application granularity for locking limits per user and per table space

Function level 507 introduces two new built-in global variables to support controlling the locks per table space and locks per user for specific applications. Before now, these limits were controlled at the Db2 subsystem level by the NUMLKTS and NUMLKUS subsystem parameter settings.

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.

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

Deletion of old statistics when Collecting statistics with USE PROFILE

When you use a statistics profile (by specifying the USE PROFILE keyword) to collect database statistics with the RUNSTATS utility or inline with the REORG TABLESPACE or LOAD utilities, Db2 collects only statistics that are specified in the existing profile. However, any previously collected statistics not profile remained unchanged in the Db2 catalog.

Now in function level 507, Db2 also deletes any previously collected statistics that are not specified in the profile for the target object, including all frequency, key cardinality, and histogram statistics not in the profile. This new behavior provides a way for you to remove stale distribution statistics from the Db2 catalog without impacting concurrently running dynamic SQL applications.

This new capability also applies when you specify the USE PROFILE option when collecting inline statistics with  the RUNSTATS, REORG TABLESPACE, or LOAD utilities.

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

CREATE OR REPLACE for stored procedures

Function level 507  simplifies managing stored procedures in complex application environments. Some clients have requested the capability to “reuse” a CREATE PROCEDURE statement to change an existing procedure, including the capability to define a new version, or change an existing version of a native SQL procedure.

Certain forms of the CREATE PROCEDURE statement (for external procedures and native SQL procedures) are now extended with a new OR REPLACE clause. By adding the OR REPLACE clause to the CREATE PROCEDURE statement you can reuse your original CREATE statement, make some additional changes to it, and reissue it to change the definition of the existing procedure. Specifically, the new OR REPLACE clause is now supported for the following CREATE PROCEDURE statements:

• CREATE PROCEDURE (external)
• CREATE PROCEDURE (SQL - native)

For native SQL procedures, you can also use  the VERSION clause with the OR REPLACE clause in the CREATE PROCEDURE statement to replace an existing version of the procedure, or to add a new version of the procedure. The result is similar to an ALTER PROCEDURE statement with the REPLACE VERSION or ADD VERSION clauses. 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.

When the OR REPLACE clause is specified and the procedure already exists, the new definition must define the same type of procedure (external or native SQL) as the existing procedure. That is, you cannot use the OR REPLACE clause to change the type of procedure with a CREATE PROCEDURE statement. To change the type of a procedure, you must first issue a DROP PROCEDURE statement for the existing procedure, and then issue a CREATE PROCEDURE with the new procedure definition.

The CREATE PROCEDURE (SQL – external) statement is now deprecated, so it has not been enhanced to support the OR REPLACE clause. If a CREATE PROCEDURE statement with the OR REPLACE clause or the SPECIFIC clause is specified for an existing external SQL procedure, Db2 returns an error.

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

Function level 507 introduces support for the following built-in functions as passthrough-only expressions, providing new analytics capabilities in Db2 for z/OS:

• ADD_DAYS
• BTRIM
• DAYS_BETWEEN
• NEXT_MONTH
• Regression functions (REGR_AVGX, REGR_AVGY, REGR_COUNT, ...)
• ROUND_TIMESTAMP (if invoked with a date expression)

Passthrough-only expressions cannot run on Db2 for z/OS and are passed through to IBM Db2 Analytics Accelerator. Db2 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.

Db2 12 also supports 16 other passthrough-only expressions, which were previously introduced in function level 504.

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