SQL For Database Locks
I wonder if anyone can point me in the right direction to create some SQL that will give me the lock status of a database? I have it all worked out for table locks but I'm struggling to work it out for database locks.
You will find an exclusive header lock on the database's
sysmaster:sysdatabases record. In onstat -k you will probably see partnum
100002 (unless you have so many databases that the datrabase tablespace
records span more than the initial root chunk) and a rowid (in hex)
corresponding to the database's record in sysdatabases. So, on my system
if I connect to the 'art' database with the EXCLUSIVE modifier, I will see:
$ onstat -k
IBM Informix Dynamic Server Version 12.10.FC1 -- On-Line -- Up 2 days
06:33:33 -- 491728 Kbytes
Locks
address wtlist owner lklist
type tblsnum rowid key#/bsiz
44607028 0 5c7ae1b8 0
S 100002 204 0
4460a328 0 5c7b3fe8 0
S 100002 204 0
4460a828 0 5c7b0c88 0
HDR+X 100002 206 0
4473f828 0 5c7aea48 0
HDR+S 100002 204 0
4473f8a8 0 5c7aea48 4473f828
HDR+S 100002 201 0
4473f928 0 5c7ad928 0
S 100002 204 0
6 active, 20000 total, 16384 hash buckets, 0 lock table overflows
rowid 206 hex corresponds to 518 decimal:
dbaccess sysmaster -
> select * from sysdatabases where rowid = 518;
name art
partnum 1049006
owner informix
created 07/26/2011
is_logging 1
is_buff_log 0
is_ansi 0
is_nls 0
is_case_insens 0
flags -12287
1 row(s) retrieved.
>
Looking in the syslocks table, I see:
dbsname sysmaster
tabname sysdatabases
rowidlk 518
keynum 0
type X
owner 1130
waiter
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other organization with which I am associated either explicitly,
implicitly, or by inference. Neither do those opinions reflect those of
other individuals affiliated with any entity with which I am affiliated nor
those of the entities themselves.
On Thu, Jun 20, 2013 at 8:02 PM, RAY BURNS
<ray.burns@velocityglobal.co.nz>wrote:
> I wonder if anyone can point me in the right direction to create some SQL
> that
> will give me the lock status of a database? I have it all worked out for
> table
> locks but I'm struggling to work it out for database locks.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--e89a8ff1cf12dd0f0a04df9f176c
Dead easy when you know. I can just use the code I already have for table locks and restrict it to the sysdatabases table. Thanks so much.