What's DB2 doing? Find long-running SQL with MON_CURRENT_SQL

Show me what's taking so long

An application is slow. A report has been running for ages. Someone says, "It's probably the database."

Before we start blaming indexes, it helps to see what the database is actually doing.

This query is a useful starting point on DB2 for Linux, UNIX and Windows (LUW):

SELECT
    application_handle,
    elapsed_time_sec,
    activity_state,
    rows_read,
    query_cost_estimate,
    SUBSTR(stmt_text, 1, 2000) AS stmt_text
FROM sysibmadm.mon_current_sql
WHERE activity_state = 'EXECUTING'
ORDER BY elapsed_time_sec DESC
FETCH FIRST 50 ROWS ONLY;

It shows up to 50 executing activities, longest elapsed time first, with enough SQL text to help identify them. The stmt_text alias gives the shortened SQL column a readable heading.

Use it while the problem is happening. It gives you leads to investigate; appearing at the top doesn't prove that a statement is faulty.

These queries target DB2 LUW. Confirm the view and column availability on your installed release; DB2 for z/OS and IBM i use different monitoring interfaces.

What the query does

SYSIBMADM.MON_CURRENT_SQL exposes unfinished activities in the connected database, including static and dynamic SQL. Its metrics are aggregated across database members. You need appropriate authorization, such as SELECT on the view; querying application tables alone doesn't establish that access. See IBM's MON_CURRENT_SQL reference.

The WHERE clause keeps EXECUTING activities. ORDER BY ... DESC puts the oldest first; FETCH FIRST 50 ROWS ONLY caps the returned rows. It limits this monitoring result, not the number of statements DB2 may execute or how long they may run.

EXECUTING doesn't mean constantly using CPU

IBM's activity lifecycle documentation explicitly says an activity encountering a lock wait is reported as EXECUTING. Other states include IDLE, where the coordinator waits for a client request, and QUEUED, where work is waiting for admission through a queue.

Elapsed time is therefore a clue about duration, not a CPU measurement. A statement near the top might be processing a large result, waiting on another transaction, or doing work that is entirely reasonable for a batch job.

Cursor activities also last until the cursor closes. Their lifetime can include time between client fetches. Looking at activity age without its state and context can send you after the wrong suspect.

Rows read and estimated cost need context

ROWS_READ counts table rows read, rather than rows returned to the caller. An aggregate can inspect millions of rows and return one answer. The counter also excludes index accesses, so low rows read doesn't prove that little work happened. IBM explains these distinctions in the rows_read monitor element.

Lots of reads for a small result can justify examining joins, predicates, statistics and the access plan. It isn't an instruction to create an index immediately: a report that genuinely needs most of a table may reasonably scan it.

QUERY_COST_ESTIMATE is an optimizer estimate expressed in timerons, a relative cost unit. It isn't seconds, milliseconds or a completion percentage. IBM's cost-estimation discussion explains why estimated cost doesn't predict elapsed time: runtime contention and other factors affect duration.

A cheap-looking statement can still wait behind another transaction. A costly statement may be expected reporting work. Treat cost as supporting evidence alongside observed behaviour.

Variation 1: only show statements older than a minute

Add an elapsed-time threshold to reduce noise:

SELECT
    application_handle,
    elapsed_time_sec,
    activity_state,
    rows_read,
    SUBSTR(stmt_text, 1, 2000) AS stmt_text
FROM sysibmadm.mon_current_sql
WHERE activity_state = 'EXECUTING'
  AND elapsed_time_sec >= 60
ORDER BY elapsed_time_sec DESC
FETCH FIRST 50 ROWS ONLY;

Sixty seconds is an example, not a universal definition of slow. Choose a threshold that makes sense for the operation you're investigating. A minute might be extraordinary for a record lookup and unremarkable for a large report.

Variation 2: include idle and queued activities

Remove the state filter when the executing-only list doesn't explain the symptom:

SELECT
    application_handle,
    elapsed_time_sec,
    activity_state,
    activity_type,
    SUBSTR(stmt_text, 1, 2000) AS stmt_text
FROM sysibmadm.mon_current_sql
ORDER BY elapsed_time_sec DESC NULLS LAST
FETCH FIRST 50 ROWS ONLY;

The view documents a null elapsed time for an activity queued before it has run. NULLS LAST keeps those entries from displacing activities with a known duration at the top. To focus specifically on admission queues, add WHERE activity_state = 'QUEUED' instead.

This still lists activities, not every connection. An idle connection without an unfinished activity needn't appear.

Variation 3: find activities reading the most rows

Sort by reads when you're investigating excessive data access rather than age:

SELECT
    application_handle,
    elapsed_time_sec,
    rows_read,
    rows_returned,
    DECIMAL(
        DOUBLE(rows_read) / NULLIF(DOUBLE(rows_returned), 0),
        20, 2
    ) AS reads_per_row_returned,
    SUBSTR(stmt_text, 1, 2000) AS stmt_text
FROM sysibmadm.mon_current_sql
WHERE activity_state = 'EXECUTING'
ORDER BY rows_read DESC NULLS LAST
FETCH FIRST 50 ROWS ONLY;

DOUBLE avoids integer division. NULLIF prevents division by zero, leaving the ratio null when no rows have been returned. Keep the raw counters visible so that null doesn't hide a large amount of reading.

For a running activity, the result is provisional. A sort or aggregate might do substantial work before returning anything. Compare observations of the same activity over time and inspect its plan before deciding the ratio indicates a problem.

Variation 4: identify the application behind the SQL

Connection metadata makes a list of handles easier to investigate:

SELECT
    coord_member,
    application_handle,
    uow_id,
    activity_id,
    application_name,
    client_applname,
    session_auth_id,
    elapsed_time_sec,
    activity_state,
    SUBSTR(stmt_text, 1, 2000) AS stmt_text
FROM sysibmadm.mon_current_sql
WHERE activity_state = 'EXECUTING'
ORDER BY elapsed_time_sec DESC
FETCH FIRST 50 ROWS ONLY;

These identity columns are also documented in the view reference. Retain the member, handle, unit-of-work and activity IDs when comparing captures: one connection can perform multiple activities.

To focus on a connection you just identified, replace the state predicate with, for example:

WHERE application_handle = 12345

12345 is a placeholder. To restrict by database login instead, use WHERE session_auth_id = 'MAXIMO', replacing the name with the actual authorization ID.

In Maximo, pooled JDBC connections may share a database login. That login won't by itself identify the Maximo user, browser session or cron task. Correlate the capture time and SQL with application logs and the operation being reproduced.

Variation 5: search for SQL mentioning a table

If you're investigating work orders, search the statement text:

SELECT
    application_handle,
    elapsed_time_sec,
    activity_state,
    SUBSTR(stmt_text, 1, 2000) AS stmt_text
FROM sysibmadm.mon_current_sql
WHERE activity_state = 'EXECUTING'
  AND LOCATE('WORKORDER', UPPER(stmt_text)) > 0
  AND application_handle <> SYSPROC.MON_GET_APPLICATION_HANDLE()
ORDER BY elapsed_time_sec DESC
FETCH FIRST 50 ROWS ONLY;

This searches the full available statement text, even though the displayed preview is shorter. The final predicate excludes your own monitoring connection, whose query contains the search term too. IBM documents MON_GET_APPLICATION_HANDLE, including the authorization needed to call it.

It's a text search, so comments and literals can match; access through a differently named view may not. For more on searching CLOB values, see DB2 LOCATE.

Variation 6: find who is waiting for whom

For table-level lock inspection, lock modes and tracing a blocking connection, see Identify table locks and blocking transactions in DB2.

When duration suggests a possible blocking problem, query the lock-wait view:

SELECT
    req_application_handle AS waiting_handle,
    hld_application_handle AS holding_handle,
    lock_wait_elapsed_time AS wait_seconds,
    tabschema,
    tabname,
    lock_mode_requested,
    SUBSTR(req_stmt_text, 1, 2000) AS waiting_sql,
    SUBSTR(hld_current_stmt_text, 1, 2000) AS holder_current_sql
FROM sysibmadm.mon_lockwaits
ORDER BY lock_wait_elapsed_time DESC
FETCH FIRST 50 ROWS ONLY;

IBM's MON_LOCKWAITS reference distinguishes the requester from the lock holder and defines the elapsed wait in seconds. This view needs its own appropriate authorization.

The holder's current statement isn't necessarily the statement that acquired the lock. An earlier statement in its transaction may be responsible. Investigate the transaction and application behaviour rather than assuming the visible SQL is the cause.

Use it as a starting point

Capture the time and activity identifiers, reproduce the slow operation, then run the query again after a sensible interval. Look for the same activity persisting and for counters changing. Capture the full stmt_text for a specific activity when the 2,000-character preview cuts off the useful predicates; your SQL client may also impose a CLOB display limit.

This is a live view, not a historical slow-query log. Short statements can finish between captures. For a problem that has already passed, use an established monitoring capture or investigate package-cache metrics with MON_GET_PKG_CACHE_STMT. Those metrics aggregate executions of cached statements, depend on metric collection, and aren't a complete historical record.

If metrics are missing or surprising, check the installed release's monitoring configuration and metric definitions. Don't interpret an unavailable value as proof of no work. Avoid tight polling loops, and review captured SQL for sensitive literals before sharing it.

The useful sequence is: find the activity, identify its owner, distinguish work from waiting, then investigate the SQL or transaction with evidence. The oldest row gives you somewhere to start asking questions.