{**************************************************************************} {* *} {* Licensed Materials - Property of IBM and/or HCL *} {* *} {* IBM Informix Dynamic Server *} {* Copyright IBM Corporation 1996, 2017 *} {* (c) Copyright HCL Technologies Ltd. 2017. All Rights Reserved. *} {* *} {**************************************************************************} { } { Title: sch_tasks.sql } { Description: } { system default set of tasks } DATABASE sysadmin; EXECUTE PROCEDURE ifx_allow_newline('t'); INSERT INTO ph_group VALUES (0,"DISK","Disk Subsystem"); INSERT INTO ph_group VALUES (0,"NETWORK","Network subsystem"); INSERT INTO ph_group VALUES (0,"MEMORY","Memory Utilization"); INSERT INTO ph_group VALUES (0,"CPU","CPU Utilization"); INSERT INTO ph_group VALUES (0,"TABLES","Tables"); INSERT INTO ph_group VALUES (0,"INDEXES","Indexes"); INSERT INTO ph_group VALUES (0,"SERVER","Global Server Information"); INSERT INTO ph_group VALUES (0,"USER","User Information"); INSERT INTO ph_group VALUES (0,"BACKUP","Backup and Restore Information"); INSERT INTO ph_group VALUES (0,"PERFORMANCE","Performance Information"); {************************************************************************** Monitor for the command history table **************************************************************************} INSERT INTO ph_threshold(id,name,task_name,value,description) VALUES (0,"COMMAND HISTORY RETENTION", "mon_command_history","30 0:00:00", "The amount of time the command_history table should contain information about SQL ADMIN commands that habe been executed. Any SQL ADMIN commands older than this will be purged."); INSERT INTO ph_task ( tk_name, tk_type, tk_group, tk_description, tk_execute, tk_start_time, tk_stop_time, tk_frequency ) VALUES ( "mon_command_history", "TASK", "TABLES", "Monitor how much data is kept in 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' ) ", DATETIME(02:00:00) HOUR TO SECOND, NULL, INTERVAL ( 1 ) DAY TO DAY ); {************************************************************************** Task to track the onconfig paramaters **************************************************************************} CREATE FUNCTION informix.onconfig_save_diffs(task_id INTEGER, ID INTEGER) RETURNING INTEGER DEFINE value LVARCHAR(1024); DEFINE conf_value LVARCHAR(1024); DEFINE conf_id INTEGER; LET value = NULL; FOREACH select cf_id, trim(cf_effective) INTO conf_id, conf_value FROM sysmaster:syscfgtab FOREACH select FIRST 1 config_value INTO value FROM sysadmin:mon_config WHERE mon_config.config_id = conf_id ORDER BY id DESC END FOREACH IF conf_value == value THEN CONTINUE FOREACH; END IF INSERT INTO mon_config VALUES( ID, conf_id, conf_value ); END FOREACH return 0; END FUNCTION; INSERT INTO ph_task ( tk_name, tk_type, tk_group, tk_description, tk_result_table, tk_create, tk_execute, tk_start_time, tk_stop_time, tk_delete, tk_frequency, tk_next_execution ) VALUES ( "mon_config", "SENSOR", "SERVER", "Collect information about database server's configuration file (onconfig). Only modified parameters are collected.", "mon_config", "create table informix.mon_config (ID integer, config_id integer, config_value lvarchar(1024)); create index mon_config_idx1 on mon_config(config_id);create index mon_config_idx2 on mon_config(id,config_id);create view mon_onconfig as select ID ID, cf_name name, config_value value from mon_config, sysmaster:sysconfig where mon_config.config_id = sysmaster:sysconfig.cf_id;grant select on mon_config TO 'db_monitor' as informix;", "onconfig_save_diffs", NULL, NULL, INTERVAL ( 60 ) DAY TO DAY, INTERVAL ( 1 ) DAY TO DAY, DATETIME(05:00:00) HOUR TO SECOND ); INSERT INTO ph_task ( tk_name, tk_type, tk_group, tk_description, tk_result_table, tk_create, tk_execute, tk_start_time, tk_stop_time, tk_delete, tk_frequency, tk_next_execution ) VALUES ( "mon_config_startup", "STARTUP SENSOR", "SERVER", "Collect information about database servers configuration file (onconfig). This will only collect paramaters which have changed.", "mon_config", "create table informix.mon_config (ID integer, config_id integer, config_value lvarchar(1024)); create view mon_onconfig as select ID ID, cf_name name, config_value value from mon_config, sysmaster:sysconfig where mon_config.config_id = sysmaster:sysconfig.cf_id;", "onconfig_save_diffs", NULL, NULL, INTERVAL ( 99 ) DAY TO DAY, NULL, NULL ); {************************************************************************** Task to track the database servers environment information **************************************************************************} 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 informix.mon_sysenv (ID integer, name varchar(250), value lvarchar(1024));grant select on mon_sysenv TO 'db_monitor' as informix;", "insert into mon_sysenv select $DATA_SEQ_ID, TRIM(env_name), TRIM(env_value) FROM sysmaster:sysenv;", NULL, NULL, "0 0:01:00", INTERVAL ( 60 ) DAY TO DAY ); {************************************************************************** Task to track the general system profile information **************************************************************************} INSERT INTO ph_task ( tk_name, tk_type, tk_group, tk_description, tk_result_table, tk_create, tk_execute, tk_start_time, tk_stop_time, tk_frequency, tk_delete ) VALUES ( "mon_profile", "SENSOR", "PERFORMANCE", "Collect the general profile information", "mon_prof", "create table informix.mon_prof (ID integer, number integer, value int8 );create view informix.mon_profile as select ID, A.name, B.value from sysmaster:sysshmhdr A, mon_prof B where B.number = A.number; create index mon_prof_idx1 on mon_prof(ID, number);create index mon_prof_idx2 on mon_prof(number);grant select on mon_profile TO 'db_monitor' as informix;grant select on mon_prof TO 'db_monitor' as informix;", "insert into mon_prof select $DATA_SEQ_ID, number, value from sysmaster:sysshmhdr where name != 'unused'", NULL, NULL, INTERVAL ( 1 ) HOUR TO HOUR, INTERVAL ( 30 ) DAY TO DAY ); {************************************************************************** Task to track the chunk information **************************************************************************} INSERT INTO ph_task ( tk_name, tk_type, tk_group, tk_description, tk_result_table, tk_create, tk_execute, tk_start_time, tk_stop_time, tk_frequency, tk_delete ) VALUES ( "mon_chunk", "SENSOR", "PERFORMANCE", "Collect the general chunk information", "mon_chunk", "create table informix.mon_chunk (ID integer, number smallint, dbsnum smallint, nfree integer,udfree integer, reads bigint, writes bigint, pagesread bigint, pageswritten bigint, readtime float, writetime float);grant select on mon_chunk TO 'db_monitor' as informix;", "insert into mon_chunk select * from ( select $DATA_SEQ_ID, chknum, dbsnum, nfree, udfree, reads, writes,pagesread,pageswritten,readtime,writetime from sysmaster:syschktab_fast union select $DATA_SEQ_ID, chknum, dbsnum, nfree, udfree, reads,writes,pagesread, pageswritten,readtime,writetime from sysmaster:sysmchktab_fast)", NULL, NULL, INTERVAL ( 1 ) HOUR TO HOUR, INTERVAL ( 30 ) DAY TO DAY ); {************************************************************************** Task to track the general cpu usage by process class **************************************************************************} 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_vps", "SENSOR", "CPU", "Process time of the Virtual Processors", "mon_vps", "create table informix.mon_vps (ID integer, vpid smallint, num_ready smallint, class integer, usecs_user float, usecs_sys float);grant select on mon_vps TO 'db_monitor' as informix;", "insert into mon_vps select $DATA_SEQ_ID, vpid, num_ready, class, usecs_user, usecs_sys FROM sysmaster:sysvplst", NULL, NULL, INTERVAL ( 4 ) HOUR TO HOUR, INTERVAL ( 15 ) DAY TO DAY ); {************************************************************************** Task to track the checkpoint information **************************************************************************} 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_checkpoint", "SENSOR", "SERVER", "Track the checkpoint information", "mon_checkpoint", "create table informix.mon_checkpoint (ID integer, intvl integer, type char(12), caller char(10), clock_time int, crit_time float, flush_time float, cp_time float, n_dirty_buffs int, plogs_per_sec int, llogs_per_sec int, dskflush_per_sec int, ckpt_logid int, ckpt_logpos int, physused int, logused int, n_crit_waits int, tot_crit_wait float, longest_crit_wait float, block_time float);create unique index idx_mon_ckpt_1 on mon_checkpoint(intvl);grant select on mon_checkpoint TO 'db_monitor' as informix;", "insert into mon_checkpoint select $DATA_SEQ_ID, intvl, type, caller, clock_time, crit_time, flush_time, cp_time, n_dirty_buffs, plogs_per_sec, llogs_per_sec, dskflush_per_sec, ckpt_logid, ckpt_logpos, physused, logused, n_crit_waits, tot_crit_wait, longest_crit_wait, block_time FROM sysmaster:syscheckpoint WHERE intvl > (select NVL(max(intvl),0) from mon_checkpoint)", NULL, NULL, INTERVAL ( 60 ) MINUTE TO MINUTE, INTERVAL ( 7 ) DAY TO DAY ); {************************************************************************** Task to track the server memory information **************************************************************************} 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_memory_system", "SENSOR", "MEMORY", "Server memory consumption", "mon_memory_system", "create table informix.mon_memory_system (ID integer, class smallint, size int8, used int8, free int8 );grant select on mon_memory_system TO 'db_monitor' as informix;", "insert into mon_memory_system select $DATA_SEQ_ID, seg_class, seg_size, seg_blkused, seg_blkfree FROM sysmaster:sysseglst", NULL, NULL, INTERVAL ( 2 ) HOUR TO HOUR, INTERVAL ( 15 ) DAY TO DAY ); {************************************************************************** Task to track the table level statistics **************************************************************************} 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_table_profile", "TABLES", "Collect SQL profile information by table/fragment, index information is excluded from this collection", "mon_table_profile", "create table informix.mon_table_profile ( id integer, partnum integer, nextns smallint, serialval int8, nptotal integer, npused integer, npdata integer, lockid integer, nrows bigint, ucount smallint, ocount smallint, pf_rqlock int8, pf_lockwait int8, pf_isread int8, pf_iswrite int8, pf_isrwrite int8, pf_isdelete int8, pf_bfcread int8, pf_bfcwrite int8, pf_seqscans int8, pf_dskreads int8, pf_dskwrites int8);grant select on mon_table_profile TO 'db_monitor' as informix; ", "insert into mon_table_profile select $DATA_SEQ_ID, partnum, nextns, decode(cur_serial8, 1, cur_serial4::int8, cur_serial8), nptotal, npused, npdata, lockid, nrows, ucount, ocount, pf_rqlock, pf_wtlock + pf_deadlk + pf_dltouts, pf_isread, pf_iswrite, pf_isrwrite, pf_isdelete, pf_bfcread, pf_bfcwrite, pf_seqscans, pf_dskreads, pf_dskwrites FROM sysmaster:sysactptnhdr WHERE (nkeys >0 AND nrows > 0 ) OR nkeys=0", NULL, NULL, INTERVAL ( 1 ) DAY TO DAY, INTERVAL ( 7 ) DAY TO DAY ); {************************************************************************** Task to track the tabnames paramaters **************************************************************************} CREATE FUNCTION informix.tabnames_save_diffs(task_id INTEGER, ID INTEGER) RETURNING INTEGER DEFINE cur_dbsname VARCHAR(128); DEFINE cur_owner VARCHAR(32); DEFINE cur_tabname VARCHAR(128); DEFINE cur_created INTEGER; DEFINE cur_partnum INTEGER; DEFINE cur_lockid INTEGER; FOREACH select tab.dbsname, tab.owner, tab.tabname , ptn.created, ptn.partnum, ptn.lockid INTO cur_dbsname, cur_owner, cur_tabname, cur_created, cur_partnum, cur_lockid FROM sysmaster:systabnames tab, sysmaster:sysptnhdr ptn WHERE tab.partnum = ptn.partnum AND sysmaster:bitval(ptn.flags,'0x20')==0 AND ptn.created NOT IN ( SELECT created FROM mon_table_names WHERE mon_table_names.partnum = ptn.partnum AND mon_table_names.lockid = ptn.lockid ) INSERT INTO sysadmin:mon_table_names (ID, partnum, dbsname, owner, tabname, created, lockid) VALUES (ID, cur_partnum, TRIM(cur_dbsname), TRIM(cur_owner), TRIM(cur_tabname), cur_created, cur_lockid ); END FOREACH return 0; END FUNCTION; 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_table_names", "SENSOR", "TABLES", "Collect table names from the system", "mon_table_names", "create table informix.mon_table_names ( id integer, partnum integer, dbsname varchar(128), owner varchar(32), tabname varchar(128), created integer,lockid integer ); create index informix.mon_table_names_idx1 on mon_table_names(partnum);grant select on mon_table_names TO 'db_monitor' as informix;", "tabnames_save_diffs", NULL, DATETIME(02:00:00) HOUR TO SECOND, INTERVAL ( 1 ) DAY TO DAY, INTERVAL ( 30 ) DAY TO DAY ); {************************************************************************** Task to track the general system profile information **************************************************************************} INSERT INTO ph_task ( tk_name, tk_type, tk_group, tk_description, tk_result_table, tk_create, tk_execute, tk_start_time, tk_stop_time, tk_frequency, tk_delete ) VALUES ( "mon_users", "SENSOR", "PERFORMANCE", "Collect information about each user", "mon_users", "CREATE TABLE informix.mon_users (ID integer, sid integer, uid integer, username varchar(16), pid integer, progname varchar(16),hostname varchar(16), memtotal integer, memused integer, upf_rqlock bigint, upf_wtlock bigint, upf_deadlk bigint, upf_lktouts bigint, upf_lgrecs bigint, upf_isread bigint, upf_iswrite bigint, upf_isrwrite bigint, upf_isdelete bigint, upf_iscommit bigint, upf_isrollback bigint, upf_longtxs bigint, upf_bufreads bigint, upf_idxbufreads integer, upf_diskreads integer, upf_bufwrites bigint, upf_diskwrites integer, upf_logspuse bigint, upf_logspmax bigint, upf_seqscans bigint, upf_totsorts bigint, upf_dsksorts bigint, upf_srtspmax bigint, nlocks integer, lkwaittime float, iowaittime float, upf_niowaits integer, net_read_cnt int8, net_read_bytes int8, net_write_cnt int8, net_write_bytes int8, net_open_time integer, net_last_read integer, net_last_write integer, wreason integer);grant select on mon_users TO 'db_monitor' as informix;", "insert into mon_users (ID, sid, uid, username, pid, progname, hostname, memtotal, memused, upf_rqlock, upf_wtlock, upf_deadlk, upf_lktouts, upf_lgrecs, upf_isread, upf_iswrite, upf_isrwrite, upf_isdelete, upf_iscommit, upf_isrollback, upf_longtxs, upf_bufreads, upf_idxbufreads, upf_diskreads, upf_bufwrites, upf_diskwrites, upf_logspuse, upf_logspmax, upf_seqscans, upf_totsorts, upf_dsksorts, upf_srtspmax, nlocks, lkwaittime, iowaittime, upf_niowaits, net_read_cnt, net_read_bytes, net_write_cnt, net_write_bytes, net_open_time, net_last_read, net_last_write, wreason) SELECT $DATA_SEQ_ID, scb.sid, scb.uid, TRIM(scb.username), scb.pid, TRIM(scb.progname), TRIM(scb.hostname), scb.memtotal, scb.memused, rstcb.upf_rqlock, rstcb.upf_wtlock, rstcb.upf_deadlk, rstcb.upf_lktouts, rstcb.upf_lgrecs, rstcb.upf_isread, rstcb.upf_iswrite, rstcb.upf_isrwrite, rstcb.upf_isdelete , rstcb.upf_iscommit, rstcb.upf_isrollback, rstcb.upf_longtxs, rstcb.upf_bufreads, rstcb.upf_idxbufreads, rstcb.nreads, rstcb.upf_bufwrites, rstcb.nwrites, rstcb.upf_logspuse, rstcb.upf_logspmax, rstcb.upf_seqscans, rstcb.upf_totsorts, rstcb.upf_dsksorts, rstcb.upf_srtspmax, rstcb.nlocks, rstcb.lkwaittime, rstcb.iowaittime, rstcb.upf_niowaits, net_read_cnt, net_read_bytes, net_write_cnt, net_write_bytes, net_open_time, net_last_read, net_last_write, wreason FROM sysmaster:sysrstcb rstcb, sysmaster:sysscblst scb, sysmaster:sysnetworkio net, sysmaster:systcblst tcb where rstcb.sid = scb.sid and scb.netscb = net.net_netscb and tcb.tid = rstcb.tid", NULL, NULL, INTERVAL ( 4 ) HOUR TO HOUR, INTERVAL ( 7 ) DAY TO DAY ); {************************************************************************** Tasks and functions to register database extensions on-first-use **************************************************************************} create table informix.autoreg_migrate ( kind varchar(20), name varchar(130) ); insert into autoreg_migrate values ('serverversion', dbinfo('version','full')); INSERT INTO ph_task ( tk_name, tk_type, tk_group, tk_description, tk_execute, tk_start_time, tk_stop_time, tk_delete, tk_frequency, tk_dbs, tk_enable, tk_attributes ) VALUES ( "autoreg exe", "TASK", "SERVER", "Register a database extension on-first-use", "autoregexe", NULL, NULL, NULL, NULL, "sysadmin", "f", "0x4004" ); INSERT INTO ph_task ( tk_name, tk_type, tk_group, tk_description, tk_execute, tk_start_time, tk_stop_time, tk_delete, tk_frequency, tk_dbs, tk_enable, tk_attributes ) VALUES ( "autoreg vp", "TASK", "SERVER", "Create a VP on-first-use", "autoregvp", NULL, NULL, NULL, NULL, "sysadmin", "f", "0x4004" ); insert into ph_task ( tk_name, tk_dbs, tk_type, tk_execute, tk_delete, tk_start_time, tk_stop_time, tk_frequency, tk_enable ) values ( "autoreg migrate-console", "sysadmin", "STARTUP TASK", " INSERT INTO ph_task ( tk_name, tk_dbs, tk_type, tk_execute, tk_delete, tk_start_time, tk_stop_time, tk_frequency ) SELECT ""autoreg migrate ""||partnum, name, ""STARTUP TASK"", ""EXECUTE FUNCTION SYSBldPrepare('any','migrate');"", NULL::DATETIME YEAR TO SECOND, CURRENT::DATETIME YEAR TO SECOND, NULL::DATETIME YEAR TO SECOND, NULL::DATETIME YEAR TO SECOND FROM sysmaster:sysdatabases a WHERE (is_logging = 1 OR is_buff_log = 1) AND name NOT LIKE 'sys%' AND 0 = ( SELECT count(*) FROM ph_task WHERE tk_name like 'autoreg migrate %' AND tk_dbs = a.name ); ", NULL::DATETIME YEAR TO SECOND, CURRENT::DATETIME YEAR TO SECOND, NULL::DATETIME YEAR TO SECOND, NULL::DATETIME YEAR TO SECOND, 'f' ); insert into ph_task ( tk_name,tk_type,tk_group, tk_description, tk_execute, tk_start_time,tk_stop_time,tk_delete,tk_frequency,tk_dbs, tk_enable,tk_priority) VALUES ( 'autoreg reset migrate','STARTUP TASK','SERVER', 'Reset for 12.10.xC4* and *C5', 'update ph_task set tk_enable = "t" where tk_name like "autoreg migrate%" and dbinfo("version", "major") = 12 and dbinfo("version", "minor") = 10 and (substr(dbinfo("version","level"), 2,1) = 4 or substr(dbinfo("version","level"), 2,1) = 5);', NULL,NULL,NULL,NULL,'sysadmin','t',4); {************************************************************************** HEALTH CHECKS **************************************************************************} {************************************************************************** Check to make sure backups have been taken **************************************************************************} INSERT INTO ph_threshold(id,name,task_name,value,description) VALUES (0,"REQUIRED LEVEL BACKUP", "check_backup","2", "Maximum number of days between backups of any level."); INSERT INTO ph_threshold(id,name,task_name,value,description) VALUES (0,"REQUIRED LEVEL 0 BACKUP", "check_backup","2", "Maximum number of days between level 0 backups."); CREATE FUNCTION informix.check_backup(task_id INT, task_seq INT) RETURNING INTEGER DEFINE dbspace_num INTEGER; DEFINE dbspace_name CHAR(257); DEFINE req_level INTEGER; DEFINE req_level0 INTEGER; DEFINE level_0 INTEGER; DEFINE level_1 INTEGER; DEFINE level_2 INTEGER; DEFINE arcdist INTERVAL DAY(5) TO SECOND; {*** Select the configuration values ***} select value::integer INTO req_level FROM ph_threshold where name = "REQUIREDLEVEL BACKUP"; select value::integer INTO req_level0 FROM ph_threshold where name = "REQUIRED LEVEL 0 BACKUP"; {*** If not found or are bad values then set better values ***} IF req_level < 1 THEN LET req_level = 1; END IF IF req_level0 < 1 THEN LET req_level0 = 1; END IF {*** Check each dbspaces backup time ***} FOREACH SELECT dbsnum, name, level0, level1, level2 INTO dbspace_num, dbspace_name, level_0, level_1, level_2 FROM sysmaster:sysdbstab WHERE dbsnum > 0 AND sysmaster:bitval(flags, '0x2000')=0 AND ( ((CURRENT - DBINFO("utc_to_datetime",level0) > req_level units day ) AND (CURRENT - DBINFO("utc_to_datetime",level1) > req_level units day ) AND (CURRENT - DBINFO("utc_to_datetime",level2) > req_level units day ) ) OR (CURRENT - DBINFO("utc_to_datetime",level0) > req_level0 units day ) ) {*** Check the dbspaces backup for a level 0 backup ***} IF level_0 == 0 THEN INSERT INTO ph_alert (ID, alert_task_id,alert_task_seq,alert_type, alert_color, alert_object_type, alert_object_name, alert_message,alert_action) VALUES (0,task_id, task_seq, "WARNING", "red", "SERVER",dbspace_name, "Dbspace ["||trim(dbspace_name)|| "] has never had a level 0 backup. Recommend taking a level 0 backup immediately." , NULL ); ELIF CURRENT-DBINFO("utc_to_datetime",level_0) > req_level0 units day THEN LET arcdist = CURRENT - DBINFO("utc_to_datetime",level_0); INSERT INTO ph_alert (ID, alert_task_id,alert_task_seq,alert_type, alert_color, alert_object_type, alert_object_name, alert_message,alert_action) VALUES (0,task_id, task_seq, "WARNING", "red", "SERVER",dbspace_name, "Dbspace ["||trim(dbspace_name)|| "] has not had a level 0 backup for " || arcdist|| ". Recommend taking a level 0 backup immediately." , NULL ); CONTINUE FOREACH; END IF IF level_0 > level_1 THEN LET arcdist = CURRENT - DBINFO("utc_to_datetime",level_0); ELIF level_1 > level_2 THEN LET arcdist = CURRENT - DBINFO("utc_to_datetime",level_1); ELSE LET arcdist = CURRENT - DBINFO("utc_to_datetime",level_2); END IF INSERT INTO ph_alert (ID, alert_task_id,alert_task_seq,alert_type, alert_color, alert_object_type, alert_object_name, alert_message,alert_action) VALUES (0,task_id,task_seq, "WARNING", "red", "SERVER",dbspace_name, "Dbspace ["||trim(dbspace_name)|| "] has not had a backup for " || arcdist||". Recommend taking a backup immediately." , NULL ); END FOREACH RETURN 0; END FUNCTION; INSERT INTO ph_task ( tk_name, tk_type, tk_group, tk_description, tk_execute, tk_start_time, tk_stop_time, tk_frequency ) VALUES ( "check_backup", "TASK", "BACKUP", "Checks to ensure a backup has been taken of the data server.", "check_backup", DATETIME(05:00:00) HOUR TO SECOND, NULL, INTERVAL ( 1 ) DAY TO DAY ); {************************************************************************** HA/MACH11 LOG REPLAY POSITION MONITOR **************************************************************************} --DROP PROCEDURE "informix".ifx_ha_monitor_log_replay ( INTEGER, INTEGER ); CREATE PROCEDURE "informix".ifx_ha_monitor_log_replay ( v_taskid INTEGER, v_seqnum INTEGER ) DEFINE v_rss_servername VARCHAR(128); DEFINE v_state CHAR(1); DEFINE v_replay_loguniq INTEGER; DEFINE v_replay_logpage INTEGER; DEFINE v_capacity LIKE sysmaster:syslogs.size; DEFINE v_distance LIKE sysmaster:syslogs.size; DEFINE v_result INTEGER; /* * We give up easily. */ ON EXCEPTION RETURN; END EXCEPTION; SET LOCK MODE TO WAIT 5; /* * Get the logical log capacity. It's not very likely * to change while we iterate over the HA secondaries. */ SELECT SUM ( size ) INTO v_capacity FROM sysmaster:syslogs WHERE is_pre_dropped = 0 AND is_new = 0 AND is_temp = 0; /* * Iterate over the HA secondaries, * and retrieve their replay positions */ FOREACH SELECT rss_servername , state , replay_loguniq , replay_logpage INTO v_rss_servername , v_state , v_replay_loguniq , v_replay_logpage FROM sysha:rss_tab IF (v_state != 'C' ) THEN CONTINUE FOREACH; END IF /* * Calculate the distance between the * clone's replay and source's current position */ SELECT NVL ( SUM ( size ), 0 ) + v_replay_logpage INTO v_distance FROM sysmaster:syslogs WHERE uniqid < v_replay_loguniq AND is_pre_dropped = 0 AND is_new = 0 AND is_temp = 0; /* * Is this clone's replay position in danger * of being overtaken by the source's current * log position ? * If so, invoke the alarm function. */ IF ( ( ( v_distance / v_capacity ) * 100 ) < 25 ) THEN CALL ifx_ha_fire_logwrap_alarm ( v_rss_servername ) RETURNING v_result; END IF END FOREACH END PROCEDURE; /******************************************************************************/ /* */ /* */ /* */ /******************************************************************************/ INSERT INTO ph_task ( tk_name, tk_type, tk_group, tk_description, tk_execute, tk_start_time, tk_stop_time, tk_frequency, tk_next_execution ) VALUES ( "ifx_ha_monitor_log_replay_task", "TASK", "SERVER", "Monitors HA secondary log replay position", "ifx_ha_monitor_log_replay", NULL, NULL, NULL, NULL ); /* ************************************************************** * Create a task which will cleanup the alert table. ************************************************************** */ DELETE from ph_threshold WHERE name = "ALERT HISTORY RETENTION"; INSERT INTO ph_threshold (name,task_name,value,value_type,description) VALUES ("ALERT HISTORY RETENTION", "Alert Cleanup","15 0:00:00","NUMERIC", "Remove all alerts that are older than then the threshold."); MERGE INTO ph_threshold AS t USING ( SELECT "ALERT HISTORY MAX ROWS", "Alert Cleanup","100000","NUMERIC", "Stop cleaning the ph_alert and ph_run tables after exceeding the cleanup threshold." FROM sysmaster:sysdual) AS s(name,task_name,value,value_type,description) ON t.name = s.name WHEN MATCHED THEN UPDATE SET (t.value_type, t.description) = (s.value_type,s.description ) WHEN NOT MATCHED THEN INSERT (t.name,t.task_name,t.value,t.value_type,t.description) VALUES (s.name,s.task_name,s.value,s.value_type,s.description); CREATE FUNCTION informix.AlertCleanup(task_id INTEGER, seq_id INTEGER) RETURNING INTEGER DEFINE cur_run_id LIKE ph_run.run_id; DEFINE cur_id LIKE ph_alert.id; DEFINE count INTEGER; DEFINE duration DATETIME YEAR TO SECOND; DEFINE max_rows INTEGER; LET count =0; LET duration = ( SELECT CURRENT - MAX(value)::INTERVAL DAY to SECOND FROM ph_threshold WHERE name = 'ALERT HISTORY RETENTION' ); LET max_rows = ( SELECT MAX(value)::INTEGER FROM ph_threshold WHERE name = 'ALERT HISTORY MAX ROWS' ); IF max_rows IS NULL OR max_rows < 100 THEN LET max_rows = 100; END IF FOREACH SELECT id, run_id INTO cur_id, cur_run_id FROM ph_alert, OUTER ph_run WHERE ph_alert.alert_task_id = ph_run.run_task_id AND ph_alert.alert_task_seq = ph_run.run_task_seq AND alert_time < duration ORDER BY id, run_id IF cur_id > 0 THEN DELETE FROM ph_run WHERE run_id < cur_run_id AND run_task_id = cur_id; ELSE DELETE FROM ph_run where run_id = cur_run_id; END IF LET count = count + DBINFO('sqlca.sqlerrd2'); DELETE FROM ph_alert where id = cur_id; LET count = count + 1; IF count > max_rows THEN EXIT FOREACH; END IF END FOREACH RETURN count; END FUNCTION; DELETE from ph_task WHERE tk_name = "Alert Cleanup"; INSERT INTO ph_task ( tk_name, tk_type, tk_group, tk_description, tk_execute, tk_start_time, tk_stop_time, tk_frequency ) VALUES ( "Alert Cleanup", "TASK", "SERVER", "Remove all old alert entries from the system.", "AlertCleanup", DATETIME(02:00:00) HOUR TO SECOND, NULL, INTERVAL ( 1 ) DAY TO DAY ); DELETE FROM ph_task WHERE tk_name="post_alarm_message"; INSERT INTO ph_task ( tk_name, tk_type, tk_group, tk_description, tk_execute, tk_start_time, tk_stop_time, tk_frequency, tk_next_execution, tk_attributes ) VALUES ( "post_alarm_message", "TASK", "SERVER", "System function to post alerts", "ph_dbs_alert", NULL, NULL, NULL, NULL, "0x4000" ); DELETE FROM ph_task WHERE tk_name="Job Runner"; INSERT INTO ph_task ( tk_name, tk_type, tk_group, tk_description, tk_execute, tk_start_time, tk_stop_time, tk_delete, tk_frequency, tk_next_execution, tk_attributes, tk_enable ) VALUES ( "Job Runner", "TASK", "SERVER", "Run server tasks in background with a private dbWorker thread.", "run_job", NULL, NULL, INTERVAL ( 30 ) DAY TO DAY, NULL, NULL, 8, 'f' ); /* ************************************************************** * Create a task to cleanup the ph_bg_jobs_results table. ************************************************************** */ DELETE from ph_threshold WHERE name = "JOB RUNNER HISTORY RETENTION"; INSERT INTO ph_threshold ( name, task_name, value, value_type, description) VALUES ( "JOB RUNNER HISTORY RETENTION", "Job Results Cleanup", "30 0:00:00", "NUMERIC", "Remove all job results that are older than then the threshold."); DELETE from ph_task WHERE tk_name = "Job Results Cleanup"; INSERT INTO ph_task ( tk_name, tk_type, tk_group, tk_description, tk_execute, tk_start_time, tk_stop_time, tk_frequency, tk_delete, tk_enable ) VALUES ( "Job Results Cleanup", "TASK", "TABLES", "Remove all old job results entries from the system.", "DELETE FROM ph_bg_jobs_results WHERE ph_bgr_starttime < ( SELECT CURRENT - value::INTERVAL DAY TO SECOND FROM ph_threshold WHERE name = 'JOB RUNNER HISTORY RETENTION') ", DATETIME(03:00:00) HOUR TO SECOND, NULL, INTERVAL ( 1 ) DAY TO DAY, INTERVAL ( 30 ) DAY TO DAY, 't' ); /* IDLE USERS TIMEOUT */ DELETE FROM ph_threshold WHERE name = "IDLE TIMEOUT"; {*** CREATE THE THRESHOLD : Default = 60 minutes ***} INSERT INTO ph_threshold(name,task_name,value,value_type,description) VALUES("IDLE TIMEOUT", "idle_user_timeout","60","NUMERIC","Maximum amount of time in minutes for non-informix users to be idle."); DELETE FROM ph_task WHERE tk_name = "idle_user_timeout"; {*** CREATE THE task by default the task will not be enabled and is scheduled for every 2 hours ***} INSERT INTO ph_task ( tk_name, tk_type, tk_group, tk_description, tk_execute, tk_next_execution, tk_start_time, tk_stop_time, tk_frequency, tk_delete, tk_enable ) VALUES ( "idle_user_timeout", "TASK", "SERVER", "Terminate idle users", "idle_user_timeout", NULL, NULL, NULL, INTERVAL ( 2 ) HOUR TO HOUR, INTERVAL ( 30 ) DAY TO DAY, 'f' ); --DROP FUNCTION idle_user_timeout( INTEGER , INTEGER ); {*** CREATE the function that will be run by the task ***} CREATE FUNCTION informix.idle_user_timeout( task_id INTEGER, task_seq INTEGER) RETURNING INTEGER DEFINE time_allowed INTEGER; DEFINE sys_hostname CHAR(256); DEFINE sys_username CHAR(32); 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"; {*** Check the value for IDLE TIMEOUT is reasonable , ie: >= 5 minutes ***} IF time_allowed < 5 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, "WARNING", "GREEN", "ADDRESSED", "USER", "TIMEOUT", "Invalid IDLE TIMEOUT value("||time_allowed||"). Needs to be greater than 4", NULL ); RETURN -1; END IF {*** Find all users who have been idle longer than the threshold and try to terminate the session. ***} 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 we sucessfully terminated a user 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; { ***** CREATE THE bad_index_alert task ***** } DELETE FROM ph_task WHERE tk_name = "bad_index_alert"; INSERT INTO ph_task ( tk_name, tk_type, tk_group, tk_description, tk_execute, tk_next_execution, tk_start_time, tk_stop_time, tk_frequency, tk_delete, tk_enable ) VALUES ( "bad_index_alert", "TASK", "SERVER", "Find indices marked as bad and create alert", "bad_index_alert", NULL, DATETIME(04:00:00) HOUR TO SECOND, NULL, INTERVAL ( 1 ) DAY TO DAY, INTERVAL ( 30 ) DAY TO DAY, 'f' ); {*** CREATE the function that will be run by the task ***} --DROP FUNCTION bad_index_alert( int , int ); CREATE FUNCTION informix.bad_index_alert ( task_id INTEGER, task_seq INTEGER) RETURNING INTEGER DEFINE p_partnum INTEGER; DEFINE p_fullname CHAR(300); DEFINE p_database CHAR(128); FOREACH SELECT k.partnum , dbsname , trim(owner)||"."|| tabname AS fullname INTO p_partnum ,p_database , p_fullname FROM sysmaster:sysptnkey k,sysmaster:systabnames t WHERE sysmaster:bitand(flags,"0x00000040") > 0 and k.partnum = t.partnum and dbsname not in ("sysmaster") INSERT INTO ph_alert ( ID, alert_task_id ,alert_task_seq ,alert_type ,alert_color , alert_object_type ,alert_object_name ,alert_message , alert_action_dbs ,alert_action ) VALUES ( 0,task_id, task_seq, "WARNING", "red", "SERVER", p_fullname , "Index "||trim(p_database)||":"||trim(p_fullname)||" is marked as bad." , p_database ,NULL ); END FOREACH; RETURN 0; END FUNCTION; {************************************************************************** Task to add storage, and task to detect low free space **************************************************************************} INSERT INTO ph_task ( tk_name, tk_type, tk_group, tk_description, tk_execute, tk_start_time, tk_stop_time, tk_delete, tk_frequency, tk_next_execution, tk_attributes ) VALUES ( "add_storage", "TASK", "DISK", "Add storage", "adm_add_storage", NULL, NULL, INTERVAL ( 30 ) DAY TO DAY, NULL, NULL, "0x408" ); INSERT INTO ph_task ( tk_name, tk_type, tk_group, tk_description, tk_execute, tk_start_time, tk_stop_time, tk_frequency, tk_delete ) VALUES ( "mon_low_storage", "TASK", "DISK", "Monitor storage and add space when necessary", "mon_low_storage", NULL, NULL, INTERVAL ( 1 ) HOUR TO HOUR, INTERVAL ( 30 ) DAY TO DAY ); {************************************************************************** Task to keep track of I/O statistics **************************************************************************} DELETE FROM ph_threshold WHERE name = "IO_SAMPLES_PER_HOUR"; INSERT INTO ph_threshold (name,task_name,value,value_type,description) VALUES ("IO_SAMPLES_PER_HOUR", "mon_iohistory","1","NUMERIC", "Number of samples per chunk saved on disk every hour. Allowable values are between 1 and 60."); INSERT INTO ph_task ( tk_name, tk_type, tk_group, tk_description, tk_result_table, tk_create, tk_execute, tk_start_time, tk_stop_time, tk_delete, tk_frequency, tk_next_execution ) VALUES ( "mon_iohistory", "SENSOR", "PERFORMANCE", "Collect performance information about chunk I/O.", "mon_iohistory", "create table informix.mon_iohist (ID integer, gfd smallint, time integer, read_ops_minute integer, read_time_minute smallfloat, write_ops_minute integer, write_time_minute smallfloat); create view informix.mon_iohistory (id,gfd,time,read_ops_minute,read_time_minute,avg_read_time, write_ops_minute, write_time_minute, avg_write_time ) as SELECT id, gfd, DBINFO('utc_to_datetime',time) as time, read_ops_minute,read_time_minute::decimal(12,6), decode(read_time_minute,0,0, read_time_minute/read_ops_minute)::decimal(12,6) , write_ops_minute, write_time_minute::decimal(12,6), decode(write_time_minute,0,0, write_time_minute/write_ops_minute)::decimal(12,6) from mon_iohist; grant select on mon_iohistory TO 'db_monitor' as informix;grant select on mon_iohist TO 'db_monitor' as informix;", "INSERT INTO informix.mon_iohist(ID,gfd,time, read_ops_minute, read_time_minute, write_ops_minute, write_time_minute) SELECT $DATA_SEQ_ID,gfd,time,read_ops_minute,read_time_minute, write_ops_minute, write_time_minute FROM sysmaster:sysiohistory WHERE time > ( select NVL(max(run_mttime),0) from ph_task , outer ph_run where tk_name='mon_iohistory' and tk_id = run_task_id) AND MOD(minute, 60/NVL((select value FROM ph_threshold WHERE name = 'IO_SAMPLES_PER_HOUR'),'1')::char(4)::integer)=0", NULL, NULL, INTERVAL ( 30 ) DAY TO DAY, INTERVAL ( 58 ) MINUTE TO MINUTE, DATETIME(01:00:00) HOUR TO SECOND ); {************************************************************************** Auto compress task ****************************************************************************} INSERT INTO ph_task ( tk_name, tk_type, tk_group, tk_description, tk_execute, tk_start_time, tk_stop_time, tk_delete, tk_frequency, tk_next_execution, tk_attributes, tk_enable ) VALUES ( "auto_compress", "TASK", "SERVER", "Auto compress", "adm_auto_compress", NULL, NULL, INTERVAL ( 30 ) DAY TO DAY, NULL, NULL, "0x4008", 'f' ); /* AUTO TUNE CPU VPS */ INSERT INTO ph_task ( tk_name, tk_type, tk_group, tk_description, tk_execute, tk_start_time, tk_stop_time, tk_frequency, tk_next_execution, tk_delete, tk_enable ) VALUES ( "auto_tune_cpu_vps", "STARTUP TASK", "SERVER", "Automatically allocate additional cpu vps at system start.", "auto_tune_cpu_vps", NULL, NULL, NULL, NULL, INTERVAL ( 30 ) DAY TO DAY, 'f' ); {*** CREATE the function that will be run by the task auto_tune_cpu_vps ***} {*** The number of desired cpu vps is limited to 8 *** } {*** if the os has 3 or more cpus then we use 50% *** } {*** if the os has 3 then we use 2 *** } {*** if the os has < 3 we use 1 *** } CREATE FUNCTION informix.auto_tune_cpu_vps(task_id INTEGER, task_seq INTEGER) RETURNING INTEGER DEFINE current_cpu_vps INTEGER; DEFINE desired_cpu_vps INTEGER; DEFINE add_cpu_vps INTEGER; DEFINE rc INTEGER; LET add_cpu_vps = 0; {* check the value of SINGLE_CPU_VP , if it is set then we cannot add any additional cpu vps so we can return early *} SELECT cf_effective INTO add_cpu_vps FROM sysmaster:sysconfig WHERE cf_name = "SINGLE_CPU_VP"; IF add_cpu_vps != 0 THEN RETURN 0; END IF SELECT count(*) INTO current_cpu_vps FROM sysmaster:sysvplst WHERE classname="cpu"; SELECT CASE WHEN os_num_procs > 16 THEN 8 WHEN os_num_procs > 3 THEN os_num_procs/2 WHEN os_num_procs = 3 THEN 2 ELSE 1 END::INTEGER INTO desired_cpu_vps FROM sysmaster:sysmachineinfo; LET add_cpu_vps = desired_cpu_vps - current_cpu_vps; IF add_cpu_vps > 0 THEN INSERT INTO ph_alert (ID, alert_task_id,alert_task_seq,alert_type, alert_color, alert_object_type, alert_object_name, alert_message,alert_action) VALUES (0,task_id, task_seq, "INFO", "YELLOW", "SERVER","CPU VPS", "Dynamically adding "||add_cpu_vps|| " cpu vp(s)." , NULL); LET rc = admin( "onmode", "p",add_cpu_vps,"cpu"); ELSE LET rc = 0; END IF RETURN rc; END FUNCTION; {*** Automatically find and address compress, repack, *** *** shrink, defragment candiates ***} DELETE FROM ph_threshold WHERE name IN ( "AUTOCOMPRESS_ENABLED", "AUTOCOMPRESS_ROWS" , "AUTOREPACK_ENABLED", "AUTOREPACK_SPACE" , "AUTOSHRINK_ENABLED", "AUTOSHRINK_UNUSED" , "AUTODEFRAG_ENABLED", "AUTODEFRAG_EXTENTS" ); {*** CREATE THE auto_crsd THRESHOLDS ***} {********** compress parameters ****************} MERGE INTO ph_threshold AS t USING ( SELECT "AUTOCOMPRESS_ENABLED", "auto_crsd","F","STRING", "Auto Compression of tables/fragments is enabled." FROM sysmaster:sysdual) AS s(name,task_name,value,value_type,description) ON t.name = s.name WHEN MATCHED THEN UPDATE SET (t.value_type, t.description) = (s.value_type,s.description ) WHEN NOT MATCHED THEN INSERT (t.name,t.task_name,t.value,t.value_type,t.description) VALUES (s.name,s.task_name,s.value,s.value_type,s.description); MERGE INTO ph_threshold AS t USING ( SELECT "AUTOCOMPRESS_ROWS", "auto_crsd","50000","NUMERIC", "Number of rows required in the table/fragment." FROM sysmaster:sysdual) AS s(name,task_name,value,value_type,description) ON t.name = s.name WHEN MATCHED THEN UPDATE SET (t.value_type, t.description) = (s.value_type,s.description ) WHEN NOT MATCHED THEN INSERT (t.name,t.task_name,t.value,t.value_type,t.description) VALUES (s.name,s.task_name,s.value,s.value_type,s.description); {********** repack parameters ****************} MERGE INTO ph_threshold AS t USING ( SELECT "AUTOREPACK_ENABLED", "auto_crsd","F","STRING", "Auto Repack of tables is enabled." FROM sysmaster:sysdual) AS s(name,task_name,value,value_type,description) ON t.name = s.name WHEN MATCHED THEN UPDATE SET (t.value_type, t.description) = (s.value_type,s.description ) WHEN NOT MATCHED THEN INSERT (t.name,t.task_name,t.value,t.value_type,t.description) VALUES (s.name,s.task_name,s.value,s.value_type,s.description); MERGE INTO ph_threshold AS t USING ( SELECT "AUTOREPACK_SPACE", "auto_crsd","50","NUMERIC", "The percentage of space free before a repack is run." FROM sysmaster:sysdual) AS s(name,task_name,value,value_type,description) ON t.name = s.name WHEN MATCHED THEN UPDATE SET (t.value_type, t.description) = (s.value_type,s.description ) WHEN NOT MATCHED THEN INSERT (t.name,t.task_name,t.value,t.value_type,t.description) VALUES (s.name,s.task_name,s.value,s.value_type,s.description); {********** shrink parameters ****************} MERGE INTO ph_threshold AS t USING ( SELECT "AUTOSHRINK_ENABLED", "auto_crsd","F","STRING", "Auto Shrink of tables is enabled." FROM sysmaster:sysdual) AS s(name,task_name,value,value_type,description) ON t.name = s.name WHEN MATCHED THEN UPDATE SET (t.value_type, t.description) = (s.value_type,s.description ) WHEN NOT MATCHED THEN INSERT (t.name,t.task_name,t.value,t.value_type,t.description) VALUES (s.name,s.task_name,s.value,s.value_type,s.description); MERGE INTO ph_threshold AS t USING ( SELECT "AUTOSHRINK_UNUSED", "auto_crsd","50","NUMERIC", "Percentage of unused space." FROM sysmaster:sysdual) AS s(name,task_name,value,value_type,description) ON t.name = s.name WHEN MATCHED THEN UPDATE SET (t.value_type, t.description) = (s.value_type,s.description ) WHEN NOT MATCHED THEN INSERT (t.name,t.task_name,t.value,t.value_type,t.description) VALUES (s.name,s.task_name,s.value,s.value_type,s.description); {********* defrag parameters ********} MERGE INTO ph_threshold AS t USING ( SELECT "AUTODEFRAG_ENABLED", "auto_crsd","F","STRING", "Auto Defrag of tables is enabled." FROM sysmaster:sysdual) AS s(name,task_name,value,value_type,description) ON t.name = s.name WHEN MATCHED THEN UPDATE SET (t.value_type, t.description) = (s.value_type,s.description ) WHEN NOT MATCHED THEN INSERT (t.name,t.task_name,t.value,t.value_type,t.description) VALUES (s.name,s.task_name,s.value,s.value_type,s.description); MERGE INTO ph_threshold AS t USING ( SELECT "AUTODEFRAG_EXTENTS", "auto_crsd","100","NUMERIC","Number of extents." FROM sysmaster:sysdual) AS s(name,task_name,value,value_type,description) ON t.name = s.name WHEN MATCHED THEN UPDATE SET (t.value_type, t.description) = (s.value_type,s.description ) WHEN NOT MATCHED THEN INSERT (t.name,t.task_name,t.value,t.value_type,t.description) VALUES (s.name,s.task_name,s.value,s.value_type,s.description); {********** outstanding in-place alter parameters ****************} MERGE INTO ph_threshold AS t USING ( SELECT "REMOVE_IPA_ENABLED", "auto_crsd","F","STRING", "Outstanding removal of in-place alters." FROM sysmaster:sysdual) AS s(name,task_name,value,value_type,description) ON t.name = s.name WHEN MATCHED THEN UPDATE SET (t.value_type, t.description) = (s.value_type,s.description ) WHEN NOT MATCHED THEN INSERT (t.name,t.task_name,t.value,t.value_type,t.description) VALUES (s.name,s.task_name,s.value,s.value_type,s.description); UPDATE ph_task SET tk_attributes = BITANDNOT(tk_attributes, 4) WHERE tk_name = "auto_crsd"; DELETE FROM ph_task WHERE tk_name = "auto_crsd"; {*** CREATE THE task by default the task will not be enabled and is scheduled for every 7 days at 3:00am ***} INSERT INTO ph_task ( tk_name, tk_type, tk_group, tk_description, tk_execute, tk_start_time, tk_stop_time, tk_next_execution, tk_frequency, tk_delete, tk_enable ) VALUES ( "auto_crsd", "TASK", "SERVER", "Automatic Compress/Repack/Shrink and Defrag", "auto_crsd", DATETIME(03:00:00) HOUR TO SECOND, NULL, NULL, INTERVAL ( 7 ) DAY TO DAY, INTERVAL ( 30 ) DAY TO DAY, 'f' ); DROP FUNCTION IF EXISTS auto_crsd( INTEGER , INTEGER ); CREATE FUNCTION informix.auto_crsd( g_task_id INTEGER, g_task_seq INTEGER) RETURNING INTEGER DEFINE p_enabled LIKE sysadmin:ph_threshold.value; DEFINE p_value INTEGER; DEFINE p_fulltabname LVARCHAR(300); DEFINE p_dbsname LIKE sysmaster:sysdatabases.name; DEFINE p_owner LIKE sysmaster:systabnames.owner; DEFINE p_tabname LIKE sysmaster:systabnames.tabname; DEFINE p_partnum LIKE sysmaster:systabnames.partnum; DEFINE p_beforedatacnt LIKE sysmaster:sysptnhdr.npdata; DEFINE p_beforepagecnt LIKE sysmaster:sysptnhdr.nptotal; DEFINE p_afterdatacnt LIKE sysmaster:sysptnhdr.npdata; DEFINE p_afterpagecnt LIKE sysmaster:sysptnhdr.nptotal; DEFINE p_retcode INTEGER; DEFINE p_alerttype LIKE sysadmin:ph_alert.alert_type; DEFINE p_alertcolor LIKE sysadmin:ph_alert.alert_color; DEFINE p_alerttext LIKE sysadmin:ph_alert.alert_message; DEFINE sys_dbs_list SET ( VARCHAR(255) NOT NULL ); LET sys_dbs_list = SET { "system", "sysmaster", "sysutils", "sysuser","syscdr", "syscdcv1" }; --SET DEBUG FILE TO "/tmp/compress."||g_task_seq; --TRACE ON; {*********** Check if AUTOCOMPRESS is enabled ***********} LET p_enabled = "F"; SELECT UPPER(value) INTO p_enabled FROM sysadmin:ph_threshold WHERE name = "AUTOCOMPRESS_ENABLED"; IF ( p_enabled == 'T' ) THEN {*** SELECT tables that qualify *** } SELECT value::INTEGER INTO p_value FROM sysadmin:ph_threshold WHERE name = "AUTOCOMPRESS_ROWS"; IF p_value IS NOT NULL AND p_value >= 2000 THEN FOREACH SELECT TRIM(T.dbsname) , TRIM(T.owner) , TRIM(T.tabname) , T.partnum , H.npdata, H.nptotal INTO p_dbsname , p_owner , p_tabname , p_partnum ,p_beforedatacnt , p_beforepagecnt FROM sysmaster:sysptnhdr H , sysmaster:systabnames T WHERE (H.nkeys=0) -- only data partnums AND H.nrows > p_value -- qualifying number of rows -- do no include system database tables. AND dbsname NOT IN sys_dbs_list -- do not include partitions that do not -- qualify to avoid unnecessary errors AND bitand(H.partnum,"0xFFFFF") != 1 AND bitand(H.flags,'0xA32F6084') ==0 AND H.partnum = T.partnum AND T.tabname NOT IN ( SELECT N.tabname FROM systables N where N.tabid < 100 ) LET p_fulltabname = TRIM(p_dbsname)||":"||TRIM(p_owner)||"."||TRIM(p_tabname); EXECUTE FUNCTION admin('fragment compress',p_partnum) INTO p_retcode; IF p_retcode < 0 THEN LET p_alerttype = "ERROR"; LET p_alertcolor = "RED"; LET p_alerttext = "Error ["||p_retcode||"] when auto compressing partnum: "||p_partnum||" ["||TRIM(p_fulltabname)||"]."; ELSE SELECT npdata, nptotal INTO p_afterdatacnt , p_afterpagecnt FROM sysmaster:sysptnhdr H WHERE H.partnum = p_partnum; LET p_alerttype = "INFO"; LET p_alertcolor = "GREEN"; LET p_alerttext = "Automatically compressed table:["||p_partnum||"] ["||TRIM(p_fulltabname)||"] went from "||p_beforedatacnt||"/"||p_beforepagecnt||" data/total pages to "||p_afterdatacnt||"/"||p_afterpagecnt||" data/total pages."; END IF INSERT INTO ph_alert (ID, alert_task_id,alert_task_seq, alert_type, alert_color, alert_object_type, alert_object_name, alert_message,alert_action) VALUES (0,g_task_id, g_task_seq, p_alerttype, p_alertcolor, "DATABASE",TRIM(p_fulltabname), p_alerttext, NULL); END FOREACH; END IF END IF {*********** Check if AUTOREPACK is enabled ***********} LET p_enabled = "F"; LET p_retcode = 0; SELECT UPPER(value) INTO p_enabled FROM sysadmin:ph_threshold WHERE name = "AUTOREPACK_ENABLED"; IF ( p_enabled == 'T' ) THEN {*** SELECT tables that qualify *** } SELECT value::INTEGER INTO p_value FROM sysadmin:ph_threshold WHERE name = "AUTOREPACK_SPACE"; IF p_value IS NOT NULL AND p_value > 0 AND p_value < 100 THEN FOREACH SELECT TRIM(dbsname) as dbsname, TRIM(owner) as owner , TRIM(tabname) as tabname , T.partnum INTO p_dbsname , p_owner , p_tabname , p_partnum FROM ( SELECT pnum , count(*) f FROM ( SELECT pe_extnum as extnum , pe_size as size , pe_partnum as pnum , count(*) as free FROM sysmaster:sysptnext E, sysmaster:sysptnbit B WHERE E.pe_partnum = B.PB_partnum AND ( bitand(pb_bitmap,12) = 0 or bitand(pb_bitmap,12) = 4 ) AND E.pe_log <= B.pb_pagenum AND B.pb_pagenum < E.pe_log + E.pe_size GROUP BY 1,2,3 ) as Y , sysmaster:sysptnhdr as H , sysmaster:systabnames as T WHERE pnum = H.partnum AND T.partnum = H.partnum AND (H.nkeys=0) -- only data partnums GROUP BY 1 ) , sysmaster:sysptnhdr as H , sysmaster:systabnames as T WHERE pnum = T.partnum AND T.partnum = H.partnum AND nextns > 4 AND (H.nkeys=0) -- only data partnums AND (f/nextns)*100::decimal(10,2) > p_value -- do no include system database tables. AND dbsname NOT IN sys_dbs_list -- do not include partitions that do not -- qualify to avoid unnecessary errors AND bitand(H.partnum,"0xFFFFF") != 1 AND bitand(H.flags,'0xA32F6084') ==0 AND T.tabname NOT IN ( SELECT N.tabname FROM systables N where N.tabid < 100 ) LET p_fulltabname = TRIM(p_dbsname)||":"||TRIM(p_owner)||"."||TRIM(p_tabname); EXECUTE FUNCTION admin('fragment repack',p_partnum) INTO p_retcode; IF p_retcode < 0 THEN LET p_alerttype = "ERROR"; LET p_alertcolor = "RED"; LET p_alerttext = "Error ["||p_retcode||"] when auto repacking partnum: "||p_partnum||" ["||TRIM(p_fulltabname)||"]."; ELSE SELECT npdata, nptotal INTO p_afterdatacnt , p_afterpagecnt FROM sysmaster:sysptnhdr H WHERE H.partnum = p_partnum; LET p_alerttype = "INFO"; LET p_alertcolor = "GREEN"; LET p_alerttext = "Automatically repacked table:["||p_partnum||"] ["||TRIM(p_fulltabname); END IF INSERT INTO ph_alert (ID, alert_task_id,alert_task_seq, alert_type, alert_color, alert_object_type, alert_object_name, alert_message,alert_action) VALUES (0,g_task_id, g_task_seq, p_alerttype, p_alertcolor, "DATABASE",TRIM(p_fulltabname), p_alerttext, NULL); END FOREACH; END IF END IF {*********** Check if AUTOSHRINK is enabled ***********} LET p_enabled = "F"; LET p_retcode = 0; SELECT UPPER(value) INTO p_enabled FROM sysadmin:ph_threshold WHERE name = "AUTOSHRINK_ENABLED"; IF ( p_enabled == 'T' ) THEN {*** SELECT tables that qualify *** } SELECT value::INTEGER INTO p_value FROM sysadmin:ph_threshold WHERE name = "AUTOSHRINK_UNUSED"; IF p_value IS NOT NULL AND p_value > 0 AND p_value < 100 THEN FOREACH SELECT TRIM(T.dbsname) , TRIM(T.owner) , TRIM(T.tabname) , T.partnum , H.npdata, H.nptotal INTO p_dbsname , p_owner , p_tabname , p_partnum ,p_beforedatacnt , p_beforepagecnt FROM sysmaster:sysptnhdr H , sysmaster:systabnames T WHERE (H.nkeys=0) -- only data partnums AND ( (SELECT count(*) FROM sysmaster:sysptnbit B WHERE B.pb_partnum = H.partnum AND B. pb_bitmap in ( 0 , 8 ) AND B. pb_pagenum > (H.nptotal*(1-p_value/100))-3 ) + (H.nptotal - H.npused) > TRUNC(H.nptotal * (p_value/100)) - 1 ) AND H.nptotal > 8 AND H.nptotal > fextsiz AND H.nextns > 0 AND H.partnum = T.partnum -- do no include system database tables. AND dbsname NOT IN sys_dbs_list -- do not include partitions that do not -- qualify to avoid unnecessary errors AND bitand(H.partnum,"0xFFFFF") != 1 AND bitand(H.flags,'0xA32F6084') ==0 AND T.tabname NOT IN ( SELECT N.tabname FROM systables N where N.tabid < 100 ) LET p_fulltabname = TRIM(p_dbsname)||":"||TRIM(p_owner)||"."||TRIM(p_tabname); EXECUTE FUNCTION admin('fragment shrink',p_partnum) INTO p_retcode; IF p_retcode < 0 THEN LET p_alerttype = "ERROR"; LET p_alertcolor = "RED"; LET p_alerttext = "Error ["||p_retcode||"] when auto shrinking partnum: "||p_partnum||" ["||TRIM(p_fulltabname)||"]."; ELSE SELECT npdata, nptotal INTO p_afterdatacnt , p_afterpagecnt FROM sysmaster:sysptnhdr H WHERE H.partnum = p_partnum; LET p_alerttype = "INFO"; LET p_alertcolor = "GREEN"; LET p_alerttext = "Automatically shrunk table:["||p_partnum||"] ["||TRIM(p_fulltabname)||"] went from "||p_beforedatacnt||"/"||p_beforepagecnt||" data/total pages to "||p_afterdatacnt||"/"||p_afterpagecnt||" data/total pages."; END IF INSERT INTO ph_alert (ID, alert_task_id,alert_task_seq, alert_type, alert_color, alert_object_type, alert_object_name, alert_message,alert_action) VALUES (0,g_task_id, g_task_seq, p_alerttype, p_alertcolor, "DATABASE",TRIM(p_fulltabname), p_alerttext, NULL); END FOREACH; END IF END IF {*********** Check if AUTODEFRAG is enabled ***********} LET p_enabled = "F"; LET p_retcode = 0; SELECT UPPER(value) INTO p_enabled FROM sysadmin:ph_threshold WHERE name = "AUTODEFRAG_ENABLED"; IF ( p_enabled == 'T' ) THEN {*** SELECT tables that qualify *** } SELECT value::INTEGER INTO p_value FROM sysadmin:ph_threshold WHERE name = "AUTODEFRAG_EXTENTS"; IF p_value IS NOT NULL AND p_value > 0 THEN FOREACH SELECT TRIM(T.dbsname) , TRIM(T.owner) , TRIM(T.tabname) , T.partnum , H.nextns INTO p_dbsname , p_owner , p_tabname , p_partnum ,p_beforedatacnt FROM sysmaster:sysptnhdr H , sysmaster:systabnames T WHERE H.partnum = T.partnum AND H.nextns > p_value -- qualifying number of extents -- do no include system database tables. AND dbsname NOT IN sys_dbs_list -- do not include partitions that do not -- qualify to avoid unnecessary errors AND bitand(H.partnum,"0xFFFFF") != 1 AND bitand(H.flags,'0xA32F6084') ==0 LET p_fulltabname = TRIM(p_dbsname)||":"||TRIM(p_owner)||"."||TRIM(p_tabname); EXECUTE FUNCTION admin('defragment partnum',p_partnum) INTO p_retcode; IF p_retcode < 0 THEN LET p_alerttype = "ERROR"; LET p_alertcolor = "RED"; LET p_alerttext = "Error ["||p_retcode||"] when auto defragging partnum: "||p_partnum||" ["||TRIM(p_fulltabname)||"]."; ELSE SELECT nextns INTO p_afterdatacnt FROM sysmaster:sysptnhdr H WHERE H.partnum = p_partnum; LET p_alerttype = "INFO"; LET p_alertcolor = "GREEN"; LET p_alerttext = "Automatically defragged table:["||p_partnum||"] ["||TRIM(p_fulltabname)||"] went from "||p_beforedatacnt||" extents to "||p_afterdatacnt||" extents."; END IF INSERT INTO ph_alert (ID, alert_task_id,alert_task_seq, alert_type, alert_color, alert_object_type, alert_object_name, alert_message,alert_action) VALUES (0,g_task_id, g_task_seq, p_alerttype, p_alertcolor, "DATABASE",TRIM(p_fulltabname), p_alerttext, NULL); END FOREACH; END IF END IF {*********** Check if REMOVE_IPA_ENABLED is enabled ***********} LET p_enabled = "F"; SELECT UPPER(value) INTO p_enabled FROM sysadmin:ph_threshold WHERE name = "REMOVE_IPA_ENABLED"; IF ( p_enabled == 'T' ) THEN {*** SELECT tables that qualify *** } FOREACH SELECT dbsname , trim(owner)||"."|| tabname AS fullname INTO p_dbsname , p_tabname FROM sysmaster:sysptnhdr h,sysmaster:systabnames t WHERE h.partnum = t.partnum and dbsname not in ("sysmaster") and pta_totpgs != 0 LET p_fulltabname = TRIM(p_dbsname)||":"||TRIM(p_tabname); EXECUTE FUNCTION admin('table update_ipa', p_tabname, p_dbsname) INTO p_retcode; IF p_retcode < 0 THEN LET p_alerttype = "ERROR"; LET p_alertcolor = "RED"; LET p_alerttext = "Error ["||p_retcode||"] when removing outstanding in-place alters for: ["||TRIM(p_fulltabname)||"]."; ELSE LET p_alerttype = "INFO"; LET p_alertcolor = "GREEN"; LET p_alerttext = "Automatically removed outstanding in-place alters for:["||TRIM(p_fulltabname)||"]."; END IF INSERT INTO ph_alert (ID, alert_task_id,alert_task_seq, alert_type, alert_color, alert_object_type, alert_object_name, alert_message,alert_action) VALUES (0,g_task_id, g_task_seq, p_alerttype, p_alertcolor, "DATABASE",TRIM(p_fulltabname), p_alerttext, NULL); END FOREACH; END IF RETURN 0; END FUNCTION; {*** CHECK for in place alters ***} DELETE FROM ph_task WHERE tk_name = "check_for_ipa"; {*** CREATE THE task by default the task will not be enabled and is scheduled for every 7 days ***} INSERT INTO ph_task ( tk_name, tk_type, tk_group, tk_description, tk_execute, tk_next_execution, tk_start_time, tk_stop_time, tk_frequency, tk_delete, tk_enable ) VALUES ( "check_for_ipa", "TASK", "SERVER", "Find tables with outstanding in place alters", "check_for_ipa", NULL, DATETIME(04:00:00) HOUR TO SECOND, NULL, INTERVAL ( 7 ) DAY TO DAY, INTERVAL ( 30 ) DAY TO DAY, 'f' ); {*** CREATE the function that will be run by the task ***} DROP FUNCTION IF EXISTS informix.check_for_ipa( int , int ); CREATE FUNCTION informix.check_for_ipa ( task_id INTEGER, task_seq INTEGER) RETURNING INTEGER DEFINE p_fullname CHAR(300); DEFINE p_database CHAR(128); FOREACH SELECT dbsname , trim(owner)||"."|| tabname AS fullname INTO p_database , p_fullname FROM sysmaster:sysptnhdr h,sysmaster:systabnames t WHERE h.partnum = t.partnum and dbsname not in ("sysmaster") and pta_totpgs != 0 INSERT INTO ph_alert ( ID, alert_task_id ,alert_task_seq ,alert_type ,alert_color , alert_object_type ,alert_object_name ,alert_message , alert_action_dbs ,alert_action ) VALUES ( 0,task_id, task_seq, "INFO", "red", "SERVER", p_fullname , "Table "||trim(p_database)||":"||trim(p_fullname)||" has outstanding in place alters." , p_database ,NULL ); END FOREACH; RETURN 0; END FUNCTION; DELETE FROM ph_task WHERE tk_name="refresh_table_stats"; INSERT INTO ph_task ( tk_name, tk_type, tk_group, tk_description, tk_execute, tk_start_time, tk_stop_time, tk_frequency, tk_next_execution, tk_enable ) VALUES ( "refresh_table_stats", "TASK", "SERVER", "System function to refresh table statistics", "refreshstats", NULL, NULL, NULL, NULL, 'f' ); /******************** * LOG FILE ROTATION * *********************/ DELETE FROM ph_threshold WHERE name = "MAX_MSGPATH_VERSIONS"; DELETE FROM ph_threshold WHERE name = "MAX_BAR_ACT_LOG_VERSIONS"; DELETE FROM ph_threshold WHERE name = "MAX_BAR_DEBUG_LOG_VERSIONS"; INSERT INTO ph_threshold(id,name,task_name,value,value_type,description) VALUES (0,"MAX_MSGPATH_VERSIONS", "online_log_rotate","12","NUMERIC" ,"Maximum number of online log files to keep."); INSERT INTO ph_threshold(id,name,task_name,value,value_type,description) VALUES (0,"MAX_BAR_ACT_LOG_VERSIONS", "bar_act_log_rotate","12","NUMERIC" ,"Maximum number of bar act log files to keep."); INSERT INTO ph_threshold(id,name,task_name,value,value_type,description) VALUES (0,"MAX_BAR_DEBUG_LOG_VERSIONS", "bar_debug_log_rotate","12","NUMERIC" ,"Maximum number of bar debug log files to keep."); --DROP FUNCTION IF EXISTS admin_message_log_rotate(INT,INT,VARCHAR(255),INT); CREATE FUNCTION informix.admin_message_log_rotate(task_id INT, task_seq INT , param VARCHAR(255) , max_files_to_keep INT DEFAULT NULL) RETURNING INTEGER; DEFINE path_to_message_log LVARCHAR(513); DEFINE is_default BOOLEAN; DEFINE ret_code INTEGER; DEFINE temp_max_files_to_keep INTEGER; DEFINE p_alert_type LIKE ph_alert.alert_type; DEFINE p_alert_color LIKE ph_alert.alert_color; DEFINE p_alert_obj_type LIKE ph_alert.alert_object_type; DEFINE p_alert_obj_name LIKE ph_alert.alert_object_name; DEFINE p_alert_message LIKE ph_alert.alert_message; DEFINE p_alert_action LIKE ph_alert.alert_action; LET ret_code = 0; { check we have a valid param } IF param IS NULL OR param = "" OR param = "ROOTPATH" OR param = "MIRRORPATH" THEN LET ret_code = -1; LET p_alert_type = "WARNING"; LET p_alert_color = "GREEN"; LET p_alert_obj_type = "SERVER"; LET p_alert_obj_name = "PARAM"; LET p_alert_message = "Invalid param("||param||") passed to admin_message_log_rotate"; LET p_alert_action = NULL; GOTO ALERT_RETURN; END IF { check max_files_to_keep if its not passed in then look in sysadmin:ph_threshold for MAX_param_VERSIONS if thats not found use the default of 12 } IF max_files_to_keep IS NULL THEN SELECT value::integer INTO temp_max_files_to_keep FROM sysadmin:ph_threshold WHERE name = "MAX_"||param||"_VERSIONS"; IF temp_max_files_to_keep IS NULL THEN LET temp_max_files_to_keep = 12; END IF LET max_files_to_keep = temp_max_files_to_keep; END IF { bound check the max_files_to_keep } IF max_files_to_keep <= 0 OR max_files_to_keep > 99 THEN LET ret_code = -1; LET p_alert_type = "WARNING"; LET p_alert_color = "GREEN"; LET p_alert_obj_type = "SERVER"; LET p_alert_obj_name = "VERSIONS"; LET p_alert_message = "Invalid value for the number of versions to keep("||max_files_to_keep||") It must be greater than 0 and less than 99."; LET p_alert_action = NULL; GOTO ALERT_RETURN; END IF { get the path name to the file } { first check if its an onconfig param } SELECT TRIM(cf_effective) INTO path_to_message_log FROM sysmaster:syscfgtab WHERE cf_name = param; { quick check that we are not using STDOUT } IF ( path_to_message_log == "/dev/tty" ) THEN LET ret_code = -1; LET p_alert_type = "WARNING"; LET p_alert_color = "GREEN"; LET p_alert_obj_type = "SERVER"; LET p_alert_obj_name = "PATH_NAME"; LET p_alert_message = param||" maybe using STDOUT. Cannot rotate."; LET p_alert_action = NULL; GOTO ALERT_RETURN; END IF { check if the param is a ph_threshold PATH_FOR_xxx } IF path_to_message_log IS NULL THEN SELECT value::lvarchar INTO path_to_message_log FROM sysadmin:ph_threshold WHERE name = "PATH_FOR_"||param; IF path_to_message_log IS NULL THEN LET path_to_message_log = param; END IF END IF EXECUTE FUNCTION sysadmin:admin("file rotate",path_to_message_log,max_files_to_keep) INTO ret_code; IF ret_code < 0 THEN LET p_alert_type = "WARNING"; LET p_alert_color = "GREEN"; LET p_alert_obj_type = "SERVER"; LET p_alert_obj_name = "PATH_NAME"; LET p_alert_message = "FILE ROTATE failed "; LET p_alert_action = NULL; GOTO ALERT_RETURN; END IF RETURN ret_code; <> INSERT INTO ph_alert (ID, alert_task_id,alert_task_seq,alert_type, alert_color, alert_object_type, alert_object_name, alert_message,alert_action) VALUES (0,task_id, task_seq, p_alert_type , p_alert_color ,p_alert_obj_type ,p_alert_obj_name , p_alert_message , p_alert_action); RETURN ret_code; END FUNCTION; DELETE FROM ph_task WHERE tk_name = "online_log_rotate"; DELETE FROM ph_task WHERE tk_name = "bar_act_log_rotate"; DELETE FROM ph_task WHERE tk_name = "bar_debug_log_rotate"; INSERT INTO ph_task ( tk_name, tk_type, tk_group, tk_description, tk_execute, tk_start_time, tk_stop_time, tk_next_execution, tk_frequency, tk_delete, tk_enable ) VALUES ( "online_log_rotate", "TASK", "SERVER", "Rotate the Online Log", "execute function admin_message_log_rotate($DATA_TASK_ID,$DATA_SEQ_ID,'MSGPATH')", DATETIME(03:00:00) HOUR TO SECOND, DATETIME(03:01:00) HOUR TO SECOND, NULL, INTERVAL ( 30 ) DAY TO DAY, INTERVAL ( 30 ) DAY TO DAY, 'f' ); INSERT INTO ph_task ( tk_name, tk_type, tk_group, tk_description, tk_execute, tk_start_time, tk_stop_time, tk_next_execution, tk_frequency, tk_delete, tk_enable ) VALUES ( "bar_act_log_rotate", "TASK", "SERVER", "Rotate the BAR ACT Log", "execute function admin_message_log_rotate($DATA_TASK_ID,$DATA_SEQ_ID,'BAR_ACT_LOG')", DATETIME(03:00:00) HOUR TO SECOND, DATETIME(03:01:00) HOUR TO SECOND, NULL, INTERVAL ( 30 ) DAY TO DAY, INTERVAL ( 30 ) DAY TO DAY, 'f' ); INSERT INTO ph_task ( tk_name, tk_type, tk_group, tk_description, tk_execute, tk_start_time, tk_stop_time, tk_next_execution, tk_frequency, tk_delete, tk_enable ) VALUES ( "bar_debug_log_rotate", "TASK", "SERVER", "Rotate the Bar Debug Log", "execute function admin_message_log_rotate($DATA_TASK_ID,$DATA_SEQ_ID,'BAR_DEBUG_LOG')", DATETIME(03:00:00) HOUR TO SECOND, DATETIME(03:01:00) HOUR TO SECOND, NULL, INTERVAL ( 30 ) DAY TO DAY, INTERVAL ( 30 ) DAY TO DAY, 'f' ); {************************************************************************** Compression task **************************************************************************} DELETE FROM ph_threshold WHERE name in ( "COMPRESSION TABLE TIMEOUT", "COMPRESSION TABLE ROW COUNT"); INSERT INTO ph_threshold (id,name,task_name,value,value_type,description) VALUES (0,"COMPRESSION TABLE TIMEOUT", "compress_table","900", "NUMERIC", "The amount of time in seconds we will wait for tables to have data in them."); INSERT INTO ph_threshold (id,name,task_name,value,value_type,description) VALUES (0,"COMPRESSION TABLE ROW COUNT", "compress_table","2000", "NUMERIC", "The number of pages we must find in a fragment of a table before a compression dictionary will be created." ); DROP FUNCTION IF EXISTS compress_table; CREATE FUNCTION compress_table(task_id INTEGER, task_seq INTEGER, tabname LVARCHAR ) RETURNING INTEGER DEFINE timeout INTEGER; DEFINE fragments_left INTEGER; DEFINE row_count INTEGER; DEFINE fragid INTEGER; DEFINE rc INTEGER; DEFINE cnt INTEGER; DEFINE created_at DATETIME YEAR TO SECOND; -- To debug enable uncomment these lines --SET DEBUG FILE TO "/tmp/debug.out."||task_id; --TRACE ON; --TRACE "CALLING function compress_table ("||task_id||","||task_seq||","||tabname||")"; -- Get the config thresholds SELECT MAX(value::integer) INTO timeout FROM sysadmin:ph_threshold WHERE name = "COMPRESSION TABLE TIMEOUT"; IF timeout IS NULL THEN LET timeout = 900; ELIF timeout < 0 THEN LET timeout = 10; ELIF timeout > 3600 THEN LET timeout = 3600; END IF SELECT MAX(value::integer) INTO row_count FROM sysadmin:ph_threshold WHERE name = "COMPRESSION TABLE ROW COUNT"; IF row_count IS NULL OR row_count < 1000 THEN LET row_count = 1000; END IF BEGIN ON EXCEPTION DROP TABLE IF EXISTS pt_list; INSERT INTO ph_alert (ID, alert_task_id,alert_task_seq,alert_type, alert_color, alert_object_type, alert_object_name, alert_message,alert_action) VALUES (0,task_id, task_seq, "INFO", "YELLOW", "SERVER","compress_table", "Failed to build compression dictionaries on " ||TRIM(tabname), NULL); END EXCEPTION IF tabname IS NULL THEN RETURN -1; END IF LET fragments_left = 99; LET cnt = 0; SELECT P.lockid FROM sysmaster:systabnames T, sysmaster:sysptnhdr P WHERE TRIM(t.dbsname)||":"||TRIM(T.tabname) = tabname --WHERE T.tabname = tabname AND P.lockid = T.partnum AND P.nkeys = 0 AND bitand( P.flags, '0x08000000' ) = 0 INTO TEMP pt_list WITH NO LOG; CREATE INDEX ix_temp_pt_list ON pt_list(lockid); WHILE ( timeout > 0 AND fragments_left > 0 ) FOREACH SELECT P.partnum INTO fragid FROM pt_list L, sysmaster:sysptnhdr P WHERE l.lockid = P.partnum AND P.nrows > row_count AND bitand( P.flags, '0x08000000' ) = 0 LET rc = admin('fragment create_dictionary', fragid); IF rc >= 0 THEN DELETE FROM pt_list WHERE lockid = fragid; LET cnt = cnt + 1; END IF END FOREACH SELECT NVL( count(*) , 0 ) INTO fragments_left FROM pt_list L, sysmaster:sysptnhdr P WHERE l.lockid = p.partnum AND P.nkeys = 0 AND bitand( P.flags, '0x08000000' ) = 0; LET rc = yieldn(1); LET timeout = timeout - 1; END WHILE END DROP TABLE IF EXISTS pt_list; INSERT INTO ph_alert (ID, alert_task_id,alert_task_seq,alert_type, alert_color, alert_object_type, alert_object_name, alert_message,alert_action) VALUES (0,task_id, task_seq, "INFO", "GREEN", "SERVER","compress_table", "Built "||cnt||" compression dictionaries on " ||TRIM(tabname), NULL); RETURN 0; END FUNCTION; DELETE FROM ph_task WHERE tk_name = "compress_table"; INSERT INTO ph_task ( tk_name, tk_type, tk_group, tk_description, tk_execute, tk_start_time, tk_stop_time, tk_frequency, tk_delete, tk_enable ) VALUES ( "compress_table", "TASK", "TABLES", "Task to be kicked off when loading a table to ensure data is compressed", "compress_table", NULL, NULL, INTERVAL ( 1 ) DAY TO DAY, INTERVAL ( 30 ) DAY TO DAY, 'f' ); DROP FUNCTION IF EXISTS informix.sync_registry( INTEGER, INTEGER); {************************************************************************** Task to sync the windows registry **************************************************************************} CREATE FUNCTION informix.sync_registry(task_id INTEGER, ID INTEGER) RETURNING INTEGER DEFINE old_file_time INTEGER; DEFINE new_file_time INTEGER; DEFINE command_id INTEGER; DEFINE osname VARCHAR(255); --SET DEBUG FILE TO "/tmp/debug.out."||task_id; --TRACE ON; LET new_file_time = 0; {* This taks utilizes the ph_run table. * If the tk_delete time is shorter than * the tk_frequency then this task will * not cache the saved results *} SELECT NVL(run_retcode,0) INTO old_file_time FROM ph_task, OUTER ph_run WHERE run_task_id = tk_id AND run_task_seq = tk_sequence AND tk_name = "sync_registry"; IF old_file_time == 0 THEN SELECT os_name INTO osname FROM sysmaster:sysmachineinfo; IF osname <> 'Windows' THEN -- If this is not windows turn off this task UPDATE ph_task SET tk_enable='f' WHERE tk_name = "sync_registry"; RETURN 0; END IF END IF LET command_id = ABS(admin("export sqlhosts", old_file_time)); SELECT cmd_ret_status INTO new_file_time FROM command_history WHERE cmd_number = command_id; RETURN new_file_time; END FUNCTION; DELETE FROM ph_task where tk_name = "sync_registry"; INSERT INTO ph_task ( tk_name, tk_type, tk_group, tk_description, tk_execute, tk_delete, tk_start_time, tk_stop_time, tk_frequency ) VALUES ( "sync_registry", "TASK", "SERVER", "Sync the sqlhost file to the Windows Registery", "sync_registry", INTERVAL ( 60 ) MINUTE TO MINUTE, NULL, NULL, INTERVAL ( 15 ) MINUTE TO MINUTE ); {************************************************************************** Rolling Window Tables **************************************************************************} DELETE FROM ph_task WHERE tk_name IN ("purge_tables", "db_purge_tables"); DELETE FROM ph_threshold WHERE name IN ("RWT LOGLEVEL", "RWT IMMEDIATE", "RWT LOCKMODE"); INSERT INTO ph_task ( tk_name, tk_type, tk_group, tk_description, tk_execute, tk_start_time, tk_stop_time, tk_frequency, tk_next_execution, tk_delete, tk_enable ) VALUES ( "purge_tables", "TASK", "TABLES", "Daily task to ensure that rolling window tables stay within limits", "rwt_purge_tables", DATETIME(00:45:00) HOUR TO SECOND, NULL, INTERVAL (1) DAY TO DAY, (TODAY + 1)::DATETIME HOUR TO SECOND + 45 UNITS MINUTE, NULL, 't' ); INSERT INTO ph_task ( tk_name, tk_type, tk_group, tk_description, tk_execute, tk_start_time, tk_stop_time, tk_frequency, tk_delete, tk_enable ) VALUES ( "db_purge_tables", "TASK", "TABLES", "Task to ensure that rolling window tables stay within limits for a database", "rwt_db_purge_tables", NULL, NULL, INTERVAL (1) DAY TO DAY, NULL, 'f' ); { RWT tasks run in private threads } UPDATE ph_task SET tk_attributes = BITOR(tk_attributes, 8) WHERE tk_name IN ('purge_tables', 'db_purge_tables'); {************************************************************************** Task to automatically start json listener if present **************************************************************************} DROP FUNCTION IF EXISTS informix.json_listener(INT, INT); CREATE FUNCTION informix.json_listener(task_id INT, task_seq INT) RETURNING INTEGER DEFINE rc INTEGER; DEFINE tmp INTEGER; DEFINE prop_file VARCHAR(130); DEFINE msg LVARCHAR; FOREACH SELECT TRIM(value) INTO prop_file FROM ph_threshold WHERE name MATCHES "JSON LISTENER PROP*" EXECUTE FUNCTION admin("json listener start", prop_file) INTO rc; IF rc < 0 THEN LET msg = ""; SELECT cmd_ret_msg INTO msg FROM command_history WHERE cmd_number = rc; INSERT INTO ph_alert (ID, alert_task_id,alert_task_seq,alert_type, alert_color, alert_object_type, alert_object_name, alert_message,alert_action) VALUES (0,task_id, task_seq, "INFO", "GREEN", "SERVER", prop_file, "JSON Listener "||TRIM(prop_file)||" failed to started. "||TRIM(msg), NULL); ELSE INSERT INTO ph_alert (ID, alert_task_id,alert_task_seq,alert_type, alert_color, alert_object_type, alert_object_name, alert_message,alert_action) VALUES (0,task_id, task_seq, "INFO", "GREEN", "SERVER", prop_file, "JSON Listener "||TRIM(prop_file)||" started", NULL); END IF END FOREACH END FUNCTION; UPDATE ph_task SET tk_attributes = 0 WHERE tk_name = "json listener"; DELETE FROM ph_task where tk_name = "json listener"; INSERT INTO ph_task ( tk_name, tk_type, tk_group, tk_description, tk_execute, tk_start_time, tk_stop_time, tk_frequency, tk_next_execution, tk_delete, tk_enable ) VALUES ( "json listener", "STARTUP TASK", "SERVER", "Start JSON Listener", "json_listener", NULL, NULL, INTERVAL ( 10 ) SECOND TO SECOND, NULL, INTERVAL ( 30 ) DAY TO DAY, 't' ); {************************************************************************** Task to start LO cleaner (sbspclean) thread **************************************************************************} DROP FUNCTION IF EXISTS informix.locleaner(INT, INT); CREATE FUNCTION informix.locleaner(task_id INT, task_seq INT) RETURNING INTEGER DEFINE rc INTEGER; DEFINE msg LVARCHAR; EXECUTE FUNCTION admin("start locleaner") INTO rc; IF rc < 0 THEN LET msg = ""; SELECT cmd_ret_msg INTO msg FROM command_history WHERE cmd_number = rc; INSERT INTO ph_alert (ID, alert_task_id,alert_task_seq,alert_type, alert_color, alert_object_type, alert_object_name, alert_message,alert_action) VALUES (0,task_id, task_seq, "INFO", "GREEN", "SERVER", "LO Cleaner", "LO Cleaner (sbspclean thread) failed to started. "||TRIM(msg), NULL); ELSE INSERT INTO ph_alert (ID, alert_task_id,alert_task_seq,alert_type, alert_color, alert_object_type, alert_object_name, alert_message,alert_action) VALUES (0,task_id, task_seq, "INFO", "GREEN", "SERVER", "LO Cleaner", "LO Cleaner (sbspclean thread) started. ", NULL); END IF END FUNCTION; UPDATE ph_task SET tk_attributes = 0 WHERE tk_name = "LO Cleaner Startup"; DELETE FROM ph_task where tk_name = "LO Cleaner Startup"; INSERT INTO ph_task ( tk_name, tk_type, tk_group, tk_description, tk_execute, tk_start_time, tk_stop_time, tk_frequency, tk_next_execution, tk_delete, tk_enable ) VALUES ( "LO Cleaner Startup", "STARTUP TASK", "SERVER", "Start LO Cleaner on startup", "locleaner", NULL, NULL, INTERVAL ( 10 ) SECOND TO SECOND, NULL, INTERVAL ( 30 ) DAY TO DAY, 'f' ); UPDATE ph_task SET tk_attributes = 0 WHERE tk_name = "LO Cleaner Task"; DELETE FROM ph_task where tk_name = "LO Cleaner Task"; INSERT INTO ph_task ( tk_name, tk_type, tk_group, tk_description, tk_execute, tk_start_time, tk_stop_time, tk_frequency, tk_next_execution, tk_delete, tk_enable ) VALUES ( "LO Cleaner Task", "TASK", "SERVER", "LO Cleaner Task that can be scheduled", "locleaner", DATETIME ( 00:30:00 ) HOUR TO SECOND, NULL, INTERVAL ( 01 00:00:00 ) DAY TO SECOND, NULL, INTERVAL ( 30 ) DAY TO DAY, 'f' ); EXECUTE PROCEDURE IFX_ALLOW_NEWLINE('T'); UPDATE ph_task SET tk_attributes = BITANDNOT(tk_attributes, 4) WHERE tk_name = "mongo_pam_auth"; DELETE FROM ph_task where tk_name = "mongo_pam_auth"; INSERT INTO ph_task ( tk_name, tk_type, tk_group, tk_description, tk_result_table, tk_dbs, tk_execute, tk_create, tk_frequency, tk_next_execution, tk_delete, tk_enable ) VALUES ( "mongo_pam_auth", "STARTUP SENSOR", "MISC", "Create if required and load the sysmongouser_ext from sysmongousers for the wire listener client to authenticate via pam.", NULL, "sysuser", "select count(*) FROM sysmongousers;", "CREATE FUNCTION IF NOT EXISTS informix.sync_mongo_users(task_id INT DEFAULT -1, task_seq INT DEFAULT -1) RETURNING INTEGER DEFINE ifxdir VARCHAR(130); DEFINE cmd VARCHAR(255); CREATE TABLE IF NOT EXISTS sysmongousers ( username nchar(32), hashed_password varchar(128) ) lock mode row; CREATE UNIQUE INDEX IF NOT EXISTS sysmongousers_idx_username ON sysmongousers(username) IN TABLE; SELECT TRIM(NVL(env_value,'/usr/informix')) INTO ifxdir FROM sysmaster:sysenv WHERE env_name = 'INFORMIXDIR'; LET cmd = 'CREATE EXTERNAL TABLE IF NOT EXISTS ' || 'sysmongousers_ext SAMEAS sysmongousers ' || 'USING ( DATAFILES ( ''DISK:' || ifxdir || '/etc/mongohash'') ) '; EXECUTE IMMEDIATE cmd; REVOKE ALL ON sysmongousers FROM PUBLIC AS INFORMIX; GRANT SELECT ON sysmongousers TO PUBLIC AS INFORMIX; INSERT into sysmongousers_ext select * FROM sysmongousers ORDER BY username; drop table sysmongousers_ext; RETURN DBINFO('sqlca.sqlerrd2'); END FUNCTION; ", INTERVAL ( 5 ) SECOND TO SECOND, NULL, INTERVAL ( 30 ) DAY TO DAY, 'f' ); {************************************************************************** MARK THE ABOVE TASKS AS SYSTEM TASKS **************************************************************************} UPDATE ph_task SET tk_attributes = BITOR(tk_attributes, 4) WHERE tk_name IN ('mon_command_history', 'mon_config', 'mon_config_startup', 'mon_sysenv', 'mon_profile', 'mon_vps', 'mon_checkpoint', 'mon_memory_system', 'mon_table_profile', 'mon_table_names', 'mon_users', 'check_backup', 'ifx_ha_monitor_log_replay_task', 'Alert Cleanup', 'post_alarm_message', 'Job Runner', 'Job Results Cleanup', 'idle_user_timeout', 'auto_tune_cpu_vps', 'auto_crsd', 'add_storage', 'mon_low_storage', 'refresh_table_stats', 'online_log_rotate','bar_act_log_rotate','bar_debug_log_rotate', 'autoreg exe', 'autoreg vp', 'autoreg migrate-console', 'bad_index_alert','check_for_ipa','compress_table', 'mon_chunk', 'purge_tables', 'db_purge_tables', 'mon_iohistory', 'json listener', 'mongo_pam_auth', 'mongo_pam_init' ); {************************************************************************** UPDATE STATISTICS **************************************************************************} UPDATE STATISTICS; EXECUTE FUNCTION TASK("tenant load"); CLOSE DATABASE;