Actions for task and sensors

The action for a task or sensor is an SQL statement or routine that performs one or more operations.

SQL statements are useful if the action consists of a single operation. A stored procedure or a user-defined routine written in C or Java™ is useful if the action consists of multiple operations. The action is stored in the tk_execute column of the ph_task table.

You have a great deal of flexibility when you create an action. Possible types of operations include:

Perform a DML operation. You can use a sensor to insert or update data in a table. You can use a task to delete older data from a table.
Perform an administrative operation. You can use a task to run an SQL administration API function to administer the database server. For example, you can create a task to take checkpoints every two minutes.
Perform an operation based on a threshold. You can use a threshold from the ph_threshold table to determine if a task action must be run. For example, you can create a task that adds a shared memory segment if the amount of available shared memory falls below a threshold value.
Create an alert to report an operation or warn of a potential problem. For example, you can create a task to terminate idle users that inserts a row into the ph_alert table when a user session is terminated. You can also create a task to monitor backups and insert a warning into the ph_alert table when a backup has not occurred.

Use the following variables in your task or sensor action:

$DATA_TASK_ID: Use to indicate the current task or sensor. This variable corresponds to the value of the tk_id field in the ph_task table.
$DATA_SEQ_ID: Use to indicate the current execution of the task or sensor. This variable corresponds to the value of the tk_sequence field in the ph_task table and the run_task_sequence field in the ph_run table.

Examples

The following action is an SQL statement that the built-in mon_command_history task uses to remove older rows from the command_history table.

DELETE FROM command_history
WHERE cmd_exec_time < (
SELECT CURRENT - value::INTERVAL DAY to SECOND
FROM ph_threshold
WHERE name = 'COMMAND HISTORY RETENTION' )

The following example is an SQL statement that the built-in mon_vps sensor uses to add data to the mon_vps result table:

INSERT INTO mon_vps 
SELECT $DATA_SEQ_ID, vpid, num_ready, 
class, usecs_user, usecs_sys 
FROM sysmaster:sysvplst

The following example is a stored procedure that terminates user sessions that are idle for longer than a value set by a threshold, and then adds an alert to the ph_alert table.

/*
 **************************************************************
 *  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
           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;