Journaling | ALTER TABLE | VantageCloud Lake - Types of Journaling Supported by ALTER TABLE (Basic Table Parameters) - Teradata VantageCloud Lake

Local Journaling

See Example: Local Journaling for information about local journaling.

Transient Journaling

Transient journaling supports rollback and backup 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.

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 with a journaling option such as AFTER JOURNAL or BEFORE JOURNAL. When the ALTER TABLE request completes, permanent journaling is activated for that table.

To determine which tables in your database are journal tables, use the following query.

SELECT DBC.dbase.databasename (FORMAT 'X(15)'),
       DBC.tvm.tvmname (FORMAT 'X(25)')
FROM DBC.tvm,DBC.dbase
WHERE DBC.dbase.databaseid=DBC.tvm.databaseid
AND   DBC.tvm.tablekind='j'
ORDER BY 1,2;

To determine which databases and users in your database have a default journal table defined for them, use the following query.

SELECT d.databasename (TITLE'Database'), TRIM(dx.databasename)
       ||'.'||TRIM(t.tvmname)(TITLE 'Journal')
FROM DBC.dbase AS d,
     DBC.TVM   AS t,
     DBC.dbase AS dx
WHERE d.journalid IS NOT NULL
AND   d.journalid <> '00'xb
AND   d.journalid = t.tvmid
AND   t.databaseid = dx.databaseid
ORDER BY 1;

To determine which tables in your database are being journaled, use the following query.

SELECT TRIM(Tables_DB)||'.'||TableName (TITLE 'Table',
       CHARACTER(26)),'Assigned To' (TITLE ' '),TRIM(journals_db)
       ||'.'||JournalName (TITLE 'Journals', CHARACTER(26))
FROM DBC.JournalsV
ORDER BY 1,2;

Alternatively, use the following query:

SELECT TRIM(d.databasename)||'.'||TRIM(t.tvmname) (FORMAT
      'x(45)',TITLE 'Table'),TRIM(dj.databasename)
       ||'.'||TRIM(tj.tvmname) (TITLE 'Journal')
FROM DBC.TVM   AS t,
     DBC.TVM   AS tj,
     DBC.dbase AS d,
     DBC.dbase AS dj
WHERE t.journalid IS NOT NULL
AND   t.journalid <> '00'xb
AND   t.journalid = tj.tvmid
AND   d.databaseid = t.databaseid
AND   dj.databaseid = tj.databaseid
ORDER BY 1;

Also see CREATE DATABASE, CREATE USER, MODIFY DATABASE, and MODIFY USER.