Function level 504 (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
Prevent creation of new deprecated objects
Newly supported built-in functions with IBM Db2 Analytics Accelerator
New SQL syntax alternatives for special registers and NULL predicates
Activation details 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.
If you activate function level V12R1M503* or lower, all new REORG, LOAD, or insert operations that produce compression dictionaries produce fixed-length dictionaries. However, any existing Huffman dictionaries continue to be used for compression and expansion of any associated partitions.
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 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:- 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).
- FL 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).
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.
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
- CUME_DIST
- CUME_DIST aggregate function
- FIRST_VALUE
- LAG
- LAST_VALUE
- LEAD
- NTH_VALUE
- NTILE
- PERCENT_RANK
- PERCENT_RANK aggregate function
- RATIO_TO_REPORT
- REGEXP_COUNT scalar function
- REGEXP_INSTR scalar function
- REGEXP_LIKE scalar function
- REGEXP_REPLACE scalar function
- REGEXP_SUBSTR scalar function
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
| 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 |
- 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.
| 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
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.
- 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. For more information, see 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.
- Tailor the CATMAINT and function level activation jobs by running the installation CLIST:
- 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.
- 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.
- 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 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.
For information about the changes to the catalog, see Catalog changes in Db2 12.
- 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:
Db2 issues message DSNU757I to indicate the results. For more information, see Testing Db2 function level activation.-ACTIVATE FUNCTION LEVEL (V12R1M504) TEST - Run the tailored DSNTIJAF job, or issue the following ACTIVATE command:
-ACTIVATE FUNCTION LEVEL (V12R1M504) -
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:
- V12R1Mnnn application compatibility levels
- Application compatibility (APPLCOMPAT) levels in Db2 12
- 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:- Run DSNTIJUZ to modify the subsystem parameter module with the APPLCOMPAT value that was specified on panel DSNTIP00.
- Run DSNTIJOZ job to issue SET SYSPARM command to bring the APPLCOMPAT subsystem parameter changes online.
- 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:
- New built-in function names supported by pass-through to IBM Db2 Analytics Accelerator
-
Starting in APPPLCOMPAT level V12R1M504 or higher, the following built-in functions are supported passthrough-only built-in functions through IBM Db2 Analytics Accelerator. If your Db2 applications have unqualified references to existing user-defined functions with these names, they might start invoking new built-in functions instead if the names and signatures match, in certain situations.
- CUME_DIST
- CUME_DIST aggregate function
- FIRST_VALUE
- LAG
- LAST_VALUE
- LEAD
- NTH_VALUE
- NTILE
- PERCENT_RANK
- PERCENT_RANK aggregate function
- RATIO_TO_REPORT
- REGEXP_COUNT scalar function
- REGEXP_INSTR scalar function
- REGEXP_LIKE scalar function
- REGEXP_REPLACE scalar function
- REGEXP_SUBSTR scalar function
Actions to take: Ensuring that Db2 executes the intended user-defined function.
- CURRENT_TIMEZONE and CURRENT_SERVER in column names
-
Starting in APPPLCOMPAT level V12R1M504 or higher, 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 column names.
Actions to take: You can use the following queries to identify columns and variables with the names.
Object type Example query Columns SELECT * FROM SYSIBM.SYSCOLUMNS WHERE NAME IN('CURRENT_SERVER','CURRENT_TIMEZONE');Variables 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.
- Creation of deprecated objects prevented
-
Starting with APPPLCOMPAT level V12R1M504 or higher, SQL statements that explicitly or implicitly create table spaces for base tables always create always create universal table spaces (UTS), which are either partition-by-growth (PBG) or partition-by-range (PBR) table spaces. Also, errors can be returned for certain SQL statements that previously succeeded, as side effects of base tables always being created in a UTS.
- CREATE AUX TABLE statements for LOB columns can start failing with the SQLCODE -769 error because the PART clause is required when the base table resides in a PBG table space.
- DROP TABLE statements can fail with the SQLCODE -669 error because the DROP TABLE statement is not supported for tables in PBG table spaces. The DROP TABLESPACE statement must be used instead. (APPLCOMPAT level V12R1M506 resolves this situation. For more information, see Support for implicitly dropping explicitly created UTS and LOB table spaces.)
- CREATE TABLE statements can start failing with the SQLCODE -646 error if they attempt to create more than one table in a the same table space. Each UTS contains only a single table.
Starting in APPLCOMPAT level 504 or higher, the following SQL statements return errors instead of creating deprecated objects:
- CREATE SYNONYM statements
- CREATE TABLE statements that specify existing non-UTS table spaces
- CREATE TABLE and ALTER TABLE statements that specify ORGANIZE BY HASH
Actions to take: Update your applications to use non-deprecated object types.
Tip: Although it is best to use non-deprecated types for all new database objects, you can still use a lower APPLCOMPAT level to create the deprecated table spaces if necessary, such as for recovery situations. For more information, see Creating non-UTS table spaces (deprecated).