DWHPro Insights

Teradata Transient Journal: How It Protects Transactions Against Failures and Deadlocks

By Roland Wenzlofsky ·

Teradata Transient Journal: How It Protects Transactions Against Failures and Deadlocks

Transient journal records support rollback, but their size and form depend on the operation and optimizations. Do not assume equal space or a full before-image for every affected row; physical journal storage was not measured here.

Users do not have access to the transient journal, which is located in the DBC database. Ensuring sufficient permanent space in the DBC database is essential to prevent system-level errors.

Table rows and their associated transient journal rows are consistently located on the same AMP. The distribution of these rows follows the customary hashing algorithm.

The transient journal facilitates the rollback of changes in the event of an error or manual transaction abortion. It writes altered rows back to the base table, ensuring data integrity. Such a feature is indispensable in a relational database system like Teradata.

The utilization of the transient journal has a negative impact on performance. Teradata, however, employs techniques to mitigate journaling and circumvent the row-by-row logging mechanism.

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Allow recovery to complete under a documented DBA procedure. Forced rollback cancellation can invalidate a table and is not a routine way to recover performance.

Teradata provides methods to circumvent the transient journal or store only a single row in it.

There are situations where a row-by-row rollback is unnecessary. Here, I will present techniques that can enhance efficiency and prevent problems.

INSERT into an empty table.

The separate INSERT requests below can have different journaling costs as the target changes from empty to populated. The first request is eligible for an empty-target optimization only when all documented prerequisites are satisfied; the syntax alone does not establish its journal behavior.

INSERT INTO target SELECT col FROM table1;
INSERT INTO target SELECT col FROM table2;

An empty-target INSERT SELECT may qualify for fast-path processing under documented restrictions. Neither UNION ALL nor BTEQ request grouping alone guarantees no row-level journaling. Verify table state, eligibility and recovery behavior.

The table-copy/DROP/RENAME excerpt is an incomplete recovery-sensitive example: replacement changes object identity, grants and dependencies. Plan concurrent access, transaction boundaries and recovery before applying it.

INSERT INTO target SELECT col FROM table1 UNION ALL SELECT col FROM table2;

Replace UPDATE with INSERT INTO … SELECT into a copy of the target table

It is advisable to refrain from using UPDATE statements on heavily skewed tables because they are not optimized for Teradata’s parallel architecture and tend to have poor performance.

UPDATE target SET col2 = 1; -- assumption: col2 is skewed
INSERT INTO copy_of_target SELECT col1, 1 AS col2 FROM target;
DROP TABLE target;
RENAME TABLE copy_of_target TO target;

A BTEQ trick

INSERT INTO target SELECT * FROM table1  -- The semicolon starts on the following line!
; INSERT INTO target SELECT * FROM table2; -- Final semicolon terminates this BTEQ request.

Load Utilities and the Teradata Transient Journal

Bulk load utilities can be effective for large inputs, but relative throughput depends on target restrictions, batching, concurrency and recovery requirements. Compare complete workloads instead of assuming every bulk load outperforms a transactional load.

Validation scope: Explicit BT/ET failure rolled back the preceding DELETE in the isolated fixture; separate implicit requests left the committed DELETE in place. Physical journal records and interruption of recovery were not tested. Fast-path empty-target exceptions require their own eligibility checks.

Deciding what to change in your data platform?

Compare optimisation, modernisation and migration against your workload and business requirements.

Explore independent platform assessments

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Follow DWHPro in Google to see our articles more often in Search.

3 thoughts on “Teradata Transient Journal: How It Protects Transactions Against Failures and Deadlocks”

  1. Valuable update on TJ….

    Can we manually purge the TJ, there is no/less activity but it is consuming more than 2.5TB of space

    Regards
    Ashok

    Reply
  2. Hi Roland,

    I suspect that a MERGE statement is also lower in TJ or at least more efficient. The orange book seems to hint at that, but only compares multi-load apply and ANSI merge.

    Regards, Johannes

    Reply

Leave a Comment

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Protected by Security by CleanTalk and CleanTalk Anti-Spam