Function level 100 (for migrating to Db2 13 - May 2022)

Contents

New capabilities in function level 100

How to migrate to Db2 13 function level 100

New capabilities in function level 100

In function level 100 (V13R1M100), Db2 runs on Db2 13 code, and virtual storage and many optimization enhancements in Db2 13 become available. However, most new application and SQL capabilities remain unavailable until you activate the function level that introduces them.

The following capabilities and enhancements in Db2 13 become available in function level 100.

Index look-aside optimization

Function level 100 introduces index look-aside optimizations, to improve performance for insert, update, and delete operations. This enhancement optimizes Db2 13 and improves its performance without changing how you configure, monitor, and use Db2.

Sort optimization

Function level 100 introduces enhanced sort optimizations, which were previously introduced for ORDER BY and GROUP BY processing. It applies them to improve the performance of certain operations, such as the following processing enhancements:

• Machine-generated code support for DECFLOAT processing.
• Support for the following enhancements for GROUPING SET, multiple DISTINCT, and PERCENTILE processing:
  • Machine-generated code support.
  • Sort processing can use its own work file.
  • A check for ordered data in the first iteration of a sort.
  • Larger sort trees can be used.
• SUBSTR support for the LISTAGG built-in function, if the start position and length for SUBSTR is a constant.
• Support for avoiding rereading of a single work file, if the sort work file is used, and if IBM Watson® Machine Learning for z/OS® is enabled.
• Support for reducing the length of long VARCHAR keys, if the last key in an ORDER BY list is a VARCHAR over 100 bytes, and if IBM Watson Machine Learning for z/OS is enabled.

This enhancement optimizes Db2 13 and improves its performance without changing how you configure, monitor, and use Db2.

Expanded SORTL usage with learning from execution (IBM® z15®)

Function level 100 introduces expanded SORTL usage based on machine learning on the amount of storage and the number of records being sorted, when run on IBM z15 or later processors. This enhancement optimizes Db2 13 and improves its performance without changing how you configure, monitor, and use Db2.

Improved locking for INSERT to partition-by-growth (PBG) table spaces

Function level 100 introduces retry logic for INSERT operations. An extra attempt is made to obtain a partition lock on a PBG table space after a failed first attempt, thereby increasing the success rate of INSERT operations.

Before this enhancement, only a single attempt was made to obtain a lock on the target partition. If the attempt failed, the target partition was skipped, and the next partition was evaluated. This process would continue until the INSERT operation either successfully obtained a partition lock or it finished searching all existing partitions without obtaining a partition lock.

In most cases, the duration of partition lock contention is short; however, because the INSERT operation did not make another attempt to obtain a lock on a partition after the first failed attempt, the INSERT operation terminated unnecessarily. In many cases, making an extra attempt to obtain a partition lock results in the successful completion of an INSERT operation that otherwise would fail.

This enhancement optimizes Db2 13 and improves its performance without changing how you configure, monitor, and use Db2.

Improved cross-partition search for INSERT to PBG table spaces

Starting in function level 100, if free space is not found in the initial target partition for an insert to a partition-by-growth (PBG) table space, Db2 13 more efficiently uses trailing empty partitions other than the last physical partitions.

In Db2 12 and later, the cross-partition search for PBG table spaces is bidirectional. That is, when an insert operation fails to find free space in the initial target partition, which is selected based on the clustering index, the partition to be searched next can be either an ascending or descending partition sequence number. The searching order is randomly decided at run time to avoid creating a “hot spot” in a single partition. When Db2 12 reaches the first physical partition during a descending partition search, it wraps around and looks at the last physical partition next. As a result, when a PBG table space has many empty partitions at the end, the descending cross-partition search algorithm in Db2 12 often uses the last physical partitions, and it can sometimes leave many unused empty partitions unused in between the first and last partitions.

To prevent this situation, Db2 13 now tracks the highest non-empty partition in the table space in real-time statistics at run time. The tracking starts when a data set is opened and continues until it is closed. When Db2 13 uses a descending cross-partition search and after it reaches the first partition, Db2 13 now searches the cached highest non-empty partition next, instead of the last physical partitions.

This enhancement optimizes Db2 13 and improves its performance without changing how you configure, monitor, and use Db2.

Reduced ECSA storage for IFI buffers

Db2 13 reduces the use of ECSA storage for IFI buffers from a maximum of 50 MB to a fixed 8 MB.

Function level 100 reduces the use of ECSA storage for IFI buffers to a maximum of 25 MB. Then, after function level 500 is first activated, it is further reduced to 8 MB. The storage behavior that is introduced in function level 500 continues even if you later activate function level 100*.

To compensate for the reduction in ECSA storage, you must set aside an extra 50 MB for HVCOMMON and 25 MB for private storage. You can reduce the ECSA storage after function level 500 is activated and Db2 starts using the new storage pools. When Db2 uses the new storage pools, the use of ECSA for the retrieval of IFI records noticeably decreases. You can monitor use of the new storage pools by starting the statistics trace to collect IFCID 225. Then, you can check the SHARED / COMMON storage summary report in the formatted IDCID 225 SMF trace record.

For more information about ECSA storage requirements, see Calculating the storage requirement for the extended common service area.

Reduced agent local below-the-bar (BTB) storage

Starting in function level 100, Db2 supports a greater number of concurrent threads, by using above-the-bar (ATB) agent-local storage for statement text and attribute strings for dynamic SQL statements. In earlier releases, Db2 kept a copy of dynamic SQL statement text and attribute strings in agent local below-the-bar (BTB) storage while the statement is being prepared and executed.

For any specific thread, multiple dynamic SQL statements can be executing depending on the nesting level. The maximum length of an SQL statement is 2 MB, but much more storage can be allocated and the consumption of BTB storage could prevent the number of threads from scaling.

This enhancement optimizes Db2 13 and improves its performance without changing how you configure, monitor, and use Db2.

DBAT availability improvements

Function level 100 introduces changes to Db2 13 DBAT termination processing to support the following objectives:

• Reduction of the overall frequency and number of DBAT terminations.
• Reduction of the number of concurrent DBAT terminations that are caused by a short-term increase in DBAT usage.

This enhancement optimizes Db2 13 and improves its performance without changing how you configure, monitor, and use Db2.

Improved performance when using external security

Function level 100 introduces the following enhancements to improve performance for Db2 environments that use external security:

• Db2 caches plan authorization checks that the access control authorization exit (ACAE) routine uses. Previously, successful authorization checks on the EXECUTE privilege for plans were not cached if those checks were completed by the ACAE routine. This enhancement provides consistent behavior in plan authorization cache behavior regardless of whether security is managed with Db2 facilities or with the ACAE.

  To enable plan authorization caching when the ACAE routine is being used, the AUTHEXIT_CACHEREFRESH subsystem parameter must be set to ALL and the z/OS release must be 2.5 or later. Db2 caches the results of authorization checks on the EXECUTE privilege for plans if a profile in the RACF® class MDSNPN permits access to the plan. Db2 does not cache the results if access is allowed due to administrative authority, such as the DATAACCESS or SYSADM authorities.

  For more information, see Caching of EXECUTE on plans, packages, and routines.

• Db2 is enhanced to cache more authorization IDs per plan. For more information, see Caching authorization IDs for plans.
• The AUTHCACH subsystem parameter is removed to simplify plan authorization cache management. Use the CACHESIZE bind option on the BIND PLAN subcommand to specify the size of the authorization cache for that plan. The default value is 4K.
• If the AUTHEXIT_CACHEREFRESH subsystem parameter is set to ALL, the global authentication cache takes the timestamp into consideration for user IDs that were authenticated by using credentials other than multi-factor authentication (MFA). For more information, see Global authentication cache.
• When you specify a key label for data set encryption, the specified key label cannot refer to an archived key for decryption operations only. Key labels can be specified by using the ENCRYPTION_KEYLABEL subsystem parameter or any of the following SQL statements:
  • ALTER STOGROUP
  • ALTER TABLE
  • CREATE STOGROUP
  • CREATE TABLE
  If the specified key label refers to a decryption-only archived key, the key label specification fails and returns an error message. For more information on decryption-only archived keys, see ICSF: Limit archived keys to decrypt operations only.

Reduced ECSA storage use for distributed data facility (DDF) processing

Function level 100 reduces the amount of ECSA storage that is used for processing DDF threads to be equivalent to processing local threads. The previous recommendation was an extra 2 KB per DDF thread. For more information, see Calculating the storage requirement for the extended common service area.

Improved storage monitoring and contraction

Function level 100 introduces the following enhancements to provide storage constraint relief:

• When below-the-bar Db2 storage consumption exceeds 64-percent threshold, Db2 automatically begins contraction of private storage pools.
• When extended common service area (ECSA) storage consumption exceeds the 85-percent threshold, Db2 automatically begins contraction of storage pools that are allocated in the ECSA.

In both cases, the storage contraction stops after storage consumption drops below the threshold.

This enhancement optimizes Db2 13 and improves its performance without changing how you configure, monitor, and use Db2.

Dynamic management of CF lock storage by IRLM

With IRLM 2.3 at function level 50C or higher, which is included with Db2 13, IRLM can now invoke an existing capability in z/OS Sysplex Services for Data sharing (XES) to dynamically expand the coupling facility (CF) lock structure storage size. This new internal monitoring capability in IRLM can improve lock request processing throughput, by expanding the CF lock structure size to process lock requests, instead of rejecting them.

The existing XES monitoring of the CF lock structure use is defined in the coupling facility resource management (CFRM) policy as a threshold percentage value, and it is enabled with a default of 80% when it is not equal to zero. This monitoring retrieves statistics on the CF lock structure every 60 seconds when the storage usage is less than the threshold and every 30 seconds when the storage usage is equal to or greater than the full threshold. IRLM can determine the storage needed at a higher level of granularity than the existing monitoring by XES of CF structure, especially when a spike in locking activities results in rejection of lock requests due to insufficient Record List Entries (RLEs) even before z/OS has a chance to start the CF lock structure alteration.

The existing structure monitoring by XES handles storage contraction, and it contracts all eligible structures in the coupling facility by 10 percent in each cycle when the entire coupling facility is at or more than 90% full.

IRLM issues the following messages when it adjusts the CF lock structure storage size: DXR189I and DXR190I.

Improved Db2 installation and migration process for customizing the amount of private storage for IRLM locks

In the MAX STORAGE FOR LOCKS field on installation panel DSNTIPJ, you can specify the maximum amount of private storage above the 2 GB bar for the IRLM lock control structure. In earlier Db2 releases, you can specify a value of only up to 102400 megabytes. Starting in Db2 13, you can specify a value of up to 16384 petabytes.

For more information, see MAX STORAGE FOR LOCKS field and MAX LOCK STORAGE UNIT field.

Memory usage reduced for REBIND with APREUSE

Function level 100 introduces reduced storage usage during BIND/REBIND for queries that involve many tables. This enhancement optimizes Db2 13 and improves its performance without changing how you configure, monitor, and use Db2.

Partition range support in IFCID 306 for users of replication applications

Function level 100 introduces the capability for applications that collect IFCID 306 trace records for the log read process to request filtering on a range of partitions. For more information, see the new WQLSFLG flag and WQLSDBPP mapping in Qualification fields for READS requests.

EDITPROC support in IFCID 306 for users of nonproxy mode replication applications

Function level 100 introduces the support for any user of IFCID 306 in non-proxy mode to use EDITPROC support as an on-request function. For more information, see the new values X'04', X'05', X'06', and X'07' for the 1-byte WQALLOPT field in Qualification fields for READS requests.

Relative page numbering for new PBR table spaces

Starting in function level 100, the default value of the PAGESET_PAGENUM subsystem parameter is changed to RELATIVE. The PAGESET_PAGENUM subsystem parameter specifies the default value that Db2 uses when you omit the PAGENUM option in CREATE TABLESPACE or CREATE TABLE statement for a partition-by-range (PBR) table space. That is, it specifies whether Db2 creates the table space and associated partitioned indexes to use relative page numbers (RPN) or absolute page numbers (APN) across partitions. RPN is the strategic direction for PBR table spaces in Db2. If you accept the new default and create all new PBR table spaces with relative page numbers, you can avoid costly future conversions. Converting from absolute to relative pages numbers always requires a REORG of the entire table space.

See PAGE SET PAGE NUMBERING field (PAGESET_PAGENUM subsystem parameter).

Improved default statistics collection granularity

Starting in function level 100, that default value of the STATIME_MAIN subsystem parameter is changed to 10 seconds. The STATIME_MAIN subsystem parameter specifies the time interval in seconds, for collection of interval-driven statistics that are not collected at the interval that is specified by the STATIME subsystem parameter. With the default statistics interval set at 60 seconds in earlier releases, Db2 database administrators and system programmers cannot identify true workload peaks by using Db2 statistics for subsystem level performance tuning and planning. Similarly, a slowdown of 5 - 15 seconds is difficult to diagnose with statistics collected at a 60-second interval.

See MAIN STATS TIME field (STATIME_MAIN subsystem parameter)

REPAIR utility WRITELOG for decompression dictionaries

Function level 100 introduces the capability to write a decompression dictionary log record up to the maximum log record size supported by Db2. This capability can be used after you run an application or utility that builds a new dictionary without writing the old one to the log. So, it is useful for replication products that would otherwise require a refresh of the replication target. After the decompression dictionary is successfully written, the REPAIR utility issues message DSNU3335I with the location of the log record. Applications can then use this information to insert a SYSIBM.SYSCOPY record.

For more information, see the option descriptions for TYPE X’4002 and SUBTYPE ’X’000A’ in Syntax and options of the REPAIR control statement, and DSNU3335I.

Enhanced space-level recovery with the RECOVER utility

Starting in function level 100, the RECOVER utility supports space-level recovery (where DSNUM ALL is specified or is the default), even if the image copies were created at the partition or piece level for the following objects:

• Universal table spaces (UTS).
• Index spaces or indexes for a UTS.
• XML UTS with a base table that resides in a UTS.
• Auxiliary index spaces or indexes for an XML UTS with a base table that resides in a UTS.
• LOB table spaces with a base table that resides in a UTS.
• Auxiliary index spaces or indexes for LOB table spaces with a base table that resides in a UTS.

Space-level recovery of these supported object types is processed as partition-level or piece-level recoveries in the RESTORE phase. Db2 catalog and directory objects that meet the criteria above are supported by this enhancement. When the list contains a mixture of supported and unsupported object types, recovery behavior for the unsupported object types is the same as Db2 12 or earlier releases.

When the RECOVER utility is invoked (with DSNUM ALL specified or as the default) in Db2 12 or earlier releases, for space-level recovery of table spaces, index spaces, or indexes, an error message is issued if the image copies were created at the partition or piece level. The DSNU512I (DATASET LEVEL RECOVERY IS REQUIRED) error message indicates that recovery cannot be done at the space level. Recovery must instead be requested at the partition or piece level. Objects with these errors are not recovered and the RECOVER ends with RC8 indicating errors were encountered.

For more information, see Recovering a data set or partition.

Column names longer than 30 bytes

Function level 100 extends the maximum length of a column name from 30 bytes of EBCDIC, up to 128 bytes with limited support for using the longer column names. The longer column names can be used when the TABLE_COL_NAME_EXPANSION subsystem parameter setting is ON. Although you can now define a column with a name up to 128 bytes, column names with a length greater than 30 bytes of EBCDIC might be truncated on a character boundary. Column names returned in an SQLDA contain 30 bytes at most. APIs that do not use the SQLDA to obtain a column name might return complete column names.

For more information, see Column names longer than 30 bytes and TABLE_COL_NAME_EXPANSION in macro DSN6SPRM.

Db2 support for z/OS continuous compliance

Customers are looking for solutions that provide evidence that they can trust the security of z/OS systems. z/OS 2.5 introduces new SMF type 1154 records that provide evidence of security compliance. Participating components and products can collect and write compliance data to their associated SMF 1154 subtype records. Function level 100 adds the capability to collect evidence on Db2 subsystems' compliance by writing SMF 1154 subtype 81 records. For more information, see Db2 evidence for z/OS continuous compliance and What is new in z/OS (V2R4 - V2R5).

More concurrent open data sets in z/OS 2.5

In function level 100 and z/OS 2.5 or later, dynamic allocation processing supports system work blocks (SWBs) for data sets that are in 64-bit storage. This new dynamic allocation function helps reduce below-the-bar storage usage for address spaces that allocate large numbers of data sets.

To enable this feature, complete one of the following actions:

• Update the ALLOCxx parmlib member to set the SYSTEM SWBSTORAGE value to ATB. The default value is SWA, which indicates that SWBs reside in 24-bit storage or 31-bit storage. ATB indicates that SWBs are allowed to reside in 64-bit storage.
• Issue system command SETALLOC SYSTEM,SWBSTORAGE=ATB.

It is best to update the ALLOCxx parmlib member because the change remains effective across IPLs. If the SETALLOC command is used to enable SYSTEM SWBSTORAGE, you must restart Db2 for the change to take effect.

Previously, the CLIST calculation for data set storage size used 5 KB per open data set. With the new dynamic allocation function, the storage that is required per open data set is reduced to 4 KB. You must adjust the calculation for data set storage size. For more information, see Calculating data set control block storage.

More efficient cleanup for above-the-bar storage

Function level 100 introduces improvements to how Db2 manages and frees above-the-bar storage, especially to reduce the disruptive impact of issuing excessive IARV64 REQUEST(DISCARDDATA) service requests.

Db2 13 no longer issues the IARV64 REQUEST(DISCARDDATA) request during thread deallocation or at certain intervals of COMMIT, and enhanced storage management is no longer controlled by the REALSTORAGE_MANAGEMENT subsystem parameter, which is also removed. In Db2 13, the storage is returned to the memory object. A system-level timer drives contraction for the memory object to release unused frames back to z/OS. Also, Db2 13 periodically checks the available free frames before the LPAR starts to page (by using the z/OS calculations for available free frames and LO threshold). If this value becomes lower than 5 times the z/OS calculated OK threshold, the memory object contraction is triggered.

Index page split instrumentation enhancements

Function level 100 introduces IFCID 396 to provide detailed information about index splits. When data is inserted into a base table, the corresponding indexes are modified accordingly. As a result, performance of SQL insert operations can be impacted during the index split process, where synchronous I/O is required under data sharing and the group buffer pool GBP is dependent on the related index page sets.

The existing IFCID 359 records already contain information index split events. However, the information that is recorded is not detailed enough to identify the cause of performance issues. IFCID 359 is also disabled by default, and it can miss capturing some abnormal index split situations.

396 is always enabled by default under statistics trace class 3 and performance trace class 6. An IFCID 396 record is generated when an index split is considered an abnormal split process, such as when the total elapsed time is greater than 1 second. The generated IFCID 396 record contains information such as the DBID, PSID, member ID, URID, page number, and more. The information is helpful for both customers and IBM Support to identify the root cause of INSERT performance issues.

For more information, see the IFCID 396 descriptions in IFCID changes in Db2 13 and in the Trace field descriptions.

Starting after the catalog level V13R1M501 update (which requires function level 500), the following RTS columns in the SYSIBM.SYSINDEXSPACESTATS catalog table are populated. They record and aggregate general index split information since last table reorganization, index rebuild, or load replace:

Column name	Data type	Description
REORGTOTALSPLITS	INTEGER	The number of index splits since last reorganization or rebuild.
REORGSPLITTIME	BIGINT	Aggregated elapsed time for all index splits since last reorganization or rebuild.
REORGEXCSPLITS	INTEGER	The number of abnormal index splits (such as elapsed times greater than 1 second) since last reorganization or rebuild.

Db2 starts populating these RTS columns as soon as the catalog level V13R1M501 update completes, even before function level 501 is activated.

Accounting information on the longest wait times for common suspension types

When Db2 transactions take a long time, it is important to determine:

• Where the transaction time is spent.
• Whether the problem is many short suspensions or a few long suspensions.
• Which resources the suspensions are for.

Before this enhancement, detailed performance traces were required to find this information. This enhancement simplifies the diagnosis process by providing information in Db2 accounting records for the longest wait time for a number of common suspension types. The following suspension types are included:

• IRLM lock suspensions
• Db2 internal latch suspensions
• Waits for Db2 synchronous or asynchronous I/O
• Waits for Db2 service tasks
• Waits for page latches

For more information, see IFCID changes in Db2 13 and the DSNWMSGS file.

IBM z16™ group buffer pool (GBP) residency time

Starting in function level 100, two new statistics are added to relevant group buffer pool statistics storage areas:

• The weighted-average time a data area resides in a storage class before it is reclaimed, or 0 if the group buffer pool has not been reclaimed.
• The weighted-time a directory entry resides in a storage class before it is reclaimed, or 0 if the group buffer pool has not been reclaimed.

The new residency time statistics are supported only if Db2 13 runs in the following environment:

• z/OS 2.4 or later, with PTFs required for cache residency time metrics applied.
• IBM z16 or later processors.
• Coupling facility at CF Level 25 or higher. For more information, see CFLEVEL and operating system level coexistence.

You can access these metrics with the IFCID record trace and the -DISPLAY GROUPBUFFERPOOL command. For more information, see "DSNB820I: Average residency times" in DSNB750I.

Subsystem parameter simplification

Function level 100 introduces changes to the default values for various subsystem parameters to match current best practices. It also removes a number of obsolete subsystem parameters. For a list of these changes, see Subsystem parameter changes in Db2 13.

New-function and service APARs for Db2 12

Changes from many Db2 12 APARs are built-in when you migrate to Db2 13:

• Enhancements from many new-function APARs in Db2 12 are built-in when you migrate to Db2 13. If you do not apply the PTFs for such APARs in Db2 12, plan for these changes to take effect when you migrate to Db2 13. See the APARs with availability dates earlier than 2022-06 in New-function APARs for Db2 12.
• Also check for other Db2 12 service-stream APARs for Db2 12 that were available before general availability of Db2 13. If you did not already complete any required actions that are described in ++HOLD data for these APARs, make plans to do so before migration to Db2 13.

New-function APARs after Db2 13 GA

Many changes introduced in new-function APARs after the general availability of Db2 13 take effect immediately when you apply the PTF in function level 100 or higher. For more information, see New-function APARs for Db2 13.

How to migrate to Db2 13 function level 100

To migrate to Db2 13 function level 100, complete the following tasks:

1. In Db2 12, identify and resolve incompatible changes and activate function level 510 (V12R1M510). You can run the pre-migration job DSNTIJPE in Db2 12 to identify the incompatible changes. For more information, see Verify Db2 13 premigration activities and activate function level 510 in Db2 12.
2. Verify that every member was restarted with the fallback SPE applied in Db2 12.
   Important: Inactive members that never started in Db2 12 with the fallback SPE (APAR PH37108) applied cannot start after the first data sharing member is migrated to Db2 13 at function level 100.
3. Migrate the Db2 subsystem or data sharing group to Db2 13, as described in Migrating your Db2 subsystem to Db2 13 or Migrating an existing data sharing group to Db2 13 .