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?

Answer 1

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