- column-name
- Names a column in the table. If a list of column names is
specified, it must consist of as many names as there are columns in the result
table of the select-statement. Each column-name must be
unique and unqualified. If a list of column names is not specified, the
columns of the table inherit the names of the columns of the result table
of the select-statement.
A list of column names must be specified
if the result table of the select-statement has duplicate
column names or an unnamed column. An unnamed column is a column derived
from a constant, function, expression, or set operation (UNION or INTERSECT)
that is not named using the AS clause of the select list.
- FOR COLUMN system-column-name
- Provides an i5/OS name for the column. Do not use the same name for
more than one column of the table or for a column-name of the table.
If
the system-column-name is not specified, and the column-name is not
a valid system-column-name, a system column name is generated. For
more information about how system column names are generated, see Rules for Column Name Generation.
- select-statement
- Specifies that the columns of the table have the same name and description
as the columns that would appear in the derived result table of the select-statement if the select-statement were to be executed. The use of AS (select-statement) is an implicit definition of n columns for
the table, where n is the number of columns that would result from
the select-statement.
The implicit definition includes the following
attributes of the n columns (if applicable to the data type):
- Column name (and system column name)
- Data type, length, precision, and scale
- CCSID
- Nullability
- Column heading and text (see LABEL)
The following attributes are not included (the default value and
identity attributes may be included by using the copy-options):
- Default value
- Identity attributes
The implicit definition does not include any other optional attributes
of the identified table or view. For example, the new table does not automatically
include a primary key or foreign key from a table. The new table has these
and other optional attributes only if the optional clauses are explicitly
specified.
The select-statement must not refer to variables
or include parameter markers.
The select-statement must
not contain a PREVIOUS VALUE or a NEXT VALUE expression. The UPDATE, READ
ONLY, and OPTIMIZE clauses may not be specified.
- WITH DATA
- Specifies that the select-statement is executed. After the
table is created, the result table rows of the select-statement are
automatically inserted into the table.
- WITH NO DATA
- Specifies that the select-statement is used only to define
the attributes of the new a table. The table is not populated using the results
of the select-statement.
- refreshable-table-options
- Specifies that the table is a materialized query table and
the REFRESH TABLE statement can be used to populate the table with the results
of the select-statement.
A materialized query table whose select-statement contains a GROUP BY clause is summarizing data from
the tables referenced in the select-statement. Such a materialized
query table is also known as a summary table. A summary table is
a specialized type of materialized query table.
When a materialized
query table is defined, the following select-statement restrictions
apply:
- The select-statement cannot contain a reference to another materialized
query table or to a view that refers to a materialized query table.
- The select-statement cannot contain a reference to a declared
global temporary table, a table in QTEMP, a program-described file, or a non-SQL
logical file in the FROM clause.
- The select-statement cannot contain a reference to
a view that references another materialized query table or a declared global
temporary table. When a materialized query table is defined with ENABLE QUERY
OPTIMIZATION, the select-statement cannot contain a reference to
a view that contains one of the restrictions from the following paragraph.
- The select-statement cannot contain an expression with a DataLink
or a distinct type based on a DataLink where the DataLink is FILE LINK CONTROL.
- The select-statement cannot contain a result column that is a
not an SQL data type, such as binary with precision, DBCS-ONLY, or DBCS-EITHER.
When a materialized query table is defined with ENABLE QUERY OPTIMIZATION,
the following additional select-statement restrictions apply:
- Must not include any special registers.
- Must not include any non-deterministic functions.
- The ORDER BY clause is allowed, but is only used by REFRESH. It may improve
locality of reference of data in the materialized query table.
- If the subselect references a view, the select-statement in the view definition must satisfy the preceding restrictions.
- DATA INITIALLY DEFERRED
- Specifies that the data is not inserted into the materialized query
table when it is created. Use the REFRESH TABLE statement to populate the
materialized query table, or use the INSERT statement to insert data into
a materialized query table.
- DATA INITIALLY IMMEDIATE
- Specifies that the data is inserted into the materialized query table
when it is created.
- REFRESH DEFERRED
- Specifies that the data in the table can be refreshed at any time using
the REFRESH TABLE statement. The data in the table only reflects the result
of the query as a snapshot at the time when the REFRESH TABLE statement is
processed or when it was last updated.
- MAINTAINED BY USER
- Specifies that the materialized query table is maintained by the user.
The user can use INSERT, DELETE, UPDATE, or REFRESH TABLE statements on the
table.
- ENABLE QUERY OPTIMIZATION or DISABLE
QUERY OPTIMIZATION
- Specifies whether this materialized query table can be used for optimization.
The default is ENABLE QUERY OPTIMIZATION.
- ENABLE QUERY OPTIMIZATION
- Specifies that the materialized query table can be used for query optimization.
If the select-statement specified does not satisfy the restrictions
for query optimization, an error is returned.
- DISABLE QUERY OPTIMIZATION
- Specifies that the materialized query table cannot be used for query
optimization. The table can still be queried directly.
(C) Copyright IBM Corporation 1992, 2006. All Rights Reserved.