Activating Permanent Journaling - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Detailed Topics

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-24
dita:mapPath
jpx1556733107962.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1184
lifecycle
previous
Product Category
Teradata Vantage™

If you specify only DEFAULT JOURNAL TABLE = table_name, then the system creates a journal table, but does not activate it.

To activate the permanent journal, you must also specify either the AFTER JOURNAL journaling option or the BEFORE JOURNAL option or both.

This action causes permanent journaling to be activated for all tables created in this database afterward.

To determine which tables in your Teradata 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 Teradata Database currently 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 Teradata Database are currently 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.journals
    ORDER BY 1,2;

You can also determine which tables in your system are currently being journaled using the following query that has a somewhat different syntax:

    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;