
Function level 500 (installation or 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.


Contents
Activation details for function level 500
Changes in Function level 500
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.
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 command (Db2) | If the externalization of real-time statistics (RTS) fails, Db2 now issues message DSNT538I. | Yes |
-ALTER BUFFERPOOL command (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 command (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 subcommand (DSN) | Support is added for processing the following SQL statement types:
|
No |
-DISPLAY DYNQUERYCAPTURE command (Db2) | New command. | No |
-DISPLAY GROUP command (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 command (Db2) | New command. 1 | No. |
-DISPLAY STATS command (Db2) | New command. New options:
|
No |
-DISPLAY UTILITY command (Db2) | The output now includes message DSNU285I. | Yes |
FREE STABILIZED DYNAMIC QUERY subcommand (DSN) | New command. | No |
FREE PACKAGE subcommand (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 command (DSN) | New command. SERVICE now supports specifying location-name and version-id. |
No |
-MODIFY DDF command (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:
If the SWTICH option is specified with an invalid package, the command now fails. |
Yes |
REBIND TRIGGER PACKAGE command (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 command (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 command (Db2) | If the PROFILE_AUTOSTART subsystem parameter is set to YES, this command is now issued automatically when Db2 starts. | No |
-START ML command (Db2) |
New command. 1 New options added: *, SQLOPT, and SA_DCC. |
No |
-START RLIMIT command (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 command (Db2) |
New command. 1 New options added: *, SQLOPT, and SA_DCC. |
No |
-TERM UTILITY command (Db2) | The TERM UTILITY command can be used for the MODIFY RECOVERY utility. | No |
-START CDDS command (Db2) | New command. | No |
-STOP CDDS command (Db2) | New command. | No |
-START DYNQUERYCAPTURE command (Db2) | New command. | No |
-STOP DYNQUERYCAPTURE command (Db2) | New command. | No |
- With IBM Db2 AI for z/OS Version 1.1.0.1 or later and APAR PH05323.
SQL statement changes in Db2 12 function level 500
The following SQL changes take effect in Db2 12 for applications that run at application compatibility V12R1M500 or higher.
Any attempt to use SQL capabilities in the following table at a lower application compatibility than V12R1M500 results in an error condition, such as SQL code -4743 or others.
SQL element | Change introduced | Incompatible change? |
---|---|---|
ALTER FUNCTION (compiled SQL scalar) | New clause: CONCENTRATE STATEMENTS. | No |
ALTER INDEX | A new clauses are added:
The following clauses are changed:
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:
The following clauses are changed:
|
No |
ALTER TABLESPACE | The following new clauses are added:
The following clauses are changed:
|
No |
ALTER TRIGGER statement (advanced trigger) | New statement. | No |
ALTER TRIGGER (basic) | Equivalent to ALTER TRIGGER in prior releases. | No |
COMMENT statement | Changed clause: TRIGGER trigger-name VERSION trigger-version-id. | No |
CREATE FUNCTION (compiled SQL scalar) | The following new clauses are added:
|
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:
|
No |
CREATE TABLE | The following new clauses are added:
The following clauses are changed:
|
No |
CREATE TABLESPACE |
The following new clauses are added:
The following clauses are changed:
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:
|
No |
EXECUTE | Changed clause: USING. | No |
EXECUTE IMMEDIATE statement | 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 |
Yes |
FETCH | New clause: target-variable. | No |
fullselect | New clause: offset-clause. The following clauses are changed: |
No |
GRANT statement (table or view privileges) | New clause: UNLOAD. | No |
GRANT statement (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:
The following clauses are changed:
|
No |
OPEN | Changed clause: USING. | No |
PREPARE statement | New clause: offset-clause. Changed clause: fetch-clause. |
No |
SELECT INTO statement | New clause: offset-clause The following clauses are changed:
|
No |
SET assignment-statement statement | Changed clause: DEFAULT. | No |
subselect | New clause: offset-clause The following clauses are changed:
|
No |
TRANSFER OWNERSHIP statement | New statement. | No |
UPDATE statement | New clause: BETWEEN value-1 AND value-2 clause of the period-clause. | No |
VALUES INTO | The following clauses are changed:
|
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 (installation or 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? |
---|---|---|
All utilities | The logical record length (LRECL) and sizes of utility work data set are increased to accommodate the 7-byte RIDs for partition-by-range table spaces with relative page numbering. | Yes |
MODIFY RECOVERY | The following new options are added:
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:
|
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 levels 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 application compatibility levels.
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:
- Verify that all incompatible changes are resolved. See Make adjustments for incompatible changes in Db2 12.
- Verify that fallback to Db2 11 and coexistence in data sharing are no longer required in your Db2 12 environment.
- 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.
