Automatic monitoring, maintenance, and administrative tasks
IDS provides automatic and flexible monitoring, maintenance, and administrative tasks through the combination of the Scheduler, SQL Admin API functions, and information stored in a system database called "sysadmin." In addition, query drill-down functionality provides a means of monitoring SQL queries in finer granularity. Let's visit these components in detail.
IDS administrative jobs can broadly be categorized into two groups: task and sensor. A task is a job that executes one of the following:
- An SQL statement or a group of SQL statements
- A stored procedure
- A C or Java™ technology routine
A sensor is a special kind of task that is used to collect desired information.
Tasks or sensors that run only during startup of the server are called startup tasks or startup sensors, respectively.
The IDS scheduler runs the tasks and sensors at specified times or intervals. It takes the information about the task and sensor in the ph_task table in the sysadmin database.
To stop the Scheduler, run the following command:
Listing 15. Stop the Scheduler
$ dbaccess sysadmin - Database selected. > execute function task ("scheduler stop"); (expression) Successfully shutdown scheduler 1 row(s) retrieved.
To start it, run this command:
Listing 16. Start the Scheduler
$ dbaccess sysadmin - Database selected. > execute function task ("scheduler start"); (expression) Successfully started 2 threads 1 row(s) retrieved.
onstat -g ath will show the thread
of the scheduler threads (dbScheduler, dbWorker1, and dbWorker2):
onstat -g ath
$ onstat -g ath IBM Informix Dynamic Server Version 11.50.FC3 -- On-Line -- Up 1 days 00:45:40 -- 149504 Kbytes Threads: tid tcb rstcb prty status vp-class name 2 112038808 0 1 IO Idle 4lio* lio vp 0 3 1120582a8 0 1 IO Idle 5pio* pio vp 0 4 1120782a8 0 1 IO Idle 6aio* aio vp 0 5 1120972a8 0 1 IO Idle 7msc* msc vp 0 6 1120c62a8 0 1 IO Idle 8aio* aio vp 1 7 1120e6548 11152e028 3 sleeping secs: 1 3cpu main_loop() 8 112058508 0 1 running 1cpu* tlitcppoll 9 112078c40 0 2 sleeping forever 1cpu* tlitcplst 10 1120978f8 11152e878 1 sleeping secs: 1 1cpu flush_sub(0) 11 112097be0 11152f0c8 1 sleeping secs: 1 3cpu flush_sub(1) 12 11218b028 11152f918 1 sleeping secs: 1 3cpu flush_sub(2) 13 11218b310 111530168 1 sleeping secs: 1 3cpu flush_sub(3) 14 11218b5f8 1115309b8 1 sleeping secs: 1 1cpu flush_sub(4) 15 1121e76a0 111531208 1 sleeping secs: 1 3cpu flush_sub(5) 16 1121e7988 111531a58 1 sleeping secs: 1 1cpu flush_sub(6) 17 1121e7c70 1115322a8 1 sleeping secs: 1 3cpu flush_sub(7) 18 1122365b0 111532af8 2 sleeping secs: 1 1cpu aslogflush 19 1122d9370 111533348 1 sleeping secs: 99 3cpu btscanner_0 35 112572d50 111534c38 3 sleeping secs: 1 1cpu* onmode_mon 36 1122fb970 1115343e8 3 sleeping secs: 1 3cpu periodic 45 1125f6520 111536d78 1 cond wait bp_cond 3cpu bf_priosweep() 261 112947808 1115375c8 1 cond wait netnorm 1cpu sqlexec 267 112362d48 111535488 1 sleeping secs: 152 3cpu* dbScheduler 268 112c42d20 111535cd8 1 sleeping forever 3cpu dbWorker1 269 11249e4c0 111536528 1 sleeping forever 1cpu dbWorker2
In Listing 17, you can see the dbScheduler thread, which constantly manages the sensors or tasks, and the dbWorker threads—the threads that execute the sensor and task (put on queue for them by the dbScheduler).
sysadmin is a system database created automatically during server initialization. It contains information that the Scheduler uses to run the task or sensor. At the heart of the sysadmin database is the ph_task table, which basically records information about a task or sensor and when to run it. Additionally for sensor, it contains information about what to collect and where to store it.
Let's take a look at a built-in example:
Listing 18. Task example
tk_id 1 tk_name mon_command_history tk_description Monitor how much data is kept in the command history table tk_type TASK tk_sequence 23 tk_result_table tk_create tk_dbs sysadmin tk_execute delete from command_history where cmd_exec_time < ( select current - value::INTERVAL DAY to SECOND from ph_threshold where name = 'COMMAND HISTORY RETENTION' ) tk_delete 0 01:00:00 tk_start_time 02:00:00 tk_stop_time tk_frequency 1 00:00:00 tk_next_execution 2009-08-05 23:46:05 tk_total_executio+ 23 tk_total_time 0.20672 tk_monday t tk_tuesday t tk_wednesday t tk_thursday t tk_friday t tk_saturday t tk_sunday t tk_attributes 404 tk_group TABLES tk_enable t tk_priority 0
From the tk_type, you know that this is a task and its name is "mon_command_history".
This task will execute the action as specified in tk_execute, which is:
Listing 19. Task specified in tk_execute
delete from command_history where cmd_exec_time < ( select current - value::INTERVAL DAY to SECOND from ph_threshold where name = 'COMMAND HISTORY RETENTION' )
If you run the following subquery, this SQL, in effect, deletes the rows from command_history where the cmd_exec_time is before 2009-07-06 09:13:49.000:
Listing 20. Subquery to delete rows
select current - value::INTERVAL DAY to SECOND from ph_threshold where name = 'COMMAND HISTORY RETENTION'; select current as now,value from ph_threshold where name = 'COMMAND HISTORY RETENTION' Output: (expression) 2009-07-06 09:29:21.000 now 2009-08-05 09:29:21.000 value 30 0:00:00
In this example, you also see another table in sysadmin database being used—ph_threshold . This table is used to keep threshold, which will trigger the delete statement, which is 30 days before the point when the task is executing.
tk_start_time indicates this task will run at 2 am daily from Monday to Sunday since tk_monday to tk_sunday are all marked true.
Let's now look at an example of a sensor:
Listing 21. Sensor example
tk_id 6 tk_name mon_vps tk_description Process time of the Virtual Processors tk_type SENSOR tk_sequence 117 tk_result_table mon_vps tk_create create table mon_vps (ID integer, vpid smallint, num_ready smallint, class integer, usecs_user float, usecs_sys float ) tk_dbs sysadmin tk_execute insert into mon_vps select $DATA_SEQ_ID, vpid, num_ready, class, usecs_user, usecs_sys FROM sysmaster:sysvplst tk_delete 15 00:00:00 tk_start_time tk_stop_time tk_frequency 0 04:00:00 tk_next_execution 2009-08-05 07:46:05 tk_total_executio+ 117 tk_total_time 8.074843700000 tk_monday t tk_tuesday t tk_wednesday t tk_thursday t tk_friday t tk_saturday t tk_sunday t tk_attributes 405 tk_group CPU tk_enable t tk_priority 0
Note that the tk_type field value indicates that this task is a sensor. And tk_execute indicates the information to capture and keep them in mon_vps table. The tk_result_table specifies the schema of the mon_vps table.
By default, the sysadmin database resides in the rootdbs. To move it out to another dbspace, use the following SQL Admin API:
EXECUTE FUNCTION task("reset sysadmin","dbs1");
Checking the online.log:
Listing 22. Check online.log
onstat -m 10:09:46 SCHAPI: thread dbScheduler task mon_checkpoint(0-457) shutting down 10:09:46 SCHAPI: thread dbWorker1 task mon_config_startup(3-38) shutting down 10:09:46 SCHAPI: thread dbWorker2 task mon_memory_system(8-231) shutting down 10:09:46 SCHAPI: 'sysadmin' database will be moved to 'dbs1'. See online message log. 10:09:46 Building 'sysadmin' database ... 10:09:53 Unloading Module <SPLNULL> 10:09:57 Loading Module <SPLNULL> 10:10:05 Unloading Module <SPLNULL> 10:10:05 Loading Module <SPLNULL> 10:10:05 'sysadmin' database built successfully. 10:10:05 SCHAPI: Started dbScheduler thread. 10:10:05 SCHAPI: Started 2 dbWorker threads.
Traditionally, administration tasks, such as management of dbspaces, chunks, logical logs, or physical logs, are done using utilities such as onspaces, onparams, and onmode. However, with the introduction of SQL Admin API functions, a set of UDRs now provides similar functionality. The DBA can now execute these administration tasks from a remote machine as long as it can run the user routine just like a query. Also, the result and audit trail of the action can be kept in a command_history table in the sysadmin database.
Two UDRs called task and admin are used. Their functionality is identical, except the result return value is different. For task, the return status is in the form of character:
EXECUTE FUNCTION task('create dbspace', 'dbspace2', '/CHUNKS/dbspace2'); (expression) created dbspace number 2 named dbspace2
For admin, return status is an integer, which is kept in the cmd_ret_status field of the command_history table:
EXECUTE FUNCTION admin('create dbspace', 'dbspace2', '/CHUNKS/dbspace2'); (expression) 107