Teradata Permanent Journal

 

teradata permanent journal

The Teradata Permanent Journal allows us to keep a snapshot of permanent tables before and after applying changes. Copies of the original rows, before any change took place, are stored in the permanent journal table. Permanent journaling is similar to the transient journal table, which stores before snapshots of tables whenever a transaction is initiated.

Journaling can be applied either on table level or database level. Even with database level journaling turned on, it is possible to turn off journaling for each table separately, by overriding the default value in the table DDL statement.

For each database, exactly one permanent journal can be used.

Permanent journaling allows you to move back in time, giving you a “UNDO” functionality.

Permanent journals are continuously monitoring tables changes, tracking all UPDATE, INSERT and DELETE statements; They are protecting us from data losses, by logging changes, until the journal tables are manually dropped (pausing the usage of journaling is possible was well). Permanent journaling can be used to avoid or postpone full backups.

Teradata allows us to keep one or two journal rows per changed table record, similar to the FALLBACK protection feature.

While permanent journaling adds some level of protection to your data, you have to consider that journal tables need the same amount of space per row like the underlying permanent table.

You should decide on a case by case base, which ones should store permanent journal tables.

Here is an example of a table which is using a fallback protected after journal:

CREATE SET TABLE <DATABASE>.<TABLE>
,FALLBACK ,
NO BEFORE JOURNAL,
AFTER JOURNAL,
WITH JOURNAL TABLE = <OTHER_DATABASE>.<MYJOURNAL> ,
CHECKSUM = DEFAULT
(
PK INTEGER NOT NULL
) PRIMARY INDEX ( PK );

You can list all available journal tables in the system with the statement below:

SELECT * FROM DBC.JOURNALS;

Roland Wenzlofsky
 

Roland Wenzlofsky is a graduated computer scientist and Data Warehouse professional working with the Teradata database system for more than 15 years. He is experienced in the fields of banking and telecommunication with a strong focus on performance optimization.

>