Tracing Locks in Table level on informix
# Find out the hex partnum number of the table
dbaccess sysmaster –
select hex(partnum),owner from systabnames where tabname=”table_name”;
example
=======
> select hex(partnum),owner,dbsname from systables where tabname=”country”;
(expression) owner dbsname
0x004004E5 grefdev gref420_cit3
1 row(s) retrieved.
Result
======
(expression) 0x004004E5
owner grefdev
dbsname gref420_cit3
tabname country
#Note : Take note of partition patnum and lockid of the table by using “oncheck”
eg:
==
oncheck -pt gref420_cit3:country |more
Partition partnum
Partition lockid
# Note: To Confirm dbsname,owner,tablename by using partnum
dbaccess sysmaster –
> select * from sysmaster:systabnames where partnum=HEX(partnum);
example
========
dbaccess sysmaster –
> select * from sysmaster:systabnames where partnum=HEX(4195557);
partnum 4195557
dbsname gref420_cit3
owner grefdev
tabname country
collate en_US.819
1 row(s) retrieved.
( ensure by converting partnum DeCimal to HEX using calculate)
# Try to find the blocker user thread address using “onstat –k” grep with the hex partnum number of the table
onstat -k | tail -6 | awk ‘{if ($6 !=100002) {print $0}}’
onstat -k |grep -i
eg:
===
onstat -k | grep -i 4004E5
449f4e58 0 59fa8db8 0 S 100002 306 0
#– get blocker session id –#
onstat -u | grep
eg:
===
onstat -u | grep 59fa8db8
#– get blocker session info –#
onstat -g ses
example
=======
onstat -g ses 39612
IBM Informix Dynamic Server Version 11.50.FC9 — On-Line — Up 27 days 19:18:57 — 594180 Kbytes
session effective #RSAM total used dynamic
id user user tty pid hostname threads memory memory explain
39612 grefdev – – -1 23.252.1 1 790528 487192 off
tid name rstcb flags curstk status
88085 sqlexec 5f5f1418 Y-BP— 5152 cond wait netnorm –
#—Note:Below to can check for hostname longer then 8 characters :—#
Query sysmaster:syssessions view.
$ dbaccess sysmaster –
> select sid, username, hostname from syssessions where sid=;
/* session id is in integer */
example
=======
$ dbaccess sysmaster –
> select sid, username, hostname from syssessions where sid=39612;
sid 39612
username grefdev
hostname 23.252.149.206