Overview of what's new in Db2 13

Db2 13 introduces new capabilities and enhancements for simplified migration, SQL enhancements, applications management, IBM® zSystems 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:
Tip: Changes from many Db2 12 APARs are built-in when you migrate to Db2 13:
  • Enhancements from many new-function APARs in Db2 12 are built-in when you migrate to Db2 13. If you do not apply the PTFs for such APARs in Db2 12, plan for these changes to take effect when you migrate to Db2 13. See the APARs with availability dates earlier than 2022-06 in New-function APARs for Db2 12.
  • Also check for other Db2 12 service-stream APARs for Db2 12 that were available before general availability of Db2 13. If you did not already complete any required actions that are described in ++HOLD data for these APARs, make plans to do so before migration to Db2 13.

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.
Migration readiness reported in DISPLAY GROUP command output

Starting in Db2 12 with APAR PH50072 (June 2023), you can determine whether your Db2 12 data sharing group or standalone Db2 subsystem is ready for migration to Db2 13 by issuing a DISPLAY GROUP command with the DETAIL keyword.

Before this APAR, it can be difficult to determine whether all Db2 data sharing members are at sufficient code level and function level, and have the fallback SPE APAR applied.

The new migration readiness report in the DISPLAY GROUP output now indicates whether your environment is ready for migration to the next Db2 release. If Db2 is not ready for migration, the report also includes reason information.

For example, the following migration readiness report indicates that a Db2 12 subsystem has a sufficient code level and the fallback SPE is applied, but it is not ready to migrate to Db2 13 because the highest activated function level is not the last Db2 12 function level:

DSN7100I  -DB2A DSN7GCMD                                             
*** BEGIN DISPLAY OF GROUP(........) CATALOG LEVEL(V12R1M500)        
                  CURRENT FUNCTION LEVEL(V12R1M100)                  
                  HIGHEST ACTIVATED FUNCTION LEVEL(V12R1M100)        
                  HIGHEST POSSIBLE FUNCTION LEVEL(V12R1M501)         
                  PROTOCOL LEVEL(2)                                  
                  GROUP ATTACH NAME(....)                            
---------------------------------------------------------------------
DB2          SUB                     DB2    SYSTEM    IRLM           
MEMBER   ID  SYS  CMDPREF   STATUS   LVL    NAME      SUBSYS PRLMPR21
-------- --- ---- --------  -------- ------ --------  ----   --------
........   0 DB2A -DB2A     ACTIVE   121510 UTEC6     PR21   PRLMPR21
---------------------------------------------------------------------
MIGRATION READINESS REPORT                                          
---------------------------                                          
DB2       CODE       SPE      MIGRATION                              
MEMBER    LEVEL      APAR     ELIGIBLE                               
--------  ---------  -------  ---                                    
........  V12R1M510  PH37108  YES                             
---------------------------------------------------------------------
MIGRATION READINESS STATUS: SUBSYSTEM IS NOT READY FOR DB2 13
REASON: HIGHEST ACTIVATED FUNCTION LEVEL NOT V12R1M510                
---------------------------------------------------------------------

The GET_CONFIG stored procedure is also updated to display the migration readiness information.

FL 504 Activation verifies that the PTF for this APAR is applied on every Db2 13 data sharing member. However, applying the PTF makes the new function available on each member, at any function level.

Tip: A follow-on APAR, PH58761 improves how the migration readiness status is displayed for Db2 13 members in coexistence situations. For more information, see PH58761.

For more information, see the following related topics:

Specify MQ administration IDs in DSNTIJRT/DSNTRIN

Starting in Db2 13 function level V13R1M100 or higher with APAR PH55025 (September 2023), you can specify a list of authorization IDs to be granted ALL privileges on the Db2 MQ tables SYSIBM.MQPOLICY_TABLE and SYSIBM.MQSERVICE_TABLE when you install or migrate Db2. A new optional configuration (DB2OPT) keyword called MQ_ADMIN_ID is added to installation job DSNTIJRT for this purpose. A new MQ ADMIN ID(s) field is also added on panel DNSTIPG1 for setting this parameter. This change helps to support appropriate separation of duties between users of MQ functions and administrators of MQ policies and services.

Before this APAR, when DSNTIJRT/DSNTRIN generates the access list for the two MQ tables based on all the authorization IDs specified in the GRANTTO parameter of each Db2 MQ function, and the result is that any user with EXECUTE privilege on any Db2 MQ function also has ALL privileges on the two Db2 MQ tables. Thus, any user that is allowed to read from or write messages into MQ queues through Db2 MQ functions is also able to delete or change entries in SYSIBM.MQPOLICY_TABLE and SYSIBM.MQSERVICE_TABLE.

This new DSNTIJRT/DSNTRIN optional configuration DB2OPT parameter serves two purposes when specified:

  • Specifying a list of authorization IDs that will be granted ALL privileges on the Db2 MQ tables.
  • Specifying that only the SELECT privilege on the MQ tables will be granted to all users with EXECUTE privilege on any of the Db2 MQ functions.

The following example shows how to specify previous behavior in MQ_ADMIN_ID parameter setting in job DSNTIJRT:

  //DSNTRIN EXEC PGM=DSNTRIN,COND=(4,LT),
  //             PARM=('Db2SSN(!DSN!) MODE(INSTALL)',
  //             ' AUTHID(!AUTHID!) SECDEFID(!SECDEFID!)',
  //             ' DEFPKOWN(!DEFPKOWN!)')
  . . .
  //DB2OPT   DD  *
    STOGROUP(SYSDEFLT)
    INDEXSTOG(SYSDEFLT)
    BP4K(BP0)
    BP8K(BP8K0)
    BP16K(BP16K0)
    BP32K(BP32K)
    LOBBP8K(BP8K0)
    LOBBP16K(BP16K0)
    LOBBP32K(BP32K)
    IMS_SECURITY(Db2)
    MQ_SECURITY(Db2)
    SOAP_SECURITY(Db2)
    RTN_PKG_APPLCOMPAT(DEFAULT)
    RTN_PKG_PLANMGMT(DEFAULT)
    MQ_ADMIN_ID(DEFAULT)

The following example shows how to specify a list of authorization IDs in the MQ_ADMIN_ID parameter to get the new behavior:


   MQ_ADMIN_ID(MQUID1,MQUID2,MQUID3)

FL 505 Activation verifies that the PTF for this APAR is applied on every Db2 13 data sharing member. However, applying the PTF makes the new function available on each member, at any function level.

For more information, see the following related topics:

SQL enhancements and applications management in Db2 13

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 special register and SET CURRENT LOCK TIMEOUT statement.

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
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. In function level 503 or lower, a partially invalidated package is handled in the same manner as an invalidated package on REBIND and in an autobind situation.

Tip: FL 504 This enhancement primarily introduces infrastructure to support a new capability that becomes available after you activate function level 504 or higher in Db2 13. For more information see Enabling autobind phase-in for packages invalidated at the statement level.

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. For more information, see Changes that invalidate packages.

Note: At function level 502 or higher, a package that is bound with DEPLEVEL(STATEMENT) option can be marked with VALID='S' if it is invalidated by a DROP FUNCTION statement. However, after the PTF for APAR PH55497 is applied, Db2 always marks a package that is invalidated by a DROP FUNCTION statement with VALID='N', regardless of the DEPLEVEL bind option for the package.

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.

Incompatible change:

An existing application might be marked with a value of 'S' in the VALID column of the SYSPACKAGE or SYSPACKCOPY table to indicate that the package has individual statements marked as invalid. There are no behavioral differences between how an invalidated package (VALID = 'N') and a partially invalidated package (VALID = 'S') are handled. The VALID value is reset to 'Y' in the same manner. However, if there are jobs or queries that look explicitly for 'N' in this column, these jobs might need to be modified to account for the new value of 'S'.

For more information, see Incompatible changes in Db2 13.

For more information, see the following related topics:

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

ORDER BY support for a fullselect that invokes LISTAGG

Function level 504 removes a restriction against specifying an ORDER BY clause in a fullselect that contains an invocation of the LISTAGG built-in function for applications that run at application compatibility level V13R1M504 or higher. For more information, see LISTAGG aggregate function.

APAR PH55596 delivered the functional code for ORDER BY support for a fullselect that invokes LISTAGG.

Minimized impact of invalidated packages with statement-level invalidation

Starting in function level 504, you can enable packages for statement-level invalidation. This new capability uses the statement-level dependency infrastructure, including the new DEPLEVEL(STATEMENT) bind option, that was introduced in function level 502. See Record dependencies and validity at the statement level for packages.

With statement-level dependencies and statement-level invalidation, applications can execute a package that is invalidated at a statement level without waiting for the completion of the automatic rebind (autobind). The application thread can execute the valid statements immediately. Any invalid statements go through incremental bind in the application thread before execution. Concurrently, Db2 initiates a special autobind, which is called autobind phase-in. When the autobind phase-in finishes successfully, the subsequent executing threads can use the new valid copy of the package, and they no longer use incremental bind for any statements.

The autobind phase-in process runs concurrently with executing application threads, and the old invalid copy becomes a phased-out copy, which is stored in the SYSIBM.SYSPACKCOPY table. The new copy of the package becomes the current copy. Subsequent executions of the package use the new valid copy. Threads that existed prior to autobind phase-in completing can also use the new current copy when they release the phased-out copy (based on the RELEASE(COMMIT) or RELEASE(DEALLOCATE) bind options) In this respect, autobind phase-in is the same as rebind phase-in. However, unlike other phase-in rebinds, autobind phase-in does not require the PLANMGMT subsystem parameter to be set to EXTENDED. For more about rebind-phase in, see Phase-in of package rebinds.

Db2 uses automatic binds only when the ABIND subsystem parameter is set to YES or COEXIST. If ABIND is set to NO when an invalid package runs, Db2 returns an error. For details, see AUTO BIND field (ABIND subsystem parameter).

Incompatible change: If incremental binds fail due to prior invalidating changes to dependent objects, applications that previously failed with SQL code -904 might start receiving different SQL codes. For example, an incremental bind for a statement that depended on a dropped table might return -204 to the application. For more information, see Incompatible changes in Db2 13.
Note: At function level 502 or higher, a package that is bound with DEPLEVEL(STATEMENT) option can be marked with VALID='S' if it is invalidated by a DROP FUNCTION statement. However, after the PTF for APAR PH55497 is applied, Db2 always marks a package that is invalidated by a DROP FUNCTION statement with VALID='N'.

For more information, see the following related topics:

APAR PH55497 delivered the functional code for minimizing the impact of invalidated packages.

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.

Enhancements for SQL Data Insights

Function level 504 introduces the following enhancements for the SQL DI capability in Db2 13:

  • New Db2 built-in AI_COMMONALITY function that computes a similarity score by using the value of the expression argument and the centroid value of the model column. You can use the function in your queries to detect the common patterns and the outliers in your data.
  • Enhanced AI_ANALOGY function with the numeric data type support. You can specify numeric data types in the arguments for the function.
  • Automatic disablement of SQL DI vector prefetching based on the AI object type and the AI cache size. When the MXAIDTCACH parameter is set to a value greater than 0 and a query invokes a SQL DI function on a table, Db2 dynamically chooses between vector prefetching and row-by-row processing to optimize the CPU usage of the function.

APAR PH55212 delivered the functional code for these SQL DI enhancements.

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 statement (SQL - native procedure) 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.
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.

FL 504 Activation verifies that the PTF for this APAR is applied on every Db2 13 data sharing member. However, applying the PTF makes the new function available on each member, at any function level.

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:

FL 504 Activation verifies that the PTF for this APAR is applied on every Db2 13 data sharing member. However, applying the PTF makes the new function available on each member, at any function level.

View management authorization enhancements

APARs PH54863 and PH54936 (July 2023) enhance Db2 13 at function level 100 or higher to allow database administrators (DBAs) with DBADM authority on databases to drop a view created for another user and select from a view without always requiring system level authority. APAR PH54936 supports the view management authorization enhancements when using RACF access control authorization exit for access control. With these APARs, DROP statements are allowed for a view if the privilege set includes DBADM authority on the database that contains one of the base tables and the subsystem parameter DBACRVW is set to YES. SELECT statements for a view are allowed if the privilege set includes the required privileges such as SELECT or EXECUTE on all the base objects.

FL 504 Activation verifies that the PTF for this APAR is applied on every Db2 13 data sharing member. However, applying the PTF makes the new function available on each member, at any function level.

For more information, see the following related topics:
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.

For more information, see the following related topics:

Starting at function level 503 or higher, redirected recovery processing is also updated to allow different ROW CHANGE TIMESTAMP column default values between source and target tables. For more information, see Running a redirected recovery.

APAR PH51185 delivered the functional code for improve default behavior for added ROW CHANGE TIMESTAMP columns.

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 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.

For more information, see "Accelerator-only support for more than 32K elements in an IN list" in IN predicate.

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

Freeing inactive packages for native REST services

Starting in Db2 13 with APAR PH54129 (June 2023), you can issue the DSN FREE PACKAGE command to free inactive packages for native REST services.

Db2 native REST services are created by invoking the REST service manager API with a createService request, or by using the DSN BIND SERVICE command. This inserts a row into the REST services table defining the service and creates an application package. You can then rebind the application package for a REST service with various options by using the DSN REBIND PACKAGE command. During rebind, inactive package copies, such as the original and previous copies, can be generated based on the PLANMGMT option. Phased-out copies of packages can be generated if already running threads have the package allocated when the rebind occurs. Invalid package copies can occur when a dependent object is changed or dropped. These types of inactive packages can build up over time when many rebinds are done.

Before this APAR, the only method of freeing inactive REST service packages is by invoking the REST service manager API with a dropService request, or by use of the FREE SERVICE command. The shortcoming to doing this is that the REST service itself is freed and all packages, both active and inactive, are freed. Also the FREE PACKAGE command allows the freeing of inactive packages only if they are not REST services, and is blocked for REST services with error message DSNT246I.

Use of the FREE PACKAGE command to free an active REST service package continues to be blocked with the DSNT264I message.

FL 504 Activation verifies that the PTF for this APAR is applied on every Db2 13 data sharing member. However, applying the PTF makes the new function available on each member, at any function level.

For more information, see the following related topics:

BIND QUERY restriction removed for certain SQL PL packages

APAR PH54056 (October 2023) relaxes a restriction against a BIND QUERY command that is issued to enforce statement-level plan hints for certain SQL PL packages.

Before this APAR, Db2 issues message DSNT281I with reason code 5 when processing a statement that has a DSN_USER_QUERY_TABLE row with a TYPE='N' column value. This restriction prevents the enforcement of statement-level access path hints for statements in SQL PL packages, which have the TYPE='N' value in the SYSPACKAGE catalog table.

FL 505 Activation verifies that the PTF for this APAR is applied on every Db2 13 data sharing member. However, applying the PTF makes the new function available on each member, at any function level.

For more information, see the following related topics:

INTERPRET scalar function

Function level 505 introduces the INTERPRET built-in function, which takes an expression that returns a built-in binary or character FOR BIT data string as its first operand and interprets it as the specified data type. The INTEPRET function is especially useful for converting hexadecimal values that identify data rows in many Db2 error messages into BIGINT values that can be used as input to the RID built-in function.

The following table shows example results from various invocations of the INTERPRET function.

Function invocation Result value
INTERPRET(BX'00000011' AS INTEGER) 17
INTERPRET(BX'0000000000B0370D' AS BIGINT) 11548429
INTERPRET(BX'616263' AS CHAR(3) CCSID 37) /ÃÄ
INTERPRET(BX'616263' AS CHAR(3) CCSID 1208) abc
INTERPRET (BX'0005C1C2C3C4C5' AS VARCHAR(5)) ABCDE
INTERPRET(BX'0003C1C2C3C4C5' AS VARCHAR(5)) ABC
INTERPRET(BX'0007C1C2C3C4C5' AS VARCHAR(7)) Error

For more information, see the following related topics:

IBM zSystems hardware synergy in Db2 13

Db2 13 introduces the following new capabilities that take advantage of the synergy between IBM zSystems 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.

FL 504 Activation verifies that the PTF for this APAR is applied on every Db2 13 data sharing member. However, applying the PTF makes the new function available on each member, at any function level.

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 statement.

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.

FL 504 Activation verifies that the PTF for this APAR is applied on every Db2 13 data sharing member. However, applying the PTF makes the new function available on each member, at any function level.

For more information, see the following related topics:

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 statement.

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 command (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.
For more information about enabling sysplex workload balancing with client drivers, see:
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 225. 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.

Dynamic management of CF lock storage by IRLM

With IRLM 2.3 at function level 50C or higher, which is included with Db2 13, IRLM can now invoke an existing capability in z/OS Sysplex Services for Data sharing (XES) to dynamically expand the coupling facility (CF) lock structure storage size. This new internal monitoring capability in IRLM can improve lock request processing throughput, by expanding the CF lock structure size to process lock requests, instead of rejecting them.

The existing XES monitoring of the CF lock structure use is defined in the coupling facility resource management (CFRM) policy as a threshold percentage value, and it is enabled with a default of 80% when it is not equal to zero. This monitoring retrieves statistics on the CF lock structure every 60 seconds when the storage usage is less than the threshold and every 30 seconds when the storage usage is equal to or greater than the full threshold. IRLM can determine the storage needed at a higher level of granularity than the existing monitoring by XES of CF structure, especially when a spike in locking activities results in rejection of lock requests due to insufficient Record List Entries (RLEs) even before z/OS has a chance to start the CF lock structure alteration.

The existing structure monitoring by XES handles storage contraction, and it contracts all eligible structures in the coupling facility by 10 percent in each cycle when the entire coupling facility is at or more than 90% full.

IRLM issues the following messages when it adjusts the CF lock structure storage size: DXR189I and DXR190I.

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(DISCARDDATA) 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.

Improved cross-partition search for INSERT to PBG table spaces

Starting in function level 100, if free space is not found in the initial target partition for an insert to a partition-by-growth (PBG) table space, Db2 13 more efficiently uses trailing empty partitions other than the last physical partitions.

In Db2 12 and later, the cross-partition search for PBG table spaces is bidirectional. That is, when an insert operation fails to find free space in the initial target partition, which is selected based on the clustering index, the partition to be searched next can be either an ascending or descending partition sequence number. The searching order is randomly decided at run time to avoid creating a “hot spot” in a single partition. When Db2 12 reaches the first physical partition during a descending partition search, it wraps around and looks at the last physical partition next. As a result, when a PBG table space has many empty partitions at the end, the descending cross-partition search algorithm in Db2 12 often uses the last physical partitions, and it can sometimes leave many unused empty partitions unused in between the first and last partitions.

To prevent this situation, Db2 13 now tracks the highest non-empty partition in the table space in real-time statistics at run time. The tracking starts when a data set is opened and continues until it is closed. When Db2 13 uses a descending cross-partition search and after it reaches the first partition, Db2 13 now searches the cached highest non-empty partition next, instead of the last physical partitions.

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.
Fast index traversal (FTB) controlling rows in SYSIBM.SYSINDEXCONTROL are read more frequently by Db2

PH57071 (October 2023) increases how frequently Db2 reads newly inserted rows for control of fast index traversal (FTB) processing in the SYSIBM.SYSINDEXCONTROL table. With this APAR, Db2 applies the newly inserted rows every 2 minutes, instead of every 10 minutes.

FL 505 Activation verifies that the PTF for this APAR is applied on every Db2 13 data sharing member. However, applying the PTF makes the new function available on each member, at any function level.

For more information, see the following related topics:
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.

For more information, see the following related topics:

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

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.

Utility object-level history

Starting in function level 504, you can collect object-level information along with utility execution information for IBM Db2 Utilities. Real time and historical information about the table spaces and index spaces for IBM Db2 Utilities executions are inserted to the SYSIBM.SYSOBJEVENTS catalog table.

System administrators and DBAs can use queries to generate, filter, order, and join the information in the SYSIBM.SYSOBJEVENTS and SYSIBM.SYSUTILITIES catalog tables to check, analyze, and compare utility executions.

For more information, see the following related topics:

The following APARs delivered the functional code for utility object-level history: PH55476, PH55914, PH55915, and PH55916.

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.

FL 504 Activation verifies that the PTF for this APAR is applied on every Db2 13 data sharing member. However, applying the PTF makes the new function available on each member, at any function level.

Additionally, if you use LOAD PRESORT, you no longer need to specify NUMRECS and SORTKEYS. For more information, see PH47800.

Utility enhancements for copying table spaces that use XML versions

Starting in Db2 13, APAR PH51434 (March 2024) enhances the following Db2 utilities to prevent inconsistencies when copying XML data from a source table to a target table when the table space contains XML versions.

  • REORG TABLESPACE
  • RECOVER with FROM
  • REPAIR with INSERTVERSIONPAGES

Unlike table space versions or index versions, Db2 uses XML versions to optimize concurrency and memory usage. For more information, see How Db2 uses XML versions.

With this APAR, when these utilities create shadow data sets or reformat a data set, they save table space version information to the page set. So, you can run one of these utilities before you copy versioned XML data to prevent problems that might occur because of XML table space version number differences between the source and target tables. These differences can occur when the REORG utility converts the source or target XML table from the 6-byte basic RBA and LRSN format to the extended 10-byte format.

An XML table space that supports multiple XML versions contains columns named START_TS and END_TS, which contain the RBA or LRSN values for the logical creation and deletion of the XML records. The data types of these columns depend on the RBA and LRSN format of the table space: they are BINARY(8) for the deprecated 6-byte basic format and BINARY(10) for the extended 10-byte format. When the REORG utility converts the format of these columns, it also increments the XML table space version number on the page set by 1. This can become an issue while copying a table space because no system page is created to indicate the difference between the version numbers. The resulting mismatch can prevent subsequent access to the data, even if both tables have the same RBA or LRSN format.

The REPAIR utility with the CATALOG option is also enhanced to reconcile the XML table space version differences on the page set after such data is copied. For best results, run REPAIR CATALOG on the XML table space to repair the XML version number on the target object after data is copied or recovered. Doing so allows subsequent insert, update, and delete statements to be able to access the data without any conflict of table version number.

However, if DSN1COPY is used to copy the data from source to target, you must ensure that the START_TS and END_TS columns have the same format in both the source and target table spaces. The REPAIR CATALOG utility cannot validate data format between two objects. It only repairs the target XML version number to match the source XML table.

FL 505 Activation verifies that the PTF for this APAR is applied on every Db2 13 data sharing member. However, applying the PTF makes the new function available on each member, at any function level.

For more information, see the following related topics:

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 396 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 359 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 359 is also disabled by default, and it can miss capturing some abnormal index split situations.

396 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 396 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 396 descriptions in IFCID changes in Db2 13 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 in Db2 13 and the DSNWMSGS file.

Long names support for timeout and deadlock messages in IRLM

Starting at function level 500, Db2 13 introduces IRLM support for long names for client information such as workstation ID, user ID, and transaction ID, in deadlock and timeout messages DSNT175I and DSNT376I.

In Db2 12 and earlier, the long name values are truncated in the message output.

With this change, Db2 13 also starts populating existing long name fields in IFICID 172 and IFCID 196 records. These fields remain unpopulated in Db2 12 and earlier.

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.

FL 504 Activation verifies that the PTF for this APAR is applied on every Db2 13 data sharing member. However, applying the PTF makes the new function available on each member, at any function level.

For more information, see the following related topics:

Improved DBAT status for MONITOR THREADS profiles in DISPLAY THREAD output
FL 500 Starting in Db2 13 at 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 message DSNL093I indicates the current number of DBATs queued because a system profile exception threshold was reached.

FL 504 Activation verifies that the PTF for this APAR is applied on every Db2 13 data sharing member. However, applying the PTF makes the new function available on each member, at any function level.

For more information, see the following related topics:

Discovery and granular control for secure TLS connectivity
APAR PH48764 (March 2023) introduces the capability to discover and enforce the use of approved authentication and encryption methods by Db2 clients. It introduces a set of actions that you can specify in the DSN_PROFILE_ATTRIBUTES_TABLE.KEYWORDS column. These actions use the pattern MONITOR product-type CONNECTIONS FOR SECURITY, where product-type is one of the following:
  • REST
  • JDBC
  • CLI
  • DB2CONNECT
  • DSN
  • * (the asterisk is a wildcard that applies for application requesters that do not match any of the more specific product types above)

The new keyword values can only be specified for profiles using the default location filtering criteria (DSN_PROFILE_TABLE - LOCATION column contains '*', '::0', or '0.0.0.0' representing any IP address). These new keyword values enable the definition of profiles, based on the application requester product type, to discover and enforce the usage of authorization mechanisms and encrypted connections.

FL 504 Activation verifies that the PTF for this APAR is applied on every Db2 13 data sharing member. However, applying the PTF makes the new function available on each member, at any function level.

For more information, see the following related topics:

Increased capacity for Db2 trace: More OP buffers
Starting in function level 504, the maximum number of OP buffers for trace monitoring with READA calls is increased from 8 to 16. The increase in the number of available OP buffers can have the following effects:
  • More monitoring capacity is available when multiple applications start traces with multiple OP destinations.
  • Monitoring applications can experience better performance when they send trace data to a larger number of destinations.
For more information, see the following related topics:

APAR PH55622 delivered the functional code for increasing the capacity for Db2 trace.

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.

FL 504 Activation verifies that the PTF for this APAR is applied on every Db2 13 data sharing member. However, applying the PTF makes the new function available on each member, at any function level.

For more information, see the following related topics:

Improved index traverse count information in DISPLAY STATS output

APAR PH51565 (January 2023) improves how Db2 13 reports index traverse count information in the TRAV. COUNT field of message DSNT380I when you issue a DISPLAY STATS command with the INDEXTRAVERSECOUNT keyword. It also adds a new FTB FACTOR field in DSNT380I, which returns a calculated value that Db2 uses to identify indexes that might benefit from the fast index traversal (FTB) capability.

Before this APAR, the TRAV. COUNT field contains the calculated FTB factor value, which can sometimes become negative, such as when index page splits occur or index-lookaside is used.

When the DISPLAY STATS command output in DSNT380I returns information about multiple indexes, they are now returned in descending order starting with the index with the highest FTB factor value.

IFCID 389 is also updated to display the FTB factor values.

FL 504 Activation verifies that the PTF for this APAR is applied on every Db2 13 data sharing member. However, applying the PTF makes the new function available on each member, at any function level.

For more information, see the following related topics:

Latch monitoring and serviceability enhancements

Starting in Db2 12 with APAR PH41756 (July 2023), the messages that Db2 issues when it boosts the priority of a long-waiting latch holder are updated to include additional information about the latch that is causing a suspension. Specifically, the DSNV491I message, which is issued with DSNV523I, is updated with field labels for all values that it returns. New fields are also added for token and ACE values, which can help you obtain client information, such as the end user id, transaction name, and workstation name.

The updated messages have the following format:

Start of change
NAME ST A REQ ID AUTHID PLAN ASID TOKEN
---- -- - --- -- ------ ---- ---- -----
text t  t num text text text hex  num
V491-LATCH=latch-addr LATCH ASID=latch-asid latch-status JOBNAME=hldr-jobname ASID=
hldr-asid CORRID=hldr-corr-id TOKEN=token-number ACE=ace-num
V490-SUSPENDED timestamp smodule soffset smodptf-level
End of change

Before this APAR, the DSNV491I message already included information such as such as the latch holder job name, latch ASID, and whether the latch is held or released but blocked by another holder. However, because it was intended for internal use by IBM Support these fields were not labeled in the message output or described in the documentation.

FL 504 Activation verifies that the PTF for this APAR is applied on every Db2 13 data sharing member. However, applying the PTF makes the new function available on each member, at any function level.

For more information, see the following related topics:

More granular accounting trace records for distributed applications

Starting in Db2 12 with APAR PH55241 (October 2023), you can request that Db2 generates more granular accounting trace records for distributed database applications, by issuing a MODIFY DDF command with the ACCTG(ALLCOMMIT) option. This option also requires that the PTF for APAR OA61811 is applied in your z/OS environment. When this option is set, Db2 generates an accounting trace record occurrence after any commit or rollback. However, Db2 still maintains the WLM enclave that was created by Db2, prior to the initial requested transaction which left open resources. Also, Db2 maintains information so that when the DBAT is terminated after completing the work requests from its client connection, it deletes the enclave and informs WLM of the transactions that were completed.

To use the ACCTG(ALLCOMMIT) option, you must also set the CMTSTAT subsystem parameter value to INACTIVE.

When you issue a MODIFY DDF command with the ACCTG(ALLCOMMIT) option, Db2 issues the following message:

DSNL302I ACCTG IS SET TO ALLCOMMIT

To return to the existing default behavior, you can issue the following MODIFY DDF command, which includes the ACCTG keyword with no option value.

-MODIFY DDF ACCTG

When you issue this command Db2 issues the following message:

DSNL302I ACCTG IS SET TO COMMIT_WITH_NO_OPEN_RESOURCES
Tip: If you specify the ACCTG(ALLCOMIT) option, but the PTF for APAR OA61811 is not applied in z/OS, or if the CMTSTAT subsystem parameter is set to ACTIVE, Db2 also issues the preceding message to indicate that the default behavior is still being used.

For existing distributed threads, the specified ACCTG option takes effect only after the thread generates an accounting trace record at a commit or rollback with no open resources. Until then, the previously saved ACCTG option for the thread continues to control when it generates accounting trace records.

When this option is set back to its default setting, or if the setting was never changed, Db2 generates an accounting trace record when no open resource exists past a commit or rollback. If any of the following resources remain open and pending, Db2 does not generate an accounting trace record:
  • Open WITH HOLD cursors
  • Declared global temporary tables that are not dropped
  • Unreleased LOB locators
  • A commit processed by a stored procedure when running with 2-phase commit rules
  • Allocated RELEASE DEALLOCATE packages (due to the -MODIFY DDF PKGREL(BNDOPT) option)
  • Allocated KEEPDYNAMIC YES packages
However, if only one the last two preceding resources types, but not both, are open after a commit or rollback, Db2 generates an accounting trace record occurrence after a commit or rollback.
Important: When the ACCTG(ALLCOMMIT) setting is used, the volume of accounting trace record occurrences increases. The size of the increase depends on whether distributed application workloads have a large number of the above open resource types. When no resources are left open after a commit or rollback, -MODIFY DDF ACCTG(ALLCOMMIT) has essentially no affect. You can also use the following subsystem parameters to control the value and size of the accounting trace records:
  • When set to a numeric value, the ACCUMACC value controls the volume of trace record occurrences.
  • When set to ON, the SMFCOMP value controls size of accounting trace records.

The DISPLAY DDF command output displays the ACCTG setting in the DSNL106I message only if the ACCTG(ALLCOMMIT) setting is in effect.

For more information, see the following related topics:

Improved processing for active log full message DSNJ110E

Starting in Db2 13 with APAR PH56288 (December 2023, when the last available active log data set is 5% or more full, Db2 keeps the last DSNJ110E message highlighted on the console until the condition is resolved. The goal is to prevent operators from overlooking this message.

FL 505 Activation verifies that the PTF for this APAR is applied on every Db2 13 data sharing member. However, applying the PTF makes the new function available on each member, at any function level.

For more information, see the following related topics:

STATIME_DDF support

Starting in Db2 12 with APAR PH56228 (December 2023), the new subsystem parameter STATIME_DDF controls the interval of location statistics trace records, IFCIDs 365, 411, and 412. All three traces now allow a time interval of up to 5 minutes.

FL 505 Activation verifies that the PTF for this APAR is applied on every Db2 13 data sharing member. However, applying the PTF makes the new function available on each member, at any function level.

For more information, see the following related topics:

More granular filtering for monitoring secure connectivity with profiles

APAR PH57811 (January 2024) introduces a capability to apply security profile rules more precisely. This capability is especially useful for enforcing security for new cloud-based clients or specific portions of the network more strictly. With this APAR applied, you can specify any of the following values in the LOCATION column for profiles that use the MONITOR product-type CONNECTIONS FOR SECURITY keyword:

  • '*', '::0', or '0.0.0.0' (for all connections).
  • Start of changeA domain name that resolves to an IP address. An example fully qualified domain name is 'stlmvs1.svl.example.com'.End of change
  • Start of changeIPV4 or IPV6 IP address.End of change
  • Start of changeIPV4 or IPV6 subnet address.End of change

Before this APAR, only the following values are supported for such profiles: '*', '::0', or '0.0.0.0'.

FL 505 Activation verifies that the PTF for this APAR is applied on every Db2 13 data sharing member. However, applying the PTF makes the new function available on each member, at any function level.

For more information, see the following related topics:

New trace destination for Db2ZAI

Function level 505 introduces a new trace destination, ZAI, that is used by some Db2ZAI Distributed Connection Control (DCC) related traces. DCC traces are processed for connection and thread related statistics and bypass writing to SMF unless the trace was started with DEST(SMF).

For more information, see the following related topics:

APAR PH56369 delivered the functional code for the trace destination for Db2ZAI.

Hardware and software requirements for Db2 13

Db2 13 operates with the following 64-bit architecture IBM zSystems 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.