Creating and altering objects for time travel
Overview
To run time travel queries on NPSaaS, create a time travel table, schema, or database (time travel objects) by setting DATA_VERSION_RETENTION_TIME (retention time interval) to a nonzero value. To set DATA_VERSION_RETENTION_TIME, use the CREATE or ALTER command for these object types or use the web console. You can select between 1 day and up to 99 days, or zero to alter a temporal object to nontemporal.
Before you set the retention time intervals for all tables in a schema or database, consider the cost of storage for temporal tables, which could be significant. See Managing time travel space usage.
Also, you can set a default DATA_VERSION_RETENTION_TIME for the system.
Temporal tables usually have current rows and historical rows. Current rows are not marked for deletion. Historical rows are marked for deletion (by the DELETE, UPDATE, MERGE, or TRUNCATE statements) and are visible to time travel queries up to the specified retention time interval past their delete timestamps.
A table with a DATA_VERSION_RETENTION_TIME value of zero is nontemporal. Only its current (not deleted) rows are visible to queries. Some historical rows might be preserved for incremental backup purposes, but the rows are not accessible to time travel queries.
If you alter DATA_VERSION_RETENTION_TIME of an existing temporal table to zero, the table becomes a nontemporal table. It no longer supports access to historical data with time travel queries.
Limitations
Changes to retention time intervals for tables, schemas, and databases are not replicated from primary to replica nodes. They are not historical (deleted) table rows either.
Retention time intervals of tables, schemas, and databases are automatically backed up by the nzbackup command and restored by the nzrestore command. The insert and delete timestamps of table rows are not backed up
and restored. Restored rows get fresh insert timestamps in the target database or system, and rows that were deleted by incremental restore get fresh delete timestamps in the target database or system. There is no expectation or guarantee
that the number of historical versions of a row on the target will match that on the source database. For example, a specific row might get updated multiple times, or get updated and then deleted, between backup increments on the source. This
actions result in a single delete of that row on the target.
If you try to expand your NPSaaS system with additional SPU enclosures, the procedure results in the loss of historical (deleted) rows. All current rows are redistributed with fresh insert timestamps.
The following types of tables cannot be temporal tables:
- Temporary tables
- External tables
- Versioned tables that were altered by adding and/or dropping columns
- Row-secure tables.
This limitation affects the following commands:
-
CREATE TABLE ROW SECURITY, CREATE EXTERNAL TABLE, CREATE TEMPORARY TABLE If DATA_VERSION_RETENTION_TIME is specified to a nonzero value, the commands fail.
-
ALTER TABLE DATA_VERSION_RETENTION_TIME If the table is a temporary, row-secure, versioned, or an external table and DATA_VERSION_RETENTION_TIME is specified with a nonzero value, the command fails.
-
ALTER TABLE ADD COLUMN, ALTER TABLE DROP COLUMN If the table has a nonzero DATA_VERSION_RETENTION_TIME specified, the command fails.
The GROOM TABLE VERSIONS command turns a versioned table into nonversioned. When this happens, you can specify a nonzero DATA_VERSION_RETENTION_TIME with the ALTER TABLE command.
Exceptions related to DATA_VERSION_RETENTION_TIME for watsonx.data
There are exceptions for watsonx.data related to DATA_VERSION_RETENTION_TIME. The following table covers the behavior of the DATA_VERSION_RETENTION_TIME property for databases, schemas, and tables for watsonx.data.
| Behavior | Exception |
|---|---|
| CREATE DATABASE with DATA_VERSION_RETENTION_TIME for watsonx.data | Not allowed |
| ALTER DATABASE with DATA_VERSION_RETENTION_TIME for watsonx.data | Not allowed |
| CREATE SCHEMA with DATA_VERSION_RETENTION_TIME for watsonx.data | Not allowed |
| ALTER SCHEMA with DATA_VERSION_RETENTION_TIME for watsonx.data | Not allowed except for NETEZZA_SCHEMA |
| CREATE TABLE with DATA_VERSION_RETENTION_TIME (temporal) table | Allowed under NETEZZA_SCHEMA only |
| ALTER TABLE with DATA_VERSION_RETENTION_TIME (temporal) table | Allowed under NETEZZA_SCHEMA only |
If SYSTEM DEFAULT DATA_VERSION_RETENTION_TIME is nonzero , CREATE DATABASE will not inherit the property from SYSTEM DEFAULT. CREATE SCHEMA including NETEZZA_SCHEMA will not inherit DB property. There will be no impact or changes needed for time travel related automaint task for watsonx.data.
Creating time travel objects with the command-line
Creating temporal tables with the command-line
To create a temporal table, set DATA_VERSION_RETENTION_TIME to a nonzero value.
For detailed syntax and privileges, see the CREATE TABLE command.
CREATE TABLE <tablename> ( <col>[, <col>… ] ) DATA_VERSION_RETENTION_TIME <number-of-days>;
Example:
CREATE TABLE PRODUCT (prodid int, proddesc char(100)) DATA_VERSION_RETENTION_TIME 30;
When you insert a row into the table, the row receives a virtual insert timestamp that is equal to the commit time of the inserting transaction.
When you delete a row from the table, the row receives a virtual delete timestamp that is equal to the commit time of the deleting (or truncating) transaction.
Creating temporal schemas with the command-line
To create a temporal schema, set DATA_VERSION_RETENTION_TIME to a nonzero value.
For detailed syntax and privileges, see the CREATE SCHEMA command.
CREATE SCHEMA <schema_name> DATA_VERSION_RETENTION_TIME <number-of-days>;
Example:
CREATE SCHEMA SCHEMA1 DATA_VERSION_RETENTION_TIME 30;
Creating temporal databases with the command-line
To create a temporal database, set DATA_VERSION_RETENTION_TIME to a nonzero value.
For detailed syntax and privileges, see the CREATE DATABASE command.
CREATE DATABASE <db_name> DATA_VERSION_RETENTION_TIME <number-of-days>;
Example:
CREATE DATABASE DB1 DATA_VERSION_RETENTION_TIME 30;
Creating time travel objects with the web console
Creating temporal tables with the web console
- Log in to the web console as described in Getting started with the web console.
- Create a temporal table as described in Creating tables.
You must set retention time interval to a nonzero value.
Databases, schemas, and table names containing a dot character (".") do not show in the time travel statistics and graphs when you set the retention time interval to a nonzero value. When you do not set the retention time interval, all special characters are supported.
When you insert a row into the table, the row receives a virtual insert timestamp that is equal to the commit time of the inserting transaction.
When you delete a row from the table, the row receives a virtual delete timestamp that is equal to the commit time of the deleting (or truncating) transaction.
Creating temporal schemas with the web console
To create a temporal schema, set retention time interval to a nonzero value.
- Log in to the web console as described in Getting started with the web console.
- Create a temporal schema as described in Creating schemas.
Creating temporal databases with the command-line
To create a temporal database, set retention time interval to a nonzero value.
- Log in to the web console as described in Getting started with the web console.
- Create a temporal database as described in Creating databases.
Altering time travel objects with the command-line
Altering temporal tables to nontemporal with the command-line
To alter a temporal table to nontemporal, set DATA_VERSION_RETENTION_TIME to 0.
For detailed syntax and the necessary privileges, see the ALTER TABLE command.
ALTER TABLE <table> DATA_VERSION_RETENTION_TIME 0;
Example:
ALTER TABLE PRODUCT DATA_VERSION_RETENTION_TIME 0;
When you set DATA_VERSION_RETENTION_TIME to 0, you cannot run time travel queries and you do not have access to historical rows for that table anymore. You can reclaim some or all of the now inaccessible historical rows in the table with GROOM TABLE.
Altering nontemporal tables to temporal with the command-line
To alter a nontemporal table to temporal, set DATA_VERSION_RETENTION_TIME] to a nonzero value.
For detailed syntax and the necessary privileges, see the ALTER TABLE command.
ALTER TABLE <table> DATA_VERSION_RETENTION_TIME <number-of-days>;
Example:
ALTER TABLE PRODUCT DATA_VERSION_RETENTION_TIME 30;
If you first disabled your temporal table and then converted the same table to a temporal table, you do not have access to the prior historical rows for that table. Historical data is collected when rows are deleted or updated after the table is converted to temporal.
As with the CREATE TABLE command, a row that is inserted into the table receives a virtual insert timestamp that is equal to the commit time of the inserting transaction. A row that is deleted from the table receives a virtual delete timestamp that is equal to the commit time of the deleting (or truncating) transaction. The table’s retention lower bound and retention start time are equal to or just before the commit time of this ALTER TABLE transaction.
Unlike the CREATE TABLE command, which does not have any existing rows, existing visible rows in the table are treated as if they were inserted by this ALTER TABLE transaction. The existing visible rows receive virtual insert timestamps that are equal to the retention start time. With these timestamps, the rows are potentially visible to time travel queries.
Altering temporal schemas to nontemporal with the command-line
To alter a temporal schema to nontemporal, set DATA_VERSION_RETENTION_TIME to 0.
For detailed syntax, the necessary privileges, and the CASCADE option, see the ALTER SCHEMA command.
ALTER SCHEMA <schema_name> DATA_VERSION_RETENTION_TIME 0 NOCASCADE;
Example:
ALTER SCHEMA SCHEMA DATA_VERSION_RETENTION_TIME 0 NOCASCADE;
Altering nontemporal schemas to temporal with the command-line
To alter a nontemporal schema to temporal, set DATA_VERSION_RETENTION_TIME to a nonzero value.
For detailed syntax, the necessary privileges, and the CASCADE option, see the ALTER SCHEMA command.
ALTER SCHEMA <schema_name> DATA_VERSION_RETENTION_TIME <number-of-days> NOCASCADE;
Example:
ALTER SCHEMA DB1 DATA_VERSION_RETENTION_TIME 30 NOCASCADE;
Altering temporal databases to nontemporal with the command-line
To alter a temporal database to nontemporal, set DATA_VERSION_RETENTION_TIME to 0.
For detailed syntax and the necessary privileges, see the ALTER DATABASE command.
ALTER DATABASE <db_name> DATA_VERSION_RETENTION_TIME 0 NOCASCADE;
Example:
ALTER DATABASE DB1 DATA_VERSION_RETENTION_TIME 0 NOCASCADE;
Altering nontemporal databases to temporal with the command-line
To alter a nontemporal database to temporal, set DATA_VERSION_RETENTION_TIME to a nonzero value.
For detailed syntax and the necessary privileges, see the ALTER DATABASE command.
ALTER DATABASE <db_name> DATA_VERSION_RETENTION_TIME <number-of-days> NOCASCADE;
Example:
ALTER DATABASE DB1 DATA_VERSION_RETENTION_TIME 30 NOCASCADE;
Altering time travel objects with the web console
Altering temporal tables to nontemporal with the web console
To alter a temporal table to nontemporal, set retention time interval to 0.
- Log in to the web console as described in Getting started with the web console.
- Alter your table as described in Updating retention time interval for tables.
When you set retention time interval to 0, you cannot run time travel queries and you do not have access to historical rows for that table anymore. You can reclaim some or all of the now inaccessible historical rows in the table with GROOM TABLE.
Altering nontemporal tables to temporal with the web console
To alter a nontemporal table to temporal, set retention time interval to a nonzero value.
- Log in to the web console as described in Getting started with the web console.
- Alter your table as described in Updating retention time interval for tables.
If you first disabled your temporal table and then converted the same table to a temporal table, you do not have access to the prior historical rows for that table. Historical data is collected when rows are deleted or updated after the table is converted to temporal.
Similarly to when you create a table, a row that is inserted into the table receives a virtual insert timestamp that is equal to the commit time of the inserting transaction. A row that is deleted from the table receives a virtual delete timestamp that is equal to the commit time of the deleting (or truncating) transaction. The table’s retention lower bound and retention start time are equal to or just before the commit time of this altering transaction.
Unlike when you create a table, existing visible rows in the table are treated as if they were inserted by this altering transaction. The existing visible rows receive virtual insert timestamps that are equal to the retention start time. With these timestamps, the rows are potentially visible to time travel queries.
Altering temporal schemas to nontemporal with the web console
To alter a temporal schema to nontemporal, set retention time interval to 0.
- Log in to the web console as described in Getting started with the web console.
- Alter your table as described in Updating retention time interval for schemas.
Altering nontemporal schemas to temporal with the web console
To alter a nontemporal schema to temporal, set retention time interval to a nonzero value.
- Log in to the web console as described in Getting started with the web console.
- Alter your table as described in Updating retention time interval for schemas.
Altering temporal databases to nontemporal with the web console
To alter a temporal database to nontemporal, set retention time interval to 0.
- Log in to the web console as described in Getting started with the web console.
- Alter your table as described in Updating retention time interval for databases.
Altering nontemporal databases to temporal with the web console
To alter a nontemporal database to temporal, set retention time interval to a nonzero value.
- Log in to the web console as described in Getting started with the web console.
- Alter your table as described in Updating retention time interval for databases.
What to do next
After you created time travel objects, you can start running time travel queries. For more information, see the following links: