Function level 506 (PH16829 - October 2019)

Function level 506 (V12R1M506) introduces support for new alternative names for existing built-in functions and support for implicitly dropping explicitly created table spaces.

Finding function level 506 changes: To find the new and changed content for this function level, try searching for "FL 506" from any page. You'll see a list of new and changed topics for function level 506.

Throughout the Db2 12 information, when you see the link FL 506, the adjacent content was changed for function level 506, and you can click the link to see the page that you are currently reading.

Alternative function names support

To improve compatibility across the Db2 product family, the new alternative spellings shown in the following table are now supported as syntax alternatives for existing built-in functions that are already supported in Db2 for z/OS.

Table 1. Newly supported names for existing built-in functions
Newly supported alternative name	Existing equivalent function
CHAR_LENGTH	CHARACTER_LENGTH, which returns the length of its argument in the number of string units that are specified
COVAR_POP	COVARIANCE or COVAR, which return the population covariance of a set of number pairs
HASH	HASH_MD5, HASH_SHA1, or HASH_SHA256, which return the result of applying a hash algorithm to an input argument, depending on the value specified for the second argument for the HASH function: 0 (default) HASH_MD5 1 HASH_SHA1 2 HASH_SHA256 The HASH function returns a varying length (VARBINARY) value, unlike the existing functions, which return fixed length (BINARY) values.
POW	POWER, which returns the value of one argument raised to the power of a second argument
RANDOM	RANDOM, which returns a double precision floating-point random number
STRLEFT scalar function	LEFT, which returns a string that consists of the specified number of leftmost bytes or the specified string units
STRPOS scalar function	POSSTR, which returns the position of the first occurrence of an argument within another argument
STRRIGHT scalar function	RIGHT, which returns a string that consists of the specified number of rightmost bytes or specified string units
TO_CLOB	CLOB, which returns a CLOB representation of the first argument
TO_TIMESTAMP scalar function	TIMESTAMP_FORMAT, which returns a timestamp for a character string expression, using a specified format to interpret the string

APAR PH14712 delivered the functional code to support the new alternative names for existing built-in functions.

Support for implicitly dropping explicitly created UTS and LOB table spaces

Function level 506 introduces the following enhancements to dropping tables:

If you drop a base table that resides in an explicitly created universal table space (UTS), Db2 implicitly drops the table space along with the table instead of returning an error in SQLCODE -669.
If you drop a system-period temporal table or an archive-enabled table, Db2 also drops the associated history table or archive table. If the history table or archive table resides in an explicitly created UTS, Db2 implicitly drops the table space along with the table instead of returning an error in SQLCODE -669.
If you drop an auxiliary table that resides in an explicitly created LOB table space, Db2 implicitly drops the table space along with the table. Previously, the LOB table space remained as an empty table space. An auxiliary table is dropped when you take one of the following actions:
- Issue the DROP TABLE statement on the auxiliary table
- Drop the associated base table
- Drop an associated LOB column
- Remove an associated trailing empty partition when running the REORG utility on a partition-by-growth table space

For more information, see DROP statement.

The new REORG utility behavior takes effect immediately after the activation of function level 506 or higher. For the new SQL statement behavior to take effect, the application compatibility level must be set to V12R1M506 or higher.

APAR PH14452 introduced the functional code to support implicitly dropping explicitly created table spaces.

Activation details for function level 506

Function level 506 has the following activation details:

Enabling APAR:	PH16829.
Minimum Db2 catalog level:	V12R1M505
Catalog changes:	None.
Application compatibility control:	Applications must run at application compatibility level V12R1M506 or higher to use the following new capabilities: Newly supported names for existing built-in functions. DROP statement enhancements for implicitly dropping explicitly created table spaces.

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 12 environment and continuous delivery in general, see Adopting new capabilities in Db2 12 continuous delivery.

To activate Db2 12 function level 506, complete the following steps:

If Db2 12 is still at function level 100, activate function level 500 or 501 first. For more information, see Activating Db2 12 new function at migration.
Verify that any incompatible changes are resolved, including for any lower function levels not yet activated. See Incompatible changes summary for function levels 501 and higher.
Check that Db2 is at a sufficient code level by issuing a DISPLAY GROUP command, as described in Determining the Db2 code level, catalog level, and function level. The DSN7100I message indicates the Db2 code level for under DB2 LVL in the member details. If DB2 LVL for any member is lower than 121506, apply the PTF for APAR PH16829 and any other required maintenance, so that all members indicate 121506.
Tip: You can apply any PTF at any function level. It is best to run Db2 at this code level or higher for some time before you proceed with activating the function level. Db2 cannot run at any lower code level after you activate a function level, so you cannot remove any of the required PTFs after you activate a function level.
Tailor the CATMAINT and function level activation jobs by running the installation CLIST:
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 ===> V12R1M506. 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.
Run the DSNTIJIC job to take an image copy of the Db2 catalog and directory.
Run the tailored DSNTIJTC job, or run the CATMAINT utility with LEVEL V12R1M506, to update the 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.
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.
Check that Db2 is ready for function level activation by issuing the following ACTIVATE command with the TEST option:
```
-ACTIVATE FUNCTION LEVEL (V12R1M506) TEST
```
Db2 issues message DSNU757I to indicate the results. For more information, see Testing Db2 function level activation.
Run the tailored DSNTIJAF job, or issue the following ACTIVATE command:
```
-ACTIVATE FUNCTION LEVEL (V12R1M506)
```
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.

Function level 506 incompatible changes

Function level 506 introduces the following incompatible changes:

Changes in explicitly created table space behavior

When the application compatibility level is set to V12R1M506 or higher, the following changes become effective:

Dropping a table that resides in an explicitly created universal table space no longer returns an error. Instead, the table space is implicitly dropped.
Dropping an auxiliary table that resides in an explicitly created LOB table space no longer leaves the LOB table space in the database. Instead, the table space is implicitly dropped.

Actions required:

Any existing applications that use the DROP TABLESPACE statement to drop a universal table space with a table can now be changed to use the DROP TABLE statement instead.
If an auxiliary table is dropped, any applications or tools that expect the LOB table space to remain for reuse must be modified accordingly.