CREATE_HISTORY_SNAPSHOTS procedure¶
Creates an exploded view of the specified history table with one row per entity per resolution time-slice (e.g. daily, monthly, yearly, etc.)
Prototypes¶
CREATE_HISTORY_SNAPSHOTS(SOURCE_SCHEMA VARCHAR(128), SOURCE_TABLE VARCHAR(128), DEST_SCHEMA VARCHAR(128), DEST_VIEW VARCHAR(128), RESOLUTION VARCHAR(11))
CREATE_HISTORY_SNAPSHOTS(SOURCE_TABLE VARCHAR(128), DEST_VIEW VARCHAR(128), RESOLUTION VARCHAR(11))
CREATE_HISTORY_SNAPSHOTS(SOURCE_TABLE VARCHAR(128), RESOLUTION VARCHAR(11))
Description¶
The CREATE_HISTORY_SNAPSHOTS procedure creates a view on top of a history table which is assumed to have a structure generated by CREATE_HISTORY_TABLE procedure. The view represents the history data as a series of “snapshots” of the main table at various points through time. The EFFECTIVE and EXPIRY columns from the source history table are replaced with a SNAPSHOT column which indicates the timestamp or date of the snapshot of the main table. All other columns are represented in their original form.
If DEST_VIEW is not specified it defaults to the value of SOURCE_TABLE
with '_HISTORY' replaced with a custom suffix which depends on the value of
RESOLUTION. For example, if RESOLUTION is 'MONTH' then the suffix
is 'MONTHLY', if RESOLUTION is 'WEEK', or 'WEEK_ISO' then the
suffix is 'WEEKLY' and so on. If DEST_SCHEMA and SOURCE_SCHEMA are
not specified they default to the current schema.
The RESOLUTION parameter determines the amount of time between snapshots.
Snapshots will be generated for the end of each period given by a particular
RESOLUTION. For example, if RESOLUTION is 'WEEK' then a snapshot
will be generated for the end of each week according to WEEKEND scalar function from
the earliest record in the history table up to the current date. See
CREATE_HISTORY_TRIGGERS procedure for a list of the possible values.
Note
All SELECT and CONTROL authorities present on the source table will be copied to the destination table.
Parameters¶
- SOURCE_SCHEMA
- If provided, specifies the schema containing the history table on which to base the new changes view. If omitted, defaults to the value of the CURRENT SCHEMA special register.
- SOURCE_TABLE
- The name of the history table on which to base the new snapshots view.
- DEST_SCHEMA
- If provided, specifies the schema which will contain the new snapshots view. If omitted, defaults to the value of the CURRENT SCHEMA special register.
- DEST_VIEW
- If provided, specifies the name of the new snapshots view. If omitted,
defaults to SOURCE_TABLE with
'_HISTORY'replaced with a suffix determined by the RESOLUTION parameter. - RESOLUTION
- Specifies the smallest unit of time that an entry in the view can cover. See CREATE_HISTORY_TRIGGERS procedure for a list of possible values. This should be greater than or equal to the RESOLUTION specified when the source table was created with CREATE_HISTORY_TABLE procedure (it is nonsensical to create a snapshot at finer resolution).
Examples¶
Create an INVOICES table in the current schema, then create a history table called INVOICES_HISTORY based on the INVOICES table with DAY resolution. Install the triggers which will keep the history table up to date with the base table, and finally create a view that will provide a weekly snapshot of the data:
CREATE TABLE INVOICES (
INVOICE CHAR(8) NOT NULL PRIMARY KEY,
CUSTOMER CHAR(8) NOT NULL REFERENCES CUSTOMERS(CUSTOMER),
ORDER INTEGER NOT NULL REFERENCES ORDERS(ORDER),
AMOUNT DECIMAL(17,2) NOT NULL,
PAID DATE DEFAULT NULL
) COMPRESS YES;
CALL CREATE_HISTORY_TABLE('INVOICES', 'DAY');
CALL CREATE_HISTORY_TRIGGERS('INVOICES', 'DAY');
CALL CREATE_HISTORY_SNAPSHOTS('INVOICES_HISTORY', 'WEEK');
The structure of the resulting tables and views can be seen below:
$ db2 DESCRIBE TABLE INVOICES
Data type Column
Column name schema Data type name Length Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
INVOICE SYSIBM CHARACTER 8 0 No
CUSTOMER SYSIBM CHARACTER 8 0 No
ORDER SYSIBM INTEGER 4 0 No
AMOUNT SYSIBM DECIMAL 17 2 No
PAID SYSIBM DATE 4 0 Yes
5 record(s) selected.
$ db2 DESCRIBE TABLE INVOICES_HISTORY
Data type Column
Column name schema Data type name Length Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
EFFECTIVE_DAY SYSIBM DATE 4 0 No
EXPIRY_DAY SYSIBM DATE 4 0 No
INVOICE SYSIBM CHARACTER 8 0 No
CUSTOMER SYSIBM CHARACTER 8 0 No
ORDER SYSIBM INTEGER 4 0 No
AMOUNT SYSIBM DECIMAL 17 2 No
PAID SYSIBM DATE 4 0 Yes
7 record(s) selected.
$ db2 DESCRIBE TABLE INVOICES_WEEKLY
Data type Column
Column name schema Data type name Length Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
SNAPSHOT SYSIBM DATE 4 0 Yes
INVOICE SYSIBM CHARACTER 8 0 No
CUSTOMER SYSIBM CHARACTER 8 0 No
ORDER SYSIBM INTEGER 4 0 No
AMOUNT SYSIBM DECIMAL 17 2 No
PAID SYSIBM DATE 4 0 Yes
6 record(s) selected.