Teradata Permanent Journaling: Snapshot Your Tables Before and After Changes

The Teradata Permanent Journal captures permanent table snapshots pre- and post-modifications. Prior to any changes, the original row duplicates are saved in the permanent journal table. This mirrors the behavior of the transient journal table, which stores BEFORE images of tables when a transaction begins.

Journaling can be implemented at the table or database level. Even if database-level journaling is enabled, individual tables can have journaling disabled by setting a different value in the table’s DDL statement.

One permanent journal can be utilized for every database.

Permanent journaling enables you to revisit the past and utilize an “UNDO” option.

Permanent journals constantly monitor table alterations by tracking all UPDATE, INSERT, and DELETE statements. They safeguard against data loss by logging changes until the journal tables are manually dropped (pausing journaling is also feasible). Utilizing permanent journaling enables us to circumvent or delay full backups.

Teradata enables us to maintain one or two journal entries for each modified table record, similar to the FALLBACK protection attribute.

Although permanent journaling offers some data protection, it is important to note that journal tables require the same amount of space per row as the underlying permanent table.

Determine on a case-by-case basis which tables should be used to store permanent journal records.

Here is an example of a table that 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 );

Use the following statement to retrieve a comprehensive list of journal tables that are currently accessible within the system:

SELECT * FROM DBC.JOURNALS;