Function level 504 (PH07672 - April 2019)

Function level 504 (V12R1M504) introduces new support for data with IBM zSystems 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 zSystems 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.

Start of changeAPAR PH04424 delivered the functional code that supports IBM z14 Huffman compression.End of change

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 statement.

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. 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. Start of changeFL 504 Non-UTS table spaces for base tables are deprecated. CREATE TABLESPACE statements that run at application compatibility level V12R1M504 or higher always create a partition-by-growth or partition-by-range table space, and CREATE TABLE statements that specify a non-UTS table space (including existing multi-table segmented table spaces) return an error. However, you can use a lower application compatibility level to create table spaces of the deprecated types if needed, such as for recovery situations. For instructions, see Creating non-UTS table spaces (deprecated).End of change

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 statement.

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.

Start of changeAPAR PH02873 delivered the functional code that supports preventing the creation of the deprecated objects.End of change

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.

Start of changeAPAR PH00224 delivered the functional code for the new support of these passthrough-only built-in functions.End of change

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

Start of changeAPAR PH02646 delivered the functional code to support the new SQL syntax alternatives.End of change

Activation details for function level 504

Function level 504 has the following 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. 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.
  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 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.
  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. 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.
  8. Check that Db2 is ready for function level activation by issuing the following ACTIVATE command with the TEST option:
    -ACTIVATE FUNCTION LEVEL (V12R1M504) TEST
    Db2 issues message DSNU757I to indicate the results. For more information, see Testing Db2 function level activation.
  9. Run the tailored DSNTIJAF job, or issue the following ACTIVATE command:
    -ACTIVATE FUNCTION LEVEL (V12R1M504)
  10. 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 504 incompatible changes

The following changes might impact your Db2 environment and applications that run at application compatibility level 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.
    Tip: Although it is best to use the non-deprecated types for new objects, you can still use a lower application compatibility level to create the deprecated table spaces or higher if necessary, such as for recovery situations. For instructions, see Creating non-UTS table spaces (deprecated).
  • Errors can be returned for SQL statements that previously succeeded, as side effects of the tables now being created in universal table spaces instead of non-UTS types. For example:
  • 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

    Action to take: Update your applications to use the non-deprecated object types.