New-function APARs for Db2 12 in 2023

Tip: Depending on how when and how you order the product code for Db2 12 or Db2 13, you might find that the external changes from any of the following APARs are already built-in when you install a new Db2 environment or migrate to Db2 13. Also, you can expect that external changes from the following APARs with availability dates earlier than 2022-06 are likely to be already built-in when you migrate to Db2 13, regardless of whether you apply the PTFs in Db2 12.

Starting in Db2 12 with APAR PH56228 (December 2023), the new subsystem parameter STATIME_DDF controls the interval of location statistics trace records, IFCIDs 365, 411, and 412. All three traces now allow a time interval of up to 5 minutes.

For more information, see the following related topics:

More granular accounting trace records for distributed applications

Starting in Db2 12 with APAR PH55241 (October 2023), you can request that Db2 generates more granular accounting trace records for distributed database applications, by issuing a MODIFY DDF command with the ACCTG(ALLCOMMIT) option. This option also requires that the PTF for APAR OA61811 is applied in your z/OS® environment. When this option is set, Db2 generates an accounting trace record occurrence after any commit or rollback. However, Db2 still maintains the WLM enclave that was created by Db2, prior to the initial requested transaction which left open resources. Also, Db2 maintains information so that when the DBAT is terminated after completing the work requests from its client connection, it deletes the enclave and informs WLM of the transactions that were completed.

To use the ACCTG(ALLCOMMIT) option, you must also set the CMTSTAT subsystem parameter value to INACTIVE.

When you issue a MODIFY DDF command with the ACCTG(ALLCOMMIT) option, Db2 issues the following message:


To return to the existing default behavior, you can issue the following MODIFY DDF command, which includes the ACCTG keyword with no option value.


When you issue this command Db2 issues the following message:

Tip: If you specify the ACCTG(ALLCOMIT) option, but the PTF for APAR OA61811 is not applied in z/OS, or if the CMTSTAT subsystem parameter is set to ACTIVE, Db2 also issues the preceding message to indicate that the default behavior is still being used.

For existing distributed threads, the specified ACCTG option takes effect only after the thread generates an accounting trace record at a commit or rollback with no open resources. Until then, the previously saved ACCTG option for the thread continues to control when it generates accounting trace records.

When this option is set back to its default setting, or if the setting was never changed, Db2 generates an accounting trace record when no open resource exists past a commit or rollback. If any of the following resources remain open and pending, Db2 does not generate an accounting trace record:
  • Open WITH HOLD cursors
  • Declared global temporary tables that are not dropped
  • Unreleased LOB locators
  • A commit processed by a stored procedure when running with 2-phase commit rules
  • Allocated RELEASE DEALLOCATE packages (due to the -MODIFY DDF PKGREL(BNDOPT) option)
  • Allocated KEEPDYNAMIC YES packages
However, if only one the last two preceding resources types, but not both, are open after a commit or rollback, Db2 generates an accounting trace record occurrence after a commit or rollback.
Important: When the ACCTG(ALLCOMMIT) setting is used, the volume of accounting trace record occurrences increases. The size of the increase depends on whether distributed application workloads have a large number of the above open resource types. When no resources are left open after a commit or rollback, -MODIFY DDF ACCTG(ALLCOMMIT) has essentially no affect. You can also use the following subsystem parameters to control the value and size of the accounting trace records:
  • When set to a numeric value, the ACCUMACC value controls the volume of trace record occurrences.
  • When set to ON, the SMFCOMP value controls size of accounting trace records.

The DISPLAY DDF command output displays the ACCTG setting in the DSNL106I message only if the ACCTG(ALLCOMMIT) setting is in effect.

For more information, see the following related topics:

Improved index traverse count information in DISPLAY STATS output

APAR PH55255 (August 2023) improves how Db2 reports index traverse count information in the TRAV. COUNT field of message DSNT380I when you issue a DISPLAY STATS command with the INDEXTRAVERSECOUNT keyword. It also adds a new FTB FACTOR field in DSNT380I, which returns a calculated value that Db2 uses to identify indexes that might benefit from the fast index traversal (FTB) capability. This APAR retrofits new function that APAR PH51565 introduced in Db2 13 to Db2 12.

Before this APAR, the TRAV. COUNT field contains the calculated FTB factor value, which can sometimes become negative, such as when index page splits occur or index-lookaside is used.

When the DISPLAY STATS command output in DSNT380I returns information about multiple indexes, they are now returned in descending order starting with the index with the highest FTB factor value.

IFCID 389 is also updated to display the FTB factor values.

For more information, see the following related topics:

Latch monitoring and serviceability enhancements

Starting in Db2 12 with APAR PH41756 (July 2023), the messages that Db2 issues when it boosts the priority of a long-waiting latch holder are updated to include additional information about the latch that is causing a suspension. Specifically, the DSNV491I message, which is issued with DSNV523I, is updated with field labels for all values that it returns. New fields are also added for token and ACE values, which can help you obtain client information, such as the end user id, transaction name, and workstation name.

The updated messages have the following format:

Before this APAR, the DSNV491I message already included information such as such as the latch holder job name, latch ASID, and whether the latch is held or released but blocked by another holder. However, because it was intended for internal use by IBM® Support these fields were not labeled in the message output or described in the documentation.

For more information, see the following related topics:

Migration readiness reported in DISPLAY GROUP command output

Starting in Db2 12 with APAR PH50072 (June 2023), you can determine whether your Db2 12 data sharing group or standalone Db2 subsystem is ready for migration to Db2 13 by issuing a DISPLAY GROUP command with the DETAIL keyword.

Before this APAR, it can be difficult to determine whether all Db2 data sharing members are at sufficient code level and function level, and have the fallback SPE APAR applied.

The new migration readiness report in the DISPLAY GROUP output now indicates whether your environment is ready for migration to the next Db2 release. If Db2 is not ready for migration, the report also includes reason information.

For example, the following migration readiness report indicates that a Db2 12 subsystem has a sufficient code level and the fallback SPE is applied, but it is not ready to migrate to Db2 13 because the highest activated function level is not the last Db2 12 function level:

DSN7100I  -DB2A DSN7GCMD                                             
*** BEGIN DISPLAY OF GROUP(........) CATALOG LEVEL(V12R1M500)        
                  CURRENT FUNCTION LEVEL(V12R1M100)                  
                  HIGHEST ACTIVATED FUNCTION LEVEL(V12R1M100)        
                  HIGHEST POSSIBLE FUNCTION LEVEL(V12R1M501)         
                  PROTOCOL LEVEL(2)                                  
                  GROUP ATTACH NAME(....)                            
DB2          SUB                     DB2    SYSTEM    IRLM           
-------- --- ---- --------  -------- ------ --------  ----   --------
........   0 DB2A -DB2A     ACTIVE   121510 UTEC6     PR21   PRLMPR21
MIGRATION READINESS REPORT                                          
DB2       CODE       SPE      MIGRATION                              
MEMBER    LEVEL      APAR     ELIGIBLE                               
--------  ---------  -------  ---                                    
........  V12R1M510  PH37108  YES                             

The GET_CONFIG stored procedure is also updated to display the migration readiness information.

Tip: A follow-on APAR, PH58761 improves how the migration readiness status is displayed for Db2 13 members in coexistence situations. For more information, see PH58761.

For more information, see the following related topics:

Enhanced SQLDA output for LOBs and distinct types in REXX applications

Starting in Db2 12 with APAR PH52852 (April 2023), if a REXX application issues a PREPARE into statement that includes a LOB column or distinct type, USING BOTH is specified, and the column is defined with labels, Db2 now correctly doubles the SQLD value in the SQLDA, and returns previously missing information, including the CCSID of a LOB column and the name for a distinct type.

Before this APAR, if a REXX application that issues a PREPARE INTO which includes a LOB column, the following errors occur in the SQLDA for REXX applications:

  • The CCSID of the LOB is not stored in the SQLCCSID.
  • If USING BOTH and the columns are defined with LABELS, the SQLD is not doubled where the labels are not returned in the second set after the columns.
  • If a distinct type exists, the name is not returned in SQLTNAME.
For more information, see the following related topics:
APPLCOMPAT and PLANMGMT support in DSNTIJRT/DSNTRIN for bind or rebind of packages for Db2-supplied routines

Starting in Db2 12 with APAR PH50704 (March 2023), you can specify the APPLCOMPAT and PLANMGMT bind options when you bind or rebind packages for Db2-supplied routines by running job DSNTIJRT, which executes the DSNTRIN program.

To ensure that all packages for Db2-supplied routines are bound with the same user-specified APPLCOMPAT level, if a valid package whose CONTOKEN matches that of the current DBRM is found, instead of bypassing the package as it currently does, DSNTIJRT/DSNTRIN is updated to perform an additional check to ensure that the APPLCOMPAT level of the package matches that of the user-specified APPLCOMPAT and PLANMGMT bind option. If the APPLCOMPAT levels do not match, DSNTIJRT/DSNTRIN rebinds the packages by using the user-specified APPLCOMPAT and PLANMGMT bind options.

Before this APAR, DSNTRIN only binds the package for a routine when any of the following conditions is true:

  • Package does not exist for current DBRM
  • Package exists for current DBRM but has been invalidated. Also, the BIND statements generated and executed by DSNTRIN do not specify an APPLCOMPAT parameter, so the APPLCOMPAT bind option defaults to the APPLCOMPAT subsystem parameter setting.
Start of changeYou can get the same behavior as before APAR PH50704 by specifying the DEFAULT options or omitting the new options from DSNTIJRT/DSNTRIN.
Important: Start of change If RTN_PKG_APPLCOMPAT(DEFAULT) is specified or applied by default, the APPLCOMPAT subsystem parameter is used to bind the packages. If the APPLCOMPAT subsystem parameter is V10R1 and DSNTIJRT/DSNTRIN has to bind a down-level DSNADMEM package, the bind fails.End of change
End of change

For more information, see the following related topics:

Improved default behavior for management of real storage

APAR PH47163 (February 2023) improves how Db2 12 manages real storage when the default setting AUTO is used for the REALSTORAGE_MANAGEMENT subsystem parameter. With the new behavior,

Db2 checks the available real storage on the LPAR, and uses the following threshold calculation to determine the management behavior to use:
REALAVAIL is the amount of currently available real storage on the LPAR, and MAXSPACE is a setting that restricts the virtual storage available to the z/OS DUMPSRV address space.

If the available storage is greater this threshold, Db2 now uses the behavior of the OFF setting and does not discard unused real storage at thread deallocation or during commit. However, if the amount of available real storage is lower than this threshold, Db2 switches to using the previous behavior (see the new AUTO1 setting description).

Db2 issues message DSNS006I when it switches behaviors.

The new AUTO behavior is preferable in most cases. However, if you encounter a situation that requires returning to the previous behavior, you can specify the new AUTO1 setting.

For more information, see the following related topics:

Installation and migration changes to support new EXPLAIN columns

Starting in Db2 12, APAR PH48053 (January 2023) updates several installation and migration jobs to support converting EXPLAIN tables to a new format that is supported in Db2 12 at function level 501 or higher. The new EXPLAIN table columns (APAR PH44421) support integration of EXPLAIN table data with data used by IBM Db2 AI for z/OS (Db2ZAI) to support the SQL optimization capabilities of Db2ZAI.

For more information, see the following related topics:

Improved IFCID 0402 reset behavior

Starting in Db2 12 or later with APAR PH50729 (March 2023), Db2 resets IFCID 402 statistics counters immediately if the warning or exception threshold for the associated profiles is changed when you issue a START PROFILE command.

In Db2 12 the new behavior is used only if the RESET_IFCID402 option is specified for the DDF_COMPATIBILITY subsystem parameter.

If the new behavior was used in Db2 12, or Db2 13 is installed with the PTF for APAR PH50729 applied, Db2 13 always uses the new behavior.

However, in the following situations, the new behavior takes effect in Db2 13 only after you resubmit your customized copy of DSNTIJUZ and issue a -SET SYSPARM command or stop and restart Db2:
  • The PTF for APAR PH50729 was applied in Db2 12 but the RESET_IFCID402 option was not specified for DDF_COMPATIBILITY subsystem parameter.
  • Installation or migration to Db2 13 was completed before the PTF for APAR PH50729 is applied.

For more information, see the following related topics: