Solved

How to remove table from super exclusive lock in db2

Posted on 2010-09-03
8
2,457 Views
1 Endorsement
Last Modified: 2012-05-10
We have a serious issue in DB2 and we need experts help with this. We have a tablespace and it contains 4 tables.One of the table went into super exclusive mode and no operations(read,update,delete etc) can be performed on that table. All the other tables in the table space can be accessed. We recycled the database many times and still the lock does not get released.

Is there a way we can release the super exclusive lock on the table ?

I would really appreciate your valuable inputs. Lookign forward to recieving your response.

Thank You.
1
Comment
Question by:partminer
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
8 Comments
 
LVL 6

Expert Comment

by:yjchong514
ID: 33594988
delete from mytable;
commit;
reorg table mytable;
commit;
runstats on table myTable on all columns;
commit;
0
 

Author Comment

by:partminer
ID: 33595085
operations(add,delete,update) are not allowing on table then how i will execute delete from mytable;
0
 
LVL 6

Expert Comment

by:yjchong514
ID: 33595878
Dear friend,
1.What version of db2 in used?
2.What error message occured when you attempt to unlock it?
3.What you mean recycled the db?
4.Do you has admin access right?

Regards,
yjchong514
0
More Than Just A Video Library

Train for your certification. Learn the latest DevOps tools. Grow your skillset to do better work.

At Linux Academy, we release new training modules every week so you'll always be up to date on the latest tech.

 

Author Comment

by:partminer
ID: 33596397
Dear yjchong : My db2 vertion is 8.1 . when we are trying to fetch data using select statement it's giving below error message
DB2 SQL error: SQLCODE: -911, SQLSTATE: 40001, SQLERRMC: 68
Message: The current transaction has been rolled back because of a deadlock or timeout. Reason code "68". ?
and we observed exclusive lock held on table .Lock will automatically creating when we remove the existing lock . I have all admin rights available .
0
 
LVL 4

Accepted Solution

by:
gmarino earned 500 total points
ID: 33597854
You will need an id that has SYSMON or SYSADM rights to do the following:

db2 UPDATE MONITOR SWITCHES USING LOCK ON STATEMENT ON TABLE ON TIMESTAMP ON UOW ON

Then run your job.  Using db2 Iist application, find your app's connection.  Eventually, it will show a Status of "Lock-Wait".  When it does, you should run the following snapshot command:

db2 get snapshot for application agentid <agent_id>

where <agent_id> = the Application Handle of the Connection in Lock-Wait (from LIST APPLICATIONS.

Inside the snapshot output, you will find a paragraph that describes the lock-wait condition:

  ID of agent holding lock                 = 123
  Application ID holding lock              = *LOCAL.db2inst1.100824044024
  Lock name                                = 0x02CE00020000D22C0000000052
  Lock attributes                          = 0x00000000
  Release flags                            = 0x40000000
  Lock object type                         = Row
  Lock mode                                = Exclusive Lock (X)
  Lock mode requested                      = Update Lock (U)
  Name of tablespace holding lock          = TS0044
  Schema of table holding lock             = BOX0044
  Name of table holding lock               = BOX_STAT_CHG
  Lock wait start timestamp                = 08/24/2010 00:41:12.442468

Take the "ID of agent holding lock" and run the

db2 get snapshot for application agentid <agent_id>

where <agent_id> this time = the "ID of agent holding lock"


This second snapshot will give you the information about the connection that is causing the contention.


If you post the information from that second snapshot, the people here can give you more direction on how to resolve the issue.

- Greg

0
 
LVL 6

Expert Comment

by:yjchong514
ID: 33671876
Are you okie?
Just check with you.
0
 

Author Closing Comment

by:partminer
ID: 33984333
Provided part of the solution.
0

Featured Post

Get Actionable Data from Your Monitoring Solution

Your communication platform is only as good as the relevance of the information you send. Ensure your alerts get to the right people every time with actionable responses. Create escalation rules that ensure everyone follows the process and nothing is left to chance.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

695 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question