Monday, January 5, 2015

Netezza Query Elapsed time


Even wanted to know or monitor the elapsed time of a currently running query ? - here is the query to do so

Here in this query are trying to figure out if there is any active query (status = 'active') that is running for long than 15 minutes ('00:15:00' ::INTERVAL DAY TO MINUTE)


select
CURRENT_TIMESTAMP - QS_TSTART ElapsedTime,
B.USERNAME,B.DBNAME, B.COMMAND
from _v_qrystat A
join _v_session B on A.QS_SESSIONID = B.id
where B.status = 'active'
and ( CURRENT_TIMESTAMP - A.QS_TSTART ) > '00:15:00' ::INTERVAL DAY TO MINUTE ;

 

This above query also show how to use "INTERVAL" data types which I discovered first in Oracle than in any other RDBMS.


This you can further extend for monitoring and alerting purposes, using shell and cron.

About Me

By profession I am a Database Administrator (DBA) with total 13 yrs. of experience in the field of Information Technology, out of that 9 yrs as SQL DBA and last 3 years in IBM System i/iSeries and DB2 LUW 9. I have handled Developer, plus production support roles, and I like both the roles. I love and live information technology hence the name "Techonologyyogi" Apart from that I am a small, retail investor, with small investments in India and United States in the form of Equity holdings via common stocks. Don't ask me if I have made money, I have been loosing money in stocks.