Informix

Expand all | Collapse all

Long Running Query thru shell Script

  • 1.  Long Running Query thru shell Script

    Posted 16 days ago
    Hello All,

        I need to write a shell script to find long running Query in Informix 11.70. (We will upgrade with 14.10 in August 2021).

        As it includes three main steps 

       1.  Search onstat -g act -g -x (x= 1, 2, 3, 4, ....)
       2. Check tcb column with Non ZERO value and run onstat -u | grep <Non ZERO value>o
       3. Now get session id and run onstat -g ses <ID> 


       But we can get multiple rows while running onstat -g act -r 1 ...... 
        So can someone please guide me how can I write script to find the exact session id which is causing the Performance.



    Thanks
    Amit

    ------------------------------
    AMIT PATEL
    ------------------------------


  • 2.  RE: Long Running Query thru shell Script

    Posted 16 days ago
    Hi Amit,

    I strongly guess you're not going to be happy with this approach.

    An easier one would be capturing a series of 'onstat -g ses 0' outputs while your problem is going on, then searching and comparig them for sessions that at more active than others, or for SQL that frequently shows up in active sessions.   What qualifies as 'active' is up for debate:  for sure any 'running' threads, but also 'ready' ones, esp. in cpu-bound situations, or 'IO Wait' or 'bufwait' ones in disk-i/o-bound situations.

    In more recent versions you could use 'onstat -g ses active'  or a number of other, more specific filters for the same.

    BR,
     Andreas

    ------------------------------
    Andreas Legner
    ------------------------------



  • 3.  RE: Long Running Query thru shell Script

    Posted 13 days ago
    I have written the following script to capture queries running more than a minute.

    echo "execute function task('set sql tracing on',1000,2,'low','global')"|dbaccess sysadmin
    >logs/slow_queries.out-${dt}
    while true; do

    echo "unload to logs/slow.unl select dbinfo('UTC_TO_DATETIME',sq.sql_finishtime),s.hostname,s.username,s.sid,sq.sql_statement,sq.sql_totaltime from syssqltrace sq ,syssessions s where s.sid=sq.sql_sid and sq.sql_runtime>60" |dbaccess sysmaster
    cat logs/slow.unl >>logs/slow_queries.out-${dt}
    sleep 1
    done

    ------------------------------
    Khurram Shahzad
    ------------------------------