Function level 506 (APAR PH62635 - October 2024)

Function level 506 introduces non-disruptive clearing of data partitions with LOAD, more concurrently active traces, improved performance for IN-list predicates, multi-row VALUES clause support for INSERT statements, a capability to add implicitly hidden ROWID columns, and it removes certain restrictions for column masks.

Contents

Enabling APAR: PH62635
Full identifier: V13R1M506
Catalog level required: V13R1M505
Product identifier (PRDID): DSN13017
Incompatible changes: None

New capabilities in function level 506

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

Non-disruptive clearing of data partitions with LOAD

Function level 506 enhances the LOAD utility to speed up the LOAD job execution and improve availability for PBR tables with NPSIs when a data partition or partitions are emptied using an empty input data set or DD DUMMY (also known as "dummy load"). The enhancement adds a new keyword, NOKEYDELETE, to postpone the deletion of key entries from the associated logical partition of the NPSI and introduces a new Db2 object state, RBDPM, to allow continued read access to all logical partitions.

For more information, see Syntax and options of the LOAD control statement.

APAR PH62543 delivered the functional code for Non-disruptive clearing of data partitions with LOAD.

Improved performance for IN-list predicates

Function level 506 introduces a new method to improve performance of a certain type of residual INLIST predicates. A single-table SQL with a single big residual INLIST predicate can expect up to 90% CPU time improvement, but the actual improvement number might vary depending on the entire query complexity.

To be eligible for the new processing method, the residual IN-list predicates must meet the following conditions:

  • The predicate would sargable if the left-hand-side (LHS) column-expression is replaced with a simple column.
  • The right-hand side (RHS) contains literals or literal expressions (non-column expressions) only.
  • The LHS and RSH do not contain incompatible data-types or encoding schemes.
  • The LHS has enough rows or the RHS has enough INLIST items to benefit from the new method.

The 'INDEXED_VALUE' value in the RIGHT_HAND_SIDE column in DSN_PREDICAT_TABLE indicates a predicate that qualifies for the new processing method.

When Db2 evaluates a residual IN predicate before function level 506, the IN predicate is transformed to a series of equal and OR predicates. Each item is compared until a matched value is found or no matched value is found after all items are compared. This approach can be much less efficient if the IN predicate contains hundreds of items.Db2 For example, consider the following statement:

SELECT * FROM T1
WHERE SUBSTR(C1,2,3) IN ('100','103','108','112','119','124',
'131','137','145','152');

Before function level 506, if T1.C1 is defined as CHAR(10), the IN predicate is residual if there is no index on SUBSTR(C1,2,3). When Db2 evaluates such an IN predicate, it might transform it to the following form:

SUBSTR(C1,2,3)='100' OR SUBSTR(C1,2,3)='103' OR SUBSTR(C1,2,3)='108' OR SUBSTR(C1,2,3)='112' OR ...

APAR PH58750 introduces the improved performance for IN-list predicates.

Increased capacity for Db2 trace: More concurrently active traces

Function level 506 raises the maximum number of concurrently active traces from 32 to 64. The increase in the number of concurrently active traces lets you control the filtering and auditing capabilities for up to 64 traces in a Db2 subsystem or data sharing member.

For more information, see the following related topics:

APAR PH59020 delivered the functional code to support more concurrently active traces.

INSERT with multi-row VALUES clause

Function level 506 introduces the capability to specify more than one row in the VALUES form of the INSERT statement. The number of values for each row in the VALUES clause must be equal to the number of names in the column list and columns that are identified in the INCLUDE clause. Comma separators are used to specify multiple column values, multiple rows, or both. When the value list is enclosed in parentheses, the first value is inserted in the first column in the list, the second value in the second column, and so on. Multiple rows enclosed in parentheses can be separated by commas. If the values list is not enclosed in parentheses, each value is inserted in a single column in a new row.

This capability increases portability of applications by adding support in Db2 for z/OS for syntax that is already supported by Db2 on other platforms. It also reduces the number of INSERT statements that application developers must write to insert multiple rows into a table, and it can potentially reduce the number of INSERT statements that Db2 needs to process.

For example, an application that runs at application compatibility level V13R1M506 or higher can issue the following statement to insert three rows in the DSN8D10.EMP table.

INSERT INTO DSN8D10.EMP (EMPNO,FIRSTNME,MIDINIT,LASTNAME,WORKDEPT,PHONENO,
                         HIREDATE,JOB,EDLEVEL,SEX,BIRTHDATE,
                         SALARY,BONUS,COMM)
   VALUES ('000206','ELIZABETH','T','GRACE','D11','2866',
            '2023-02-17','ANALYST',16,'F','1975-06-28',
           16345,500,2300),
           ('000207','JACK','Q','JOHNSON','D11','2867',
            '2023-08-10','ANALYST',16,'M','1979-07-22',
           16345,500,2300),
           ('000208','JENNIFER','K','WHITE','D11','2868',
            '2023-08-10','ANALYST',16,'F','1980-08-13',
           16345,500,2300);

For more information, see the following related topics:

APAR PH62561 delivered the functional code for multi-row INSERT with the VALUES clause.

Add implicitly hidden ROWID columns

Function level 506 introduces the capability to add an implicitly hidden ROWID column to a table. In application compatibility level V13R1M506 or higher, you can specify IMPLICLITY HIDDEN for a ROWID column, in the ADD COLUMN clause of an ALTER TABLE statement for a table in a universal table space.

With implicitly hidden ROWID columns, the result of a select statement includes the column only when the select-clause explicitly names the column. That is, the result of an SQL statement that only implies selection of the column, such as when SELECT * is used, does not return the implicitly hidden ROWID column. As a result, such ROWID columns can be added without impacting existing applications. This capability is especially useful for converting existing tables to partition-by-range (PBR) when a suitable column does not already exist for the partitioning key.

Only one implicitly hidden ROWID column can be added to each table, and Db2 Db2 issues SQL code -270 if you attempt to add a row

Below application compatibility level V13R1M506, Db2 13 issues the -270 SQL code in this situation.

For more information, see the following related topics:

APAR PH61629 delivered the functional code for adding implict hidden ROWID columns.

Lift certain restrictions for column masks

Function level 506 removes certain restrictions for column masks. Specifically, reason codes 24 and 27 are removed from SQL code -20478.

24
The column is referenced in a WHEN clause of a CASE expression. Start of changeThis restriction is removed in application compatibility level V13R1M506 and higher, and the masked values are used to evaluate the WHEN clause.End of change
27
A sort key expression for ARRAY_AGG, LISTAGG, PERCENTILE_CONT, PERCENTILE_DISC or a numeric expression for MEDIAN references a column for which a column mask is defined. Start of changeThis restriction is removed in application compatibility level V13R1M506 or higher, and the masked values are used to evaluate the expression.End of change
For more information, see the following related topics:

APARs PH62393 and PH62690 delivered the functional code to lift certain column mask restrictions.

V13R1M506 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 application compatibility levels.

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

How to activate function level 506

The following steps summarize the process for activating this function level. To learn more about how to activate and control the adoption of new capabilities available for use in your Db2 13 environment and continuous delivery in general, see Adopting new capabilities in Db2 13 continuous delivery.

Procedure

To activate function level 506, complete the following steps:

  1. If Db2 13 is still at function level 100, activate function level 500 first. For more information, see Activating Db2 13 function level 500 or higher.
  2. 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 the 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 ISPSTART 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 the current function level and TARGET FUNCTION LEVEL ===> V13R1M506. 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.
  3. Ensure that no incompatible applications will interfere with the catalog update. For details, see Identifying applications that are incompatible with catalog updates.
  4. Update the catalog and verify the changes for function level 506 by completing the following steps:
    1. Run the tailored DSNTIJTC job, or run the CATMAINT utility with LEVEL V13R1M506, 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.

      For information about the changes to the catalog, see Catalog levels in Db2 13.

    2. If the CATMAINT utility jobs from the previous step placed any altered Db2 catalog objects in REORG-pending (AREO*) advisory status, run the REORG utility for those objects.
    3. Run the generated DSNTIJX2 job to run the CHECK INDEX utility for Db2 catalog and directory indexes for new objects 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 (V13R1M506) 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 (V13R1M506)
  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.