Transient Journal

Database

Transient Journal

Hi

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

Thanks
Madhavi.
8 REPLIES
Teradata Employee

Re: Transient Journal

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
Teradata Employee

Re: Transient Journal

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

Regards,

Adeel

Re: Transient Journal

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?

Thanks
Madhavi
Teradata Employee

Re: Transient Journal

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

Re: Transient Journal

thanks Adeel
I 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????
Teradata Employee

Re: Transient Journal

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
Teradata Employee

Re: Transient Journal

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

Re: Transient Journal

TJ's Skew has consistenly been above 90+

There are no sessions which are highly skewed, 

Please suggest on where to start ?