Function level 501 (Db2 13 installation or migration - May 2022)

Contents

New capabilities in function level 501

How to activate function level 501

New capabilities in function level 501

Function level 501 activates the following new capabilities in Db2 13.

Allow applications to specify a deadlock resolution priority

Function level 501 introduces the SYSIBMADM.DEADLOCK_RESOLUTION_PRIORITY built-in global variable to allow an application to specify a priority to use when resolving a deadlock situation with other threads. When an application sets and uses this built-in global variable (by using a SET assignment-statement SQL statement), the Db2 subsystem uses that value as a relative weighting factor to resolve deadlock situations with other threads.

For more information, see DEADLOCK_RESOLUTION_PRIORITY.

You can also use Db2 profile tables to specify values for the new SYSIBMADM.DEADLOCK_RESOLUTION_PRIORITY built-in global variable for both remote and local applications. See Setting built-in global variables by using profile tables.

Profile table enhancements for application environment settings

Db2 13 introduces the capability to use system profiles for local applications in certain situations. Previously, the initial values for special registers and system built-in global variables can be specified in the Db2 profile tables, but they are used only for initialization with distributed threads. The new Db2 profile table support for local applications requires Db2 to be started with the DDF subsystem parameter set to AUTO or COMMAND. See DDF STARTUP OPTION field (DDF subsystem parameter).

Starting in function level 501, Db2 profile tables can be used for both local and remote applications in the following situations:

• You can specify values for the new SYSIBMADM.DEADLOCK_RESOLUTION_PRIORITY built-in global variable. See DEADLOCK_RESOLUTION_PRIORITY and Setting built-in global variables by using profile tables.

Real-time statistics scalability

As data volumes become larger, the widths of some columns in the real-time statistics tables are not large enough to accommodate larger values. In addition, during high volume processing, lock escalation might occur on the real-time statistics history table spaces. Lock escalation can negatively affect concurrency and performance.

Starting after function level 501 is activated, the following changes to the real-time statistics tables and table spaces are introduced to provide greater capacity and concurrency:

• In real-time statistics tables SYSIBM.SYSTABLESPACESTATS and SYSIBM.SYSINDEXSPACESTATS, and their associated history tables SYSIBM.SYSTABSPACESTATS_H and SYSIBM.SYSIXSPACESTATS_H, some column data types are BIGINT instead of INTEGER, or INTEGER instead of SMALLINT.
• Lock escalation is disabled on the following table spaces: DSNDB06.SYSTSTSS and DSNDB06.SYSTSISS for the RTS tables; and DSNDB06.SYSTSTSH and DSNDB06.SYSTSISH for the RTS history tables

For more information, see SYSTABLESPACESTATS catalog table and SYSINDEXSPACESTATS catalog table.

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

Collection of real-time and historical information about utility execution

To improve utility management, function level 501 introduces the ability to collect real-time and historical information about utility execution. After you activate utility history collection by setting the UTILITY_HISTORY subsystem parameter to UTILITY, information about utilities is added to the SYSIBM.SYSUTILITIES catalog table. One row is inserted into the SYSUTILITIES table at the start of each utility execution. Then, information in the row is updated as the utility progresses, and final information is updated in the row when the utility execution finishes.

For more information, see Monitoring utility history, UTILITY HISTORY (UTILITY_HISTORY subsystem parameter), and SYSUTILITIES catalog table.

Real-time statistics support for index splits

Catalog level V13R1M501 introduces three new real-time statistics (RTS) table columns in the Db2 catalog to provide detailed information about index splits.

The following RTS table fields are added in the SYSIBM.SYSINDEXSPACESTATS catalog table to record and aggregate general index split information since the 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.

V13R1M501 application compatibility

Most new SQL capabilities become available only to applications that use the equivalent application compatibility (APPLCOMPAT) level or higher. For a list, see SQL changes in Db2 13.

For more information about application compatibility levels, see Controlling the Db2 application compatibility level.

How to activate function level 501

In new Db2 13 installations, function level 501 is already activated. Use this procedure if you are migrating from Db2 12.

To activate function level 501, complete the following steps:

1. Generate tailored JCL jobs for the CATMAINT and function level activation steps. You can use the DSNTIJBC batch job or the Db2 installation CLIST.

   Tip: You can avoid working through the Db2 installation CLIST panels in interactive mode by running a batch job with valid input files to generate the required JCL jobs and input files with a background process. See Generating tailored Db2 migration or function level activation jobs in the background.

   To generate the required JCL jobs and input files with a background process, complete the following steps:

   1. Customize the DSNTIDOA parameter override file by following that instructions in the file.
   2. Customize the DSNTIJBC job. For example, if prefix.SDSNSAMP(DSNTIDOA) is the customized parameter override file, you can specify the following values in the IPSTART command in DSNTIJBC.

        ISPSTART CMD(%DSNTINSB + 
          OVERPARM(prefix.SDSNSAMP(DSNTIDOA)) + 
          ) BREDIMAX(1)

   3. If you use Db2 Value Unit Edition, you must also provide the data set name of the DSNTIDVU parameter override file in the IPSTART command in the DSNTIJBC job, as shown in the following example, where prefix.SDSNSAMP(DSNTIDVU) is the customized OTC LICENSE file.

        ISPSTART CMD(%DSNTINSB + 
          OVERPARM(<prefix>.SDSNSAMP(DSNTIDOA)) + 
          OTCLPARM(<prefix>.SDSNSAMP(DSNTIDVU)) + 
          ) BREDIMAX(1)

   4. Submit the customized DSNTIJBC job.

   
   To generate the required JCL jobs and input files with the Db2 installation CLIST in interactive mode, complete the following steps:
   1. In panel DSNTIPA1, specify INSTALL TYPE ===> ACTIVATE. Then, specify the name of the output member from the previous function level activation (or migration) in the INPUT MEMBER field, and specify a new member name in the OUTPUT MEMBER field.
   2. In panel DSNTIP00, specify the current function level and TARGET FUNCTION LEVEL ===> V13R1M501. The Db2 installation CLIST uses this value when it tailors the ACTIVATE command in the DSNTIJAF job and the CATMAINT utility control statement in the DSNTIJTC job.
   3. Proceed through the remaining Db2 installation CLIST panels, and wait for the Db2 installation CLIST to tailor the jobs for the activation process. The output data set contains the tailored jobs for the activation process. For more information, see The Db2 installation CLIST panel session.
2. If the current function level is V13R1M100, activate function level 500 by running the generated DSNTIJA0 job, or by issuing the following ACTIVATE command:

   -ACTIVATE FUNCTION LEVEL (V13R1M500)

3. Ensure that no incompatible applications can interfere with the catalog update. For more information, see Identifying applications that are incompatible with catalog updates.
4. Update the catalog and verify the changes for function level 501 by completing the following steps:
   1. Run the tailored DSNTIJTC job, or run the CATMAINT utility with LEVEL V13R1M501, to update the catalog to the appropriate catalog level. If multiple catalog updates are required, the CATMAINT job processes each update in sequential order. If a later update in the sequence fails, the previous successful updates do not roll back, and the catalog level remains at the highest level reached. If that occurs, you can correct the reason for the failure and resubmit the same CATMAINT job.
   2. Run the generated DSNTIJX2 job to run the CHECK INDEX utility for Db2 catalog and directory indexes for new objects that are created in Db2 13.
5. Check that Db2 is ready for function level activation by issuing the following ACTIVATE command with the TEST option:

   -ACTIVATE FUNCTION LEVEL (V13R1M501) TEST

   Db2 issues message DSNU757I to indicate the results. For more information, see Testing Db2 function level activation.
6. Run the tailored DSNTIJAF job, or issue the following ACTIVATE command:

   -ACTIVATE FUNCTION LEVEL (V13R1M501)

7. If you are ready for applications to use new capabilities in this function level, rebind them at the corresponding application compatibility level. For more information, see Controlling the Db2 application compatibility level.
   Optionally, when you are ready for all applications to use the new capabilities of the target function level, you can run the following jobs:

   1. Run DSNTIJUZ to modify the subsystem parameter module with the APPLCOMPAT value that was specified on panel DSNTIP00.
   2. Run DSNTIJOZ job to issue SET SYSPARM command to bring the APPLCOMPAT subsystem parameter changes online.
   3. Run DSNTIJUA job to modify the Db2 data-only application defaults module with the SQLLEVEL value that was specified on panel DSNTIP00.