The
REFRESH TABLE statement refreshes the data in a materialized query
table.
Invocation
This statement can be embedded
in an application program or issued through the use of dynamic SQL
statements. It is an executable statement that can be dynamically
prepared.
Authorization
The privileges held by the
authorization ID of the statement must include at least one of the
following authorities:
- CONTROL privilege on the table
- DATAACCESS authority
Syntax
.-,--------------------------------------------------------------.
V |
>>-REFRESH TABLE----table-name--| online-options |--| query-optimization-options |-+-->
>--+-----------------+-----------------------------------------><
+-INCREMENTAL-----+
'-NOT INCREMENTAL-'
online-options
.-ALLOW NO ACCESS----.
|--+--------------------+---------------------------------------|
+-ALLOW READ ACCESS--+
'-ALLOW WRITE ACCESS-'
query-optimization-options
|--+---------------------------------------------------------------------------------------+--|
| .-ALLOW QUERY OPTIMIZATION-. .-WITH REFRESH AGE ANY-. |
'-+--------------------------+--USING REFRESH DEFERRED TABLES--+----------------------+-'
Description
- table-name
- Identifies the table to be refreshed.
The name, including
the implicit or explicit schema, must identify a table that already
exists at the current server. The table must allow the REFRESH TABLE
statement (SQLSTATE 42809). This includes materialized query tables
defined with:
- REFRESH IMMEDIATE
- REFRESH DEFERRED
- online-options
- Specifies the accessibility of the table while it is being processed.
- ALLOW NO ACCESS
- Specifies
that no other users can access the table while it is being refreshed,
except if they are using the Uncommitted Read isolation level.
- ALLOW READ ACCESS
- Specifies that other users have read-only access to the table
while it is being refreshed.
- ALLOW WRITE ACCESS
- Specifies that other users have read and write access to the table
while it is being refreshed.
To prevent a rollback of the entire statement because
of a lock timeout when using the ALLOW READ ACCESS or the ALLOW WRITE
ACCESS option, it is recommended that you issue a SET CURRENT LOCK
TIMEOUT statement (specifying the WAIT option) before executing the REFRESH
TABLE statement, and to reset the special register to its previous
value afterwards. Note, however, that the CURRENT LOCK TIMEOUT
register only impacts a specific set of lock types, not all lock types.
- query-optimization-options
- Specifies the query optimization options for the refresh of REFRESH
DEFERRED materialized query tables.
- ALLOW QUERY OPTIMIZATION USING REFRESH DEFERRED TABLES WITH REFRESH
AGE ANY
- Specifies that when the CURRENT REFRESH AGE special register is
set to 'ANY', the refresh of table-name will
allow REFRESH DEFERRED materialized query tables to be used to optimize
the query that is used to refresh table-name.
If table-name is not a REFRESH DEFERRED
materialized query table, an error is returned (SQLSTATE 428FH). REFRESH
IMMEDIATE materialized query tables are always considered for query
optimization.
- INCREMENTAL
- Specifies an incremental refresh for the table by considering
only the delta portion (if any) of its underlying tables
or the content of an associated staging table (if one exists and its
contents are consistent). If such a request cannot be satisfied (that
is, the system detects that the materialized query table definition
needs to be fully recomputed), an error (SQLSTATE 55019) is returned.
- NOT INCREMENTAL
- Specifies a full refresh for the table by recomputing the materialized
query table definition.
If neither INCREMENTAL nor NOT INCREMENTAL is
specified, the system will determine whether incremental processing
is possible; if not, full refresh will be performed. If a staging
table is present for the materialized query table that is to be refreshed,
and incremental processing is not possible because the staging table
is in a pending state, an error is returned (SQLSTATE 428A8). Full
refresh will be performed if the staging table or the materialized
query table is in an inconsistent state; otherwise, the contents of
the staging table will be used for incremental processing.
Rules
- If REFRESH TABLE is issued on a materialized query table that
references one or more nicknames, the authorization ID of the statement
must have authority to select from the tables at the data source (SQLSTATE
42501).