All Forums Database
madhavi_kl 27 posts Joined 07/06
22 May 2008
Transient Journal

HiI would like to know whether TRANSIENT JOURNAL comes into picture when we INSERT rows into a table. The table can be an EMPTY one or can be ALREADY POPULATED.Can anyone help me know this.ThanksMadhavi.

Adeel Chaudhry 773 posts Joined 04/08
22 May 2008

Hello,Yes it does, regard-less of the table being empty or not, after each update it stores ROW-ID of that inserted row, if the transaction fails it deletes the inserted row.Regards,Adeel

-- If you are stuck at something .... consider it an opportunity to think anew.

Adeel Chaudhry 773 posts Joined 04/08
22 May 2008

Additionally, just for info, TRANSIENT JOURNAL has nothing to do with Fastload/Multiload.Regards,Adeel

-- If you are stuck at something .... consider it an opportunity to think anew.

madhavi_kl 27 posts Joined 07/06
22 May 2008

But in some cases like INSERT/SELECT , DELETE ALL and also in case of MULTILOAD and FASTLOAD transient journal will nor come into picture, right?so to my knowledge it depends whether the table is already populated or an empty table.Also i would like to know when we say T.J takes BEFORE image... is that a complete image of the TABLE?ThanksMadhavi

Adeel Chaudhry 773 posts Joined 04/08
22 May 2008

No, TJ does store information about all INSERTs, UPDATEs and DELETEs regardless of the form of these statements. But for Fastload/MultiLoad.Fastload/Multiload uses seperate mechanism, they dont use TJ, additionally, for Fastload table must be empty for Multiload it may be or may not be.I am not 100% sure, but i believe TJ only keeps the snapshot of affected rows, otherwise updating on a table having 1 TB+ data could have taken a lot of time.Regards,Adeel

-- If you are stuck at something .... consider it an opportunity to think anew.

madhavi_kl 27 posts Joined 07/06
22 May 2008

thanks AdeelI agree with your points.I am just concerned with INSERT operation only. and HOW TJ mechanism works there?when we discuss abput TJ we speak about BEFORE IMAGE. how will that work in INSERTS????

Adeel Chaudhry 773 posts Joined 04/08
22 May 2008

As in first reply, whenever you issue INSERT command on any table, after its completion i.e. after inserting the new row in the table, Teradata automatically stores its ROW-ID in transient journal, if the transaction is successful the inserted row is kept/commited, if transaction is to be rolled-back, Teradata will get the ROW-ID from transient journal and will delete the inserted row so that Database may be in consistant form.HTH.Regards,Adeel

-- If you are stuck at something .... consider it an opportunity to think anew.

lateshpant 16 posts Joined 01/08
22 May 2008

To answer why operations are faster on empty tables than populated tables...Because when any operation is done on empty table the TJ just has one row in it , meaning that this table is empty... and in case of rollback ...just make the table empty again....Where as when you have a populated table and your operation involve 10000 inserts and 50000 updates in single txn . these many corresponding entries are also made into TJ....no wonder the operation takes that bit of extra time/cpu.Yeah, the bulk load utilities don’t need TJ... infact the later versions of DB/utilities affects at a block level.Hope this helps.Latesh

varanasianup 14 posts Joined 09/14
28 Apr 2016

TJ's Skew has consistenly been above 90+
There are no sessions which are highly skewed, 
Please suggest on where to start ?

You must sign in to leave a comment.