Recent enhancements to Db2 12

Db2® 12 introduces the continuous delivery of new capabilities in a single service stream. Many APARs in Db2 12 deliver deactivated functional code to support future function levels. However, some introduce their enhancements in Db2 12 before you activate function level 501 or higher.

You add the code for each of the following enhancements by applying the PTF for the listed APAR. However, some of these enhancements might be controlled by the activation of function level 500, which is comparable to new-function mode in previous Db2 releases. APARs that enable activation of higher function levels are also included because you can activate any higher Db2 12 function level after you activate function level 500.

The enhancements are listed roughly in order of availability, beginning with the most recent.

For enhancements from previous years, see:

Consistent inline image copies with LOAD REPLACE (PH39300 - December, 2021)

The LOAD utility is enhanced so that if you specify REPLACE or RESUME NO, any requested inline sequential image copies are consistent and usable as a recovery point. Sequential image copies are requested by the existing LOAD options COPYDDN and RECOVERYDDN.

Previously, for LOAD to create consistent sequential image copies, you had to specify RESUME YES with SHRLEVEL NONE. (SHRLEVEL CHANGE is not valid with COPYDDN and RECOVERYDDN.) If you specified REPLACE or RESUME NO, any sequential image copies did not include changed pages caused by updates during the INDEXVAL and ENFORCE utility phases. With this APAR, any sequential image copies that are taken during a LOAD REPLACE or RESUME NO operation now include updates during the INDEXVAL and ENFORCE phases. Therefore, these copies are consistent and valid as recovery points.

This enhancement is automatic and does not require any new keywords or changes to the LOAD statement syntax.

Reduced console messages for FTB (PH41751 - December, 2021)

APAR PH41571 eliminates message DSNI070I to reduce the number of console messages issued when fast index traversal (FTB) is used in Db2 12. The removed DSNI070I message reported changes to the number of objects using FTB and other FTB-related status information for a Db2 subsystem. Customers reported seeing too many of these messages.

To obtain detailed information about FTB use in a Db2, you can use IFCID 389 trace information, or issue the DISPLAY STATS command with the INDEXMEMORYUSAGE option.

Support for disabling the filtering of alter or modify log record collection in IFCID 306 trace applications (APAR PH40937 - December, 2021)

APAR PH40937 introduces the capability for applications that collect IFCID 306 trace records to receive alter or modify log records for all table spaces. Filtering by DBID and PSID for those log records is not performed. This capability is primarily for support of IBM Db2 Analytics Accelerator V7.5 using IBM Integrated Synchronization, but any application that collects IFCID 306 records can use it.

Longer password phrase support for connections to Db2 for z/OS (APAR PH40443 - November, 2021)

APAR PH40443 enhances the CONNECT statement to support longer password phrases for applications that connect to Db2 for z/OS. The authorization USING host-variable clause of the CONNECT statement now accepts password phrases up to 100 characters. With this enhancement, password phrases are also now supported for applications that use IBM Data Server Driver for JDBC and SQLJ type 2 connectivity for connections to Db2 for z/OS. However, the previous 8-character limit still applies for establishing trusted connections by using type 2 connectivity.

Improved identification of running log reader tasks from Integrated Synchronization products (APAR PH40261 - October, 2021)

APAR PH40261 enhances log reader related messages to identify the location and correlation ID of the IBM Db2 Analytics Accelerator for z/OS or the IBM Db2 for z/OS Data Gate instance that started the log reader task.

The APAR also adds a SCOPE(GROUP) option to the -DISPLAY STATS (LOGREADERTASKS) command to display information about log reader tasks running on all active members of a data sharing group.

The SYSPROC.ADMIN_COMMAND.DSN stored procedure now supports DCLGEN (APAR PH28863 - August, 2021)

DCLGEN generates a table or view declaration and puts it into a member of a partitioned data set that you can include in your program.

Prior to PH28863, the DCLGEN subcommand could be issued only from a DSN session running in either foreground or background mode, or through DB2I. With PH28863, you can also use SYSPROC.ADMIN_COMMAND_DSN to execute the DCLGEN subcommand.

This enhancement provides modern IDEs with a simple method for generating language structures for data objects in the source code. Supporting DCLGEN from a stored procedure also enables REST services to be built around it. This new capability also reduces the z/OS skills that are needed to create and submit a JCL job that executes DCLGEN.

Support for new SUBSTR_COMPATIBILITY subsystem parameter (APAR PH36071 - August, 2021)

APAR PH36071 introduces support for the SUBSTR_COMPATIBILITY subsystem parameter, which specifies whether the Db2 built-in function SUBSTR always returns an error message for invalid input.

Previously, during execution of the SUBSTR built-in function, Db2 sometimes incorrectly returned a result for invalid input instead of issuing an appropriate error message. After the PTF for APAR PH36071 is applied and Db2 12 function level 500 or higher is activated, the SUBSTR_COMPATIBILITY subsystem parameter is set to PREVIOUS by default and Db2 continues to behave as before the PTF was applied. If the SUBSTR_COMPATIBILITY subsystem parameter is set to CURRENT, Db2 always enforces the rules for the SUBSTR built-in function that are documented in SUBSTR and returns an SQL error code if the rules are not met.

Before you set the SUBSTR_COMPATIBILITY subsystem parameter to CURRENT, you might need to modify some of your applications to handle this change.

Improved APREUSE bind option functionality (PH36728 - June, 2021)

APAR PH36728 provides new functionality to improve the reusability of certain access paths with the APREUSE bind option when query transformations are involved.

This reduces the likelihood that the APREUSE bind option will result in a bind error, or a bind warning.

IBM Db2 for z/OS Developer Extension 1.2 (June, 2021)

Announcing IBM Db2 for z/OS Developer Extension which delivers a new developer experience for Db2 for z/OS application development in a free, lightweight, easy-to-install, and easy-to-use package, integrated with today's most widely used IDE, Microsoft Visual Studio Code.

Support for Redirected Recovery with the RECOVER utility (PH35266 - June, 2021)

This APAR enhances the RECOVER utility to support redirected recovery of index spaces and indexes using the recovery resources of different index spaces or indexes.

With redirected recovery, Db2 system administrators can more accurately determine how long the recoveries will take, check for recovery issues, and generate a replica of their production data at different points in time with transactional consistency without affecting the production data and without impact to production applications. Redirected recovery can also be used to evaluate whether recovering indexes from image copies plus application of log records is faster than rebuilding the indexes.

The FROM option of the RECOVER utility can now be used to restore image copies and apply Db2 log records from source index spaces or indexes to target index spaces and indexes. Both the source and target indexes and index spaces must have the COPY YES attribute.

Support for limiting image copy data sets for REORG (PI75518 - June, 2021)

With this APAR the REORG utility can combine data from multiple partitions into a single image copy data set, and you can now specify limits for the maximum number of image copy data sets the utility can allocate. To specify the limits, you can use two new keywords in the REORG utility control statement:

ICLIMIT_DASD specifies the maximum number of sequential image copies that REORG TABLESPACE can allocate to DASD, from 0 to 32767. 0 indicates no limit. ICLIMIT_DASD overrides the value of the REORG_IC_LIMIT_DASD subsystem parameter.

ICMLIMT_TAPE specifies the maximum number of sequential image copies that REORG TABLESPACE can allocate to tape, from 0 to 32767. 0 indicates no limit. ICLIMIT_TAPE overrides the value of the REORG_IC_LIMIT_TAPE subsystem parameter.

Before this APAR, REORG was able to create a single inline image copy for all target base partitions being reorganized, or create a separate image copy for each base partition. When reorganizing a large number of partitions or the entire partitioned table space, neither option provided the flexibility needed to operate under external constraints such as the number of available tape drives or memory consumption. 

SQL tuning capabilities added to Db2 Accessories Suite 4.2 (June, 2021)

IBM Db2 Accessories Suite for z/OS 4.2 has been enhanced with the Database Services Expansion Pack feature, which includes IBM SQL Tuning Services.

SQL Tuning Services provides a series of APIs that can be incorporated into your continuous integration/continuous delivery (CI/CD) process to help ensure that your SQL is optimally tuned before new code is deployed. SQL Tuning Services includes APIs for capturing queries, generating visual representations of access plans for a query, generating diagnostic information, and generating statistics advice, as well as a collection of administrative APIs for setting up and administering SQL Tuning Services.

Fast index traversal (FTB) support for non-unique indexes (PH30978 - June, 2021)

With APAR PH30978 applied, Db2 12 at function level 508 or higher now supports fast index traversal (FTB) for non-unique indexes.

FTBs provide valuable Db2 transaction performance improvements without the need for application changes. FTBs use a cache-friendly optimized in-memory structure to accelerate index-tree traversals, which can significantly reduce get page operations and CPU consumption. They are enabled by default in Db2 12 by the INDEX_MEMORY_CONTROL subsystem parameter setting and can take effect as soon as function level 100.

To enable use of FTBs for non-unique indexes, you set the value of the FTB_NON_UNIQUE_INDEX subsystem parameter to YES. The default value is NO.

Improved REORG INDEX utility performance (PH25217 – May, 2021)

To improve performance and resource consumption, REORG INDEX utility jobs with SHRLEVEL REFERENCE or SHRLEVEL CHANGE no longer require a work data set to hold the unloaded index keys. Instead, the utility can store these keys in memory. REORG INDEX can also now use parallel subtasks to unload and build the index keys and partitions in parallel. To specify that you want REORG INDEX to use this new behavior, specify the new NOSYSUT1 option in your utility statement or set the new subsystem parameter REORG_INDEX_NOSYSUT1 to YES. The default is NO.

If you set REORG_INDEX_NOSYSUT1 to YES or add NOSYSUT1 to your REORG INDEX statements, you do not have to remove the WORKDDN option from existing utility statements or modify existing JCL. In this case, REORG INDEX ignores the WORKDDN option and the associated template or JCL DD statement (SYSUT1 by default).

REORG INDEX with SHRLEVEL NONE is not affected by this new behavior.


Previously, Db2 REST services versioning support set the first created instance of each service as being the "default" service version and there was no way to change the default version of a REST Service.

PH34544 introduces a new RESTSERVICEDEFAULT bind option, which can be used with the REBIND PACKAGE DSN subcommand to change a REST service "default service version" attribute without causing unnecessary application outages. The new RESTSERVICEDEFAULTbind option is only allowed for the REBIND PACKAGE DSN subcommand and only when the package being rebound is a REST service package.

Display information about use of fast index traversal (FTB) with DISPLAY STATS (PH34859 - May, 2021)

With this APAR, you can issue a DISPLAY STATS command and specify the new INDEXTRAVERSECOUNT option to display information about the use of fast index traversal (FTBs) for a specific index, or a list of the first n indexes with the most uses of FTBs. Db2 returns the results in message DSNT830I.

Db2 12 function level 510 activation (PH33727 - April, 2021)

Activating function level 510 enforces the completion of various pre-migration activities before migration to the next Db2 release, and is required before you can migrate a Db2 12 environment to the next Db2 release. It does not introduce any new features or capabilities, or catalog changes, in Db2 12.

REORG utility improvements for SORTNPSI YES (PH34403 – April 2021)

Previously, when you specified the REORG utility option SORTNPSI YES or set the REORG_PART_SORT_NPSI subsystem parameter to YES, REORG sorted the non-partitioned secondary index (NPSI) keys only when certain internal thresholds were met. If the index keys are not sorted, REORG cannot collect inline statistics. Therefore, in those cases where REORG determined not to sort the NPSI keys, you had to run the RUNSTATS utility separately to collect statistics.

With PH34403, REORG sorts the keys for NPSIs when you specify the SORTNPSI YES utility option or set the REORG_PART_SORT_NPSI subsystem parameter to YES. Because the keys are always sorted, inline statistics can always be collected.

Clean-up phased-out copies for Rebind phase-in (PH33295 - March, 2021)

This APAR provides new options to free unused phased-out copies which could be created when a package is rebound. The freeing of phased-out package copies is recommended in order to reduce space in the Db2 directory and catalog.

Real-time monitoring of CICS attachment facility calls (PH31447 - February, 2021)

When z/OS Connect calls a REST API to invoke a CICS application that accesses Db2 through the CICS attachment facility, and Db2 APAR PH31447 and CICS APAR PH30252 are applied, CICS passes new identifying client information to Db2. That information can be recorded in Db2 trace records, which you can correlate with corresponding trace records from CICS to clearly identify the origin of requests from such clients.

The new CICS client information is also available through the Db2 CURRENT CLIENT_APPLNAME and CURRENT CLIENT_ACCTNG special registers.

Db2 12 function level 509 activation (PH33015 - January, 2021)

Function level 509 introduces support for tamper-proof audit policies, high availability for accelerator-only tables, the specification of a compression algorithm at the object level, and a temporal RI enhancement to allow UPDATE or DELETE on a parent table.

Improved transaction execution times and concurrency for REBIND PACKAGE (PH28693 - January, 2021)

APAR PH28693 introduces a change that will improve the REBIND concurrency of Db2 on FL505. A REBIND will now always obtain a U lock, allowing subsequent transactions that are executing a package to run in parallel. This change does not apply when the package is for a trigger, a native SQL stored procedure, or a UDF.

The change applies to packages rebound with the following options:




Enhanced DEFAULTIF specifications for the LOAD utility (PH30610 - January, 2021)

In LOAD utility statements, you can specify that a default value be loaded into a column by specifying the DEFAULTIF clause. Prior to this APAR, the only condition that you could specify in that clause is an “equal to” (=) comparison. For example:


In this case, if COL1 equals ABC, the default column value is loaded into COL2.

With this APAR, the DEFAULTIF clause is enhanced, so that you can now specify the following additional conditions:

A default column value is loaded if a conversion error occurs. For example:


The new CONV_ERROR keyword indicates that if a conversion error occurs when loading the column value, the default column value is to be used instead.

A default column value is loaded if a field does not equal (<>) a particular value. For example:


The new <> specification indicates that if COL1 does not equal ABC, the default value is loaded into COL2.

Similarly, the following example specifies that if the value from position 5 to position 10 does not equal ABCDE, the default value is loaded into COL2:


LOAD REPLACE or REBUILD INDEX utilities now reset RTS for GETPAGES (PH32224 - January, 2021)

With this APAR, when the LOAD REPLACE or REBUILD INDEX utilities are run for an object, Db2 now resets the real-time statistics (RTS) value for GETPAGES in the SYSIBM.SYSTABLESPACESTATS and SYSIBM.SYSINDEXSPACESTATS catalog tables. This change makes the LOAD REPLACE and REBUILD index utility behavior consistent with the existing REORG utility behavior for the RTS GETPAGES values.