SQL statements

SQL statements to support materialized views The following SQL statements have been added, or have had their syntax and functionality extended, to support materialized views:

ALTER MATERIALIZED VIEW statement. See ALTER MATERIALIZED VIEW statement.
COMMENT statement. See COMMENT statement.
CREATE INDEX statement. See CREATE INDEX statement.
CREATE MATERIALIZED VIEW statement. See CREATE MATERIALIZED VIEW statement.
DROP MATERIALIZED VIEW statement. See DROP MATERIALIZED VIEW statement.
REFRESH MATERIALIZED VIEW statement. See REFRESH MATERIALIZED VIEW statement.
VALIDATE statement. See VALIDATE statement.

A new OPTION clause in the SELECT statement can be used to override the materialized_view_optimization database option. See SELECT statement.

New SQL statements to support diagnostic tracing and application profiling The new SQL statements to support the Application Profiling feature are listed below:

ATTACH TRACING statement. See ATTACH TRACING statement.
DETACH TRACING statement. See DETACH TRACING statement.
REFRESH TRACING LEVEL statement. See REFRESH TRACING LEVEL statement.

New VALIDATE DATABASE statement You can now validate the database using the VALIDATE DATABASE statement. See VALIDATE statement.

New VALIDATE MATERIALIZED VIEW statement You can now validate materialized views using the VALIDATE MATERIALIZED VIEW statement. See VALIDATE statement.

New ALTER STATISTICS statement You can now control whether column statistics are automatically updated using the ALTER STATISTICS statement. You can still force an update of statistics on columns where automatic updating has been disabled, using an explicit CREATE STATISTICS or DROP STATISTICS statement. See ALTER STATISTICS statement.

ALTER INDEX statement enhancement You can now rebuild an index using the REBUILD clause of the ALTER INDEX statement. See ALTER INDEX statement.

ALTER TABLE and CREATE TABLE statement enhancements You now have finer control over what constitutes a match between a foreign key in a referencing table, and the primary key in the referenced table using the MATCH clause. You are also able to declare a foreign key as unique, thereby eliminating the need to declare uniqueness separately. See CREATE TABLE statement and ALTER TABLE statement.

New CALIBRATE PARALLEL READ clause for the ALTER DATABASE statement Use the new CALIBRATE PARALLEL READ clause of the ALTER DATABASE statement to detect hardware capable of parallel input and output. You can retrieve the calibration result for a dbspace by querying the new IOParallelism extended database property using the DB_EXTENDED_PROPERTY function. See ALTER DATABASE statement, and Accessing database property values.

New PRIMARY KEY ON clause for COMMENT statement You can now create remarks for primary keys using the PRIMARY KEY ON clause of the COMMENT statement. See COMMENT statement.

CREATE ENCRYPTED FILE statement enhancement to change encryption keys Using extensions to the CREATE ENCRYPTED FILE statement, you can now change the encryption key used to encrypt a database, transaction log, or dbspace without unloading and reloading the database. If the database is not encrypted, but table encryption is enabled, you can use the CREATE ENCRYPTED FILE statement to change the key used for table encryption. See CREATE ENCRYPTED FILE statement.

CREATE DATABASE statement enhancements Three new clauses, ENCODING, NCHAR COLLATION, and ACCENT, have been added for improved handing of character sets. Also, a DATABASE SIZE clause has been added so you can specify the initial size of a database. See CREATE DATABASE statement.

SELECT statement enhancements The FOR UPDATE clause, used in updating rows through a cursor, has been extended to allow column lists to restrict which columns can be modified using a subsequent positioned UPDATE statement. See SELECT statement.

The FROM clause of a SELECT statement has been extended to support the READPAST table hint, which directs the database server to ignore locked rows, and the UPDLOCK table hint, which behaves similarly to XLOCK. See FROM clause.

The SELECT statement has been extended to support an OPTION clause to control aspects of query optimization for that particular statement. The OPTION clause includes syntax for controlling the matching of materialized views via the MATERIALIZED VIEW OPTIMIZATION clause for this specific SELECT statement. A second clause, FORCE OPTIMIZATION, directs the database server to perform optimization on a query, even if the query qualifies for bypassing cost-based optimization. See SELECT statement.

LOAD TABLE and UNLOAD TABLE statement enhancements The STRIP clause for the LOAD TABLE statement now accepts options that allow you to control whether leading blanks are stripped from unquoted values before they are inserted. Additional STRIP options let you fine tune how the data is stripped.

The LOAD TABLE statement has also been extended to support the COMMENTS INTRODUCED BY option. This option allows you to specify the string used to identify comments in the input data. Any lines in the input that begin with the specified string are ignored during the load operation.

Both the LOAD TABLE and UNLOAD TABLE statements have been extended to support the following options:

ENCODING option Used to specify the encoding to use when loading or unloading data.
ROW DELIMITED BY option Used to specify the string that indicates the end of an input record when bulk loading or unloading data.
QUOTE option Similar to the QUOTE option for the OUTPUT statement in Interactive SQL. See OUTPUT statement [Interactive SQL].

See LOAD TABLE statement and UNLOAD statement.

VALIDATE INDEX statement enhancements The syntax for VALIDATE INDEX has been enhanced to support index specifications. See VALIDATE statement.

Enhancements to the ALTER INDEX statement to rename primary keys You can now rename primary keys using the ALTER INDEX statement. See ALTER INDEX statement.

New CONTINUE statement Use this statement to restart a loop. Statements in the loop following the CONTINUE statement are skipped. See CONTINUE statement.

New BREAK statement [T-SQL] Use this statement to leave a compound statement or loop. See BREAK statement [T-SQL].

Enhancement to the INSERT statement control updating default values during an INSERT You can control whether default values are updated during an INSERT when a row already exists using the DEFAULTS ON | OFF clause. This new capability does not extend to the following default fields: DEFAULT TIMESTAMP, DEFAULT UTC TIMESTAMP, and DEFAULT LAST USER; these fields are always updated. See INSERT statement.

Enhancement to the DELETE statement to support an ORDER BY clause The DELETE statement now supports the ORDER BY clause, which allows you to specify the order in which rows are deleted from the database. See DELETE statement.

Enhancements to the START DATABASE statement The START DATABASE statement now returns a wider range of error messages when the statement fails to indicate the reason why the database failed to start. As well, the START DATABASE clauses can now be specified in any order. See START DATABASE statement.

Enhancement to the MESSAGE statement to support logging only to event or system log In addition to being able to turn on or off logging, you can also specify whether to log only to the Event or System log. Syntax for the MESSAGE statement has been extended to allow the optional clause [ EVENT | SYSTEM ] in within the TO LOG clause. For example, TO EVENT LOG results in logging only to the Event log. See MESSAGE statement.

FOR OLAP WORKLOAD option The syntax for the CREATE INDEX, CREATE TABLE, and ALTER TABLE statements has been extended to support a FOR OLAP WORKLOAD option in the foreign key definition. This option instructs the database server to perform certain optimizations and gather statistics on the key to improve OLAP performance. See CREATE INDEX statement, and CREATE TABLE statement.

Support for temporary stored procedures You can now create temporary stored procedures using an extension to the CREATE PROCEDURE statement. Temporary stored procedures are visible only by the connection that created them, and are automatically dropped when the connection is dropped. See CREATE PROCEDURE statement [Web service].

Support for local temporary tables You can now create local temporary tables using the CREATE LOCAL TEMPORARY TABLE statement. Local temporary tables created this way are dropped when the connection closes. See CREATE LOCAL TEMPORARY TABLE statement.

Enhancements to temporary tables You can now create global temporary tables whose data can be shared by all connections to a database, using the SHARE BY ALL clause of the CREATE GLOBAL TEMPORARY TABLE statement. See CREATE TABLE statement.