|
|
|
Supreme Being
Group: Forum Members
Last Login: 10/29/2009 2:48:53 AM
Posts: 94,
Visits: 321
|
|
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,
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 10/29/2009 2:48:53 AM
Posts: 94,
Visits: 321
|
|
Hi All,
Please provide some input...
Regards,
|
|
|
|
|
Forum Member
Group: Forum Members
Last Login: 10/30/2009 6:28:19 AM
Posts: 34,
Visits: 117
|
|
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
|
|
|
|
|
Junior Member
Group: Forum Members
Last Login: 8/24/2009 7:41:51 AM
Posts: 12,
Visits: 55
|
|
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.
Regards
Ankit
|
|
|
|