analytics/informix/etc/bootcdr.sql

--
--
--  Licensed Materials - Property of IBM and/or HCL
-- 
--  IBM Informix Dynamic Server
--  Copyright IBM Corporation 1996, 2013
--  (c) Copyright HCL Technologies Ltd. 2017.  All Rights Reserved.
-- 
--   Title:  syscdr.sql
--
--   N.B. - all commands should be in lowercase only
--

set lock mode to wait;

database syscdr;

grant connect to public;
grant dba to root;

{ Define any UDRs now }
create function informix.timet_to_datetime(informix.integer)
    returns datetime year to second
    external name '(cdrcmd_timet2datetime)'
    language C not variant;

{ ************************************************************************ }
{   Implementation of ifmx_er_extfile                                      }
{ ************************************************************************ }
create opaque type informix.ifmx_er_extfile (
    internallength = variable,
    maxlen=8000,
    alignment = 4);

{ Constructors }

create function informix.ifmx_er_extfile(informix.integer, informix.lvarchar)
    returns ifmx_er_extfile with (handlesnulls )
    external name '(ifmx_er_extfile_in1)'
    language C Variant;

grant execute on function informix.ifmx_er_extfile(informix.integer, informix.lvarchar) to public as informix;

create function informix.ifmx_er_extfile(informix.integer, informix.lvarchar, informix.lvarchar)
    returns ifmx_er_extfile with (handlesnulls )
    external name '(ifmx_er_extfile_in2)'
    language C Variant;

grant execute on function informix.ifmx_er_extfile(informix.integer, informix.lvarchar, informix.lvarchar) 
    to public as informix;

{ lvarchar casts to/from ifmx_er_extfile UDT }
create function informix.ifmx_er_extfile_out(informix.ifmx_er_extfile)
    returns lvarchar with ( handlesnulls )
    external name '(ifmx_er_extfile_out)'
    language C Variant;

grant execute on function informix.ifmx_er_extfile_out(informix.ifmx_er_extfile)
    to public as informix;

create implicit cast (informix.ifmx_er_extfile as informix.lvarchar with informix.ifmx_er_extfile_out );

{ ER stream write UDRs for ifmx_er_extfile UDT }

create function informix.streamwrite(informix.stream, informix.ifmx_er_extfile)
    returns integer
    external name '(ifmx_er_extfile_StreamWrite)'
    language C
    not variant;

create function informix.streamread(informix.stream, OUT informix.ifmx_er_extfile)
    returns integer
    external name '(ifmx_er_extfile_StreamRead)'
    language C
    not variant;

{ Other UDRS for ifmx_er_extfile UDT }

create function informix.compare(informix.ifmx_er_extfile, informix.ifmx_er_extfile)
    returns integer
    external name '(ifmx_er_extfile_compare)'
    language C
    not variant;

grant execute on function informix.compare(informix.ifmx_er_extfile, informix.ifmx_er_extfile)
    to public as informix;

create function informix.is_applied(informix.ifmx_er_extfile)
    returns boolean
    external name '(ifmx_er_extfile_is_applied)'
    language C
    not variant;

grant execute on function informix.is_applied(informix.ifmx_er_extfile)
    to public as informix;

create function informix.get_flags(informix.ifmx_er_extfile)
    returns integer
    external name '(ifmx_er_extfile_get_flags)'
    language C
    not variant;

grant execute on function informix.get_flags(informix.ifmx_er_extfile)
    to public as informix;

create function informix.get_gridid(informix.ifmx_er_extfile)
    returns integer
    external name '(ifmx_er_extfile_get_gridid)'
    language C
    not variant;

create function informix.get_error(informix.ifmx_er_extfile)
    returns integer
    external name '(ifmx_er_extfile_get_error)'
    language C
    not variant;

grant execute on function informix.get_error(informix.ifmx_er_extfile)
    to public as informix;

{ Enterprise Replication servers (like SQLHOSTS) }
create table informix.hostdef_tab (
    servid    integer not null check (servid != 0),
					{ id equivalent to SQLHOSTS }
    name      varchar(255)	 not null, { name }
    groupname lvarchar not null { group name }
) lock mode row;
create unique index informix.host_idix on informix.hostdef_tab (servid) in table;
create unique index informix.host_namix on informix.hostdef_tab (name) in table;
alter table informix.hostdef_tab add constraint primary key  (servid);
revoke all on informix.hostdef_tab from public as informix;
grant select on informix.hostdef_tab to public as informix;


{ server protocol information }
create table informix.protodef_tab (
    servid   integer not null,	     { server id }
    protocol varchar(36) not null,     { protocol to use }
    address  varchar(255) not null  { server address (url) }
) lock mode row;
create unique index informix.proto_servproto on informix.protodef_tab (servid,protocol) in table;
create unique index informix.proto_protoaddr on informix.protodef_tab (protocol,address) in table;
create index informix.proto_idix on informix.protodef_tab(servid) in table;
alter table informix.protodef_tab add constraint primary key(servid, protocol);
alter table informix.protodef_tab add constraint
	foreign key(servid) references informix.hostdef_tab(servid) on delete cascade;
revoke all on informix.protodef_tab from public as informix;
grant select on informix.protodef_tab to public as informix;

{ Enterprise replication server definition }
create table informix.servdef_tab (
    servid      integer,          { SQL host id }
    servstate   smallint,         { state: active, suspend, ... }
    idletimeout integer,          { connection idle timeout }
    atsdir      char(512),     	  { abort transaction directory }
    risdir      char(512),        { row spool directory }
    flags       integer,          { API specfic flags }
    create_time integer,          { time of creation }
    modify_time integer           { time of last change }
    ) lock mode row;
create unique index informix.sdef_idix  on informix.servdef_tab(servid) in table;
alter table informix.servdef_tab add constraint primary key(servid);
alter table informix.servdef_tab add constraint foreign key(servid) 
			references informix.hostdef_tab(servid);
revoke all on informix.servdef_tab from public as informix;
grant select on informix.servdef_tab to public as informix;


{ replicate definition }
create table informix.repdef_tab (
    repid       integer,        { replicate key }
	primaryrepid integer,		{ primary repid for shadow replicates }
	dsid		integer,		{ DataSync ID for shadow replicates }
	replsetid	integer,		{ exclusive replset }
    repstate 	smallint,       { active, suspend, ... }
    flags 		integer,        { API specfic flags }
    repname     char(256),   	{ replicate name }
    cr_primary  char(1),        { primary conflict resolution method }
    cr_secondary char(1),       { secondary conflict resolution method }
    cr_spopt    char(1),        { optimized option }
    cr_spname   char(256),   	{ conflict resolution stored procedure }
    freqtype    char(1),        { time based frequency }
    create_time integer,        { time of creation }
    modify_time integer,        { time of last change }
	susp_time	integer			{ time of last suspend }
    )  lock mode row;
create unique index informix.repdef_idix on informix.repdef_tab (repid) in table;
create unique index informix.repdef_namix on informix.repdef_tab (repname) in table;
create index informix.repdef_pridx on informix.repdef_tab (primaryrepid) in table;
alter table informix.repdef_tab add constraint primary key(repid);
revoke all on informix.repdef_tab from public as informix;
grant select on informix.repdef_tab to public as informix;

{ extended replicate attributes }
create table informix.repxtdattr (
    repid       integer,  { replicate key }
    xtd_attr1   integer,  { extended attributes }
    xtd_attr2   integer,
    xtd_attr3   integer,
    xtd_attr4   integer ) lock mode row;

create unique index informix.repxtd_idx on informix.repxtdattr (repid) in table;
alter table informix.repxtdattr add constraint primary key (repid);
revoke all on informix.repxtdattr from public as informix;
grant select on informix.repxtdattr to public as informix;

{ replicate columns }
create table informix.repl_keys_tab (
    repid       integer,
    order_num   integer,
    col_name    varchar(255)
    )   lock mode row;

create unique index informix.repkeyidx on informix.repl_keys_tab (repid, order_num) in table;
alter table informix.repl_keys_tab add constraint foreign key(repid) references
        informix.repdef_tab (repid) on delete cascade;
revoke all on informix.repl_keys_tab from public as informix;
grant select on informix.repl_keys_tab to public as informix;

{ timestamp check definition }
create table informix.check_timestamp (
    repname     char(256),      {   shadow replicate name }
    repid       integer,        {   shadow replicate id }
    node        integer,        {   node being checked }
    flags       integer         {   state }
    )   lock mode row;

alter table informix.check_timestamp add constraint primary key (repname, node);
revoke all on informix.check_timestamp from public as informix;
grant select on informix.check_timestamp to public as informix;

{ er trigger definition }
create table informix.triggerdef_tab (
	triggerid	serial,			{ trigger key - local per server }
	repid		integer,		{ replicate id for the trigger }
	spname		varchar(255),	{ sp name for the trigger }
	flags		integer			{ trigger flags }
	) lock mode row;
create unique index informix.triggerdef_idx1 on informix.triggerdef_tab (repid, spname) in table;
create unique index informix.triggerdef_idx2 on informix.triggerdef_tab (triggerid) in table;
alter table informix.triggerdef_tab add constraint primary key(triggerid);
revoke all on informix.triggerdef_tab from public as informix;
grant select on informix.triggerdef_tab to public as informix;

{ er trigger columns }
create table informix.triggercol_tab (
	triggerid	integer,		{ trigger key }
	colnum		integer, 		{ column order }
	col			lvarchar		{ column name }
	) lock mode row;
create unique index informix.triggercol_idx1 on informix.triggercol_tab (triggerid, colnum) 
	in table;
alter table informix.triggercol_tab add constraint primary key (triggerid, colnum);
revoke all on informix.triggercol_tab from public as informix;
grant select on informix.triggercol_tab to public as informix;


{ participant in replicate }
create table informix.partdef_tab (
    repid       integer,       { replicate key }
    servid      integer,       { server key }
    partnum     integer,       { partition id for table }
    partstate   smallint,      { particpant state }
    partmode    char(1),       { Primary|Readonly }
    flags       integer,       { use table owner }
	start_time	integer,	   { last start time }
	stop_time   integer,	   { last stop time }
    db          char(256), 	   { database name }
    owner       char(64),      { owner name }
    table       char(256),     { table name }
    selecstmt   lvarchar(30000) { select statement }
) lock mode row;
create unique index informix.pdef_ridsid on informix.partdef_tab (repid, servid) in table;
create index pdef_sid on informix.partdef_tab(servid) in table;
create index pdef_rid on informix.partdef_tab(repid) in table;
alter table informix.partdef_tab add constraint primary key(repid,servid);
alter table informix.partdef_tab add constraint foreign key(servid) references 
		informix.servdef_tab (servid) on delete cascade;
alter table informix.partdef_tab add constraint foreign key(repid) references 
		informix.repdef_tab (repid) on delete cascade;
revoke all on informix.partdef_tab from public as informix;
grant select on informix.partdef_tab to public as informix;

{   The following are used for the Templates }

{ Template Description }


create sequence informix.cdrSequence cycle;
revoke all on informix.cdrSequence from public as informix;
grant select on informix.cdrSequence to public as informix;

{   The following are used for the Mastered Replicate }

{ Master Replicate Description }

create table informix.mastered_replicates_tab (
	replid		integer,		{ replicate key }
	flags		integer,		{ table flags }
	tabserver	lvarchar,		{ master server }
	tabdb		lvarchar,		{ master database }
	tabowner	lvarchar,		{ table owner }
	tabname		lvarchar		{ table name }
	)  lock mode row;
create unique index informix.mst_replidx1
	on informix.mastered_replicates_tab (replid) in table;
revoke all on informix.mastered_replicates_tab from public as informix;
grant select on informix.mastered_replicates_tab to public as informix;	

create table informix.mastered_syscolumns_tab (
    replid      integer,        { replicate key }
    selectnum   smallint,       { order of selection }
    name        lvarchar,       { name of column }
    pknum       smallint,       { order within primary key }
    coltype     smallint,       { field type }
    collength   smallint,       { column length }
    extended_id integer,        { extended id of column }
    offset      smallint,       { offset in row }
    isdropped   char(1)         { 'y' if column has been dropped }
    )   lock mode row;
create unique index informix.mst_syscolidx1
    on informix.mastered_syscolumns_tab (replid, selectnum) in table;
revoke all on informix.mastered_syscolumns_tab from public as informix;
grant select on informix.mastered_syscolumns_tab to public as informix;

{ Master Extended Types }

create table informix.mastered_sysxtdtypes_tab (
    replid      integer,        { replicate key }
    extended_id integer,        { extended id }
    mode        char(1),        { mode }
    name        lvarchar,       { name of extended type }
    type        smallint,       { type of extended type }
    align       smallint,       { alignment of extended type }
    source      integer,        { source type of extended type }
    maxlen      integer,        { max length of extended type }
    length      integer         { length of extended type }
    )   lock mode row;
create unique index informix.mst_sysxtdidx1
    on informix.mastered_sysxtdtypes_tab (replid, extended_id) in table;
revoke all on informix.mastered_sysxtdtypes_tab from public as informix;
grant select on informix.mastered_sysxtdtypes_tab to public as informix;

{ Attributes for extended types }

create table informix.mastered_sysattr_tab (
    replid      integer,        { replicate }
    extended_id integer,        { Extended id of this attribute }
    seqno       smallint,       { Seq number of the attribute }
    levelno     smallint,       { nesting of this entry }
    fieldno     smallint,       { field number of entry }
    fieldname   lvarchar,       { Name of this entry }
    type        smallint,       { type of field }
    length      smallint,       { Length of field }
    xtd_type_id integer         { extended type of field }
    )   lock mode row;
create unique index informix.mst_sysattridx1
    on informix.mastered_sysattr_tab (replid, extended_id, seqno) in table;
revoke all on informix.mastered_sysattr_tab from public as informix;
grant select on informix.mastered_sysattr_tab to public as informix;

{ Resync job definition table }
create table informix.rsncjobdef_tab (
	rsncjobid		serial,		{ job identifier }
	rsncjobname		char(256),	{ job name }
	flags			integer,	{ job specific flags }
	srcservid		integer,	{ source of the job }		
	srcfilter		lvarchar,	{ optional where clause on source }
	tgtservid		integer,	{ target server for the job  }
	tgtfilter		lvarchar,	{ optional where clause on target }
	repid			integer,	{ replicate on which the job is defined }
	replsetid		integer,	{ replset on which the job is defined }
	replsetjobid	integer,	{ replsetjoid if this job is on a replset }
	blocksize		integer,	{ number of rows per resynch block }
	ctrltabname  	char(256),	{ resync control table }
	ctrltabrepid	integer,	{ replicate for the resync control table }
	acktabname  	char(256),	{ resync acknowledgements table }	
	acktabrepid   	integer,	{ replicate for the acks table }
	progtabname		char(256),	{ resync progress table }	
	progtabrepid 	integer,	{ replicate for the rsnc progress table }
	shadowrepid 	integer,	{ shadow replicate on the table being synced }
	rsncjobstate	integer,	{ current state of the job }
	totalrowcount	int8,		{ total #of  rows processed }
	sqlerror		integer,	{ sql error if the job aborted }
	isamerror		integer,	{ sql error if the job aborted }
	cdrerror		integer,	{ cdr error if the job aborted }
	tgtsqlerror		integer,	{ target sql error if the job aborted }
	tgtisamerror	integer,	{ target sql error if the job aborted }
	tgtcdrerror		integer,	{ target cdr error if the job aborted }
	start_time		datetime year to second,	{ current state of the job }
	end_time		datetime year to second		{ current state of the job }
	) lock mode row;
create unique index informix.rsncjobdef_namix on informix.rsncjobdef_tab (rsncjobname) in table;
create unique index informix.rsncjobdef_srcservidx
    on informix.rsncjobdef_tab (rsncjobid, srcservid) in table;
alter table informix.rsncjobdef_tab add constraint primary key(rsncjobid, srcservid);
revoke all on informix.rsncjobdef_tab from public as informix;
grant select on informix.rsncjobdef_tab to public as informix;

{ Resync job dependecies }
create table informix.rsncjobdeps (
		rsncjobid 	integer,
		srcservid	integer,
		parentjobid	integer 
		) lock mode row;
create index informix.rsncjobdeps_srcservidx
    on informix.rsncjobdeps (rsncjobid,srcservid) in table;
alter table informix.rsncjobdeps add constraint foreign key(rsncjobid,srcservid) 
		references informix.rsncjobdef_tab(rsncjobid,srcservid) on delete cascade;
revoke all on informix.rsncjobdeps from public as informix;
grant select on informix.rsncjobdeps to public as informix;

{ Resync procedure names }
create table informix.rsncprocnames_tab (
	rsncjobid		integer,	{ job identifier }
	srcservid		integer,	{ source for this job }
	db				char(256), 	{ db in which proc is created }
	procname		char(256)	{ rsnc procedure name }
	) lock mode row;
create index informix.rsncprocnames_idix on informix.rsncprocnames_tab (rsncjobid, 
			srcservid) in table;
alter table informix.rsncprocnames_tab add constraint foreign key(rsncjobid,srcservid) 
		references informix.rsncjobdef_tab(rsncjobid,srcservid) on delete cascade;
revoke all on informix.rsncprocnames_tab from public as informix;
grant select on informix.rsncprocnames_tab to public as informix;

{ Deleted Replicate Table }
create table informix.delrepl (
	repid	integer,			{ replicate key }
	deltime integer				{ time replicate deleted }
) lock mode row;

{ replset definitions }
create table informix.replsetdef_tab (
    replsetid     integer,        { replset key }
    replsetattr   integer,        { replset flags }
	replsetstate  integer,		  { only for exclusive replsets }
    replsetname   char(256),      { replset name }
	freqtype      char(1),        { time based frequency }
	susp_time     integer,
    create_time   integer,        { time of creation }
    modify_time   integer         { time of last change }
)  lock mode row;
create unique index informix.rsdef_idix on informix.replsetdef_tab(replsetid) in table;
create unique index informix.rsdef_namix on informix.replsetdef_tab(replsetname) in table;
alter table informix.replsetdef_tab add constraint primary key(replsetid);
revoke all on informix.replsetdef_tab from public as informix;
grant select on informix.replsetdef_tab to public as informix;

{ replicates in replsets }
create table informix.replsetpartdef (
    replsetid   integer,      { replset key }
    repid       integer       { replicate key }
) lock mode row;
create unique index informix.rspdef_rsidrid on informix.replsetpartdef(replsetid,repid) in table;
create  index informix.rspdef_rsid on informix.replsetpartdef (replsetid) in table;
create  index informix.rspdef_rid on informix.replsetpartdef (repid) in table;
alter table informix.replsetpartdef add constraint primary key(replsetid, repid); 
alter table informix.replsetpartdef add constraint foreign key(replsetid) references 
		informix.replsetdef_tab(replsetid) on delete cascade; 
alter table informix.replsetpartdef add constraint foreign key(repid) references
		informix.repdef_tab (repid) on delete cascade;
revoke all on informix.replsetpartdef from public as informix;
grant select on informix.replsetpartdef to public as informix;

{ replicate token to replicate id mapping }
create table informix.ts_repltoken  (
    tokenid     integer,      { token id }
    repid       integer       { replicate id }
) lock mode row;
create unique index informix.ts_repltoken_uidx on informix.ts_repltoken (tokenid,repid) in table;
create  index informix.ts_repltoken_idx on informix.ts_repltoken (tokenid) in table;
create  index informix.ts_repltoken_repid on informix.ts_repltoken (repid) in table;
revoke all on informix.ts_repltoken from public as informix;
grant select on informix.ts_repltoken to public as informix;

{ replicate token update requests }
create table informix.ts_repltokenupd  (
    pkhash      integer,        { primary key hash }
    tokenid     integer,        { token id }
    rsplogid    integer,        { tx begin logid }
    rsplogpos   integer,        { tx begin lopos }
    logid       integer,        { dml logid mod compression }
    logpos      integer,        { dml logpos mod compression }
    updlen      integer,        { length of updstmt }
    dbname      char(256),      { database name }
    updstmt     lvarchar(32400) { update statement }
) lock mode row;

create index informix.ts_repltokenupd_lsnidx on 
        informix.ts_repltokenupd (logid,logpos) in table;
revoke all on informix.ts_repltokenupd from public as informix;
{ no select permission to public }

{ server routing table }
create table informix.servroute (
    serverid    integer,
    parentServ  integer,
    serverflags smallint
)  lock mode row;
create unique index informix.servroute_idix on informix.servroute(serverid) in table;
create index informix.servroute_paridix on informix.servroute (parentServ) in table;
alter table informix.servroute add constraint primary key(serverid);
alter table informix.servroute add constraint foreign key(serverid) references 
		informix.servdef_tab (servid) on delete cascade;
revoke all on informix.servroute from public as informix;
grant select on informix.servroute to public as informix;

{ error log }
create table informix.cdr_errors (
    local_seqnum    serial,
    remote_seqnum   integer,
    error_number    integer,
    timestamp       datetime year to second,
    source_id       integer,
    ctrl_id         integer,
	errreviewed		char(1),
    error_desc      text
    )  lock mode row;
create unique index informix.cdrerr_lseqix on informix.cdr_errors(local_seqnum) in table;
alter table informix.cdr_errors add constraint primary key(local_seqnum);
revoke all on informix.cdr_errors from public as informix;
grant select on informix.cdr_errors to public as informix;

{ global catalog version }
create table informix.gcversion (verstamp    decimal);
{ Note: Keep this in sync with syscheckcdr() in sysmaster.sql }
insert into informix.gcversion values (9);
revoke all on informix.gcversion from public as informix;
grant select on informix.gcversion to public as informix;

{ swap log position table }
create table informix.swaploginfo (
	repid              int,
    swap1_llid          int,
    swap1_llpos         int,
    swap2_llid          int,
    swap2_llpos         int
    ) lock mode row;
create unique index informix.swaploginfo_idx1 on informix.swaploginfo (repid) in table;
revoke all on informix.swaploginfo from public as informix;
grant select on informix.swaploginfo to public as informix;

{ Shadow replicate event table }
create table informix.shadow_event_info (
	repid              int,
    servid             int,
    event_logged int default 0,
    event_received int default 0
    ) lock mode row;
create index informix.shadow_event_info_idx1 on informix.shadow_event_info (repid) in table;
create index informix.shadow_event_info_idx2 on informix.shadow_event_info (repid,servid) in table;
revoke all on informix.shadow_event_info from public as informix;
grant select on informix.shadow_event_info to public as informix;

{ replay position table }
create table informix.replaytab (
    llid    int,
    llpos   int,
    cntrlkey1   int,
    cntrlkey2   int,
    gridid   int,
    gridpos  int);
revoke all on informix.replaytab from public as informix;

{ receive control messages duplicate detection }
create table informix.recvcntldup (
    servid   integer,
    qrkey1   integer,
    qrkey2   integer,
    qrkey3   integer,
    qrkey4   integer,
    committime  integer,
    localtime   integer ) lock mode row;
revoke all on informix.recvcntldup from public as informix;
create unique index informix.recvcntldupix on informix.recvcntldup(servid) in table;

{ cdr catalog state }
create table informix.cdrstatedef_tab (
    state       integer,   { state of cdr }
    hdrpriname  char(256), { hdrprimary name }
    maxseqnum   integer    { maximum sequence num to generate repid/replsetid }
    );
revoke all on informix.cdrstatedef_tab from public as informix;
grant select on informix.cdrstatedef_tab to public as informix;

{ active delete table definitions }
create table informix.deltabdef_tab (
    tabname    char(256),   { replicate table name }
    owner      char(64),    { replicate table owner }
    deltabid   serial,      { unique id }
    dbname     char(256)    { database name }
    ) lock mode row;
create unique index informix.deltab_idix on informix.deltabdef_tab (deltabid) in table;
alter table informix.deltabdef_tab add constraint primary key(deltabid);
revoke all on informix.deltabdef_tab from public as informix;
grant select on informix.deltabdef_tab to public as informix;

{ cdr violations table definitions }
create table informix.cdrviotabdef_tab (
    tabname    char(256),   { replicate table name }
    owner      char(64),    { replicate table owner }
    viotabid   serial,      { unique id }
    dbname     char(256)    { database name }
    ) lock mode row;
create unique index informix.cdrviotab_idix on informix.cdrviotabdef_tab (viotabid) in table;
alter table informix.cdrviotabdef_tab add constraint primary key(viotabid);
revoke all on informix.cdrviotabdef_tab from public as informix;
grant select on informix.cdrviotabdef_tab to public as informix;

{ delete table to replicate mapping }
create table informix.deltabrep (
    deltabid    integer,
    repid       integer
    )  lock mode row;
create index informix.deltabrep_didix on informix.deltabrep(deltabid) in table;
create index informix.deltabrep_ridix on informix.deltabrep(repid) in table;
alter table informix.deltabrep add constraint foreign key(deltabid) references 
		informix.deltabdef_tab(deltabid) on delete cascade;
alter table informix.deltabrep add constraint foreign key(repid) references 
		informix.repdef_tab(repid) on delete cascade;
revoke all on informix.deltabrep from public as informix;
grant select on informix.deltabrep to public as informix;

{ time based replication frequency information }
create table informix.freqdef (
    repid       integer,
	objtype		char(1),       { Replicate or rSet??? }	
    hour        smallint,
    min         smallint,
    day         smallint,
    lastexec    integer        { time when run }
    ) lock mode row;
create index informix.freqdef_ridix on informix.freqdef(repid) in table;
revoke all on informix.freqdef from public as informix;
grant select on informix.freqdef to public as informix;
	
{ For tracking the row counts for resync jobs }
create table informix.rsncrowstats (
	srcservid	integer,
	rsncjobid	integer,
	sequence	integer,
	rsncrowcnt	integer
	) lock mode row; 
create index informix.rsncrow_idix
    on informix.rsncrowstats(srcservid,rsncjobid,sequence) in table;
create index informix.rsncrow_srcservidx
    on informix.rsncrowstats(rsncjobid,srcservid) in table;
alter table informix.rsncrowstats add constraint foreign key(rsncjobid,srcservid) 
		references informix.rsncjobdef_tab(rsncjobid,srcservid) on delete cascade;
revoke all on informix.rsncrowstats from public as informix;
grant select on informix.rsncrowstats to public as informix;
	
{ Replicate suspend/resume events }
create table informix.replevents (
	repid		integer,
	eventtime	integer,
	event		integer) lock mode row;
revoke all on informix.replevents from public as informix;
grant select on informix.replevents to public as informix;

{ PostCommit Trigger Progress Table }
create table informix.cdr_pcpt (
	logid		integer,
	logpos		integer) lock mode row;

revoke all on informix.cdr_pcpt from public as informix;
grant select on informix.cdr_pcpt to public as informix;


{ PostCommit DDL log Progress Table }
create table informix.cdrddlpt (
	pttype		char(1),
	logid		integer,
	logpos		integer,
	seq			integer) lock mode row;
create index informix.cdrddlpt_idix
	on informix.cdrddlpt(pttype) in table;
revoke all on informix.cdrddlpt from public as informix;
grant select on informix.cdrddlpt to public as informix;

insert into informix.cdrddlpt values ('S',0,0,0);
insert into informix.cdrddlpt values ('A',0,0,0);


create table informix.replcheck_stat_tab (                     
        replcheck_id                serial primary key,
        replcheck_name              varchar(255),
        replcheck_replname          varchar(255),
        replcheck_type              char(1),
        replcheck_scope             char(1),
        replcheck_numrows           integer,
        replcheck_rows_processed    integer,
        replcheck_status            char(1),
        replcheck_start_time        datetime year to second,
        replcheck_end_time          datetime year to second 
        )   lock mode row;

revoke all on informix.replcheck_stat_tab from public as informix;
grant insert, update, delete, select on informix.replcheck_stat_tab to public as informix;
create index informix.replcheck_name on informix.replcheck_stat_tab (replcheck_name);

create view informix.replcheck_stat (
        replcheck_id, replcheck_name, replcheck_replname, replcheck_type,
        replcheck_scope, replcheck_numrows, replcheck_rows_processed,
        replcheck_status, replcheck_start_time, replcheck_end_time)
    as select
        r.replcheck_id, cdrgcdisp(r.replcheck_name), 
        cdrgcdisp(r.replcheck_replname), r.replcheck_type, 
        r.replcheck_scope, r.replcheck_numrows,
        r.replcheck_rows_processed, r.replcheck_status,
        r.replcheck_start_time, r.replcheck_end_time
    from informix.replcheck_stat_tab r;

revoke all on informix.replcheck_stat from public as informix;
grant select on informix.replcheck_stat to public as informix;

create table informix.replcheck_stat_node_tab (
        replnode_replcheck_id       integer,
        replnode_node_id            integer,
        replnode_order              integer,
        replnode_node_name          varchar(255),
        replnode_table_owner        varchar(255),
        replnode_table_name         varchar(255),
        replnode_row_count          integer,
        replnode_processed_rows     integer,
        replnode_missing_rows       integer,
        replnode_extra_rows         integer,
        replnode_mismatched_rows    integer,
        replnode_extra_child_rows   integer
        )   lock mode row;

revoke all on informix.replcheck_stat_node_tab from public as informix;
grant insert, update, delete, select 
        on informix.replcheck_stat_node_tab to public as informix;
create unique index informix.replnode_id on informix.replcheck_stat_node_tab
        ( replnode_replcheck_id, replnode_node_id);
create index informix.replnode_order on informix.replcheck_stat_node_tab
        ( replnode_replcheck_id, replnode_order);
alter table informix.replcheck_stat_node_tab add constraint
        foreign key (replnode_replcheck_id)
        references informix.replcheck_stat_tab(replcheck_id)
        on delete cascade;

create view informix.replcheck_stat_node (
        replnode_replcheck_id, replnode_id, replnode_order,
        replnode_node_name, replnode_table_owner, replnode_table_name,
        replnode_row_count, replnode_processed_rows, 
        replnode_missing_rows, replnode_extra_rows,
        replnode_mismatched_rows, replnode_extra_child_rows)
    as select
        r.replnode_replcheck_id, r.replnode_node_id, r.replnode_order,
        cdrgcdisp(r.replnode_node_name), cdrgcdisp(r.replnode_table_owner),
        cdrgcdisp(r.replnode_table_name), r.replnode_row_count,
        r.replnode_processed_rows, r.replnode_missing_rows,
        r.replnode_extra_rows, r.replnode_mismatched_rows, 
        r.replnode_extra_child_rows
    from informix.replcheck_stat_node_tab r;

revoke all on informix.replcheck_stat_node from public as informix;
grant select on informix.replcheck_stat_node to public as informix;

create table informix.replcheck_restart_tab (
    replcheck_restart_id            serial primary key,
    replcheck_restart_type          char(1),
    replcheck_restart_state         char(1),
    replcheck_restart_name          varchar(255),
    replcheck_restart_rc            integer,
    replcheck_restart_regtime       integer,
    replcheck_restart_retrytime     integer,
    replcheck_restart_retryincr     integer,
    replcheck_restart_retrytimes    integer,
    replcheck_restart_command       lvarchar,
    replcheck_restart_object        lvarchar, 
    replcheck_restart_parms         lvarchar(10000)
    )   lock mode row;
    
revoke all on informix.replcheck_restart_tab from public as informix;
grant insert, update, delete, select on informix.replcheck_restart_tab to public as informix;
create index informix.replcheck_restart_idx1 on informix.replcheck_restart_tab(
    replcheck_restart_state, 
    replcheck_restart_retrytime);

create index informix.replcheck_restart_idx2 on informix.replcheck_restart_tab(
    replcheck_restart_name);

create view informix.replcheck_restart (
        replcheck_restart_id, replcheck_restart_type,
        replcheck_restart_state, replcheck_restart_name,
        replcheck_restart_rc,
        replcheck_restart_regtime, replcheck_restart_retrytime,
        replcheck_restart_retryincr, replcheck_restart_retrytimes,
        replcheck_restart_command, replcheck_restart_parms)
    as  select
        r.replcheck_restart_id, 
        r.replcheck_restart_type,
        r.replcheck_restart_state,
        cdrgcdisp(r.replcheck_restart_name),
        r.replcheck_restart_rc,
        informix.timet_to_datetime(r.replcheck_restart_regtime), 
        informix.timet_to_datetime(r.replcheck_restart_retrytime),
        r.replcheck_restart_retryincr, 
        r.replcheck_restart_retrytimes,
        r.replcheck_restart_command,
        r.replcheck_restart_parms
    from informix.replcheck_restart_tab r;

revoke all on informix.replcheck_restart from public as informix;
grant select on informix.replcheck_restart to public as informix;

{ Stored procedure for setting boolean 'columns' in views for flags values }
    create procedure informix.bitval ( bitset int, bitmask int) returning int;
    if (bitset < 0) then
        if (bitmask < 0) then
        return 1;
        end if;
        let bitset = bitset + 2147483648;
    end if;
    if (bitset > 1073741824) then
        if (bitmask = 1073741824) then
        return 1;
        end if;
    end if
    if (mod(bitset,2*bitmask) >= bitmask) then
        return 1;
    end if
    return 0;
    end procedure;
grant execute on informix.bitval to public as informix;



{	The following views made on syscdr are used convert the internal format }
{	of the character data to an external, displayable format. }

create view informix.hostdef(servid, name, groupname)
    as select
        h.servid,
        cdrgcdisp(h.name),
        cdrgcdisp(h.groupname) 
    from informix.hostdef_tab h;
revoke all on informix.hostdef from public as informix;
grant select on informix.hostdef to public as informix;

create view informix.servdef (servid, servstate, idletimeout, atsdir, risdir,
        flags, create_time, modify_time)
	as select s.servid, s.servstate, s.idletimeout,
        cdrgcdisp(s.atsdir), cdrgcdisp(s.risdir), s.flags,
        timet_to_datetime(s.create_time), timet_to_datetime(s.modify_time)
    from informix.servdef_tab s;
revoke all on informix.servdef from public as informix;
grant select on informix.servdef to public as informix;

create view informix.repdef (repid, primaryrepid, replsetid, repstate, flags,
        repname, cr_primary, cr_secondary,
        cr_spopt, cr_spname, freqtype,
        create_time, modify_time, susp_time)
    as select  r.repid, r.primaryrepid, r.replsetid, r.repstate, r.flags,
        cdrgcdisp(r.repname), r.cr_primary, r.cr_secondary,
        r.cr_spopt, cdrgcdisp(r.cr_spname), r.freqtype,
        timet_to_datetime(r.create_time), 
        timet_to_datetime(r.modify_time), 
        timet_to_datetime(r.susp_time)
    from informix.repdef_tab r;
revoke all on informix.repdef from public as informix;
grant select on informix.repdef to public as informix;

create view informix.repl_keys(repid, order_num, col_name) 
    as select repid, order_num, cdrgcdisp(col_name)
            from informix.repl_keys_tab;
revoke all on informix.repl_keys from public as informix;
grant select on informix.repl_keys to public as informix;

create view informix.partdef (repid, servid, partnum, partstate, partmode,
        flags, start_time, stop_time, db, owner, table,
        selecstmt)
	as select p.repid, p.servid, p.partnum, p.partstate, p.partmode,
        p.flags, p.start_time, p.stop_time,
        cdrgcdisp(p.db), cdrgcdisp(p.owner),
        cdrgcdisp(p.table), cdrgcdisp(p.selecstmt)
    from informix.partdef_tab p;
revoke all on informix.partdef from public as informix;
grant select on informix.partdef to public as informix;

create view informix.rsncjobdef (rsncjobid, rsncjobname,  flags,
		srcservid, srcfilter, tgtservid, tgtfilter, repid, replsetid, 
		replsetjobid,  blocksize, ctrltabname, ctrltabrepid, 
		acktabname, acktabrepid, progtabname, progtabrepid, shadowrepid,
		rsncjobstate, totalrowcount, sqlerror, isamerror, cdrerror, tgtsqlerror, 
		tgtisamerror, tgtcdrerror, start_time, end_time) 
	as select r.rsncjobid, cdrgcdisp(r.rsncjobname),  r.flags, 
	    r.srcservid, cdrgcdisp(r.srcfilter), r.tgtservid, cdrgcdisp(r.tgtfilter),
	    r.repid, r.replsetid, r.replsetjobid,  r.blocksize, 
	    cdrgcdisp(r.ctrltabname), r.ctrltabrepid, cdrgcdisp(r.acktabname), 
		r.acktabrepid, cdrgcdisp(r.progtabname), r.progtabrepid, 
		r.shadowrepid, r.rsncjobstate, r.totalrowcount, r.sqlerror, 
		r.isamerror, r.cdrerror, r.tgtsqlerror, r.tgtisamerror, r.tgtcdrerror, 
		r.start_time, r.end_time
	from informix.rsncjobdef_tab r;
revoke all on informix.rsncjobdef from public as informix;
grant select on informix.rsncjobdef to public as informix;

create view informix.rsncprocnames(rsncjobid, srcservid, db, procname)
	as select r.rsncjobid, r.srcservid, cdrgcdisp(r.db), cdrgcdisp(r.procname)
	from informix.rsncprocnames_tab r;
revoke all on informix.rsncprocnames from public as informix;
grant select on informix.rsncprocnames to public as informix;

create view informix.replsetdef (replsetid, replsetattr, replsetstate,
    	replsetname, freqtype, susp_type, create_time, modify_time)
	as select r.replsetid, r.replsetattr, r.replsetstate,
        cdrgcdisp(r.replsetname), r.freqtype,
        timet_to_datetime(r.susp_time), 
        timet_to_datetime(r.create_time), 
        timet_to_datetime(r.modify_time)
    from informix.replsetdef_tab r;
revoke all on informix.replsetdef from public as informix;
grant select on informix.replsetdef to public as informix;

create view informix.triggerdef (triggerid, repid, spname, flags)
	as select t.triggerid, t.repid, cdrgcdisp(t.spname), t.flags
	from informix.triggerdef_tab t;
revoke all on informix.triggerdef from public as informix;
grant select on informix.triggerdef to public as informix;

create view informix.triggercol (triggerid, colnum, col)
	as select t.triggerid, t.colnum, cdrgcdisp(t.col)
	from informix.triggercol_tab t;
revoke all on informix.triggercol from public as informix;
grant select on informix.triggercol to public as informix;

create view informix.deltabdef (tabname, owner, dbname, deltabid)
	as select cdrgcdisp(d.tabname), cdrgcdisp(d.owner), cdrgcdisp(d.dbname), d.deltabid
    from informix.deltabdef_tab d;
revoke all on informix.deltabdef from public as informix;
grant select on informix.deltabdef to public as informix;

create view informix.cdrviotabdef (tabname, owner, dbname, viotabid)
	as select cdrgcdisp(v.tabname), cdrgcdisp(v.owner), cdrgcdisp(v.dbname), v.viotabid
    from informix.cdrviotabdef_tab v;
revoke all on informix.cdrviotabdef from public as informix;
grant select on informix.cdrviotabdef to public as informix;

create view informix.cdrstate (state, hdrpriname, maxseqnum)
	as select c.state, cdrgcdisp(c.hdrpriname), c.maxseqnum 
    from informix.cdrstatedef_tab c;
revoke all on informix.cdrstate from public as informix;
grant select on informix.cdrstate to public as informix;

create view informix.mastered_syscolumns
    	(replid, selectnum, name, pknum, coltype, collength, 
        extended_id, offset, isdropped)
	as select replid, selectnum, cdrgcdisp(name), pknum,
    	coltype, collength, extended_id, offset, isdropped
	from informix.mastered_syscolumns_tab;
revoke all on informix.mastered_syscolumns from public as informix;
grant select on informix.mastered_syscolumns to public as informix;

create view informix.mastered_sysxtdtypes
	    (replid,  extended_id, mode, name, type,
	     align, source, maxlen, length)
	as select replid, extended_id, mode, cdrgcdisp(name),
    	type, align, source, maxlen, length
	from informix.mastered_sysxtdtypes_tab;
revoke all on informix.mastered_sysxtdtypes from public as informix;
grant select on informix.mastered_sysxtdtypes to public as informix;

create view informix.mastered_sysattr
    	(replid, extended_id, seqno, levelno, fieldno, fieldname,
	     type, length, xtd_type_id)
	as select replid, extended_id, seqno, levelno,
    	fieldno, cdrgcdisp(fieldname), type, length, xtd_type_id
	from informix.mastered_sysattr_tab;
revoke all on informix.mastered_sysattr from public as informix;
grant select on informix.mastered_sysattr to public as informix;

create view informix.mastered_replicates
		(replid, flags, tabserver, tabdb, tabowner, tabname)
	as select m.replid, m.flags, cdrgcdisp(m.tabserver),
		cdrgcdisp(m.tabdb), cdrgcdisp(m.tabowner), cdrgcdisp(m.tabname)
	from informix.mastered_replicates_tab m;
revoke all on informix.mastered_replicates from public as informix;
grant select on informix.mastered_replicates to public as informix;

create view informix.templatedef
		(replsetid, replsetname)
	as select r.replsetid, r.replsetname
		from informix.replsetdef r
where informix.bitval(r.replsetattr, 2097152) = 1;
revoke all on informix.templatedef from public as informix;
grant select on informix.templatedef to public as informix;

create view informix.templatetables
		(templatename, repname, replid,  server, database, owner, table)
	as select t.replsetname, r.repname, m.replid, m.tabserver, 
		m.tabdb, m.tabowner, m.tabname 
	from informix.templatedef t, outer (replsetpartdef p, 
		outer (mastered_replicates m,  outer repdef r)) 
	where t.replsetid = p.replsetid
	and	  p.repid = m.replid
	and   m.replid = r.repid;
revoke all on informix.templatetables from public as informix;
grant select on informix.templatetables to public as informix;

create procedure informix.cdrcmd(p1 integer)
   	define ignoreMe integer;
	execute function cdrcmd(p1) into ignoreMe;
	end procedure;

create procedure informix.cdrcmd(p1 integer, p2 integer)
	define ignoreMe integer;
	execute function informix.cdrcmd(p1, p2) into ignoreMe;
	end procedure;

create procedure informix.cdrcmd(p1 integer, p2 integer, p3 integer)
	define ignoreMe integer;
	execute function informix.cdrcmd(p1, p2, p3) into ignoreMe;
	end procedure;

create procedure informix.cdrcmd(p1 integer, p2 lvarchar)
	define ignoreMe integer;
	execute function informix.cdrcmd(p1, p2) into ignoreMe;
	end procedure;

create procedure informix.cdrcmdfl(p1 integer)
	define ignoreMe float;
	execute function informix.cdrcmdfl(p1) into ignoreMe;
	end procedure;

create procedure informix.rss2er()
    define ignoreMe integer;
    execute function informix.cdrcmd(115) into ignoreMe;
    end procedure;

{ Tables, views for grid based replication }

create sequence informix.gridseq increment by 1 start with 1 
    nomaxvalue nominvalue nocycle order;

create sequence informix.gridack increment by 1 start with 1
    nomaxvalue nominvalue nocycle order;

create sequence informix.gridnack increment by 1 start with 1
    nomaxvalue nominvalue nocycle order;

create sequence informix.gridredo increment by 1 start with 1
    nomaxvalue nominvalue nocycle order; 

create table informix.grid_cdr_node_tab (
    gn_id           integer,                { cdrid }
    gn_name         varchar(255),           { cdrname }
    gn_version      integer,                { grid version }
    gn_create_time  integer,                { time created }
    gn_sync_time    integer,                { time internal repl synced }
    gn_node_type    char(1),                { Root, Nonroot, Leaf }
    gn_need_sync    char(1),                { need sync flag }
    gn_sync_by      integer                 { node performing sync }
    )   lock mode row;

create unique index informix.grid_cdr_node_idx1 on informix.grid_cdr_node_tab(gn_id);
alter table informix.grid_cdr_node_tab add constraint primary key (gn_id);
revoke all on informix.grid_cdr_node_tab from public as informix;
grant select on informix.grid_cdr_node_tab to public as informix;

create trigger informix.grid_cmd_nodet1 insert on informix.grid_cdr_node_tab
    referencing new as newrow
    for each row
        (execute procedure informix.cdrcmd(111));


create view informix.grid_cdr_node( gn_id, gn_create_time, gn_sync_time, 
        gn_node_type, gn_need_sync, gn_sync_by)
	as select gn_id, informix.timet_to_datetime(gn_create_time), 
        informix.timet_to_datetime(gn_sync_time), 
        gn_node_type, gn_need_sync,
        gn_sync_by
    from informix.grid_cdr_node_tab;

revoke all on informix.grid_cdr_node from public as informix;
grant select on informix.grid_cdr_node to public as informix;


create table informix.grid_def_tab (
    gd_name         char(256),              { grid name }
    gd_id           integer                 { grid id - from replset id }
    )   lock mode row;

create unique index informix.grid_def_idx1 on informix.grid_def_tab(gd_id);
alter table informix.grid_def_tab add constraint primary key (gd_id);
create unique index informix.grid_def_idx2 on informix.grid_def_tab(gd_name);
alter table informix.grid_def_tab add constraint unique(gd_name);
revoke all on informix.grid_def_tab from public as informix;
grant select on informix.grid_def_tab to public as informix;

create view informix.grid_def(
    	gd_name, gd_id)
	as select 
    	r.gd_name, r.gd_id 
	from informix.grid_def_tab r;

revoke all on informix.grid_def from public as informix;
grant select on informix.grid_def to public as informix;

create table informix.grid_part_tab (
    gp_id           integer,                { grid id }
    gp_servid       integer,                { servdef servid }
    gp_enable       char(1)                 { enable grid commands }
    )   lock mode row;    
create unique index informix.grid_part_idx1 on informix.grid_part_tab(gp_servid, gp_id);
alter table informix.grid_part_tab add constraint primary key(gp_servid, gp_id);
revoke all on informix.grid_part_tab from public as informix;
grant select on informix.grid_part_tab to public as informix;

create view informix.grid_part (
	    gp_id, gp_servid, gp_enable )
	as select r.gp_id, r.gp_servid, r.gp_enable 
	from informix.grid_part_tab r; 

revoke all on informix.grid_part from public as informix;
grant select on informix.grid_part to public as informix;

create table informix.grid_users_tab (
    gu_id           integer,                { grid ID }
    gu_user         char(32)                { user name }
    )   lock mode row;
create unique index informix.grid_users_idx1 on informix.grid_users_tab(gu_id, gu_user);
alter table informix.grid_users_tab add constraint primary key(gu_id, gu_user);
revoke all on informix.grid_users_tab from public as informix;
grant select on informix.grid_users_tab to public as informix;


create view informix.grid_users(gu_id, gu_user)
    as select u.gu_id, u.gu_user
    from informix.grid_users_tab u;
revoke all on informix.grid_users from public as informix;
grant select on informix.grid_users to public as informix;

create table informix.grid_region_tab (
    gr_regid        bigint,                { Region ID }
    gr_name         char(256),              { Region Name }
    gr_grid         integer                 { Parent Grid }
    )   lock mode row;
create unique index informix.grid_reg_idx1 
        on informix.grid_region_tab(gr_regid);
alter table informix.grid_region_tab add constraint primary key(gr_regid);
alter table informix.grid_region_tab add constraint
    ( foreign key (gr_grid) references grid_def_tab(gd_id) 
        on delete cascade);
create unique index informix.grid_reg_idx2
        on informix.grid_region_tab(gr_name);
revoke all on informix.grid_region_tab from public as informix;
grant select on informix.grid_region_tab to public as informix;


create table informix.grid_region_part_tab (
    grp_regid       bigint,                 { Region ID }
    grp_partid      integer                 { participant id }
    )   lock mode row;
create unique index informix.grid_reg_part_idx
        on  informix.grid_region_part_tab(grp_regid, grp_partid);
alter table informix.grid_region_part_tab 
        add constraint primary key(grp_regid, grp_partid);
alter table informix.grid_region_part_tab add constraint
    (foreign key (grp_regid) references informix.grid_region_tab (gr_regid) 
        on delete cascade);
revoke all on informix.grid_region_part_tab from public as informix;
grant select on informix.grid_region_part_tab to public as informix;

create trigger grid_part_del delete on informix.grid_part_tab
    referencing old as gp
    for each row (delete from informix.grid_region_part_tab rp
            where gp.gp_servid = rp.grp_partid and
            rp.grp_regid in (select gr_regid from informix.grid_region_tab
                where gr_grid = gp.gp_id));


create table informix.grid_database_tab (
    gdb_key          bigint,                { key }
    gdb_gid          integer,               { grid ID }
    gdb_dbname       char(256)              { grid database }
    )   lock mode row;
create unique index informix.grid_gdb_idx1 
    on informix.grid_database_tab(gdb_key);
alter table informix.grid_database_tab add constraint primary key(gdb_key);
alter table informix.grid_database_tab add constraint
    ( foreign key (gdb_gid) references grid_def_tab(gd_id)
        on delete cascade);
revoke all on informix.grid_database_tab from public as informix;
grant select on informix.grid_database_tab to public as informix;

create view informix.grid_database (gdb_key, gdb_gid, gdb_dbname)
    as select
    g.gdb_key, g.gdb_gid, cdrgcdisp(g.gdb_dbname)
    from informix.grid_database_tab g;
revoke all on informix.grid_database from public as informix;
grant select on informix.grid_database to public as informix;

create table informix.grid_tablelist_tab (
    gtb_key         bigint,                 { key }
    gtb_dbkey       bigint,                 { grid db key }
    gtb_type        char(1),                { replicated ? }
    gtb_owner       char(64),               { table owner }
    gtb_table       char(256)               { table name }
    )   lock mode row;
create unique index informix.gtb_idx1 
    on informix.grid_tablelist_tab(gtb_key);
alter table informix.grid_tablelist_tab add constraint
    primary key (gtb_key);
alter table informix.grid_tablelist_tab add constraint
    ( foreign key (gtb_dbkey) references informix.grid_database_tab(gdb_key)
        on delete cascade);
create index informix.gtb_idx2
    on informix.grid_tablelist_tab(gtb_table);
revoke all on informix.grid_tablelist_tab from public as informix;
grant select on informix.grid_tablelist_tab to public as informix;


create view informix.grid_tablelist(
        gtb_key, gtb_dbkey, gtb_type, gtb_owner, gtb_table)
    as select
        t.gtb_key,  t.gtb_dbkey, t.gtb_type, 
        cdrgcdisp(t.gtb_owner), cdrgcdisp(t.gtb_table)
    from informix.grid_tablelist_tab t;
revoke all on informix.grid_tablelist from public as informix;
grant select on informix.grid_tablelist to public as informix;
     

create table informix.grid_cmd_tab (
    gcmd_source     integer,                { source node }
    gcmd_stmtid     integer,                { statement identity }
    gcmd_gridid     integer,                { grid ID }
    gcmd_numpieces  integer,                { number of rows in grid_cmd_part }
    gcmd_textsize   integer,                { size of this command in bytes }
    gcmd_flags      integer,                { command type }
    gcmd_time       integer,                { time of command }
    gcmd_database   char(128),              { database for this operation } 
    gcmd_user       char(32),               { user name for this operation }
    gcmd_locale     char(36),               { locale for command }
    gcmd_collation  char(36),               { collation for command }
    gcmd_tag        lvarchar                { collation tag }
    )   lock mode row;

create unique index informix.grid_cmd_idx1 on informix.grid_cmd_tab (gcmd_source, gcmd_stmtid);
alter table informix.grid_cmd_tab add constraint primary key (gcmd_source, gcmd_stmtid);
revoke all on informix.grid_cmd_tab from public as informix;
grant select on informix.grid_cmd_tab to public as informix;

create view informix.grid_cmd (gcmd_source, gcmd_stmtid, gcmd_gridid, 
        gcmd_numpieces, gcmd_textsize, gcmd_flags, gcmd_time,
        gcmd_database, gcmd_user, gcmd_locale, gcmd_collation, gcmd_tag)
	as select r.gcmd_source, r.gcmd_stmtid, r.gcmd_gridid, r.gcmd_numpieces,
    	r.gcmd_textsize, r.gcmd_flags, informix.timet_to_datetime(r.gcmd_time),
	    r.gcmd_database, r.gcmd_user, r.gcmd_locale, r.gcmd_collation, 
    	r.gcmd_tag
    from informix.grid_cmd_tab r;
revoke all on informix.grid_cmd from public as informix;
grant select on informix.grid_cmd to public as informix;

create table informix.grid_cmd_part_tab (
    gcmdpart_gridid     integer,            { grid id }
    gcmdpart_source     integer,            { source node }
    gcmdpart_stmtid     integer,            { stmt num  }
    gcmdpart_seq        integer,            { stmt sequence }
    gcmdpart_ercmd      integer,            { er command }
    gcmdpart_text       lvarchar(30000)     { statement text }
    )  lock mode row;
create unique index informix.grid_cmd_part_idx1
    on informix.grid_cmd_part_tab (gcmdpart_source, gcmdpart_stmtid, gcmdpart_seq);
alter table informix.grid_cmd_part_tab
 add constraint primary key (gcmdpart_source,
            gcmdpart_stmtid, gcmdpart_seq);
revoke all on informix.grid_cmd_part_tab from public as informix;
grant select on informix.grid_cmd_part_tab to public as informix;

create view informix.grid_cmd_part (gcmdpart_gridid, gcmdpart_source,
        gcmdpart_stmtid, gcmdpart_seq, gcmdpart_ercmd, gcmdpart_text)
    as select gcmdpart_gridid, gcmdpart_source,
        gcmdpart_stmtid, gcmdpart_seq, gcmdpart_ercmd, gcmdpart_text
    from informix.grid_cmd_part_tab;
revoke all on informix.grid_cmd_part from public as informix;
grant select on informix.grid_cmd_part to public as informix;

create table informix.defered_grid_cmd_tab(
    gcmd_source     integer,               { source node }
    gcmd_stmtid     integer,               { statement identity }
    gcmd_gridid     integer,               { grid ID }
    gcmd_numpieces  integer,               { number of rows in grid_cmd_part }
    gcmd_textsize   integer,               { size of this command in bytes }
    gcmd_flags      integer,               { command type }
    gcmd_time       integer,               { time of command }
    gcmd_database   char(128),             { database for this operation }
    gcmd_user       char(32),              { user name for this operation }
    gcmd_locale     char(36),              { locale for command }
    gcmd_collation  char(36),              { collation for command }
    gcmd_tag        lvarchar               { collation tag }
    )   lock mode row;

create unique index informix.dgrid_cmd_idx1 
    on informix.defered_grid_cmd_tab (gcmd_source, gcmd_stmtid);
alter table informix.defered_grid_cmd_tab 
add constraint primary key (gcmd_source, gcmd_stmtid);
revoke all on informix.defered_grid_cmd_tab from public as informix;
grant select on informix.defered_grid_cmd_tab to public as informix;

create view informix.defered_grid_cmd (gcmd_source, gcmd_stmtid, gcmd_gridid,
        gcmd_numpieces, gcmd_textsize, gcmd_flags, gcmd_time,
        gcmd_database, gcmd_user, gcmd_locale, gcmd_collation, gcmd_tag)
    as select r.gcmd_source, r.gcmd_stmtid, r.gcmd_gridid, 
        r.gcmd_numpieces, r.gcmd_textsize, 
        r.gcmd_flags, informix.timet_to_datetime(r.gcmd_time),
        r.gcmd_database, r.gcmd_user, r.gcmd_locale, r.gcmd_collation,
        r.gcmd_tag
    from informix.defered_grid_cmd_tab r;
revoke all on informix.defered_grid_cmd from public as informix;
grant select on informix.defered_grid_cmd to public as informix;

create table informix.defered_grid_cmd_part_tab (
    gcmdpart_gridid     integer,            { grid id }
    gcmdpart_source     integer,            { source node }
    gcmdpart_stmtid     integer,            { stmt num  }
    gcmdpart_seq        integer,            { stmt sequence }
    gcmdpart_ercmd      integer,            { er command }
    gcmdpart_text       lvarchar(30000)     { statement text }
    )  lock mode row;
create unique index informix.dgrid_cmd_part_idx1
    on informix.defered_grid_cmd_part_tab 
        (gcmdpart_source, gcmdpart_stmtid, gcmdpart_seq);

alter table informix.defered_grid_cmd_part_tab
 add constraint primary key (gcmdpart_source,
            gcmdpart_stmtid, gcmdpart_seq);
revoke all on informix.defered_grid_cmd_part_tab from public as informix;
grant select on informix.defered_grid_cmd_part_tab to public as informix;

create view informix.defered_grid_cmd_part (gcmdpart_gridid, 
        gcmdpart_source, gcmdpart_stmtid, gcmdpart_seq, gcmdpart_ercmd, 
        gcmdpart_text)
    as select gcmdpart_gridid, gcmdpart_source,
        gcmdpart_stmtid, gcmdpart_seq, gcmdpart_ercmd, gcmdpart_text
    from informix.defered_grid_cmd_part_tab;
revoke all on informix.defered_grid_cmd_part from public as informix;
grant select on informix.defered_grid_cmd_part to public as informix;

create table informix.grid_cmd_errors_tab (
    gerr_target         integer,            { target node }
    gerr_errid          integer,            { target errid }
    gerr_source         integer,            { source node }
    gerr_stmtid         integer,            { statment identity }
    gerr_gridid         integer,            { grid id }
    gerr_sqlerr         integer,            { sql error code }
    gerr_isamerr        integer,            { isam error code }
    gerr_time           integer,            { time of nack }
    gerr_text           lvarchar            { error text }
    )   lock mode row;
alter table informix.grid_cmd_errors_tab 
        add constraint primary key (gerr_target, gerr_errid);
create index informix.grid_errors_source on informix.grid_cmd_errors_tab
        (gerr_gridid, gerr_source, gerr_stmtid);
revoke all on informix.grid_cmd_errors_tab from public as informix;
grant select on informix.grid_cmd_errors_tab to public as informix;

create trigger informix.grid_cmd_clean_nacks insert on informix.grid_cmd_errors_tab
    referencing new as newrow
    for each row
        (execute procedure informix.cdrcmd(108));

create view informix.grid_cmd_errors (
	    gerr_target, gerr_errid, gerr_source, gerr_stmtid, gerr_gridid,
    	gerr_sqlerr, gerr_isamerr, gerr_time, gerr_text )
	as select r.gerr_target, r.gerr_errid, r.gerr_source, r.gerr_stmtid,
    	r.gerr_gridid, r.gerr_sqlerr, r.gerr_isamerr,
	    informix.timet_to_datetime( r.gerr_time), r.gerr_text
    from informix.grid_cmd_errors_tab r;

revoke all on informix.grid_cmd_errors from public as informix;
grant select on informix.grid_cmd_errors to public as informix;

create table informix.grid_cmd_ack_tab (
    gack_target         integer,            { target node }
    gack_ackid          integer,            { ack identity }
    gack_gridid         integer,            { grid id }
    gack_source         integer,            { source node }
    gack_stmtid         integer,            { statement id }
    gack_time           integer,            { time of ack }
    gack_output         lvarchar(30000)     { output from grid_function }
    )   lock mode row;
create unique index informix.grid_ack_idx1 on informix.grid_cmd_ack_tab (
       gack_source, gack_stmtid, gack_target, gack_ackid);
alter table informix.grid_cmd_ack_tab  
    add constraint primary key (gack_source, gack_stmtid, 
            gack_target, gack_ackid);
revoke all on informix.grid_cmd_ack_tab from public as informix;
grant select on informix.grid_cmd_ack_tab to public as informix;

create trigger informix.grid_cmd_clean_acks insert on informix.grid_cmd_ack_tab
    referencing new as newrow
    for each row
        (execute procedure informix.cdrcmd(108));

create view informix.grid_cmd_ack ( gack_target, gack_ackid, gack_gridid,
    	gack_source, gack_stmtid, gack_time, gack_output )
	as select r.gack_target, r.gack_ackid, r.gack_gridid, 
    	r.gack_source, r.gack_stmtid, 
	    informix.timet_to_datetime(r.gack_time), r.gack_output
    from informix.grid_cmd_ack_tab r;

revoke all on informix.grid_cmd_ack from public as informix;
grant select on informix.grid_cmd_ack to public as informix;

create table informix.grid_redo_tab(
    gredo_source        integer,            { node on which command executed }
    gredo_id            integer,            { redo id }
    gredo_time          integer,            { time of redo }
    gredo_flags         integer,            { flags and options }
    gredo_grid          integer,            { grid to redo }
    gredo_source_list   lvarchar,           { source list to redo }
    gredo_target_list   lvarchar,           { target list to redo }
    gredo_cmd_list      lvarchar,           { command list to redo }
    gredo_tag           lvarchar            { tag id for redo }
    )   lock mode row;
create unique index informix.grid_redo_idx1
    on informix.grid_redo_tab (gredo_source, gredo_id);
alter table informix.grid_redo_tab
    add constraint primary key(gredo_source, gredo_id);
revoke all on informix.grid_redo_tab from public as informix;
grant select on informix.grid_redo_tab to public as informix;

create procedure informix.grid_cmd_del(
        sourceid    integer,
        stmtid      integer)
define  repl_state integer;

execute function informix.ifx_get_erstate() into repl_state;
execute procedure informix.ifx_set_erstate(0);

delete {+ AVOID_FULL } from informix.grid_cmd_part_tab
        where gcmdpart_source = sourceid
          and gcmdpart_stmtid = stmtid;
delete {+ AVOID_FULL } from informix.grid_cmd_errors_tab
        where gerr_source = sourceid
          and gerr_stmtid = stmtid;
delete {+ AVOID_FULL } from informix.grid_cmd_ack_tab
        where gack_source = sourceid
          and gack_stmtid = stmtid;
execute procedure informix.ifx_set_erstate(repl_state);

end procedure;

create procedure informix.grid_cdr_node_del(nodeid integer)
define replstate integer;
execute function informix.ifx_get_erstate() into replstate;
execute procedure informix.ifx_set_erstate(0);

delete {+ AVOID_FULL } from informix.grid_part_tab 
    where gp_servid = nodeid;
delete {+ AVOID_FULL } from informix.grid_cmd_tab
    where gcmd_source = nodeid;
delete {+ AVOID_FULL } from informix.grid_cmd_errors_tab
    where gerr_target = nodeid;
delete {+ AVOID_FULL } from informix.grid_cmd_ack_tab
    where gack_target = nodeid;
execute procedure informix.ifx_set_erstate(replstate);

end procedure;

create procedure informix.servdef_del(cdrid integer)
define replstate integer;
execute function informix.ifx_get_erstate() into replstate;
execute procedure informix.ifx_set_erstate(0);

delete {+ AVOID_FULL } from informix.grid_cdr_node_tab         
    where gn_id = cdrid;

execute procedure informix.ifx_set_erstate(replstate);

end procedure;

create trigger informix.servdef_trig1 delete on informix.servdef_tab
    referencing old as oldrow
    for each row
        (execute procedure informix.servdef_del(oldrow.servid));

create trigger informix.grid_cmd_trig1 insert on informix.grid_cmd_tab
    referencing new as newrow
    for each row
        (execute procedure
            informix.cdrcmd(107, newrow.gcmd_source, newrow.gcmd_stmtid));

create trigger informix.grid_cmd_trig2 delete on informix.grid_cmd_tab
    referencing old as oldrow
    for each row
        (execute procedure informix.grid_cmd_del(
                    oldrow.gcmd_source, oldrow.gcmd_stmtid));

create trigger informix.grid_cdr_cmd_trig1 delete on informix.grid_cdr_node_tab
    referencing old as oldrow
    for each row
        (execute procedure informix.grid_cdr_node_del(oldrow.gn_id));




create trigger informix.grid_cmd_redo1 insert on informix.grid_redo_tab
    referencing new as newrow
    for each row
        (execute procedure
            informix.cdrcmd(109, newrow.gredo_source, newrow.gredo_id));

create view informix.grid_redo (gredo_source, gredo_id, gredo_time, gredo_flags,
	    gredo_grid, gredo_source_list, gredo_target_list,
	    gredo_cmd_list)
	as select gredo_source, gredo_id, informix.timet_to_datetime(gredo_time),
    	gredo_flags, 
	    gredo_grid, gredo_source_list, gredo_target_list,
	    gredo_cmd_list
    from informix.grid_redo_tab;

revoke all on informix.grid_redo from public as informix;
grant select on informix.grid_redo to public as informix;

create table informix.grid_repl_tab (
    grep_gridid         integer,            { grid id }
    grep_source         integer,            { source node }
    grep_stmtseq        integer,            { stmtement identity }
    grep_replid         integer             { replicate ID }
    )   lock mode row;
alter table informix.grid_repl_tab 
    add constraint primary key (grep_source, grep_stmtseq);
revoke all on informix.grid_repl_tab from public as informix;
grant select on informix.grid_repl_tab to public as informix;

create table informix.grid_cm_nodes (
    grid_cm_cm_name     varchar(255),       { CM name }
    grid_cm_cm_node     varchar(255),       { node CM is monitoring }
    grid_cm_cm_host     varchar(255)
    )   with ERKEY lock mode row;
revoke all on informix.grid_cm_nodes from public as informix;
grant select on informix.grid_cm_nodes to public as informix;

create index informix.grid_cm_nodes_idx1 on informix.grid_cm_nodes
    (grid_cm_cm_name) in table;

create table informix.grid_cm_sla    (
    grid_cm_sla_cm_name varchar(255),       { CM name }
    grid_cm_sla_name    varchar(255),       { SLA NAME }
    grid_cm_sla_type    char(1),            { Type of SLA }
    grid_cm_sla_rule    lvarchar            { rule of this SLA }
    )   with ERKEY lock mode row;
revoke all on informix.grid_cm_sla from public as informix;
grant select on informix.grid_cm_sla to public as informix;

create index informix.grid_cm_sla_idx1 on informix.grid_cm_sla
    (grid_cm_sla_cm_name) in table;

create table informix.grid_cm_er_serv (
    grid_cm_er_name     varchar(255),       { CM name }
    grid_cm_er_sla_name varchar(255),       { SLA name }
    grid_cm_er_order    integer,            { order }
    grid_cm_er_server   varchar(255)        { server name }
    )   with ERKEY lock mode row;
revoke all on informix.grid_cm_er_serv from public as informix;
grant select on informix.grid_cm_er_serv to public as informix;

create index informix.grid_cm_er_serv1_idx1 on informix.grid_cm_er_serv
    (grid_cm_er_name) in table;

create table informix.grid_copy (
    gcpy_source     integer,                { source node }
    gcpy_stmtid     integer,                { statement identity }
    gcpy_gridid     integer,                { grid ID }
    gcpy_files      ifmx_er_extfile         { file to copy }
    )    lock mode row;

create unique index informix.grid_cpy_idx1 on informix.grid_copy (gcpy_source, gcpy_stmtid);
alter table informix.grid_copy add constraint primary key (gcpy_source, gcpy_stmtid);
revoke all on informix.grid_copy from public as informix;
grant select on informix.grid_copy to public as informix;

create table informix.grid_signal (
    gs_id           integer         primary key,
    gs_version      integer,
    gs_signal       integer,
    gs_data         char(2000)
    )   lock mode row;

insert into grid_signal values (1,0,0,"");
revoke all on informix.grid_signal from public as informix;
grant select on informix.grid_signal to public as informix;

create trigger informix.grid_signal1 update on informix.grid_signal
    referencing new as newrow
    for each row
        (execute procedure
            informix.cdrcmd(newrow.gs_signal, newrow.gs_data));

create trigger informix.grid_signal2 insert on informix.grid_signal
    referencing new as newrow
    for each row
        (execute procedure
            informix.cdrcmd(newrow.gs_signal, newrow.gs_data));


{ Tables, views, etc. for Quality of Data (qod) functionality }

{ quality of data version table }
create table informix.qod_version (
	version	integer
);
insert into informix.qod_version (version) values (1);
revoke all on informix.qod_version from public as informix;
grant select on informix.qod_version to public as informix;

{ qod control information }
create table informix.qod_control_tab (
	qod_master_node_id	 integer not null,  { master node }
	state                integer not null,  { state of Qod }
	when_last_turned_on  integer default 0, { in seconds }
	when_last_turned_off integer default 0, { in seconds }
	last_updated	     integer not null   { in seconds }
) with CRCOLS lock mode row;
alter table informix.qod_control_tab add constraint primary key(qod_master_node_id);
revoke all on informix.qod_control_tab from public as informix;
grant select on informix.qod_control_tab to public as informix;

{ this trigger fires when a new qod master node has been set }
create trigger informix.qod_control_tab_trig1
	insert on informix.qod_control_tab
	after (execute procedure informix.cdrcmd(106));

{ this trigger fires when the state of qod changes }
create trigger informix.qod_control_tab_trig2
	update on informix.qod_control_tab
	after (execute procedure informix.cdrcmd(106));

create view informix.qod_control (
		qod_master_node_id, state, when_last_turned_on, when_last_turned_off,
	    last_updated)
	as select 
		qod_master_node_id, state,
	    informix.timet_to_datetime(when_last_turned_on),
	    informix.timet_to_datetime(when_last_turned_off), 
	    informix.timet_to_datetime(last_updated)
	from informix.qod_control_tab;
revoke all on informix.qod_control from public as informix;
grant select on informix.qod_control to public as informix;

{ clock difference between qod master node and other node }
create table informix.qod_clock_differences_tab (
    masterid         integer not null, { Qod master node id }
	servid			 integer not null, { node that qod master is compared to }
	time_difference	 float   not null, { in centiseconds }
	num_measurements integer not null, { num of measurements taken }
	last_updated	 integer not null  { in seconds }
) lock mode row;
alter table informix.qod_clock_differences_tab 
        add constraint primary key(masterid, servid);
revoke all on informix.qod_clock_differences_tab from public as informix;
grant select on informix.qod_clock_differences_tab to public as informix;

create view informix.qod_clock_differences (
        masterid, servid, time_difference, num_measurements, last_updated)
	as select
        masterid, servid, time_difference, num_measurements,
		informix.timet_to_datetime(last_updated)
	from informix.qod_clock_differences_tab;
revoke all on informix.qod_clock_differences from public as informix;
grant select on informix.qod_clock_differences to public as informix;

{ replicate latency between other nodes and me }
create table informix.qod_replication_latency_tab (
	sourceid			integer not null, { source node }
	duration		    float   not null,  
		{ latency in centiseconds not adjusting for any clock difference }
	num_measurements 	integer not null, { number of measurements }
	last_updated	 	integer not null  { in seconds }
) lock mode row;
alter table informix.qod_replication_latency_tab add constraint 
	primary key(sourceid);
revoke all on informix.qod_replication_latency_tab from public as informix;
grant select on informix.qod_replication_latency_tab to public as informix;

create view informix.qod_replication_latency (
		sourceid, duration, num_measurements, last_updated)
	as select
		sourceid, duration,	num_measurements,
		informix.timet_to_datetime(last_updated)
	from informix.qod_replication_latency_tab;
revoke all on informix.qod_replication_latency from public as informix;
grant select on informix.qod_replication_latency to public as informix;

{ stores individual measurements of replication latency }
create table informix.qod_replicate_latency_measurements (
	sourceid			integer not null, { source node }
	targetid			integer not null, { target node }
	counter				integer not null, { unique measurement number }
	time_sent			float   not null,  
		{ when txn committed on source in centiseconds } 
	time_received		float   
		{ when tx committed on target in centiseconds }
) lock mode row;
alter table informix.qod_replicate_latency_measurements add constraint
	primary key (sourceid, targetid, counter);
revoke all on informix.qod_replicate_latency_measurements from public as informix;
grant select on informix.qod_replicate_latency_measurements to public as informix;

{ this trigger helps measure when a txn committed on the target node }
create procedure informix.getTime() returning float
	define curTime float;
    execute function cdrcmdfl(104) into curTime;
	return curTime;
	end procedure;
create trigger informix.qod_replicate_latency_measurements_trig1
  insert on informix.qod_replicate_latency_measurements 
  referencing new as new
  for each row when (new.targetid IN (select servid from sysmaster:syscdrs
									  where cnnstate = "L")) (
	execute procedure informix.getTime() INTO time_received
  );

{ records qod info about a given replicate-source node pair }
create table informix.qod_replicate_tab (
	repid					  integer not null, { replicate id }
	sourceid                  integer not null,	{ where failed txn originated }
	last_reset_time      	  integer default 0 not null, { when last reset }
        { time when a replicate was last cdr repaired or synced }
	first_failure_time        integer default 0 not null, 
		{ for the first txn with a row that failured to be applied the time }
	    { on the target at or after which the failure occurred }
	first_failure_rowcnt      integer default 0 not null
        { syscdrrecvqprog.total_failed_rows before first failure noticed }
) lock mode row;
create unique index informix.qod_replicate_tab_idx1 on informix.qod_replicate_tab 
    (repid, sourceid) in table;
alter table informix.qod_replicate_tab add constraint
	primary key(repid, sourceid);
revoke all on informix.qod_replicate_tab from public as informix;
grant select on informix.qod_replicate_tab to public as informix;

create view informix.qod_replicate (
		repid, sourceid, last_reset_time, first_failure_time, 
	    first_failure_rowcnt)
	as select
		repid, sourceid, informix.timet_to_datetime(last_reset_time),
 		informix.timet_to_datetime(first_failure_time),	first_failure_rowcnt
	from informix.qod_replicate_tab;
revoke all on informix.qod_replicate from public as informix;
grant select on informix.qod_replicate to public as informix;

create dba function informix.ifx_local2utf8(
    informix.lvarchar, informix.integer)
    returns informix.lvarchar
    with (HANDLESNULLS, PARALLELIZABLE, NOT VARIANT)
    external name '(cdrUTF8_cvt2UTF8)'
    language C;
grant execute on function informix.ifx_local2utf8(
    informix.lvarchar, integer)
    to public as informix;

create table informix.backgroundsync(
    logid           integer,
    logpos          integer,
    done_logid      integer,
    done_logpos     integer,
    type            integer,
    flags           integer,
    partnum         integer,
    start_page      integer,
    num_pages       integer,
    primary key (logid, logpos)) lock mode row;

revoke all on informix.backgroundsync from public as informix;
grant select on informix.backgroundsync to public as informix;

create table informix.timeseries_virtual_tables_tab (
    db          char(256),      { database wherein the VT resides }
	owner       char(64),       { owner of the virtual table }
	table       char(256)       { name  of the virtual table }
) lock mode row;
create unique index informix.timeseries_virtual_tables_tab_idx1 
    on informix.timeseries_virtual_tables_tab (table) 
    in table;
alter table informix.timeseries_virtual_tables_tab add constraint
	primary key(table);
revoke all on informix.timeseries_virtual_tables_tab from public as informix;
grant select on informix.timeseries_virtual_tables_tab to public as informix;

create view informix.timeseries_virtual_tables (
	    db, owner, table)
    as select cdrgcdisp(db), cdrgcdisp(owner), cdrgcdisp(table)
    from informix.timeseries_virtual_tables_tab;
revoke all on informix.timeseries_virtual_tables from public as informix;
grant select on informix.timeseries_virtual_tables to public as informix;

{ ************************************************************************ }
{   Objects needed to support shard replication                            }
{ ************************************************************************ }

create table informix.shard_tab (  
    shard_name         char(256),       { name of the shard }
    shard_version      integer,         { version of the shard }
    shard_id           integer,         { shard ID - same as replset }
    flags              integer,         { flags describing shard }
    db                 char(256),       { database name of sharded table }
    owner              char(64),        { owner name of sharded table }
    table              char(256),       { table name of sharded table }
    table_alias        char(256),       { Collection alias }
    shard_key          lvarchar,        { sharding key }
    shard_version_col  lvarchar         { column to identify  an update }
    ) lock mode row;

create unique index informix.shard_idx 
    on informix.shard_tab (shard_name, shard_version) in table;

create unique index informix.shard_idx2 
    on informix.shard_tab (shard_id, shard_version) in table;

alter table informix.shard_tab 
    add constraint primary key (shard_id, shard_version);

revoke all on informix.shard_tab from public as informix;
grant select on informix.shard_tab to public as informix;

create view informix.shard 
    (shard_name, shard_version, shard_id, flags, 
    db, owner, table, table_alias, shard_key, shard_version_col) 
    as select cdrgcdisp(shard_name), shard_version, 
    shard_id, flags, 
    cdrgcdisp(db), cdrgcdisp(owner), cdrgcdisp(table), cdrgcdisp(table_alias),
    cdrgcdisp(shard_key), cdrgcdisp(shard_version_col) 
    from informix.shard_tab;
revoke all on informix.shard from public as informix;
grant select on informix.shard to public as informix;

{ ------------------------------------------------------------------- }
{   Each target in the sharding schema has a replicate associated     }
{   with it.  All participants other than the target are defined as   }
{   send only nodes.                                                  }
{ ------------------------------------------------------------------- }
create table informix.shardpart_tab (  
    shard_id           integer,         { shard ID - match with shard_tab }
    shard_version      integer,         { version of shard }
    server             integer,         { CDRID of this node }        
    flags              integer,         { Shard Flags }
    replicate_id       integer,         { replicate id for this target } 
    key_values         lvarchar(30000)         { where clause of this target }
    ) lock mode row;

create unique index informix.shard_part_idx 
    on informix.shardpart_tab (shard_id, shard_version, server) in table;

alter table informix.shardpart_tab 
    add constraint primary key (shard_id, shard_version, server);

alter table informix.shardpart_tab 
    add constraint foreign key(shard_id, shard_version)
    references informix.shard_tab(shard_id, shard_version) 
    on delete cascade;  


revoke all on informix.shard_tab from public as informix;
grant select on informix.shard_tab to public as informix;

create view informix.shardpart 
    (shard_id, shard_version, server, flags, replicate_id, key_values ) 
    as select shard_id, shard_version, server, flags, replicate_id, 
    cdrgcdisp(key_values) from informix.shardpart_tab;

revoke all on informix.shardpart from public as informix;
grant select on informix.shardpart to public as informix;

create table informix.shard_sync  ( 
    state              char(1),         { state of the sync } 
    shard_id           integer,         { shard_id of the sync }
    shard_version      integer,         { version of the sync }
    master_node        integer,         { node on which command originated }
    needack            integer,         { ACKs from target nodes }
    complete_needack   integer          { ACKs indicating node has finished }
    ) lock mode row;

create index informix.shard_sync_shard_id_idx on informix.shard_sync
    (shard_id, shard_version) in table;
revoke all on informix.shard_sync from public as informix;
grant select on informix.shard_sync to public as informix;

{ *********************************************************************** }
{   Objects needed to manage events used initially for sharding           }
{ *********************************************************************** }

{   The cdr_events table is used to communicate between a specific node }
{   or to broadcast communication to all nodes.  There is a default     }
{   broadcast replicate which is passed to all targets.  The where      }
{   clause for the default replicate is "where to_node = 0 ".           }
{   Additionally if there is a need to communicate directly to a node   }
{   then there will be a distinct replicate created with a where clause }
{   of where to_node = <cdrid>                                          }

create table informix.cdr_events (
    to_node        integer,         { Target node }
    from_node      integer,         { Source node }
    event_num      integer,         { output of eventSequence.NEXTVAL }
    sequence_num   integer,         { sequence number for multipart messages }
    event_type     integer,         { event code }
    event_data     lvarchar(30000)  { payload }
    ) lock mode row;

revoke all on informix.cdr_events from public as informix;
grant select on informix.cdr_events to public as informix;

create unique index informix.cdr_event_idx 
 on informix.cdr_events (from_node, event_num, sequence_num);

alter table informix.cdr_events 
add constraint primary key (from_node, event_num, sequence_num);

create sequence informix.eventSequence cycle;
revoke all on informix.eventSequence from public as informix;
grant select on informix.eventSequence to public as informix;

{ cdr_even_sp passes the cdr_events row to ER for processing }
create dba procedure 
 informix.cdr_event_sp(informix.integer, informix.integer,
        informix.integer, informix.integer, informix.lvarchar) 
with (handlesnulls, parallelizable) 
external name '(cdr_event_sp)' 
 language c 
end procedure;

grant execute on procedure informix.cdr_event_sp(
informix.integer, 
 informix.integer, informix.integer, informix.integer, 
 informix.lvarchar) to public as informix;

create trigger informix.cdr_event_trig1 
 insert on informix.cdr_events 
 referencing new as newrow  
 for each row 
   (execute procedure 
     informix.cdr_event_sp(newrow.from_node, newrow.event_num, 
     newrow.sequence_num, newrow.event_type, newrow.event_data));


close database;