Correct Use Of The Teradata Permanent Journal

Roland Wenzlofsky

May 27, 2014

minutes reading time


The Teradata Permanent Journal allows us to keep a snapshot of permanent tables before and after applying changes. Before any change occurs, copies of the original rows 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.

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

For each database, we can use precisely one permanent journal.

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

Permanent journals continuously monitor table changes, tracking all UPDATE, INSERT and DELETE statements; they protect us from data losses by logging changes until the journal tables are manually dropped (pausing the usage of journaling is possible as well). We can use permanent journaling 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 protection level to your data, you must consider that journal tables need the same amount of space per row as the underlying permanent table.

It would be best if you decided on a case-by-case basis that should store permanent journal tables.

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 );

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

SELECT * FROM DBC.JOURNALS;

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

You might also like

>