Function level 503 (APAR PH51469 - February 2023)

Contents

New default values in existing rows for added ROW CHANGE TIMESTAMP columns

Accelerator-only support for more than 32K elements in an IN list

SELECT INTO statement support for OPTIMIZE FOR n ROWS

How to activate function level 503

New capabilities in function level 503

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

New default values in existing rows for added ROW CHANGE TIMESTAMP columns

Starting at application compatibility level V13R1M503 or higher, Db2 uses a constant default value when a new ROW CHANGE TIMESTAMP column is added. When processing ALTER TABLE statements that specify ADD COLUMN for ROW CHANGE TIMESTAMP columns, Db2 now sets the corresponding value in the DEFAULTVALUE column value in the SYSIBM.SYSCOLUMNS catalog table to the timestamp of the ALTER TABLE statement. (Note that CREATE TABLE processing to define a ROW CHANGE TIMESTAMP column does not set the DEFAULTVALUE column value.)

Before this change, Db2 derives the default values for existing rows from the page header from the row, which is the RBA for standalone Db2 subsystems. In data sharing, the default is derived is based on an internal mapping table between the LRSN and a timestamp. As a result, inserts, deletes, or updates to any rows in a page can change the derived default row change timestamp column values for unchanged rows, leading to unpredictable results.

For more information, see the following related topics:

• ROW CHANGE expression
• SYSCOLUMNS catalog table
• ALTER TABLE

Starting at function level 503 or higher, redirected recovery processing is also updated to allow different ROW CHANGE TIMESTAMP column default values between source and target tables. For more information, see Running a redirected recovery.

APAR PH51185 delivered the functional code for improve default behavior for added ROW CHANGE TIMESTAMP columns.

Accelerator-only support for more than 32K elements in an IN list

Starting in function level 503, a query that has an IN list of more than 32,767 (32K) elements in an IN predicate can run as an accelerator-only query on IBM Db2 Analytics Accelerator V7 for z/OS, if all of the following conditions are met:

• Query acceleration is enabled and requested for the query. For more information, see Enabling acceleration of SQL queries.
• The application runs at Db2 application compatibility level V13R1M503 or higher.
• Option 12 is specified in the list of values for the QUERY_ACCEL_OPTIONS subsystem parameter.
• The target accelerator is IBM Db2 Analytics Accelerator V7 for z/OS.
• The IN list specifies only SQL constants.
• The query can be functionally supported by the target accelerator and IBM Db2 Warehouse.

When such queries run on a V7 accelerator, the enforcement of any limits on the number of elements in the IN list predicate is handled by IBM Db2 Warehouse, instead of Db2 for z/OS. IBM Db2 Warehouse has no documented limit for elements in an IN list. However, practical limitations such as the memory and processing resources available for the query in IBM Db2 Warehouse are still likely to impose some limit on the number of elements that can be specified in the IN list predicate.

A view can also be created in Db2 for z/OS for a query with an IN list that contains more than 32K elements, but only under the same conditions listed previously for queries that contain such IN predicates. Any query that uses such a view is also evaluated and limited to the same conditions and restrictions described above. Such views also have the value 'R' in IBMREQD column in the SYSIBM.SYSVIEWS catalog table, to indicate the Db2 13 release dependency.

For more information, see "Accelerator-only support for more than 32K elements in an IN list" in IN predicate.

APAR PH50756 delivered the functional code for the accelerator support for large IN list predicates.

SELECT INTO statement support for OPTIMIZE FOR n ROWS

Starting at application compatibility level V13R1M503 or higher, you can specify an optimize-clause in SELECT INTO statements to enable Db2 to consider access paths that use a sort. SELECT INTO statements always return a single row. However, you can use OPTIMIZE FOR 2 ROWS can be specified to influence the Db2 optimizer.

When FETCH FIRST 1 ROW ONLY is specified, Db2 also applies a sort avoidance preference that is associated with OPTIMIZE FOR 1 ROWS during access path selection. However, sometimes the avoiding of any sort can result in a more expensive (in total cost) access path being used. If this situation occurs, OPTIMIZE FOR 2 ROWS can be specified in the SELECT INTO statement to enable Db2 to consider use of a more efficient access path that uses a sort. Other integer values can be specified for n in OPTIMZE FOR n ROWS, but OPTIMZE FOR 2 ROWS is recommended for this scenario.

For more information, see the following related topics:

• SELECT INTO
• optimize-clause
• Interaction between FETCH and OPTIMIZE FOR clauses

APAR PH50010 delivered the functional code for optimize-clause support for SELECT INTO statements.

V13R1M503 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 503

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.

To activate function level 503, 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. 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 ===> V13R1M503. 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 503 by completing the following steps:
   1. Run the tailored DSNTIJTC job, or run the CATMAINT utility with LEVEL V13R1M503, 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 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 (V13R1M503) 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 (V13R1M503)

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.