mload locks _ teradata forums
TRANSCRIPT
HIDE FEATURES
Search
TOOLSWelcome to the Teradata Developer Exchange discussion forums. For a general introduction and guidelines on
posting, please see this forum topic. RSS feeds are available for each forum, as well as a combined all
forums feed.
CONNECTIVITY
Laddered ConcurrentConnect (LCC): ClientPerformanceImprovementsDarrick S
BLOGS
Missing Functions:DENSE_RANKdnoeth
BLOGS
Two Levels ofConcurrency Control forLoad Utilities in Teradata13.10carrie
GENERAL
Teradata Multi-SystemManager 13.10 GCAldraper
CONNECTIVITY
Teradata JMS UniversalConnector 13.10.00releasedDarrick S
prakhar
101 posts
Joined 05/08
prakhar
101 posts
Joined 05/08
16 Aug 2009
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,
21 Aug 2009
Hi All,
Please provide some input...
Regards,
21 Aug 2009
If your Multiload job fails at Application Phase - you've
to use the following statement to release the lock on
TAGS
.net .Net Data Provider Team bteq cloudcompression cookbook database dataprovider devx download eclipseeclipse ide geospatial ide java Java
External Stored Procedures JDBC jmsJMS Adapter linux load LOB Mac odbc
PDK Performance plug-inplugin portlet portlets query sqlsqla sqla-je sqla je sql assistantTASM teradata teradataexpress Teradata Manager
Teradata SampleApplication TPT udf unicodeuniversal connector utf-8
viewpoint vmwarewindows Workload management
ALL FORUMS TOOLS
ANIMESH.DUTTA
35 posts
Joined 05/09
@nkit
12 posts
Joined 09/08
Bodhi.Sattva
17 posts
Joined 07/10
to use the following statement to release the lock on
the Mloaded table.
RELEASE MLOAD (TableName) IN APPLY
Animesh Dutta
Teradata Certified Master
24 Aug 2009
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.
08 Jul 2010
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
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.
"The Day You Cease to Learn, is the Day Yoe Cease to
Exist..."
You must sign in to leave a comment.