DB2 Version 9.7 for Linux, UNIX, and Windows

Upgrade impact from SQL statement changes

The changes to SQL statements in DB2® Version 9.7 can impact your existing applications and scripts after you upgrade to DB2 Version 9.7.

The changes to SQL statements include new default behaviors and modifications to statement output. In addition, some statements are discontinued. The following table lists the changes that impact applications and scripts:
Table 1. Changes to SQL statements
SQL statement Summary of changes with upgrade impact
ALTER FUNCTION,
ALTER HISTOGRAM TEMPLATE,
ALTER METHOD,
ALTER NICKNAME,
ALTER PROCEDURE,
ALTER SEQUENCE,
ALTER SERVER,
ALTER TABLE,
ALTER TYPE (Structured),
ALTER USER MAPPING,
ALTER VIEW,
ALTER WRAPPER, and
ALTER XSROBJECT		 Due to changes in the DB2 authorization model, the SYSADM group is no longer authorized to run these statements. The UPGRADE DATABASE command grants DBADM authority to the SYSADM group so that there is no upgrade impact. However, for these statements, you should review all of the changes in authorization and grant any required authorization to users.

If you create databases in DB2 Version 9.7, grant the required authorization to users that need to run these statements or explicitly grant DBADM authority to the SYSADM group to maintain the same authorization as in previous releases.

Soft invalidation is supported on ALTER FUNCTION and ALTER VIEW statements when the DB2_DDL_SOFT_INVAL registry variable is set to ON. Refer to Automatic invalidation and revalidation of database objects for details about soft invalidation semantics.
ALTER SERVICE CLASS,
ALTER THRESHOLD,
ALTER WORK ACTION SET,
ALTER WORK CLASS SET,
ALTER WORKLOAD,
CREATE HISTOGRAM TEMPLATE,
CREATE SERVICE CLASS,
CREATE THRESHOLD,
CREATE WORK ACTION SET,
CREATE WORK CLASS SET, and CREATE WORKLOAD		 Due to changes in the DB2 authorization model, the SYSADM group is no longer authorized to run these statements. The UPGRADE DATABASE command grants DBADM authority to the SYSADM group so that there is no upgrade impact. However, for these statements, you should review all of the changes in authorization and grant any required authorization to users.

However, if you create databases in DB2 Version 9.7, grant the required authorization to users that need to run these statements or explicitly grant DBADM or WLMADM authority to the SYSADM group to maintain the same authorization as in previous releases.
ALTER DATABASE The ADD STORAGE ON clause can now be issued with the ALTER DATABASE statement to enable automatic storage on existing databases. In previous releases, the SQL20317N error message was returned. This statement creates the SQLSGF.1 and SQLSGF.2 files that are required for maintaining automatic storage. Refer to Adopting new Version 9.7 functionality in upgraded databases for details on how to enable automatic storage.
ALTER TABLE The COMPRESS clause now enables compression for data in the table and the XML storage object. If you issue the ALTER TABLE statement with the ADD COLUMN clause to add an XML column, the compression of the XML data depends on whether data row compression is enabled for the table.

If you issue the ALTER TABLE statement with the COMPRESS YES clause in a table with XML columns created in a pre-Version 9.7 release, only table data compression is supported. To convert the XML storage object to the new Version 9.7 format that supports compression on XML data, re-create the table. See Converting XML storage objects to the Version 9.7 format for details.

The COMPRESS and DATA CAPTURE CHANGES clauses can now be used together. SQL replication now supports tables with data row compression enabled as source and target tables. You will not receive an error if you specify both clauses with this statement.

The ALTER TABLE statement with the ATTACH PARTITION clause now requires that columns defined with structured, XML, or LOB data type in the partitioned table and the table that you attaching have the same INLINE LENGTH value. If you issue this statement in a table with XML columns, maintenance is performed in all XML column path indexes as part of the statement processing.

Starting with Version 9.7 Fix Pack 1, the ALTER TABLE statement with the DETACH PARTITION clause proceeds concurrently with certain queries. See Data partition detach phases in Partitioning and Clustering Guide for details.

If you issue the ALTER TABLE statement with the DETACH PARTITION clause in a table with XML columns, XML column path indexes for the separate table are created as part of the statement processing.

XML columns are now supported in partitioned tables. The ALTER TABLE statement with the ADD COLUMN, ATTACH PARTITION, or DETACH PARTITION clause no longer returns SQL1242N error message with reason code 2 when attaching or detaching a table with XML columns to partitioned tables, or adding XML columns to partitioned tables.

You can now add columns with the XML type to MDC tables. In previous releases, the SQL1242N error message with reason code 1 was returned.

Refer to the ALTER FUNCTION statement row for additional authorization changes that also apply to this command.
CALL,
CREATE ALIAS,
CREATE EVENT MONITOR,
CREATE FUNCTION,
CREATE INDEX,
CREATE INDEX EXTENSION,
CREATE METHOD,
CREATE NICKNAME,
CREATE PROCEDURE,
CREATE SCHEMA,
CREATE SEQUENCE,
CREATE SERVER,
CREATE TABLE,
CREATE TRANSFORM,
CREATE TRIGGER,
CREATE TYPE,
CREATE TYPE MAPPING,
CREATE VARIABLE,
FLUSH EVENT MONITOR,
FLUSH OPTIMIZATION PROFILE CACHE,
FLUSH PACKAGE CACHE,
RENAME,
SET EVENT MONITOR STATE, and
SET INTEGRITY		 Due to changes in the DB2 authorization model, the SYSADM group is no longer authorized to run these statements. The UPGRADE DATABASE command grants DBADM authority to the SYSADM group so that there is no upgrade impact. However, for these statements, you should review all of the changes in authorization and grant any required authorization to users.

If you create databases in DB2 Version 9.7, grant the required authorization to users that need to run these statements or explicitly grant DBADM authority to the SYSADM group to maintain the same authorization as in previous releases.

Soft invalidation is supported on CREATE ALIAS, CREATE FUNCTION, CREATE NICKNAME, CREATE PROCEDURE, CREATE TRIGGER, and CREATE VARIABLE statements when the DB2_DDL_SOFT_INVAL registry variable is set to ON. Refer to Automatic invalidation and revalidation of database objects for details about soft invalidation semantics.

If you create databases in DB2 Version 9.7, the auto_reval configuration parameter is set to DEFERRED by default so that revalidation deferred semantics are enabled. This setting allows you to create views, functions, stored procedures, triggers, and global variables even if they reference objects that do not exist or are invalid.
CREATE VIEW,
DECLARE CURSOR,
DECLARE GLOBAL TEMPORARY TABLE,
DELETE,
INSERT,
MERGE,
REFRESH TABLE,
SELECT INTO,
SET variable, and
UPDATE		 Due to changes in the DB2 authorization model, the SYSADM group is no longer authorized to run these statements and in some cases the DBADM authority requirement is replaced by the DATAACCESS authority. The UPGRADE DATABASE command grants DBADM authority to the SYSADM group and grants DATAACCESS authority to users holding DBADM authority so that there is no upgrade impact. However, for these statements, you should review all of the changes in authorization and grant any required authorization to users.

If you create databases in DB2 Version 9.7, grant the required authorization to users that need to run these statements. You can explicitly grant DBADM authority to the SYSADM group, DATAACCESS authority to users holding DBADM authority, or both to maintain the same authorization as in previous releases.

Soft invalidation is supported on CREATE VIEW statement when the DB2_DDL_SOFT_INVAL registry variable is set to ON. Refer to Automatic invalidation and revalidation of database objects for details about soft invalidation semantics.
CALL If you specified DEFAULT as a parameter or variable name, DEFAULT now resolves to the DEFAULT keyword. In previous releases, DEFAULT resolved to a variable or parameter called DEFAULT. Delimit in double quotes the word DEFAULT in upper case letters when you want to resolve to a variable or parameter called DEFAULT as shown in this example:
CALL (P1,"DEFAULT");

Refer to the CALL statement row for additional authorization changes that also apply to this command.
COMMENT Due to changes in the DB2 authorization model, the SYSADM group is no longer authorized to run these statements. The UPGRADE DATABASE command grants DBADM authority to the SYSADM group so that there is no upgrade impact. However, for these statements, you should review all of the changes in authorization and grant any required authorization to users. Also, WLMADM authority is required to comment on workload management objects.

If you create databases in DB2 Version 9.7, grant the required authorization to users that need to run these statements or explicitly grant DBADM authority to the SYSADM group to maintain the same authorization as in previous releases.
CONNECT If you enable the read capability on HADR standby databases, you can connect to them except when the database is switching roles on takeover. In previous releases, the SQL1776N error message was returned.
CREATE INDEX When you create indexes in tables with data row compression enabled, indexes will have compression enabled by default. If data row compression is disabled, index compression is disabled. Use the COMPRESS NO clause if you must disable index compression.

Online index create is now supported when you create indexes on an XML column. This support requires additional log space and index table space. The additional requirements are the same for online index create on relational indexes.

When you create indexes for partitioned tables, by default, they are created as partitioned indexes. If you must create nonpartitioned indexes, use the NOT PARTITIONED clause. Partitioned indexes are not supported for spatial indexes and unique indexes with index key columns that are not a superset of the range-partitioning key columns.

Refer to the CALL statement row for additional authorization changes that also apply to this command.
CREATE PROCEDURE (SQL) statement If you declare variables called TRUE or FALSE in an SQL procedure, this statement returns the SQL0408N error message and fails to create the procedure. Modify the procedure code and change the variable name before you re-issue the CREATE PROCEDURE statement.

In SQL procedures, when you assign XML data to input and output parameters of XML type or local variables of XML type, the XML data is now passed by reference. In previous releases, the XML data was passed by value. Therefore, some operations using XML data can return results that are different from the results returned by the same operations in previous releases.
CREATE TABLE The COMPRESS clause now enables compression for data in the table and the XML storage object. If you issue the ALTER TABLE statement with the ADD COLUMN clause to add an XML column, the compression of the XML data depends on whether data row compression is enabled for the table.

The COMPRESS and DATA CAPTURE CHANGES clauses can now be used together. SQL replication now supports tables with data row compression enabled as source and target tables. You will not receive an error if you specify both clauses with this statement.

You can now specify columns using the XML type when creating partitioned tables. In previous releases, the SQL1242N error message with reason code 2 was returned. XML data placement on a partitioned table follows the long data placement rules. The XML storage objects and the XML regions indexes are partitioned in the same manner as table data.

You can now specify columns with the XML type and use the ORGANIZE BY clause in the CREATE TABLE statement. If you specify columns with the XML type in the ORGANIZE BY clause, you will receive the SQL0350N error message. In previous releases, the SQL1242N error message with reason code 1 was returned.

XML columns are now supported in partitioned tables. The CREATE TABLE statement no longer returns SQL1242N error message with reason code 4 when you define a distribution key and XML columns in the same table. However, XML columns cannot be used to define the distribution key.

When you create a partitioned table, the system generated indexes for primary keys and unique constraints are always partitioned by default. If you create a partitioned table with XML columns, the XML regions indexes are always partitioned, and the XML column paths indexes are always non-partitioned. Refer to SQL Reference for details.

The NOT LOGGED option only applies to LOB data that is not inlined. In upgraded databases, the LOB data is implicitly inlined when the length is less than the LOB descriptor size. In this case, the NOT LOGGED option does not apply to implicitly inlined LOB data.

The COMPRESS YES clause now enables index compression in addition to data row compression when you create new indexes on the table. Use the CREATE INDEX statement with the COMPRESS NO clause if you must disable index compression.

Refer to the CALL statement row for additional authorization changes that also apply to this command.
CREATE TABLESPACE Whether a database has automatic storage enabled or not, reclaimable storage is the default when you create a new DMS table space. Reclaimable storage is a new functionality that allows you to reduce the table space high water mark and reclaim space. Refer to Adopting new Version 9.7 functionality in upgraded databases for details about reclaimable storage in upgraded databases.
DECLARE GLOBAL TEMPORARY TABLE Data row compression is enabled by default in temporary tables. However, rows are only compressed when the database manager determines that there is a performance gain.Consequently, index compression is also enabled for indexes that you create on temporary tables.

The DISTRIBUTE BY clause replaces the PARTITIONING KEY clause. For compatibility with previous releases, you can still use the PARTITIONING KEY clause.

Refer to the CREATE VIEW statement row for additional authorization changes that also apply to this command.
DROP Due to changes in the DB2 authorization model, the SYSADM group is no longer authorized to run this statement. Also, you might be required to hold SECADM, SQLADM , or WLMADM authority to drop certain objects. Refer to the SQL Reference for details about all the changes in authorization.

Soft invalidation is supported on the DROP ALIAS, DROP FUNCTION, DROP TRIGGER, DROP VARIABLE, and DROP VIEW statements when the DB2_DDL_SOFT_INVAL registry variable is set to ON. Refer to Automatic invalidation and revalidation of database objects for details about soft invalidation semantics.

Starting with Version 9.7 Fix Pack 1, the DROP WORKLOAD statement has a new optional clause RESTRICT. Dropping a workload that has activity thresholds or a work action sets applied to it now returns the SQL2090N error message. The RESTRICT clause is the default. You must drop any activity thresholds or a work action sets applied to the workload before dropping the workload.
GRANT and REVOKE The authorization to run these statements has changed. You are now required to hold SECADM authority to grant ACCESSCTRL, DATAACCESS, DBADM, or SECADM authority, and to hold ACCESSCTRL or SECADM authority to grant other authorities or privileges on global variables, indexes, packages, routines, schemas, sequences, servers, tables, views, nicknames, workloads, and XSR objects. The same changes apply to the REVOKE statements.

The UPGRADE DATABASE command explicitly grants DBADM authority to the SYSADM group and explicitly grants ACCESSCTRL and DATAACCESS authorities to users holding DBADM authority. After the upgrade, grant SECADM authority to users that need to grant ACCESSCTRL, DATAACCESS, DBADM, or SECADM authority.
Prefix operator + (unary plus) and prefix operator - (unary minus) The result data type of a prefix operator + or prefix operator - with an untyped parameter marker as an operand is now DECFLOAT(34). In previous releases, the result data type was DOUBLE.

Applications expecting a DOUBLE might receive the SQL0440N error message. Use the DOUBLE scalar function to explicitly cast the result data type of a prefix operator + or prefix operator - with an untyped parameter marker as an operand.
SET variable If you specified DEFAULT as a value to assign to a variable, DEFAULT now resolves to the DEFAULT keyword. If the usage of the DEFAULT keyword is invalid, it now returns the SQL0584N error message. In previous releases, DEFAULT as a value resolved to a variable or parameter called DEFAULT and only returned SQL0584N when such a variable or parameter was not defined. Delimit in double quotes the word DEFAULT in uppercase letters when you want to resolve to variables or parameters called DEFAULT as shown in this example:
CREATE PROCEDURE FOO(IN DEFAULT INTEGER)
BEGIN 
  DECLARE V0 INTEGER DEFAULT 1;
  SET V0 = "DEFAULT";
  RETURN V0;
END%

Refer to the CREATE VIEW statement row for additional authorization changes that also apply to this command.
Untyped NULL keyword in expressions You can now specify an untyped NULL keyword anywhere in an expression. If you use identifiers called NULL in SQL statements without being fully qualified or delimited such as column names or parameter names, the identifier specification could resolve to the keyword NULL instead of the identifier name. To avoid conflict with the untyped NULL keyword, fully qualify or delimit columns named NULL in SQL statements as shown in the following example:
SELECT MY_TAB.NULL FROM MY_TAB
SELECT "NULL" FROM MY_TAB
Related concepts:
Upgrade essentials for database applications
Related reference:
Upgrade impact from DB2 API changes
Upgrade impact from DB2 command changes
Upgrade impact from system catalog changes