Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2880
  • Last Modified:

How to remove table from super exclusive lock in db2

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
partminer
Asked:
partminer
  • 3
  • 3
1 Solution
 
yjchong514Commented:
delete from mytable;
commit;
reorg table mytable;
commit;
runstats on table myTable on all columns;
commit;
0
 
partminerAuthor Commented:
operations(add,delete,update) are not allowing on table then how i will execute delete from mytable;
0
 
yjchong514Commented:
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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
partminerAuthor Commented:
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
 
gmarinoCommented:
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
 
yjchong514Commented:
Are you okie?
Just check with you.
0
 
partminerAuthor Commented:
Provided part of the solution.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now