Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2011
    Posts
    4

    Unanswered: Extract last executed queries

    Hi all,

    I am looking for a way to extract from our db2 udb database (v. 9.5) the queries executed during the last 1-2 days.
    Reading on this forum I found a link to a "DB2 Monitor" tool that shows them, but what I need is to extract last the queries throught a view (I didn't find one that shows the last days history) or a clp command.

    Any suggestion?

    Thanks in advance,

    Alessandro.

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    you can start event monitoring
    see for detail
    Collecting information about database system events
    or use the get snapshot with dynamic sql option
    or use sysibmadm administrative views ..
    all detail at the same place
    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

  3. #3
    Join Date
    Sep 2011
    Posts
    4
    Hi przytula_guy,

    Thanks for your answer. I'd like to avoid to start an event monitor because it's our production database and I must follow a long iter.
    About the remaining solutions I already tried the views of sysibmadm schema but what I found was only views that shows just heavy query or cpu expensive query, executing queries, but nothing that lists all the queries of the last two/three days.

    So I tried

    get snapshot for dynamic sql on <mydatabase>

    but I get the following error and I don't know how to solve:

    SQL1096N The command is not valid for this node type.

    SQL1096N The command is not valid for this node type.

    Explanation:

    A command was issued on a node that does not support the command, or the
    system environment was found to be set up incorrectly for this node
    type. For example, a database was cataloged as LOCAL on a client node.

    The command cannot be processed.

    User response:

    Verify that the command and parameters are correct for the node type.
    Also verify that the environment where the command is to be processed is
    correct. Resubmit the command.
    How can I solve it?

    Thanks,

    Alessandro.

  4. #4
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    I executed this
    db2 get snapshot for dynamic sql on clsm <-- in db2inst1 := instance owner
    and no problem
    are you local in the instance owner...
    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

  5. #5
    Join Date
    Sep 2011
    Posts
    4
    I tried to execute the "get snapshot" statement directly on the server and it runs.
    Via the clp on my local machine I tried the one you suggested:

    Code:
    get snapshot for dynamic sql on <mydatabase> in <myinstanceowner>
    but I suppose I misunderstood your suggestion because it doesn't run, I get an error about "IN" clause no expected in that position.

    Looking at the result of the command (on the server) it seems the same result I get with:

    Code:
    select * from SYSIBMADM.SNAPDYN_SQL
    Isn't it?

  6. #6
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    yes - get snapshots can be produced with dynamic admin views doing similar operation as get snapshot
    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

  7. #7
    Join Date
    Sep 2011
    Posts
    4
    Ok thanks, when I saw it for the first time I was snared by the "snapshot_timestamp" field..
    My problem is now that the view contains only data related to the executed query. Is there a way to link it to the user that launches it, the ip of its machine, and the real time the query was executed?

  8. #8
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    with event monitor you get this - similar to get snapshot for application agentid xxx

    DUOW correlation token = 10.104.116.63.40191.110908065504
    Application name = db2jcc_application
    Application ID = 10.104.116.63.40191.110908065504
    UOW start timestamp = 09/08/2011 08:55:04.942245
    UOW stop timestamp = 09/08/2011 08:55:05.768879
    UOW completion status = Committed - Commit Statement
    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

Tags for this Thread

Posting Permissions

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