This article describes how IBM Informix DB Scheduler tool automatically keeps administrators up-to-date on database performance and availability and execute corrective actions automatically.
What is DB Scheduler?
The DB Scheduler is a self-managing technology simplifies database administration and lets administrator concentrate on other business issues. Current 24/7 support environment where each minutes counts, this self-managing technology can significantly reduce the database downtime by sending alerts prior to a problem occur.
The DB Scheduler manages and executes scheduled maintenance, monitoring, and administration tasks. This tool enables administrator to monitor activities (for example, space management, automatic backup etc. ) and create corrective actions that run automatically.
The DB Scheduler comprise of Tasks and Sensors.
The DB Scheduler is defined and driven by tasks. A new database ‘sysadmin’ has introduced starting with IDS v.11 for DB Scheduler. The sysadmin database has five various tables which contain and organize different properties of DB Scheduler. Only members of the DBSA group are granted access to connect to the sysadmin database. By default, user informix is granted access to the sysadmin database. Following are list of tables in sysadmin database:
There are two parameters available for use with create task and sensor. Values of these parameters can be used within SQL statement for storing and tracing data in sysadmin database.
The SQL statement is checked for two reserve strings $DATA_SEQ_ID & $DATA_TASK_ID. If those are found inside a SQL statement than the current sequence id is replace the $DATA_SEQ_ID and the current task id replaces $DATA_TASK_ID.
How to create a new Task?
A task can be created by inserting a row in the ph_task table. The ph_task table lists all tasks and contains information about how and when the database server will execute each task.
When the database server executes a task, the server invokes the SQL object contained in the pk_execute column of the ph_task table. The pk_execute column can contain a user-defined function, a single SQL statement, or a multiple-statement.
Prior to creating a new task we need to plan on following:
The following is an example of create task that runs once a day at 4:00 A.M. to ensure that table ‘orders’ contains only last one month of data:
The same above task can be creating using ph_threshold table for order retention policy information. An associated row must be inserted in ph_threshold table for 'DELETE ORDER RECORDS'. Modify the order retention period by changing information in the 'DELETE ORDER RECORDS' row in the ph_threshold table. The 'DELETE ORDER RECORDS' parameter sets the length of time rows should remain in the order table. Following is an example of create task using ph_threshold table:
How to create a Sensor?
Sensor is a specialized TASK for collecting and saving data. Sensor can be created same way as task by inserting a row in the ph_task table, with some additional information.
Prior to creating a new sensor we need to plan on following:
Following is an example of sensor that collects information on database memory usage and stores information in a result table called ‘mon_memory_system’. In case the result table does not exist, it creates the table. In the example sensor runs every 30 minutes, and also deletes data from ‘mon_memory_system’ table older than 30 days:
The DB Scheduler has some built in sensors and tasks, which can be use for different scheduled maintenance, monitoring, and administration. Following is a list of built in sensors and tasks:
How to stop the DB Scheduler?
The DB Scheduler is a useful feature. However, if it needs to stop for some reason, the recommended way is use the task() routine. Following SQL can be use for shutdown DB Scheduler:
DATABASE sysadmin; EXECUTE FUNCTION task("scheduler shutdown"); CLOSE DATABASE;
How to limit space utilize by DB Scheduler?
The DB Scheduler needs additional disk space for historical data. Sensors collect and save information in results table at sysadmin database. Following formula can be use for estimate the disk usage for each sensor:
(Number of rows collected * Size of the row collected * The frequency of data collection per day * The retention period)
By disabling sensors one can prevent collect and save historical information. However, disabling sensors can effects adversely other database server functions. It is always better to update the ph_task table in sysadmin database to limit the amount of data collect by a sensor than disable sensors. Following is an example that tells the DB Scheduler to delete data older than 4 times the collection frequency:
DATABASE sysadmin; UPDATE ph_task SET tk_delete = (4 * tk_frequency) WHERE tk_type = 'SENSOR';