onstat -g opn: What sysmaster tables will get me this info?

Hi y'all. I have a situation here where we are trying to track down high-impact users and what tables they are hitting. I already have a query on sysptprof that tells me (if crudely) what tables are getting hit the most but that doesn't tell me who's doing it. Here's a sample of output from onstat -g opn: rstcb 0x274c22eb8 tid 1219 isfd op_mode op_flags partnum ucnt ocnt lk ra partp ocnt ucnt 0 0x400 0x317 0x1700002 2 2 1 0x27d946028 226 226 1 0x2 0x3 0x1700002 2 2 0 0x27d946028 226 226 2 0x400 0x317 0x100004 2 2 1 0x276674530 958 958 3 0x2 0x3 0x100004 2 2 0 0x276674530 958 958 rstcb 0x274c237a8 tid 2728224 isfd op_mode op_flags partnum ucnt ocnt lk ra partp ocnt ucnt 0 0x400 0x317 0x1b00002 2 2 1 0x2769a1a78 413 413 1 0x2 0x3 0x1b00002 2 2 0 0x2769a1a78 413 413​ I have no problem mapping that partnum to a database:owner.name; that's easy enough in systabnames. But I see no object in sysmaster that I can easily recognize as relating to that rstcb. I can start my search with some thread information from systhreads. Note that systhreads.th_addr is not the address of the rstcb for the thread. For example, in my situation, hex(th_addr) for tid 1219 is 0x2781b7a18 (I removed the leading 0's), not the 0x274c22eb8 hown in the output. The next issue for me is how to associate a TID with a session ID so I can identify the user. I'm certain I did this YEARS ago but intervening years tend to erase some memories. The dregs of that memory whisper to me that I would start with a thread ID -> rsctcb -> session -> pid -> the running user program (using ps -ef | grep <pid>). Bottom line: What sysmaster tables/views do I need in order to get this information? This seems to be a 4-6 tables. Thanks for help here. ------------------------------ Jacob Salomon --- Nobody goes there anymore, it's too crowded. --Attr: Yogi Berra ------------------------------ #Informix

Hello. Please check this link. https://www.ibm.com/support/knowledgecenter/SSGU8G_14.1.0/com.ibm.adref.doc/ids_adr_0269.htm ------------------------------ Alexandre Marini ------------------------------

Sorry your onstat command is not on that list of manual. My mistake. Hope somebody else has an sql for fhat. Some tables are best described in sysmaster creation script. Have you checked it? ------------------------------ Alexandre Marini ------------------------------

Without digging can't you use sysrstcb, systcblst and sysactptndhr ------Original Message------ Hi y'all. I have a situation here where we are trying to track down high-impact users and what tables they are hitting. I already have a query on sysptprof that tells me (if crudely) what tables are getting hit the most but that doesn't tell me who's doing it. Here's a sample of output from onstat -g opn: rstcb 0x274c22eb8 tid 1219 isfd op_mode op_flags partnum ucnt ocnt lk ra partp ocnt ucnt 0 0x400 0x317 0x1700002 2 2 1 0x27d946028 226 226 1 0x2 0x3 0x1700002 2 2 0 0x27d946028 226 226 2 0x400 0x317 0x100004 2 2 1 0x276674530 958 958 3 0x2 0x3 0x100004 2 2 0 0x276674530 958 958 rstcb 0x274c237a8 tid 2728224 isfd op_mode op_flags partnum ucnt ocnt lk ra partp ocnt ucnt 0 0x400 0x317 0x1b00002 2 2 1 0x2769a1a78 413 413 1 0x2 0x3 0x1b00002 2 2 0 0x2769a1a78 413 413​ I have no problem mapping that partnum to a database:owner.name; that's easy enough in systabnames. But I see no object in sysmaster that I can easily recognize as relating to that rstcb. I can start my search with some thread information from systhreads. Note that systhreads.th_addr is not the address of the rstcb for the thread. For example, in my situation, hex(th_addr) for tid 1219 is 0x2781b7a18 (I removed the leading 0's), not the 0x274c22eb8 hown in the output. The next issue for me is how to associate a TID with a session ID so I can identify the user. I'm certain I did this YEARS ago but intervening years tend to erase some memories. The dregs of that memory whisper to me that I would start with a thread ID -> rsctcb -> session -> pid -> the running user program (using ps -ef | grep <pid>). Bottom line: What sysmaster tables/views do I need in order to get this information? This seems to be a 4-6 tables. Thanks for help here. ------------------------------ Jacob Salomon --- Nobody goes there anymore, it's too crowded. --Attr: Yogi Berra ------------------------------ #Informix

Jacob: I believe that sysrstcb has the beginnings of the open table list in the opentab column, but I do not know how to find the rest of the linked list of open tables associated with that session. The rstcb shown by onstat -g opn should be match sysrstcb.address. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.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 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. ------Original Message------ Hi y'all. I have a situation here where we are trying to track down high-impact users and what tables they are hitting. I already have a query on sysptprof that tells me (if crudely) what tables are getting hit the most but that doesn't tell me who's doing it. Here's a sample of output from onstat -g opn: rstcb 0x274c22eb8 tid 1219 isfd op_mode op_flags partnum ucnt ocnt lk ra partp ocnt ucnt 0 0x400 0x317 0x1700002 2 2 1 0x27d946028 226 226 1 0x2 0x3 0x1700002 2 2 0 0x27d946028 226 226 2 0x400 0x317 0x100004 2 2 1 0x276674530 958 958 3 0x2 0x3 0x100004 2 2 0 0x276674530 958 958 rstcb 0x274c237a8 tid 2728224 isfd op_mode op_flags partnum ucnt ocnt lk ra partp ocnt ucnt 0 0x400 0x317 0x1b00002 2 2 1 0x2769a1a78 413 413 1 0x2 0x3 0x1b00002 2 2 0 0x2769a1a78 413 413​ I have no problem mapping that partnum to a database:owner.name; that's easy enough in systabnames. But I see no object in sysmaster that I can easily recognize as relating to that rstcb. I can start my search with some thread information from systhreads. Note that systhreads.th_addr is not the address of the rstcb for the thread. For example, in my situation, hex(th_addr) for tid 1219 is 0x2781b7a18 (I removed the leading 0's), not the 0x274c22eb8 hown in the output. The next issue for me is how to associate a TID with a session ID so I can identify the user. I'm certain I did this YEARS ago but intervening years tend to erase some memories. The dregs of that memory whisper to me that I would start with a thread ID -> rsctcb -> session -> pid -> the running user program (using ps -ef | grep <pid>). Bottom line: What sysmaster tables/views do I need in order to get this information? This seems to be a 4-6 tables. Thanks for help here. ------------------------------ Jacob Salomon --- Nobody goes there anymore, it's too crowded. --Attr: Yogi Berra ------------------------------ #Informix

Paul replied privately but I think this should opened up for the sake of community knowledge: Paul suggested use of tables sysrstcb, systcblst and sysactptnhdr. That last one is must obviously related to the onstst -g opn option, since it relates some address to the active partition. I tried to run this query just to check if it is related to systhreads: select th.th_id, th.th_addr, act.address from systhreads as th,sysactptnhdr as act where act.address = th.th_addr Alas, no rows found. I had already tried to relate the tcb address on onstat -g act (and ath) to rstcb in the opn option. And I recall that the tcb and rstcb are not the same thing. So I would need a linkage to get from sysactptnhdr.address (which I presume is the rstcb address) to the tcb address in systhreads (systhreads.th.tcb). This would yield the tid, the integer first column in outstat -g act. That tidbit ( ahem ) would get me halfway to my goal, which is to chase up from the tid to a session id and up to the client program. So thanks, Paul, for this first step. ------------------------------ Jacob Salomon --- Nobody goes there anymore, it's too crowded. --Attr: Yogi Berra ------------------------------

Look at filefd column – I think that links to something that will provide the isfd from onstat –g opn and then the partnums Cheers Paul ------Original Message------ Paul replied privately but I think this should opened up for the sake of community knowledge: Paul suggested use of tables sysrstcb, systcblst and sysactptnhdr. That last one is must obviously related to the onstst -g opn option, since it relates some address to the active partition. I tried to run this query just to check if it is related to systhreads: select th.th_id, th.th_addr, act.address from systhreads as th,sysactptnhdr as act where act.address = th.th_addr Alas, no rows found. I had already tried to relate the tcb address on onstat -g act (and ath) to rstcb in the opn option. And I recall that the tcb and rstcb are not the same thing. So I would need a linkage to get from sysactptnhdr.address (which I presume is the rstcb address) to the tcb address in systhreads (systhreads.th.tcb). This would yield the tid, the integer first column in outstat -g act. That tidbit ( ahem ) would get me halfway to my goal, which is to chase up from the tid to a session id and up to the client program. So thanks, Paul, for this first step. ------------------------------ Jacob Salomon --- Nobody goes there anymore, it's too crowded. --Attr: Yogi Berra ------------------------------ #Informix

Art said: > The rstcb shown by onstat -g opn should be match sysrstcb.address. Alas, it does not. I had already run this query: select tcb.indx, tcb.address, act.address, act.partnum from sysrstcb as tcb,sysactptnhdr as act where tcb.address = act.address And again, no rows found. There may be an actual tcb address buried in the haystack of columns in sysactpartnhdr. And there must be a table with specific partition info referencing a column in this table; hdr kinda predicts a detail table someplace. Obviously my goal is to produce a query that will expose the session information of the tid listed in onstat -g opn. Chasing from the parnition num to systabnames will then be a walk in the park. I could bypass all this tcb and rstcb stuff, charming as it is, and get to my goal. ------------------------------ Jacob Salomon --- Nobody goes there anymore, it's too crowded. --Attr: Yogi Berra ------------------------------

I think they are two different rstcb control values in the mix, AFAIK there is a 'comment' in sysmaster.sql that hints at the fact ------Original Message------ Art said: > The rstcb shown by onstat -g opn should be match sysrstcb.address. Alas, it does not. I had already run this query: select tcb.indx, tcb.address, act.address, act.partnum from sysrstcb as tcb,sysactptnhdr as act where tcb.address = act.address And again, no rows found. There may be an actual tcb address buried in the haystack of columns in sysactpartnhdr. And there must be a table with specific partition info referencing a column in this table; hdr kinda predicts a detail table someplace. Obviously my goal is to produce a query that will expose the session information of the tid listed in onstat -g opn. Chasing from the parnition num to systabnames will then be a walk in the park. I could bypass all this tcb and rstcb stuff, charming as it is, and get to my goal. ------------------------------ Jacob Salomon --- Nobody goes there anymore, it's too crowded. --Attr: Yogi Berra ------------------------------ #Informix

Paul: I don't find any column in sysmaster named filefd. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.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 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. ------Original Message------ Look at filefd column – I think that links to something that will provide the isfd from onstat –g opn and then the partnums Cheers Paul #Informix

Jacob: No, the "address" column in sysactptnhdr is NOT the same as the address column in sysrstcb. The latter is the 'address' shown in onstat -u for a session and in 'rstdb' field in the onstat -g opn output associated with each session. The sysrstcb table is the session control block pseudo-table it contains the session address (aka the "rstdb" in some onstat reports and "address" in others - but not all "address" fields in onstat are rstcb), sid, and main thread tid along with other session specific information. The sysactptnhdr table is the dynamic version of sysptnhdr that only shows open tables, but it has only one entry for each table and has nothing to do with which sessions have that table open. The 'address' column in there is just the location in memory of the in-cache copy of the table's partition header page from disk. The "tcb" address is the Thread Contol Block structure and its address (different from the rstcb BTW) is stored in the systcblst table along with that thread's tid, so you COULD look up tcb info for a thread to the rstcb info for the session that owns that thread using the tid columns in sysrstcb and systcblst. But that still won't get you what you want. I believe that the fields in sysrstcb that I flagged, opentab and nextopen, are internal pointers to the list in memory of tables open by that session. However, I don't find where there is an exposed copy of that list in sysmaster. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.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 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. ------Original Message------ Art said: > The rstcb shown by onstat -g opn should be match sysrstcb.address. Alas, it does not. I had already run this query: select tcb.indx, tcb.address, act.address, act.partnum from sysrstcb as tcb,sysactptnhdr as act where tcb.address = act.address And again, no rows found. There may be an actual tcb address buried in the haystack of columns in sysactpartnhdr. And there must be a table with specific partition info referencing a column in this table; hdr kinda predicts a detail table someplace. Obviously my goal is to produce a query that will expose the session information of the tid listed in onstat -g opn. Chasing from the parnition num to systabnames will then be a walk in the park. I could bypass all this tcb and rstcb stuff, charming as it is, and get to my goal. ------------------------------ Jacob Salomon --- Nobody goes there anymore, it's too crowded. --Attr: Yogi Berra ------------------------------ #Informix

My bad – doing it from memory – isfd (sysrstcb) and sdb_isfd (sysdbsblock) ------Original Message------ Paul: I don't find any column in sysmaster named filefd. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.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 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. #Informix

Hi, Y'all. No, I have not forgotten the discussion; I just had to put it aside for a little. But here's what I *think* I can do, since everyone seems to be stumped on the original question. (Remember, that was: What sysmaster table holds the partition-related data for onstat -g opn?) Suppose I give up (for the moment) getting it all from sysmaster and start with onstat -g opn. The heading for each thread has a tid and an rstcb address. The latter, in retrospect, seems less important. But maybe I can use the tid to peek into systhreads, where column th_id does indeed match the tid in the section header. From there I thought I could look into sysrstcb and match up to syssesions. Alas, this deck is stacked against me. Here's the problem, at risk of opening a whole new can of worms: onstat -g opn IBM Informix Dynamic Server Version 12.10.FC12 -- On-Line (Prim) -- Up 5 days 10:20:34 -- 186675748 Kbytesrstcb 0x274c213e8 tid 451 isfd op_mode op_flags partnum ucnt ocnt lk ra partp ocnt ucnt 0 0x70000 0x403 0x2a000e5 1 0 0 0x28e7d8a38 0 2 1 0x70000 0x403 0x2a000ea 1 0 0 0x27c9d1028 0 3 2 0x70000 0x403 0x2a00065 1 0 0 0x27bfc45d0 0 3 3 0x70000 0x403 0x2b0012e 1 0 0 0x2829836e0 0 4 4 0x70000 0x403 0x2300010 1 0 0 0x27922d738 0 1 5 0x70000 0x403 0x2f0071f 1 0 0 0x2c9780028 0 1 ....​ Sure enough, that tid and address show up on onstat -g ath: $ onstat -g ath | grep 274e3f028451 274e3f028 274c213e8 3 cond wait ReadAhead 1cpu readahead_0 And indeed, I can find that TID in systhreads: select th_id, hex(th_addr) th_addr from systhreads where th_id = 451 th_id th_addr 451 0x0000000274e3f028​ Now, the th_addr does not match the rstrcb address on the -g opn output. However, the third column of onstat -g ath *does* match the rstcb of the opn output. But when I try to find that TID in sysrstcb, I'm outaluck: select tid, uid, sid, hex(address) address, hex(txp) txp from sysrstcb where tid = 451 No rows found. That is truly unfortunate, because I had thought I could use sysrstcb to tie together the TID with the session and chase that up to the pid of the client program and, stepping out to Shell commands, get the command line. SO the TID is sysrstcb seems unrelated to the th_id in systhreads. Bummer, unless someone has an insight that can unbummer this turn of events! Hey, I'm still open to suggestions. This has been an eye-opener! By the way, it's pretty d**n awkward trying to run a query to match a hex value provided in an onstat command. Is there an SQL built-in function that would match up the numeric values of hex values? ------------------------------ Jacob Salomon --- Nobody goes there anymore, it's too crowded. --Attr: Yogi Berra ------------------------------

In sysrstcb you have the mttcb ( "{ addr of mt thread cb }" ) column which seems to be related to the rstcb field of the "onstat -g opn" . Maybe it can be used for your objective. ------------------------------ Luis Marques ------------------------------

OK, Folks, I've let that sleep enough. Another search turned up a script names lockrep - by Jayakumar George, written in 1997, which seems to address this very question. I'm trying to beautify that code because it's tabbed so deeply but in the process, I've run across his usage of a script or program named listusers . If it's a Shell command I don't find it in my current Linux box. If it's an Informix command (highly unlikely), well, it gets the same "Command not found". It's also not a Shell or awk function in that script. While I can easily get all users logged in to the system with this command: w | tail --lines=+3|awk '{print $1}'​ it's uncertain to me if the listusers command to which Jayakumar refers is for logged in users or users connected to the server. Similarly, I can get a list of database users with something like: onstat -u | tail --lines=+6 | awk '{print $4}' |sort|uniq # (Not perfect but good enough for the illustration.) Is this "listusers" command familiar to anyone on this forum? Thanks to all who have tried to help. ------------------------------ Jacob Salomon --- Nobody goes there anymore, it's too crowded. --Attr: Yogi Berra ------------------------------