Informix

Informix

Connect with Db2, Informix, Netezza, open source, and other data experts to gain value from your data, share insights, and solve problems.

 View Only
Back to discussions
Expand all | Collapse all

Can we get the same output as onstat -g spa from SQL queries against sysmaster

  • 1.  Can we get the same output as onstat -g spa from SQL queries against sysmaster

    Posted Mon February 03, 2025 11:27 AM

    Hello community, does anybody tried to get the same or similar output as an SQL query from sysmaster?

     

    Thanks a lot

     

    Obsah obrázku text, klipart Popis byl vytvořen automaticky

    Milan Rafaj

     

    Unless stated otherwise above:
    Kyndryl Česká republika, spol. s r. o.
    Sídlo: V Parku 2308/8, Chodov, 148 00 Praha 4,
    IČ: 096 28 886
    Zapsaná v obchodním rejstříku, vedeném Městským soudem v Praze (oddíl C, vložka 339277)
    Registered address: V Parku 2308/8, Chodov, 148 00 Prague 4
    Company ID: 096 28 886
    Registered in the Commercial Register maintained by the Municipal Court in Prague (Part C, Entry 339277)


  • 2.  RE: Can we get the same output as onstat -g spa from SQL queries against sysmaster

    Posted Mon February 03, 2025 11:33 AM

    Hi Milan,

    onstat -g spa, quite expectedly, gives me the onstat syntax overview, so likely not what you're after ;-)

    What exact information is it that you want to get to?

    BR,

     Andreas



    ------------------------------
    Andreas Legner
    Informix Dev
    HCL Software
    ------------------------------

    Original Message


  • 3.  RE: Can we get the same output as onstat -g spa from SQL queries against sysmaster

    Posted Mon February 03, 2025 12:46 PM
    Hello Andreas, mispelling. Onstat -g spf i meant

    Milan Rafaj
    Kyndryl Česká Republika/Czech Republic

    Odesláno z/Sent from  Outlook / iOS



    Original Message


  • 4.  RE: Can we get the same output as onstat -g spa from SQL queries against sysmaster

    Posted Tue February 04, 2025 07:44 AM

    So, to get back to the original question: in how far are sysmaster:syssqltrace[_*]  SMI tables not good enough as equivalent to onstat -g spf?



    ------------------------------
    Andreas Legner
    Informix Dev
    HCL Software
    ------------------------------

    Original Message


  • 5.  RE: Can we get the same output as onstat -g spa from SQL queries against sysmaster

    Posted Tue February 04, 2025 08:38 AM

    Hello Andreas,

     

    SMI tables sysmaster:syssqltrace[_*] show me „the past" and are very useful for history analyses and similar.

    When I am trying to find reasons why systém is „slow" currently, onstat -g ses [Aa]ctive, onstat -g ses [Rr]unning, onstat -g spf and in 14.10 onstat -g top give me quick overview

    where the problem can be – so I would like to have similar SQL equivalent to customize output and integrate with InformixHQ as well

     

    Obsah obrázku text, klipart Popis byl vytvořen automaticky

    Milan Rafaj

    -- 

     

     




    Original Message


  • 6.  RE: Can we get the same output as onstat -g spa from SQL queries against sysmaster

    Posted Tue February 04, 2025 02:31 AM

    Hi Milan,

    What is the motivation for "tracing on" and monitoring the SQL behavior against sysmaster  ( -g spf)?

    Regards, Henri



    ------------------------------
    Henri Cujass
    leolo IT, CTO
    Germany
    IBM Champion
    henri.cujass@leolo.com
    ------------------------------

    Original Message


  • 7.  RE: Can we get the same output as onstat -g spa from SQL queries against sysmaster

    Posted Tue February 04, 2025 02:42 AM

    Hello Henri,

    Main motivation is to create a sensor/custom report in InformixHQ on currently long-running SQL queries – which is easily visible from onstat -g spf output if columns tottime and runtime are not 0 and have almost the same values and column execs has value of 0.

     

    SQL tracing gives me info only on already finished queries.

     

    Obsah obrázku text, klipart Popis byl vytvořen automaticky

    Milan Rafaj

    Senior Lead, Infrastructure/Cloud Architecture

    Kyndryl Consult

    +420 737 264 248

    www.kyndryl.cz

     

    Planned absence/Plánovaná nepřítomnost:

     

    Kyndryl Česká republika, spol. s r. o.

    Sídlo: Praha 4, Chodov, V Parku 2308/8, PSČ: 148 00,

    IČ: 14890992

    Zapsaná v obchodním rejstříku, vedeném Městským soudem v Praze (oddíl C, vložka 339277)

    Registered address: Prague 4, Chodov, V Parku, 2308/8, Zip code: 148 00

    Company ID: 14890992

    Entered in the Commercial Register maintained by the Municipal Court in Prague (Part C, Entry 339277)

    -- 

     

     




    Original Message


  • 8.  RE: Can we get the same output as onstat -g spa from SQL queries against sysmaster

    Posted Tue February 04, 2025 02:52 AM

    Hi Milan,

    nice idea, but you need to set SQL trace on to get these counts. A large impact on production systems - I don't recommend it.

    Regards, Henri



    ------------------------------
    Henri Cujass
    leolo IT, CTO
    Germany
    IBM Champion
    henri.cujass@leolo.com
    ------------------------------

    Original Message


  • 9.  RE: Can we get the same output as onstat -g spa from SQL queries against sysmaster

    Posted Tue February 04, 2025 02:57 AM

    Hello Henri – I know this, Tracing is enabled and most of time trace buffer is on defaults, increasing when looking for reason of current performance leaks.

     

    Obsah obrázku text, klipart Popis byl vytvořen automaticky

    Milan Rafaj

    Senior Lead, Infrastructure/Cloud Architecture

    Kyndryl Consult

    +420 737 264 248

    www.kyndryl.cz

     

    Planned absence/Plánovaná nepřítomnost:

     

    Kyndryl Česká republika, spol. s r. o.

    Sídlo: Praha 4, Chodov, V Parku 2308/8, PSČ: 148 00,

    IČ: 14890992

    Zapsaná v obchodním rejstříku, vedeném Městským soudem v Praze (oddíl C, vložka 339277)

    Registered address: Prague 4, Chodov, V Parku, 2308/8, Zip code: 148 00

    Company ID: 14890992

    Entered in the Commercial Register maintained by the Municipal Court in Prague (Part C, Entry 339277)

    -- 

     

     




    Original Message


  • 10.  RE: Can we get the same output as onstat -g spa from SQL queries against sysmaster

    Posted Tue February 04, 2025 06:04 AM

    Henri:

    Honestly, if you set SQLTRACE to LOW or MEDIUM and have a reasonable number of reasonably sized buffers configured, I have not seen any significant performance hit from turning SQLTRACE on.

    The single incident I did see was a client who crashed their host be configuring a million 1 MB buffers on a production server which, of course, caused the server's host to totally lock up trying to allocate a TB of memory. Otherwise, I have never seen more than a couple of percent of performance hit.

    What have you experienced that is different?

    Art



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

    Original Message


  • 11.  RE: Can we get the same output as onstat -g spa from SQL queries against sysmaster

    Posted Mon February 17, 2025 10:13 AM
    Hi,
    This may sound silly but I don’t hear a lot about containers for Informix (developer ed). I use it and it’s very easy to install, tear apart, and use, and it’s fast! I run Ifx containers “natively” on my laptop as well as via ssh from my iPad/laptops... on my Synology NAS (as a “server”) for personal education. I’d like to hear more folks espousing its virtues/tips/drawbacks. I used to run it as a VMWare VM but it was clunky and I’ve never renewed VMware as a result.


    Original Message


Related Content