With the availability of the PTF for APAR PH51469, you can now activate function level V13R1M503 in Db2 13 for z/OS. Function level 503 introduces the following new application capabilities in Db2 13 for z/OS:

New default values in existing rows for added ROW CHANGE TIMESTAMP columns

Starting at application compatibility level V13R1M503 or higher, Db2 uses a constant default value when a new ROW CHANGE TIMESTAMP column is added. When processing ALTER TABLE statements that specify ADD COLUMN for ROW CHANGE TIMESTAMP columns, Db2 now sets the corresponding value in the DEFAULTVALUE column value in the SYSIBM.SYSCOLUMNS catalog table to the timestamp of the ALTER TABLE statement. (Note that CREATE TABLE processing to define a ROW CHANGE TIMESTAMP column does not set the DEFAULTVALUE column value.)

Before this change, Db2 derives the default values for existing rows from the page header from the row, which is the RBA for standalone Db2 subsystems. In data sharing, the default is derived is based on an internal mapping table between the LRSN and a timestamp. As a result, inserts, deletes, or updates to any rows in a page can change the derived default row change timestamp column values for unchanged rows, leading to unpredictable results.

APAR PH51185 delivered the functional code for this support.

Accelerator-only support for more than 32K elements in an IN list

Starting in function level 503, a query that has an IN list of more than the Db2 for z/OS limit of 32,767 (32K) elements in an IN predicate can run as an accelerator-only query on IBM Db2 Analytics Accelerator V7 for z/OS, if all of the following conditions are met:

·       Query acceleration is enabled and requested for the query. For more information, see Enabling acceleration of SQL queries.

·       The application runs at Db2 application compatibility level V13R1M503 or higher.

·       Option 12 is specified in the list of values for the QUERY_ACCEL_OPTIONS subsystem parameter.

·       The target accelerator is IBM Db2 Analytics Accelerator V7 for z/OS.

·       The IN list specifies only SQL constants.

·       The query can be functionally supported by the target accelerator and IBM Db2 Warehouse.

When such queries run on a V7 accelerator, the enforcement of any limits on the number of elements in the IN list predicate is handled by IBM Db2 Warehouse, instead of Db2 for z/OS. IBM Db2 Warehouse has no documented limit for elements in an IN list. However, practical limitations such as the memory and processing resources available for the query in IBM Db2 Warehouse are still likely to impose some limit on the number of elements that can be specified in the IN list predicate.

A view can also be created in Db2 for z/OS for a query with an IN list that contains more than 32K elements, but only under the same conditions listed previously for queries that contain such IN predicates. Any query that uses such a view is also evaluated and limited to the same conditions and restrictions described above. Such views also have the value 'R' in IBMREQD column in the SYSIBM.SYSVIEWS catalog table, to indicate the Db2 13 release dependency.

APAR PH50756 delivered the functional code for the accelerator support for large IN list predicates.

SELECT INTO statement support for OPTIMIZE FOR n ROWS

Starting at application compatibility level V13R1M503 or higher, you can specify an optimize-clause in SELECT INTO statements to enable Db2 to consider access paths that use a sort. SELECT INTO statements always return a single row. However, you can use OPTIMIZE FOR 2 ROWS can be specified to influence the Db2 optimizer.

When FETCH FIRST 1 ROW ONLY is specified, Db2 also applies a sort avoidance preference that is associated with OPTIMIZE FOR 1 ROWS during access path selection. However, sometimes the avoiding of any sort can result in a more expensive (in total cost) access path being used. If this situation occurs, OPTIMIZE FOR 2 ROWS can be specified in the SELECT INTO statement to enable Db2 to consider use of a more efficient access path that uses a sort. Other integer values can be specified for n in OPTIMZE FOR n ROWS, but OPTIMZE FOR 2 ROWS is recommended for this scenario.

APAR PH50010 delivered the functional code for optimize-clause support for SELECT INTO statements.

How to activate function level 503

For information about how to activate the new capabilities function level 503 in your Db2 13 for z/OS  environment, see How to activation function level 503 in the online product documentation.

#Db2forz/OS  #db2z13 #Db2Znews