New-function APARs for Db2 12 in 2020

The IBM z15 processor includes the Integrated Accelerator for Z Sort coprocessor, which can help to reduce CPU usage and improve elapsed time for eligible sort workloads.

With PH31684, when Db2 for z/OS applications run on a system that has the IBM Integrated Accelerator for Z Sort coprocessor, sort operations in queries that include GROUP BY or ORDER BY clauses can experience better performance.

This APAR (PH30780 - December, 2020)introduces new profile table actions, MONITOR ALL CONNECTIONS and MONITOR ALL THREADS, in the KEYWORDS column of the DSN_PROFILE_ATTRIBUTES table. You can use the new keywords to specify warning and exception thresholds that apply _cumulatively_ across all connections and threads under the control of the default location profile. The new MONITOR ALL actions are independent of the existing MONITOR CONNECTIONS and MONITOR THREADS, which means they can be used together or separately.

Before this new capability, profile monitoring allowed limiting the number of connections and threads, through the use of existing keywords MONITOR CONNECTIONS and MONITOR THREADS, including for unknown applications on an individual IP address basis. However, it was not possible to limit the cumulative number of connections and threads for all unknown locations. An excessive number of unknown locations accessing Db2 can exceed system limits for connections or threads and cause an outage.

The new keywords can be specified only for profiles that use the default location filtering criteria. That is, the LOCATION column in DSN_PROFILE_TABLE contains one of the following values representing any IP address: '*', '::0', or '0.0.0.0'.

With PH30093 (December, 2020) TEMPLATE utility control statement is enhanced to support large block interface (LBI) when allocating data sets. To request LBI, specify the new BLKSZLIM keyword with an upper limit for the data set block size. When the data set is dynamically allocated,z/OS determines the optimal block size, not to exceed the specified upper limit.

Using LBI can help decrease CPU use and elapsed time for utilities and thus improve overall utility performance. LBI allows you to increase the block size for data sets and allows utilities to handle much larger blocks with BSAM. LBI is mainly useful when creating new data sets on tape.

Prior to APAR PH28017 (October 2020), when a connection to Db2 was requested, the maximum length of the authorization ID needed to be seven or fewer bytes. If the authorization ID was eight bytes, the last byte was truncated, which caused a connection request to Db2 to fail. This APAR allows IBM products, vendor tools, and other applications that are authorized programs to pass eight-byte authorization IDs to Db2.

APAR PH27043 (October 2020) enhances the RECOVER utility to support redirected recovery of table spaces using the recovery resources of different table spaces. With redirected recovery, Db2 system administrators can more accurately determine how long the recoveries will take, check for recovery issues, and generate a replica of their production data at different points in time with transactional consistency without affecting the production data and without impact to production applications.

The new FROM option is added to the RECOVER utility to restore image copies and apply Db2 log records from source table spaces to target table spaces.

APAR PH28280 (October 2020) is the last of a series of four that adjust Db2 subsystem parameters defaults to better align with current best practices, or remove obsolete subsystem parameters to simplify installation, migration, and configuration of Db2 subsystems. (The previous APARs are PH26317, PH24358, and PH21370.) APAR PH28280 removes the following Db2 subsystem parameters.

Function level 508 (PH29392 - October 2020) introduces support for moving tables from deprecated multi-table simple and multi-table segmented table spaces to partition-by-growth universal table spaces (UTS), without taking an outage, to improve application and database operations and performance.

Before this APAR, Db2 inadvertently allows the illegal syntax.

Starting in Db2 12 function level 100, APAR PH28183 (September 2020) introduces the support for the Db2 REORG TABLESPACE utility to invoke DFSORT with the new IBM Integrated Accelerator for Z Sort feature available in the latest IBM z15 mainframe hardware and above. The new capability applies only to the variable length data sort for data records of a target table for the REORG TABLESPACE utility. It improves the CPU and elapsed time in the sort processing by DFSORT utilizing more in-memory sorting.

The new UTILS_USE_ZSORT subsystem parameter specifies whether Db2 utilities use the IBM Integrated Accelerator for Z Sort interface when invoking DFSORT for sort processing.

With PH10358 (August 2020) The REPAIR utility is enhanced so that you can use the DBD statement with the TEST or DIAGNOSE options on the work file database DSNDB07. Previously, those options were restricted from being used with DSNDB07. REPAIR DBD REBUILD is still not allowed with DSNDB07.

Starting in Db2 12 function level 100 with APAR PH28476 (August 2020), the LOAD utility always maintains the value of MAXASSIGNEDVAL in SYSIBM.SYSEQUENCES for any loaded identity columns. For some clients migrating to Db2 12, the new Db2 12 behavior is an incompatible change because of logic in their applications that relies on the previous behavior.

Prior to Db2 12, the LOAD utility did not maintain the value of MAXASSIGNEDVAL when the identity column value was provided by the user. The identity column value can be provided by the user if the column is defined as GENERATED BY DEFAULT or if the column is defined as GENERATED ALWAYS and the OVERRIDE(IDENTITY) option is specified in the LOAD statement.

With this APAR, you can disable the new behavior in Db2 12 by specifying UPDMAXASSIGNEDVAL NO in your LOAD statements. To aid your migration to Db2 12, you can begin adding this new option to your LOAD jobs in Db2 11. Although UPDMAXASSIGNEDVAL NO is accepted in Db2 11, it has no effect until Db2 12.

With APAR PH26131 (August 2020) You can now use the LOAD utility to load data into read-only objects if the LOAD_RO_OBJECTS subsystem parameter is set to Y. With this setting, LOAD is supported for read-only target objects, including base table, LOB, and XML table spaces and indexes. The default value for LOAD_RO_OBJECTS is N, which disallows loading data into read-only target objects with the LOAD utility.

Before APAR PH26131, Db2 always issued message DSNU590I with reason code 00C90080 for any attempt to load a read-only target object.

APAR PH21341 (August 2020) introduces the capability to enable Db2 to store multi-factor authentication (MFA) credentials in the Db2 global authentication cache for distributed clients that do not use sysplex workload balancing (WLB) or seamleass failover, and to specify the amount of time that the entry remains if unused.

The new MFA_AUTHCACHE_UNUSED_TIME subsystem parameter specifies how long in seconds that multi-factor authentication (MFA) security credentials from a distributed client (DRDA or REST) can remain unused in the Db2 global authentication cache before new security credentials must be provided.

An authentication cache entry is considered unused until its related MFA-based security credentials are reused by the same client IP address in a subsequent authentication request. The unused time is reset each time that the credentials are used. The authorization cache entry does not contain the text of the security credentials.

To avoid situations where the LOAD utility fails because it could not gain exclusive control over the target object, APAR PH24369 (August 2020) introduces the new FORCE option for LOAD. This option is similar to the existing FORCE option for the REORG TABLESPACE utility. You can use it to force the cancellation of blocking claimers during drain processing to ensure that the utility completes.

With PH25593, if you run CHECK DATA with SHRLEVEL CHANGE and no inconsistencies are found, any existing CHKP, ACHKP, or AUXW states on the objects are reset. Similarly, if you run CHECK LOB with SHRLEVEL CHANGE and no inconsistencies are found, any existing CHKP or AUXW states on the objects are reset. (Note that ACHKP applies to base table spaces only, and therefore cannot be reset by CHECK LOB.)

With APAR PH19072 (August 2020), the LOAD utility can now allocate the associated data sets for target auxiliary objects with the DEFINE NO attribute, regardless of whether these objects are populated by LOAD. To specify that LOAD is to allocate data sets for DEFINE NO LOB and XML objects, specify the new utility option DEFINEAUX YES. The default behavior is the previous LOAD behavior: the utility allocates data sets for DEFINE NO auxiliary objects only when data is loaded into them.

The first iteration of IBM Db2 for z/OS Developer Extension for Visual Studio Code (Db2 Developer Extension) is available as a free download from the [Microsoft Visual Studio Code Marketplace](https://marketplace.visualstudio.com/items?itemName=IBM.db2forzosdeveloperextension).

This extension provides application developers with language support for the SQL syntax that is used to define, manipulate, and control data in Db2 for z/OS databases. It is supported by Visual Studio Code and by the [Eclipse Theia](https://theia-ide.org/) IDE platform.

APAR PH26317 (July 2020) removes the following Db2subsystem parameters to simplify installation, migration, and configuration of Db2subsystems.

APAR PH25940 (July 2020) adds message DSNS005I to explain what situation caused Db2to begin storage contraction. DSNS005I is issued after DSNV516I, which indicates that STORAGE CONTRACTION MODE is beginning. Storage contraction is often related to the setting of the REALSTORAGE_MANAGEMENT subsystem parameter.

You can now use the LOAD utility to load data into row change timestamp columns that are defined as GENERATED ALWAYS. For example, if you unload data from a table with a row change timestamp column, you can preserve those timestamp values when loading the data into the same or another table. Previously, if the row change timestamp column in the target table was defined as GENERATED ALWAYS, the utility could not load those timestamp values. With APAR PH25572 (July 2020), you can now override this GENERATED ALWAYS column by specifying the new OVERRIDE(ROWCHANGE) option in the LOAD statement.

With APAR PH23105 (June 2020), the LOAD utility can now sort input records in clustering order before loading a table space. Therefore, after the utility operation is complete, the loaded records are in clustering order without requiring you to take any additional action. (Existing records in the table space are not sorted.) To specify that you want LOAD to sort the input records first, use the new PRESORT option.

Having records in clustering order can improve the performance of certain SQL operations such as grouping, ordering, or comparisons other than equal. Previously, to ensure that loaded data was in clustering order, you had to either run the REORG utility after loading the data or add a prior step in your JCL to presort the input data. This APAR eliminates those extra steps.

APAR PH22838 (June 2020) changes the GETPAGES columns of the real-time statistics (RTS) tables, SYSIBM.SYSTABLESPACESTATS and SYSIBM.SYSINDEXSPACESTATS, so that the values now wrap after they reach the maximum supported positive BIGINT value, which is 9223372036854775807.

Before now, the GETPAGES values in the RTS tables remained static after reaching the maximum value, until the next REORG reset it to 0. However, the static maximum values were not useful for clients who wanted to monitor ongoing getpage activity in specific intervals. Now you can continue seeing getpage activity after the maximum values was reached. To determine when the wraparound occurred, you can analyze the temporal history tables for the RTS tables.

APARs PH15742, PH16111, PH24097, and PH25342 (June 2020) introduce support for Db2ZAI 1.3.0. Additional capabilities added by this version of Db2ZAI include automated SQL regression detection and resolution. Db2ZAI will now detect when an access path has degraded compared to a prior access path, and will automatically lock-in and fall back to the better-performing access path. This self-healing capability enables Db2ZAI to resolve regressions without intervention by a database administrator, thus saving valuable time and effort.

Another change with this version is that Db2ZAI 1.3.0 now provides an enhanced exception analysis experience, in which exceptions are categorized into general regions of interest to system programmers (such as CPU, storage, and connectivity) to allow a more immediate identification of problem areas.

Db2ZAI 1.3.0 also introduces distributed connection control, which provides protection for Db2 for z/OS from unintentional disruptions and intrusions that can originate from remote applications.

With APAR PH22944 (June 2020), you can now use the LOAD utility to load data that is the 3-byte packed decimal representation of a date in DATE_P format (X'YYDDDs'). To specify this format, use the new DATE DATE_P option in the field specification. For details on this new syntax, see Syntax and options of the LOAD control statement.

Function level 507 (PH24371 - June 2020) introduces application granularity for locking limits, deletion of old statistics when using profiles, CREATE OR REPLACE for procedures, and new passthrough-only and acceleration support with IBM Db2 Analytics Accelerator.

APAR PH23238 (June 2020) introduces the capability to enable the fast index traversal capability (sometimes also called "fast traverse blocks" or "FTBs") for specific selected indexes. The general intent is for the use of FTBs to be controlled by Db2 through automation. However, clients have requested the ability to limit the use of FTBs to specific indexes, to enable focused testing as they prepare to exploit FTBs at scale.

When either type of SELECTED setting is used, you control the specific indexes that can use FTBs by inserting a rows for the indexes with ACTION='A' in the SYSIBM.SYSINDEXCONTROL catalog table.

APAR PH24358 (June 2020) removes the following Db2 subystem parameters to simplify installation, migration, and configuration of Db2 subsystems.

APAR PH24619 (June 2020) introduces the ability for Db2 native REST service users to optionally set Db2 client information registers by adding HTTP request header fields to a the Db2 native REST service request. In addition to the new HTTP request header field, this APAR also clarifies and enforces the Unicode code points that can be used for the Db2 REST HTTP request header client information fields.

Db2 native REST service users can override the current Db2 REST generated default values that are assigned to the CURRENT CLIENT_ACCTNG, CURRENT CLIENT_APPLNAME, CURRENT CLIENT_CORR_TOKEN, CURRENT CLIENT_USERID, and CURRENT CLIENT_WRKSTNNAME values. If the new header fields are not present, then the Db2 client information special registers will be populated with the default values.

APAR PH10826 (May 2020) introduces the capability to identify applications that might be incompatible with catalog migrations and updates.

Before scheduling an online migration, you can issue the new DISPLAY BLOCKERS command or invoke the new BLOCKING_THREADS built-in function to identify locks and claims that might interfere with migration processing. This information allows you to take appropriate actions beforehand to minimize the possibility of a failed migration.

The new command and function are also available in Db2 11, and can be used for both system and non-system databases. For more information, see Identify applications that are incompatible with online migration.

APAR PH19242 (April 2020) introduces the capability to estimate space savings for data in universal table spaces (UTS) when Huffman encoding is used with the hardware compression support in IBM z14 or newer hardware. It introduces a new optional COMPTYPE parameter for the DSN1COMP utility and also improves the format of the DSN1COMP compression report in message DSNI1940I.

If you run DSN1COMP for a UTS on IBM z14 or newer hardware, the default report now contains estimates for both fixed-length compression and Huffman encoding. You can also specify The new COMPTYPE parameter to specify which compression estimates to include in the compression report.

If you run DSN1COMP on IBM z13 or earlier hardware, or for a non-UTS table space, the default report also uses the new format, but it reports only the fixed-length compression estimates. However, if you specify COMPTYPE(ALL) or COMPTYPE(HUFFMAN) for a UTS on IBM z13 or earlier hardware, the compression report includes the Huffman compression estimates that you can expect after migration to IBM z14 or newer hardware.

APAR PH18658 (April 2020) introduces more granular control for the collection of key Db2 statistics trace records, which were previously collected at fixed one-minute intervals. With faster processors, monitoring at a more granular interval can help in understanding the performance peak and analyzing slow down.

The new MAIN STATS TIME field is added on panel DSNTIPN for the STATIME_MAIN subsystem parameter. STATIME MAIN specifies the time interval, in seconds, between collection of certain interval-driven Db2 statistics trace records that are not collected at interval specified by the existing STATIME parameter. Previously these statistics were always collected at a fixed one-minute interval.

APAR PH21058 (March 2020) introduces the capability to specify a location-name value in the DSN FREE SERVICE subcommand, to remotely free a service that was previously copied, without requiring you to log in to the remote system that contains the service.

The location name must be defined in the SYSIBM.LOCATIONS table. If this table does not exist or the DBMS is not found, you receive an error message. If the location name is specified, the name of the local Db2 subsystem must be defined. If you omit location-name, the default is the local Db2 subsystem.

Although DSN BIND SERVICE COPY is the only way for a Db2 for z/OS requester to create a service on a Db2 for z/OS server, the remote FREE SERVICE support added by this APAR supports freeing any native REST service remotely, regardless of whether it was created with the CREATE SERVICE API or a DSN BIND SERVICE or BIND SERVICE COPY command.

A sample Db2 software service template is also now available for services that rapidly provision an n-way Db2 data sharing group (cluster), in IBM Cloud Provisioning and Management for z/OS (CP&M). The files (z/OSMF artifacts) of this sample software service template are compressed into the Db2ProvisionSystemDS.pax file, which you download onDb2ZTools/DevOps/Db2SystemServices on Github.

APAR PH19888 (March 2020) 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 6.0.0.

Prior to this APAR, Db2 allows the value 'UNI_SIMPLE' or 'UNI' as a locale 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. Locale '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. With this APAR, Db2 allows a new locale, UNI_60, which specifies that z/OS Unicode Conversion Services will be used with only "normal" casing capabilities and use Unicode Standard 6.0.0.

APAR PH18944 (March 2020)adds additional information to the DSNI055I and DSNI087I to improve serviceability for the insert algorithm 2 feature introduced in Db2 12. Specifically, it adds URID, RBA, and LRSN information for agents that enable or disable the use of insert algorithm 2.

With APAR PH19073 (March 2020), the PARALLEL option of the LOAD utility has several documented restrictions. Prior to PH19073, the LOAD utility failed with RC=8 when PARALLEL was specified in those restricted situations. With this APAR, the LOAD utility ignores PARALLEL in those situations and returns message DSNU073I to inform you that PARALLEL was ignored, and continues processing. For the specific list of situations where this option is ignored, see the description of the PARALLEL option in Syntax and options of the LOAD control statement.

APAR PH15896 (February 2020) enforces APREUSE(WARN) and APREUSESOURCE(CURRENT) during autobind operations so that autobinds result in a comparable level of performance.

For Db2 10 and later packages, Db2 will attempt to reuse the access path from the current package copy. You don't need to change any system parameter settings, and this behavior is independent from the plan management and APREUSE that currently exist in the package.

For Db2 9 and earlier packages, the new behavior is not applied. To retain the access path from the current package copy, you must issue a REBIND command with APREUSE(WARN) and APREUSESOURCE(CURRENT).

Running certain utilities on tables or table spaces with replication enabled can cause the data in the replication target tables to be inconsistent until the tables are refreshed. APAR PH14363 (January 2020)) introduces a new subsystem parameter, UTILS_BLOCK_FOR_CDC, that prevents these utilities from updating replicated tables (those tables with the DATA CAPTURE CHANGES attribute) and therefore helps keep the replicated tables consistent.

Prior to PH11309 (January 2020), the BINDAGENT privilege could only be granted between a specific grantor and grantee, preventing some automated processes from granting BINDAGENT to the binder when necessary. This APAR enables SECADM authority to grant BINDAGENT privileges to the binder, which allows the grantee to bind and rebind a plan or package by specifying any owner.

APAR PH12920 (January 2020) updates the ADMIN_DS_LIST stored procedure to return a flag that indicates whether the data set is encrypted with z/OS DFSMS data set encryption, and it also now returns the encryption key label. These values are returned in the new ENCRYPT_TYPE and ENCRYPT_KEY_LABEL columns in the result table. DFSMS APAR OA57544 is a prerequisite for this enhancement.

APAR PH11423 (January 2020) makes it easier to see the profile options that are being used when gathering statistics. When you run a utility with the USE PROFILE option, the profile options are listed in the SYSPRINT output data set. Prior to this APAR, if you wanted to see the profile options that are used, you first had to run the OPTIONS utility with the PREVIEW option to see the profile options. Then you had to execute the utility. With this APAR, you can see the profile options and execute the utility all in one step. The profile options continue to be displayed in the same message, DSNU1376I, as they were prior to this APAR by OPTIONS PREVIEW.