REFRESH TABLE statement

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 on the schema containing the materialized query table
DATAACCESS authority

Syntax

online-options

query-optimization-options

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 or from all schemas of the tables at the data source(SQLSTATE 42501).
The NOT INCREMENTAL clause must be used if REFRESH TABLE is issued on a system-maintained column-organized MQT.

Notes

When the statement is used to refresh a REFRESH IMMEDIATE materialized query table whose underlying tables have been loaded, attached, or detached, the system might choose to incrementally refresh the materialized query table with the delta portions of its underlying tables. When the statement is used to refresh a REFRESH DEFERRED materialized query table with a supporting staging table, the system might choose to incrementally refresh the materialized query table with the delta portions of its underlying tables that have been captured in the staging table. However, there are some situations in which this optimization is not possible, and a full refresh (that is, a recomputation of the materialized query table definition) is necessary to ensure data integrity. You can explicitly request incremental maintenance by specifying the INCREMENTAL option; if this optimization is not possible, the system returns an error (SQLSTATE 55019).
If the ALLOW QUERY OPTIMIZATION USING REFRESH DEFERRED TABLES WITH REFRESH AGE ANY option is used, ensure that the refresh order is correct for REFRESH DEFERRED materialized query tables. For example, consider two materialized query tables, MQT1 and MQT2, whose materialized queries share the same underlying tables. The materialized query for MQT2 can be calculated using MQT1, instead of the underlying tables. If separate statements are used to refresh these two materialized query tables, and MQT2 is refreshed first, the system might choose to use the contents of MQT1, which have not yet been refreshed, to refresh MQT2. In this case, MQT1 would contain current data, but MQT2 could still contain stale data, even though both were refreshed at almost the same time. The correct refresh order, if two REFRESH statements are used instead of one, is to refresh MQT1 first.
If the materialized query table has an associated staging table, the staging table is pruned when the refresh is successfully performed.
Any label-based access control on the base tables or on the materialized query table does not interfere with the refresh process. The refresh happens as if label-based access control were not present. The automatic protection that is associated with the materialized query table when it is created ensures that the data from the base tables remains protected when it is passed into the materialized query table.
For materialized query table only, SET INTEGRITY FOR mqt_name IMMEDIATE CHECKED is the same as REFRESH TABLE mqt_name.
Refresh use of materialized query tables: Materialized query tables are not used to evaluate the select-statement during the processing of the REFRESH TABLE statement.
Refresh isolation level: The isolation level used to evaluate the select-statement is the isolation level specified on the isolation-level clause of the select-statement. Or, if the isolation-level clause was not specified, the isolation level of the materialized query table recorded when CREATE TABLE or ALTER TABLE was issued is used to evaluate the select-statement.
Consider the statement:
```
   SET INTEGRITY FOR T IMMEDIATE CHECKED
```
In the following scenarios, neither the INCREMENTAL check option for T nor an incremental refresh of T---if T is a materialized query table (MQT) or a staging table---is supported:
- New constraints have been added to T while it is in set integrity pending state
- When a LOAD REPLACE operation against T, it parents, or its underlying tables has taken place
- When the NOT LOGGED INITIALLY WITH EMPTY TABLE option has been activated after the last integrity check on T, its parents, or its underlying tables
- The cascading effect of full processing, when any parent of T (or underlying table, if T is a materialized query table or a staging table) has been checked for integrity non-incrementally
- If the table space containing the table or its parent (or underlying table of a materialized query table or a staging table) has been rolled forward to a point in time, and the table and its parent (or underlying table if the table is a materialized query table or a staging table) reside in different table spaces
- T is an MQT, and a LOAD REPLACE or LOAD INSERT operation directly into T has taken place after the last refresh
Incremental processing will be used whenever the situation allows it, because it is more efficient. The INCREMENTAL option is not needed in most cases. It is needed, however, to ensure that integrity checks are indeed processed incrementally. If the system detects that full processing is needed to ensure data integrity, an error is returned (SQLSTATE 55019).
If the conditions for full processing described in the previous bullet are not satisfied, the system will perform an incremental refresh (if it is a materialized query table) when the user does not specify the NOT INCREMENTAL option for the statement SET INTEGRITY FOR T IMMEDIATE CHECKED.