All Forums Database
giri 24 posts Joined 11/05
22 Sep 2006
Permanent Journals

Hi I would like to know about Permanent Journals in Teradata. How to define Permanent Journal tables ? Where are they stored? How to drop it?Thanks Giri

leo.issac 184 posts Joined 07/06
22 Sep 2006

The purpose of a permanent journal is to maintain a sequential history of all changes made to the rows of one or more tables. Permanent journals help protect user data when users commit, uncommit or abort transactions. A permanent journal can capture a snapshot of rows before a change, after a change, or both. You use permanent journaling to protect data. Unlike the automatic journal, the contents of a permanent journal remain until you drop them. When you create a new journal table, you can use several options to control the type of information to be captured. You create permanent journals when you create a user or database. To create permanent journals within an existing user or database, use the MODIFY statement. Users activate permanent journaling by including the JOURNAL option in the CREATE or MODIFY statements for users or databases. You must allocate sufficient permanent space to a database or user that will contain permanent journals. If a database or user that contains a permanent journal runs out of space, all table updates that write to that journal abort.Use the MODIFY USER or MODIFY DATABASE statement to delete a permanent journal. Before you delete the journal, you must use the ALTER TABLE statement to stop the journaling being done to that journal. You can find more details in user manuals

giri 24 posts Joined 11/05
25 Sep 2006

Thank u,Leo Now, I have a clear Idea of Permanent Journals. Can I use Show Table command to view the Permanent Table ? Can I define check points from SQL Assistant. Can Multiple Check Points be assigned?Regards,Giri.

leo.issac 184 posts Joined 07/06
25 Sep 2006

giri,I beleive that Teradata specific command SHOW is used for only Tables/Views/Join Indexes/Hash Indexes/Triggers/Stored Procedure. I hope we cannot show the Journal table definition.Not very sure about it !. I hope there should be someway of doing it. For now, I tried using Teradata Administrator to view the attribute details of Jouranl table. I could get the details successfully.Regarding the checkpoint, I refered the manuals and I could find the following details. I hope this will be usefulTo checkpoint a journal table, you must meet at least one of the following criteria. • Have CHECKPOINT privilege on the journal table. • Have CHECKPOINT privilege on the database containing the journal table. • Be an owner of the database containing the journal table. • Be an immediate or indirect owner of the journal table.I thank you for posting these questions. This made me refer few manuals and gather more information on permanent journals.

dnoeth 4628 posts Joined 11/04
25 Sep 2006

"I thank you for posting these questions. This made me refer few manuals and gather more information on permanent journals."Hey, i like that. Sounds so much nicer than "RTFM" ;-)Dieter

Dieter

leo.issac 184 posts Joined 07/06
25 Sep 2006

Dieter,Really! Reading The FINE Manual helps. Ofcourse, if one does not have the oppurtunity to refer them, then, I beleive someone else can refer them and share the knowledge.Nothing wrong in that! Dont you think so? ;-)After all, The very purpose of Forums is knowledge sharing amongst the members . No matter how the members gain /Share the knowledge.

giri 24 posts Joined 11/05
27 Sep 2006

Thank U,I am really having a good time with u. can u share the link or the document for permanent journals.Giri.

varun2102 37 posts Joined 12/06
12 Apr 2007

I can kind of understand the concept of Journaling for dataBases, but i dont understand Journaling for a user...can someone please explain?

Jim Chapman 449 posts Joined 09/04
12 Apr 2007

Permanent journals provide the ability for the user to execute roll-back or roll-forward operations between user-defined checkpoints.

prakash_r02 8 posts Joined 08/10
20 Aug 2010

"I can kind of understand the concept of Journaling for dataBases, but i dont understand Journaling for a user...can someone please explain? "

Permanent Journal can be created when the User or Database is ctreated.
CREATE DATABASE mydb
FROM xxspace
AS
PERM = 20000000
SPOOL = 2000000
NO FALLBACK
ACCOUNT = '$xxxxx'
NO BEFORE JOURNAL
AFTER JOURNAL
DEFAULT JOURNAL TABLE = mydb.journals;

Here DBA opt for only AFTER JOURNAL and he has name the journal table as "mydb.journals".
When user creates a table in the database "mydb" , by default AFTER JOURNAL is available for him to protect his data when the hardware failure occurs.
He can opt for NO AFTER JOURNAL by overriding the default. Follwoing is the example.

case1: in this case, by default the table has AFTER JOURNAL option.

"CREATE TABLE mydb.my_table
( field1 INTEGER,
field2 INTEGER)
PRIMARY INDEX field1;"

case2: in this case, user has specifically stated he wanted no AFTER JOURNAL for his data. This is how user can override the defult.

"CREATE TABLE mydb.my_table1
FALLBACK,
NO AFTER JOURNAL
( field1 INTEGER,
field2 INTEGER)
PRIMARY INDEX field1;"
In this case whenever the user inserts/updates and the transaction is committed , then the affected rows will be taken backup in the journal table "mydb.journals".

The above is applicable for a user's schema also.

The users can opt for the Permanent journal when they create their tables.

20 Apr 2013

hi all,
I have a doubt related to the discussion above that
is one permanent journal table is defined for the full db or it is defined per table or for  multiple tables.. ?  
Also Dieter/Leo can u pls suggest me the pdf for this to refer..
 
Cheers!
Nishant

dnoeth 4628 posts Joined 11/04
20 Apr 2013

Hi Nishant,
there might be one PJ in each database/user.
Any table in any database might use any PJ, it's up to you how you set it up. Usually tables within a database use the same PJ.
An overview on Permanent Joiurnals is found in the Database Design manual (plus some topics in the DDL manuals).
Dieter

Dieter

dba_1234 13 posts Joined 09/13
14 Oct 2013

so a table having journal  and fallback protection is called dual image right ?
and what is purpose of having 2 copies of row which is undergoing a change. how this can be used?
is PJ is same as Tj.which is going to take a sanpshot of only the rows which are going to be changed or it will take a whole snapshot of the table.and what could be the size of the snapshot is it same size of the base table/row
thanks for you time 

17 Apr 2015

Hi All, 
we had faced an issue recently due journal, we introduced a temp table and by mistake it went with before and After journal, while first insert into temp table failed with no room in database and we dropped and recreated the temp table without journal , when we dropped and recreated Temp. tables there was no data in the table still has caused sudden dense to journal table. can anybody tell how this space can be recovered from journal table as the temp table never required the journal. 
Thanks,

sharib hussain 12 posts Joined 01/13
22 Apr 2015

Journals can be deleted after submiting the "checkpoint with save" command which moves the rows from active journal to saved journal. Once the rows are moved to saved part then you can delete the journal rows using DELETE JOURNAL command.

Thanks & Regards,
Sharib Hussain

You must sign in to leave a comment.