Overview of what's new in Db2 13

Db2 13 introduces new capabilities and enhancements for simplified migration, SQL enhancements, applications management, IBM® Z hardware synergy, availability and scalability, performance, and more!

Many of the new capabilities in Db2 13 take effect when you activate function level 500 or higher in Db2 13. The function level for each new capability is identified here.

Tip: This topic contains a comprehensive list of all new capabilities and enhancements available in Db2 13. It is most useful if you are preparing to install or migrate to Db2 13 for the first time. If you are looking for descriptions of the most recent new function APARs, or new capabilities in specific Db2 13 function levels, see these topics instead:

Contents

Simplified migration to Db2 13

Availability of Db2 13 new function

Migrations to Db2 13 for z/OS® use a single phase. As in migrations to Db2 12, you use function levels to control the availability of most new function in Db2 13. For a complete list of available function levels, see Db2 13 function levels.

At general availability, Db2 13 includes the following function levels:

Function level 100
Db2 starts at function level 100 (V13R1M100) during migration to Db2 13, and fallback and coexistence with Db2 12 in data sharing remain possible. Many new capabilities in Db2 13 remain unavailable. For more information, see Function level 100 (for migrating to Db2 13 - May 2022).
Function level 500
Activating function level 500 (V13R1M500) prevents coexistence with and fallback to Db2 12. Function level 500 is also the first opportunity for applications to use many of the new capabilities in Db2 13. However, new capabilities that depend on Db2 13 catalog changes remain unavailable. For more information, see Function level 500 (for migrating to Db2 13 - May 2022).
Function level 501
Function level 501 (V13R1M501) is the first opportunity after migration to Db2 13 for applications to use new features and capabilities that depend on catalog changes in Db2 13. For more information, see Function level 501 (Db2 13 installation or migration - May 2022).

For more information, see Activating Db2 13 function level 500 or higher.

Simplified catalog migration
When migrating to Db2 13, you use the CATMAINT utility invoked by job DSNTIJTC to control the timing of the migration as in earlier releases, but it does not make any structural changes to the Db2 catalog. After the V13R1M100 CATMAINT completes, Db2 is at catalog level and function level V13R1M100. This is a change from Db2 12, which started with catalog level V12R1M500 at function level 100.

The first changes to catalog objects are delayed until you tailor the Db2 catalog for activation of function level 501.

For more information, see Migrating your Db2 subsystem to Db2 13.

Subsystem parameter simplification
Function level 100 introduces changes to the default values for various subsystem parameters to match current best practices. It also removes a number of obsolete subsystem parameters. For a list of these changes, see Subsystem parameter changes in Db2 13.

SQL enhancements and applications management in Db2 13

Record dependencies and validity at the statement level for packages

Starting in function level 502, when a package is bound or rebound with the new DEPLEVEL(STATEMENT) option, package invalidation is handled at a statement level. If one or more statements in a package are invalidated, a value of 'S' is recorded in the VALID column of the SYSPACKAGE or SYSPACKCOPY catalog table to indicate that the package is partially invalidated. The VALID column of the SYSPACKSTMT or SYSPACKSTMTCOPY catalog tables indicates the specific statement that is invalidated. A partially invalidated package is handled in the same manner as an invalidated package on REBIND and in an auto bind situation.

The new dependencies recorded are only for statement-level object dependencies. Only changes to an object invalidate a package at the statement level. Any changes in authorization privileges still invalidate a package at the package level.

Package dependencies continue to be stored in the SYSPACKDEP catalog table. If a package is bound with the DEPLEVEL(STATEMENT) option, a new catalog table, SYSPACKSTMTDEP, stores the dependencies between a package statement and the objects referenced by that statement. The SYSPACKSTMTDEP catalog table contains the statement-level dependencies for a current package and all of the package copies.

PH47560 delivered the functional code to record dependencies and validity for a package at the statement level.

SQL Data Insights
Function level 500 delivers SQL Data Insights (SQL DI), an integrated solution that brings deep learning AI capabilities into Db2. SQL DI uses unsupervised neural networks to generate a specialized vector-embedding model called database embedding, which can be referenced through SQL queries called "cognitive intelligence" queries.

The SQL DI user interface is an optional feature available at no additional charge with Db2 13, which provides the user interface for training models and exploring data insights. Db2 provides the in-database infrastructure for training and model table (vector table) management. Db2 also provides three new built-in cognitive functions to speed up query execution.

For more information, see Running AI queries with SQL Data Insights.

Increased flexibility for package ownership
Starting at function level 500, you can specify the type of owner for a plan, package, or service, or the type of package owner for an SQL PL routine. The owner can be a role or an authorization ID. The default owner is a role in a trusted context that is defined with the role as object owner and qualifier attributes, otherwise the default owner is an authorization ID.

For more information, see the PACKAGE OWNER clause of CREATE PROCEDURE (SQL - native) and the OWNERTYPE option of the OWNER bind option.

Column names longer than 30 bytes

Function level 100 extends the maximum length of a column name from 30 bytes of EBCDIC, up to 128 bytes with limited support for using the longer column names. The longer column names can be used when the TABLE_COL_NAME_EXPANSION subsystem parameter setting is ON. Although you can now define a column with a name up to 128 bytes, column names with a length greater than 30 bytes of EBCDIC might be truncated on a character boundary. Column names returned in an SQLDA contain 30 bytes at most. APIs that do not use the SQLDA to obtain a column name might return complete column names.

For more information, see Column names longer than 30 bytes and TABLE_COL_NAME_EXPANSION in macro DSN6SPRM.

Memory usage reduced for REBIND with APREUSE
Function level 100 introduces reduced storage usage during BIND/REBIND for queries that involve many tables. This enhancement optimizes Db2 13 and improves its performance without changing how you configure, monitor, and use Db2.
Db2 controlled sysplex workload balancing
Starting in function level 502, you can enable sysplex workload balancing (WLB) on a Db2 for z/OS server.

sysplex WLB functionality (also called transaction-level workload balancing) on Db2 for z/OS servers provides high availability for client applications that connect directly to a data sharing group by balancing work among members of a data sharing group at the start of a transaction. Db2 for z/OS server returns a list of available members with weights provided by WLM . At the start of a transaction, the client application chooses the member with the best weight from the list. Configuration parameters are available on clients (Java and non-Java) to enable this functionality.

To enable use of this new capability, function level 502 introduces the following new keywords for the -MODIFY DDF (Db2) command.

Start of changeRQSTWLBEnd of change
Start of change Specifies that Db2 returns to the remote client a request to enable sysplex workload balancing. This option can only be specified if the subsystem is part of a data sharing group. This option has group-wide scope. The decision to honor sysplex workload balancing request lies with the client.
Notes:
  • The RQSTWLB option applies for Db2 for Linux®, UNIX, and Windows 11.5 or later (CLI Db2 code release "SQL11050" and IBM Data Server Driver for JDBC and SQLJ 4.26.14 or later.)
  • If RQSTWLB is used, applications might encounter additional communication errors such as SQL codes -30108 and -20542. Start of changeThese errors are removed by default with Db2 11.5.8 or later client drivers, and the client property enableseamlessfailovererrorcodes controls whether they are issued.End of change
*
The option is enabled for entire data-sharing group. This is the default option.
location-name
The option is enabled for remote clients that access the data-sharing group using the location-name as the data source.
alias-name
The option is enabled for remote clients that access the data-sharing group using the specified alias-name as data source.
End of change
Start of changeDFLTWLBEnd of change
Start of change Db2 honors the sysplex workload balancing option requested by the client. This option can only be specified if the subsystem is part of a data sharing group. This option has group-wide scope. DFLTWLB is the default installed option.
*
The option is enabled for entire data-sharing group. This is the default option.
location-name
The option is enabled for remote clients that access the data-sharing group using the location-name as the data source.
alias-name
The option is enabled for remote clients that access the data-sharing group using the specified alias-name as data source.
End of change

PH48253 delivered the functional code for Db2 controlled sysplex workload balancing.

Support for UNI_90 locale for the LOWER, TRANSLATE, and UPPER built-in functions

APAR PH47187 (September 2022) adds support to specify that the LOWER, TRANSLATE, and UPPER built-in functions use z/OS Unicode Conversion Services only with "normal" casing capabilities and use Unicode Standard 9.0.0. Db2 now supports a new locale, UNI_90, which specifies that z/OS Unicode Conversion Services is used with only "normal" casing capabilities and use Unicode Standard 9.0.0.

Before to this APAR, Db2 supports the following locales to indicate that Db2 should use z/OS Unicode Conversion Services to perform the upper or lower conversion with "normal" or "normal" and "special" casing capabilities.

  • 'UNI' uses Unicode version 3.0.0 (which is the default for z/OS Unicode Services).
  • 'UNI_SIMPLE' uses the latest version of the Unicode Standard that z/OS Unicode Conversion Services supports.
  • 'UNI_60' specifies thatz/OS Unicode Conversion Services is used with only "normal" casing capabilities and use Unicode Standard 6.0.0.

For more information, see the following related topics:

List prefetch for MERGE statements

APAR PH47581 (September 2022) introduces list prefetch as a possible access path for the MERGE statement. Index access with list prefetch can sometimes be used when an index column is being updated by the MERGE statement. Prior to this APAR, such MERGE statements would likely resort to table space scans. For more information, see the following related topics:

IBM Z hardware synergy in Db2 13

Db2 13 introduces the following new capabilities that take advantage of the synergy between IBM Z hardware and Db2 for z/OS.

Expanded SORTL usage with learning from execution (IBM z15™)
Function level 100 introduces expanded SORTL usage based on machine learning on the amount of storage and the number of records being sorted, when run on IBM z15 or later processors. This enhancement optimizes Db2 13 and improves its performance without changing how you configure, monitor, and use Db2.
Db2 support for z/OS continuous compliance
Customers are looking for solutions that provide evidence that they can trust the security of z/OS systems. z/OS 2.5 introduces new SMF type 1154 records that provide evidence of security compliance. Participating components and products can collect and write compliance data to their associated SMF 1154 subtype records. Function level 100 adds the capability to collect evidence on Db2 subsystems' compliance by writing SMF 1154 subtype 81 records. For more information, see Db2 evidence for z/OS continuous compliance and What is new in z/OS (V2R4 - V2R5).
IBM z16 group buffer pool (GBP) residency time
Starting in function level 100, two new statistics are added to relevant group buffer pool statistics storage areas:
  • The weighted-average time a data area resides in a storage class before it is reclaimed, or 0 if the group buffer pool has not been reclaimed.
  • The weighted-time a directory entry resides in a storage class before it is reclaimed, or 0 if the group buffer pool has not been reclaimed.
The new residency time statistics are supported only if Db2 13 runs in the following environment:

You can access these metrics with the IFCID record trace and the -DISPLAY GROUPBUFFERPOOL command. For more information, see "DSNB820I: Average residency times" in DSNB750I.

DECFLOAT datatype support for PL/I and C with DCLGEN

APAR PH47453 (August 2022) enhances DCLGEN to support the DECFLOAT data type for the PL/I and C programming languages.

For more information, see the following related topics:

Availability in Db2 13

Db2 13 introduces the following new capabilities that improve the online availability of your data and database applications on Db2 for z/OS.

DBAT availability improvements
Function level 100 introduces changes to Db2 13 DBAT termination processing to support the following objectives:
  • Reduction of the overall frequency and number of DBAT terminations.
  • Reduction of the number of concurrent DBAT terminations that are caused by a short-term increase in DBAT usage.
This enhancement optimizes Db2 13 and improves its performance without changing how you configure, monitor, and use Db2.
Online conversion of tables from growth-based (PBG) to range-based (PBR) partitions

Function level 500 introduces the capability to convert the partitioning scheme of a table with growth-based partitions (in a PBG table space) to use range-based partitions (in a PBR table space). The conversion can be completed as an online change with minimal impact to your applications.

PBG and PBR universal table spaces (UTS) are the strategic table space types for tables in Db2 for z/OS. PBG table spaces are the default UTS type, and they are well-suited for small to medium-sized tables. However, if an existing table in a PBG table space grows too large, performance degradation or data and index management issues might arise. Consider converting from PBG to PBR when that occurs.

To complete the conversion, you issue an ALTER TABLE statement with the new ALTER PARTITIONING TO PARTITION BY RANGE clause and run the REORG TABLESPACE utility to materialize the pending change. The table space for the table is converted to PBR with relative page numbering (RPN).

For more information, see Converting tables from growth-based to range-based partitions and ALTER PARTITIONING TO PARTITION BY RANGE in ALTER TABLE.

Removed stacking limitations for PBG to PBR conversions
Starting in function level 500 or higher with APAR PH51359 (December 2022), Db2 13 supports stacking of certain pending data definition changes when a table in a partition-by-growth (PBG) table space is converted to partition-by-range (PBR). That is, the pending definition changes in following table can now be issued together and materialized by a single execution of the REORG utility. This capability is especially useful if you need to enlarge the partition data set sizes to accommodate the distribution of data into the partitions, alter the columns to be used as partitioning keys, or alter other table space or index attributes.
Object level Supported stacked pending definition changes for PBG to PBR conversion
Table space
  • BUFFERPOOL
  • DSSIZE
  • SEGSIZE (excluding conversion to UTS)
  • MEMBER CLUSTER
Table
  • ALTER COLUMN
  • DROP COLUMN
Index
  • BUFFERPOOL
  • COMPRESS

Before this change, Db2 issues SQLCODE -20385 if you try to issue any of these alterations when a PBG to PBR conversion is pending, or in the opposite situation, so at least two executions of the REORG utility are required to complete any of these changes if they are needed for the conversion.

For more information, see the following related topics:

Increased control for applications over how long to wait for a lock
Function level 500 introduces the CURRENT LOCK TIMEOUT special register and the SET CURRENT LOCK TIMEOUT SQL statement to allow the lock timeout value to be set at the application level. So, you can set a lock timeout interval that suits the needs of a specific application, or even an individual SQL statement. Doing so minimizes application lock contention and simplifies portability of applications to Db2, without the need to assign the application to a separate Db2 subsystem.

The value of the CURRENT LOCK TIMEOUT special register overrides the value of the IRLMRWT subsystem parameter. It applies to certain processes related to locking, like the claim or drain of an object and cached dynamic statement quiescing.

For more information, see CURRENT LOCK TIMEOUT and SET CURRENT LOCK TIMEOUT.

You can limit use of CURRENT LOCK TIMEOUT by setting the new SPREG_LOCK_TIMEOUT_MAX subsystem parameter. For more information, see LOCK TIMEOUT MAX (SPREG_LOCK_TIMEOUT_MAX subsystem parameter).

You can also use Db2 profile tables to specify an assignment for the CURRENT LOCK TIMEOUT special register, for both remote and local threads. See Setting special registers by using profile tables.

Allow applications to specify a deadlock resolution priority
Function level 501 introduces the SYSIBMADM.DEADLOCK_RESOLUTION_PRIORITY built-in global variable to allow an application to specify a priority to use when resolving a deadlock situation with other threads. When an application sets and uses this built-in global variable (by using a SET assignment-statement SQL statement), the Db2 subsystem uses that value as a relative weighting factor to resolve deadlock situations with other threads.

For more information, see DEADLOCK_RESOLUTION_PRIORITY.

You can also use Db2 profile tables to specify values for the new SYSIBMADM.DEADLOCK_RESOLUTION_PRIORITY built-in global variable for both remote and local applications. See Setting built-in global variables by using profile tables.

Profile table enhancements for application environment settings

Db2 13 introduces the capability to use system profiles for local applications in certain situations. Previously, the initial values for special registers and system built-in global variables can be specified in the Db2 profile tables, but they are used only for initialization with distributed threads. The new Db2 profile table support for local applications requires Db2 to be started with the DDF subsystem parameter set to AUTO or COMMAND. See DDF STARTUP OPTION field (DDF subsystem parameter).

You can use Db2 profiles tables for both local and remote applications in the following situations:

Db2 function level New Db2 profile table support
Function level 500 or higher
Function level 501 or higher
Ability to delete an active log data set from the BSDS while Db2 is running
Function level 500 introduces the new REMOVELOG option for the -SET LOG command to support online removal of an active log data set from the BSDS, eliminating the need to stop Db2 to accomplish the task by using the offline utility DSNJU003. The -SET LOG REMOVELOG command deletes the specified log from the BSDS if it is not in use or mark the log REMOVAL PENDING if it is in use.

To provide monitoring of the current active log status for log data sets with REMOVAL PENDING status, function level 500 also introduces the DETAIL option for the -DISPLAY LOG command. It shows information regarding REMOVAL PENDING status for local active log data sets. The output from the utility DSNJU004 also shows the REMOVAL PENDING status where applicable.

For more information, see Deleting an active log data set from the BSDS with the -SET LOG command.

Relative page numbering for new PBR table spaces

Starting in function level 100, the default value of the PAGESET_PAGENUM subsystem parameter is changed to RELATIVE. The PAGESET_PAGENUM subsystem parameter specifies the default value that Db2 uses when you omit the PAGENUM option in CREATE TABLESPACE or CREATE TABLE statement for a partition-by-range (PBR) table space. That is, it specifies whether Db2 creates the table space and associated partitioned indexes to use relative page numbers (RPN) or absolute page numbers (APN) across partitions. RPN is the strategic direction for PBR table spaces in Db2. If you accept the new default and create all new PBR table spaces with relative page numbers, you can avoid costly future conversions. Converting from absolute to relative pages numbers always requires a REORG of the entire table space.

See PAGE SET PAGE NUMBERING field (PAGESET_PAGENUM subsystem parameter).

Improved concurrency for altering tables for DATA CAPTURE

Function level 500 introduces a concurrency improvement for ALTER TABLE statements that change the DATA CAPTURE attribute of tables. With this enhancement, Db2 no longer waits for other statements that depend on the altered table to commit. As a result, the DATA CAPTURE alteration can now succeed even when concurrent statements are running continually against the table.

Earlier Db2 releases quiesce the following objects that depend on the altered table as part of the DATA CAPTURE alteration:

  • Static packages
  • Cached dynamic SQL statements

Because the DATA CAPTURE alteration waited for applications that depended on the altered table to commit, continuous concurrent activity on the table might cause the ALTER TABLE statements to fail.

The new DATA CAPTURE attribute now takes effect immediately when the processing completes, even before the ALTER statement commits. As a result, concurrent statements on the same Db2 member might write out different log formats in the same transaction. For more information, see Altering a table to capture changed data.

CREATE TABLESPACE uses MAXPARITIONS 254 by default

At application compatibility level V13R1M500 or higher, CREATE TABLESPACE statements use MAXPARTITIONS 254 by default.

When MAXPARTITIONS 256 is explicitly specified, the default DSSIZE varies from 4 G to 32 G depending on the page size. However, starting with application compatibility level V12R1M504, when MAXPARTITIONS is not explicitly specified, Db2 12 use MAXPARTITIONS 256 by default, but the default DSSIZE is always 4 G regardless of the page size.

This apparent inconsistency avoided a risk of failure for existing statements, where the default data set size might be greater than 4 G depending on the page size. The statements might fail with SQLCODE -904 with reason code 00D70008 if the data sets for the table space are not associated with a DFSMS data class that is specified with extended format and extended addressability.

With MAXPARTITIONS 254 as the default, the result is now consistent regardless of whether MAXPARTITONS is explicitly specified. The calculated default DSSIZE is always 4 G.

See the MAXPARITIONS and DSSIZE descriptions in CREATE TABLESPACE.

Db2 controlled sysplex workload balancing
Starting in function level 502, you can enable sysplex workload balancing (WLB) on a Db2 for z/OS server.

sysplex WLB functionality (also called transaction-level workload balancing) on Db2 for z/OS servers provides high availability for client applications that connect directly to a data sharing group by balancing work among members of a data sharing group at the start of a transaction. Db2 for z/OS server returns a list of available members with weights provided by WLM . At the start of a transaction, the client application chooses the member with the best weight from the list. Configuration parameters are available on clients (Java and non-Java) to enable this functionality.

To enable use of this new capability, function level 502 introduces the following new keywords for the -MODIFY DDF (Db2) command.

Start of changeRQSTWLBEnd of change
Start of change Specifies that Db2 returns to the remote client a request to enable sysplex workload balancing. This option can only be specified if the subsystem is part of a data sharing group. This option has group-wide scope. The decision to honor sysplex workload balancing request lies with the client.
Notes:
  • The RQSTWLB option applies for Db2 for Linux, UNIX, and Windows 11.5 or later (CLI Db2 code release "SQL11050" and IBM Data Server Driver for JDBC and SQLJ 4.26.14 or later.)
  • If RQSTWLB is used, applications might encounter additional communication errors such as SQL codes -30108 and -20542. Start of changeThese errors are removed by default with Db2 11.5.8 or later client drivers, and the client property enableseamlessfailovererrorcodes controls whether they are issued.End of change
*
The option is enabled for entire data-sharing group. This is the default option.
location-name
The option is enabled for remote clients that access the data-sharing group using the location-name as the data source.
alias-name
The option is enabled for remote clients that access the data-sharing group using the specified alias-name as data source.
End of change
Start of changeDFLTWLBEnd of change
Start of change Db2 honors the sysplex workload balancing option requested by the client. This option can only be specified if the subsystem is part of a data sharing group. This option has group-wide scope. DFLTWLB is the default installed option.
*
The option is enabled for entire data-sharing group. This is the default option.
location-name
The option is enabled for remote clients that access the data-sharing group using the location-name as the data source.
alias-name
The option is enabled for remote clients that access the data-sharing group using the specified alias-name as data source.
End of change

PH48253 delivered the functional code for Db2 controlled sysplex workload balancing.

Scalability in Db2 13

Db2 13 introduces the following capabilities to improve the scalability of your data and database applications on Db2 for z/OS.

Reduced ECSA storage for IFI buffers

Db2 13 reduces the use of ECSA storage for IFI buffers from a maximum of 50 MB to a fixed 8 MB.

Function level 100 reduces the use of ECSA storage for IFI buffers to a maximum of 25 MB. Then, after function level 500 is first activated, it is further reduced to 8 MB. The storage behavior that is introduced in function level 500 continues even if you later activate function level 100*.

To compensate for the reduction in ECSA storage, you must set aside an extra 50 MB for HVCOMMON and 25 MB for private storage. You can reduce the ECSA storage after function level 500 is activated and Db2 starts using the new storage pools. When Db2 uses the new storage pools, the use of ECSA for the retrieval of IFI records noticeably decreases. You can monitor use of the new storage pools by starting the statistics trace to collect IFCID 0225. Then, you can check the SHARED / COMMON storage summary report in the formatted IDCID 225 SMF trace record.

For more information about ECSA storage requirements, see Calculating the storage requirement for the extended common service area.

Reduced ECSA storage use for distributed data facility (DDF) processing
Function level 100 reduces the amount of ECSA storage that is used for processing DDF threads to be equivalent to processing local threads. The previous recommendation was an extra 2 KB per DDF thread. For more information, see Calculating the storage requirement for the extended common service area.
Reduced agent local below-the-bar (BTB) storage

Starting in function level 100, Db2 supports a greater number of concurrent threads, by using above-the-bar (ATB) agent-local storage for statement text and attribute strings for dynamic SQL statements. In earlier releases, Db2 kept a copy of dynamic SQL statement text and attribute strings in agent local below-the-bar (BTB) storage while the statement is being prepared and executed.

For any specific thread, multiple dynamic SQL statements can be executing depending on the nesting level. The maximum length of an SQL statement is 2 MB, but much more storage can be allocated and the consumption of BTB storage could prevent the number of threads from scaling.

This enhancement optimizes Db2 13 and improves its performance without changing how you configure, monitor, and use Db2.

Improved storage monitoring and contraction
Function level 100 introduces the following enhancements to provide storage constraint relief:
  • When below-the-bar Db2 storage consumption exceeds 64-percent threshold, Db2 automatically begins contraction of private storage pools.
  • When extended common service area (ECSA) storage consumption exceeds the 85-percent threshold, Db2 automatically begins contraction of storage pools that are allocated in the ECSA.

In both cases, the storage contraction stops after storage consumption drops below the threshold.

This enhancement optimizes Db2 13 and improves its performance without changing how you configure, monitor, and use Db2.

Improved Db2 installation and migration process for customizing the amount of private storage for IRLM locks

In the MAX STORAGE FOR LOCKS field on installation panel DSNTIPJ, you can specify the maximum amount of private storage above the 2 GB bar for the IRLM lock control structure. In earlier Db2 releases, you can specify a value of only up to 102400 megabytes. Starting in Db2 13, you can specify a value of up to 16384 petabytes.

For more information, see MAX STORAGE FOR LOCKS field and MAX LOCK STORAGE UNIT field.

Real-time statistics scalability
As data volumes become larger, the widths of some columns in the real-time statistics tables are not large enough to accommodate larger values. In addition, during high volume processing, lock escalation might occur on the real-time statistics history table spaces. Lock escalation can negatively affect concurrency and performance.

Starting after function level 501 is activated, the following changes to the real-time statistics tables and table spaces are introduced to provide greater capacity and concurrency:

  • In real-time statistics tables SYSIBM.SYSTABLESPACESTATS and SYSIBM.SYSINDEXSPACESTATS, and their associated history tables SYSIBM.SYSTABSPACESTATS_H and SYSIBM.SYSIXSPACESTATS_H, some column data types are BIGINT instead of INTEGER, or INTEGER instead of SMALLINT.
  • Lock escalation is disabled on the following table spaces: DSNDB06.SYSTSTSS and DSNDB06.SYSTSISS for the RTS tables; and DSNDB06.SYSTSTSH and DSNDB06.SYSTSISH for the RTS history tables

For more information, see SYSTABLESPACESTATS catalog table and SYSINDEXSPACESTATS catalog table.

This enhancement optimizes Db2 13 and improves its performance without changing how you configure, monitor, and use Db2.

More efficient cleanup for above-the-bar storage

Function level 100 introduces improvements to how Db2 manages and frees above-the-bar storage, especially to reduce the disruptive impact of issuing excessive IARV64 REQUEST(DISCARDDATA) service requests.

Db2 13 no longer issues the IARV64 REQUEST(DICARDDATA) request during thread deallocation or at certain intervals of COMMIT, and enhanced storage management is no longer controlled by the REALSTORAGE_MANAGEMENT subsystem parameter, which is also removed. In Db2 13, the storage is returned to the memory object. A system-level timer drives contraction for the memory object to release unused frames back to z/OS. Also, Db2 13 periodically checks the available free frames before the LPAR starts to page (by using the z/OS calculations for available free frames and LO threshold). If this value becomes lower than 5 times the z/OS calculated OK threshold, the memory object contraction is triggered.

SPT01 and SYSLGRNX table spaces are converted to DSSIZE 256 GB
Starting in function level 500, the first time that the REORG TABLESPACE utility runs for the following directory objects, it converts the DSSIZE to 256 GB.
  • DSNDB01.SPT01 to resolve issues that are related to the removal of the SPT01_INLINE_LENGTH subsystem parameter by APAR PH24358 in Db2 12.
  • DSNDB01.SYSLGRNX in anticipation of future growth in this table for increasing workloads and conversions of non-UTS table space to UTS.

The conversion is automatic and does not require any special utility syntax. For more information, see Function level 500 (for migrating to Db2 13 - May 2022).

More concurrent open data sets in z/OS 2.5
In function level 100 and z/OS 2.5 or later, dynamic allocation processing supports system work blocks (SWBs) for data sets that are in 64-bit storage. This new dynamic allocation function helps reduce below-the-bar storage usage for address spaces that allocate large numbers of data sets.
To enable this feature, complete one of the following actions:
  • Update the ALLOCxx parmlib member to set the SYSTEM SWBSTORAGE value to ATB. The default value is SWA, which indicates that SWBs reside in 24-bit storage or 31-bit storage. ATB indicates that SWBs are allowed to reside in 64-bit storage.
  • Issue system command SETALLOC SYSTEM,SWBSTORAGE=ATB.
It is best to update the ALLOCxx parmlib member because the change remains effective across IPLs. If the SETALLOC command is used to enable SYSTEM SWBSTORAGE, you must restart Db2 for the change to take effect.

Previously, the CLIST calculation for data set storage size used 5 KB per open data set. With the new dynamic allocation function, the storage that is required per open data set is reduced to 4 KB. You must adjust the calculation for data set storage size. For more information, see Calculating data set control block storage.

Performance in Db2 13

Sort optimization
Function level 100 introduces enhanced sort optimizations, which were previously introduced for ORDER BY and GROUP BY processing. It applies them to improve the performance of certain operations, such as the following processing enhancements:
  • Machine-generated code support for DECFLOAT processing.
  • Support for the following enhancements for GROUPING SET, multiple DISTINCT, and PERCENTILE processing:
    • Machine-generated code support.
    • Sort processing can use its own work file.
    • A check for ordered data in the first iteration of a sort.
    • Larger sort trees can be used.
  • SUBSTR support for the LISTAGG built-in function, if the start position and length for SUBSTR is a constant.
  • Support for avoiding rereading of a single work file, if the sort work file is used, and if IBM Watson® Machine Learning for z/OS is enabled.
  • Support for reducing the length of long VARCHAR keys, if the last key in an ORDER BY list is a VARCHAR over 100 bytes, and if IBM Watson Machine Learning for z/OS is enabled.

This enhancement optimizes Db2 13 and improves its performance without changing how you configure, monitor, and use Db2.

Improved locking for INSERT to partition-by-growth (PBG) table spaces
Function level 100 introduces retry logic for INSERT operations. An extra attempt is made to obtain a partition lock on a PBG table space after a failed first attempt, thereby increasing the success rate of INSERT operations.

Before this enhancement, only a single attempt was made to obtain a lock on the target partition. If the attempt failed, the target partition was skipped, and the next partition was evaluated. This process would continue until the INSERT operation either successfully obtained a partition lock or it finished searching all existing partitions without obtaining a partition lock.

In most cases, the duration of partition lock contention is short; however, because the INSERT operation did not make another attempt to obtain a lock on a partition after the first failed attempt, the INSERT operation terminated unnecessarily. In many cases, making an extra attempt to obtain a partition lock results in the successful completion of an INSERT operation that otherwise would fail.

This enhancement optimizes Db2 13 and improves its performance without changing how you configure, monitor, and use Db2.

Index look-aside optimization
Function level 100 introduces index look-aside optimizations, to improve performance for insert, update, and delete operations. This enhancement optimizes Db2 13 and improves its performance without changing how you configure, monitor, and use Db2.
Improved performance when using external security
Function level 100 introduces the following enhancements to improve performance for Db2 environments that use external security:
  • Db2 caches plan authorization checks that the access control authorization exit (ACAE) routine uses. Previously, successful authorization checks on the EXECUTE privilege for plans were not cached if those checks were completed by the ACAE routine. This enhancement provides consistent behavior in plan authorization cache behavior regardless of whether security is managed with Db2 facilities or with the ACAE.

    To enable plan authorization caching when the ACAE routine is being used, the AUTHEXIT_CACHEREFRESH subsystem parameter must be set to ALL and the z/OS release must be 2.5 or later. Db2 caches the results of authorization checks on the EXECUTE privilege for plans if a profile in the RACF® class MDSNPN permits access to the plan. Db2 does not cache the results if access is allowed due to administrative authority, such as the DATAACCESS or SYSADM authorities.

    For more information, see Caching of EXECUTE on plans, packages, and routines.

  • Db2 is enhanced to cache more authorization IDs per plan. For more information, see Caching authorization IDs for plans.
  • The AUTHCACH subsystem parameter is removed to simplify plan authorization cache management. Use the CACHESIZE bind option on the BIND PLAN subcommand to specify the size of the authorization cache for that plan. The default value is 4K.
  • If the AUTHEXIT_CACHEREFRESH subsystem parameter is set to ALL, the global authentication cache takes the timestamp into consideration for user IDs that were authenticated by using credentials other than multi-factor authentication (MFA). For more information, see Global authentication cache.
  • When you specify a key label for data set encryption, the specified key label cannot refer to an archived key for decryption operations only. Key labels can be specified by using the ENCRYPTION_KEYLABEL subsystem parameter or any of the following SQL statements:
    • ALTER STOGROUP
    • ALTER TABLE
    • CREATE STOGROUP
    • CREATE TABLE
    If the specified key label refers to a decryption-only archived key, the key label specification fails and returns an error message. For more information on decryption-only archived keys, see ICSF: Limit archived keys to decrypt operations only.
Fast index traversal (FTB) support for larger index keys
Function level 500 extends FTB support to unique indexes with a key size for the ordering columns up to 128 bytes and nonunique indexes with a key size up to 120 bytes. For more information, see Fast index traversal.

Db2 Utilities in Db2 13

Db2 13 introduces the following new capabilities and enhancements to Db2 Utilities Suite for z/OS:

Collection of real-time and historical information about utility execution
To improve utility management, function level 501 introduces the ability to collect real-time and historical information about utility execution. After you activate utility history collection by setting the UTILITY_HISTORY subsystem parameter to UTILITY, information about utilities is added to the SYSIBM.SYSUTILITIES catalog table. One row is inserted into the SYSUTILITIES table at the start of each utility execution. Then, information in the row is updated as the utility progresses, and final information is updated in the row when the utility execution finishes.

For more information, see Monitoring utility history, UTILITY HISTORY (UTILITY_HISTORY subsystem parameter), and SYSUTILITIES catalog table.

Page sampling for inline statistics
Beginning in function level 500, the REORG TABLESPACE and LOAD utilities can now use page sampling when they gather inline statistics. Page sampling has the potential to reduce both CPU time and elapsed time. In earlier Db2 releases, the RUNSTATS utility can use page sampling, but inline statistics that are gathered by other utilities can use row sampling only. To use page sampling for inline statistics with REORG TABLESPACE or LOAD, specify the TABLESAMPLE SYSTEM option or ensure that the STATPGSAMP subsystem parameter is set to SYSTEM (the default) or YES. In function level 500, STATPGSAMP is extended to apply to inline statistics. For more information, see the TABLESAMPLE SYSTEM option description in Syntax and options of the LOAD control statement and Syntax and options of the REORG TABLESPACE control statement.
Enhanced space-level recovery with the RECOVER utility

Starting in function level 100, the RECOVER utility supports space-level recovery (where DSNUM ALL is specified or is the default), even if the image copies were created at the partition or piece level for the following objects:

  • Universal table spaces (UTS).
  • Index spaces or indexes for a UTS.
  • XML UTS with a base table that resides in a UTS.
  • Auxiliary index spaces or indexes for an XML UTS with a base table that resides in a UTS.
  • LOB table spaces with a base table that resides in a UTS.
  • Auxiliary index spaces or indexes for LOB table spaces with a base table that resides in a UTS.

Space-level recovery of these supported object types is processed as partition-level or piece-level recoveries in the RESTORE phase. Db2 catalog and directory objects that meet the criteria above are supported by this enhancement. When the list contains a mixture of supported and unsupported object types, recovery behavior for the unsupported object types is the same as Db2 12 or earlier releases.

When the RECOVER utility is invoked (with DSNUM ALL specified or as the default) in Db2 12 or earlier releases, for space-level recovery of table spaces, index spaces, or indexes, an error message is issued if the image copies were created at the partition or piece level. The DSNU512I (DATASET LEVEL RECOVERY IS REQUIRED) error message indicates that recovery cannot be done at the space level. Recovery must instead be requested at the partition or piece level. Objects with these errors are not recovered and the RECOVER ends with RC8 indicating errors were encountered.

For more information, see Recovering a data set or partition.

REPAIR utility WRITELOG for decompression dictionaries
Function level 100 introduces the capability to write a decompression dictionary log record up to the maximum log record size supported by Db2. This capability can be used after you run an application or utility that builds a new dictionary without writing the old one to the log. So, it is useful for replication products that would otherwise require a refresh of the replication target. After the decompression dictionary is successfully written, the REPAIR utility issues message DSNU3335I with the location of the log record. Applications can then use this information to insert a SYSIBM.SYSCOPY record.

For more information, see the option descriptions for TYPE X’4002 and SUBTYPE ’X’000A’ in Syntax and options of the REPAIR control statement, and DSNU3335I.

Change REORG INDEX SHRLEVEL REFERENCE or CHANGE so the NOSYSUT1 keyword is the default
Starting at function level 500, the NOSYSUT1 keyword is the default for the REORG INDEX utility when specified with the SHRLEVEL REFERENCE or CHANGE keywords. So, the utility avoids use of a work data set, which improves performance and allows REORG INDEX to use parallel subtasks to unload and to build index keys. The default value of the REORG_INDEX_NOSYSUT1 subsystem parameter is also changed from NO to YES, and YES is now the only option. So, this subsystem parameter no longer influences the behavior of REORG INDEX.

For more information, see Syntax and options of the REORG INDEX control statement and REORG TS NOPAD DEFAULT (REORG_TS_NOPAD_DEFAULT subsystem parameter).

Improved tape data set estimation for LOAD
APAR PH47800 (December 2022) improves size estimates for the LOAD utility when the input is tape data sets managed by DFSMSrmm. A more accurate row estimate results in better sort processing for LOAD PRESORT and better sorting of keys for rebuilding indexes. If you use tape data sets managed by DFSMSrmm as input to LOAD, you do not need to specify the SPACE parameter for the discard and sort input data sets. Additionally, if you use LOAD PRESORT, you no longer need to specify NUMRECS and SORTKEYS. For more information, see PH47800.

Insight, instrumentation, and serviceability in Db2 13

Improved default statistics collection granularity

Starting in function level 100, that default value of the STATIME_MAIN subsystem parameter is changed to 10 seconds. The STATIME_MAIN subsystem parameter specifies the time interval in seconds, for collection of interval-driven statistics that are not collected at the interval that is specified by the STATIME subsystem parameter. With the default statistics interval set at 60 seconds in earlier releases, Db2 database administrators and system programmers cannot identify true workload peaks by using Db2 statistics for subsystem level performance tuning and planning. Similarly, a slowdown of 5 - 15 seconds is difficult to diagnose with statistics collected at a 60-second interval.

See MAIN STATS TIME field (STATIME_MAIN subsystem parameter)

Index page split instrumentation enhancements
Function level 100 introduces IFCID 0396 to provide detailed information about index splits. When data is inserted into a base table, the corresponding indexes are modified accordingly. As a result, performance of SQL insert operations can be impacted during the index split process, where synchronous I/O is required under data sharing and the group buffer pool GBP is dependent on the related index page sets.

The existing IFCID 0359 records already contain information index split events. However, the information that is recorded is not detailed enough to identify the cause of performance issues. IFCID 0359 is also disabled by default, and it can miss capturing some abnormal index split situations.

IFCID 0396 is always enabled by default under statistics trace class 3 and performance trace class 6. An IFCID 396 record is generated when an index split is considered an abnormal split process, such as when the total elapsed time is greater than 1 second. The generated IFCID 0396 record contains information such as the DBID, PSID, member ID, URID, page number, and more. The information is helpful for both customers and IBM Support to identify the root cause of INSERT performance issues.

For more information, see the IFCID 0396 descriptions in IFCID changes and in the Trace field descriptions.

Starting after the catalog level V13R1M501 update (which requires function level 500), the following RTS columns in the SYSIBM.SYSINDEXSPACESTATS catalog table are populated. They record and aggregate general index split information since last table reorganization, index rebuild, or load replace:

Column name Data type Description
REORGTOTALSPLITS
INTEGER
The number of index splits since last reorganization or rebuild.
REORGSPLITTIME
BIGINT
Aggregated elapsed time for all index splits since last reorganization or rebuild.
REORGEXCSPLITS
INTEGER
The number of abnormal index splits (such as elapsed times greater than 1 second) since last reorganization or rebuild.

Db2 starts populating these RTS columns as soon as the catalog level V13R1M501 update completes, even before function level 501 is activated.

Accounting information on the longest wait times for common suspension types
When Db2 transactions take a long time, it is important to determine:
  • Where the transaction time is spent.
  • Whether the problem is many short suspensions or a few long suspensions.
  • Which resources the suspensions are for.
Before this enhancement, detailed performance traces were required to find this information. This enhancement simplifies the diagnosis process by providing information in Db2 accounting records for the longest wait time for a number of common suspension types. The following suspension types are included:
  • IRLM lock suspensions
  • Db2 internal latch suspensions
  • Waits for Db2 synchronous or asynchronous I/O
  • Waits for Db2 service tasks
  • Waits for page latches

For more information, see IFCID changes and the DSNWMSGS file.

Partition range support in IFCID 306 for users of replication applications
Function level 100 introduces the capability for applications that collect IFCID 306 trace records for the log read process to request filtering on a range of partitions. For more information, see the new WQLSFLG flag and WQLSDBPP mapping in Qualification fields for READS requests.
EDITPROC support in IFCID 306 for users of nonproxy mode replication applications
Function level 100 introduces the support for any user of IFCID 306 in non-proxy mode to use EDITPROC support as an on-request function. For more information, see the new values X'04', X'05', X'06', and X'07' for the 1-byte WQALLOPT field in Qualification fields for READS requests.
Improved DBAT status information in DISPLAY THREAD output

APAR PH45504 (July 2022) improves Db2 reports the status of database access threads (DBATs), so that you can distinguish between DBATS that are processing SQL requests for active transactions and threads that reached a transaction boundary and are waiting for new transaction requests. Specifically, this APAR introduces the new status values that are listed in the following table in DISPLAY THREAD command output and in IFCID148 data obtained in IFC READS calls.

Before this APAR, Db2 returns the RA status in all such cases, which makes it difficult to accurately determine the current state of DBATs that use KEEPDYNAMIC(YES) or high-performance DBAT support. That is, you cannot distinguish between threads that are processing SQL requests for active transactions and threads that reached a transaction boundary and those that are waiting for new transaction requests.

ST value Meaning
RD The DBAT is processing a request from a remote location and waiting for the next transaction. The DBAT is an active KEEPDYNAMIC-refreshable thread, and not a candidate for idle thread detection and termination. For more information, see Enabling KEEPDYNAMIC refresh for DBATs.
RH The DBAT is processing a request from a remote location and waiting for the next transaction. The DBAT is being kept active due to resources of various types being held past a commit or rollback. A DBAT with this status is a candidate for idle thread detection and termination based on the IDTHTOIN subsystem parameter setting. For more information, see IDLE THREAD TIMEOUT field (IDTHTOIN subsystem parameter).
RP The DBAT is processing a request from a remote location and waiting for the next transaction. The thread is an active high-performance DBAT, and it is not a candidate for idle thread detection and termination. For more information, see Enabling high-performance DBATs.

For more information, see the following related topics:

Improved DBAT status for MONITOR THREADS profiles in DISPLAY THREAD output
Starting in Db2 13 function level 500 or higher, improves the status values in DISPLAY THREAD output for DBATs that are queued because the MAXDBAT subsystem parameter or the except threshold for a MONITOR THREADS profile was reached. This APAR introduces the new status value RS in DSNV402I to indicate a thread that is suspended because a MONITOR THREADS profile was reached. The existing RQ value is also updated to indicate that it only applies that threads that are suspended because the MAXDBAT value was reached.

This APAR also adds a new counter in the output for the DISPLAY DDF command with the DETAIL option. The new PQDBAT counter in mess DSNL093I indicates the current number of DBATs queued because a system profile exception threshold was reached.

For more information, see the following related topics:

Product identifier (PRDID) values for specific DRDA levels
APAR PH48184 (December 2022) enhances product identifier (PRDID) values returned by Db2, so that they accurately reflect the DRDA level of the Db2 for z/OS data server, which corresponds to a specific Db2 13 function level. As before, the PRDID value is an 8-byte character value in pppvvrrm format, where: ppp is a 3-letter product code; vv is the version;rr is the release; and m is the modification level. With this APAR in Db2 13, the modification level (0 - 9 or A - Z) indicates a specific function level. For example:
  • DSN13012 for V13R1M501.
  • DSN13011 for V13R1M500.
  • DSN13010 for V13R1M100.
Before this APAR and in Db2 12, the PRDID values indicate only a range of function levels. For example, the following values are used in Db2 12 uses these values:
  • DSN12015 for V12R1M500 or higher.
  • DSN12010 for V12R1M100.

For more information, see the following related topics:

Hardware and software requirements for Db2 13

Db2 13 operates with the following 64-bit architecture IBM Z hardware and software:

Hardware: IBM System zEC12 or later
Operating system: z/OS 2.4 or later
Other software: IRLM 2.3 running at function level 3.050 or later

You can use the following command to determine the IRLM function level: MODIFY irlmproc,STATUS,ALLI

For information about system requirements for Db2 13 for z/OS, see the Program Directories for Db2 13.