Teradata Journals - Maintains database consistency

What are the types of Teradata journals?

This is often asked in interviews. As it happened, the interviewer asked me


"How many journals are there in Teradata? How many have you used?"
Well, the answer is:


For the User = 1
For the System = 2


 Permanent journal - This journal is available to the user. It maintains complete history of the table
The journal is specified along with the DDL


For example:


CREATE TABLE TA_62917_QRMDB.VERSION_HEAD
,NO FALLBACK
,BEFORE JOURNAL
,DUAL AFTER JOURNAL
(
VERSION_ID INTEGER
, FLOWNAME VARCHAR(20)
,LOAD_DATE DATE FORMAT 'YYYY-MM-DD'
)
PRIMARY INDEX(VERSION_ID)
;


The following are the feature of Permanent Journals:


Continuously Active
Used for both tables or databases
Provides rollforward for hardware failure recovery
Provides rollback for software failure recovery
Provides full recovery of nonfallback tables
Reduces need for frequent, full-table archives




Down AMP recovery journal - This is available to the System automatically. This journal stores all entries of the down AMP. Once the AMP comes back online, it copies all the missed entries. Once the catch-up is completed, the DARJ ( also called the Recovery Journal) is dropped.

The following are the features of Recovery Journal:



It is active during an AMP failure only
It includes journals FALLBACK tables only
The journal is discarded after the down AMP recovers

Transient journal - This is also a System Journal. It maintains history of the current transaction.

Functions of the Transient journal are:



Logs BEFORE images for all transactions
Is used by system to roll back failed transactions aborted either by the user or by the system
Captures:

– BT/ET images for all transactions
– Before images for updates and deletes
– Row IDs for inserts
– Control records for creates and drops
-Rollback in case parallel queries failed