sysrstcb
Felipe wanted an SQL way to find which session has a given table open, by decoding the opentab column of sysmaster's sysrstcb. Art Kagel explained opentab is just a memory address with no SMI mapping, so the usual route is 'onstat -g opn', which lists partnums plus tid/rstcb addresses that can be joined back to sessions. Andrew Ford posted a ksh script: get the table's partnum from systables, grep it in onstat -g opn, then look up sid in sysrstcb by tid and session details in syssessions. Doug Lawry suggested a simpler query against sysconblock matching cbl_stmt on the table name. Felipe confirmed both approaches worked.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi, I need to create a SELECT to verify which session is working on a certain table, but until now I couldn't decrypt the opentab column from sysrstcb table. Anyone knows how decrypt this column? Best Regards, Felipe Martins Clemente MC Software
It's a memory address, but where you could look it up to trace it to a table or list of open tables I don't know. Not every memory table has a pseudo-table in sysmaster assigned to it. 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 Fri, Feb 22, 2013 at 8:13 AM, FELIPE CLEMENTE <felipe@mcsoftware.com.br>wrote: > Hi, > > I need to create a SELECT to verify which session is working on a certain > table, but until now I couldn't decrypt the opentab column from sysrstcb > table. > > Anyone knows how decrypt this column? > > Best Regards, > > Felipe Martins Clemente > MC Software > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --bcaec54d3f8477cc9704d6502b74
Can't it be mapped to the output from onstat -g opn?
On 22/02/2013 13:28, Art Kagel wrote:
> It's a memory address, but where you could look it up to trace it to a
> table or list of open tables I don't know. Not every memory table has a
> pseudo-table in sysmaster assigned to it.
>
> 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 Fri, Feb 22, 2013 at 8:13 AM, FELIPE CLEMENTE
> <felipe@mcsoftware.com.br>wrote:
>
> > Hi,
> >
> > I need to create a SELECT to verify which session is working on a certain
> > table, but until now I couldn't decrypt the opentab column from sysrstcb
> > table.
> >
> > Anyone knows how decrypt this column?
> >
> > Best Regards,
> >
> > Felipe Martins Clemente
> > MC Software
> >
> >
> >
> >
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --bcaec54d3f8477cc9704d6502b74
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
Thanks Art.
So the only way to look the session and the table is use onstat -g opn to get
TID then, search for SID at sysrstcb?
Well, in the onstat -g opn report, you have the partnums of tables open by
each thread and there is also the rstcb address there so using either tid
or the rstcb address you can map the partnums from that report to
sessions. I just don't know how you do that using SMI tables.
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 Fri, Feb 22, 2013 at 9:27 AM, FELIPE CLEMENTE
<felipe@mcsoftware.com.br>wrote:
> Thanks Art.
>
> So the only way to look the session and the table is use onstat -g opn to
> get
> TID then, search for SID at sysrstcb?
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--f46d04479f29a3a9d404d6514acc
OK. Thanks !
I don't think it can be done via the SMI tables, I've looked and looked but
I never found a way to do it.
You have to do something like this (this will show who has a specific table
open, you can take the basic idea and modify it to do what you need to do)
#!/bin/ksh
tabname="some_table"
dbname="some_dbname"
# get partnum of the table we are looking for
partnum=$(print -- "output to pipe cat without headings select
substr(lower(hex(partnum)),3) from systables where tabname = '${tabname}'" |
dbaccess ${dbname} 2>/dev/null | grep -v "^$")
print -- "partnum [${tabname}]: 0x${partnum}"
# for each thread id that has the table open, get and print the session info
for tid in $(onstat -g opn | grep ${partnum} | awk '{print $1}')
do
sid=$(print -- "output to pipe cat without headings select
sid::char(11) from sysrstcb where tid = ${tid}" | dbaccess sysmaster
2>/dev/null | grep -v "^$")
sessinfo=$(print -- "output to pipe cat without headings select
trim(hostname) || ' ' || pid::char(32) from syssessions where sid = ${sid}"
| dbaccess sysmaster 2>/dev/null | grep -v "^$")
print -- " tid: ${tid} - sessid: ${sid} - ${sessinfo}"
done
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art
Kagel
Sent: Friday, February 22, 2013 8:49 AM
To: ids@iiug.org
Subject: Re: sysrstcb [29572]
Well, in the onstat -g opn report, you have the partnums of tables open by
each thread and there is also the rstcb address there so using either tid or
the rstcb address you can map the partnums from that report to sessions. I
just don't know how you do that using SMI tables.
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 Fri, Feb 22, 2013 at 9:27 AM, FELIPE CLEMENTE
<felipe@mcsoftware.com.br>wrote:
> Thanks Art.
>
> So the only way to look the session and the table is use onstat -g opn
> to get TID then, search for SID at sysrstcb?
>
>
>
>
****************************************************************************
***
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--f46d04479f29a3a9d404d6514acc
****************************************************************************
***
Forum Note: Use "Reply" to post a response in the discussion forum.
This is an easier way to identify prepared statements (and session IDs)
referencing a particular table, which I expect is what you need:
SELECT * FROM sysconblock WHERE LOWER(cbl_stmt) MATCHES '*table-name*'
Regards,
Doug Lawry
Thanks Andrew! Thanks Doug! Both works