New-function APARs for Db2 12 in 2021

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

For more information, see the following related topics:

• Taking an inline COPY with LOAD
• Syntax and options of the LOAD control statement
• PH39300

APAR PH42524 introduces acceleration support for the MULTIPLY_ALT built-in function with IBM Db2 Analytics Accelerator 7.1 or later.

For more information, see the following related topics:

• MULTIPLY_ALT scalar function

• Supported SQL functions and expressions

• PH42524

APAR PH41751 (December 2021) 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 Db2, you can use the IFCID 389 and IFCID 477 trace information, or issue the DISPLAY STATS command with the INDEXMEMORYUSAGE option.

For more information, see the following related topics:

• Fast index traversal

• -DISPLAY STATS command (Db2)

• PH41751

APAR PH40937 (December 2021) 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.

For more information, see the following related topics:

• Qualification fields for READS requests

• Qualifying log records for IFCID 0306 requests

• PH40937

APAR PH40443 (November 2021) 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.

For more information, see the following related topics:

• CONNECT statement

• Encrypted password, user ID, or data security under the IBM Data Server Driver for JDBC and SQLJ

• PASSWORD or PHRASE command (z/OS)

• PH40443

APAR PH40261 (October 2021) 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.

For more information, see the following related topics:

• -DISPLAY STATS command (Db2)

• DSNT788I

• DSNI090I

• DSNI091I

• DSNI092I

• PH40261

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

For more information, see the following related topics:

• ADMIN_COMMAND_DSN stored procedure

• DCLGEN (declarations generator) subcommand (DSN)

• PH28863

APAR PH36071 (August 2021) 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 scalar function 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.

For more information, see the following related topics:

• SUBSTR COMPATIBILITY field (SUBSTR_COMPATIBILITY subsystem parameter)

• Application and SQL release incompatibilities

• DSNTIPX: Routine parameters panel

• PH36071

IBM Text Search for Db2 for z/OS V5.5.4 is now available, after you apply the PTF for APAR PH35720 (August 2021) in Db2 12.

For more information, see the following related topics:

• IBM Text Search for Db2 for z/OS

• Installing and configuring text search functions

• IBM Text Search for Db2 for z/OS downloads

• PH35720

APAR PH36728 (June 2021) 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.

For more information, see the following related topics:

• APREUSE bind option

• Analyzing access path changes at bind or rebind

• PH36728

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.

APAR PH35266 (June 2021) 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.

For more information, see the following related topics:

• RECOVER

With APAR PI75518 (June 2021) 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, 0–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, 0–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. 

For more information, see the following related topics:

• Syntax and options of the REORG TABLESPACE control statement

• REORG IC LIMIT DASD field (REORG_IC_LIMIT_DASD subsystem parameter)

• REORG IC LIMIT TAPE field (REORG_IC_LIMIT_TAPE subsystem parameter)

• PI75518

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.

For more information, see the following related topics:

• Overview of IBM SQL Tuning Services

With APAR PH30978 (June 2021) 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.

For more information, see the following related topics:

• Fast index traversal

• FTB NON UNIQUE INDEX field (FTB_NON_UNIQUE_INDEX subsystem parameter)

• Function level 508 (PH29392 - October 2020)

• PH30978

To improve performance and resource consumption, with APAR PH25217 (May 2021), 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.

For more information, see the following related topics:

• Syntax and options of the REORG INDEX control statement

• REORG INDEX NOSYSUT1 (REORG_INDEX_NOSYSUT1 subsystem parameter)

• PH25217

APAR PH34544 (May 2021) 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 RESTSERVICEDEFAULT bind option is only allowed for the REBIND PACKAGE DSN subcommand and only when the package being rebound is a REST service package.

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.

For more information, see the following related topics:

• RESTSERVICEDEFAULT bind option

• PH34544

With APAR PH34859 (May 2021), 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.

For more information, see the following related topics:

• Fast index traversal

• -DISPLAY STATS command (Db2)

• DSNT830I

• PH34859

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

For more information, see the following related topics:

• Function level 510 (V12R1M510) (PH33727 - April 2021)

• PH33727

• IBM Db2 13 for z/OS (announcement letter)

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 (April 2021), 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.

For more information, see the following related topics:

• Syntax and options of the REORG TABLESPACE control statement

• REORG PART SORT NPSI field (REORG_PART_SORT_NPSI subsystem parameter)

• Db2 utility release incompatibilities

• PH34403

APAR PH35411 (April 2021) increases the size of the input parameter PASSWORD for the following Db2-supplied stored procedures from VARCHAR(24) to VARCHAR(100), and enables these stored procedures to accept 100-character passphrases.

For more information, see the following related topics:

• ADMIN_COMMAND_UNIX stored procedure

• ADMIN_JOB_CANCEL stored procedure

• ADMIN_JOB_FETCH stored procedure

• ADMIN_JOB_QUERY stored procedure

• ADMIN_JOB_QUERY stored procedure

• PH35411

APAR PH30575 (March 2021) enables Python applications to access data in Db2 for z/OS data by using the IBM_DB Pyhton support for DB2 for z/OS driver through Db2 for z/OS ODBC driver CLI APIs.

For more information, see the following related topics:

• Python support for Db2 for z/OS

• IBM_DB Python Support for IBM Db2 for z/OS and IBM Informix databases

• PH30575

APAR PH33295 (March 2021) 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.

For more information, see the following related topics:

• Package copies for plan management

• FREE PACKAGE subcommand (DSN)

• PH33295

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 (February 2021) 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.

For more information, see the following related topics:

• CURRENT CLIENT_APPLNAME special register

• CURRENT CLIENT_ACCTNG special register

• PH31447

• PH30252

Function level 509 (PH33015 - January, 2021) 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.

For more information, see the following related topics:

• Function level 509 (PH33015 - February 2021)

• PH33015

FL 505 APAR PH28693 (January 2021) improves concurrency for REBIND commands in Db2 12 at function level 505 or higher. With this APAR, a REBIND command now always obtains 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:

• APREUSE(NONE) PLANMGMT(EXTENDED)
• APREUSE(WARN) PLANMGMT(EXTENDED) APREUSESOURCE(CURRENT)
• APREUSE(ERROR) PLANMGMT(EXTENDED) APREUSESOURCE(CURRENT)

For more information, see the following related topics:

• Package copies for plan management
• Rebind phase-in for packages that are being used for execution
• PH28693

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

LOAD … COL2 POSITIONS(5:10) CHAR(6) DEFAULTIF(COL1=’ABC’)

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:

COL2 POSITION(5:14)DATE DEFAULTIF(CONV_ERROR)

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:

COL2 POSITION(5:10) CHAR(6) DEFAULTIF(COL1<>’ABC’)

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:

COL2 POSITION(5:10) CHAR(6) DEFAULTIF((5:9) <> ‘ABCDE’)

For more information, see the following related topics:

• Syntax and options of the LOAD control statement

• PH30610

With APAR PH32224 (January 2021), 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.

For more information, see the following related topics:

• How LOAD affects real-time statistics

• How REBUILD INDEX affects real-time statistics

• SYSINDEXSPACESTATS catalog table

• SYSTABLESPACESTATS catalog table

• PH30780