Share via:


Dear Readers,

In this article, we will learn about PERMANENT JOURNAL IN TERADATA

  • The permanent journal in Teradata provides the capability to capture the snapshot of  permanent tables before and after applying changes using DML statements like insert, delete and update. Copies of the original rows, before any change took place, are stored in the permanent journal table.
  • Snapshot means a sequential history of all changes made to the rows of one or more tables , unlike Transient journal which captures 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.
  • 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 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, like the FALLBACK protection feature.
  • While permanent journaling adds some level of protection to your data, you must 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:

Permanent journaling involves keeping an audit trail of all new, deleted or changed data.

For example, if a table has one million rows and is fallback protected then there are one million fallback rows. If a Permanent Journal is used instead of FALLBACK, the only rows copied to the journal are for the inserts, updates, and deletes since the last JOURNAL backup. Therefore, in most cases this is far less expensive than storing the additional one million rows needed for fallback.

Permanent journal can be used up with fallback.

By default, AFTER JOURNAL is available for him to protect his data when the hardware failure occurs.

Default option : In this case, by default the table has AFTER JOURNAL option.

Explicit option : explicitly user defines NO AFTER JOURNAL.

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.
  • 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 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:

Here we have chosen after journal. You can override this in table level by specifying “NO AFTER JOURNAL”.

Syntax to define Permanent journal on table level:

Activating Permanent Journaling

If a table is created in a database that does not have permanent journaling activated, you can activate its journaling with ALTER TABLE. To do this, you must alter the table to include a journaling option such as AFTER JOURNAL or BEFORE JOURNAL or both.

As soon as the ALTER TABLE request completes successfully, permanent journaling is activated for that table.

To list all the available journal tables in the system, use the below query :

To determine which tables in your Teradata Database are journal tables, use the following query :


Thank you for giving your valuable time to read the above information.
Follow us on 
Facebook Page KTExperts Facebook
Linkedin Page : KT EXPERTS Linkedin

Share via:
Note: Please test scripts in Non Prod before trying in Production.
1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)

Add Comment