New-function APARs for Db2 12 in 2022

APAR PH44833 (December 2022) introduces a new subsystem parameter to control the number of user-defined external scalar functions that can run concurrently in a Db2 thread. With this APAR in Db2 12 at function level 100 or higher, the MAX_UDF subsystem parameter now specifies the maximum number of user-defined external scalar functions that can run concurrently in a Db2 thread. The default value is 2000.

For more information, see the following related topics:

• MAX UDFS field (MAX_UDF subsystem parameter)
• PH44833

Starting in Db2 12 at function level 100 or higher, APAR PH49335 (December 2022) introduces the capability to use lock avoidance for singleton SELECT statements that run with the ISOLATION(CS) and CURRENTDATA(YES) options. A singleton SELECT is a cursor SELECT that returns at most one row.

Db2 can use lock avoidance for such SINGLETON SELECT statements if you specify YES for the LA_SINGLESEL_ISOCS_CDY subsystem parameter.

Allowing lock avoidance can significantly lessen locking activity for such singleton SELECT statements. However, occasional false warning or error conditions are also possible during certain small timing windows, such as SQLCODE -811 (more than one row returned) or SQLCODE +100 (no row found). The YES setting for LA_SINGLESEL_ISOCS_CDY should only be used if your environment can tolerate these situations.

For more information, see the following related topics:

• LA_SINGLESEL_ISOCS_CDY subsystem parameter
• Lock avoidance
• PH49335

Before this APAR, the USE authorization checks could cause such CREATE INDEX statements to fail. However, this only occurred if the defaults were changed, or use of the default buffer pool and storage group was revoked from public.

• LISTAGG aggregate function
• RANDOM or RAND scalar function with the optional numeric-expression seed value

Offload of these functions is supported by IBM Db2 Analytics Accelerator 7.1.9 or later.

To enable offload of these functions, you specify YES in the ENABLE ACCELERATOR SPECIFIC RESULTS field on panel DSNTIP8A. For more information, see ACCELERATION OPTIONS field (QUERY_ACCEL_OPTIONS subsystem parameter) .

For more information, see the following related topics:

• PH48480

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

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

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

For more information, see the following related topics:

• LOWER scalar function
• UPPER scalar function
• TRANSLATE scalar function
• PH47187

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

For more information, see the following related topics:

• Data types that DCLGEN uses for variable declarations
• DCLGEN (declarations generator)
• PH47453

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.

For more information, see the following related topics:

• Managing distributed database access threads (DBATs)
• DSNV041I
• PH44504

Starting in Db2 12 with APAR PH44941 (June 2022), when you specify the FORCE option for the LOAD utility or REORG utility, and the utility forces the cancellation of blocking claimers, the utility job output will now include the new message DSNU2929I. This message reports the class of claimers that are canceled and the time of the cancellation. This information aids in serviceability.

For more information, see the following related topics:

• Syntax and options of the LOAD control statement
• Syntax and options of the REORG INDEX control statement
• Syntax and options of the REORG TABLESPACE control statement
• DSNU2929I
• PH44941

APAR PH33864 (May 2022) introduces a new option, LASTLOG,for both the REORG INDEX utility and the REORG TABLESPACE utility. You can use this new option to help reduce the outage window during REORG SHRLEVEL CHANGE executions. Specifically, LASTLOG controls whether the utility applies log records during the last log iteration in the LOG phase. By specifying LASTLOG NO, you can avoid costly subprocesses that occur during the final log iteration and potentially reduce the outage window. LASTLOG YES ensures that REORG completes the final round of applying log records. The default is LASTLOG YES, which is the existing behavior prior to this APAR.

For more information, see the following related topics:

• Syntax and options of the REORG INDEX control statement
• Syntax and options of the REORG TABLESPACE control statement
• PH33864

APAR PH39194 (May 2022) introduces the new LOAD utility option KEEP_EMPTY_PAGES allows you to specify whether LOAD deletes empty index leaf pages of nonpartitioned secondary indexes (NPSIs) when keys are deleted from the logical partitions of the NPSI. This option is applicable to only LOAD SHRLEVEL NONE PART REPLACE utility executions.

The main benefit of keeping these empty pages (KEEP_EMPTY_PAGES=YES) is improved utility performance. Additionally, new index keys that are inserted by LOAD or other applications can reuse the empty index leaf pages. The benefit of deleting these empty pages (KEEP_EMPTY_PAGES=NO) is that space can be reclaimed. Prior to this enhancement, these empty leaf pages were always kept. This behavior continues to be the default if KEEP_EMPTY_PAGES NO is not explicitly specified.

For more information, see the following related topics:

• Syntax and options of the LOAD control statement
• PH39194

With APAR PH43479 (May 2022), the commands to start and stop the Db2 functions used by IBM Db2 AI for z/OS® have been improved to allow for the separate activation or deactivation of either the SQL optimization or the system assessment and distributed connection control features of IBM Db2 AI for z/OS.

For more information, see the following related topics:

• -START ML command (Db2)
• -STOP ML command (Db2)
• PH43479

APAR PH40243 (April 2022), enhances the Distributed connection control (DCC) function of IBM Db2 AI for z/OS. You can enable IFCID 411 or 412 to collect thread-related statistics for client user IDs or client application names and use the statistics as input for DCC training.

For more information, see the following related topics:

• Preparing to monitor distributed connections (Db2ZAI)
• PH40243

APAR PH44270 (April 2022) improves how Db2 detects and terminates incomplete connections, and it adds new a message DSNL079I that Db2 issues to indicate that it terminated incomplete connections, when the required network exchanges between Db2 and the client do not process in a timely manner. Db2 issues the DSNL079I message at most once every 5 minutes. The DSNL079I indicates the number of connections terminated since it was last issued.

This APAR also enhances the DISPLAY LOCATION command with a keyword INCOMPLT. When INCOMPLT is specified, the output messages include a list of locations that had incomplete connections terminated.

For more information, see the following related topics:

• -DISPLAY LOCATION command (Db2)
• DSNL079I
• DSNL200I
• PH44270

With APAR PH42857 (March 2022), the Db2 declarations generator (DCLGEN) now uses COMP-5 for declarations for integer data types, including SMALLINT, INTEGER, and BIGINT. This change simplifies the generation of declarations for COBOL programs when the TRUNC(OPT) option is used in an Enterprise COBOL Version 5 or later compiler. TRUNC(OPT) provides significant performance advantages over the other TRUNC options.

Before this change, if you wanted to benefit from the performance advantages of the TRUNC(OPT) compiler option and use DCLGEN, you needed to use one of the following manual workarounds, by modifying the DCLGEN copybooks:

• Change COMP to COMP-5 so it is handled as native binary.
• Add a digit to the definition, such as by changing PIC S9(4) to PIC S9(5), so that the data fits into the underlying picture clause.

Otherwise, you were forced to continue using the TRUNC(BIN) compiler option, and thus not benefit from the performance improvements available with TRUNC(OPT).

For more information, see the following related topics:

• DCLGEN (declarations generator)
• Data types that DCLGEN uses for variable declarations
• TRUNC compiler option (Enterprise COBOL for z/OS)
• PH42857

With APAR PH43916 (March 2022), IFCID 0369 is added to statistics class 1, and is activated by default. A trace for IFCID 0369 records wait time and CPU time for threads that run in a Db2 subsystem, aggregated by connection type. In previous Db2 releases, IFCID 0369 was in statistics class 9, and was not activated by default.

For more information, see the following related topics:

• -START TRACE command (Db2)
• PH43916

APAR PH41968 (February 2022) introduces a new PKGSET parameter for the DSNTEP2, DSNTEP4, DSNTIAD, and DSNTIAUL sample programs. When you specify this parameter, the sample programs implicitly issue SET CURRENT PACKAGESET statements before processing the dynamic SQL statements.

With this capability, you can bind packages in specific collections for different application compatibility (APPLCOMPAT) levels, and use the PKGSET parameter to specify the package set, and thus the APPLCOMPAT level, that the program uses when it processes dynamic SQL statements.

In the DSNTEP2 and DSNTEP4 programs, you can also change the APPLCOMPAT level for different statements within a single SYSIN by specifying a --#PKGSET package-set control statement between the statements.

For more information, see the following related topics:

• Application compatibility levels in Db2 12
• DSNTEP2 and DSNTEP4 sample programs
• DSNTIAD sample program
• DSNTIAUL sample program
• PH41968

With APAR PH40709 (January 2022), you can now request that the LOAD utility take partition-level sequential image copies by specifying the copy options only once, at the table space level. Previously, to get partition-level copies, you had to specify the copy options in each INTO TABLE PART clause.

This new syntax has the benefit of serialization at the partition level while still applying the global copy options to all partition-level copies. Serializing at the partition level, instead of the table space level, allows concurrent LOAD jobs to run against other partitions within the same table space. Applying global copy options to each partition copy can also help with resource constraints. For example, instead of needing 200 tape drives or data sets to take inline copies of 200 partitions, LOAD can use the single tape drive or data set that is specified in the copy options at the table space level. Prior to this APAR, you could not get both of these benefits in the same LOAD job. To get partition-level serialization, you had to specify the copy options in the INTO TABLE PART clauses. To apply global copy options, you had to specify them at the table space level. You could not do both.

The existing syntax is still valid. The new syntax introduced by this APAR is another option for requesting inline copies of partitions.

• Syntax and options of the LOAD control statement
• PH40709