analytics/informix/etc/sysadmin/sch_iwa.sql

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

DATABASE sysadmin;

{****************************************************}
{***                                              ***}
{***        create IWA tables                     ***}
{***                                              ***}
{****************************************************}

-- table that keeps meta data on data marts for this IDS instance
create table "informix".iwa_datamarts (

  -- identifiers:
  m_mart_id      serial,                    -- mart identifier key
  m_name         varchar(128),              -- mart name
  m_accel_name   varchar(128),              -- accelerator name
  m_dbname       varchar(128),              -- database name
  m_owner        varchar(32) default user,  -- creator of the mart
  m_created      datetime year to second default current year to second,
                                            -- date/time mart was created

  -- refresh operating values:
  m_lockmode              integer default -1, -- loadMart locking mode
  m_flags                 integer default 0,  -- for future use

  -- refresh statistic values:
  m_nrefreshes            integer default 0,  -- number of refreshes done
  m_avg_refresh_runtime   integer default 0,
  m_last_refresh_runtime  integer default 0,

  -- trickle feed statistic values:
  m_trickle_secs          integer default 0,
  m_trickle_rows          bigint  default 0,
  unique (m_name, m_accel_name)       -- mart name is unique per accelerator
) lock mode row;
alter table "informix".iwa_datamarts add constraint
  primary key (m_mart_id) constraint iwa_datamarts_pk1;

-- table that keeps track of tables in the different data marts
create table "informix".iwa_marttables (
  -- identifiers:
  mt_tab_id     serial,                    -- table identifier key
  mt_mart_id    integer,                   -- -> iwa_datamarts.m_mart_id
  mt_tabid      integer,                   -- from systables.tabid
  mt_dwaversion integer default 0,         -- from systables.dwa_version
  mt_servername varchar(128) default NULL, -- (remote) server
  mt_dbname     varchar(128) default NULL, -- (remote) database
  mt_tabname    varchar(128) default NULL, -- from systables.tabname
  mt_owner      varchar(32)  default NULL, -- from systables.owner
  -- refresh operating values:
  mt_partnum    integer,                   -- boolean: fragmented ? 0 : >0
  mt_isfact     integer default 0          -- boolean
) lock mode row;
alter table "informix".iwa_marttables add constraint
  primary key (mt_tab_id) constraint iwa_marttables_pk1;
alter table "informix".iwa_marttables add constraint
  (foreign key (mt_mart_id) references "informix".iwa_datamarts (m_mart_id)
  on delete cascade constraint iwa_marttables_fk1);

-- table that keeps track of table fragments and their change values:
create table "informix".iwa_martpartitions (

  -- partition identifiers:
  mp_part_id        serial unique,      -- partition identifier
  mp_mart_id        integer,            -- -> iwa_datamarts.m_mart_id
  mp_tab_id         integer,            -- -> iwa_marttables.mt_tab_id
  mp_tabid          integer,            -- from systables.tabid
  mp_partnum        integer,            -- from sysfragments.partn
  mp_dwaversion     integer default 0,  -- from sysfragments.dwa_version

  -- values at last refresh time (from sysmaster:sysptnhdr):
  mp_last_created   integer default -1,
  mp_last_nrows     bigint default -1,
  mp_last_ninserts  bigint default -1,
  mp_last_nupdates  bigint default -1,
  mp_last_ndeletes  bigint default -1,

  -- values at current refresh time (from sysmaster:sysptnhdr):
  mp_cur_created    integer default -1,
  mp_cur_nrows      bigint default -1,
  mp_cur_ninserts   bigint default -1,
  mp_cur_nupdates   bigint default -1,
  mp_cur_ndeletes   bigint default -1
) lock mode row;
alter table "informix".iwa_martpartitions add constraint
  (foreign key (mp_tab_id) references "informix".iwa_marttables (mt_tab_id)
  on delete cascade constraint iwa_martpartitions_fk1);
alter table "informix".iwa_martpartitions add constraint
  (foreign key (mp_mart_id) references "informix".iwa_datamarts (m_mart_id)
  on delete cascade constraint iwa_martpartitions_fk2);

-- table that keeps track of table columns in a data mart:
create table "informix".iwa_martcolumns (

  -- column identifiers:
  mc_col_id     serial,                    -- column identifier
  mc_mart_id    integer,                   -- -> iwa_datamarts.m_mart_id
  mc_tab_id     integer,                   -- -> iwa_marttables.mt_tab_id
  mc_colno      integer,                   -- from syscolumns.colno
  mc_colname    varchar(128) default NULL  -- from syscolumns.colname
) lock mode row;
alter table "informix".iwa_martcolumns add constraint 
  primary key (mc_col_id) constraint iwa_martcolumns_pk1; 
alter table "informix".iwa_martcolumns add constraint 
  (foreign key (mc_tab_id) references "informix".iwa_marttables (mt_tab_id) 
  on delete cascade constraint iwa_martcolumns_fk1);
alter table "informix".iwa_martcolumns add constraint
  (foreign key (mc_mart_id) references "informix".iwa_datamarts (m_mart_id)
  on delete cascade constraint iwa_martcolumns_fk2);

create table 'informix'.iwa_dmd_marts (
  dmdm_id serial,
  dmdm_name varchar(128),
  dmdm_dbname varchar(128),
  primary key (dmdm_id) constraint iwa_dmd_marts_pk,
  unique (dmdm_name, dmdm_dbname) constraint iwa_dmd_marts_un1
) lock mode row;

create table 'informix'.iwa_dmd_tables (
  dmdt_id serial,
  dmdt_mid integer,
  dmdt_owner varchar(32),
  dmdt_tabname varchar(128),
  dmdt_isfact integer,
  primary key (dmdt_id) constraint iwa_dmd_tables_pk,
  unique (dmdt_owner, dmdt_tabname, dmdt_mid) constraint iwa_dmd_tables_un1,
  foreign key (dmdt_mid) references 'informix'.iwa_dmd_marts (dmdm_id)
    on delete cascade constraint iwa_dmd_tables_fk1
) lock mode row;

create table 'informix'.iwa_dmd_columns (
  dmdc_id serial,
  dmdc_tid integer,
  dmdc_colname varchar(128),
  primary key (dmdc_id) constraint iwa_dmd_columns_pk,
  foreign key (dmdc_tid) references 'informix'.iwa_dmd_tables (dmdt_id)
    on delete cascade constraint iwa_dmd_columns_fk1
) lock mode row;

create table 'informix'.iwa_dmd_refs (
  dmdr_id serial,
  dmdr_mid integer,
  dmdr_sig varchar(255),
  dmdr_ctid integer,
  dmdr_ptid integer,
  dmdr_uniq char(1),
  dmdr_type char(1),
  primary key (dmdr_id) constraint iwa_dmd_refs_pk1,
  foreign key (dmdr_mid) references 'informix'.iwa_dmd_marts (dmdm_id)
    on delete cascade constraint iwa_dmd_refs_fk1,
  foreign key (dmdr_ctid) references 'informix'.iwa_dmd_tables (dmdt_id)
    constraint iwa_dmd_refs_fk2,
  foreign key (dmdr_ptid) references 'informix'.iwa_dmd_tables (dmdt_id)
    constraint iwa_dmd_refs_fk3,
  check (dmdr_uniq in ('y', 'n')) constraint iwa_dmd_refs_chk1,
  check (dmdr_type in ('i', 'l')) constraint iwa_dmd_refs_chk2
) lock mode row;

create table 'informix'.iwa_dmd_refcols (
  dmdrc_id serial,
  dmdrc_rid integer,
  dmdrc_ccid integer,
  dmdrc_pcid integer,
  primary key (dmdrc_id) constraint iwa_dmd_refcols_pk1,
  foreign key (dmdrc_rid) references 'informix'.iwa_dmd_refs (dmdr_id)
    on delete cascade constraint iwa_dmd_refcols_fk1,
  foreign key (dmdrc_ccid) references 'informix'.iwa_dmd_columns (dmdc_id)
    constraint iwa_dmd_refcols_fk2,
  foreign key (dmdrc_pcid) references 'informix'.iwa_dmd_columns (dmdc_id)
    constraint iwa_dmd_refcols_fk3
) lock mode row;

-- time series virtual table partitioning calendar
create table 'informix'.iwa_tsvt_partcal (
  tab_id  integer,                 -- -> iwa_marttables.mt_tab_id
  calname varchar(255) not null,   -- calendar name
  parted  integer      not null    -- table is partitioned
) lock mode row;
alter table 'informix'.iwa_tsvt_partcal add constraint
  (foreign key (tab_id) references 'informix'.iwa_marttables(mt_tab_id)
  on delete cascade constraint iwa_tsvt_partcal_fk1);

-- time series virtual table windows
create table 'informix'.iwa_tsvt_windows (
  tab_id integer,                  -- -> iwa_marttables.mt_tab_id
  begin  integer not null,         -- begin interval
  end    integer not null,         -- end interval
  check (begin < end)
) lock mode row;
alter table 'informix'.iwa_tsvt_windows add constraint
  (foreign key (tab_id) references 'informix'.iwa_marttables(mt_tab_id)
  on delete cascade constraint iwa_tsvt_windows_fk1);


GRANT SELECT ON 'informix'.iwa_datamarts      TO "db_monitor" as informix;
GRANT SELECT ON 'informix'.iwa_marttables     TO "db_monitor" as informix;
GRANT SELECT ON 'informix'.iwa_martpartitions TO "db_monitor" as informix;
GRANT SELECT ON 'informix'.iwa_martcolumns    TO "db_monitor" as informix;
GRANT SELECT ON 'informix'.iwa_dmd_marts   TO "db_monitor" as informix;
GRANT SELECT ON 'informix'.iwa_dmd_tables  TO "db_monitor" as informix;
GRANT SELECT ON 'informix'.iwa_dmd_columns TO "db_monitor" as informix;
GRANT SELECT ON 'informix'.iwa_dmd_refs    TO "db_monitor" as informix;
GRANT SELECT ON 'informix'.iwa_dmd_refcols TO "db_monitor" as informix;
GRANT SELECT ON 'informix'.iwa_tsvt_partcal   TO "db_monitor" as informix;
GRANT SELECT ON 'informix'.iwa_tsvt_windows   TO "db_monitor" as informix;

GRANT UPDATE, DELETE, INSERT ON 'informix'.iwa_datamarts      TO "db_admin" as informix;
GRANT UPDATE, DELETE, INSERT ON 'informix'.iwa_marttables     TO "db_admin" as informix;
GRANT UPDATE, DELETE, INSERT ON 'informix'.iwa_martpartitions TO "db_admin" as informix;
GRANT UPDATE, DELETE, INSERT ON 'informix'.iwa_martcolumns    TO "db_admin" as informix;
GRANT UPDATE, DELETE, INSERT ON 'informix'.iwa_dmd_marts      TO "db_admin" as informix;
GRANT UPDATE, DELETE, INSERT ON 'informix'.iwa_dmd_tables     TO "db_admin" as informix;
GRANT UPDATE, DELETE, INSERT ON 'informix'.iwa_dmd_columns    TO "db_admin" as informix;
GRANT UPDATE, DELETE, INSERT ON 'informix'.iwa_dmd_refs       TO "db_admin" as informix;
GRANT UPDATE, DELETE, INSERT ON 'informix'.iwa_dmd_refcols    TO "db_admin" as informix;
GRANT UPDATE, DELETE, INSERT ON 'informix'.iwa_tsvt_partcal   TO "db_admin" as informix;
GRANT UPDATE, DELETE, INSERT ON 'informix'.iwa_tsvt_windows   TO "db_admin" as informix;

INSERT INTO ph_version(object,type,value) VALUES ('IWA','version',2);

CLOSE DATABASE;