Activating Permanent Journaling - Analytics Database - Teradata Vantage

SQL Data Definition Language Detailed Topics

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2023-07-11
dita:mapPath
vuk1628111288877.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
B035-1184
lifecycle
latest
Product Category
Teradata Vantage™

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 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 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 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 database 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.