With the availability of the PTF for APAR PH62635, you can activate function level V13R1M506 in Db2 13 for z/OS. Function level 506 introduces the following new capabilities and enhancements in Db2 13 for z/OS.

Non-disruptive clearing of data partitions with the LOAD utility. These enhancements to the LOAD utility improve the elapsed time and availability when a data partition or partitions are emptied using an empty input data set or DD DUMMY (also known as "dummy load"). The enhancement adds a new keyword, NOKEYDELETE in the LOAD utility syntax, to postpone the deletion of key entries from the associated logical partition of the NPSI and introduces a new Db2 object state, RBDPM, to allow continued read access to all logical partitions. For more information.

APAR PH62543 introduces the functional code for this enhancement.

Improved performance for IN-list predicates. Function level 506 introduces a new method is introduced to improve performance of a certain type of residual INLIST predicates. A single-table SQL with a single big residual INLIST predicate can expect up to 90% CPU time improvement, but the actual improvement number might vary depending on the entire query complexity.

APAR PH58750 introduces the improved performance for IN-list predicates.

More concurrently active Db2 traces. Function level 506 raises the maximum number of concurrently active traces from 32 to 64. The increase in the number of concurrently active traces lets you control the filtering and auditing capabilities for up to 64 traces in a Db2 subsystem or data sharing member.

APAR PH59020 delivered the functional code to support more concurrently active traces.

INSERT with multi-row VALUES clause. Applications that run at application compatibility level V13R1M506 or higher can specify multiple rows in the VALUES clause of a single INSERT statement. The number of values for each row in the VALUES clause must be equal to the number of names in the column list and columns that are identified in the INCLUDE clause. Comma separators are used to specify multiple column values, multiple rows, or both. When the value list is enclosed in parentheses, the first value is inserted in the first column in the list, the second value in the second column, and so on. Multiple rows enclosed in parentheses can be separated by commas. If the values list is not enclosed in parentheses, each value is inserted in a single column in a new row.

This capability increases portability of applications by adding support in Db2 for z/OS for syntax that is already supported by Db2 on other platforms, and it reduces the number of INSERT statements that application developers must write to insert multiple rows into a table.  

APAR PH62561 delivered the functional code for multi-row INSERT with the VALUES clause.

Adding implicitly hidden ROWID columns. In application compatibility level V13R1M506 or higher, you can specify IMPLICLITY HIDDEN when you add a ROWID column with the ADD COLUMN clause of an ALTER TABLE statement. With implicitly hidden ROWID columns, the result of a select statement includes the column only when the select-clause explicitly names the column. That is, the result of an SQL statement that only implies selection of the column, such as when SELECT * is used, does not return the implicitly hidden ROWID column. As a result, such ROWID columns can be added without impacting existing applications.

This capability is especially useful for converting existing tables to a partition-by-range (PBR) when a suitable column does not already exist for the partitioning key.

Below application compatibility level V13R1M506, Db2 13 issues SQL code -270 in this situation. 

APAR PH61629 delivered the functional code for adding implict hidden ROWID columns.

Lift certain restrictions of column masks. Function level 506 removes certain restrictions for column masks for applications that run at application compatibility level V13R1M506 or higher. Specifically, the following numeric reason codes are removed from SQL code -20478: 

24 - The column is referenced in a WHEN clause of a CASE expression. 

27 - A sort key expression for ARRAY_AGG, LISTAGG, PERCENTILE_CONT, PERCENTILE_DISC or a numeric expression for MEDIAN references a column for which a column mask is defined. APARs PH62393 and PH62690 delivered the functional code to lift certain column mask restrictions.

Verification of new-function APARs. Continuing a pattern introduced in function level 504 and continued with function level 505, function level 506 also verifies that several PTFs for new function APARs that were previously released without function level control are applied consistently in your Db2 environment, especially in data sharing. The new function behaviors in these APARs become available as the apply the PTF on each member, but activating function level 506 conforms that they are available for use on every member. 

§  Allow binding of a location alias to a different IP address for a standalone Db2 subsystems (PH62990)

§  SPECIFICIMP function support when Db2 calls the WLM sysplex routing service (PH62438)

§  DDF WLM performance blocks moved from ECSA to HVCommon storage (PH58162)

§  New statistics for understanding the IP addresses that applications use to access Db2 (PH60133)

§  Display privilege authority for DISPLAY ML and DISPLAY RLIMIT (PH60536)

§  Removal of ephemeral port use for DVIPA monitoring from DDF (PH58161)

§  Enhanced DSNJ114I message output (PH60669)

§  New model retraining capability of SQL Data Insights (PH60870)

§  Cross-Origin Resource Sharing (CORS) support for Db2 REST services (PH59837)

For more information about function level 506 and how to activate it in your Db2 13 for z/OS environment, see Function level 506 (APAR PH62635 - October 2024).

#Db2forz/OS