All Forums Database
28 May 2013
Types of locks during Fastload abort-?

Hi All,
Have a doubt in my mind, just want to clear it regarding the types of locks applied during fast  load abort case and why is it different from that of multiload
normally based on the phase in which mload aborts we have to first release the lock   applied on the target table and then restart it from the last check point by simply resubmitting the script..
However in case of fast load  failure during the loading , we release the lock on the target table by using this in the script (commenting the insert)
Begin loading
error file  et1,et12
end loading
 
after succesful completition, locks gets automatically relased and we resubmit the job..
 
My doubt here is that why is this different from mload where we simply release the locks by RELEASE MLOAD statement.??
Is there any reason behind it or its the way fload is programmed?
 
Cheers!
Nishant
 
 
 
 

14 Sep 2013

Hi Experts,
Your comments on the above pls.
cheers!
Nishant
 

15 Oct 2013

Hi All,
Can you please share your thoughts on the above pls?
Cheers!
Nishant
 

Raja_KT 1246 posts Joined 07/09
15 Oct 2013

Nishant, I will answer from my viewpoints :). Fload is built for fast loading into empty tables. Mload is built with 5 phases...blah blah.. So you see the differences. Both utilities are built for their own purposes by TD. They have different steps of processing.
 
Cheers,
Raja
 

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

15 Oct 2013

Thanks Raja for reply. 
My query is more from the Locking  Prosepective.
Can you please throw some light on the locking mechnasin in Fload? vs what we have in mload
Cheers!
Nishant
 

19 Oct 2013

Hi Dieter/Carlos,
As I am not sure,can you please share some pointers in the difference of locking mechanism between Fast load vs  Mload.
Also,can we execute different fast load scripts at a time, to load a single table but in to different partitions,
Is it possible ..? Please suggest
 
Cheers!
Nishant

25 Oct 2013

Hi Dieter/Carlos,
Your thoughts on the above pls.
Cheers!
Nishant

18 Nov 2013

Hi Experts,
Your opinions on the below pls.
 
cheers!
Nishant

ToddAWalter 316 posts Joined 10/11
18 Nov 2013

Technically, there are no locks when Fastload or Multiload are not running - if the job has aborted or been halted prior to finishing. There is a status recorded in the table that says that the table is in process of being loaded. The status is left in the table to enable the job to be restarted, continue from where it left off and run to completion.
This status is simple to remove in the case of fastload because the only choices are to drop or delete all and reload the table.
Since there is already data in the table being multiloaded, there is more complexity. If the apply phase has not been started yet, then cleanup must be done but the table contents have not yet been affected. If apply phase has begun, then the table is in an uncertain state. It is not recommended to release the mload when the table is in that state but it is allowed with the additional option. Because of the added complexity, the release mload is a form of "are you sure" check before you put your table into an uncertain state or remove the ability to restart and complete the mload.
It is not possible to run concurrent fastload jobs into the same table at this time. It is on the to do list to allow multiple insert selects to insert into separate partitions via new functionalty being build to allow locks at the partition level - no release date available at this time.

21 Nov 2013

Thanks a lot Todd for your sharing your thoughts & comments . It really  helps.
Just have a doubt in mind regarding the H.U.T ( hostutility locks), I was under the impression that during the execution of various T.T.U (mload/fload), these got applied for mainiting the
data consistency.
Can you please share some pointers to deep dive in this area as well?
 
Cheers!
Nishant
 
 
 

ToddAWalter 316 posts Joined 10/11
21 Nov 2013

HUT locks are for ARC backup/restore only. This is the only kind of lock in Teradata that persists when there is not a job running. The lock is left if the job does not finish normally so that the object's state cannot be changed while awaiting the archive/restore job to be repaired and restarted. The lock must be explicitly released if the job is not going to be continued.

21 Nov 2013

Ok thanks a lot Todd for your reply.. .. Please bear with me as  I am getting tempted now to clear my doubts
I am bit unclear abt the status as you mentioned that  
"There is a status recorded in the table that says that the table is in process of being loaded. The status is left in the table to enable the job to be restarted, continue from where it left off and run to completion."  
The table which you are referring is the target table or the check point log table? and is this status is internal to teradata or can we also check that..?
 
Cheers!
Nishant
 

 

ToddAWalter 316 posts Joined 10/11
21 Nov 2013

The status is in the target table and the error, log tables. they are all tied together internally. Unfortunately the status cannot be seen in the dictionary with a query. A list of tables in this state may be found by using Checktable - there is an option there that looks only for that type of table.
A specific error code will be returned to any statement that tries to change the structure of the table or the data - it will state that the statement is not allowed due to the table being in this state.

21 Nov 2013

Thanks Todd.. It helps..

You must sign in to leave a comment.