Function level 504 (activation enabled by APAR PH07672 - April 2019)

Function level 504 (V12R1M504) introduces new support for data with IBM® Z hardware-based Huffman compression of Db2 data, the ability to prevent the creation of certain new deprecated objects types, new support for certain built-in functions by pass-through to IBM Db2 Analytics Accelerator, and new SQL syntax alternatives for certain special registers and NULL predicates.

Contents

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

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

IBM z14 Huffman compression

Function level 504 introduces support for compression of Db2 data with IBM Z hardware-based entropy encoding (Huffman) compression with the IBM z14® Compression Coprocessor (CMPSC).

The TS_COMPRESSION_TYPE subsystem parameter controls the compression method for Db2 subsystems on IBM z14 hardware with Huffman compression enabled. Huffman compression is only available for data in universal table spaces, regardless of the setting of the TS_COMPRESSION_TYPE subsystem parameter.

For more information, see TS COMPRESSION TYPE field (TS_COMPRESSION_TYPE subsystem parameter) and Using Huffman compression to compress your data.

APAR PH04424 delivered the functional code that supports IBM z14 Huffman compression.

Prevent creation of new deprecated objects

Certain Db2 capabilities are deprecated. That is, although they remain supported, they have been replaced by other capabilities, their continued use is not recommended, and support for them is likely to be removed in the future. For a full list of such capabilities in Db2 12, see Deprecated function in Db2 12.

Function level 504 introduces capability to prevent the creation of certain deprecated objects in your Db2 for z/OS® environments. Starting in function level 504, SQL statement in packages that are bound with APPLCOMPAT(V12R1M504) or higher, or with comparable SQL options in effect, are prevented from creating following types of deprecated objects:

Synonyms

The CREATE SYNONYM statement results in an error. Use aliases instead of synonyms. For more information about aliases, see Aliases and CREATE ALIAS.

Segmented (non-UTS) or partitioned (non-UTS) table spaces
The result of a CREATE TABLESPACE statement that specifies the NUMPARTS clause but no MAXPARTITIONS clause is a partition-by-range table space table space. Otherwise, the result is a partition-by-growth table space.

If no SEGSIZE clause is specified, Db2 uses the DPSEGSZ subsystem parameter value. For more information, see DEFAULT PARTITION SEGSIZE field (DPSEGSZ subsystem parameter).

Table 1. CREATE TABLESPACE clauses for specifying table space types, by application compatibility level.
Table space type APPLCOMPAT(V12R1M504) and higher APPLCOMPAT(V12R1M503) and lower
Partition-by-growth Any of the following combinations:
  • MAXPARTITIONS and NUMPARTS
  • MAXPARTITIONS
  • Omit both
Any of the following combinations:
  • MAXPARTITIONS and NUMPARTS
  • MAXPARTITIONS and SEGSIZE n1
  • MAXPARTITIONS
Partition-by-range NUMPARTS only NUMPARTS and SEGSIZE n1
Segmented (non-UTS) Not supported2 One of the following combinations:
  • SEGSIZE n1
  • Omit MAXPARTITIONS, NUMPARTS, and SEGSIZE
Partitioned (non-UTS) Not supported2 NUMPARTS and SEGSIZE 0
Notes:
  1. Where n is a non-zero value. The DPSEGSZ subsystem parameter determines the default value. For more information, see DEFAULT PARTITION SEGSIZE field (DPSEGSZ subsystem parameter).
  2. FL 504 Non-UTS table spaces for base tables are deprecated. In general, for packages bound with APPLCOMPAT(V12R1M504) or higher, the result of a CREATE TABLESPACE statement is always a partition-by-growth or partition-by-range UTS table space, and a CREATE TABLE statement that specifies a non-UTS table space, including existing multi-table segmented table spaces, returns an error. The only exception is a dynamic CREATE TABLESPACE statement that is executed after the CURRENT APPLICATION COMPATIBILITY special register is set to a value lower than V12R1M504. In this case, CREATE TABLESPACE creates a non-UTS table space, and a CREATE TABLE statement can specify a non-UTS table space. Existing tables in non-UTS table spaces remain supported. However, support is likely to be removed in the future.

Also, a CREATE TABLE statement to create a new table in a segmented (non-UTS) table space results in an error.

For more information, see Table space types and characteristics in Db2 for z/OS and CREATE TABLESPACE.

Hash-organized tables
With the release of function level 504, hash organized tables are now considered deprecated. CREATE TABLE or ALTER TABLE statement that specifies ORGANIZE BY HASH results in an error. Newer capabilities such as fast index traversal in Db2 12 offer a more effective solution than hash-organized tables. For more information, see Fast index traversal.
Tip: Application compatibility operates at the package level. So, any of these deprecated object types can still be created in function level 504 and higher if absolutely necessary (such as for recovery situations). To create the objects, you can bind packages at V12R1M503 or lower, or issue the following statement:
SET CURRENT APPLICATION COMPATIBILITY = 'V12R1M503'

For remote applications, you can avoid application changes by using the DSN_PROFILE_TABLE to set the special register value. For more information see SET CURRENT APPLICATION COMPATIBILITY and Setting special registers with profile tables.

APAR PH02873 delivered the functional code that supports preventing the creation of the deprecated objects.

Newly supported built-in functions with IBM Db2 Analytics Accelerator

Function level 504 introduces support for the following passthrough-only built-in functions, which are passed through from Db2 for z/OS to IBM Db2 Analytics Accelerator: Db2 for z/OS only verifies that the data types of the parameters are valid for the functions. The accelerator engine does all other function resolution processing and validation. For more information, see Accelerating queries with passthrough-only expressions.

APAR PH00224 delivered the functional code for the new support of these passthrough-only built-in functions.

New SQL syntax alternatives for special registers and NULL predicates

Function level 504 introduces new syntax alternatives in Db2 for z/OS to simplify the porting of database applications from other platforms. For example, support is introduced in Db2 for z/OS for the following alternative syntax for referencing existing special registers. For more information, see Special registers

Table 2. New syntax alternatives for special registers in function level 504
Existing Special Register New Syntax Alternative Number of Keywords for New Syntax Alternative
CURRENT CLIENT_ACCTNG CLIENT ACCTNG 2
CURRENT CLIENT_APPLNAME CLIENT APPLNAME 2
CURRENT CLIENT_USERID CLIENT USERID 2
CURRENT CLIENT_WRKSTNNAME CLIENT WRKSTNNAME 2
CURRENT SERVER CURRENT_SERVER 1 1
CURRENT TIME ZONE

CURRENT TIMEZONE

CURRENT_TIMEZONE 1 1
Notes:
  1. The new single-token syntax alternatives might introduce an incompatible change if existing data or applications use a column or variable with the same name as the single token.

The following table shows the new syntax alternatives for the NULL predicate that function level 504 introduces in Db2 for z/OS. For more information, see NULL predicate.

Table 3. New syntax alternatives for the NULL predicate in function level 504
Existing Predicate New Syntax Alternatives
IS NULL ISNULL
IS NOT NULL NOTNULL

APAR PH02646 delivered the functional code to support the new SQL syntax alternatives.

Activation details for function level 504

Table 4. Function level 504 activation details
Enabling APAR: PH07672.
Minimum Db2 catalog level: V12R1M503
Application compatibility control: Applications must run at application compatibility level V12R1M504 or higher to use the following new capabilities:

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

To activate Db2 12 function level 504, complete the following steps:
  1. 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.
  2. 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.
  3. 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 121504, apply the PTF for APAR PH07672 and any other required maintenance, so that all members indicate 121504.
    Tip: You can apply any PTF at any function level, and it is best to run Db2 at this code level or higher for a period of time before proceeding with activation of 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 activating a function level.
  4. 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 ===> V12R1M504. The 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 CLIST panels, and wait for the CLIST to tailor the jobs for the activation process. The output data set contains the tailored jobs for the activation process.
  5. Run the DSNTIJIC job to take an image copy of the Db2 catalog and directory.
  6. Run the DSNTIJTC job, or run the CATMAINT utility with LEVEL V12R1M504, 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.
  7. Optionally, check that Db2 is ready for function level activation by issuing the following ACTIVATE command with the TEST option:
    -ACTIVATE FUNCTION LEVEL (V12R1M504) TEST
    Message DSNU757I indicates the results. For more information, see Testing Db2 function level activation.
  8. Run the tailored DSNTIJAF job, or issue the following ACTIVATE command:
    -ACTIVATE FUNCTION LEVEL (V12R1M504)
  9. 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 Db2 application compatibility.
    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 change online.
    3. Run DSNTIJUA job to modify the Db2 data-only application defaults module with the SQLLEVEL value that was specified on panel DSNITP00.

Function level 504 incompatible changes

Function level 504 introduces no incompatible changes. However, be aware of changes that might impact your Db2 environment and applications for packages bound at APPLCOMPAT(V12R1M504) or higher, such as the following changes:

  • If your Db2 applications have unqualified references to existing user-defined functions, they might start invoking new built-in functions instead if the names and signatures match, in certain situations. For information about avoiding such situations, see Ensuring that Db2 executes the intended user-defined function.
  • If your Db2 environment contains existing columns or variables with names such as CURRENT_TIMEZONE or CURRENT_SERVER, you must modify your applications to delimit these names. For example, you can issue the following queries to identify columns and variables with these names. For columns, issue:
    SELECT * FROM SYSIBM.SYSCOLUMNS
      WHERE NAME IN('CURRENT_SERVER','CURRENT_TIMEZONE');

    For variables, issue:

    SELECT * FROM SYSIBM.SYSVARIABLES
    WHERE NAME IN('CURRENT_SERVER','CURRENT_TIMEZONE');

    Based on the result, you can then check for dependencies, for example by querying the SYSIBM.SYSPACKDEP catalog table, to identify any static applications that depend on the nondelimited forms of these names.

  • CREATE TABLESPACE statements that previously created non-UTS segmented or partitioned table spaces, now always create partition-by-growth or partition-by-range table spaces.
  • The following SQL statements now return errors:
    • CREATE SYNONYM statements
    • CREATE TABLE statements that specify existing non-UTS table spaces
    • CREATE TABLE and ALTER TABLE statements that specify ORGANIZE BY HASH