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

Activating function level 500 (V13R1M500) prevents coexistence with and fallback to Db2 12. Function level 500 is also the first opportunity for applications to use many of the new capabilities in Db2 13. However, new capabilities that depend on Db2 13 catalog changes remain unavailable.

Contents

New capabilities in function level 500

New-function APARS in function level 500 or higher

How to activate function level 500

Enabling APAR: None
Full identifier: V13R1M500
Catalog level required: V13R1M100
Product identifier (PRDID): DSN13011
Incompatible changes: None

New capabilities in function level 500

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

Increased flexibility for package ownership
Starting at function level 500, you can specify the type of owner for a plan, package, or service, or the type of package owner for an SQL PL routine. The owner can be a role or an authorization ID. The default owner is a role in a trusted context that is defined with the role as object owner and qualifier attributes, otherwise the default owner is an authorization ID.

For more information, see the PACKAGE OWNER clause of CREATE PROCEDURE (SQL - native) and the OWNERTYPE option of the OWNER bind option.

Page sampling for inline statistics
Beginning in function level 500, the REORG TABLESPACE and LOAD utilities can now use page sampling when they gather inline statistics. Page sampling has the potential to reduce both CPU time and elapsed time. In earlier Db2 releases, the RUNSTATS utility can use page sampling, but inline statistics that are gathered by other utilities can use row sampling only. To use page sampling for inline statistics with REORG TABLESPACE or LOAD, specify the TABLESAMPLE SYSTEM option or ensure that the STATPGSAMP subsystem parameter is set to SYSTEM (the default) or YES. In function level 500, STATPGSAMP is extended to apply to inline statistics. For more information, see the TABLESAMPLE SYSTEM option description in Syntax and options of the LOAD control statement and Syntax and options of the REORG TABLESPACE control statement.
SQL Data Insights
Function level 500 delivers SQL Data Insights (SQL DI), an integrated solution that brings deep learning AI capabilities into Db2. SQL DI uses unsupervised neural networks to generate a specialized vector-embedding model called database embedding, which can be referenced through SQL queries called "cognitive intelligence" queries.

The SQL DI user interface is an optional feature available at no additional charge with Db2 13, which provides the user interface for training models and exploring data insights. Db2 provides the in-database infrastructure for training and model table (vector table) management. Db2 also provides three new built-in cognitive functions to speed up query execution.

For more information, see Running AI queries with SQL Data Insights.

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.

Online conversion of tables from growth-based (PBG) to range-based (PBR) partitions

Function level 500 introduces the capability to convert the partitioning scheme of a table with growth-based partitions (in a PBG table space) to use range-based partitions (in a PBR table space). The conversion can be completed as an online change with minimal impact to your applications.

PBG and PBR universal table spaces (UTS) are the strategic table space types for tables in Db2 for z/OS®. PBG table spaces are the default UTS type, and they are well-suited for small to medium-sized tables. However, if an existing table in a PBG table space grows too large, performance degradation or data and index management issues might arise. Consider converting from PBG to PBR when that occurs.

To complete the conversion, you issue an ALTER TABLE statement with the new ALTER PARTITIONING TO PARTITION BY RANGE clause and run the REORG TABLESPACE utility to materialize the pending change. The table space for the table is converted to PBR with relative page numbering (RPN).

For more information, see Converting tables from growth-based to range-based partitions and ALTER PARTITIONING TO PARTITION BY RANGE in ALTER TABLE.

Fast index traversal (FTB) support for larger index keys
Function level 500 extends FTB support to unique indexes with a key size for the ordering columns up to 128 bytes and nonunique indexes with a key size up to 120 bytes. For more information, see Fast index traversal.
Increased control for applications over how long to wait for a lock
Function level 500 introduces the CURRENT LOCK TIMEOUT special register and the SET CURRENT LOCK TIMEOUT SQL statement to allow the lock timeout value to be set at the application level. So, you can set a lock timeout interval that suits the needs of a specific application, or even an individual SQL statement. Doing so minimizes application lock contention and simplifies portability of applications to Db2, without the need to assign the application to a separate Db2 subsystem.

The value of the CURRENT LOCK TIMEOUT special register overrides the value of the IRLMRWT subsystem parameter. It applies to certain processes related to locking, like the claim or drain of an object and cached dynamic statement quiescing.

For more information, see CURRENT LOCK TIMEOUT special register and SET CURRENT LOCK TIMEOUT.

You can limit use of CURRENT LOCK TIMEOUT by setting the new SPREG_LOCK_TIMEOUT_MAX subsystem parameter. For more information, see LOCK TIMEOUT MAX (SPREG_LOCK_TIMEOUT_MAX subsystem parameter).

You can also use Db2 profile tables to specify an assignment for the CURRENT LOCK TIMEOUT special register, for both remote and local threads. See Setting special registers 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 500, Db2 profile tables can now be used for both local and remote applications in the following situations:

Ability to delete an active log data set from the BSDS while Db2 is running
Function level 500 introduces the new REMOVELOG option for the -SET LOG command to support online removal of an active log data set from the BSDS, eliminating the need to stop Db2 to accomplish the task by using the offline utility DSNJU003. The -SET LOG REMOVELOG command deletes the specified log from the BSDS if it is not in use or mark the log REMOVAL PENDING if it is in use.

To provide monitoring of the current active log status for log data sets with REMOVAL PENDING status, function level 500 also introduces the DETAIL option for the -DISPLAY LOG command. It shows information regarding REMOVAL PENDING status for local active log data sets. The output from the utility DSNJU004 also shows the REMOVAL PENDING status where applicable.

For more information, see Deleting an active log data set from the BSDS with the -SET LOG command.

SPT01 and SYSLGRNX table spaces are converted to DSSIZE 256 GB
Starting in function level 500, the first time that the REORG TABLESPACE utility runs for the following directory objects, it converts the DSSIZE to 256 GB.
  • DSNDB01.SPT01 to resolve issues that are related to the removal of the SPT01_INLINE_LENGTH subsystem parameter by APAR PH24358 in Db2 12.
  • DSNDB01.SYSLGRNX in anticipation of future growth in this table for increasing workloads and conversions of non-UTS table space to UTS.

The conversion is automatic and does not require any special utility syntax. It updates the following Db2 catalog table values for each table space:

  • The DSSIZE columns in SYSIBM.SYSTABLESPACE and SYSIBM.SYSTABLEPART are updated to 256G.
  • A SYSCOPY record is inserted for the table space, with the following values to indicate that REORG changed the DSSIZE: ICTYPE = 'A', STYPE = 'D', TTYPE = '64G'.

If function level 100* is activated, already converted table spaces continue to use the larger DSSIZE, but the REORG utility does not convert unconverted table spaces.

Recovery to a point-in-time (PIT) before REORG converted the DSSIZE reverts the DSSIZE to 64GB. As always, if any one of the catalog or directory objects are recovered to a prior PIT, it is best to recover all catalog and directory objects to the same PIT.

Improved concurrency for altering tables for DATA CAPTURE

Function level 500 introduces a concurrency improvement for ALTER TABLE statements that change the DATA CAPTURE attribute of tables. With this enhancement, Db2 no longer waits for other statements that depend on the altered table to commit. As a result, the DATA CAPTURE alteration can now succeed even when concurrent statements are running continually against the table.

Earlier Db2 releases quiesce the following objects that depend on the altered table as part of the DATA CAPTURE alteration:

  • Static packages
  • Cached dynamic SQL statements

Because the DATA CAPTURE alteration waited for applications that depended on the altered table to commit, continuous concurrent activity on the table might cause the ALTER TABLE statements to fail.

The new DATA CAPTURE attribute now takes effect immediately when the processing completes, even before the ALTER statement commits. As a result, concurrent statements on the same Db2 member might write out different log formats in the same transaction. For more information, see Altering a table to capture changed data.

Change REORG INDEX SHRLEVEL REFERENCE or CHANGE so the NOSYSUT1 keyword is the default
Starting at function level 500, the NOSYSUT1 keyword is the default for the REORG INDEX utility when specified with the SHRLEVEL REFERENCE or CHANGE keywords. So, the utility avoids use of a work data set, which improves performance and allows REORG INDEX to use parallel subtasks to unload and to build index keys. The default value of the REORG_INDEX_NOSYSUT1 subsystem parameter is also changed from NO to YES, and YES is now the only option. So, this subsystem parameter no longer influences the behavior of REORG INDEX.

For more information, see Syntax and options of the REORG INDEX control statement and REORG TS NOPAD DEFAULT (REORG_TS_NOPAD_DEFAULT subsystem parameter).

Long names support for timeout and deadlock messages in IRLM

Starting at function level 500, Db2 13 introduces IRLM support for long names for client information such as workstation ID, user ID, and transaction ID, in deadlock and timeout messages DSNT175I and DSNT376I.

In Db2 12 and earlier, the long name values are truncated in the message output.

With this change, Db2 13 also starts populating existing long name fields in IFICID 172 and IFCID 196 records. These fields remain unpopulated in Db2 12 and earlier.

CREATE TABLESPACE uses MAXPARITIONS 254 by default

At application compatibility level V13R1M500 or higher, CREATE TABLESPACE statements use MAXPARTITIONS 254 by default.

When MAXPARTITIONS 256 is explicitly specified, the default DSSIZE varies from 4 G to 32 G depending on the page size. However, starting with application compatibility level V12R1M504, when MAXPARTITIONS is not explicitly specified, Db2 12 use MAXPARTITIONS 256 by default, but the default DSSIZE is always 4 G regardless of the page size.

This apparent inconsistency avoided a risk of failure for existing statements, where the default data set size might be greater than 4 G depending on the page size. The statements might fail with SQLCODE -904 with reason code 00D70008 if the data sets for the table space are not associated with a DFSMS data class that is specified with extended format and extended addressability.

With MAXPARTITIONS 254 as the default, the result is now consistent regardless of whether MAXPARTITONS is explicitly specified. The calculated default DSSIZE is always 4 G.

See the MAXPARITIONS and DSSIZE descriptions in CREATE TABLESPACE.

New-function APARS in function level 500 or higher

The following capabilities that are introduced by new-function APARs after the general availability of Db2 13 take effect when you activate function level 500 with the PTF applied, or immediately if you apply the PTF at any higher function level.

Improved DBAT status for MONITOR THREADS profiles in DISPLAY THREAD output
FL 500 Starting in Db2 13 at function level 500 or higher, improves the status values in DISPLAY THREAD output for DBATs that are queued because the MAXDBAT subsystem parameter or the except threshold for a MONITOR THREADS profile was reached. This APAR introduces the new status value RS in DSNV402I to indicate a thread that is suspended because a MONITOR THREADS profile was reached. The existing RQ value is also updated to indicate that it only applies that threads that are suspended because the MAXDBAT value was reached.

This APAR also adds a new counter in the output for the DISPLAY DDF command with the DETAIL option. The new PQDBAT counter in message DSNL093I indicates the current number of DBATs queued because a system profile exception threshold was reached.

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:

Reduced LOGREC entries for parallel queries with FETCH FIRST n ROWS

Starting in Db2 13 function level 500 or higher, APAR PH48183 (September 2022) reduces the number of EREP LOGREC entries with reason code 00E50013 for parallel queries that specify FETCH FIRST n ROWS.

If a query that specifies FETCH FIRST n ROWS runs in parallel, the parallel child tasks continue fetching more records as the parent retrieves and returns them to the application. As soon as n rows are returned, the parent sends a "stop" message to the child tasks. When each child receives the stop message, it stops processing as if canceled. Before this APAR, each child task also writes out an EREP LOGREC entry with reason code 00E50013 in this situation.

With APAR PH48183 applied, the parallel child tasks can stop processing sooner and avoid issuing the EREP LOGREC entries with 00E50013.

The new logic applies for applications that run at application compatibility V13R1M500 or higher.

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:

V13R1M500 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 500

Before you begin

Before you activate function level 500 or higher, complete the following prerequisite 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 .
  4. Verify that you no longer need to fall back to Db2 12.
    Important: After function level 500 is activated in Db2 13, coexistence and fallback to Db2 12 are no longer possible. You can activate function level 100* to disable new capabilities in Db2 13, but function level 100* does not support coexistence or fallback .
  5. In data sharing, ensure that the group has no active Db2 12 members. See Migrating subsequent members of a group to Db2 13.
Procedure

To activate function level 500, complete the following steps:

  1. Generate tailored JCL jobs for the CATMAINT and function level activation steps. Start of changeYou can use the DSNTIJBC batch job or the Db2 installation CLIST.End of change
    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.
    Start of changeTo generate the required JCL jobs and input files with a background process, complete the following steps:End of changeStart of change
    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.
    End of change
    Start of changeTo generate the required JCL jobs and input files with the Db2 installation CLIST in interactive mode, complete the following steps:End of change
    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 TARGET FUNCTION LEVEL ===> V13R1M500. 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. (Function level 500 uses catalog level 100, and the tailored DSNTIJTC job is not used.)
    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. Check that Db2 is ready for function level activation by issuing the following ACTIVATE command with the TEST option:
    -ACTIVATE FUNCTION LEVEL (V13R1M500) TEST
    Db2 issues message DSNU757I to indicate the results. For more information, see Testing Db2 function level activation.
  3. Run the tailored DSNTIJAF job, or issue the following ACTIVATE command:
    -ACTIVATE FUNCTION LEVEL (V13R1M500)
  4. 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.
What to do next
To activate new capabilities with catalog dependencies in Db2 13, activate function level 501 or higher. See Function level 501 (Db2 13 installation or migration - May 2022).