New-function APARs for Db2 13 in 2023

Starting in Db2 13 with APAR PH56288 (December 2023, when the last available active log data set is 5% or more full, Db2 keeps the last DSNJ110E message highlighted on the console until the condition is resolved. The goal is to prevent operators from overlooking this message.

FL 505 Activation verifies that the PTF for this APAR is applied on every Db2 13 data sharing member. However, applying the PTF makes the new function available on each member, at any function level.

For more information, see the following related topics:

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.

FL 505 Activation verifies that the PTF for this APAR is applied on every Db2 13 data sharing member. However, applying the PTF makes the new function available on each member, at any function level.

For more information, see the following related topics:

• DDF STATS TIME field (STATIME_DDF subsystem parameter)
• PH56228

• Function level 504 (APAR PH54919 - October 2023)
• PH54919

PH57071 (October 2023) increases how frequently Db2 reads newly inserted rows for control of fast index traversal (FTB) processing in the SYSIBM.SYSINDEXCONTROL table. With this APAR, Db2 applies the newly inserted rows every 2 minutes, instead of every 10 minutes.

FL 505 Activation verifies that the PTF for this APAR is applied on every Db2 13 data sharing member. However, applying the PTF makes the new function available on each member, at any function level.

• Enabling or disabling fast index traversal at the index level
• SYSINDEXCONTROL catalog table
• PH57071

APARs PH56996 and PH56997 (October 2023) introduce support for users with the DISPLAY privilege to issue the DISPLAY PROFILE and DISPLAY DYNQUERYCAPTURE commands. This change makes these commands more consistent with the behavior for other DISPLAY commands.

Before these APARs, users issuing these commands needed to have at least one of the following administrative authorities:

• SQLADM authority
• System DBADM authority
• SYSOPR authority
• SYSCTRL authority
• SYSADM authority

FL 505 Activation verifies that the PTF for this APAR is applied on every Db2 13 data sharing member. However, applying the PTF makes the new function available on each member, at any function level.

• -DISPLAY DYNQUERYCAPTURE command (Db2)
• -DISPLAY PROFILE command (Db2)
• System privileges
• PH56996
• PH56997

APAR PH54056 (October 2023) relaxes a restriction against a BIND QUERY command that is issued to enforce statement-level plan hints for certain SQL PL packages.

Before this APAR, Db2 issues message DSNT281I with reason code 5 when processing a statement that has a DSN_USER_QUERY_TABLE row with a TYPE='N' column value. This restriction prevents the enforcement of statement-level access path hints for statements in SQL PL packages, which have the TYPE='N' value in the SYSPACKAGE catalog table.

FL 505 Activation verifies that the PTF for this APAR is applied on every Db2 13 data sharing member. However, applying the PTF makes the new function available on each member, at any function level.

For more information, see the following related topics:

• Specifying access paths at the statement level
• BIND QUERY subcommand (DSN)
• PH54056

Starting in Db2 13 function level V13R1M100 or higher with APAR PH55025 (September 2023), you can specify a list of authorization IDs to be granted ALL privileges on the Db2 MQ tables SYSIBM.MQPOLICY_TABLE and SYSIBM.MQSERVICE_TABLE when you install or migrate Db2. A new optional configuration (DB2OPT) keyword called MQ_ADMIN_ID is added to installation job DSNTIJRT for this purpose. A new MQ ADMIN ID(s) field is also added on panel DNSTIPG1 for setting this parameter. This change helps to support appropriate separation of duties between users of MQ functions and administrators of MQ policies and services.

Before this APAR, when DSNTIJRT/DSNTRIN generates the access list for the two MQ tables based on all the authorization IDs specified in the GRANTTO parameter of each Db2 MQ function, and the result is that any user with EXECUTE privilege on any Db2 MQ function also has ALL privileges on the two Db2 MQ tables. Thus, any user that is allowed to read from or write messages into MQ queues through Db2 MQ functions is also able to delete or change entries in SYSIBM.MQPOLICY_TABLE and SYSIBM.MQSERVICE_TABLE.

This new DSNTIJRT/DSNTRIN optional configuration DB2OPT parameter serves two purposes when specified:

• Specifying a list of authorization IDs that will be granted ALL privileges on the Db2 MQ tables.
• Specifying that only the SELECT privilege on the MQ tables will be granted to all users with EXECUTE privilege on any of the Db2 MQ functions.

The following example shows how to specify previous behavior in MQ_ADMIN_ID parameter setting in job DSNTIJRT:

  //DSNTRIN EXEC PGM=DSNTRIN,COND=(4,LT),
  //             PARM=('Db2SSN(!DSN!) MODE(INSTALL)',
  //             ' AUTHID(!AUTHID!) SECDEFID(!SECDEFID!)',
  //             ' DEFPKOWN(!DEFPKOWN!)')
  . . .
  //DB2OPT   DD  *
    STOGROUP(SYSDEFLT)
    INDEXSTOG(SYSDEFLT)
    BP4K(BP0)
    BP8K(BP8K0)
    BP16K(BP16K0)
    BP32K(BP32K)
    LOBBP8K(BP8K0)
    LOBBP16K(BP16K0)
    LOBBP32K(BP32K)
    IMS_SECURITY(Db2)
    MQ_SECURITY(Db2)
    SOAP_SECURITY(Db2)
    RTN_PKG_APPLCOMPAT(DEFAULT)
    RTN_PKG_PLANMGMT(DEFAULT)
    MQ_ADMIN_ID(DEFAULT)

The following example shows how to specify a list of authorization IDs in the MQ_ADMIN_ID parameter to get the new behavior:

   MQ_ADMIN_ID(MQUID1,MQUID2,MQUID3)

FL 505 Activation verifies that the PTF for this APAR is applied on every Db2 13 data sharing member. However, applying the PTF makes the new function available on each member, at any function level.

For more information, see the following related topics:

• Job DSNTIJRT
• MQ ADMIN ID(S) field
• DSNTIPG1: Installation preferences panel 2
• Db2 MQ tables
• PH55025

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:

DSNL302I ACCTG IS SET TO ALLCOMMIT

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

-MODIFY DDF ACCTG

When you issue this command Db2 issues the following message:

DSNL302I ACCTG IS SET TO COMMIT_WITH_NO_OPEN_RESOURCES

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.

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:

• -MODIFY DDF command (Db2)
• PH55241
• OA61811

APAR PH55212 (August 2023) enhances the SQL Data Insights vector prefetch capability by making the enablement decision dynamic based on data and AI cache availability. When the MXAIDTCACH parameter is set to a value in the range 1–512 and a query invokes a SQL DI function on a table, Db2 dynamically elects vector prefetching or row-by-row processing to optimize CPU usage for the query. If needed, Db2 automatically disables vector prefetching for the SQL DI function in the query. This update also delivers Db2 support of numeric data types for the AI_ANALOGY function.

FL 504 Activation verifies that the PTF for this APAR is applied on every Db2 13 data sharing member. However, applying the PTF makes the new function available on each member, at any function level.

APARs PH54863 and PH54936 (July 2023) enhance Db2 13 at function level 100 or higher to allow database administrators (DBAs) with DBADM authority on databases to drop a view created for another user and select from a view without always requiring system level authority. APAR PH54936 supports the view management authorization enhancements when using RACF access control authorization exit for access control. With these APARs, DROP statements are allowed for a view if the privilege set includes DBADM authority on the database that contains one of the base tables and the subsystem parameter DBACRVW is set to YES. SELECT statements for a view are allowed if the privilege set includes the required privileges such as SELECT or EXECUTE on all the base objects.

FL 504 Activation verifies that the PTF for this APAR is applied on every Db2 13 data sharing member. However, applying the PTF makes the new function available on each member, at any function level.

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:

NAME ST A REQ ID AUTHID PLAN ASID TOKEN
---- -- - --- -- ------ ---- ---- -----
text t  t num text text text hex  num
V491-LATCH=latch-addr LATCH ASID=latch-asid latch-status JOBNAME=hldr-jobname ASID=
hldr-asid CORRID=hldr-corr-id TOKEN=token-number ACE=ace-num
V490-SUSPENDED timestamp smodule soffset smodptf-level

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.

FL 504 Activation verifies that the PTF for this APAR is applied on every Db2 13 data sharing member. However, applying the PTF makes the new function available on each member, at any function level.

For more information, see the following related topics:

• DSNV523I
• DSNV491I
• PH41756

Starting in Db2 13 with APAR PH54129 (June 2023), you can issue the DSN FREE PACKAGE command to free inactive packages for native REST services.

Db2 native REST services are created by invoking the REST service manager API with a createService request, or by using the DSN BIND SERVICE command. This inserts a row into the REST services table defining the service and creates an application package. You can then rebind the application package for a REST service with various options by using the DSN REBIND PACKAGE command. During rebind, inactive package copies, such as the original and previous copies, can be generated based on the PLANMGMT option. Phased-out copies of packages can be generated if already running threads have the package allocated when the rebind occurs. Invalid package copies can occur when a dependent object is changed or dropped. These types of inactive packages can build up over time when many rebinds are done.

Before this APAR, the only method of freeing inactive REST service packages is by invoking the REST service manager API with a dropService request, or by use of the FREE SERVICE command. The shortcoming to doing this is that the REST service itself is freed and all packages, both active and inactive, are freed. Also the FREE PACKAGE command allows the freeing of inactive packages only if they are not REST services, and is blocked for REST services with error message DSNT246I.

Use of the FREE PACKAGE command to free an active REST service package continues to be blocked with the DSNT264I message.

FL 504 Activation verifies that the PTF for this APAR is applied on every Db2 13 data sharing member. However, applying the PTF makes the new function available on each member, at any function level.

For more information, see the following related topics:

• Freeing inactive packages for Db2 REST service
• FREE PACKAGE subcommand (DSN)
• FREE SERVICE command (DSN)
• DSNT264I
• PH54129

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.

FL 504 Activation verifies that the PTF for this APAR is applied on every Db2 13 data sharing member. However, applying the PTF makes the new function available on each member, at any function level.

• REST
• JDBC
• CLI
• DB2CONNECT
• DSN
• * (the asterisk is a wildcard that applies for application requesters that do not match any of the more specific product types above)

The new keyword values can only be specified for profiles using the default location filtering criteria (DSN_PROFILE_TABLE - LOCATION column contains '*', '::0', or '0.0.0.0' representing any IP address). These new keyword values enable the definition of profiles, based on the application requester product type, to discover and enforce the usage of authorization mechanisms and encrypted connections.

FL 504 Activation verifies that the PTF for this APAR is applied on every Db2 13 data sharing member. However, applying the PTF makes the new function available on each member, at any function level.

For more information, see the following related topics:

• Discovering and controlling secure TCP/IP connectivity with profile tables
• DSN_PROFILE_ATTRIBUTES profile table
• DSNT775I
• DSNT776I
• PH48764

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.

You can get the same behavior as before APAR PH50704 by specifying the DEFAULT options or omitting the new options from DSNTIJRT/DSNTRIN.

Important: 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.

FL 504 Activation verifies that the PTF for this APAR is applied on every Db2 13 data sharing member. However, applying the PTF makes the new function available on each member, at any function level.

For more information, see the following related topics:

• PKG APPLCOMPAT field
• PKG PLANMGMT field
• PH50704

APAR PH51892 (February 2023) introduces a new vector prefetch capability and improves the existing built-in scalar AI_SEMANTIC_CLUSTER function, which together with other optimizations, delivers significant functional and performance improvements for SQL Data Insights (SQL DI).

Vector prefetch is the advanced process that SQL DI uses to upload numeric vectors to the IBM Z® AI Optimization Library (zAIO) for calculating similarity scores. The existing query processing architecture forces Db2 to submit one record at a time to z/OS for processing. Prefetching enables Db2 AI functions to submit multiple vectors in a batch at a time, which greatly accelerates SQL DI query processing.

Db2 stores training vectors in a normalized form for all scoring requests. However, the AI_SEMANTIC_CLUSTER function requires non-normalized vectors when it computes the average of the input vectors. To meet this requirement, Db2 adds a new NORMALIZE_ABSOLUTE_VALUE column to the vector table and populates it with an absolute value during model training. The AI_SEMANTIC_CLUSTER function uses the value in the new column to convert the normalized vectors to non-normalized, calculate the input average, and convert the non-normalized vectors back to normalized. The addition of the new column helps improve not only the scoring accuracy of the AI_SEMANTIC_CLUSTER function but also the overall scoring performance of the SQL DI feature.

FL 504 Activation verifies that the PTF for this APAR is applied on every Db2 13 data sharing member. However, applying the PTF makes the new function available on each member, at any function level.

For more information, see the following related topics:

• AI_SEMANTIC_CLUSTER scalar function
• PH51469

• Function level 503 (APAR PH51469 - February 2023)
• PH51469

APAR PH51565 (January 2023) improves how Db2 13 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.

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.

FL 504 Activation verifies that the PTF for this APAR is applied on every Db2 13 data sharing member. However, applying the PTF makes the new function available on each member, at any function level.

For more information, see the following related topics:

• -DISPLAY STATS command (Db2)
• DSNT830I
• Fast index traversal
• PH51565

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.

FL 504 Activation verifies that the PTF for this APAR is applied on every Db2 13 data sharing member. However, applying the PTF makes the new function available on each member, at any function level.

For more information, see the following related topics:

• EXPLAIN table changes
• Migration step 24: Convert EXPLAIN tables to the current format
• Activating Db2 13 function level 500 or higher
• PH48053
• PH44421

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.

FL 504 Activation verifies that the PTF for this APAR is applied on every Db2 13 data sharing member. However, applying the PTF makes the new function available on each member, at any function level.

For more information, see the following related topics:

• Incompatible changes in Db2 13
• PH50729