lock level in tables

georgipa · #1 (**permalink**) 05-11-10, 15:28

Hi Colleagues,

Somebody can tell me how to view the lock level in tables. por example page or row. what in the command to display this information and how to define this lock level in table.

Thank you for you help.

ARWinner · #2 (**permalink**) 05-11-10, 16:29

The default behavior is to do row level locking. If too many row locks are held DB2 will escalate the locks to a table level lock. The only control you have is to issue "LOCK TABLE" statement--see the manual. Use the snapshot monitor to view the locks currently being held.

Andy

georgipa · #3 (**permalink**) 05-12-10, 03:37

Hi ARWinner, thank you for you quick answer. I am DBA Informix and some activities DB2. at informix when create a table exist the statement lock mode (page or row) then you can modify this lock level if you it prefers. also you can view the lock level for any table in specific.

Greetings.

ARWinner · #4 (**permalink**) 05-12-10, 08:27

Well this is not Informix. This is DB2. It works the way I stated.

Andy

stiruvee · #5 (**permalink**) 05-13-10, 02:45

Hi,

You can use locksize column in syscat.tables to view current lock size for the table.

To change lock size use alter table command

example:
alter table <table name> locksize TABLE
alter table <table name> locksize Row

Satya

ARWinner · #6 (**permalink**) 05-13-10, 08:03

Quote:

Originally Posted by stiruvee

Hi,

You can use locksize column in syscat.tables to view current lock size for the table.

To change lock size use alter table command

example:
alter table <table name> locksize TABLE
alter table <table name> locksize Row

Satya

Well, I learned something new today.

Andy

georgipa · #7 (**permalink**) 05-20-10, 06:11

Hi All,

In my database there are a table lock in lock mode Z, ¿Anybody can tell me an procedure for detect what process or application is locking this table in DB2?

The version DB2 is the 9.5 and the system operation is Linux Suse 11.

thank you for you help.

ARWinner · #8 (**permalink**) 05-20-10, 08:00

Sure

Code:

select a.* 
from sysibmadm.snaplock as l 
inner join sysibm.snapappl_info as a on (l.agent_id = a.agent_id) 
where l.tabschema = MyTableSchema and l.tabname = MyTableName and 
l.lock_mode = 'Z'

Andy

georgipa · #9 (**permalink**) 05-20-10, 10:17

Thank you for you quick answer, ¿in db2 exist an sequence of command for monitoring lock en tables? and any method for look the statement is executing in database?, what applications is generating the locks and what user?

Greetings.

ARWinner · #10 (**permalink**) 05-20-10, 10:22

You can use the snapshot monitor or the administrative views (AS I gave you an inkling already). Look in the manual for more information.

Andy

Internet Services

Web Development

Digital Marketing

Consumer Tech