IDS 12.10 - temporary dbspaces locked
A user saw many locks on temporary dbspaces in sysmaster:syslocks (IX locks on "TBLSpace" in tempdbs, tempdbs1, tempdbs2 held by several sessions) and asked whether this indicated a concurrency problem, also wondering about RAMDISK for temp space performance. The reply explained that TBLSpace is Informix's internal partition-tracking table and that the Intent Exclusive locks simply reflect sessions creating temporary tables — normal behaviour, not a problem. The RAMDISK question was not addressed.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi, I have repaired that my systems have several locks over temporary dbspaces on syslocks. I have seen some information about using RAMDISK to improve performance. Can somebody give some help about this? How to do it and if it is the case, how can I detect that kind of concurrency problems? Thanks in advance, SP
Some examples of the "locks" you are seeing would be great. From the online documentation, the sysmaster:syslocks has no field for dbspace: dbsname char(128) Database name tabname char(128) Table name rowidlk integer Real rowid, if it is an index key lock keynum smallint Key number of index key lock type char(4) Type of lock owner integer Session ID of the lock owner waiter integer Session ID of the user waiting for the lock. Best regards, Luis Marques
Hi Luis,
Thanks for your reply,
I am using this SQL code:
select owner, username, hostname, dbsname, tabname, type
from syssessions s, syslocks l
where sid = owner
and tabname not like "sys%"
it return this:
34015|bduser|joaomendes.dominio.local|tempdbs|TBLSpace|IX|
34015|bduser|joaomendes.dominio.local|tempdbs1|TBLSpace|IX|
36850|bduser|10.212.133.100|tempdbs1|TBLSpace|IX|
36850|bduser|10.212.133.100|tempdbs|TBLSpace|IX|
34015|bduser|joaomendes.dominio.local|tempdbs2|TBLSpace|IX|
36850|bduser|10.212.133.100|tempdbs2|TBLSpace|IX|
27898|bduser|pedrofernandes.dominio.local|tempdbs1|TBLSpace|IX|
27898|bduser|pedrofernandes.dominio.local|tempdbs2|TBLSpace|IX|
29044|bduser|nunobento1.dominio.local|tempdbs1|TBLSpace|IX|
27898|bduser|pedrofernandes.dominio.local|tempdbs|TBLSpace|IX|
29044|bduser|nunobento1.dominio.local|tempdbs|TBLSpace|IX|
29044|bduser|nunobento1.dominio.local|tempdbs2|TBLSpace|IX|
as I don't know much SQL, the instructions are correct?
this is normal? as my temporary dbspaces are empty?
Dbspaces
address number flags fchunk nchunks pgsize flags owner name
88b80028 1 0x4030001 1 1 2048 N BA informix rootdbs
8aba97b8 2 0x2001 2 1 2048 N TBA informix tempdbs
8aba99f8 3 0x2001 3 1 2048 N TBA informix tempdbs1
8aba9c38 4 0x2001 4 1 2048 N TBA informix tempdbs2
8aca5028 5 0x4030001 5 1 2048 N BA informix logdbs
8aca5268 6 0x4020001 6 1 4096 N BA informix dbspace1
6 active, 2047 maximum
Chunks
address chunk/dbs offset size free bpages flags pathname
88b80268 1 1 512 1500000 1445207 PO-B-- /dev/raw/raw1
8aca54a8 2 2 512 4000000 3999947 PO-B-- /dev/raw/raw2
8aca6028 3 3 512 4000000 3999947 PO-B-- /dev/raw/raw3
8aca7028 4 4 512 4000000 3999947 PO-B-- /dev/raw/raw4
8aca8028 5 5 512 4000000 149947 PO-B-- /dev/raw/raw5
8aca9028 6 6 512 37250000 28384458 PO-B-- /dev/raw/raw6
6 active, 32766 maximum
SP
The TBLSpace is an Informix internal table, used to keep track of all partitions in a tablespace (https://www-01.ibm.com/support/docview.wss?uid=swg21174940). What you are seeing is sessions that created what I suppose are temporary tables in the temporary dbspaces. The IX (Intent Exclusive - https://www.ibm.com/developerworks/data/library/techarticle/dm-0609herber/index. html ) locks on the TBLspace TBLspace are just a result of the normal operation of the sessions creating temporary tables.