Determine How Long a Query Has Been Running
Query start time is stored in the imadb database.
To see when a query started
1. Connect to the imadb database:
sql imadb
2. Run this query:
SELECT server, session_id, real_user, db_name, session_terminal, VARCHAR(_DATE(query_start_secs)) + ' ' + VARCHAR(_TIME(query_start_secs)) + VARCHAR(':') + VARCHAR(MOD(query_START_secs,60))
FROM ima_server_sessions
WHERE db_name NOT IN ('', 'imadb')\g
To see how long a query has been running
1. Connect to the imadb database:
sql imadb
2. Run this query:
SELECT server, db_name, session_query, (BIGINT((CURRENT_TIMESTAMP - TIMESTAMP_WITH_TZ('1970-01-01 00:00:00+00:00'))/INTERVAL '1' second) - query_start_secs) AS elapsedsecs
FROM ima_server_sessions
WHERE db_name NOT IN ('', 'imadb')\g
Note: The longest running query in the system is shown.
Note: If a query is finished, it will still be returned in the list. In this case, we can join another table and ignore queries in BIO CS_EVENT_WAIT state. The following query will show all queries that are actually running (rather than ran, finished, and waiting for user input, such as \q):
SELECT db_name, session_query, (BIGINT((CURRENT_TIMESTAMP - TIMESTAMP_WITH_TZ('1970-01-01 00:00:00+00:00'))/INTERVAL '1' second) - query_start_secs) AS elapsedsecs
FROM ima_server_sessions s,ima_server_sessions_extra e
WHERE s.session_id = e.session_id and s.effective_user != ''
AND (e.session_state != 'CS_EVENT_WAIT' OR e.session_wait_reason != 'BIOR')
AND db_name NOT IN ('', 'imadb')
Notes:
• The start time is the start of execution, not parsing/optimization.
• The start time is seconds since 1-Jan-1900 00:00:00. The functions _DATE4 and _TIME can be used to perform time calculations; or use an ANSI interval in seconds.
• The time is not set to zero when the query ends.