analytics/informix/etc/syscdrview.sql

--
--
--  Licensed Materials - Property of IBM and/or HCL
--
--  IBM Informix Dynamic Server
--  Copyright IBM Corporation 1996, 2011
--  (c) Copyright HCL Technologies Ltd. 2017.  All Rights Reserved.
--
--   Title:  syscdrview.sql
--   Setup sysmaster views for Enterprise Replication
--
--   N.B. all SQL commands should be in lower case only
--

database sysmaster; 

{ add flag text values for ER states }
insert into flags_text values ('cdrstate', 0, 'Catalog');
insert into flags_text values ('cdrstate', 1, 'Definition failed');
insert into flags_text values ('cdrstate', 2, 'Inactive');
insert into flags_text values ('cdrstate', 4, 'Active');
insert into flags_text values ('cdrstate', 8, 'Suspend');
insert into flags_text values ('cdrstate', 16, 'Pending Delete');
insert into flags_text values ('cdrstate', 32, 'Quiescent');

insert into flags_text values ('cdr_rsncjob_state', 1, 'Being Defined');
insert into flags_text values ('cdr_rsncjob_state', 2, 'Defined');
insert into flags_text values ('cdr_rsncjob_state', 4, 'Definition failed');
insert into flags_text values ('cdr_rsncjob_state', 8, 'Running');
insert into flags_text values ('cdr_rsncjob_state', 16, 'Being Stopped');
insert into flags_text values ('cdr_rsncjob_state', 32, 'Stopped');
insert into flags_text values ('cdr_rsncjob_state', 64, 'Pending Completion');
insert into flags_text values ('cdr_rsncjob_state', 128, 'Completed');
insert into flags_text values ('cdr_rsncjob_state', 256, 'Pending Delete');
insert into flags_text values ('cdr_rsncjob_state', 512, 'Being Aborted');
insert into flags_text values ('cdr_rsncjob_state', 1024, 'Aborted');

create view informix.syscdrhost (servid, name, url)
    as select
       h.servid, cdrgcdisp(h.name), 
       cdrgcdisp(p.protocol) || ':' || cdrgcdisp(p.address)
    from syscdr:informix.hostdef_tab h, outer syscdr:informix.protodef_tab p
    where h.servid = p.servid;
grant select on informix.syscdrhost to public as informix;

create view informix.syscdrserver (
    servid,  servername, connstate, connstatechange, 
    servstate, ishub, isleaf, rootserverid, forwardnodeid,
    idletimeout, atsdir, risdir)
    as select   
        m.servid, m.servname, m.cnnstate, m.cnnstatechg,
        f.txt, m.ishub, m.isleaf, m.rootserverid, m.forwardnodeid,
        a.idletimeout, cdrgcdisp(a.atsdir), cdrgcdisp(a.risdir) 
    from informix.syscdrs m, informix.flags_text f, syscdr:informix.servdef_tab a
    where a.servid = m.servid and 
          f.tabname = 'cdrstate' and
          f.flags = a.servstate;

grant select on informix.syscdrserver to public as informix;

create view informix.syscdrerror (
        errornum, errorserv, errorseqnum, errortime,
        sendserv, reviewed, errorstmnt)
    as select   
        error_number, b.servname, remote_seqnum, timestamp,
        c.servname, errreviewed, error_desc
    from syscdr:informix.cdr_errors a, outer informix.syscdrs b, outer informix.syscdrs c
    where a.source_id = b.servid and a.ctrl_id = c.servid;
grant select on informix.syscdrerror to public as informix;

create view informix.syscdrreplset (replsetname, replname, replsetattr)
    as select cdrgcdisp(a.replsetname), cdrgcdisp(c.repname), a.replsetattr
       from syscdr:informix.replsetdef_tab a, OUTER( syscdr:informix.replsetpartdef b,
            syscdr:informix.repdef_tab c )
       where a.replsetid = b.replsetid and
             b.repid = c.repid;
grant select on informix.syscdrreplset to public as informix;

{ Stored proced for returning 'Y' for true and 'N' for false }
create procedure informix.bityesno ( bitset int, bitmask int) returning char;
    if (bitval(bitset, bitmask) <> 0) then return 'Y';
    else return 'N';
    end if;
end procedure;
grant execute on informix.bityesno to public as informix;

{ Stored proced for returning 'N', 'C', or 'I' according to whether the }
{ floating point flags specify Native, Canonical, or IEEE transport }
create procedure informix.replfloattype ( flags int) returning char;
    if (bitval(flags, '0x2') <> 0) then return 'C';
    else 
        if (bitval(flags, '0x40000000') <> 0) then return 'I';
        else return 'N';
        end if;
    end if;
end procedure;
grant execute on informix.replfloattype to public as informix;

create procedure informix.bitrow (flags int) returning char;
    if (bitval(flags, '0x100') <> 0) then return 'T';
    else return 'R';
    end if;
end procedure;
grant execute on informix.bitrow to public as informix;

{ Stored proced for returning replicate name given replid }
create procedure informix.replid2Name (replicateid int) returning char(256);
    define replicatename char(256);
    if (replicateid <> 0) 
    then
    select cdrgcdisp(repname) into replicatename
    from syscdr:repdef_tab a 
    where a.repid = replicateid; 
    return replicatename;
    else
    return NULL;
    end if;
end procedure;
grant execute on informix.replid2Name to public as informix;

create view informix.syscdrrepl (
    replname,  replstate, replid,
    freqtype, freqmin, freqhour, freqday, 
    scope, invokerowspool, invoketranspool,
    primresolution, secresolution, storedprocname,
    ismasterrepl, isshadowrepl, shadowparentrepl,
    floattype, istriggerfire, isfullrow, isgrid)
    as select cdrgcdisp(repname),  f.txt, a.repid,
        freqtype, min, hour, day,
        bitrow(a.flags), bityesno(a.flags,'0x400'), bityesno(a.flags,'0x800'),
        cr_primary, cr_secondary, cdrgcdisp(cr_spname),
        bityesno(a.flags,'0x800000'), bityesno(a.flags,'0x2000000'),
        replid2Name(a.primaryrepid), replfloattype(a.flags),
        bityesno(a.flags,'0x1000'), bityesno(a.flags,'0x10000000'),
        bityesno(a.flags,'0x00100000')
    from syscdr:informix.repdef_tab a,
        outer syscdr:informix.freqdef b,
        flags_text f
    where a.repid = b.repid 
        and f.tabname = 'cdrstate' and f.flags = a.repstate;
grant select on informix.syscdrrepl to public as informix;

create view informix.syscdrpart (
        replname, servername, partstate, partmode,
        usetabowner, dbsname, owner, tabname, pendingsync
        ) 
    as select
        cdrgcdisp(b.repname), c.servname, f.txt, a.partmode,
        bitval(a.flags, '0x04'),
        cdrgcdisp(a.db), cdrgcdisp(a.owner), cdrgcdisp(a.table),
        bitval(a.flags, '0x040')
    from syscdr:informix.partdef_tab a, syscdr:informix.repdef_tab b, 
         syscdrs c, outer flags_text f
    where b.repid = a.repid and c.servid = a.servid
       and f.tabname = 'cdrstate' and f.flags = a.partstate;
grant select on informix.syscdrpart to public as informix;

create view informix.syscdrqueued (servername, name, bytesqueued)
     as select     srvname, replname, SUM(bytesqued)
     from  syscdrq group by srvname, replname;
grant select on informix.syscdrqueued to public as informix;

create view informix.syscdrtxproc ( 
    servername, txprocessed, txcommitted, txaborted, 
    rowscommitted, rowsaborted, txbadcnt)
    as select 
    srvname, txprocssd, txcmmtd, txabrtd, 
    rowscmmtd, rowsabrtd, txbadcnt 
    from  informix.syscdrtx;
grant select on informix.syscdrtxproc to public as informix;

close database;