DB2 Version 10.1 for Linux, UNIX, and Windows

Upgrade impact from SQL statement changes

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

The changes to SQL statements include new default behaviors and modifications to statement output. In addition, some statements are changed, deprecated or 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 DATABASE	The ALTER DATABASE statement has been deprecated. The CREATE STOGROUP statement or ALTER STOGROUP statement provide the same functionality as the ALTER DATABASE statement and more. Modify any scripts or application using the ALTER DATABASE statement and replace it with the CREATE STOGROUP or ALTER STOGROUP statements. For more details, see ALTER DATABASE statement has been deprecated for details.
ALTER TABLE, CREATE TABLE	The ALTER TABLE and CREATE TABLE statements have been changed. The COMPRESS clause for the ALTER TABLE and CREATE TABLE statements have default compression method changed. Also, the IMPLICITLY HIDDEN clause can be used in any type of column. If you have scripts or applications that issue the ALTER TABLE or CREATE TABLE statements with the COMPRESS YES clause, make sure that you add the STATIC or ADAPTIVE keyword to explicitly indicate the table compression method that you want. For more details, see ALTER TABLE and CREATE TABLE statement have been changed for details.
CREATE INDEX	The DETAILED keyword in the CREATE INDEX statement has changed default behavior. The DETAILED keyword in the CREATE INDEX statement now specifies that a sampling technique is to be used when processing index entries to collect extended index statistics. Use the SAMPLED DETAILED or UNSAMPLED DETAILED keywords when you issue a CREATE INDEX statement to get the wanted behavior. For more details, see DETAILED keyword in the CREATE INDEX statement now indicates a changed default behavior for details.
DECLARE GLOBAL TEMPORARY TABLE	The DECLARE GLOBAL TEMPORARY TABLE statement has been changed. Implicitly hidden columns in the declared temporary tables are not supported. Modify any scripts or applications using the LIKE clause to create created temporary tables or declared temporary tables from a base table that contains implicitly hidden columns. For more details, see CREATE GLOBAL TEMPORARY TABLE and DECLARE GLOBAL TEMPORARY TABLE statements have been changed for details.
ALTER WORKLOAD, CREATE WORKLOAD	The syntax for the ALTER WORKLOAD and CREATE WORKLOAD statements has changed. Modify scripts and applications that use the COLLECT UNIT OF WORK DATA PACKAGE LIST clause. Use the new syntax to collect package list information, as shown in the following example: `ALTER WORKLOAD REPORTS COLLECT UNIT OF WORK DATA BASE INCLUDE PACKAGE LIST` For more details, see ALTER WORKLOAD and CREATE WORKLOAD statements have changed for details.