REFRESH TABLE
The REFRESH TABLE statement refreshes the data in a materialized query table. The statement deletes all rows in the materialized query table, executes the fullselect in the table definition to recalculate the data from the tables specified in the fullselect, inserts the calculated result into the materialized query table, and updates the catalog for the refresh timestamp and cardinality of the table. The table can exist at the current server or at any DB2® subsystem with which the current server can establish a connection.
Invocation
This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared only if DYNAMICRULES run behavior is implicitly or explicitly specified.
Authorization
The privilege set for REFRESH TABLE must include at least one of the following authorities:
- Ownership of the materialized query table
- DBADM or DBCTRL authority on the database that contains the materialized query table
- SYSADM or SYSCTRL authority
DATAACCESS
authority
If the database is implicitly created, the database privileges must be on the implicit database or on DSNDB04.
Privilege set: If the statement is embedded in an application program, the privilege set is the privileges that are held by the owner of the plan or package. If the statements dynamically prepared, the privilege set is determined by the DYNAMICRULES behavior in effect (run, bind, define, or invoke). For more information on these behaviors, including a list of the DYNAMICRULES bind option values, see Authorization IDs and dynamic SQL.
Syntax
>>-REFRESH TABLE--table-name--+------------------+------------->< '-QUERYNO--integer-'
Description
- table-name
- Identifies the table to be refreshed. The name must identify a materialized query table. REFRESH TABLE evaluates the fullselect in the materialized-query-definition clause to refresh the table. The isolation level for the fullselect is the isolation level of the materialized query table recorded when CREATE TABLE or ALTER TABLE was issued.
- QUERYNO integer
- Specifies the number to be used for this SQL statement in EXPLAIN
output and trace records. The number is used for the QUERYNO column
of the plan table for the rows that contain information about this
SQL statement. This number is also used in the QUERYNO column of the
SYSIBM.SYSSTMT and SYSIBM.SYSPACKSTMT catalog tables.
If the clause is omitted, the number associated with the SQL statement is the statement number assigned during precompilation. Thus, if the application program is changed and then precompiled, that statement number might change.
Notes
Automatic query rewrite using materialized query tables is not attempted for the fullselect in the materialized query table definition during the processing of REFRESH TABLE statement.
After successful execution of a REFRESH TABLE statement, the SQLCA field SQLERRD(3) will contain the number of rows inserted into the materialized query table.
The EXPLAIN output for REFRESH TABLE table-name is the same as the EXPLAIN output for INSERT INTO table-name fullselect where fullselect is from the materialized query table definition.
If the materialized query table has a security label column, the REFRESH TABLE statement does not do any checking for multilevel security with row-level granularity when it deletes and repopulates the data in the table by executing the fullselect. Instead, DB2 performs the checking for multilevel security with row-level granularity when the materialized query table is exploited in automatic query rewrite or is used directly.
The REFRESH TABLE statement can be used to remove a table space from the logical page list and reset recover-pending status. This can only be done by using REFRESH TABLE to repopulate a materialized query table where the materialized query table is the only table in the table space.
Example
REFRESH TABLE SALESCOUNT;