analytics/informix/etc/sysadmin/upgrade_ph_task.sql

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

{* update the task attributes for task post_alarm_message *}
UPDATE ph_task SET tk_attributes = BITOR(tk_attributes,"0x4000")
WHERE tk_name = "post_alarm_message";


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'
);

{* Need to change the function signature as parameter and column *}
{* are getting confused                                         *}
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);

DROP FUNCTION IF EXISTS informix.AlertCleanup(INTEGER, INTEGER);
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;

{* Alter some integer columns of table mon_users to bigint to reflect     *}
{* the coresponding change in sysmaster tables                            *}

DROP PROCEDURE IF EXISTS alter_table_mon_users( );
CREATE PROCEDURE alter_table_mon_users( )

    DEFINE tab_exists INTEGER;
    DEFINE is_cols_altered INTEGER;

    LET tab_exists = 0 ;
    LET is_cols_altered = 0;

    SELECT count(*) INTO tab_exists FROM sysadmin:systables
        WHERE tabname = "mon_users";

    SELECT count(*) INTO is_cols_altered FROM sysadmin:syscolumns c, 
    	sysadmin:systables t WHERE tabname="mon_users" AND
	t.tabid = c.tabid AND colname = "upf_rqlock" AND coltype = 52;

    IF tab_exists == 1 AND is_cols_altered == 0 THEN
	ALTER TABLE mon_users MODIFY (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_bufwrites bigint, 
	    upf_logspuse bigint, upf_logspmax bigint, upf_seqscans bigint, 
	    upf_totsorts bigint, upf_dsksorts bigint, upf_srtspmax bigint);
        UPDATE mon_users set upf_rqlock = upf_rqlock WHERE 1=1;
    END IF;

END PROCEDURE;

EXECUTE PROCEDURE alter_table_mon_users();
DROP PROCEDURE alter_table_mon_users();

DROP PROCEDURE IF EXISTS alter_table_mon_table_profile();
CREATE PROCEDURE alter_table_mon_table_profile()
  
    DEFINE tab_exists INTEGER;
    DEFINE is_col_altered INTEGER;

    LET tab_exists = 0 ;
    LET is_col_altered = 0;

    SELECT count(*) INTO tab_exists FROM sysadmin:systables
        WHERE tabname = "mon_table_profile";

    SELECT count(*) INTO is_col_altered FROM sysadmin:syscolumns c, 
    	sysadmin:systables t WHERE tabname="mon_table_profile" AND
	t.tabid = c.tabid AND colname = "nrows" AND coltype = 52;

    IF tab_exists == 1 AND is_col_altered == 0 THEN
	ALTER TABLE mon_table_profile MODIFY (nrows bigint);
        UPDATE mon_table_profile set nrows = nrows WHERE 1=1;
    END IF;

END PROCEDURE;

EXECUTE PROCEDURE alter_table_mon_table_profile();
DROP PROCEDURE alter_table_mon_table_profile();

DROP PROCEDURE IF EXISTS alter_table_mon_page_usage();
CREATE PROCEDURE alter_table_mon_page_usage()
  
    DEFINE tab_exists INTEGER;
    DEFINE is_col_altered INTEGER;

    LET tab_exists = 0;
    LET is_col_altered = 0;

    SELECT count(*) INTO tab_exists FROM sysadmin:systables
        WHERE tabname = "mon_page_usage";

    SELECT count(*) INTO is_col_altered FROM sysadmin:syscolumns c, 
    	sysadmin:systables t WHERE tabname="mon_page_usage" AND
	t.tabid = c.tabid AND colname = "nrows" AND coltype = 52;

    IF tab_exists == 1 AND is_col_altered == 0 THEN
	ALTER TABLE mon_page_usage MODIFY (nrows bigint);
        UPDATE mon_page_usage set nrows = nrows WHERE 1=1;
    END IF;

END PROCEDURE;

EXECUTE PROCEDURE alter_table_mon_page_usage();
DROP PROCEDURE alter_table_mon_page_usage();

DROP PROCEDURE IF EXISTS add_new_indexes();
CREATE PROCEDURE add_new_indexes()
    DEFINE tab_exists INTEGER;

    SELECT NVL(count(*),0) INTO tab_exists 
        FROM sysadmin:systables WHERE tabname = "mon_prof";
    IF tab_exists > 0 THEN
        CREATE INDEX IF NOT EXISTS informix.mon_prof_idx2 ON mon_prof(number);
        CREATE INDEX IF NOT EXISTS informix.mon_config_idx2 ON mon_config(id,config_id);
    END IF;

    SELECT NVL(count(*),0) INTO tab_exists 
        FROM sysadmin:systables WHERE tabname = "ph_alert";
    IF tab_exists > 0 THEN
        CREATE INDEX IF NOT EXISTS informix.ix_ph_alert_03 ON ph_alert(alert_time);
    END IF;

    SELECT NVL(count(*),0) INTO tab_exists 
        FROM sysadmin:systables WHERE tabname = "mon_page_usage";
    IF tab_exists > 0 THEN
        ALTER TABLE mon_page_usage MODIFY ( type char(2));
        UPDATE mon_page_usage set type = type WHERE 1=1;

        UPDATE ph_task SET 
                tk_execute = '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)'
WHERE tk_name = 'mon_page_usage';
    END IF;

END PROCEDURE;

EXECUTE PROCEDURE add_new_indexes();
DROP PROCEDURE add_new_indexes();


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'
);

UPDATE ph_task SET tk_attributes = BITOR(tk_attributes, 4)
               WHERE tk_name IN ( "mongo_pam_auth", "mongo_pam_init" );