New-function APARs for Db2 12 in 2022
- Control the maximum number of concurrent user-defined external scalar functions
-
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:
- Lock avoidance for singleton SELECT with ISOLATION(CS) and CURRENTDATA(YES)
-
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:
- Authorization update for CREATE INDEX on DGTT
- APAR PH48601 (December 2022) removes certain authorization checks for CREATE INDEX statements for creating indexes on declared global temporary tables (DGTTs). Specifically, this enhancement removes the USE check for the buffer pool and storage group when you creating an index on a DGTT, if the specified buffer pool and storage group are the same as the default values associated with the work file database. If the specified values differ from the defaults,
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.
- Accelerator expression-offload support for LISTAGG and RAND
- APAR PH48480 (November 2022) introduces offload support from Db2 12 or later to IBM Db2 Analytics Accelerator offload support for the following built-in functions:
- 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:
- Support for UNI_90 locale for the LOWER, TRANSLATE, and UPPER built-in functions
-
APAR PH47187 (September 2022) adds support to specify that the LOWER, TRANSLATE, and UPPER built-in functions use z/OS Unicode Conversion Services only with "normal" casing capabilities and use Unicode Standard 9.0.0. Db2 now supports a new locale, UNI_90, which specifies that z/OS Unicode Conversion Services is used with only "normal" casing capabilities and use Unicode Standard 9.0.0.
Before to this APAR, Db2 supports the following locales to indicate that Db2 should use z/OS Unicode Conversion Services to perform the upper or lower conversion with "normal" or "normal" and "special" casing capabilities.
- 'UNI' uses Unicode version 3.0.0 (which is the default for z/OS Unicode Services).
- 'UNI_SIMPLE' uses the latest version of the Unicode Standard that z/OS Unicode Conversion Services supports.
- 'UNI_60' specifies thatz/OS Unicode Conversion Services is used with only "normal" casing capabilities and use Unicode Standard 6.0.0.
For more information, see the following related topics:
- DECFLOAT datatype support for PL/I and C with DCLGEN
-
APAR PH47453 (August 2022) enhances DCLGEN to support the DECFLOAT data type for the PL/I and C programming languages.
For more information, see the following related topics:
- Improved DBAT status information in DISPLAY THREAD output
-
APAR PH45504 (July 2022) improves Db2 reports the status of database access threads (DBATs), so that you can distinguish between DBATS that are processing SQL requests for active transactions and threads that reached a transaction boundary and are waiting for new transaction requests. Specifically, this APAR introduces the new status values that are listed in the following table in DISPLAY THREAD command output and in IFCID148 data obtained in IFC READS calls.
Before this APAR, Db2 returns the RA status in all such cases, which makes it difficult to accurately determine the current state of DBATs that use KEEPDYNAMIC(YES) or high-performance DBAT support. That is, you cannot distinguish between threads that are processing SQL requests for active transactions and threads that reached a transaction boundary and those that are waiting for new transaction requests.
ST value Meaning RD The DBAT is processing a request from a remote location and waiting for the next transaction. The DBAT is an active KEEPDYNAMIC-refreshable thread, and not a candidate for idle thread detection and termination. For more information, see Enabling KEEPDYNAMIC refresh for DBATs. RH The DBAT is processing a request from a remote location and waiting for the next transaction. The DBAT is being kept active due to resources of various types being held past a commit or rollback. A DBAT with this status is a candidate for idle thread detection and termination based on the IDTHTOIN subsystem parameter setting. For more information, see IDLE THREAD TIMEOUT field (IDTHTOIN subsystem parameter). RP The DBAT is processing a request from a remote location and waiting for the next transaction. The thread is an active high-performance DBAT, and it is not a candidate for idle thread detection and termination. For more information, see Enabling high-performance DBATs. For more information, see the following related topics:
- New serviceability message for LOAD FORCE and REORG FORCE
-
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:
- New REORG option to minimize the duration of the last log iteration
-
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:
- New LOAD option to reclaim NPI space
-
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:
- Improvements to the -START ML and -STOP ML commands
-
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:
- New statistics trace classes for monitoring distributed user statistics
-
With APAR PH40244 (April 2022), you can use the following new statistics trace classes to monitor statistics for DRDA connections:
- Statistics class 10 records statistics in IFCID 411 for remote applications based on the CURRENT CLIENT_APPLNAME special register.
- Statistics class 11 records statistics in IFCID 412 for remote users based on the CURRENT CLIENT_USERID special register.
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:
- Improved detection and removal of incomplete connections to Db2
-
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:
- DCLGEN updated to use COMP-5 declarations for integer data for COBOL applications
-
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)
toPIC 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:
- Statistics class 1 includes IFCID 0369
-
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:
- Controlling application compatibility for productivity-aid sample programs
-
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:
- LOAD improvements for inline image copies of partitions
-
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.