Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

How to obtain Obtaining locks forcibily of a table

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: How to obtain Obtaining locks forcibily of a table
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    Post subject:
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    Post subject:
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    Post subject:
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    Post subject: Reply to: How to obtain Obtaining locks forcibily of a table
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    Post subject:
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    Post subject:
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    Post subject: Reply to: How to obtain Obtaining locks forcibily of a table
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

Site Director


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

PostPosted: Wed Jun 18, 2008 9:37 pm    Post subject:
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    Post subject: Reply to: How to obtain Obtaining locks forcibily of a table
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

Site Director


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

PostPosted: Thu Jun 19, 2008 11:59 am    Post subject: Reply to: How to obtain Obtaining locks forcibily of a table
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    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts how to send just 10 rows in a CICS sc... Megha Gupta CICS 5 Thu Feb 23, 2017 6:57 pm
No new posts Join of more than one table dibyendumandal11111 DB2 6 Mon Feb 13, 2017 11:36 pm
No new posts GETMAIN / FREEMAIN versus STORAGE OBT... steve-myers PL/I & Assembler 8 Wed Feb 01, 2017 1:45 am
No new posts SQL query to run through list of valu... Ni3-db2 DB2 14 Wed Dec 14, 2016 9:52 am
No new posts Obtaining file information pahiker COBOL Programming 25 Tue Sep 27, 2016 7:25 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us