New-function APARs for Db2 13 in 2022

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.

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 of function level 504 or higher verifies that the PTF for this APAR is applied on every data sharing member, but its changes take effect at any function level.

For more information, see the following related topics:

• Converting tables from growth-based to range-based partitions
• Restrictions for pending data definition changes
• -20385
• PH51359

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.

FL 504 Activation of function level 504 or higher verifies that the PTF for this APAR is applied on every data sharing member, but its changes take effect at any function level.

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

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 of function level 504 or higher verifies that the PTF for this APAR is applied on every data sharing member, but its changes take effect at any function level.

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

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.

• DSN13012 for V13R1M501.
• DSN13011 for V13R1M500.
• DSN13010 for V13R1M100.

• LISTAGG
• RANDOM or RAND 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) .

FL 504 Activation of function level 504 or higher verifies that the PTF for this APAR is applied on every data sharing member, but its changes take effect at any function level.

For more information, see the following related topics:

• PH48480

After a query that contains a view or table expression is explained, the PLAN_TABLE already contains a plan for accessing the view or table expression, and it also contains a child query block that produces the result rows for the view or table expression. However, in some scenarios before this APAR, no linkage exists in the PLAN_TABLE between rows for the plan and rows for child query block. With this APAR applied, Db2 now writes th PLAN_TABLE.PARENT_PLANNO column value for the plan into the same column for rows for a child query block that returns result rows for a view or table expression.

FL 504 Activation of function level 504 or higher verifies that the PTF for this APAR is applied on every data sharing member, but its changes take effect at any function level.

For more information, see the following related topics:

• PLAN_TABLE
• PH49972

• Function level 502 (APAR PH47891 - October 2022)
• PH47891

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 of function level 504 or higher verifies that the PTF for this APAR is applied on every data sharing member, but its changes take effect at any function level.

• Index access for MERGE
• MERGE
• List prefetch (PREFETCH='L' or 'U')
• PH47581

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 of function level 504 or higher verifies that the PTF for this APAR is applied on every data sharing member, but its changes take effect at any function level.

For more information, see the following related topics:

• LOWER
• UPPER
• TRANSLATE
• PH47187

Starting in Db2 13 function level 500 or higher, APAR PH48183 (September 2022) reduces the number of EREP LOGREC entries with reason code 00E50013 for parallel queries that specify FETCH FIRST n ROWS.

If a query that specifies FETCH FIRST n ROWS runs in parallel, the parallel child tasks continue fetching more records as the parent retrieves and returns them to the application. As soon as n rows are returned, the parent sends a "stop" message to the child tasks. When each child receives the stop message, it stops processing as if canceled. Before this APAR, each child task also writes out an EREP LOGREC entry with reason code 00E50013 in this situation.

With APAR PH48183 applied, the parallel child tasks can stop processing sooner and avoid issuing the EREP LOGREC entries with 00E50013.

The new logic applies for applications that run at application compatibility V13R1M500 or higher.

FL 504 Activation of function level 504 or higher verifies that the PTF for this APAR is applied on every data sharing member, but its changes take effect at any function level.

For more information, see the following related topics:

• 00E50013
• PH48183

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 of function level 504 or higher verifies that the PTF for this APAR is applied on every data sharing member, but its changes take effect at any function level.

For more information, see the following related topics:

• ST in DSNV401I
• PQDBAT in DSNL080I
• PH47626

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

FL 504 Activation of function level 504 or higher verifies that the PTF for this APAR is applied on every data sharing member, but its changes take effect at any function level.

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.

FL 504 Activation of function level 504 or higher verifies that the PTF for this APAR is applied on every data sharing member, but its changes take effect at any function level.

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.

FL 504 Activation of function level 504 or higher verifies that the PTF for this APAR is applied on every data sharing member, but its changes take effect at any function level.

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 PH46346 (or PH39194 in Db2 12) (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.

FL 504 Activation of function level 504 or higher verifies that the PTF for this APAR is applied on every data sharing member, but its changes take effect at any function level.

For more information, see the following related topics:

• Syntax and options of the LOAD control statement
• PH46346

With APAR PH46031 (or PH43479 in Db2 12) (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.

FL 504 Activation of function level 504 or higher verifies that the PTF for this APAR is applied on every data sharing member, but its changes take effect at any function level.

For more information, see the following related topics:

• -START ML (Db2)
• -STOP ML (Db2)
• PH46031

For more information, see the following related topics:

• Preparing to monitor distributed connections (Db2ZAI)
• PH44815