analytics/informix/etc/sysadmin/sch_tasks.sql

{**************************************************************************}
{*                                                                        *}
{*  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;

<<ALERT_RETURN>>
        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;