Permanent Journal creation at Table Level

General

Permanent Journal creation at Table Level

I am trying to enable permamnet journal for my table.PFB the table structure I am using and I am getting error message Database doesnot have a permanent journal.

CREATE SET TABLE pp_tables_d.test_1802 ,NO FALLBACK ,

NO BEFORE JOURNAL,
AFTER JOURNAL,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO
(
id INTEGER,
name VARCHAR(40) CHARACTER SET LATIN NOT CASESPECIFIC,
amt INTEGER)
PRIMARY INDEX ( id );

1. If we want to enable permanent journal then do we need to create a database with permanent journal option. 

2. if we didnt enable permanent journal option while creating a database, can we enable at table level.

3. please share syntax to enable PJ at Table level.

Thanks,

Muthu.

2 REPLIES
Teradata Employee

Re: Permanent Journal creation at Table Level

1. You must create a PJ using CREATE DATABASE or MODIFY DATABASE before you can name it or default to it in any CREATE TABLE.

2. You can explicitly name a journal table that already exists in another database in the CREATE TABLE, or in CREATE / MODIFY database you can specify an already existing journal in another database as the default for new objects created in the database.  You can't create the journal as as result of CREATE TABLE.

3. Once the journal table exists, you can ALTER TABLE tbl WITH JOURNAL TABLE db.pj ...

Re: Permanent Journal creation at Table Level

Thank Fred .

CREATE SET TABLE journal_database.journal_table ,NO FALLBACK ,
NO BEFORE JOURNAL,
AFTER JOURNAL,
with journal TABLE =journal_database.journal_name,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO
(
id INTEGER,
name VARCHAR(40) CHARACTER SET LATIN NOT CASESPECIFIC,
amt INTEGER)
PRIMARY INDEX ( id );

so in with journal TABLE =journal_database.journal_name,

we can give only default journal names that shoud be part of database in with journal TABLE

option.

we can not give a regular table name which is created in database with PJ.

Please clarify.