analytics/informix/etc/sysadmin/sch_oat.sql

{**************************************************************************}
{*                                                                        *}
{*  Licensed Materials - Property of IBM and/or HCL                                  *}
{*                                                                        *}
{*  IBM Informix Dynamic Server                                           *}
{*  Copyright IBM Corporation 2001, 2015                                  *}
{*  (c) Copyright HCL Technologies Ltd. 2017.  All Rights Reserved.                                  *}
{*                                                                        *}
{**************************************************************************}

DATABASE sysadmin;

{****************************************************}
{***                                              ***}
{***        IWA-OAT Integration                   ***}
{***        dwa_start_capture                     ***}
{***        dwa_stop_capture                      ***}
{***                                              ***}
{****************************************************}
DROP FUNCTION IF EXISTS
  dwa_start_capture(VARCHAR(128),VARCHAR(50),INT,INT,INT);
CREATE FUNCTION dwa_start_capture(
  warehouse_db VARCHAR(128),    /* name of the warehouse database          */
  uname VARCHAR(50),            /* optional: user name                     */
  sess_id INT,                  /* optional: session ID                    */
  tr_num INT,                   /* optional: number of traces              */
  tr_size INT)                  /* optional: size (kB) of single trace row */
  RETURNING INT AS sql_id,      /* last sqlid before capture started or    */
                                /* zero if no trace record exists.         */
    INT as sql_finishtime,      /* last finishtime before capture started  */
                                /* or zero if no trace record exists.      */
    VARCHAR(255) AS message;    /* dummy message                           */

  /* Required database connection: sysadmin database as user informix.     */
  /* Function to start workload capture: set tracing parameters and        */
  /* retrieve sql_id and sql_finishtime of the last trace entry in the     */
  /* trace buffer. The combination of both serves as unique begin marker   */
  /* for the capture in the trace buffer.                                  */
  /* When not providing values for the optional parameter: pass NULL.      */
  /* When specifying a user name, the tracing will be narrowed down for    */
  /* that user only, accumulating less trace records and hence minmizing   */
  /* the risk of trace buffer wrap around.                                 */
  /* Specifying a session ID can narrow down tracing even more.            */
  /* Default for tr_num is 10000, for tr_size it is 32 (kB).               */

  DEFINE sqlid INT;
  DEFINE sqlfinishtime INT;
  DEFINE task_result VARCHAR(255);
  DEFINE esql INT;
  DEFINE eisam INT;

  BEGIN
    ON EXCEPTION SET esql, eisam
      ROLLBACK WORK;
      RAISE EXCEPTION esql, eisam;
    END EXCEPTION

    IF ((warehouse_db IS NULL) OR (TRIM(warehouse_db) == '')) THEN
      RAISE EXCEPTION -1822;
    END IF

    BEGIN WORK;

    LET sqlid = -1;
    LET sqlfinishtime = -1;

    IF (tr_num is NULL) THEN
      LET tr_num = 10000;
    END IF
    IF (tr_size is NULL) THEN
      LET tr_size = 32;
    END IF

    LET task_result = task('set sql tracing database clear');
    LET task_result = task('set sql tracing database', warehouse_db);

    LET task_result = task('set sql tracing user clear');
    IF (sess_id is not NULL) THEN
      LET task_result = task('set sql tracing session', 'on', sess_id);
    ELIF (uname is not NULL) THEN
      LET task_result = task('set sql tracing user add', uname);
    END IF

    IF (uname is not NULL) THEN
      LET task_result =task('set sql tracing on', tr_num, tr_size, 'low',
                            'user');
    ELSE
      LET task_result = task('set sql tracing on', tr_num, tr_size, 'low',
                             'global');
    END IF

    LET task_result = task('set sql tracing resume');

    SELECT MAX(sql_id) INTO sqlid FROM sysmaster:syssqltrace;
    IF (sqlid is NULL) THEN
      LET sqlid = 0;
      LET sqlfinishtime = 0;
    ELSE
      SELECT sql_finishtime INTO sqlfinishtime FROM sysmaster:syssqltrace
        WHERE sql_id = sqlid;
    END IF

    COMMIT WORK;
  END

  RETURN sqlid, sqlfinishtime, task_result;
END FUNCTION;


DROP FUNCTION IF EXISTS dwa_stop_capture();
CREATE FUNCTION dwa_stop_capture()
  RETURNING INT AS sql_id,    /* id of last trace record within capture    */
    INT AS sql_finishtime,    /* finishtime of last record within capture  */
    VARCHAR(255) as message;  /* dummy message                             */

  /* Required database connection: sysadmin database as user informix.     */
  /* Function to stop workload capture: retrieve sql_id and sql_finishtime */
  /* of the last trace entry in the trace buffer. The combination of both  */
  /* serves as unique end marker for the capture in the trace buffer.      */
  /* Tracing is suspended to avoid wrap around of the trace buffer between */
  /* the stop action and the saving of the trace entries to a separate     */
  /* workload table by a call to dwa_fill_wltab().                         */

  DEFINE sqlid INT;
  DEFINE sqlfinishtime INT;
  DEFINE task_result VARCHAR(255);
  DEFINE esql INT;
  DEFINE eisam INT;

  BEGIN
    ON EXCEPTION SET esql, eisam
      ROLLBACK WORK;
      RAISE EXCEPTION esql, eisam;
    END EXCEPTION

    BEGIN WORK;

    LET sqlid = -1;
    LET sqlfinishtime = -1;

    LET task_result = task('set sql tracing suspend');

    SELECT MAX(sql_id) INTO sqlid FROM sysmaster:syssqltrace;
    IF (sqlid is NULL) THEN
      LET sqlid = -1;
      LET sqlfinishtime = -1;
    ELSE
      SELECT sql_finishtime INTO sqlfinishtime FROM sysmaster:syssqltrace
        WHERE sql_id = sqlid;
    END IF

    COMMIT WORK;
  END

  RETURN sqlid, sqlfinishtime, task_result;
END FUNCTION;


{****************************************************}
{***                                              ***}
{***        Crete GLS Files                       ***}
{***                                              ***}
{****************************************************}
EXECUTE FUNCTION admin('create glfiles');

DROP PROCEDURE IF EXISTS oat_create_gls_table;
DROP TABLE IF EXISTS locales_ext;

CREATE PROCEDURE oat_create_gls_table( )
DEFINE cmd          CHAR(4096);
DEFINE unload_file  VARCHAR(255);
DEFINE bad_file     VARCHAR(255);
DEFINE ifxdir       VARCHAR(255);
DEFINE osname       VARCHAR(255);

--SET DEBUG FILE TO "/tmp/oat_debug.out";
--TRACE ON;

    SELECT env_value INTO ifxdir FROM sysmaster:sysenv 
                     WHERE env_name = "INFORMIXDIR";
    SELECT os_name INTO osname FROM sysmaster:sysmachineinfo;
    IF (osname = 'Windows') THEN
        LET unload_file = '\gls\glsinfo.csv';
        LET bad_file = (select TRIM(env_value) from sysmaster:sysenv where env_name = 'DBTEMP') ||  '\'||DBINFO('sessionid')||'glsinfo_bad.csv';
    ELSE
        LET unload_file = '/gls/glsinfo.csv';
        LET bad_file = (select TRIM(env_value) from sysmaster:sysenv where env_name = 'DBTEMP') ||  '/'||DBINFO('sessionid')||'glsinfo_bad.csv';
    END IF

   EXECUTE IMMEDIATE "DROP TABLE IF EXISTS informix.locales_ext";

   LET cmd = "CREATE EXTERNAL TABLE informix.locales_ext (" ||
                   " filename varchar(200)," ||
                   " language varchar(200)," ||
                   " territory varchar(200)," ||
                   " modifier varchar(200)," ||
                   " codeset varchar(200)," ||
                   " name varchar(200), " ||
                   " lc_source_version integer, " ||
                   " cm_source_version integer " ||
                   " ) USING ( " ||
                   " DATAFILES  ('DISK:"||TRIM(ifxdir)||unload_file||"'),"||
                   " REJECTFILE '"||TRIM(bad_file)||"',"||
                   " FORMAT 'delimited'," ||
                   " DELIMITER ',')";

   EXECUTE IMMEDIATE cmd;
END PROCEDURE;
EXECUTE PROCEDURE oat_create_gls_table();




{****************************************************}
{***                                              ***}
{***        Background admin Task                 ***}
{***                                              ***}
{****************************************************}
DROP FUNCTION IF EXISTS admin_async(lvarchar, CHAR(129), lvarchar(1024),
                        DATETIME hour to second, DATETIME hour to second,
                        INTERVAL day(2) to second, BOOLEAN, BOOLEAN,
                        BOOLEAN, BOOLEAN, BOOLEAN, BOOLEAN, BOOLEAN );
CREATE FUNCTION admin_async(cmd lvarchar(4096),
          cur_group CHAR(129),
          comments lvarchar(1024)
                     DEFAULT "Background admin API",
          start_time DATETIME hour to second
                     DEFAULT CURRENT hour to second,
          end_time   DATETIME hour to second
                     DEFAULT NULL,
          frequency  INTERVAL day(2) to second
                     DEFAULT NULL,
          monday    BOOLEAN DEFAULT 't',
          tuesday   BOOLEAN DEFAULT 't',
          wednesday BOOLEAN DEFAULT 't',
          thursday  BOOLEAN DEFAULT 't',
          friday    BOOLEAN DEFAULT 't',
          saturday  BOOLEAN DEFAULT 't',
          sunday    BOOLEAN DEFAULT 't'
          )
   RETURNING INTEGER
   DEFINE ret_task_id  INTEGER;
   DEFINE del_time     INTERVAL DAY TO SECOND;
   DEFINE id           INTEGER;
   DEFINE task_id      INTEGER;
   DEFINE seq_id       INTEGER;
   DEFINE cmd_num      INTEGER;
   DEFINE boot_time    DATETIME YEAR TO SECOND;

--SET DEBUG FILE TO "/tmp/debug.out";
--TRACE ON;

   IF cur_group IS NULL THEN
       LET cur_group = "MISC";
   END IF

   SELECT FIRST 1 value::INTERVAL DAY TO SECOND INTO del_time FROM ph_threshold
      WHERE name = "BACKGROUND TASK HISTORY RETENTION";
   IF del_time IS NULL THEN
       LET del_time = 7 UNITS DAY;
   END IF

    BEGIN
        ON EXCEPTION IN ( -310, -316 )
        END EXCEPTION WITH RESUME

            CREATE TABLE IF NOT EXISTS job_status (
               js_id         SERIAL,
               js_task       INTEGER,
               js_seq        INTEGER,
               js_comment    LVARCHAR(512),
               js_command    LVARCHAR(4096),
               js_start      DATETIME year to second
                             DEFAULT CURRENT year to second,
               js_done       DATETIME year to second DEFAULT NULL,
               js_result     INTEGER
           );
            CREATE INDEX IF NOT EXISTS job_status_ix1 ON job_status(js_id);
            CREATE INDEX IF NOT EXISTS job_status_ix2 ON job_status(js_task);
            CREATE INDEX IF NOT EXISTS job_status_ix3 ON job_status(js_result);
     END

     BEGIN
        ON EXCEPTION IN ( -8301 )
        END EXCEPTION WITH RESUME

        CREATE SEQUENCE background_task START 1 NOMAXVALUE ;

     END

    INSERT INTO ph_task
        ( tk_name,
        tk_description,
        tk_type,
        tk_group,
        tk_execute,
        tk_start_time,
        tk_stop_time,
        tk_frequency,
        tk_Monday,
        tk_Tuesday,
        tk_Wednesday,
        tk_Thursday,
        tk_Friday,
        tk_Saturday,
        tk_Sunday,
        tk_attributes
        )
        VALUES
        (
        "Background Task ("||  background_task.NEXTVAL ||")",
        TRIM(comments),
        "TASK",
        cur_group,
        "insert into job_status (js_task, js_seq , js_comment,js_command) VALUES($DATA_TASK_ID,$DATA_SEQ_ID, '"||TRIM(comments)||"','"||TRIM(REPLACE(REPLACE(cmd,"'"),"""") )||"' ); update job_status set (js_result)= ( admin("||TRIM(cmd)||") ) WHERE js_task = $DATA_TASK_ID AND js_seq = $DATA_SEQ_ID ;update job_status set (js_done)  = ( CURRENT ) WHERE js_task = $DATA_TASK_ID AND js_seq = $DATA_SEQ_ID ",
        start_time,
        end_time,
        frequency,
        monday,
        tuesday,
        wednesday,
        thursday,
        friday,
        saturday,
        sunday,
        8);

   LET ret_task_id = DBINFO("sqlca.sqlerrd1");

   /* Cleanup the job_status table */

   SELECT dbinfo('UTC_TO_DATETIME',sh_boottime)
          INTO boot_time
           FROM sysmaster:sysshmvals;

   FOREACH  SELECT js_id, js_task, js_seq, js_result
        INTO id, task_id,  seq_id, cmd_num
        FROM job_status J, OUTER ph_run, command_history
        WHERE  ( CURRENT - js_done > del_time OR
                (js_start < boot_time AND js_done IS NULL ) )
        AND    js_task = run_task_id
        AND    js_seq  = run_task_seq
        AND    js_result = ABS(cmd_number)

       DELETE FROM ph_run WHERE run_task_id = task_id
                          AND run_task_seq = seq_id;
       DELETE FROM command_history WHERE cmd_number = cmd_num;
       DELETE FROM job_status WHERE js_id = id;

       -- Cleanup the task table only if this is not a repeating task
       DELETE FROM ph_task WHERE tk_id = task_id AND tk_next_execution IS NULL;

   END FOREACH

   RETURN  ret_task_id;
END FUNCTION;
   
  	
INSERT INTO ph_threshold
    (name,task_name,value,value_type,description)
    VALUES
    ("BACKGROUND TASK HISTORY RETENTION", "Cleanup background tasks",
     "7 0:00:00","NUMERIC",
     "Remove all dormant background tasks that are older than then the threshold.");

DROP TABLE IF EXISTS jobs_status;
CREATE TABLE IF NOT EXISTS job_status (
               js_id         SERIAL,
               js_task       INTEGER,
               js_seq        INTEGER,
               js_comment    LVARCHAR(512),
               js_command    LVARCHAR(4096),
               js_start      DATETIME year to second
                             DEFAULT CURRENT year to second,
               js_done       DATETIME year to second DEFAULT NULL,
               js_result     INTEGER
           );

CREATE INDEX IF NOT EXISTS job_status_ix1 ON job_status(js_id);
CREATE INDEX IF NOT EXISTS job_status_ix2 ON job_status(js_task);
CREATE INDEX IF NOT EXISTS job_status_ix3 ON job_status(js_result);


{****************************************************}
{***        Compression                           ***}
{***                                              ***}
{***  mon_estimate_compression                    ***}
{***  get_compression_estimate                    ***}
{***  admin_async_estimates                       ***}
{***  mon_page_usage                              ***}
{***                                              ***}
{****************************************************}

DROP FUNCTION IF EXISTS mon_estimate_compression( INTEGER, INTEGER);
CREATE FUNCTION mon_estimate_compression(task_id integer, id integer)
       RETURNING integer;

DEFINE p INTEGER;
DEFINE d VARCHAR(128);
DEFINE o VARCHAR(32);
DEFINE t VARCHAR(128);
DEFINE e VARCHAR(255);

    FOREACH SELECT tab.partnum , TRIM(dbsname) AS dbsname , 
            TRIM(owner) AS owner, TRIM(tabname) AS tabname,
            ( SUBSTR( sysadmin:task('fragment estimate_compression',
                                     tab.partnum) , 102 ) )
            INTO p , d , o , t , e
            FROM sysmaster:systabnames tab , sysmaster:sysptnhdr hdr
            WHERE dbsname NOT IN ( 'sysmaster','sysutils','syscdr',
                                   'sysuser','system', 'syscdcv1','syscdcv2',
                                   'syscdcv3','syscdcv4','syscdcv5','syscdcv6',
                                   'syscdcv7', 'syscdcv8','syscdcv9')
            AND tabname != dbsname
            AND ((nrows >= 2000 AND npdata > 0)  OR (npdata = 0 AND npused >= 500))
            AND tab.partnum = hdr.partnum
            AND tabname != 'TBLSpace'
            AND bitand(flags,'0x0004') != 4
	    -- Time series
	    AND bitand(flags,2147483648) != 2147483648
            AND tabname NOT IN ( SELECT tabname FROM systables WHERE tabid < 100 ) 

      INSERT INTO  mon_compression_estimates
            (id, est_partnum , est_dbname , est_owner , est_tabname , est_estimate )
            VALUES
            (id, p,d,o,t,e);

     END FOREACH

     RETURN 0;
END FUNCTION;

MERGE 
INTO ph_group AS t
USING ( select "COMPRESSION","Compression Tasks"  FROM sysmaster:sysdual) as s(grp,desc)
ON t.group_name = s.grp
WHEN MATCHED THEN UPDATE
    SET t.group_description = s.desc
WHEN NOT MATCHED THEN  INSERT
    (t.group_id,t.group_name, t.group_description)
    VALUES
    (0,s.grp, s.desc);

DELETE FROM ph_task WHERE tk_name = 'mon_compression_estimates';
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_compression_estimates',
'SENSOR',
'COMPRESSION',
'Get compression estimates',
'mon_compression_estimates',
'create table mon_compression_estimates(id  integer, est_partnum integer,est_dbname varchar(128),est_owner  varchar(32),est_tabname varchar(128),est_estimate lvarchar(32000),est_date datetime year to second default CURRENT year to second); create index mon_estimate_compression_idx1 on mon_compression_estimates ( est_partnum );',
'mon_estimate_compression',
NULL,
DATETIME(02:30:00) HOUR TO SECOND,
INTERVAL ( 7 ) DAY TO DAY,
INTERVAL ( 30 ) DAY TO DAY
);



DROP FUNCTION IF EXISTS get_compression_estimate( VARCHAR(128), VARCHAR(12), 
                                          VARCHAR(128), VARCHAR(32) );
CREATE FUNCTION get_compression_estimate( pnum varchar(128), comptype varchar(12), 
                                          dbname varchar(128) , owner varchar(32) )
        RETURNING int;

DEFINE e    LVARCHAR;
DEFINE cmd  INTEGER;

--SET DEBUG FILE TO "/tmp/debug2.out";
--TRACE ON;

     IF comptype == 'fragment' THEN
          SELECT admin(comptype||' estimate_compression',pnum) 
                INTO cmd FROM sysmaster:sysdual ;
     ELSE
          SELECT admin(comptype||' estimate_compression',pnum , dbname , owner ) 
                INTO cmd FROM sysmaster:sysdual ;
     END IF

     RETURN cmd;
END FUNCTION;


DROP FUNCTION admin_async_estimates(varchar(128), varchar(12), varchar(128),
                      varchar(32), CHAR(129), lvarchar(1024), 
                      DATETIME hour to second, DATETIME hour to second,
                      INTERVAL day(2) to second, BOOLEAN, BOOLEAN,  
                      BOOLEAN,  BOOLEAN, BOOLEAN, BOOLEAN,  BOOLEAN);

CREATE FUNCTION admin_async_estimates(cmd varchar(128),
                          comptype varchar(12),
                          dbname varchar(128),
                          owner  varchar(32),
                          cur_group CHAR(129),
                          comments lvarchar(1024)
                               DEFAULT "Background admin API",
                          start_time DATETIME hour to second
                               DEFAULT CURRENT hour to second,
                          end_time   DATETIME hour to second
                               DEFAULT NULL,
                          frequency  INTERVAL day(2) to second
                               DEFAULT NULL,
                          monday    BOOLEAN DEFAULT 't',
                          tuesday   BOOLEAN DEFAULT 't',
                          wednesday BOOLEAN DEFAULT 't',
                          thursday  BOOLEAN DEFAULT 't',
                          friday    BOOLEAN DEFAULT 't',
                          saturday  BOOLEAN DEFAULT 't',
                          sunday    BOOLEAN DEFAULT 't'
                          )
   RETURNING INTEGER
   DEFINE ret_task_id  INTEGER;
   DEFINE del_time     INTERVAL DAY TO SECOND;
   DEFINE id           INTEGER;
   DEFINE task_id      INTEGER;
   DEFINE seq_id       INTEGER;
   DEFINE cmd_num      INTEGER;
   DEFINE boot_time    DATETIME YEAR TO SECOND;

   IF cur_group IS NULL THEN
       LET cur_group = 'MISC';
   END IF

   SELECT FIRST 1 value::INTERVAL DAY TO SECOND INTO del_time FROM ph_threshold
      WHERE name = 'BACKGROUND TASK HISTORY RETENTION';
   IF del_time IS NULL THEN
       LET del_time = 7 UNITS DAY;
   END IF

    BEGIN
        ON EXCEPTION IN ( -310, -316 )
        END EXCEPTION WITH RESUME

            CREATE TABLE IF NOT EXISTS job_status (
               js_id         SERIAL,
               js_task       INTEGER,
               js_seq        INTEGER,
               js_comment    LVARCHAR(512),
               js_command    LVARCHAR(4096),
               js_start      DATETIME year to second
                             DEFAULT CURRENT year to second,
               js_done       DATETIME year to second DEFAULT NULL,
               js_result     INTEGER
           );
            CREATE INDEX IF NOT EXISTS job_status_ix1 ON job_status(js_id);
            CREATE INDEX IF NOT EXISTS job_status_ix2 ON job_status(js_task);
            CREATE INDEX IF NOT EXISTS job_status_ix3 ON job_status(js_result);
     END

     BEGIN
        ON EXCEPTION IN ( -8301 )
        END EXCEPTION WITH RESUME

        CREATE SEQUENCE background_task START 1 NOMAXVALUE ;

     END
   IF comptype == 'fragment' THEN

    INSERT INTO ph_task
        ( tk_name,
        tk_description,
        tk_type,
        tk_group,
        tk_execute,
        tk_start_time,
        tk_stop_time,
        tk_frequency,
        tk_Monday,
        tk_Tuesday,
        tk_Wednesday,
        tk_Thursday,
        tk_Friday,
        tk_Saturday,
        tk_Sunday,
        tk_attributes
        )
        VALUES
        (
        'Background Task ('||  background_task.NEXTVAL ||')',
        TRIM(comments),
        'TASK',
        cur_group,
        "insert into job_status (js_task, js_seq , js_comment,js_command) VALUES($DATA_TASK_ID,$DATA_SEQ_ID, '"||TRIM(comments)||"','"||"Estimate for "||comptype||" "||TRIM(REPLACE(REPLACE(cmd,"'"),"""") )||"' ); update job_status set js_result=get_compression_estimate("""||cmd||""" ,"""||comptype||""" , """" , """" )  WHERE js_task = $DATA_TASK_ID AND js_seq = $DATA_SEQ_ID; insert into mon_compression_estimates SELECT 0,tab.partnum , TRIM(dbsname) , TRIM(owner) , TRIM(tabname) , substr(trim(cmd_ret_msg),102) , current FROM sysmaster:systabnames tab , sysmaster:sysptnhdr hdr , command_history where hdr.partnum = tab.partnum and tab.partnum ="""||cmd||""" and cmd_number = ( select case when ( js_result < 0 ) then js_result *-1 else js_result end from job_status where  js_task = $DATA_TASK_ID AND js_seq = $DATA_SEQ_ID ) ;update job_status set (js_done)  = ( CURRENT ) WHERE js_task = $DATA_TASK_ID AND js_seq = $DATA_SEQ_ID ;" ,
                start_time,
        end_time,
        frequency,
        monday,
        tuesday,
        wednesday,
        thursday,
        friday,
        saturday,
        sunday,
        8);
        ELSE

    INSERT INTO ph_task
        ( tk_name,
        tk_description,
        tk_type,
        tk_group,
        tk_execute,
        tk_start_time,
        tk_stop_time,
        tk_frequency,
        tk_Monday,
        tk_Tuesday,
        tk_Wednesday,
        tk_Thursday,
        tk_Friday,
        tk_Saturday,
        tk_Sunday,
        tk_attributes
        )
        VALUES
        (
        'Background Task ('||  background_task.NEXTVAL ||')',
        TRIM(comments),
        'TASK',
        cur_group,

        "insert into job_status (js_task, js_seq , js_comment,js_command) VALUES($DATA_TASK_ID,$DATA_SEQ_ID, '"||TRIM(comments)||"','"||"Estimate for "||comptype||" "||TRIM(REPLACE(REPLACE(cmd,"'"),"""") )||"' ); update job_status set js_result=get_compression_estimate("""||cmd||""" ,"""||comptype||""", """||dbname||""" , """||owner||""")  WHERE js_task = $DATA_TASK_ID AND js_seq = $DATA_SEQ_ID; insert into mon_compression_estimates SELECT 0,tab.partnum , TRIM(dbsname) , TRIM(owner) , TRIM(tabname) , substr(trim(cmd_ret_msg),102) , current FROM sysmaster:systabnames tab , sysmaster:sysptnhdr hdr , command_history where hdr.partnum = tab.partnum and tab.tabname ="""||cmd||"""  and dbsname = """||dbname||""" and owner = """||owner||""" and cmd_number = ( select case when ( js_result < 0 ) then js_result *-1 else js_result end from job_status where  js_task = $DATA_TASK_ID AND js_seq = $DATA_SEQ_ID ) ;update job_status set (js_done)  = ( CURRENT ) WHERE js_task = $DATA_TASK_ID AND js_seq = $DATA_SEQ_ID ;" ,

        start_time,
        end_time,
        frequency,
        monday,
        tuesday,
        wednesday,
        thursday,
        friday,
        saturday,
                sunday,
        8);


     END IF

   LET ret_task_id = DBINFO('sqlca.sqlerrd1');

   /* Cleanup the job_status table */

   SELECT dbinfo('UTC_TO_DATETIME',sh_boottime)
          INTO boot_time
           FROM sysmaster:sysshmvals;

   FOREACH  SELECT js_id, js_task, js_seq, js_result
        INTO id, task_id,  seq_id, cmd_num
        FROM job_status J, OUTER ph_run, command_history
        WHERE  ( CURRENT - js_done > del_time OR
                (js_start < boot_time AND js_done IS NULL ) )
        AND    js_task = run_task_id
        AND    js_seq  = run_task_seq
        AND    js_result = ABS(cmd_number)

       DELETE FROM ph_run WHERE run_task_id = task_id
                          AND run_task_seq = seq_id;
       DELETE FROM command_history WHERE cmd_number = cmd_num;
       DELETE FROM job_status WHERE js_id = id;

       -- Cleanup the task table only if this is not a repeating task
       DELETE FROM ph_task WHERE tk_id = task_id AND tk_next_execution IS NULL;

   END FOREACH

   RETURN  ret_task_id;
END FUNCTION;

DROP TABLE IF EXISTS mon_compression_estimates;
CREATE TABLE mon_compression_estimates (
       id             INTEGER, 
       est_partnum    INTEGER,
       est_dbname     VARCHAR(128),
       est_owner      VARCHAR(32),
       est_tabname    VARCHAR(128),
       est_estimate   LVARCHAR(32000),
       est_date       DATETIME YEAR TO SECOND DEFAULT CURRENT YEAR TO SECOND);

CREATE INDEX IF NOT EXISTS mon_estimate_compression_idx1 
       ON mon_compression_estimates ( est_partnum );


DELETE FROM ph_task WHERE tk_name = 'mon_page_usage';
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_page_usage',
'SENSOR',
'DISK',
'Get page usage estimate',
'mon_page_usage',
'CREATE TABLE mon_page_usage(ID integer, dbsnum smallint, type char(2), partnum integer, lockid integer, nextns smallint, nrows bigint, nptotal integer, npused integer, free integer, partly_used integer, mostly_used integer, very_full integer,run_time datetime year to second );create index mon_page_usage_ix1 on mon_page_usage(partnum,id);create index mon_page_usage_ix2 on mon_page_usage(lockid,id);',
'INSERT INTO mon_page_usage select $DATA_SEQ_ID, trunc(P.partnum / 1048577) as dbsnum, CASE WHEN bitand(P.flags,"0x80000000") > 0 THEN "TS" WHEN bitand(P.flags,"0x22760") > 0 THEN "T" WHEN P.nkeys = 1 AND P.npused > 1 AND P.partnum <> P.lockid AND bitand(P.flags,4) = 0 THEN "I" ELSE "T" END::CHAR(2) as type, P.partnum, P.lockid, P.nextns, P.nrows, P.nptotal, P.npused, P.nptotal - ( BM.partly_used+BM.mostly_used+BM.very_full) AS free ,BM.partly_used ,BM.mostly_used , BM.very_full, CURRENT FROM sysmaster:sysptnhdr P, outer (select b.pb_partnum as partnum, (b.pb_partnum/1048577)::integer as dbsnum ,sum(decode(bitand(b.pb_bitmap, 12),4 ,1,0)) as partly_used ,sum(decode(bitand(b.pb_bitmap, 12),8 ,1,0)) as mostly_used ,sum(decode(bitand(b.pb_bitmap, 12),12,1,0)) as very_full from sysmaster:sysptnbit b where b.pb_bitmap > 0 group by b.pb_partnum ) as BM WHERE P.partnum = BM.partnum and bitand(p.flags,"0xE0") = 0 and sysmaster:partpagenum(P.partnum)>1;UPDATE STATISTICS HIGH FOR TABLE mon_page_usage(ID,dbsnum,partnum,lockid)',
NULL,
DATETIME(03:00:00) HOUR TO SECOND,
INTERVAL (1) DAY TO DAY,
INTERVAL (7) DAY TO DAY
);


{****************************************************}
{***     Backup Stored Procedure                  ***}
{****************************************************}

DROP FUNCTION IF EXISTS oatBackup(INT, INT, INT);
CREATE FUNCTION oatBackup(backupLevel INT, task_id INT, task_seq INT)
RETURNING INT

  DEFINE tapedevice   CHAR(257);
  DEFINE deviceType   CHAR(10);
  DEFINE tapeblock    INTEGER;
  DEFINE args1        CHAR(40);
  DEFINE args2        CHAR(1000);
  DEFINE args3        CHAR(5);
  DEFINE errsql       INTEGER;
  DEFINE errisam      INTEGER;
  DEFINE errtext      VARCHAR(255);
  DEFINE rc           INTEGER;

 ON EXCEPTION SET errsql, errisam, errtext
   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, 'ERROR', 'RED',
       'NEW', 'SERVER','BACKUP LEVEL '||backupLevel,
       'Level '||backupLevel||' backup of server FAILED with error('
           ||errsql||','||errisam||' - '||errtext||').',
      NULL
      );
 END EXCEPTION;

-- SET DEBUG FILE TO '/tmp/debug.oatBackup.out';
-- TRACE ON;

   LET tapedevice = NULL;
   {*** Get the ontape tape device ***}
   SELECT value::CHAR(257) INTO tapedevice FROM ph_threshold
      WHERE name = 'ONTAPE_TAPEDEV';
   IF tapedevice IS NULL 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, 'ERROR', 'RED',
             'NEW', 'SERVER','oatBackup stored procedure',
             'Error executing procedure oatBackup. sysadmin:ph_threshold does not have an entry for ONTAPE_TAPEDEV which contains the TAPEDEV value for backup',
            NULL
            );
       RETURN -1;
   END IF

   {*** Get the device type: file, directory or tape ***}
   SELECT value::CHAR(10) INTO deviceType FROM ph_threshold
      WHERE name = 'ONTAPE_DEVICE_TYPE';

   LET tapeblock = 512;
   {*** Get the ontape block size ***}
   SELECT value::integer INTO tapeblock FROM ph_threshold
      WHERE name = 'ONTAPE_TAPEBLK';

   {*** Build the command ***}
   LET args1 = 'ontape archive ' || TRIM(deviceType) ||' level ' || backupLevel;

   IF deviceType = 'file'  THEN
      LET args2 = TRIM(tapedevice) ||'_L'||backupLevel;
   ELSE
      LET args2 = tapedevice;
   END IF

   LET args3 = tapeblock;

   LET rc = -1;
   BEGIN
       ON EXCEPTION SET errsql, errisam, errtext
          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, 'ERROR', 'RED',
               'NEW', 'SERVER','BACKUP LEVEL '||backupLevel,
               'Level '|| backupLevel 
                   ||' backup of server FAILED starting ontape with error('
                   ||errsql||','||errisam||' - '||errtext||').',
              NULL
              );
       END EXCEPTION;

       {*** Run the command ***}
       SELECT admin(TRIM(args1),TRIM(args2),TRIM(args3)) 
              INTO rc FROM systables WHERE tabid=1;

       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',
            'NEW', 'SERVER','BACKUP LEVEL ' || backupLevel,
            'Level ' || backupLevel || ' backup of server completed successfully.',
            NULL
          );
       ELSE
          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, 'ERROR', 'RED',
               'NEW', 'SERVER','BACKUP LEVEL '||backupLevel,
               'Level '|| backupLevel ||' backup of server FAILED starting ontape with error(see sysadmin:command_history.cmd_number=' ||ABS(rc),
              NULL
              );
       END IF

   END

RETURN rc;

END FUNCTION;

{****************************************************}
{***     Onbar Backup Stored Procedure            ***}
{****************************************************}

DROP FUNCTION IF EXISTS oatOnbarBackup(INT, INT, INT);
CREATE FUNCTION oatOnbarBackup(backupLevel INT, task_id INT, task_seq INT)
RETURNING INT

  DEFINE backupGen    CHAR(2);
  DEFINE onbarArgs1   CHAR(40);
  DEFINE smsyncArgs1  CHAR(10);
  DEFINE smsyncArgs2  CHAR(6);
  DEFINE errsql       INTEGER;
  DEFINE errisam      INTEGER;
  DEFINE errtext      VARCHAR(255);
  DEFINE rc           INTEGER;

 ON EXCEPTION SET errsql, errisam, errtext
   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, 'ERROR', 'RED',
       'NEW', 'SERVER','BACKUP LEVEL '||backupLevel,
       'Level '||backupLevel||' backup of server FAILED with error('
           ||errsql||','||errisam||' - '||errtext||').',
      NULL
      );
 END EXCEPTION;

--  SET DEBUG FILE TO '/tmp/debug.oatOnbarBackup.out';
-- TRACE ON;

   {*** Get the number of level 0 backup generations to retain ***}
   SELECT value::CHAR(2) INTO backupGen FROM ph_threshold
      WHERE name = 'BACKUP_GENR_RETAIN';

   {*** Build the command ***}
   LET onbarArgs1 = 'onbar backup whole system level ' || backupLevel;
   LET smsyncArgs1 = 'onsmsync';
   LET smsyncArgs2 = '-g ' || backupGen;

   LET rc = -1;
   BEGIN
       ON EXCEPTION SET errsql, errisam, errtext
          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, 'ERROR', 'RED',
               'NEW', 'SERVER','BACKUP LEVEL '||backupLevel,
               'Level '|| backupLevel ||' backup of server FAILED starting ON-Bar with error('
                   ||errsql||','||errisam||' - '||errtext||').',
              NULL
              );
       END EXCEPTION;

       {*** Run the command ***}
       SELECT admin(TRIM(onbarArgs1)) INTO rc FROM systables WHERE tabid=1;
              
       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',
            'NEW', 'SERVER','BACKUP LEVEL ' || backupLevel,
            'Level ' || backupLevel || ' backup of server completed successfully.',
            NULL
          );

         IF (backupGen <> '-1') THEN
           {*** Run the onsmsync command to retain the backup generations ***}
           SELECT admin(TRIM(smsyncArgs1),TRIM(smsyncArgs2)) INTO rc FROM systables WHERE tabid=1;

           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',
                'NEW', 'SERVER','RETAIN BACKUP LEVEL 0',
                'Setting retention of ' || backupGen || ' generations completed successfully.',
                NULL
              );
           ELSE
              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, 'ERROR', 'RED',
                   'NEW', 'SERVER','RETAIN BACKUP LEVEL 0',
                   'Setting retention of '|| backupGen ||' generations FAILED with error(see sysadmin:command_history.cmd_number=' ||ABS(rc),
                  NULL
                  );
           END IF
         END IF
       ELSE
          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, 'ERROR', 'RED',
               'NEW', 'SERVER','BACKUP LEVEL '||backupLevel,
               'Level '|| backupLevel ||' backup of server FAILED starting ON-Bar with error(see sysadmin:command_history.cmd_number=' ||ABS(rc),
              NULL
              );
       END IF

   END

RETURN rc;

END FUNCTION;

{****************************************************}
{***        Permission of db_admin                ***}
{***                                              ***}
{***        Do not put ph_allow into this list    ***}
{****************************************************}

GRANT SELECT ON command_history TO "db_monitor";
GRANT SELECT ON ph_alert TO "db_monitor";
GRANT SELECT ON ph_bg_jobs TO "db_monitor";
GRANT SELECT ON ph_bg_jobs_results TO "db_monitor";
GRANT SELECT ON ph_config TO "db_monitor";
GRANT SELECT ON ph_group TO "db_monitor";
GRANT SELECT ON ph_run TO "db_monitor";
GRANT SELECT ON ph_task TO "db_monitor";
GRANT SELECT ON ph_threshold TO "db_monitor";
GRANT SELECT ON ph_version TO "db_monitor";
GRANT SELECT ON storagepool TO "db_monitor";
{* views *}
GRANT SELECT ON locales_ext TO "db_admin";
GRANT SELECT ON ph_alerts TO "db_admin";
GRANT SELECT ON ph_bg_jobs_seq TO "db_admin";
GRANT SELECT ON ph_config TO "db_admin";
GRANT SELECT ON ph_allow_list TO "db_admin";

GRANT "db_monitor" TO "db_admin";
GRANT UPDATE, DELETE, INSERT ON ph_alert TO "db_admin";
GRANT UPDATE, DELETE, INSERT ON ph_bg_jobs TO "db_admin";
GRANT UPDATE, DELETE, INSERT ON ph_bg_jobs_results TO "db_admin";
GRANT UPDATE, DELETE, INSERT ON ph_config TO "db_admin";
GRANT UPDATE, DELETE, INSERT ON ph_group TO "db_admin";
GRANT UPDATE, DELETE, INSERT ON ph_task TO "db_admin";
GRANT UPDATE, DELETE, INSERT ON ph_threshold TO "db_admin";
GRANT UPDATE, DELETE, INSERT ON storagepool TO "db_admin";

CLOSE DATABASE;