How to abort a session running over QUERYTIMEOUT?

Answer

Queries that run longer than QUERYTIMEOUT will trigger a RunawayQuery event. That event can be used to send an email or run a command script. One possible use of the command script is to automatically abort sessions with long running queries.

Steps:
1. Specify QUERYTIMEOUT for a user:
ALTER USER QT_USER QUERYTIMETOUT 10;

2. Create an runawayQuery event:

nzevent add -eventType runawayQuery -name abort_long_running_query \
-on yes -eventArgsExpr '$duration > 60' -notifyType runCmd \
-dst '/nz/RAQueries/abort_session $sessionId $planId'


3. Create script /nz/RAQueries/abort_session to abort that session. It also writes the plan file to /tmp/RAQueries/Username.Session.PlanID.pln:

CURRUSERNAME=`nzsql -q -X -A -t -c "SELECT USERNAME FROM _V_SESSION WHERE ID=$1"` 2> /dev/null
nzsession abort -id $1 -force
nz_plan $2 > /tmp/EVENT/$CURRUSERNAME.$1.$2.pln


With the above in place, if QT_USER has a SQL running over 10 minutes, the runawayQuery event will be triggered and the session aborted.

You could extend the command script to only do this for a certain set of users, or to have different behaviours at different times of day/days of week/etc.