What SQL statements are currently running on my DB2 database server?

To see some detailed info about which users are currently connected to your DB2 database server (including the SQL statements they are running), you can use the following query (tested with DB2 9.7, fixpack 3a):

    WITH a AS (SELECT a.application_handle, a.system_auth_id, a.application_name, a.client_wrkstnname, a.workload_name, a.workload_occurrence_state, b.activity_id, b.uow_id, b.local_start_time, b.activity_state, b.activity_type, b.total_cpu_time, b.rows_read, b.rows_returned, b.query_cost_estimate, b.direct_reads, b.direct_writes
                 FROM TABLE(wlm_get_service_class_workload_occurrences_v97(NULL, NULL, NULL)) a
                      LEFT JOIN TABLE(wlm_get_workload_occurrence_activities_v97(NULL, NULL)) b ON a.application_handle = b.application_handle),
         b AS (SELECT t.*
                 FROM TABLE(mon_get_activity_details(FOR EACH ROW OF (SELECT application_handle, uow_id, activity_id, NULL FROM a WHERE activity_id > 0))) t),
         c AS (SELECT x."activity_id" activity_id, x."uow_id" uow_id, x."stmt_text" stmt_text
                 FROM b,
                      XMLTABLE(XMLNAMESPACES(DEFAULT 'http://www.ibm.com/xmlns/prod/db2/mon'), '$m/db2_activity_details' PASSING XMLPARSE(DOCUMENT b.details) AS "m" COLUMNS
                                                                                                             "activity_id" INTEGER PATH 'activity_id',
                                                                                                             "uow_id" INTEGER PATH 'uow_id',
                                                                                                             "stmt_text" VARCHAR(1024) PATH 'stmt_text') x)
  SELECT a.*, TRANSLATE(c.stmt_text, ' ', CHR(10)) stmt_text
    FROM a
         LEFT JOIN c ON a.activity_id = c.activity_id AND a.uow_id = c.uow_id
   WHERE stmt_text IS NULL OR SUBSTR(stmt_text, 1, 40) <> 'WITH a AS (SELECT a.application_handle, '
ORDER BY application_handle, a.activity_id

This entry was posted in DB2. Bookmark the permalink.

6 Responses to What SQL statements are currently running on my DB2 database server?

  1. Test says:

    Excellent query… Thanks.

  2. k o says:

    Really useful query to provide the info required!

  3. Anderson says:

    Thanks man, this command was helpful. I had a problem to execute an insert in a table, one client was blocking another executions, with the query I could find who are causing the problem.

  4. harinath says:

    This query is awesome, specially for DB2 Beginners…Great Job! Thanks

  5. vinay says:

    Very useful and informative .. Thanks for sharing the knowledge!!

  6. Earthling says:

    Excellent, saved my day!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.