Mload locks

Tools
Enthusiast

Mload locks

Hi Forum Gurus,
I am having some confusions about locks in various phases of Mload

Acquisition Phase

1)Access lock
2)Acquisition lock which sets a flag in table header rejecting any DDL except DROP.
How these 2 locks differ with each other.....basically access lock doesnt allow any exclusive lock but acquisition lock allows DROP....does that mean it overrides access lock????

Application Phase
1)Write lock
2)Application phase which sets a flag in table header rejecting any DDL except DROP and allowing DML with access lock only(select)Same ques here also......how these 2 differs

RELEASE MLOAD;
Will release access lock on target table and acquisition lock or both....?
Also please specify what causes these errors and what shud we do in each case

a)error 2571 ....mload can't be released and what is the raeson for such error
b)error 2652 Error Code 2652. Operation Not allowed.tableis being mloaded

I have read several articles ,discussions regarding this......i urge all supreme being to answer this so that Teradata users can use this post as benchmark to solve these commom problems

Regards,
4 REPLIES
Enthusiast

Re: Mload locks

Hi All,
Please provide some input...

Regards,
Enthusiast

Re: Mload locks

If your Multiload job fails at Application Phase - you've to use the following statement to release the lock on the Mloaded table.

RELEASE MLOAD (TableName) IN APPLY
Teradata Employee

Re: Mload locks

Hi Prakhar,

Check this out..

Acquisition phase

1. This is an advantage to mload that user can access table while it is loading (in acquisition phase) as in this phase data is taken
from client to the worktables, hence there is no involvement of target table.

2. Acquisition lock allows only DML access and drop DDL SQL statements and reject others, here this is a flag which sets a limitation over said advantage (of access),
it signifies that table definition is captured and loading has been started so table structure can not be changed although drop table is allowed.
That is the reason one of the error faced in this phase is for "table does not exist"

Application Phase

1. Write lock because target table is being loaded from work tables.

2. Here application lock flag only allows select with access (unlike DML in acquisition phase) and Drop DDL SQL statements and rejects others. However,
MultiLoad delays processing of the DROP statements. After the application phase completes, MultiLoad processes any waiting DROP statements.

Both MultiLoad acquisition lock and MultiLoad application lock are flags, setup to provide flexibility while loading with multiload.
Do not consider them as a type of lock.

RELEASE MLOAD;
This statement obtains the Exclusive lock over target tables and frees the target table from any kind of locks.

Generally one has to Resubmit/Rerun the job after release mload and droping error/work and restart table.

Error Codes
a)error 2571 ....mload can't be released. this error occurs when
Table does not exist or user does not have release mload access or table is not in application phase (using IN APPLY).

b)Error Code 2652. When user try to access the table that is being mloaded.
In this scenario use locking with access modifier.
Enthusiast

Re: Mload locks

Hello,
I have been looking for answers to a basic question on table partitioning in Teradata. Can someone please shed more light. Here it is -

If we do Range Partitioning on a table based on Numeric values and then different jobs load data corresponding to different partition ranges in parallel - then do we get into a resource contention scenario OR do all parallel running jobs hit specific partition and only acquire lock on a particular partition. In short - the question is - while loading data into a partitioned table [in this case Range Partition], do we acquire a lock at the partition level and multiple programs can run in parallel acquiring lock at individual partitions OR the lock is acquired at whole table level and the parallel jobs get into a contention mode.

Ex: An error table where each program loads specific error information that is encountered based on error code. If the table is range partitioned on error codes of range of 1000s, will individual programs when run in parallel hit their specific partition acquiring only lock to it or they will acquire complete table lock and release them for subsequent jobs when error load is done.

Please advice.