Informix

Expand all | Collapse all

HQ - Current SQL statement or Last parsed SQL statement

  • 1.  HQ - Current SQL statement or Last parsed SQL statement

    Posted 19 days ago

    Informix HQ does not display "Current SQL statement" or "Last parsed SQL statement" for session unless sql trace is turned on.
    Is this bug or feature?
    Some customers have 100.000s statements per second and turning sql trace on will keep just last 0,05 seconds of sql commands (unless you put 400GB for sql trace buffer - so you have to kill your server to monitor it - call me crazy but this is crazy)

    Why HQ does not use sysmaster tables (as onstat -g sql does)?
    Regards
    Hrvoje



    ------------------------------
    Hrvoje Zokovic
    ------------------------------


  • 2.  RE: HQ - Current SQL statement or Last parsed SQL statement

    Posted 19 days ago
    Hrvoje:

    If your site has that level of SQL traffic, then you are correct, SQL Trace isn't going to be particularly useful. An onstat -g ses or sql will show the "current" and "last" but that's an instantaneous picture and no better than the SQL Trace except that it will show the SQL for less active sessions better. Adding that capability to InformixHQ isn't going to help much, though I do agree that it should be there!

    In this case you really need something like iWatch or SQL PowerTools, at least as long as most of your traffic is through network connections.

    Art


    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------



  • 3.  RE: HQ - Current SQL statement or Last parsed SQL statement

    Posted 19 days ago
    Art, yes that is correct. And you predicted right - customer have users who are less active (they are doing some ad hoc queries) and they used OAT for finding their session so they can terminate it if necessary.
    Hrvoje


    ------------------------------
    Hrvoje Zokovic
    ------------------------------



  • 4.  RE: HQ - Current SQL statement or Last parsed SQL statement

    Posted 18 days ago
    Hrvoje:

    So, what the customer needs is the ability to locate sessions that have been inactive for a period of time so they can kill the session? Here: 

    select s.*, t.tid, current - dbinfo( 'utc_to_datetime', last_run_time ) idle_time
    from syssessions as s, sysrstcb as r, systcblst as t
    where s.sid = r.sid
      and r.mttcb = t.address  
      and current - dbinfo( 'utc_to_datetime', last_run_time ) > 5.0;    

    You can join in sysmaster:syssqexplain to see the current SQL.


    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------



  • 5.  RE: HQ - Current SQL statement or Last parsed SQL statement

    Posted 18 days ago

    Thanks Art - I know that, their DBA knows that. But, as I explained some of users (app/dba users) are making some ad hoc queries. And when they realize it is lasting for a long time, they open OAT, locate their session (looking for query) and then terminate that session. This is simple explanation, there are some more events when they terminate their own session. And there is task for killing long lasting sessions (if I remember right). But sometimes they want session to last as long as it takes.

    So, I know I can do it, DBA can do it - but then they would have one person assigned to killing sessions - since sometimes it is urgent to kill such a session and DBA is doing other stuff besides DBA and I have to jump through couple of vpn-s, log to vpn, ask for access, ....

    The simplest way is that users kill their own session. They are not even logged on DB server - they use different frontend tools.

    I think if HQ will not show these data they will have to turn sqltrace on for that users - but I miss that from OAT :)
    Hrvoje



    ------------------------------
    Hrvoje Zokovic
    ------------------------------



  • 6.  RE: HQ - Current SQL statement or Last parsed SQL statement

    Posted 18 days ago
    Agreed, this feature should be there. HQ is definitely still a work-in-progress and not close to complete yet.

    Put in the RFE and promote that here so others know to vote for it.

    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------



  • 7.  RE: HQ - Current SQL statement or Last parsed SQL statement

    Posted 19 days ago
    Look at Iwatch - if configured correctly it will have zero impact on the database server and is more than capable of capturing that sql rate 

    Cheers
    Paul

    Paul Watson
    Oninit LLC
    +1-913-387-7529
    www.oninit.com
    Oninit®️ is a registered trademark of Oninit LLC





  • 8.  RE: HQ - Current SQL statement or Last parsed SQL statement

    Posted 19 days ago
    Paul, customer does not need capturing all busy sql. They used to use OAT to find session (less active sessions generated mostly by ad hoc queries) and to terminate such session. Now, flash is gone - OAT is gone.
    Hrvoje

    ------------------------------
    Hrvoje Zokovic
    ------------------------------



  • 9.  RE: HQ - Current SQL statement or Last parsed SQL statement

    Posted 19 days ago
    And ofc I know they can sqltrace just users doing ad hoc queries (so there is much less recorded statements per second) but IMHO Current SQL statement and/or Last parsed SQL statement should be displayed.
    Hrvoje

    ------------------------------
    Hrvoje Zokovic
    ------------------------------



  • 10.  RE: HQ - Current SQL statement or Last parsed SQL statement

    Posted 18 days ago

    Hi Hrvoje,

    we have this feature in our Admin-Scout. It is possible to sort on every column and such sessions are killable directly. 

    Here the report in our online demo: https://scout-demo.cursor.de/openadmin/index.php?act=home&do=sessexplorer

    Regards
    Erik



    ------------------------------
    Erik Stahlhut
    ------------------------------



  • 11.  RE: HQ - Current SQL statement or Last parsed SQL statement

    Posted 16 days ago
    Hi Eric,
    does Admin-Scout use sql trace as well? As I explained earlier, sql trace in very busy instance (100.000s ops per second) is not useful unless you have TBs of memory.
    I know Admin-Scout is great tool - but customer had that functionality in OAT so they expected to see it in HQ (especially now when flash == OAT is dead).
    Hrvoje

    ------------------------------
    Hrvoje Zokovic
    ------------------------------



  • 12.  RE: HQ - Current SQL statement or Last parsed SQL statement

    Posted 15 days ago
    Hi Hrvoje,

    sql trace is not necessary for this report, it works similar to OAT. 
    We analyse the entries in sysconblock and if you have tracing on, syssqltraces. The effect is, if you have tracing disabled you just see only the current statement.  
    Unfortunately we recognized that in some newer versions (14.10) no statements are stored in sysconblock (bug or feature?) In 12.10 it works fine.



    Greetings
    Erik