--
--
--  Licensed Materials - Property of IBM and/or HCL
--
--  IBM Informix Dynamic Server
--  (c) Copyright IBM Corporation 1996, 2008 All rights reserved.
--  (c) Copyright HCL Technologies Ltd. 2017.  All Rights Reserved.
--
--
--
--   Title:       sysutils.sql
--
--   Description: create system utilities (sysutils) database tables
--
--

set lock mode to wait;

create database sysutils with log;
database sysutils exclusive;

{ onbar }

{ active OnLine servers }
{ The size of srv_name should be SVRNAME_LEN from asf.h }
{ The size of srv_node should = HOST_LEN from asf.h }
{ SVRNAME_LEN should equal IDENTSIZE and HOST_LEN should be 2 * IDENTSIZE }
create table bar_server
(
    srv_name    char(128) not null,    { server name }
    srv_node    char(256) not null,    { server node }

    primary key (srv_name)
) lock mode row;
revoke all on bar_server from public;
grant select on bar_server to public;
grant all on bar_server to root, informix;

{ backup objects }
{ object names are unique within an OnLine server }
{ The size of obj_srv_name and obj_name should = IDENTSIZE from rdsmac.h. }
{ The size of obj_type should = BAR_MAX_TYPE_LEN in bar.h }

{ DOIT obj_srv_name should be a referential constraint of bar_server:srv_name, }
{      but we presently don't need it.  Think about this more as high          }
{      availability and replication develop                                    }
create table bar_object
(
    obj_srv_name    char(128) not null, { which server object is from }
    obj_oid         serial not null,    { object identifier }
    obj_name        char(128) not null, { name of object    }
    obj_type        char(2) not null,   { type of object    }

    primary key (obj_oid)
) lock mode row;
create unique index bar_obj_idx on bar_object(obj_srv_name, obj_name);
revoke all on bar_object from public;
grant select on bar_object to public;
grant all on bar_object to root, informix;

{ backup or restore actions attempted against an object }
create table bar_action
(
    act_aid    serial not null,     { action identifier }
    act_oid    integer not null references bar_object(obj_oid),
                                    { object identifier }
    act_type   integer not null,    { type of action } 
    act_status integer default -1,  { status of action }
    act_start  datetime year to second default CURRENT year to second,
                                    { time action began }
    act_end    datetime year to second default CURRENT year to second,
                                    { time action ended }

    primary key (act_oid, act_aid)
) lock mode row;
create index bar_act_idx on bar_action(act_aid);
revoke all on bar_action from public;
grant select on bar_action to public;
grant all on bar_action to root, informix;

{ successful backup actions }
{ The size of ins_sm_name should = IDENTSIZE from rsparam.h.}
create table bar_instance 
(
    ins_aid    integer not null,   { action that created instance }
    ins_oid    integer not null,   { object identifier }
    ins_time   integer not null,   { global timestamp from server }
    rsam_time  integer,            { rsam timestamp from server        }
                                   { null in SQL is -MAXINT which is a }
                                   { valid rsam timestamp, so this is  }
                                   { nullable }
    seal_time        integer not null,  { time log file was sealed }
    prev_seal_time   integer not null,  { time previous log file was sealed }
    ins_level        integer default 0 not null, { backup level }
    ins_copyid_hi    integer not null,  { high bytes of the copyid }
    ins_copyid_lo    integer not null,  { low bytes of the copyid }
    ins_req_aid      integer not null,  {aid of required BU for an incremental}
    ins_logstream    integer not null,  { logstream needed for restore }
    ins_first_log    integer not null,  { first log needed for restore }
    ins_chpt_log     integer not null,  { log with archive checkpoint }
    ins_last_log     integer not null,  { log from restore }
    ins_partial      integer not null,  { partial flag from salvage }
    ins_sm_id        integer not null,  { storage manager id }
    ins_sm_name      char(128),         { storage manager name }
    ins_verify       integer default 0 not null, {verified? 0=F 1=T}
    ins_verify_date  datetime year to second, { date of verification }
    ins_backup_order integer default 0 not null, {dbspaces: filled by sequence}

    primary key (ins_oid, ins_aid),
    foreign key (ins_oid, ins_aid) references bar_action(act_oid, act_aid)
                on delete cascade
) lock mode row;
create index ins_aid_idx on bar_instance
    (ins_aid) using btree;
create index bar_inst_idx on bar_instance
    (ins_req_aid) using btree;
create index bar_inst_idx2 on bar_instance
    (ins_aid, ins_copyid_lo) using btree;
revoke all on bar_instance from public;
grant select on bar_instance to public;
grant all on bar_instance to root, informix;

create table bar_ordertab(dummy integer);
insert into bar_ordertab values(1);
create sequence bar_orderseq cycle;

grant connect to public;
grant dba to root, informix;
close database;