CREATE_HISTORY_CHANGES procedure¶

Creates an “OLD vs NEW” changes view on top of the specified history table.

Prototypes¶

CREATE_HISTORY_CHANGES(SOURCE_SCHEMA VARCHAR(128), SOURCE_TABLE VARCHAR(128), DEST_SCHEMA VARCHAR(128), DEST_VIEW VARCHAR(128))
CREATE_HISTORY_CHANGES(SOURCE_TABLE VARCHAR(128), DEST_VIEW VARCHAR(128))
CREATE_HISTORY_CHANGES(SOURCE_TABLE VARCHAR(128))

Description¶

The CREATE_HISTORY_CHANGES 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 “change” rows. The EFFECTIVE and EXPIRY columns from the source history table are merged into a CHANGED column, a CHANGE column is calculated to show whether each change was an insertion, update, or deletion, and all other columns are represented twice as OLD_ and NEW_ variants.

If DEST_VIEW is not specified it defaults to the value of SOURCE_TABLE with '_HISTORY' replaced with '_CHANGES'. If DEST_SCHEMA and SOURCE_SCHEMA are not specified they default to the current schema.

Note

All SELECT and CONTROL authorities present on the source table will be copied to the destination table.

The type of change can be determined by querying the CHANGE column in the new view. The possible values (and their criteria) are:

CHANGE value	Criteria
`'INSERT'`	If the old key or keys are NULL and the new are non-NULL,the change was an insertion.
`'UPDATE'`	If both the old and new key or keys are non-NULL the change was an update.
`'DELETE'`	If the old key or keys are non-NULL and the new are NULL the change was a deletion.
`'ERROR'`	This should never happen!

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 changes view.
DEST_SCHEMA: If provided, specifies the schema which will contain the new changes view. If omitted, defaults to the value of the CURRENT SCHEMA special register.
DEST_VIEW: If provided, specifies the name of the new changes view. If omitted, defaults to SOURCE_TABLE with '_HISTORY' replaced with '_CHANGES'.

Examples¶

Create a CUSTOMERS table in the current schema, then create a history table called CUSTOMERS_HISTORY based upon on the CUSTOMERS 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 old vs. new comparisons of the history:

CREATE TABLE CUSTOMERS (
  ID         INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  NAME       VARCHAR(100) NOT NULL,
  ADDRESS    VARCHAR(2000) NOT NULL,
  SECTOR     CHAR(2) NOT NULL
) COMPRESS YES;
CALL CREATE_HISTORY_TABLE('CUSTOMERS', 'DAY');
CALL CREATE_HISTORY_TRIGGERS('CUSTOMERS', 'DAY');
CALL CREATE_HISTORY_CHANGES('CUSTOMERS_HISTORY');

The structure of the resulting tables and views can be seen below:

$ db2 DESCRIBE TABLE CUSTOMERS

                                Data type                     Column
Column name                     schema    Data type name      Length     Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
ID                              SYSIBM    INTEGER                      4     0 No
NAME                            SYSIBM    VARCHAR                    100     0 No
ADDRESS                         SYSIBM    VARCHAR                   2000     0 No
SECTOR                          SYSIBM    CHARACTER                    2     0 No

  4 record(s) selected.


$ db2 DESCRIBE TABLE CUSTOMERS_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
ID                              SYSIBM    INTEGER                      4     0 No
NAME                            SYSIBM    VARCHAR                    100     0 No
ADDRESS                         SYSIBM    VARCHAR                   2000     0 No
SECTOR                          SYSIBM    CHARACTER                    2     0 No

  6 record(s) selected.


$ db2 DESCRIBE TABLE CUSTOMERS_CHANGES

                                Data type                     Column
Column name                     schema    Data type name      Length     Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
CHANGED                         SYSIBM    DATE                         4     0 Yes
CHANGE                          SYSIBM    CHARACTER                    6     0 No
OLD_ID                          SYSIBM    INTEGER                      4     0 Yes
NEW_ID                          SYSIBM    INTEGER                      4     0 Yes
OLD_NAME                        SYSIBM    VARCHAR                    100     0 Yes
NEW_NAME                        SYSIBM    VARCHAR                    100     0 Yes
OLD_ADDRESS                     SYSIBM    VARCHAR                   2000     0 Yes
NEW_ADDRESS                     SYSIBM    VARCHAR                   2000     0 Yes
OLD_SECTOR                      SYSIBM    CHARACTER                    2     0 Yes
NEW_SECTOR                      SYSIBM    CHARACTER                    2     0 Yes

  10 record(s) selected.