-- -- -- 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 = } 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;