Informix

 View Only
Expand all | Collapse all

Find table accessing information

  • 1.  Find table accessing information

    Posted Thu February 29, 2024 02:26 PM
    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
    ------------------------------


  • 2.  RE: Find table accessing information

    IBM Champion
    Posted Thu February 29, 2024 02:48 PM

    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
    ------------------------------

    Original Message


  • 3.  RE: Find table accessing information

    IBM Champion
    Posted Thu February 29, 2024 03:30 PM

    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
    ------------------------------

    Original Message


  • 4.  RE: Find table accessing information

    Posted Thu February 29, 2024 10:10 PM

    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
    ------------------------------

    Original Message


  • 5.  RE: Find table accessing information

    IBM Champion
    Posted Thu February 29, 2024 11:10 PM

    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
    ------------------------------

    Original Message


  • 6.  RE: Find table accessing information

    IBM Champion
    Posted Fri March 01, 2024 06:12 AM

    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
    ------------------------------

    Original Message


  • 7.  RE: Find table accessing information

    Posted Fri March 01, 2024 01:47 AM
    Edited by Doug Lawry Fri March 01, 2024 01:48 AM

    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
    ------------------------------



  • 8.  RE: Find table accessing information

    Posted Fri March 01, 2024 07:08 AM

    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
    ------------------------------

    Original Message


  • 9.  RE: Find table accessing information

    IBM Champion
    Posted Fri March 01, 2024 08:48 AM

    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
    ------------------------------

    Original Message


  • 10.  RE: Find table accessing information

    IBM Champion
    Posted Fri March 01, 2024 09:18 AM

    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
    ------------------------------

    Original Message