/*
**************************************************************
* Create a function that will find all users that have
* been idle for the specified time. Call the SQL admin API to
* terminate those users. Create an alert to track which
* users have been terminated.
**************************************************************
*/
CREATE FUNCTION idle_timeout( task_id INT, task_seq INT)
RETURNING INTEGER
DEFINE time_allowed INTEGER;
DEFINE sys_hostname CHAR( 16 );
DEFINE sys_username CHAR( 257 );
DEFINE sys_sid INTEGER;
DEFINE rc INTEGER;
{*** Get the maximum amount of time to be idle ***}
SELECT value::integer
INTO time_allowed
FROM ph_threshold
WHERE name = "IDLE TIMEOUT";
{*** Find all users who are idle longer than the threshold ***}
FOREACH SELECT admin("onmode","z",A.sid), A.username, A.sid, hostname
INTO rc, sys_username, sys_sid, sys_hostname
FROM sysmaster:sysrstcb A , sysmaster:systcblst B,
sysmaster:sysscblst C
 28 - 10  IBM Informix Dynamic Server Administrator’s Guide
WHERE A.tid = B.tid
AND C.sid = A.sid
AND lower(name) in ("sqlexec")
AND CURRENT - DBINFO("utc_to_datetime",last_run_time) > time_allowed UNITS MINUTE
AND lower(A.username) NOT IN( "informix", "root")
{*** If a user is successfully terminated, log ***}
{*** the information into the alert table. ***}
IF rc >  0  THEN
INSERT INTO ph_alert
(
ID, alert_task_id,alert_task_seq,
alert_type, alert_color,
alert_state,
alert_object_type, alert_object_name,
alert_message,
alert_action
) VALUES (
 0 ,task_id, task_seq,
"INFO", "GREEN",
"ADDRESSED",
"USER","TIMEOUT",
"User "||TRIM(sys_username)||"@"||TRIM(sys_hostname)||
" sid("||sys_sid||")"||
" terminated due to idle timeout.",
NULL
);
END IF
END FOREACH;
RETURN  0 ;
END FUNCTION;

 28 - 10  IBM Informix Dynamic Server Administrator’s Guide