16.20 - Activating Permanent Journaling - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Data Definition Language Detailed Topics

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Release Date
March 2019
Content Type
Programming Reference
Publication ID
B035-1184-162K
Language
English (United States)

If a table is created in a database that does not have permanent journaling activated, you can activate its journaling with ALTER TABLE. To do this, you must alter the table to include a journaling option such as AFTER JOURNAL or BEFORE JOURNAL or both. As soon as the ALTER TABLE request completes successfully, permanent journaling is activated for that table.

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.JournalsV
    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;

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