In this article, we will see Transient Journaling in Teradata
Journals are kept on the system to provide data availability in the event of a component or process failure in the system:
- Recovery journals
- Permanent journals
- The Teradata Database uses Recovery Journals to automatically maintain data integrity in the case of:
- An interrupted transaction (Transient Journal)
- An AMP failure (Down-AMP Recovery Journal)
- Recovery Journals are created, maintained, and purged by the system automatically, so no DBA intervention is required.
- Recovery Journals are tables stored on disk arrays like user data is, so they take up disk space on the system.
- Transient journal is the mechanism to capture before image of data during transaction processing. It helps to roll back to the previous state of data if transaction fails for any reason.
- A Transient Journal maintains data integrity when in-flight transactions are interrupted (due to aborted transactions, system restarts, and so on). Data is returned to its original state after transaction failure.
- A Transient Journal is used during normal system operation to keep before images of changed rows so the data can be restored to its previous state if the transaction is not completed. This happens on each AMP as changes occur.
- When a transaction is started, the system automatically stores a copy of all the rows affected by the transaction in the Transient Journal until the transaction is committed (completed). Once the transaction is completed, the “before images” are purged for the particular transaction from the transient journal log.
- In the event of a transaction failure, the “before images” are re-applied to the affected tables and deleted from the journal, and the “rollback” operation is completed.
- The transient journal is a system‑maintained dictionary table that provides a way to protect transactions from various system failures and from deadlock.
- Transient journaling supports rollback and back up operations.
- To preserve the restore capability of transient journaling, changing only journaling options with ALTER TABLE retains the version number of the table. Other ALTER TABLE options change the structure of tables and thus require incrementing the table version number.
- The transient journaling options include.
- WITH JOURNAL TABLE
- AFTER JOURNAL
LOG and NO LOG
Global temporary and volatile tables permit you to define whether their activity is logged to the transient journal. While the NO LOG option reduces the system overhead of logging, it is also true that table modifications are lost and cannot be recovered upon an aborted SQL request.
Working of TJ
- Before Teradata changes a data row (update, insert or delete statement), it takes a backup copy of the entire row.
- Each AMP manages its local transient journal. Backup rows are always kept together with the base table row on the same AMP.
- The decision about which row belongs to each AMP is built on the hashing algorithm Teradata used to distribute rows evenly.
- The one and only task of the transient journal is to allow Teradata to roll back failed transactions.
- In the case of a rollback, the base table row can be quickly replaced with the backup row from the transient journal.
- The transient journal cannot be turned off manually.This is reasonable because database integrity has to be ensured. But sometimes we want to avoid the usage of it for performancereasons, especially when doing a lot of changes on a table (such as updating billions of rows), the transient journal can become huge.
- Because it’s stored in the DBC database, we could run out of space. Running out of space would have significant implications on the existing workload (failing sessions, etc.)
- Another nasty side effect of the transient journal shows up when updating many rows in an enormous and skewed table. The rollback usually consumes a lot of resources and might have an adverse impact on the overall system performance.
- Remember, the transient journal is AMP-local: For any rollback executed on a skewed table, the AMPsholding the majority of the rows will have to do most of the work. Of course, above described issues also apply to DELETE statements.
- Luckily, there are several techniques to avoid the usage of the transient journal in such a situation described above.
- As we said: The only reason for the system to write into the transient journal is to be able to rollback failed (or manually aborted) transactions.
- But some transactions can be rolled back quickly without keeping a backup copy of rows and should be preferred.
How to find out amount of Transient Journal(TJ) per session?
- TransientJournal table contains “before-image” and “after-image” of each changed row, which are used to perform rollback and rollforward and maintain data consistency.
- There is no way to check size of TJ per session.
- TransientTable is a special table which you cannot access via SQL.
- Also, culprit session of large TJ does not always have large TJ records.
- TJ has FIFO structure and newer TJ records cannot be purged until older TJ records are purged.It means large TJ can happen if One session keeps only a few TJ records for long time.
- Therefore, finding session with many TJ records does not help identify the cause of large TJ issue.
- 3869 failure is returned when you try to access DBC.TransientJournal table via SQL.
3869 A system journal table cannot be accessed via Teradata SQL.
Explanation: There are 10 system journal tables:
DBC.TransientJournal ChangedRowJournal LocalTransactionStatusTable UtilityLockJournalTable LocalSessionStatusTable
SysRcvStatJournal SavedTransactionStatusTable OrdSysChngTable RecoveryLockTable RecoveryPJTable
and 1 DBC table:
These system journal tables and DBC.ALL are different from regular tables and they are for internal processing only. They
cannot be accessed via Teradata SQL.
SQL to find AMP# and its TJ size
SELECT TOP 10 d.vproc
,d.currentperm AS DBCCurrentPerm
,t.currentperm AS TJCurrentPerm
FROM dbc.diskspace d
WHERE d.databasename = t.databasename
AND d.vproc = t.vproc
AND Upper(d.databasename) = 'DBC'
AND Upper(tablename) = 'TRANSIENTJOURNAL'
ORDER BY DBCCurrentPerm DESC