Results 1 to 13 of 13
  1. #1
    Join Date
    Mar 2008
    Posts
    7

    Unanswered: Find Active Queries in real-time

    Hi. I am writing a small monitoring tool for our operators that can monitor our DB2 databases in the organization. I know there are plenty of tools out there but I need something very customized that will be plugged into one of our main monitoring systems.

    What I need is actually a query that returns all the active sessions, and for each of them I am mostly interested in :

    * Agent id (to locate the process in the Os)
    * sql statement
    * Amount of io it is doing
    * Amount of cpu it is doing
    * The program that ran it
    ..

    If not all information is available - I may settle for less.
    Just to emphasize, I need this information in real-time. meaning that if it is running now, I need to see it now. I am going to poll that information every 5-10 seconds.

    Please help me. I am out of ideas.

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    You can use the snapshot functions to query DB2 to retrieve such information.

    p.s: What would you need the process ID for? The application ID is much more helpful. Besides, DB2 LUW is using a threaded process model since V9.5 and, therefore, you have much less processes now.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Mar 2008
    Posts
    7
    hi stolze

    Thanks for your answer.

    Is this snapshot stuff gives you information in real-time for current active queries or just historical ? I see that there is a timestamp column in those tables that represents time in the past.

    Can you send me a syntax of how I can retriev this data please ?
    I am not that familiar with DB2.

    best regards,
    Elimeli

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    The snapshot table functions are documented in the DB2 manual: http://publib.boulder.ibm.com/infoce...v9r5/index.jsp

    You use them like any other table function.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Jan 2008
    Posts
    45
    I am planning to do the same..May be we can do it together..The following select would give you the current running statements..

    select stmt_stop,stmt_text from table (snapshot_statement(' ',-1)) as SNAPAPP where stmt_stop is NULL

  6. #6
    Join Date
    Mar 2008
    Posts
    7
    hi db2rocks

    thanks for the answer.

    I tried this query but for some reasone it returns nothing while I have a query running for sure. I have 6 lines in the table functions but all the STMT_TEXT is empty for them all.

    Should I change any configuraiton in the DB2 to have the statement written there ?

    thanks

  7. #7
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Following the link in the documentation leads to a page that explains what STMT_TEXT contains: http://publib.boulder.ibm.com/infoce.../r0001352.html

    Does any of this apply in your scenario?
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  8. #8
    Join Date
    Mar 2008
    Posts
    7
    Hi Stolze

    I read all the document very cerfully - and still I have a problem with it.

    Here is the scenario I am testing
    --------------------------------

    I logong to the db2 and connect to teh sample database

    I than run a very long running query that consumes a lot of CPU but never finishes - select count(*) form employee,employee,employee,employee,employee,emplo yee,employee,employee. - just a simple cartesian join that does nothing rather than burning my CPU

    I go to the snapshot tables and look for the tatement which is currently active - and I get nothing - just "---"


    select stmt_text from table (snapshot_statement(' ',-1))

    What am I doing wrong ?

  9. #9
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    I tried this myself, and a regular "GET SNAPSHOT" command shows the statement text while the table function does not. In my opinion, this looks very much like a bug in DB2 or the manual (in case that something else has to be done first). You may want to open a PMR to get this resolved.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  10. #10
    Join Date
    Mar 2008
    Posts
    7
    Hi Stolze

    thanks for the answer. I have 3 questions :

    1. I am currently working with Version 9.5. is this table fucntion works in version 8 as well ?
    2. Should I activate the statements monitoring for using the table function or not ? if yes - is this a lot of overhead ?
    3. I am still not a customer of DB2. Just writing a software for the company I work for (I am self employed). Can I open this PMR ?

    Again - thank you very much for the help. I really appraciate it.
    Best regards,

    Elimeli

  11. #11
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    1. I am currently working with Version 9.5. is this table fucntion works in version 8 as well ?
    Have you checked the manuals yourself already?

    2. Should I activate the statements monitoring for using the table function or not ? if yes - is this a lot of overhead ?
    Event monitors are something different than snapshots.

    3. I am still not a customer of DB2. Just writing a software for the company I work for (I am self employed). Can I open this PMR ?
    I don't know what your process is in this case. Since you are a contractor, you may want to work through the company that contracted you.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  12. #12
    Join Date
    Mar 2008
    Posts
    7
    Hi Stolze

    I am a bit confused with question #2.

    I copy this from one of the redbook documents :

    SNAPSHOT_STATEMENT Application information regarding statements for the
    applications connected to the database on the partition.
    This includes the most recent SQL statement executed (if
    the statement monitor switch is set).


    so should I activate it ? and what is the expected overhead of this ?

    thanks

  13. #13
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    I didn't phrase my response precisely enough; sorry. There are monitors (like the statement monitor) and there are events. Both are different things. You should activate the statement monitor - but do not use an event monitor, for which you'd have to use the CREATE EVENT MONITOR command (http://publib.boulder.ibm.com/infoce.../r0000915.html)

    And yes, you should activate the statement monitor and verify to see if the statement text is available. I guess that this is the reason why you didn't get the desired information and activating this monitor should close this gap. (I assume that the GET SNAPSHOT command does this activation implicitly.)

    As for the overhead, the usual answer is "it depends". It depends on your system and workload, of course. You should measure it in your specific environment. I believe that we are talking about a very low single-digit percentage of overhead only. I recommend you have a look at the manual for this: http://publib.boulder.ibm.com/infoce.../c0005719.html
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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