Function level 500 (activated at Db2 12 installation or after migration - October 2016)

Function level 500 (V12R1M500) represents the first opportunity for applications to take advantage of most new capabilities in the Db2 12 initial release, including new SQL capabilities and many new subsystem parameter settings. Activation of function level 500 prevents any future fallback to Db2 11. Function level 500 is comparable in many respects to new-function mode in previous releases.

In Db2 12, function level 500 is activated by default in new installations. However, you can activate a higher function level during the installation process.
Important: Before you activate function level 500 or higher for the first time, read Activating Db2 12 new function at migration.
Important: Do not issue the ACTIVATE command or run job DSNTIJAF for activation of Db2 12 until you are certain that the subsystem or data sharing group can proceed on Db2 12, without the possibility of falling back to or coexistence with Db2 11. In data sharing, the ACTIVATE command has group scope. Fallback and coexistence become impossible with the successful activation of function level 500 or higher.
Important: Apply the for fallback SPE (APAR PI33871) and stop and restart Db2 11 for every subsystem or data sharing member that you plan to migrate to Db2 12. For data sharing, every member must be started in Db2 11 after the fallback SPE is applied. Inactive members that never started with the fallback SPE applied in Db2 11 cannot start in Db2 12 or Db2 11 after migration to Db2 12 and activation of function level 500 on any other member. See Required maintenance for Db2 12 installation or migration.

Contents

Changes in Function level 500

Activation details for function level 500

How to activate function level 500

Function level 500 incompatible changes

Changes in Function level 500

To read more about many of new capabilities in Db2 12, see the following topics:

The following sections summarize changes that function level 500 introduces in Db2 12.

Subsystem parameter changes in function level 500

The following changes take effect when you activate function level 500 in Db2 12.

Subsystem parameter Change introduced Incompatible change?
AUTH_COMPATIBILITY in macro DSN6SPRM New subsystem parameter.

* This new subsystem parameter is not an incompatible change. However, its purpose is to provide time to resolve specific authorization-related incompatible changes, and the default value specifies that Db2 12 uses the new authorization behavior. See "Changes to the authorization check of the UNLOAD utility" in Security release incompatibilities.

 No *
CACHE DYN STABILITY field (CACHEDYN_STABILIZATION subsystem parameter) New subsystem parameter. No
COMPRESS DB2 DIR LOBS field (COMPRESS_DIRLOB subsystem parameter) New subsystem parameter. No
DDL MATERIALIZATION field (DDL_MATERIALIZATION subsystem parameter) New subsystem parameter. No
DEFAULT INSERT ALGORITHM field (DEFAULT_INSERT_ALGORITHM subsystem parameter) New subsystem parameter. No
PAGE SET PAGE NUMBERING field (PAGESET_PAGENUM subsystem parameter) New subsystem parameter. No
REORG IC LIMIT DASD field (REORG_IC_LIMIT_DASD subsystem parameter) New subsystem parameter. No
REORG IC LIMIT TAPE field (REORG_IC_LIMIT_TAPE subsystem parameter) New subsystem parameter. No
UTILS BLOCK FOR CDC field (UTILS_BLOCK_FOR_CDC subsystem parameter) New subsystem parameter. No
REORG INDEX NOSYSUT1 (REORG_INDEX_NOSYSUT1 subsystem parameter) New subsystem parameter.  
SUBSTR COMPATIBILITY field (SUBSTR_COMPATIBILITY subsystem parameter) New subsystem parameter. No

Command changes in function level 500

The following changes to commands take effect when you activate function level 500 in Db2 12.

Command Change introduced Incompatible change?
-ACCESS DATABASE (Db2) If the externalization of real-time statistics (RTS) fails, Db2 now issues message DSNT538I. Yes
-ALTER BUFFERPOOL (Db2) The meaning of the existing PGSTEAL(NONE) option is changed. As in previous versions, Db2 pre-loads the data for assigned objects when they are opened and keeps the data resident until the objects are closed.

However, in Db2 12, Db2 creates an overflow area within the VPSIZE of the buffer pool. If all open assigned objects do not fit into the main part of the buffer pool, pages are allocated to the overflow area. Page stealing is possible in the overflow area. After pages are allocated in the overflow area, you must close the object to move the pages back to the overflow area.

 No
-ALTER GROUPBUFFERPOOL (Db2) The maximum value that can be specified for the RATIO keyword is now increased to 1024. No
BIND PACKAGE subcommand (DSN) The following new option is added: CONCENTRATESTMT. No
BIND QUERY (DSN) Support is added for processing the following SQL statement types:
  • INSERT
  • UPDATE WHERE CURRENT OF
  • DELETE WHERE CURRENT OF
 No
-DISPLAY DYNQUERYCAPTURE (Db2) New command. No
-DISPLAY GROUP (Db2) The DSN7100I message output is modified to indicate information about the code, catalog, and function levels of the Db2 subsystem or data sharing group. No
-DISPLAY ML (Db2) New command. 1 No.
-DISPLAY STATS (Db2) New command.

New options:

  • INDEXTRAVERSECOUNT is used to display the index traverse counts for a specific index, or for a specified number of indexes with the highest traverse counts, in descending order. Output is returned by message DSNT830I.
  • LOGREADERTASKS and LOGREADERTASKS SCOPE(GROUP) options are used to display statistics about any log reading tasks that are currently running. Output is returned by message DSNT788I.
No
-DISPLAY UTILITY (Db2) The output now includes message DSNU285I. Yes
FREE STABILIZED DYNAMIC QUERY (DSN) New command. No
FREE PACKAGE (DSN) If you specify INACTIVE, PREVIOUS, ORIGINAL for the PLANMGMTSCOPE option, FREE PACKAGE processing can now complete while applications that use the package are running.

New values are added for an existing option: PLANMGMTSCOPE(PREVIOUS|ORIGINAL).

A new option is added: INVALIDONLY

A new value is added to an existing option: PLANMGMTSCOPE(PHASEOUT).

 No
FREE SERVICE (DSN) New command.

SERVICE now supports specifying location-name and version-id.

 No
-MODIFY DDF (Db2)

After the PTF for APAR PH08188 is applied, PORT and SECPORT can now have the name value.

A new option is added: SESSIDLE.

 No
REBIND PACKAGE subcommand (DSN)

This command can be used to rebind a package for an advanced trigger.

The following new options are added:

  • APREUSESOURCE
  • CONCENTRATESTMT

If the SWTICH option is specified with an invalid package, the command now fails.

 Yes
REBIND TRIGGER PACKAGE (DSN) This command can be used to rebind a package for a basic trigger, including any trigger that was created prior to the activation of function level 500 or higher.

A new option is added: APREUSESOURCE.

If the SWTICH option is specified with an invalid package, the command now fails.

 Yes
-START DB2 (Db2) You cannot start Db2 at any code level that is lower than the catalog level or the highest activated function level. No
-START PROFILE (Db2) If the PROFILE_AUTOSTART subsystem parameter is set to YES, this command is now issued automatically when Db2 starts. No
-START ML (Db2)

New command. 1

New options added: *, SQLOPT, and SA_DCC.

 No
-START RLIMIT (Db2) Certain previously supported resource limit table formats are deprecated or unsupported in Db2 12. Resource limits are not activated for tables with unsupported formats. No
-STOP ML (Db2)

New command. 1

New options added: *, SQLOPT, and SA_DCC.

 No
-TERM UTILITY (Db2) The TERM UTILITY command can be used for the MODIFY RECOVERY utility. No
-START CDDS (Db2) New command. No
-STOP CDDS (Db2) New command. No
-START DYNQUERYCAPTURE (Db2) New command. No
-STOP DYNQUERYCAPTURE (Db2) New command. No
Notes:
  1. With IBM Db2 AI for z/OS® Version 1.1.0.1 or later and APAR PH05323.

SQL statement changes in function level 500

The following SQL changes take effect in Db2 12 for applications that run at application compatibility V12R1M500 or higher.

SQL element Change introduced Incompatible change?
ALTER FUNCTION (compiled SQL scalar) New clause: CONCENTRATE STATEMENTS. No
ALTER INDEX A new clauses are added: DSSIZE.

The following clauses are changed:

  • ADD COLUMN column-name (can now specify DECFLOAT columns)
  • COMPRESS

Altering to use index compression for indexes in universal table spaces is now a pending change that places the index in advisory REORG-pending (AREOR) status

 Yes
ALTER PROCEDURE (SQL native) New clause: CONCENTRATE STATEMENTS. No
ALTER TABLE The following new clauses are added:
  • CCSID
  • EXCLUSIVE and INCLUSIVE clauses of the PERIOD BUSINESS_TIME clause
  • PERIOD BUSINESS_TIME clause for constraints

The following clauses are changed:

  • ADD PERIOD FOR
  • ADD PARTITION
 No
ALTER TABLESPACE The following new clauses are added:
  • PAGENUM
  • INSERT ALGORITHM

The following clauses are changed:

  • COMPRESS
  • DSSIZE
 No
ALTER TRIGGER (advanced) New statement. No
ALTER TRIGGER (basic) Equivalent to ALTER TRIGGER in prior releases. No
COMMENT Changed clause: TRIGGER trigger-name VERSION trigger-version-id. No
CREATE FUNCTION (compiled SQL scalar) The following new clauses are added:
  • CONCENTRATE STATEMENTS
  • WRAPPED
 No
CREATE FUNCTION (inline SQL scalar) New clause: WRAPPED. No
CREATE FUNCTION (SQL table) New clause: WRAPPED. No
CREATE INDEX New clause: DSSIZE.

CREATE INDEX statements that omit the USING clause at the table space or index level now fail with SQLCODE -204, if the storage group specified when the containing database was created does not exist.

 Yes
CREATE PROCEDURE (SQL native) The following new clauses are added:
  • CONCENTRATE STATEMENTS
  • WRAPPED
 No
CREATE TABLE The following new clauses are added:
  • CCSID on a CHAR, GRAPHIC, CLOB, or DBCLOB column
  • EXCLUSIVE and INCLUSIVE clauses of the PERIOD BUSINESS_TIME clause
  • PAGENUM
  • PERIOD BUSINESS_TIME for constraints

The following clauses are changed:

  • DSSIZE
  • PERIOD FOR
 No
CREATE TABLESPACE

The following new clauses are added:

  • PAGENUM
  • INSERT ALGORITHM

The following clauses are changed:

  • COMPRESS
  • DSSIZE
  • NUMPARTS

CREATE TABLESPACE statements that omit the USING clause at the table space or index level now fail with SQLCODE -204, if the storage group specified when the containing database was created does not exist.

 Yes
CREATE TRIGGER (basic) New clause: WRAPPED. No
CREATE TRIGGER (advanced) New statement. No
CREATE VARIABLE Changed clause: data-type. No
DELETE The following new clauses are added:
  • BETWEEN value-1 AND value-2 clause of the period-clause
  • FETCH FIRST n ROWS ONLY
 No
EXECUTE Changed clause: USING. No
EXECUTE IMMEDIATE Changed clause: variable. No
EXPLAIN New clause: STABILIZED DYNAMIC QUERY STMTID.

When Db2 processes the SQL statement EXPLAIN PACKAGE or EXPLAIN STABILIZED DYNAMIC QUERY, the HINT_USED column in the PLAN_TABLE is populated with EXPLAIN PACKAGE: copy.

 Yes
FETCH New clause: target-variable. No
fullselect New clause: offset-clause.

The following clauses are changed:

 No
GRANT (table or view privileges) New clause: UNLOAD. No
GRANT (system privileges) New clause: BINDAGENT. No
MERGE The MERGE statement now includes the delete operation as part of the merge process.

The following new clauses are added:

  • signal-statement
  • ELSE IGNORE

The following clauses are changed:

  • AS correlation-name
  • assignment-clause
  • WHEN matching-condition
  • THEN modification-operation
 No
OPEN Changed clause: USING. No
PREPARE New clause: offset-clause.

Changed clause: fetch-clause.

 No
SELECT INTO New clause: offset-clause

The following clauses are changed:

 No
SET assignment-statement Changed clause: DEFAULT. No
subselect New clause: offset-clause
The following clauses are changed:
No
TRANSFER OWNERSHIP New statement. No
UPDATE New clause: BETWEEN value-1 AND value-2 clause of the period-clause. No
VALUES INTO The following clauses are changed:
  • Assignment clause source
  • INTO
 No
compound-statement for SQL routines New clause: ATOMIC.

Changed clause: DEFAULT or CONSTANT.

 No

For more information about these changes, see Function level 500 (activated at Db2 12 installation or after migration - October 2016).

Utility changes in function level 500

The following changes take effect when you activate function level 500 in Db2 12.

Utility Change introduced Incompatible change?
MODIFY RECOVERY The following new options are added:
  • DELETEDS
  • FLASHCOPY ONLY
  • NOCOPYPEND

When MODIFY RECOVERY is run at the table space level, a SYSIBM.SYSCOPY row with ICTYPE='M' and STYPE='R' is inserted for each partition of the table space and any partitioned indexes with the COPY YES attribute that the MODIFY utility processes. This differs from previous versions where only one SYSCOPY record was inserted for the entire table space, regardless of partitions.

 No
RECOVER FROM No
REORG TABLESPACE The following new options are added:
  • DROP_PART NO
  • DROP_PART YES
  • ICLIMIT_DASD
  • ICLIMIT_TAPE
 No
TEMPLATE The BLKSZLIM option is added. No
UNLOAD The authorization checking is changed, so that Db2 no longer checks for the SELECT privilege on the tables being unloaded. Yes

Activation details for function level 500

Catalog level V12R1M500
Catalog level V12R1M500 is the result when you install a new Db2 12 subsystem, or when you tailor the catalog during migration to Db2 12, as described in Migration step 19: Tailor Db2 12 catalog: DSNTIJTC.

For a summary of changes in Db2 12, see Catalog changes in Db2 12.

Application compatibility level V12R1M500
Most new SQL capabilities in function level 500 become available only to applications that use the equivalent application compatibility level (V12R1M500) or higher. For a list of such SQL capabilities, see SQL changes in Db2 12.

For more information about application compatibility levels, see Controlling the Db2 application compatibility level.

How to activate function level 500

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.

In a new installation of Db2 12, function level 500 is already activated. For migration from Db2 11, complete the following steps to activation function level 500:

  1. Verify that all incompatible changes are resolved. See Make adjustments for incompatible changes in Db2 12.
  2. Verify that fallback to Db2 11 and coexistence in data sharing are no longer required in your Db2 12 environment.
  3. Follow the steps in Activating Db2 12 new function at migration.

Function level 500 incompatible changes

For information about incompatible changes to resolve before activating function level 500, see Make adjustments for incompatible changes in Db2 12.