mload locks _ teradata forums

3
HIDE FEATURES Search TOOLS Welcome 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 Concurrent Connect (LCC): Client Performance Improvements Darrick S BLOGS Missing Functions: DENSE_RANK dnoet h BLOGS Two Levels of Concurrency Control for Load Utilities in Teradata 13.10 carrie GENERAL Teradata Multi-System Manager 13.10 GCA ldraper CONNECTIVIT Teradata Connecto released Darrick 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 cloud compression cookbook database data provider devx download eclipse eclipse ide geospatial ide java Java External Stored Procedures JDBC jms JMS Adapter linux load LOB Mac odbc PDK Performance plug-in plugin portlet portlets query sql sqla sqla-je sqla je sql assistant TASM teradata teradata express Teradata Manager Teradata Sample Application TPT udf unicode universal connector utf-8 viewpoint vmware windows Workload management ALL FORUMS TOOLS

Upload: soumya-ranjan-pradhan

Post on 09-Mar-2015

337 views

Category:

Documents


6 download

TRANSCRIPT

Page 1: Mload Locks _ Teradata Forums

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

Page 2: Mload Locks _ Teradata Forums

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

Page 3: Mload Locks _ Teradata Forums

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.