15.00 - Restoring a Prior Table State - Teradata Database

Teradata Database Temporal Table Support

prodname
Teradata Database
vrm_release
15.00
category
Programming Reference
featnum
B035-1182-015K

Restoring a Prior Table State

Tables with transaction time automatically store in the table a snapshot copy of any row that is modified or deleted. This characteristic of these tables can be used to recover a prior state of the table using only SQL. This can be useful to quickly recover from a localized problem, such as if a table or set of tables have become corrupted by a user error, and need to be restored to a consistent state.

Assume a table that has transaction time needs to be restored to the state it was in at a point (time X) prior to the current time. Rows in the table can be classified based on whether they are open or closed, and on their transaction-time column period relation to time X:

 

Row

Row State

BEGIN(TT)
Beginning Transaction Time

END(TT)
End Transaction Time

1

Open

Prior to time X

UNTIL_CLOSED

2

Open

Equal to time X

UNTIL_CLOSED

3

Open

After time X

UNTIL_CLOSED

4

Closed

Prior to time X

Prior to time X

5

Closed

Prior to time X

Equal to time X

6

Closed

Prior to time X

After time X

7

Closed

Equal to time X

After time X

8

Closed

After time X

After time X

These rows are represented graphically below.

Example  

To return the table back to the state it was in at time x, use the following plan:

 

Row

Row at Time X
Compared to Row Now

Plan

1

Row existed at time X and row exists now.

Leave the row as it is.

2

It existed at time X and row exists now.

Leave the row as it is.

3

It did not exist at time X.

Delete the row.

4

It was closed at time X, and is still closed now.

Leave the row as it is.

5

It was closed at time X, and is still closed now.

Leave the row as it is.

6

It was open at time X, but is closed now.

Leave BEGIN(TT) as it is.
Update END(TT) to UNTIL_CLOSED.

7

It was open at time X, but row is closed now.

Leave BEGIN(TT) as it is.
Update END(TT) to UNTIL_CLOSED.

8

It did not yet exist at time X.

Delete the row.

The following SQL will realize the plan for each of the different row states.

NONTEMPORAL DELETE tt_table where BEGIN(tt_col) > time X;
NONTEMPORAL UPDATE tt_table 
  SET tt_col = PERIOD(BEGIN(tt_col), UNTIL_CLOSED)
  WHERE END(tt_col) IS NOT UNTIL_CLOSED
  AND   BEGIN(tt_col) <= time X
  AND   END(tt_col) > time X;

Note: Because this solution requires NONTEMPORAL SQL, nontemporal operations on temporal tables must be enabled using the DBS Control utility. Additionally, the user executing this SQL must be granted the NONTEMPORAL privilege. For more information on DBS Control see Utilities. For more information on NONTEMPORAL operations, see “NONTEMPORAL Privilege” on page 181.