The REFRESH DIMENSION TABLE command copies the contents of the head dimension table to the corresponding table on each partition node.
Required
Privileges
In order to refresh a dimension table, the user authorization must have at least SELECT privileges on the head dimension table.
As well, the users defined for the connections to the remote nodes must have at least DELETE and INSERT privileges on the remote dimension tables.
Syntax
table name
This is the name of an existing dimension table in the current schema. The
table name may be qualified by a schema name (that is, schema-name.table-name) to refresh a dimension table
that is not in the current schema.
Description
Remote dimension tables are automatically updated whenever an INSERT, UPDATE, or DELETE command is excuted against the head dimension table, thereby maintaining consistency of dimension data across all nodes. However, if any records in remote dimension tables were added, changed, or removed manually, or if data was loaded into the head dimension table via the SAND Data Loader, the dimension tables would no longer be in a consistent state. In this situation, the REFRESH DIMENSION TABLE command can be used to restore consistency. This command copies the data from the specified dimension table on the head node to all of the associated dimension tables on remote nodes, replacing the existing contents of those remote tables.
In effect, this command initiates a two-part process for each related dimension table:
- delete all records in the remote dimension table
- load all records from the head dimension table
Note that the effects of the REFRESH DIMENSION TABLE command cannot be undone via a ROLLBACK. When the REFRESH DIMENSION TABLE is executed, the head dimension data replaces the remote dimension data immediately, without requiring a COMMIT [WORK] command.
Example
REFRESH DIMENSION TABLE s1.d1;
The above example refreshes dimension table d1 (belonging to schema s1) across all nodes where the same dimension table exists.