With the availability of the PTF for APAR PH64907, you can now activate function level V13R1M507 (FL 507) in your Db2 13 for z/OS environment. 

Activating FL 507 introduces the following new-function capabilities in  your Db2 13 for z/OS environment:

Online conversion of table partitions from PBR to PBG

 Function level 507 introduces a valuable capability that enables DBAs to convert tables from range-based to growth-based partitions (PBR-to-PBG) with an online change, which is a key milestone on the journey to fully online schema evolution for tables that reside in partition-by-range (PBR) universal table spaces (UTS). 

In application compatibility level V13R1M507 or higher, you can issue an ALTER TABLE statement and specify ALTER PARTITIONING TO PARTITION BY GROWTH to convert an existing table in partition-by-range (PBR) universal table space (UTS) to use growth-based data partitions in a partition-by-growth (PBG) UTS. The PBR-to-PBG conversion is a pending data definition change, and you can also stack it with PBG-to-PBR conversions in the same materializing REORG operation. 

Moreover, by using a stacked "round-trip" (PBR to PBG to PBR) conversion, you can use an online change to alter the following attributes of an existing table with PBR data partitions, which previously could only be achieved by a DROP and re-create of the table and table space:

• Alter the partitioning key.
• Drop an existing partition entirely, assuming that the table is not defined with DATA CAPTURE CHANGES enabled.
• Alter limit key values of multiple partitions with a single ALTER statement.
• Insert new partitions at any position by adjusting the partitioning scheme.

The PTFs for APARs PH64949 and PH64950 delivered the functional code for online conversion from PBR to PBG. Stay tuned for a future blog with more details about these new capabilities.

Enhanced application concurrency for system temporal tables

FL 507 improves availability for applications that process highly concurrent update activities in user-defined system-period temporal tables by introducing a new system global variable. Applications can use the new SYSTIME_PERIOD_ADJUST global variable if they encounter an error situation that can occur when many concurrent UPDATE statements attempt to update the same data rows in a system-period temporal table. In this situation, timing windows can occur where a row to be inserted in a history table would have a beginning timestamp value greater than the ending timestamp. Db2 returns SQL error SQLSTATE 57062, SQLCODE -20528 when this situation occurs. 

Applications that run at APPLCOMPAT level V13R1M507 or higher, can instruct Db2 to adjust the timestamp values instead of returning the -20528 SQLCODE error, by setting the SYSTIME_PERIOD_ADJUST global variable. Db2 issues the +20528 SQLCODE warning when it makes the timestamp adjustments.

The PTF for APAR PH65039 delivered the functional code for enhanced application concurrency for system temporal tables.

LASTUSED column support in the SYSIBM.SYSPLAN catalog table

Starting in function level 507, Db2 13 populates the previously unused LASTUSED column in the  SYSIBM.SYSPLAN catalog table.

Db2 populates the LASTUSED column in the SYSIBM.SYSPLAN catalog table when a plan is created and after it is used. The LASTUSED value is set to '0001-01-01' when the plan is created, and the value is updated within 24 hours after the plan is used while real time statistics are enabled, which means that the DISABLE_EDMRTS subsystem parameter is set to NO.

BIND REPLACE and REBIND commands and automatic rebinds following commands and operations preserve the existing LASTUSED value.

For more information, see SYSPLAN catalog table and DISABLE EDM RTS field (DISABLE_EDMRTS subsystem parameter).

APAR PH64762 delivered the functional code for LASTUSED column support in the SYSIBM.SYSPLAN catalog table.

Referencing temporal and archive-enabled tables in the same SELECT statement

FL 507 introduces the capability for a single SELECT statement to reference both a temporal table, which can be an application period temporal table or system-period temporal table, and an archive-enable, if the statement does not request any historical data. Specifically, a restriction previously enforced by SQL code -20555 with reason code 1 is lifted at application compatibility level V13R1M507 or higher for SELECT statements in the following situations where no historical data is requested:

• If an application-period temporal table is referenced, a FOR BUSINESS TIME period is not specified, and the CURRENT TEMPORAL BUSINESS_TIME special register is NULL.
• If a system-period temporal table is referenced, a FOR SYSTEM TIME period is not specified, and the CURRENT TEMPORAL SYSTEM_TIME special register is NULL.
• If an archive-enabled table is referenced, the SYSIBMADM.GET_ARCHIVE global variable is set to 'N'.

For more information, see the reason-code 1 description in SQLCODE -20555.

Greater than 64GB primary and secondary space allocation quantities

Function level 507 introduces support for specifying greater than 64 GB primary and secondary allocation quantities for table spaces in the CREATE TABLESPACE and ALTER TABLESPACE statements. The primary space allocation quantity (PRIQTY) is increased to support up to 1 TB, and the secondary space allocation quantity (SECQTY) is increased to support up to 200 GB. This enhancement reduces the need to allocate extents frequently by taking advantage of the DSSIZE value and increased space allocation quantities for table spaces.

The PTF for APAR PH64760 delivered the functional code for greater than 64GB primary and secondary space allocation quantities.

Verification of earlier new-function APARs

Continuing a pattern that first was introduced with FL 504 in Db2 13, FL 507 also verifies that many previously delivered new-function APARs that take effect when the PTF is applied at any Db2 13 function level are consistently applied in your Db2 13 environment. 

These are APARs that Db2 development has determined can be applied to take effect in your Db2 environment without causing problems when the PTF not evenly applied in a data sharing group, do not require Db2 catalog changes, and do not require any changes to or otherwise disrupt your database applications. The goal is to get these valuable enhancements to customers with as few barriers as possible. 

The goal of requiring these APARs to be applied before the activation of a higher function level is to help customers and vendors, not to mention IBM Support, better understand the baseline set of enhancements from continuous delivery that are available in any given Db2 environment. 

For a list of such APARs that are verified when you activate FL 507, see the updated Function levels column in the table in New-function APARs for Db2 13. You might notice that this column has also been updated to include the effective function level for each APAR, which is the lowest function level where the enhancement introduced by the APAR can be used. In many cases FL 100 is shown, which is another way of saying that the enhancement in the APAR takes effect and can be used at any Db2 13 function level. Most of the others can be used when the Db2 is at function level 500 or higher. 

For more information about how to activate function level 507 and the new capabilities it introduced in Db2 13, see Function level 507.

#Db2Z
#Db2forz/OS