Results 1 to 5 of 5
  1. #1
    Join Date
    May 2005
    Posts
    5

    Question Unanswered: How to get complete SQL-statement from sysmaster

    Hi, folks,

    following sql returns all running sessions for dateabase "pegasusdb" and host "BENONI3" :

    CONNECT TO 'sysmaster';

    SELECT t0.sqs_sessionid, t0.sqs_statement FROM syssqlstat AS t0, syssessions AS t1
    WHERE t0.sqs_dbname = 'pegasusdb'
    AND NOT t0.sqs_statement IS NULL
    AND t0.sqs_sessionid = t1.sid AND t1.tty = 'BENONI3'
    ORDER BY t0.sqs_sessionid DESC


    Nevertheless, the sql-statement will be truncated after 200 characters because of column width. Any ideas what can be used instead without truncating ?

    I'm wondering how onstat -g sql <sid> will do that ...

    Yours, Christof.

  2. #2
    Join Date
    Mar 2007
    Location
    Sofia, Bulgaria
    Posts
    6

    Lightbulb

    Hi Christof,

    With onstat you can do the following:
    onstat -g ses | grep BENONI3

    This will return all session ids from this host.
    Then you can write sample shell script like this one:

    $ vi onstat_g.sh

    # $1 hostname
    # $2 sleep time

    while true
    ses="$1"
    do
    for i in `onstat -g ses | grep $1 | awk '{print $1}'`
    do
    # the next line creates file onstat_g_ses containing the sql statement for current session
    onstat -g ses $i >> onstat_g_ses
    ses=$ses", "$i
    done
    # Just view some output - hostname, session id and current date
    echo $ses" - "`date`
    sleep $2
    done
    # END

    So you can execute this script (parameters are hostname and sleep time in seconds to wait before repeat the script):

    $ onstat_g.ksh BENONI3 60

    Hope this will help you.

    PS.
    You have to change mode to execute the script.
    You have to interrupt the script manually (Ctr+C)

  3. #3
    Join Date
    May 2005
    Posts
    5
    Hi kmstanev,

    thanks for your reply and support.

    Using your shell script could work, but nevertheless for technical reasons, I need a solution in SQL.

    Many greetings, Christof.

  4. #4
    Join Date
    Aug 2005
    Posts
    140
    Hi
    look at table sysconblock(cbl_stmt) or view syssqexplain(sqx_sqlstatement).

  5. #5
    Join Date
    Mar 2007
    Location
    Sofia, Bulgaria
    Posts
    6
    Sometimes query (select *) from these tables return no or too few recors.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •