How to check the status of long running DB2 query?

Question

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.

How can I check what is happening to the query?

score 5 · Answer 1 · answered Jan 18 '17 at 16:07

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

score 0 · Answer 2 · edited Aug 19 '20 at 09:49

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"

How to check the status of long running DB2 query?

2 Answers2