fbpx

The Teradata Permanent Journal allows us to keep a snapshot of permanent tables before and after applying changes. Before any change took place, copies of the original rows are stored in the permanent journal table. Permanent journaling is similar to the transient journal table, which stores before snapshots of tables whenever a transaction is initiated.

Journaling can be applied either on the table level or database level. Even with database-level journaling turned on, it is possible to separately turn off journaling for each table by overriding the default value in the table DDL statement.

For each database, precisely one permanent journal can be used.

Permanent journaling allows you to move back in time, giving you an “UNDO” functionality.

Permanent journals are continuously monitoring tables changes, tracking all UPDATE, INSERT and DELETE statements; They are protecting us from data losses by logging changes until the journal tables are manually dropped (pausing the usage of journaling is possible as 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, similar to the FALLBACK protection feature.

While permanent journaling adds some protection level to your data, you have to consider that journal tables need the same amount of space per row, like the underlying permanent table.

It would be best if you decided on a case-by-case basis that should store permanent journal tables.

Here is an example of a table which is using a fallback protected after journal:

CREATE SET TABLE <DATABASE>.<TABLE>
,FALLBACK ,
NO BEFORE JOURNAL,
AFTER JOURNAL,
WITH JOURNAL TABLE = <OTHER_DATABASE>.<MYJOURNAL> ,
CHECKSUM = DEFAULT
(
PK INTEGER NOT NULL
) PRIMARY INDEX ( PK );

You can list all available journal tables in the system with the statement below:

SELECT * FROM DBC.JOURNALS;

__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"62516":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"62516":{"val":"var(--tcb-skin-color-0)"}},"gradients":[]},"original":{"colors":{"62516":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"b4fbe":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"b4fbe":{"val":"rgb(241, 99, 52)"}},"gradients":[]},"original":{"colors":{"b4fbe":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
Previous Article
__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"b4fbe":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"b4fbe":{"val":"rgb(241, 99, 52)"}},"gradients":[]},"original":{"colors":{"b4fbe":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
Next Article
Buy the Book Teradata Query Performance Tuning

Roland Wenzlofsky

Roland Wenzlofsky is an experienced freelance Teradata Consultant & Performance Trainer. Born in Austria's capital Vienna, he is building and tuning some of the largest Teradata Data Warehouses in the European financial and telecommunication sectors for more than 20 years. He has played all the roles of developer, designer, business analyst, and project manager. Therefore, he knows like no other the problems and obstacles that make many data warehouse projects fail and all the tricks and tips that will help you succeed.

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

You might also like

>