CODEWITHSUNDEEP
db2statusdb2-luw
Pan
Pan

Reputation: 938

How to check the status of long running DB2 query?

I am running a db2 query that unions two very large tables. I started the query 10 hours ago, and it doesn't seem to finish yet.

However, when I check the status of the process by using top, it shows the status is 'S'. Does this mean that my query stopped running? But I couldn't find any error message.

enter image description here

How can I check what is happening to the query?

Upvotes: 5

Views: 27055

Answers (2)

Monish Patil
Monish Patil

Reputation: 1

You can try this command as well

db2 "SELECT agent_id, 
            Substr(appl_name, 1, 20) AS APPLNAME, 
            elapsed_time_min, 
            Substr(authid, 1, 10)    AS AUTH_ID, 
            agent_id, 
            appl_status, 
            Substr(stmt_text, 1, 30) AS STATEMENT 
     FROM   sysibmadm.long_running_sql 
     WHERE  elapsed_time_min > 0 
     ORDER  BY elapsed_time_min desc 
     FETCH first 5 ROWS only"

Upvotes: 0

mustaccio
mustaccio

Reputation: 19001

In DB2 for LUW 11.1 there is a text-based dsmtop utility that allows you to monitor the DB2 instance, down to individual executing statements, in real time. It's pre-11.1 equivalent is called db2top.

There is also a Web-based application, IBM Data Server Manager, which has a free edition with basic monitoring features.

Finally, you can query one of the supplied SQL monitor interfaces, for example, the SYSIBMADM.MON_CURRENT_SQL view:

SELECT session_auth_id,
       application_handle,
       elapsed_time_sec,
       activity_state,
       rows_read,
       SUBSTR(stmt_text,1,200)
FROM sysibmadm.mon_current_sql
ORDER BY elapsed_time_sec DESC
FETCH FIRST 5 ROWS ONLY

Upvotes: 6

Related Questions