Solved

How to remove table from super exclusive lock in db2

Posted on 2010-09-03
8
2,303 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
  • 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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

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 (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

832 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