Creating a sensor

You can create a Scheduler sensor to collect and store data about the database server.

Before you begin

You must be connected to the sysadmin database as user informix or another authorized user.

About this task

To create a sensor, use an INSERT statement to add a row into the ph_task table:

Procedure

  1. Include values for the following columns:
    • tk_name: Give the task a unique name.
    • tk_description: Add a description of the action the task performs.
    • tk_result_table: Add the name of the table that holds the data that the sensor gathers.
    • tk_create: Add a CREATE statement to create the results table. The results table must have an INTEGER column named ID to hold the sensor ID. You can add other columns to the table.
    • tk_execute: Add the action that the sensor performs. The action can be a user-defined function, a single SQL statement, or a multiple-statement prepared object that was created using the PREPARE SQL to enable the assembly of one or more SQL statements at runtime.
  2. Optionally change the default values for the following columns:
    • tk_type: The default value is SENSOR. For a startup sensor, change the value to STARTUP SENSOR.
    • tk_delete: The default interval after which to delete sensor data is one day.
    • tk_start_time: The default start time is 8:00:00. For a startup sensor, set the start time to NULL.
    • tk_stop_time: The default stop time is 19:00:00. For a startup sensor, set the stop time to NULL.
    • tk_frequency: The default frequency once a day. For a startup sensor, set the frequency to NULL.
    • tk_group: The default group is MISC.
    • tk_monday through tk_sunday: The default is to run every day.

Results

The sensor runs at the specified start time and subsequently at the time calculated from the frequency.

Examples

The following example shows the code for a sensor that tracks the startup environment of the database server. The $DATA_SEQ_ID variable is the current execution of the sensor.
INSERT INTO ph_task
(
tk_name,
tk_type,
tk_group,
tk_description,
tk_result_table,
tk_create,
tk_execute,
tk_stop_time,
tk_start_time,
tk_frequency,
tk_delete
)
VALUES
(
"mon_sysenv",
"STARTUP SENSOR",
"SERVER",
"Tracks the database servers startup environment.",
"mon_sysenv",
"create table mon_sysenv (ID integer, name varchar(250), value lvarchar(1024))",
"insert into mon_sysenv select $DATA_SEQ_ID, env_name, env_value 
FROM sysmaster:sysenv",
NULL,
NULL,
NULL,
"60 0:00:00"
);
The following example shows the code for a sensor that collects information about the amount of memory that is being used and stores the information in the mon_memory_system table. If that table does not exist, the task creates it. This task, which runs every 30 minutes, deletes any data in the mon_memory_system table that has existed for more than 30 days.
INSERT INTO ph_task
(
tk_name, 
tk_group, 
tk_description, 
tk_result_table, 
tk_create,
tk_execute, 
tk_stop_time, 
tk_start_time, 
tk_frequency, 
tk_delete 
)
VALUES
("mon_memory_system",
"MEMORY",
"Server memory consumption",
"mon_memory_system",
"create table mon_memory_system (ID integer, class smallint, size int8, 
     used int8, free int8 )",
"insert into mon_memory_system select $DATA_SEQ_ID, seg_class, seg_size, 
     seg_blkused, seg_blkfree FROM sysmaster:sysseglst",
NULL,
NULL,
INTERVAL ( 30 ) MINUTE TO MINUTE,
INTERVAL ( 30 ) DAY TO DAY
);