Permanent Journal in Teradata

The permanent journal in Teradata provides the capability to capture the snapshot of  the  table/tables before and after applying changes using DML statements like insert, delete and update. Unlike transient journal which capture the snapshot of table before applying  changes and discards upon commit, it stores in journal table permanently and required Teradata perm space until you drop it explicitly.

Advantages of permanent journal:-

Permanent journal protects user data against below-

• loss of data due to disk failure in a table that is not fall back or RAID protected.
• loss of data because of 2 or more AMPs fail in the same cluster.
• Incorrect batch or application processing.

You can define permanent journal in database level or table level. The single journal table will be maintained in one database or user space. Once you define permanent journal for table/tables and start doing operation like insert, delete and update on the table, permanent journal  will start to grow and will take addition space. So it’s the responsibility of DBA to maintain permanent journal by deleting, archiving and so on. During creation of permanent journal of table you can specify to capture table snapshot as below-

Before journal / before image:- Snapshot will be captured before applying any changes on table, used for rollback or undo the changes. You can choose snapshot of the table as single copy/dual copy or no copy.

After journal/after image:- Snapshot will be captured after applying any changes on table, used for rollin or redo the changes. You can choose snapshot of the table as single copy/dual copy/no copy or local copy which will be created on same AMP. First you need to create one journal table either on same database or any other database or user space as below:-

CREATE DATABASE "Mydb" FROM "TESTDB"
AS PERM=10737418240
NO FALLBACK
NO BEFORE JOURNAL
AFTER JOURNAL
DEFAULT JOURNAL TABLE="Mydb"."Myjournal";

Here we opted for the after journal. You can override this in table level by specifying “NO AFTER JOURNAL”.

Syntax to define Permanent journal on table level:-

Create Set Table "MyDB"."My_Table"
, No Fallback
, No Before Journal
, After Journal
, With Journal Table = "MyDB"."journals"
("COLUMN1" CHAR
,"COLUMN2? CHAR
) INDEX  ("COLUMN1") INDEX  ("COLUMN1");

From the above example we can see that after image of  “MyDB”.”My_Table” will be captured in “MyDB”.”Myjournals” journal table.

Delete permanent journal table syntax:-

If you think that permanent journal is no longer required, you can drop that after modifying table and database properties.

ALTER TABLE Mydb.Tablename
,NO BEFORE JOURNAL
,NO AFTER JOURNAL;

MODIFY USER Mydb.Tablename AS
no after journal;

MODIFY USER Mydb.Tablename AS
DROP DEFAULT JOURNAL table= Mydb.journals;

You can check journal tables in the whole system using below query-

SELECT * FROM DBC.JOURNALS;