Using the scheduler and the SQL administration API to view information about the database server
You can use the scheduler and the SQL administration API to manage automatic maintenance and to monitor administrative tasks. These components of Informix Dynamic Server enable you to simplify the collection of information and maintenance of the server in complex systems.
The scheduler can run administrative and maintenance tasks or can collect information at predefined times. You can use the built-in tasks and sensors, or you can write your own tasks and sensors to customize your automatic maintenance tasks. The scheduler is controlled by a set of tables and a set of stored procedures in the sysadmin database. To view, to modify components of the scheduler, or to add new components to the scheduler, you must be connected to the sysadmin database as user informix or another authorized user.
The scheduler has four different job types:
- Runs an action at a specific time and frequency.
- Collects and saves information at a specific time and frequency. Creates a result table to hold the information.
- Startup task
- Defines a task that runs only when the database server moves from quiescent mode to online mode.
- Startup sensor
- Defines a sensor that runs only when the database server moves from quiescent mode to online mode.
The scheduler contains built-in tasks and sensors that run automatically, as shown in Table 8.
Table 8. Built-in tasks and sensors
|Task or sensor||Description||Enabled by default?|
|alert cleanup||This task removes all alert entries from the ph_alert table that are older than the threshold of 15 days.||Yes|
|auto_crsd||This task compresses, shrinks, repacks, and defragments tables and fragments.||No|
|autoreg exe||This task registers database extensions when they are first used.||No|
|autoreg vp||This task creates a specialized virtual processor for a database extension as needed.||No|
|auto_tune_cpu_vps||This task automatically adds CPU virtual processors if the number of allocated virtual processors is less than half the number of CPU processors on the computer.||No|
|auto update statistics evaluation||This task analyzes all the tables in all logged databases, identifies the tables whose distributions need to be updated, and generates UPDATE STATISTICS statements for those tables.||Yes|
|auto update statistics refresh||This task runs the UPDATE STATISTICS statements generated by the auto update statistics evaluation task.||Yes|
|bad_index_alert||This task checks for corrupted indexes.||No|
|check_backup||This task checks to ensure that backups have run since the time specified by thresholds.||Yes|
|check_for_ipa||This task checks tables for outstanding in-place alter operations.||No|
|idle_user_timeout||This task terminates user sessions that have been idle for longer than 60 minutes.||No|
|mon_checkpoint||This sensor saves information about checkpoints.||Yes|
|mon_command_history||This task deletes rows from the command_history table that are older than the threshold of 30 days.||Yes|
|mon_config||This sensor saves the most recent value for each configuration parameter in the onconfig file.||Yes|
|mon_config_startup||This sensor saves the value for each configuration parameter in the onconfig file when the server starts.||Yes|
|mon_low_storage||This task scans the list of dbspaces to find spaces that fall below the threshold specified by the SP_THRESHOLD configuration parameter. Then, the task expands the spaces by extending chunks or adding chunks.||Yes|
|mon_memory_system||This sensor collects server memory consumption information.||Yes|
|mon_profile||This sensor saves server profile information.||Yes|
|mon_sysenv||This startup sensor saves the database server startup environment.||Yes|
|mon_table_names||This sensor saves table names along with their creation time.||Yes|
|mon_table_profile||This sensor saves table profile information.||Yes|
|mon_users||This sensor saves profile information about each user.||Yes|
|mon_vps||This sensor collects virtual processor information.||Yes|
|online_log_rotate||This task rotates the online message log file that is specified in the MSGPATH configuration parameter.||No|
|post_alarm_message||This task posts alerts.||Yes|
You can change built-in task and sensor properties by updating the ph_task table. You can enable or disable tasks by updating the field tk_enable in the ph_task table under the sysadmin database. Listing 19 shows an example of updating the ph_task table by showing how to enable the task check_for_ipa to check for outstanding in-place alter operations.
Listing 19. Enable the task check_for_ipa to check for outstanding in-place alter operations
UPDATE ph_task set tk_enable = "t" where tk_name = "check_for_ipa"
Listing 20 shows how to configure the task mon_profile to run every 10 hours (instead of running every 4 hours) and to delete data older than 20 days (instead of deleting data older than 30 days) in the resulting table.
Listing 20. Configure the task mon_profile
UPDATE ph_task set (tk_frequency, tk_delete) = (INTERVAL (10) HOUR to HOUR, INTERVAL (20) DAY TO DAY) where tk_name = "mon_profile"
Each task requires the following:
- A name for the task
- A description of the task
- An action that the task performs, which can be a user-defined function or an SQL statement
Each sensor requires the following:
- A name for the sensor
- A description of the sensor
- An action that the sensor performs, which can be a user-defined function or an SQL statement
- A name of a table that will hold the resulting data
- A CREATE TABLE statement for the table that will hold the resulting data
To create a task or a sensor, use the INSERT statement to add a new row to the ph_task table. For example, Listing 21 shows how to create a task that tries to free shared memory each day at 10:00.
Listing 21. Create a task that tries to free shared memory each day at 10:00
insert into ph_task (tk_name, tk_description, tk_type, tk_execute, tk_start_time) values ("Free Memory", "free unused shared memory segments", "TASK", "EXECUTE FUNCTION task ('onmode', 'F');", DATETIME (10) HOUR TO HOUR);
A threshold value can be compared to a current value to determine whether a task or sensor should be run. To create a threshold, use the INSERT statement to add a new row to the ph_threshold table. Write the task or a sensor action to use the threshold.
To create an alert, use the INSERT statement to add a new row to the ph_alert table. Write the task or a sensor action to create an alert.
You can use groups to organize scheduler tasks and sensors. To create a group, use the INSERT statement to add a new row to the ph_group table.
You can modify the properties of scheduler tasks, sensors, alerts, groups, or thresholds. You can modify both built-in properties and properties that you added. To modify the properties, use the UPDATE statement for the appropriate scheduler table.
The scheduler has the following two types of threads:
- dbWorker is executing scheduled tasks and sensors
- dbScheduler is preparing the next task or sensor that is scheduled to run
You can view currently running tasks and sensors with the onstat -g dbc command. Listing 22 shows an example of the resulting output.
Listing 22. Output of onstat -g dbc
Worker Thread(0) 46fa6f80 ===================================== Task: 4729fc18 Task Name: mon_sysenv Task ID: 4 Task Type: STARTUP SENSOR Task Execution: insert into mon_sysenv select 1, env_name, env_value FROM sysmaster:sysenv WORKER PROFILE Total Jobs Executed 3 Sensors Executed 2 Tasks Executed 1 Purge Requests 2 Rows Purged 0 Scheduler Thread 46fa6f80 ===================================== Run Queue Empty Run Queue Size 0 Next Task 7 Next Task Waittime 57
You can view information about the tasks and sensors that have completed by querying the ph_run table, as shown in Listing 23.
Listing 23. Query the last execution time and the returncode of the task Free Memory
select ph_task.tk_name, ph_run.run_retcode, ph_run.run_time from ph_run, ph_task where ph_task.tk_name = "Free Memory" and ph_run.run_task_id = ph_task.tk_id;
The resulting output is shown in Listing 24.
Listing 24. See the last execution time and the returncode of the task Free Memory
tk_name run_retcode run_time Free Memory 0 2011-04-05 10:40:19 1 row(s) retrieved.
Use the SQL administration API to start and stop the scheduler. Start the scheduler
EXECUTE FUNCTION task("scheduler start");. Stop the
EXECUTE FUNCTION task("scheduler stop");.
The built-in task auto_crsd enables you to do automatic compressing, shrinking, repacking, and defragmenting of tables and extents. This task is disabled by default. To enable this task, update the table ph_task, and set tk_enable to t.
You can control this task by updating rows in the table ph_threshold. Update the rows for AUTOCOMPRESS_ENABLED, AUTOREPACK_ENABLED, AUTOSHRINK_ENABLED, and AUTODEFRAG_ENABLED control if this task is doing compression, repacking, shrinking, or defragmenting.
For example, if you want the task to do compression and defragmenting but not repacking and shrinking, then UPDATE the ph_threshold table as shown in Listing 25.
Listing 25. UPDATE statements
UPDATE ph_threshold SET value = 'T' WHERE name = 'AUTOCOMPRESS_ENABLED'; UPDATE ph_threshold SET value = 'F' WHERE name = 'AUTOREPACK_ENABLED'; UPDATE ph_threshold SET value = 'F' WHERE name = 'AUTOSHRINK_ENABLED'; UPDATE ph_threshold SET value = 'T' WHERE name = 'AUTODEFRAG_ENABLED';
The following rows are the thresholds for when compression, repacking, shrinking, or defragmenting occur:
- AUTOCOMPRESS_ROWS is the number of uncompressed rows in a table.
- AUTOREPACK_SPACE is the percentage of noncontiguous space in a table.
- AUTOSHRINK_UNUSED is the percentage of unused, allocated space in a able or a fragment.
- AUTODEFRAG_EXTENTS is the number of extents of a table or a fragment.
The built-in task mon_low_storage enables adding more storage space automatically when more space is needed. This task is enabled by default. This task uses information from the storage pool to automatically expand an existing dbspace, temporary dbspace, sbspace, temporary sbspace, or blobspace.
You can control the task by changing the onconfig parameters SP_AUTOEXPAND and SP_THRESHOLD. Set SP_AUTOEXPAND to 0 or SP_THRESHOLD to 0 to disable the automatic expansion of storage spaces. Set SP_THRESHOLD to a value to define the minimum amount of free space that can exist in a storage space before the task expands the space.
The SQL administration API enables you to perform remote administration using the SQL function in client tools. You can perform administrative tasks such as updating configuration parameters, checking data, and configuring storage spaces. You can also complete many of the tasks using the various administrative utilities of Informix.
The SQL administration API consists of two built-in functions: admin() and task(), which are defined in the sysadmin database and invoked with the EXECUTE FUNCTION statement. You must be connected to the sysadmin database to run these functions.
These functions take one or more arguments to define the operation, as shown in Table 9.
Table 9. Administrative tasks you can perform with the SQL administration API
|Compress data and optimize storage||None|
|Update configuration parameters||onmode|
|Manage data, partitions, and extents||onmode, oncheck -c, oncheck -p, onspaces|
|Manage enterprise replication||cdr|
|Manage high-availability replication||onmode -d,ontape|
|Control listen threads||onmode -P|
|Manage logical and physical logs||onparams, ondblog|
|Manage memory||onmode, onparams|
|Change the server mode||onmode|
|Manage storage spaces||onspaces|
|Manage storage spaces from a storage pool||None|
|Manage SQL statement cache||onmode|
|Manage SQL tracing||SQLTRACE onconfig parameter|
|Manage SQL tracing for a user, a session, or a database||None|
Following are examples of how to use the SQL administration API.
Listing 26 shows the SQL statement that checks the extents. This is equivalent to the oncheck -ce command.
Listing 26. SQL statement that checks extents
EXECUTE FUNCTION admin("check extents");
Listing 27 shows the SQL statement that creates a physical log in the dbspace physdbs with a size of 100 MB. This is equivalent to the onparams -p command.
Listing 27. SQL statement that creates a physical log in the dbspace physdbs with a size of 100 MB
EXECUTE FUNCTION task ("alter plog","physdbs","100 M");
Listing 28 shows the SQL statement that switches the current logical-log file to the next logical-log file. This is equivalent to the onmode -l command.
Listing 28. SQL statement that switches the current logical-log file to the next logical-log file
EXECUTE FUNCTION task ("onmode", "l");
The command_history table in the sysadmin database contains information about all the SQL administration API commands executed on this server. The command_history table shows each SQL administration API function that was run and displays information about the user who ran the function, the time the function was run, the primary arguments of the function, and the message returned when the database server finished running the function.
Listing 29 shows the information from the command_history table about the onmode command that is executed in the Free Memory task.
Listing 29. Information from the command_history table
cmd_number 110 cmd_exec_time 2011-04-05 10:40:19 cmd_user informix cmd_hostname NA cmd_executed onmode cmd_ret_status 0 cmd_ret_msg OK
The built-in task mon_command_history controls the size of the table. This task is enabled by default and deletes rows in this table that are older than 30 days. You can update the table ph_threshold to reduce this time period.
Listing 30 shows how to update the ph_threshold table to delete all rows older than 20 days.
Listing 30. Updating the ph_threshold table to delete all rows older than 20 days
UPDATE ph_threshold SET value = "20" WHERE name = "COMMAND HISTORY RETENTION";
The sysadmin database contains the tables that contain and organize the scheduler tasks and sensors, store data collected by sensors, and record the results of scheduler jobs and SQL administration API functions.
The sysadmin database contains the following:
- The scheduler tables
- The result tables
- The storage pool table
- The command_history table
- The built-in task() function
- The built-in admin() function
Table 10 shows the tables in the sysadmin database and describes their purposes.
Table 10. Tables in the sysadmin database
|ph_alert||Contains a list of errors, warnings, or informational messages associated with tasks that must be monitored.|
|ph_group||Contains a list of group names.|
|ph_run||Contains information about how and when each task and sensor was run.|
|ph_task||Lists tasks and sensors, and contains information about how and when the database server will run them.|
|ph_threshold||Contains a list of thresholds that are associated with tasks or sensors.|
|result tables||Offers multiple tables that contain historical data collected by sensors. The structure of these tables is determined by the CREATE TABLE statement in the sensor definition in the ph_task table.|
|storagepool||Contains information about all of the entries in the storage pool. Each entry represents free space that the server can use when automatically expanding a storage space.|
|command_history||Contains the list and results of all the SQL administration API functions that were run.|
The scheduler tables and sensor results tables can consume significant amounts of disk space. You can move the sysadmin database from its default root dbspace location to a different dbspace using the SQL administration API, as shown in Listing 31. Moving the sysadmin database resets the database back to its original state when it was first created, which means all data, command history, results tables, and user-defined tasks are lost.
Listing 31. Move the sysadmin database to the dbspaces dbs1
EXECUTE FUNCTION task("reset sysadmin","dbs1");