Function level 504 (APAR PH54919 - October 2023)

Contents

Minimized impact of invalidated packages with statement-level invalidation

Increased capacity for Db2 trace: More OP buffers

Utility object-level history

Enhancements for SQL Data Insights

ORDER BY support for a fullselect that invokes LISTAGG

Verification of enhancements to Db2 13 in new-function APARs

How to activate function level 504

New capabilities in function level 504

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

Minimized impact of invalidated packages with statement-level invalidation

Starting in function level 504, you can enable packages for statement-level invalidation. This new capability uses the statement-level dependency infrastructure, including the new DEPLEVEL(STATEMENT) bind option, that was introduced in function level 502. See Record dependencies and validity at the statement level for packages.

With statement-level dependencies and statement-level invalidation, applications can execute a package that is invalidated at a statement level without waiting for the completion of the automatic rebind (autobind). The application thread can execute the valid statements immediately. Any invalid statements go through incremental bind in the application thread before execution. Concurrently, Db2 initiates a special autobind, which is called autobind phase-in. When the autobind phase-in finishes successfully, the subsequent executing threads can use the new valid copy of the package, and they no longer use incremental bind for any statements.

The autobind phase-in process runs concurrently with executing application threads, and the old invalid copy becomes a phased-out copy, which is stored in the SYSIBM.SYSPACKCOPY table. The new copy of the package becomes the current copy. Subsequent executions of the package use the new valid copy. Threads that existed prior to autobind phase-in completing can also use the new current copy when they release the phased-out copy (based on the RELEASE(COMMIT) or RELEASE(DEALLOCATE) bind options) In this respect, autobind phase-in is the same as rebind phase-in. However, unlike other phase-in rebinds, autobind phase-in does not require the PLANMGMT subsystem parameter to be set to EXTENDED. For more about rebind-phase in, see Phase-in of package rebinds.

Db2 uses automatic binds only when the ABIND subsystem parameter is set to YES or COEXIST. If ABIND is set to NO when an invalid package runs, Db2 returns an error. For details, see AUTO BIND field (ABIND subsystem parameter).

Incompatible change: If incremental binds fail due to prior invalidating changes to dependent objects, applications that previously failed with SQL code -904 might start receiving different SQL codes. For example, an incremental bind for a statement that depended on a dropped table might return -204 to the application. For more information, see Incompatible changes in Db2 13.

Note: At function level 502 or higher, a package that is bound with DEPLEVEL(STATEMENT) option can be marked with VALID='S' if it is invalidated by a DROP FUNCTION statement. However, after the PTF for APAR PH55497 is applied, Db2 always marks a package that is invalidated by a DROP FUNCTION statement with VALID='N'.

For more information, see the following related topics:

• Autobind phase-in for packages with statement-level invalidation
• Enabling autobind phase-in for packages invalidated at the statement level
• MAX_CONCURRENT_PKG_OPS in macro DSN6SPRM
• SYSPACKAGE catalog table
• 00E30305

APAR PH55497 delivered the functional code for minimizing the impact of invalidated packages.

Increased capacity for Db2 trace: More OP buffers

Starting in function level 504, the maximum number of OP buffers for trace monitoring with READA calls is increased from 8 to 16. The increase in the number of available OP buffers can have the following effects:

• More monitoring capacity is available when multiple applications start traces with multiple OP destinations.
• Monitoring applications can experience better performance when they send trace data to a larger number of destinations.

For more information, see the following related topics:

• Requesting data asynchronously from a monitor program
• READA
• -START TRACE (Db2)

APAR PH55622 delivered the functional code for increasing the capacity for Db2 trace.

Utility object-level history

Starting in function level 504, you can collect object-level information along with utility execution information for IBM® Db2 Utilities. Real time and historical information about the table spaces and index spaces for IBM Db2 Utilities executions are inserted to the SYSIBM.SYSOBJEVENTS catalog table.

System administrators and DBAs can use queries to generate, filter, order, and join the information in the SYSIBM.SYSOBJEVENTS and SYSIBM.SYSUTILITIES catalog tables to check, analyze, and compare utility executions.

For more information, see the following related topics:

• Monitoring utility history
• Utility object-level history information in SYSOBJEVENTS
• UTILITY HISTORY (UTILITY_HISTORY subsystem parameter)
• SYSUTILITIES catalog table
• SYSOBJEVENTS catalog table

The following APARs delivered the functional code for utility object-level history: PH55476, PH55914, PH55915, and PH55916.

Enhancements for SQL Data Insights

Function level 504 introduces the following enhancements for the SQL DI capability in Db2 13:

• New Db2 built-in AI_COMMONALITY function that computes a similarity score by using the value of the expression argument and the centroid value of the model column. You can use the function in your queries to detect the common patterns and the outliers in your data.
• Enhanced AI_ANALOGY function with the numeric data type support. You can specify numeric data types in the arguments for the function.
• Automatic disablement of SQL DI vector prefetching based on the AI object type and the AI cache size. When the MXAIDTCACH parameter is set to a value greater than 0 and a query invokes a SQL DI function on a table, Db2 dynamically chooses between vector prefetching and row-by-row processing to optimize the CPU usage of the function.

APAR PH55212 delivered the functional code for these SQL DI enhancements.

ORDER BY support for a fullselect that invokes LISTAGG

Function level 504 removes a restriction against specifying an ORDER BY clause in a fullselect that contains an invocation of the LISTAGG built-in function for applications that run at application compatibility level V13R1M504 or higher. For more information, see LISTAGG.

APAR PH55596 delivered the functional code for ORDER BY support for a fullselect that invokes LISTAGG.

Verification of enhancements to Db2 13 in new-function APARs

Many APARs introduce enhancements to Db2 13 that are available at any function level. That is, their changes take immediate effect as you apply the PTF to each Db2 data sharing member.

However, starting with function level 504, activating a Db2 13 function level also verifies that the PTFs for a specific set of these previously available APARs are consistently applied in the Db2 13 environment. For more information, see New-function APARs for Db2 13.

V13R1M504 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 504

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 504, 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 ===> V13R1M504. 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 504 by completing the following steps:
   1. Run the tailored DSNTIJTC job, or run the CATMAINT utility with LEVEL V13R1M504, 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 (V13R1M504) 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 (V13R1M504)

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.