Find table accessing information

Dear All, Can we get the following details from a TABLE in the DB ? 1. USER who is accessing the table 2. The HOSTNAME of the program where the table is accessing 3. PROGRAM NAME (e.g $INFORMIIXDR/bin/dbacces ) 4. SQL TYPE ( e.g INSERT/UPDATE/DELETE/SELECT etc ) Thanks! Best Regards, Indika ------------------------------ Indika Jinadasa ------------------------------

You can get most of this from the syssessions view in sysmaster, except for the first bit...who is accessing a table. I use "onstat -g opn" for this - and the output varies considerably by Informix version. I have not found an equivalent in sysmaster for this information (who is using a table), but maybe somebody has. In the past, I script the parsing of the output to get the threads that have the table open, and then use SQL to get the remaining information. One thing to note about "onstat -g opn" though is that even if a table is considered in use by a session, it does not mean that the session is actively using the table. For example it may have the table open in a cursor, or also app servers can hold on to a table. ------------------------------ Mike Walker xDB Systems, Inc www.xdbsystems.com ------------------------------

Indika: You could pipe onstat -g ses 0 into more or less and search for the table name. Alternatively in sysmaster: > select partnum from systabnames where dbsname = 'art' and tabname = 'extents';partnum 11534341 1 row(s) retrieved. > select * from sysopendb where odb_dbname = 'art';odb_sessionid 756 odb_odbno 0 odb_dbname art odb_iscurrent Y odb_islog Y odb_isansi N odb_isolation 2 odb_usrtype D odb_prior 5 odb_tmstamp 1 odb_lc_collate odb_dbflags 16384 1 row(s) retrieved. > select * from syssessions where sid = 756;sid 756 username art uid 1000 pid 162778 hostname Elezar-II tty /dev/pts/5 connected 1709237490 feprogram /home/art/bin/sqlcmd pooladdr 1172414528 is_wlatch 0 is_wlock 0 is_wbuff 0 is_wckpt 0 is_wlogbuf 0 is_wtrans 0 is_monitor 0 is_incrit 0 state 524289 > select * from syssqexplain where sqx_sessionid = 756;sqx_sessionid 756 sqx_sdbno 0 sqx_iscurrent Y sqx_executions 0 sqx_cumtime 0.49264663065 sqx_bufreads 101365 sqx_pagereads 16 sqx_bufwrites 0 sqx_pagewrites 0 sqx_totsorts 0 sqx_dsksorts 0 sqx_sortspmax -1 sqx_conbno 0 sqx_ismain Y sqx_selflag SQ_SELECT sqx_estcost 41419 sqx_estrows 876797 sqx_seqscan 1 sqx_srtscan 0 sqx_autoindex 0 sqx_index 0 sqx_remsql 0 sqx_mrgjoin 0 sqx_dynhashjoin 0 sqx_keyonly 0 sqx_tempfile 0 sqx_tempview 0 sqx_secthreads 0 sqx_sqlstatement SELECT * FROM extents 1 row(s) retrieved. 1 row(s) retrieved. ----------------------------- Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------ ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------

Thank you, Mike and Art,, How do you map PARTMUM from sysmaster and sysopndb to get only the specified table information? In your case 'extents' ? Thanks ! Best Regards, Indika ------------------------------ Indika Jinadasa ------------------------------

I beleive that in Art's example, sysopendb is used to determine the users connected to a database. You would need to parse the SQL statement in syssqexplain to identify the table being accessed. ------------------------------ Mike Walker xDB Systems, Inc www.xdbsystems.com ------------------------------

Checking prepared statements may be easier: onstat -g stm | grep -i table-name ;(The equivalent sysmaster table is "sysconblock".) That gives the session ID to get further details: onstat -g ses session-id ;(The equivalent sysmaster table is "syssessions".) ------------------------------ Doug Lawry Oninit Consulting ------------------------------

Indika: I'm not sure exactly what you are asking, but if Mike's interpretation is correct, then so is his response. In my case I was the only users at the time so it was easy and I knew what database and what table and there was only one session that I had to examine. In the more general case, there will be dozens or hundreds of users connected to your specific database. You will know what table you are interested in, however. So, you could: select sqx_serssionid from syssqexplain where sqx_sqlstatement matches '*my_table*';To see what sessions are currently accessing that specific table. Then query syssessions for each of those sessions to get the details of host, user, and application name. There are more details about the session in syssesprof, sysrstcb, and sysscblst. Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------

Thanks Mike , Doug and Art, Yes Art, Mike interpretation is correct, But I am not getting the required output for the following SQL. Do I need to configure any additional settings in my sever? select sqx_serssionid from syssqexplain where sqx_sqlstatement matches '*<TABLE name to get information> *';I used Doug method to get the required outputs successfully. Thanks!! Best Regards, Indika ------------------------------ Indika Jinadasa ------------------------------

Indika: The only thing I can think of is that sqx_sqlstatement is an CHAR[32000] it's possible that the statement you are searching for has the FROM clause beyond that 32K in the string so is not available through sqx_sqlstatement. ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------

You may want to remove the space after your table name because there might be other characters there, or nothing. Also this is not a case insensitive search - so you may consider using, for example, UPPER(sqx_sqlstatement) and a capitalized "*TABLE*" in your search string. This is a nasty query though if you have many concurrent queries running. Also add: and sqx_sessionid != dbinfo("sessionid") otherwise you will get your own query against syssqexplain returned! I am not sure your true objective here but consider that this will not show you if anybody is accessing the table through a view or a synonym, depending on the how they have been named. ------------------------------ Mike Walker xDB Systems, Inc www.xdbsystems.com ------------------------------