The CREATE SNAPSHOT TABLE command creates a new snapshot table based on one or more standard tables, views, or other snapshot tables.
Required
Privileges
To execute the CREATE SNAPSHOT TABLE command, the user authorization must own the schema where the table will be created, possess the OWNER privilege on the schema, or possess DBA privileges.
For every base table, snapshot table, or view identified in the query expression part of the CREATE SNAPSHOT TABLE statement, the user authorization must own the table/view, own or possess OWNER privileges on the schema containing the table/view, possess SELECT privileges on the table/view, or else possess DBA privileges.
Syntax
snapshot table name
This is the name of the snapshot table that will be created.
column name
The column name argument is an SQL identifier (up to 128 characters
long) specifying a column in the snapshot table. Column names must satisfy
the same criteria as base table column names. If multiple column name
items are specified, the number of these columns must match the number of
columns in the result table specified in the query expression of the CREATE
SNAPSHOT TABLE statement.
The column name list is optional. If the list is absent, the snapshot table columns will use the column names or aliases specified in the projection list of the query expression. If the query expression specifies duplicate output column names, a column name list must be specified for the snapshot table.
query expression
Preceded by the keyword AS, the query expression argument is a SELECT
statement producing a results table that effectively constitutes the snapshot
table. All referenced tables in the query expression must exist in the database.
Description
The CREATE SNAPSHOT TABLE statement brings a new snapshot table into existence. The structure and contents of this snapshot table is defined by the query expression in the defining statement, similar to the CREATE TABLE...SELECT command or a CREATE VIEW statement. Essentially, the output table produced by the query is preserved, and becomes the new snapshot table.
If an ORDER BY clause is part of the query, a FETCH FIRST...ONLY clause must be present as well or else the ORDER BY clause will be ignored. Also, duplicate column names in the SELECT list are not permitted, unless the columns are provided with unique names in the snapshot table column name list. Otherwise, any legal query expression can be used to define a snapshot table.
To subsequently update a snapshot table, if the data in the source table(s) has changed, execute the REFRESH SNAPSHOT TABLE command.
If an existing snapshot table is no longer required, it can be removed from the database by executing the DROP SNAPSHOT TABLE command.
Example
CREATE SNAPSHOT TABLE st1 ( Account, Total, Differential ) AS
SELECT A.acctnum, A.wtotal, A.wtotal - W.diff
FROM acctab A, worktab W
WHERE A.acctnum BETWEEN '4000' AND '7000'
AND A.wtotal = W.wtotal
AND W.wtotal > 0 ;
The above example creates a snapshot table called st1, based on a join of the acctab and worktab tables. A column list is included in the snapshot table definition, though it is optional in this case (note that if both A.wtotal and W.wtotal were included in the query projection list, the column list or explicit naming in the query expression would be a necessity). The SELECT statement defines the structure of the snapshot table and determines its data content: The projection list specifies the fields in the resulting snapshot table, while the conditions in the WHERE clause limit which records from the acctab and worktab tables will be joined in st1.