The Teradata Transient Journal

The Teradata Transient Journal is a dictionary table maintained across the system to protect transactions against system failures and deadlocks.

Each AMP manages its own Transient Journal, where each table row is copied before being changed by a transaction. Should one transaction fail to complete (i.e., aborts), the Transient Journal is used to roll back and restore the contents of the affected table rows.

The rows changed by a transaction are removed from the Transient Journal when the transaction completes. Despite being short-lived compared to a Permanent Journal, the Transient Journal can significantly affect performance:

A transaction causing an update on billions of rows requires an equal space for journal entries. The Transient Journal cannot be manually disabled, or it would not be possible to ensure the ACID properties of a transaction.

As a user, we have no access to the transient journal. The table is located in the DBC database. But we must ensure that there is always enough permanent space in the DBC database to avoid system-level errors.

Base table rows are always with their transient journal rows at the same AMP. The distribution of the rows is done as usual by the hashing algorithm.

In case of an error or if a transaction is aborted manually, the AMP uses the transient journal to roll back the changes. All changed rows are copied from the transient journal to the base table. This is an essential function of any relational database system. Without a transient journal, Teradata would not guarantee data integrity.

The use of the transient journal negatively impacts performance. Therefore, Teradata uses techniques to minimize journaling and bypass the row-by-row logging mechanism.

Especially when we process tables with many rows (with DML statements such as UPDATE, DELETE, INSERT), the transient journal’s maintenance can negatively affect the performance because each changed row is also stored in the journal table, which doubles the workload.

Since the transient journal is stored in the DBC database, we risk filling up all the permanent space. All workloads relying on the transient journal will be aborted, and errors will be returned to the clients.

The impact of a skewed base table is multiplied by the Transient Journal since it will also be skewed. If e.g., the loading into a skewed table fails, the rollback typically takes a very long time and can only be terminated by force (this often leaves an inconsistent table). Remember, the transient journal is AMP-local: For any rollback on a skewed table, the AMPs holding the most rows must do most of the work.

Teradata offers techniques that bypass the transient journal, or only store one row.

There are scenarios where a rollback row by row is not needed. In the following, I will show you some tricks you can use to improve performance and avoid issues.

INSERT into an empty table.

Here is a simple example of how slightly changing a query can avoid the usage of the transient journal. First, we write a statement that needs journaling and logs the changes row by row. The first of two INSERT INTO statements will not use row-level journaling, but the second INSERT INTO statement must use it:

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

If we want to avoid the usage of the transient journal for both INSERT INTO statements, we can achieve this by rewriting the query as below, using a UNION:

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

We should avoid UPDATE statements on large skewed tables, as these perform poorly. They do not make good use of Teradata’s parallel architecture.

UPDATE target SET col2 = 1; -- assumption: col2 is skewed

The method below is better because the transient journal is bypassed, and a long running(and possibly long-lasting) rollback can be avoided. The transient journal is not involved because we write into an empty table (only one row for the rollback is used to store the fact that the table can be entirely deleted in case of a rollback):

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

BTEQ allows us to avoid the transient journal if we put a semicolon for multiple INSERT statements into the same table at the beginning of each line of the INSERT INTO statement, as shown below:

INSERT INTO target SELECT * FROM table1  -- The semicolon starts on the following line!
; INSERT INTO target SELECT * FROM table2 -- The semicolon has to be the first character!

Load Utilities and the Teradata Transient Journal

All Teradata bulk load utilities (TPT Bulk Load, Fastload, Multiload) do not use the transient journal and are better for loading huge amounts of data than transactional loads such as BTEQ or TPump.