Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

How to remove table from super exclusive lock in db2

Posted on 2010-09-03
8
Medium Priority
?
2,696 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
7 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
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 

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 2000 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

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

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…
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…

876 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