IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

How to obtain Obtaining locks forcibily of a table


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
rajandhla

Active User


Joined: 18 Oct 2005
Posts: 182
Location: Luton UK

PostPosted: Wed Jun 18, 2008 4:56 pm
Reply with quote

Hi,

Contention is going on for of one my job and it is critical and the business has approved and fine to get exclusive locks.

I got a doubt if some body is using the resource (table) then how to obtion the lock by forcibily and disconnecting the current user.

Then i will release the locks at the end of the job.

Any ideas how to get exclusive locks by force

Thanks
Jai
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Wed Jun 18, 2008 5:16 pm
Reply with quote

In your application program code

Code:


LOCK TABLE <TABLENAME> IN EXCLUSIVE MODE

Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Wed Jun 18, 2008 5:19 pm
Reply with quote

Typical hammer solution, instead of determining the problem.
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Wed Jun 18, 2008 5:25 pm
Reply with quote

Contention in Jai's system is something which we cannot look into and figure out what the problem is... and more over he has mentioned that business has already approved to get exclusive locks , now what is left is the solution for this ....
Back to top
View user's profile Send private message
ascelepius

New User


Joined: 16 Jun 2008
Posts: 35
Location: bangalore

PostPosted: Wed Jun 18, 2008 5:34 pm
Reply with quote

I agree with ashimer, there cases when long running threads maybe accessing Tables. In such cases, when batch programs may need to insert/update, these programs will have to ATTEMPT to lock the Table or Partition in exclusive mode and then continue with DML operations. I must emphasize here that you cannot disconnect the user, the program will have to wait(which can be handled within the program) to gain exclusive access to the table/partition.

regards,
ASCI(I)
Back to top
View user's profile Send private message
rajandhla

Active User


Joined: 18 Oct 2005
Posts: 182
Location: Luton UK

PostPosted: Wed Jun 18, 2008 5:45 pm
Reply with quote

I did tried and identified the problem and the resource that is causing the problem....

this jobs runs in a month and considering the cost benfit business is fine to run the job by obtaining exclusive locks.

But still is there any way to avoid contention for a table by doing some thing at database level.

Table space is a segmented table space.

plz let me know ur thoughts.

Thanks,
jai
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Wed Jun 18, 2008 5:49 pm
Reply with quote

Why do application programs need to have exclusive control over a table, when reorg utilities can reorg a db2 table without shutting out users?

I really don't care if management has accepted the advice of someone who suggested A. I am not here to be a 'yes man'.

Now, you can give him all the code in the world, but if the OP is so inexperienced that he can not find the 'DB2 Table lock command and syntax' in the manual, why do you assume that everything is hunky doory and on with the rookie solution?

But I have answered my question. If the OP can't use the manual, why expect him to determine his contention issues?
Back to top
View user's profile Send private message
ascelepius

New User


Joined: 16 Jun 2008
Posts: 35
Location: bangalore

PostPosted: Wed Jun 18, 2008 6:09 pm
Reply with quote

Yup DBZ is correct...

But as always "It depends"!

Enforcing a exclusive access on tables is not your best solution. Locks maybe acquired on Tables due to other batch/online programs that may access your Table for DML operations.

You could therefore,
a) change the locksize of your table to lower granularity(row maybe) after you have assessed the maximum locks that can be aquired on your tablespace. This would reduce some amount of contention.

b) Partition your table after analyzing your data and allow applications to access only partitions they need.

c) Setup a semaphore system that does not allow other applications to access the table or partition so that your can weed out contention once in for all.

If you feel these maybe too much of an overhead, I guess you can code the exclusive lock and gain exclusive access if your batch job can execute without shutting out users for too long...

regards,
asci(i)
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Wed Jun 18, 2008 9:37 pm
Reply with quote

Hello,

And what happens when the next 20 "most important" or "critical" processes are identified. . . ?

It won't be too long until there is no reason to have a multi-user database system. Everything will be so important that it must have exclussive control of the table(s) it uses. And let us not forget the "special" online users - they too should not have to share. When they want to run their transactions, all other use should be prevented. I've actually been to a couple of sites that tried to implement this way and fortunately, the management that insisted on such bad practices were excused from further employment.

A far better solution is to properly manage the data resource.
Back to top
View user's profile Send private message
ascelepius

New User


Joined: 16 Jun 2008
Posts: 35
Location: bangalore

PostPosted: Thu Jun 19, 2008 11:10 am
Reply with quote

Quote:

A far better solution is to properly manage the data resource.


It depends...(and how is another topic of debate)
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Thu Jun 19, 2008 11:59 am
Reply with quote

Quote:
and how is another topic of debate

Yup, it surely is. . . icon_wink.gif

d
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Load new table with Old unload - DB2 DB2 6
No new posts Pulling a fixed number of records fro... DB2 2
No new posts Multiple table unload using INZUTILB DB2 2
No new posts Check data with Exception Table DB2 0
No new posts Dynamically pass table name to a sele... DB2 2
Search our Forums:

Back to Top