Function level 507 (APAR PH64907 - April 2025)

Contents

Online conversion of table partitioning from PBR to PBG

Enhanced application availability for system temporal tables

LASTUSED column support in the SYSIBM.SYSPLAN catalog table

Referencing temporal and archive-enabled tables in the same SELECT statement

Specify greater than 64 GB primary and secondary space allocation quantities

How to activate function level 507

New capabilities in function level 507

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

Online conversion of table partitioning from PBR to PBG

Function level 507 introduces a capability to convert a table with range-based (PBR) partitions to growth-based (PBG) partitions with an online change, including support for "stacking" the PBR-to-PBG conversion together with a PBG-to-PBR conversion, and resolving them in a single materializing REORG operation. The stacking support enables you to change the partitioning scheme of an existing table in a PBR table space with an online change.

If the ALTER TABLE statement for the PBR-to-PBG conversion is a pending data definition change, the semantic validation and authorization checking occurs as usual when the ALTER statement is executed. However, the alteration is not applied to the current definition or data at the time of the ALTER statement. That is, the catalog and data are untouched. Db2 writes a set of records in the catalog table SYSIBM.SYSPENDINGDDL for the pending conversion operation and the PBR table space is placed in an advisory REORG-pending (AREOR) state.

A REORG TABLESPACE operation that specifies SHRLEVEL REFERENCE or CHANGE is required to materialize the pending conversion operation. As part of the REORG processing, new records are inserted into the SYSIBM.SYSCOPY catalog table.

The stacked "round-trip" (PBR-to-PBG-to-PBR) conversion capability enables online changes to the following attributes of an existing table in a PBR table space:

• Alter the partitioning key.
• Drop a partition.
• Remove legacy 40-byte truncated limit key values.

Applications must run at application compatibility level V13R1M507 or higher to issue the ALTER TABLE statement with the new ALTER PARTITIONING TO PARTITION BY GROWTH clause.

For more information, see the following related topics:

• Converting tables from range-based to growth-based partitions
• Changing partitioning schemes for tables with range-based partitions
• ALTER TABLE statement

APARs PH64949 and PH64950 delivered the functional code for online conversion from PBR to PBG.

Enhanced application availability for system temporal tables

Function level 507 improves availability for applications that process highly concurrent update activities in user-defined system-period temporal tables by introducing a new system global variable. The new built-in global variable named SYSTIME_PERIOD_ADJUST is an option that applications can use if they encounter an error situation that occurs when too many concurrent UPDATE statements are attempting to update the same data rows in a system-period temporal table.

In this situation, a timing window can occur where a row to be inserted into the associated history table would have a beginning timestamp value greater than the ending timestamp value, and Db2 returns SQL error SQLSTATE 57062, SQLCODE -20528 when this situation occurs.

Applications that encounter this error situation can instruct Db2 to adjust the timestamp values instead of returning the -20528 SQLCODE error, by setting the SYSTIME_PERIOD_ADJUST global variable. Db2 issues the +20528 SQLCODE warning when it makes the timestamp adjustments.

To set the SYSTIME_PERIOD_ADUST global variable, applications must run at application compatibility level V13R1M507 or higher.

For more information, see the following related topics:

• Updating data in system-period temporal tables in high concurrency applications
• Querying temporal tables
• SYSTIME_PERIOD_ADJUST built-in global variable
• -20528
• +20528

APAR PH65039 delivered the functional code for enhanced application concurrency for system temporal tables.

LASTUSED column support in the SYSIBM.SYSPLAN catalog table

Starting in function level 507, Db2 13 populates the previously unused LASTUSED column in the SYSIBM.SYSPLAN catalog table.

Db2 populates the LASTUSED column in the SYSIBM.SYSPLAN catalog table when a plan is created and after it is used. The LASTUSED value is set to '0001-01-01' when the plan is created, and the value is updated within 24 hours after the plan is used while real time statistics are enabled, which means that the DISABLE_EDMRTS subsystem parameter is set to NO. The following commands and operations preserve the existing LASTUSED value:

• BIND REPLACE
• REBIND
• Automatic rebinds

For more information, see the following related topics:

• SYSPLAN catalog table
• DISABLE EDM RTS field (DISABLE_EDMRTS subsystem parameter)

APAR PH64762 delivered the functional code for LASTUSED column support in the SYSIBM.SYSPLAN catalog table.

Referencing temporal and archive-enabled tables in the same SELECT statement

Function level 507 introduces a capability for the same SELECT statement to reference both a temporal table, which can be an application period temporal table or system-period temporal table, and an archive-enabled table, if the statement does not return any historical data.

That is, a restriction that is enforced at lower application compatibility levels by SQL code -20555 reason code 1 is lifted for applications that run at application compatibility level V13R1M507 or higher. This restriction is lifted for SELECT statements that reference both a temporal table (application-period or system-period) and an archive-enabled table, in the following specific situations where no historical data is returned:

• If an application-period temporal table is referenced, a FOR BUSINESS TIME period is not specified, and the CURRENT TEMPORAL BUSINESS_TIME special register is NULL.
• If a system-period temporal table is referenced, a FOR SYSTEM TIME period is not specified, and the CURRENT TEMPORAL SYSTEM_TIME special register is NULL.
• If an archive-enabled table is referenced, the SYSIBMADM.GET_ARCHIVE global variable is set to 'N'.

For more information, see the following related topics:

• -20555
• Archive-enabled tables and archive tables
• Temporal tables and data versioning
• GET_ARCHIVE built-in global variable

APAR PH65133 delivered the functional code for references to temporal and transparent archive tables in the same SELECT statement.

Specify greater than 64 GB primary and secondary space allocation quantities

Function level 507 introduces support for specifying greater than 64 GB primary and secondary allocation quantities in the CREATE TABLESPACE and ALTER TABLESPACE statements. The primary space allocation quantity (PRIQTY) is increased to support up to 1 TB, and the secondary space allocation quantity (SECQTY) is increased to support up to 200 GB. This enhancement reduces the need to allocate extents frequently by taking advantage of the DSSIZE value and increased space allocation quantities for table spaces.

For more information, see the following related topics:

• CREATE TABLESPACE statement
• CREATE LOB TABLESPACE
• ALTER TABLESPACE statement
• Secondary space allocation for Db2 table spaces and indexes

APAR PH64760 delivered the functional code for greater than 64GB primary and secondary space allocation quantities.

V13R1M507 application compatibility

How to activate function level 507

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 507, 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 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.

   
   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 ===> V13R1M507. 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 507 by completing the following steps. If the Db2 catalog is already at the catalog level required for the function level being activated, you can skip this step.

   1. Run the DSNTIJIC job to take an image copy of the Db2 catalog and directory.

      Important: After you apply the PTF for APAR PH66270 and until you stop and restart Db2, the DSNTIJIC job might return the following error messages:

      • DSNU537I
      • DSNU470I

      For more information, see PH66270.

   2. Run the tailored DSNTIJTC job, or run the CATMAINT utility with LEVEL V13R1M507, 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 changes in Db2 13.

   3. 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.
   4. 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 (V13R1M507) 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 (V13R1M507)

7. If you are ready for applications to use new SQL capabilities in this function level, rebind the applications at the equivalent application compatibility level for higher. For more information, see the following topics:

   • V13R1Mnnn application compatibility levels
   • APPLCOMPAT levels in Db2 13
   • 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.