Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2006
    Posts
    31

    Unanswered: get snapshot for dynamic sql

    Hi all,
    OS-AIX
    DB2 - V8, FixPak7
    I am trying to find the longest running query using the command ..
    get snapshot for dynamic sql on <db_name>
    The above command is listing all the quries on the db but I need to find out the longest running query..
    Thanks in advance..

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You could use the snapshot functions in a query. Something like this:

    select * from table(snapshot_dyn_sql('MYDB',-1)) as x order by total_exec_time desc fetch first 5 rows only

    Andy

  3. #3
    Join Date
    May 2010
    Posts
    88
    Hi,
    Continuing the very old post instead of creating a new. As recommended, we only enable switches as and when required. If asked to kill the longest running query so as to bring down the CPU utilization, I will first turn on the monitor switch for statements and timestamp (on by default). But this will not capture data for work in execution. How do I find the longest running query in such case?

    Thanks,
    db2cap

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by db2cap View Post
    How do I find the longest running query
    Uhm...
    Code:
    SELECT * FROM SYSIBMADM.LONG_RUNNING_SQL
    ORDER BY ELAPSED_TIME_MIN DESC
    ?

    How's the "long running" part related to CPU utilization though?

  5. #5
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    DB2 - V8, FixPak7
    not all sysibmadm views are available if any ....
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  6. #6
    Join Date
    May 2010
    Posts
    88
    przytula_guy: caught the point...
    Actually we still have some of our databases on V8 which unfortunately does not have those easy to manage administrative views.

    n_i, you did not got the pitch of my point actually. Even if I use SYSIBMADM.LONG_RUNNING_SQL view, will turning on the monitor switches immediately start gathering the data which otherwise is not collected for the running queries?

    Kindly refer the below post for better understanding:
    http://www.dbforums.com/db2/1667785-...ml#post6508598

Posting Permissions

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