{**************************************************************************}
{*                                                                        *}
{*  Licensed Materials - Property of IBM and/or HCL                                  *}
{*                                                                        *}
{*  IBM Informix Dynamic Server                                           *}
{*  Copyright IBM Corporation 2001, 2013                                  *}
{*  (c) Copyright HCL Technologies Ltd. 2017.  All Rights Reserved.                                  *}
{*                                                                        *}
{**************************************************************************}
{									    }
{   Title:	sysmaster.sql						    }
{   Description:							    }
{		create sysmaster database and SMI tables		    }
{									    }
{  NOTE: Ensure that any changes in the schema of the "sysmaster" database  }
{	 OR changes in the corresponding shared memory structure  defns	    }
{	 are reflected *appropriately* in ALL the files below:		    }
{		rsam/sysmaster.sql.IUS,					    }
{		rsam/rsmem.h,						    }
{		rsam/rspseudo.h and					    }
{		rsam/rspseudo.c						    }
{									    }
{ ************************************************************************* }

{ Create Pseudo Tables }

set lock mode to wait;

create database sysmaster with log;

database sysmaster exclusive;
set environment delimident off;

{ databases }
    create table informix.sysdbspartn
	(
	partnum 	integer,	{ table id for systables	}
	created 	integer,	{ date created			}
	owner 		char(32),	{ user name of creator		}
	name 		char(128),	{ database name			}
	flags 		smallint	{ flags indicating logging	}
	);
    create unique index informix.sysdbs_nameix on sysdbspartn(name);
    revoke all on informix.sysdbspartn from public as informix;
    grant select on informix.sysdbspartn to public as informix;

{ join for partnums to table names }
    create table informix.systabnames
	(
	partnum		integer,	{ table id for table		}
	dbsname		char(128),	{ database name			}
	owner		char(32),	{ table owner			}
	tabname		char(128),	{ table name			}
	collate         char(32),       { collation assoc with database }
	dbsnum 		integer 	{ dbspace number		}
	);
    create unique index informix.systabs_pnix on systabnames(partnum);
    create index informix.systabs_pnix2 on systabnames(dbsnum);
    revoke all on informix.systabnames from public as informix;
    grant select on informix.systabnames to public as informix;

{ Raw Disk }
    create table informix.sysrawdsk		{ Internal Use Only		}
	(
	pg_chunk  	integer,	{ physical page address	- chunk	}
	pg_offset	integer,	{ physical page address - offset}
	offset   	smallint,	{ bytes into page		}
	loc		char(23),	{ location representation	}
	hexdata     	char(40),	{ 16 bytes hexdumped from offset}
	ascdata		char(16)	{ 16 bytes ascii-dumped       	}
	);
    create unique index informix.sysrawdskidx on sysrawdsk (pg_chunk, pg_offset, offset);
    revoke all on informix.sysrawdsk from public as informix;

{ Page Headers }
    create table informix.syspaghdr
	(
	pg_partnum 	integer,	{ partition number of page	}
	pg_pagenum 	integer,	{ logical page number in partn	}
	pg_chunk 	integer,	{ pg_chunk - calculated value	}
	pg_offset 	integer,	{ pg_offset - calculated value	}
	pg_stamp	integer,	{ pg_stamp			}
	pg_chksum	smallint,	{ pg_chksum			}
	pg_pagesize	smallint,	{ pg_pagesize			}
	pg_nslots	smallint,	{ pg_nslots			}
	pg_flags	smallint,	{ pg_flags			}
	pg_frptr	smallint,	{ pg_frptr			}
	pg_frcnt	smallint,	{ pg_frcnt			}
	pg_next		integer,	{ pg_pgnext			}
	pg_prev		integer,	{ pg_pgprev			}
	pg_chunk_disk	integer,	{ pg_chunk - value from disk	}
	pg_offset_disk	integer		{ pg_offset - value from disk	}
	);
    create unique index informix.syspaghdridx on syspaghdr (pg_partnum, pg_pagenum);
    create unique index informix.syspaghdridx_idx2 on syspaghdr (pg_chunk, pg_offset);
    revoke all on informix.syspaghdr from public as informix;
    grant select on informix.syspaghdr to public as informix;

{ Slot Tables }
    create table informix.sysslttab		{ Internal Use Only		}
	(
	partnum 	integer,	{ partition number of page	}
	pagenum 	integer,	{ logical page number in partn	}
	slotnum 	smallint,	{ slot number on page		}
	slotptr 	smallint,	{ slot pointer			}
	slotlen 	smallint,	{ slot length			}
	slotflg 	smallint	{ slot flag			}
	);
    create unique index informix.sysslttabidx on sysslttab (partnum, pagenum, slotnum);
    revoke all on informix.sysslttab from public as informix;
    grant select on informix.sysslttab to public as informix;

{ Slot Data }
    create table informix.syssltdat		{ Internal Use Only		}
	(
	partnum 	integer,	{ partition number of page	}
	pagenum 	integer,	{ logical page number in partn	}
	slotnum 	smallint,	{ slot number on page		}
	slotoff 	smallint,	{ slot offset			}
	loc     	char(28),	{ location representation	}
	hexdata    	char(40),	{ 16 bytes hexdumped from offset}
	ascdata    	char(16)	{ 16 bytes ascii dumped         }
	);
    create unique index informix.syssltdatidx on 
	syssltdat (partnum, pagenum, slotnum, slotoff);
    revoke all on informix.syssltdat from public as informix;

{ Chunk Free List }
    create table informix.syschfree
	(
	chknum  	integer,	{ chunk number 			}
	extnum  	integer,	{ extent number	in chunk	}
	start   	integer,	{ physical addr of start	}
	leng    	integer		{ length of extent		}
	);
    create unique index informix.syschfreeidx on syschfree (chknum, extnum);
    revoke all on informix.syschfree from public as informix;
    grant select on informix.syschfree to public as informix;

{ Symbol Table }
    create table informix.syssymtab
	(
        location	integer,	{ index into ex_symtable	}
        address		int8,		{ symbol address		}
        last_address	int8,		{ last address in symbol	}
	size		integer,	{ size to next symbol		}
	name		char(40),	{ symbol name			}
	extra		integer		{ symbol extra			}
	);
    revoke all on informix.syssymtab from public as informix;
    grant select on informix.syssymtab to public as informix;

{ Profiling Data }
    create table informix.sysproftab
	(
	vpid		integer,	{ VP id				}
	index		integer,	{ histogram index		}
	address		int8,		{ histogram address		}
	count		integer		{ histogram count		}
	);
    revoke all on informix.sysproftab from public as informix;
    grant select on informix.sysproftab to public as informix;

{ Partition Headers }
    create table informix.sysptnhdr
	(
	partnum 	integer,	{ table's partnum                   }
	flags   	integer,	{ partition flags		    }
	rowsize 	integer,	{ rowsize (max for variable)	    }
	ncols   	smallint,	{ number of varchar or blob columns }
	nkeys   	smallint,	{ number of indexes                 }
	nextns  	smallint,	{ number of extents		    }
	pagesize	smallint,	{ page size			    }
	created 	integer,	{ date created			    }
	serialv 	integer,	{ current serial value		    }
	fextsiz 	integer,	{ first extent size ( in pages )    }
	nextsiz 	integer,	{ next extent size ( in pages )     }
	nptotal 	integer,	{ number of pages allocated	    }
	npused  	integer,	{ number of pages used		    }
	npdata  	integer,	{ number of data pages		    }
	octptnm         integer,	{ OCT partnum (optical blobs only)  }
        lockid          integer,        { table lock id			    }
	nrows	        bigint,		{ number of data rows		    }
	ninserts	bigint,		{ # inserts 			    }
	nupdates	bigint,		{ # updates 			    }
	ndeletes	bigint,		{ # deletes 			    }
        cur_serial8     int8,           { Current serial8 value             }
        cur_bigserial   bigint,         { Current bigserial value           }
	dbsnum	        integer, 	{ dbspace number of partition       }
        pta_oldvers     smallint,       { Inplace alter                     }
        pta_newvers     smallint,       { Inplace alter                     }
        pta_bmpagenum   integer,        { Inplace alter                     }
        pta_totpgs      integer,        { Inplace alter                     }
        pta_opems_allocd  integer,      { Inplace alter                     }
        pta_opems_filled  integer,      { Inplace alter                     }
        glscollname     char(32),       { gls locale                        }
	flags2   	integer,	{ partition flags2		    }
	sid		integer
	);
    create unique index informix.sysptnhdridx on sysptnhdr (partnum);
    create index informix.sysptnhdridx2 on sysptnhdr (dbsnum);
    revoke all on informix.sysptnhdr from public as informix;
    grant select on informix.sysptnhdr to public as informix;

{ Partition Key Descriptions }
    create table informix.sysptnkey		{ Internal Use Only		}
	(
	partnum 	integer,	{ partnum for partition		}
	keynum  	smallint,	{ keynumber for key		}
	flags		smallint,	{ key flags			}
	rootnode 	int8,		{ logical pagenum for root node	}
	nparts		smallint,	{ number of parts in key	}
	keylen		smallint,	{ key length			}
	kpartno		smallint,	{ partno for this part		}
	kpstart		smallint,	{ offset into row of the part	}
	kpleng		smallint,	{ length of this part		}
	kptype		smallint	{ type of this part 		}
	);
    create unique index informix.sysptnkeyidx on sysptnkey (partnum, keynum, kpartno);
    revoke all on informix.sysptnkey from public as informix;
    grant select on informix.sysptnkey to public as informix;

{ Partition Extent Descriptions }
    create table informix.sysptnext		{ Internal Use Only		}
	(
	pe_partnum 	integer,	{ partnum for this partition	}
	pe_extnum  	smallint,	{ extent number			}
	pe_chunk	integer,	{ chunk number for this extent	}
	pe_offset	integer,	{ chunk offset for this extent	}
	pe_size 	integer,	{ size of this extent		}
	pe_log  	integer		{ logical page for start	}
	);
    create unique index informix.sysptnextidx on sysptnext (pe_partnum, pe_extnum);
    revoke all on informix.sysptnext from public as informix;
    grant select on informix.sysptnext to public as informix;

{ Partition Column Descriptions }
    create table informix.sysptncol		{ Internal Use Only		}
	(
	partnum 	integer,	{ partnum for this partition	}
	colnum  	smallint,	{ column number			}
	coloff  	smallint,	{ offset into row		}
	colblob 	smallint,	{ blobspace num if blob 	}
	colsize 	smallint,	{ column size			}
	colflags 	smallint,	{ flags for column		}
	coltype 	smallint	{ data type of column		}
	);
    create unique index informix.sysptncolidx on sysptncol (partnum, colnum);
    revoke all on informix.sysptncol from public as informix;
    grant select on informix.sysptncol to public as informix;

{ Partition Bit Maps }
    create table informix.sysptnbit
	(
	pb_partnum 	integer,	{ partnum for this partition 	}
	pb_pagenum 	integer,	{ logical pagenum represented	}
	pb_bitmap  	integer		{ bitmap value for page		}
	);
    create unique index informix.sysptnbitidx on sysptnbit (pb_partnum, pb_pagenum);
    revoke all on informix.sysptnbit from public as informix;
    grant select on informix.sysptnbit to public as informix;

{ Active Partition Headers }
    create table informix.sysactptnhdr
        (
        address         int8,
        partnum         integer,        { table's partnum                   }
        pn_flags        integer,        { partition flags                   }
        rowsize         smallint,       { rowsize (max for variable)        }
        ncols           smallint,       { number of varchar or blob columns }
        nkeys           smallint,       { number of indexes                 }
        nextns          smallint,       { number of extents                 }
        pagesize        smallint,       { page size                         }
        created         integer,        { date created                      }
        cur_serial4     integer,        { current serial value              }
	cur_bigserial	bigint,		{ current bigserial value	    }
        cur_serial8     int8,           { current serial value              }
        fextsiz         integer,        { first extent size ( in pages )    }
        nextsiz         integer,        { next extent size ( in pages )     }
        nptotal         integer,        { number of pages allocated         }
        npused          integer,        { number of pages used              }
        npdata          integer,        { number of data pages              }
        lockid          integer,        { table lock id                     }
        nrows           bigint,		{ number of data rows               }
	ninserts	bigint,		{ # inserts 			    }
	nupdates	bigint,		{ # updates 			    }
	ndeletes	bigint,		{ # deletes 			    }
        flags           integer,
        ucount          integer,
        chunk           integer,
        offset          integer,
        lastidxpn       integer,
        nextns1         integer,
        nextns2         integer,
        badkeys         integer,
        altstmp         integer,
        ocount          integer,
        skstamp         integer,
        pta_oldvers     smallint,
        pta_newvers     smallint,
        pta_bmpagenum   integer,
        pta_totpgs      integer,
        pta_opems_allocd  integer,
        pta_opems_filled  integer,
        glscollname     char(32),
        pf_rqlock       bigint,
        pf_wtlock       bigint,
        pf_deadlk       bigint,
        pf_dltouts      bigint,
        pf_isread       bigint,
        pf_iswrite      bigint,
        pf_isrwrite     bigint,
        pf_isdelete     bigint,
        pf_bfcread      bigint,
        pf_bfcwrite     bigint,
        pf_seqscans     bigint,
        pf_dskreads     bigint,
        pf_dskwrites    bigint,
	pn_flags2       integer        { partition flags2                  }
        );
    create unique index informix.sysactptnhdridx on sysactptnhdr (partnum);
    revoke all on informix.sysactptnhdr from public as informix;
    grant select on informix.sysactptnhdr to public as informix;

{ rsam thread control blocks }
    create table informix.sysrstcb		{ Internal Use Only		}
	(
	indx	 	integer,	{ index into rstcb table (rs_number) }
	address	 	int8,		{ addr of rstcb structure	}
	txp	 	int8,		{ addr of txp			}
	localtxp 	int8,		{ addr of local txp (XA only)	}
        tmptxp          int8,       	 { addr of temp file txp         }
        savetxp         int8,
	next		int8,		{ addr of next on active or free list }
        tmpdepth        integer,        { depth of temp file ops        }
        rmid            integer,        { XA Resource Manager ID        }
        xrecvrpos       smallint,
	iserrno	 	integer,	{ rsam errno number		}
	isrecnum 	integer,	{ current rowid			}
	isfragnum 	integer,	{ current fragment number	}
	flags	 	integer,	{ flags for rstcb		}
	uid	 	integer,	{ user id			}
	asyncerr 	int8,		{ address of async error 	}
	username	char(32),	{ user name			}
	timeout  	smallint,	{ lock timeout counter		}
	nxtthread	int8,		{ addr of next if >1 threads	}
	sid		integer,	{ session id			}
	scb		int8,		{ addr of scb			}
	ostcb		int8,		{ addr of ostcb			}
	gentcb		int8,		{ addr of gentcb		}
	tid		integer,	{ thread id			}
        join            smallint,       { thread will join others       }
	mttcb		int8,		{ addr of mt thread cb		}
	aio		int8,		{ addr of aio request struct	}
	mirror_aio	int8,		{ addr of mirror req struct	}
	lkwait	 	int8,		{ waiting for this lock		}
        lkwttype        integer,        { lock type waiting for         }
	bfwait	 	int8,		{ waiting for this buffer	}
	bfwtflag	smallint,	{ buffer wait type flag		}
	txwait		int8,		{ waiting for this transaction	}
	txsusp		int8,		{ suspended transaction		}
	wtlist	 	int8,		{ addr of next waiter on lock	}
	wtthrlist 	int8,		{ addr of next thread in trans  }
	bflist	 	int8,		{ addr of next waiter on buff	}
	txlist		int8,		{ addr of next waiter on tx	}
        xamts           smallint,       { flag to run as MTS/DTC        }
	lbufwake	int8,		{ addr of log buffer for wake up}
	nreads	 	integer,	{ number of reads		}
	nwrites	 	integer,	{ number of writes		}
	tolist		int8,		{ addr of next in timeout list	}
	nopens		integer,	{ size of open table		}
	nfiles		integer,	{ size of file table		}
	opentab		int8,		{ open table			}
        opfree          int8,
        nextopen        int8,
	filetab		int8,		{ file table open files		}
        hfiles          int8,		{ hash into file table		}
        flfree          int8,		{ file table free descriptors	}
        nfilesopen      int8,		{ num of file table open files	}
	tmprow		int8,		{ temp space for row		}
	tmpsize		integer,	{ size of tmprow space		}
	cmprow		int8,		{ temp space for compressed row	}
	rcmprow		int8,		{ temp space for reading cmprow	}
	rowupdatep	int8,		{ address of row update info **	}
	isbisfd		integer,	{ isfd for isb* routines	}
	blobptr		int8,		{ ptr to current open blob	}
	blobrarea	int8,		{ blobpage buffer read area	}
	blobrszbuf	integer,	{ size of blob buffer read area	}
	blobrbufp	int8,		{ addr of blob buffer read	}
	blobwarea	int8,		{ blobpage buffer write area	}
	blobwszbuf	integer,	{ size of blob buffer write area}
	blobwbufp	int8,		{ addr of blob buffer write	}
	blobcarea	int8,		{ blobpage buffer copy area	}
	blobcszbuf	integer,	{ size of blob buffer copy area	}
	blobcbufp	int8,		{ addr of blob buffer copy	}
	blobpiecesp	int8,		{ addr of blobpieces struct **	}
        affp            int8,
        afid            int8,
        afcnt           smallint,
        udrcbcnt        smallint,
	operrno		integer,	{ error in optical subsystem	}
	dolinkchk	integer,	{ do link checks in btchknode	}
	nnode		integer,	{ next node for link check	}
	pnode		integer,	{ prev node for link check	}
	rsamdebug	integer,	{ do rsam debugging checks	}
	lastlock	integer,	{ last lock granted		}
	bufferlogging	integer,	{ do buffered logging		}
        abcpytab	int8,		{ auxiliary bcpy table          }
	abcpytabmult	integer,	{ size multiple for aux bcpy tab}
        logbuff		int8,		{ normal log buffer             }
        logbuff_beg     int8,
	undologbuff	int8,		{ undo log buffer		}
        tmplogrec       int8,
	btp		int8,		{ addr of current bt struct **	}
        rkeysfree       int8,
        turbonum        integer,
	pnfd_count      integer,        { num rsfd's for rollfwd/rollbk }
	svptnum		integer,	{ savepoint number		}
	numsorts	integer,	{ number of open sorts allowed	}
	srttab		int8,		{ addr of sort table		}
	srttmpdir	int8,		{ ptr to sort temp dir pathname	}
	srtfileid	integer,	{ file id for sort file		}
	privdata	int8,		{ generic pointer to private mem}
        precnum         integer,
	rootaddr	integer,	{ root node of idx being built	}
	relocking	integer,	{ relock on recovery		}
        pitstop         int8,
        pitaction       integer,
        debugerrno      integer,
        bfheld_count    integer,
        logbu           int8,
        arcbu           int8,
        physrecvr       int8,
        logrecvr        int8,
        recvryflag      integer,
        logs            int8,
        auditp          int8,
        nexttrace       integer,
        ntraces         integer,
        traces          int8,
        trflags         integer,
        opsubbuf        int8,
        opbuf           int8,
        opbufsize       integer,
        blobtxtabp      int8,
        nblobtxs        integer,
        maxnbtxs        integer,
        onut_bufflist   int8,
        onut_cb         int8,
	upf_rqlock	bigint,	{ number of locks requested	}
	upf_wtlock	bigint,	{ ... lock waits		}
	upf_deadlk	bigint,	{ ... deadlocks detected	}
	upf_lktouts	bigint,	{ ... lock timeouts		}
	upf_lgrecs	bigint,	{ ... log records written	}
	upf_isread	bigint,	{ ... reads			}
	upf_iswrite	bigint,	{ ... writes			}
	upf_isrwrite	bigint,	{ ... rewrites			}
	upf_isdelete	bigint,	{ ... deletes			}
	upf_iscommit	bigint,	{ ... commits			}
	upf_isrollback	bigint,	{ ... rollbacks			}
	upf_longtxs	bigint,	{ ... long transactions		}
	upf_bufreads	bigint,	{ ... buffer reads		}
	upf_bufwrites	bigint,	{ ... buffer writes		}
	upf_logspuse	bigint,	{ % log space currently used	}
	upf_logspmax	bigint,	{ max % of logspace ever used	}
	upf_seqscans	bigint,	{ number of sequential scans	}
        upf_totsorts    bigint,
        upf_dsksorts    bigint,
        upf_srtspmax    bigint,
	nlocks		integer,	{ number of locks currently held}
        lktout          smallint,       { lock timeout counter          }
        lkthreadlist    int8,           { next thread in same tx wtg    }
        lkwaittime      float,          { time spent waiting on locks }
        iowaittime      float,          { time spent waiting on disk io }
        upf_niowaits    integer,        { Number of disk IO waits }
        upf_idxbufreads integer         { Number of index buffer reads }
	);
    create unique index informix.sysrstcbidx  on sysrstcb (indx);
    create unique index informix.sysrstcbaddr on sysrstcb (address);
    --Note that sysrstcbscb will only be used for equality lookups
    create unique index informix.sysrstcbscb on sysrstcb (scb) hash on (scb) with 2 buckets;
    revoke all on informix.sysrstcb from public as informix;
    grant select on informix.sysrstcb to public as informix;

{ Transactions }
    create table informix.systxptab		{ Internal Use Only		}
	(
	indx		integer,	{ index into transaction table	}
	address		int8,		{ address of transaction struct	}
	latchp		int8,		{ address of transaction latch	}
	next		int8,		{ addr of next on active or free list }
	gtridp		int8,		{ address of gtrid		}
	txid		integer,	{ id of transaction		}
	flags		integer,	{ transaction flags		}
	desc		integer,	{ flags describing the transaction }
	logbeg		integer,	{ loguniq containing BEGIN	}
	logbeg_pos	integer,	{ logical log begin work logpos	}
	loguniq		integer,	{ loguniq of last record	}
	logpos		integer,	{ logpos of last record		}
	dlklist 	int8,		{ used for deadlk detection	}
	deadflag 	smallint,	{ flag for deadlock detection 	}
	mgmquery	int8,		{ tx_query			}
	lkwaitcnt	smallint,	{ # of threads waiting on locks }
	lklist		int8,		{ list of locks held		}
	lklatchp	int8,		{ addr of private lock latch **	}
	owner		int8,		{ addr of owner (rstcb_t *)	}
	wtlist		int8,		{ users waiting for this tx	}
	ptlist		int8,		{ list of partitions dropped	}
	nlocks		integer,	{ number of locks held		}
	lkwait		smallint,	{ lock wait timeout		}
	splevel		smallint,	{ savepoint level		}
	isolevel	smallint,	{ isolation level		}
	locktablep	int8,		{ addr of table locks table **	}
	svptcnt		integer,	{ number of savepoints alloc'ed }
	svuniqp		int8,		{ addr of savepoint loguniqs **	}
	svposp		int8,		{ addr of savepoint logpos's **	}
	longtx		smallint,	{ this is long transaction	}
	nsusp		smallint,	{ number of suspends for tx	}
	stamp		integer,	{ activity time stamp		}
	istar_coord	char(128),	{ istar coordinator		}
	sblock		int8,		{ ptr to dynamic shm block	}
	sqlptr		int8,		{ ptr to first ismalloc piece	}
        nremotes        smallint,       { # of rem thread activations   }  
        begstamp        integer,        { stamp at time of BEGIN WORK   }  
        mts_ref_count   integer,        { Reference count for MTS       }  
	mts_tbranch	int8,		{ List of prepared GTRIDS for MTS }
	rb_time		char(20) 	{ Estimated rollback time	 }
	);
    create unique index informix.systxpidx  on systxptab (indx);
    create unique index informix.systxpaddr on systxptab (address);
    revoke all on informix.systxptab from public as informix;
    grant select on informix.systxptab to public as informix;

{ XA Transactions }
    create table informix.sysxatab		{ Internal Use Only		}
	(
	indx		integer,	{ index into transaction table	}
	address		int8,		{ address of transaction struct	}
	tx_flags	integer,	{ transaction flags		}
	tb_flags	integer,	{ transaction branch flags	}
	isolevel	smallint,	{ isolation level		}
        timeout         smallint,       { timeout 			}  
        format_id       integer,        { format ID 			}  
        gtridlen        integer,        { GTRID length 			}  
        bquallen        integer,        
        data            char(256)    	
	);

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

{ Locks }
    create table informix.syslcktab		{ Internal Use Only		}
	(
	indx		integer,	{ index into lock table		}
	address	        int8,		{ addr of lock structure	}
	hash		int8,		{ addr of next in hash bucket	}
	same		int8,		{ list of same locks		}
	wtlist	        int8,		{ list of waiters (rstcb *)	}
	owner	        int8,		{ owner of lock	(rtx *)		}
	list	        int8,		{ list of owner's locks		}
	type		smallint,	{ type of lock		 	}
	flags	        smallint,	{ lock flags			}
	bsize	        smallint,	{ size of bytes key		}
	keynum	        smallint,	{ keynum of item lock		}
	rowidr	        integer,	{ real rowid if key lock	}
	partnum	        integer,	{ partnum lock is on            }
	rowidn	        integer,	{ key value locked 		}
        dipnum          integer,        { pagenum if deleted item       }
        grtime          integer         { time lock was granted         }
	);
    create unique index informix.syslckidx  on syslcktab (indx);
    create unique index informix.syslckaddr on syslcktab (address);
    revoke all on informix.syslcktab from public as informix;
    grant select on informix.syslcktab to public as informix;

{ Buffer Pools }
    create table informix.sysbufpool
        (
	indx		integer,	{ index into buffer pool list }
	address		int8,		{ address of buffer pool structure }
	bufsize		integer,	{ buffer page size in bytes }
	nbuffs		integer,	{ number of buffers in buffer pool  }
	buff_header	int8,		{ address of buffer headers }
	nlrus		smallint,	{ number of LRUs }
	mindirty	float,		{ pct of dirty pages cleaning stops }
	maxdirty	float,		{ pct of dirty pages cleaning starts }
	dskreads	int8,		{ number of physical block reads }
	pagreads	int8,		{ number of pages read from disk }
	bufreads	int8,		{ number of buffer cache reads }
	dskwrites	int8,		{ number of physical block writes }
	pagwrites	int8,		{ number of pages written to disk }
	bufwrites	int8,		{ number of buffer cache writes }
	bufwrites_sinceckpt int8,	{ number of buffer cache writes since last checkpoint }
	bufwaits	int8,		{ number of buffer waits }
	ovbuff		int8,		{ number of buffer pool overflows }
	flushes		int8,		{ number of buffer flush calls }
	fgwrites	int8,		{ number of foreground writes }
	lruwrites	int8,		{ number of lru writes }
	chunkwrites	int8,		{ number of chunk writes }
	lru_time_total	float,		{ time spent cleaning LRUs }
	lru_calls	int8		{ number of times LRU cleaning invoked }
        );
    create unique index informix.sysbfpoolidx  on sysbufpool (indx);
    revoke all on informix.sysbufpool from public as informix;
    grant select on informix.sysbufpool to public as informix;

{ Buffer Headers }
    create table informix.sysbufhdr		{ Internal Use Only		}
	(
	indx		integer,	{ index into buffer table	}
	address		int8,		{ address of buffer structure	}
	bpoolindx	smallint,	{ which buffer pool 		}
	latchp		int8,		{ ptr to buffer latch		}
	bhforw		int8,		{ forward pointer in hash list	}
	bhback		int8,		{ backward pointer in hash list	}
	blforw		int8,		{ forward pointer in lru list	}
	blback		int8,		{ backward pointer in lru list	}
	bflags		integer,	{ buffer flags			}
	berror		smallint,	{ i/o error			}
	reusecnt	integer,	{ how often buff is reused	}
	lrunum		integer,	{ last lru this buff was on	}
	chunk		integer,	{ physical page addr - chunk 	}
	offset		integer,	{ physical page addr - offset 	}
	pagemem		int8,		{ ptr to page in shared memory	}
	owner		int8,		{ owner with lock (rstcb *) 	}
	wtlist 		int8,		{ list of waiters for buff lock	}
	sharecnt	smallint,	{ count of users sharing buff	}
	xflags		smallint,	{ type of lock on buffer	}
        ostamp          integer,        { stamp of oldest update not flushed }
        lsnuq           integer,        { LSN entries of oldest update  }
        lsnpos          integer,        { not flushed                   }
	bufsize		integer		{ buffer page size		}
	);
    create unique index informix.sysbufhdridx  on sysbufhdr (bpoolindx, indx);
    create unique index informix.sysbufhdraddr on sysbufhdr (address);
    revoke all on informix.sysbufhdr from public as informix;
    grant select on informix.sysbufhdr to public as informix;

{ Dbspace Table }
    create table informix.sysdbstab		{ Internal Use Only		}
	(
        address         int8,		{ address of dbspace structure  }
        dbsnum          integer,        { dbspace number                }
        flags           integer,        { dbspace flags                 }
        fchunk          integer,        { first chunk in dbspace        }
        nchunks         integer,        { number of chunks in dbspace   }
        created         integer,        { date created                  }
        prtpage         integer,        { partition partition starts at }
        partp           int8,		{ ptr to partp in partition tab }
        pagesize        integer,        { BLOB page size                }
        bcolcnt         integer,        { number of blob columns ref    }
	create_size     float,		{ Min size of chunk (for SP)	}
	extend_size     float,		{ Min chunk extension (for SP)	}
        level0          integer,        { time of last level 0 archive  }
        stamp0          integer,        { timestamp last level 0 archive}
        logid0          integer,        { logid for last level 0 archive}
        logpos0         integer,        { log pos last level 0 archive  }
        level1          integer,        { time of last level 1 archive  }
        stamp1          integer,        { timestamp last level 1 archive}
        logid1          integer,        { logid for last level 1 archive}
        logpos1         integer,        { log pos last level 1 archive  }
        level2          integer,        { time of last level 2 archive  }
        stamp2          integer,        { timestamp last level 2 archive}
        logid2          integer,        { logid for last level 2 archive}
        logpos2         integer,        { log pos last level 2 archive  }
        logid           integer,        { log id (for logical restore)  }
        logpos          integer,        { log pos (for logical restore) }
        oldlogid        integer,        { oldest log id (for log resto) }
        lastlogid       integer,        { last log id (for log resto)   }
        rest_time       integer,        { time of last physical restore }
        arc_pit         integer,        { PIT to terminate log replay   }
        name            char(128),      { dbspace name                  }
        owner           char(32),       { dbspace owner                 }
        sbcflag         integer,        { SBlob default create flags    }
        sboflag         integer,        { SBlob default open flags      }
	max_size        int8,		{ Max size of space in MB	}
	tenant_partnum  integer		{ multi-tenant dbs systables    }
	);
    create unique index informix.sysdbstab_dbsnum on sysdbstab (dbsnum);
    revoke all on informix.sysdbstab from public as informix;
    grant select on informix.sysdbstab to public as informix;

{ Chunk Table }
    create table informix.syschktab		{ Internal Use Only		}
	(
	address		int8,		{ address of chunk structure	}
	chknum		smallint,	{ chunk number			}
	nxchunk		smallint,	{ number of next chunk in dbsp	}
	pagesize	integer,	{ page size			}
	fpage		integer,	{ page offset of chunk in dbsp	}
	offset		int8,		{ pages offset into device (in
					  units of base page size)	}
	chksize		integer,	{ pages in chunk		}
	nfree		integer,	{ free pages in chunk		}
	mdsize		integer,	{ metadata pages in chunk	}
	udsize		integer,	{ user data pages in chunk	}
	udfree		integer,	{ free user data pages in chunk	}
	dbsnum		smallint,	{ dbspace number		}
	overhead 	integer,	{ blob freemap overhead 	}
	flags		integer,	{ chunk flags			}
	namlen		smallint,	{ length of device pathname	}
	fname		char(256),	{ device pathname		}
	reads		integer,	{ number of read ops 		}
	writes		integer,	{ number of write ops		}
	pagesread	integer,	{ number of pages read 		}
	pageswritten	integer,	{ number of pages written	}
	readtime	float,		{ time spent reading (usecs)	}
	writetime	float		{ time spent writing (usecs)	}
	);
    create unique index informix.syschktab_chknum on syschktab (chknum);
    revoke all on informix.syschktab from public as informix;
    grant select on informix.syschktab to public as informix;

{ Mirror Chunk Table }
    create table informix.sysmchktab		{ Internal Use Only		}
	(
	address		int8,		{ address of chunk structure	}
	chknum		smallint,	{ chunk number			}
	nxchunk		smallint,	{ number of next chunk in dbsp	}
	pagesize	integer,	{ page size			}
	fpage		integer,	{ page offset of chunk in dbsp	}
	offset		int8,		{ pages offset into device (in
					  units of base page size)	}
	chksize		integer,	{ pages in chunk		}
	nfree		integer,	{ free pages in chunk		}
	mdsize		integer,	{ metadata pages in chunk	}
	udsize		integer,	{ user data pages in chunk	}
	udfree		integer,	{ free user data pages in chunk	}
	dbsnum		smallint,	{ dbspace number		}
	overhead 	integer,	{ blob freemap overhead 	}
	flags		integer,	{ chunk flags			}
	namlen		smallint,	{ length of device pathname	}
	fname		char(256),	{ device pathname		}
	reads		integer,	{ number of read ops 		}
	writes		integer,	{ number of write ops		}
	pagesread	integer,	{ number of pages read 		}
	pageswritten	integer,	{ number of pages written	}
	readtime	float,		{ time spent reading (usecs)	}
	writetime	float		{ time spent writing (usecs)	}
	);
    create unique index informix.sysmchktab_chknum on sysmchktab (chknum);
    revoke all on informix.sysmchktab from public as informix;
    grant select on informix.sysmchktab to public as informix;

{ Fast version of Chunk Table }
    create table informix.syschktab_fast        { Internal Use Only             }
        (
        address         int8,           { address of chunk structure    }
        chknum          smallint,       { chunk number                  }
        nxchunk         smallint,       { number of next chunk in dbsp  }
        pagesize        integer,        { page size                     }
        fpage           integer,        { page offset of chunk in dbsp  }
        offset          int8,           { pages offset into device (in
                                          units of base page size)      }
        chksize         integer,        { pages in chunk                }
        nfree           integer,        { free pages in chunk           }
        mdsize          integer,        { metadata pages in chunk       }
        udsize          integer,        { user data pages in chunk      }
        udfree          integer,        { free user data pages in chunk }
        dbsnum          smallint,       { dbspace number                }
        overhead        integer,        { blob freemap overhead         }
        flags           integer,        { chunk flags                   }
        namlen          smallint,       { length of device pathname     }
        fname           char(256),      { device pathname               }
        reads           integer,        { number of read ops            }
        writes          integer,        { number of write ops           }
        pagesread       integer,        { number of pages read          }
        pageswritten    integer,        { number of pages written       }
        readtime        float,          { time spent reading (usecs)    }
        writetime       float           { time spent writing (usecs)    }
        );
    create unique index informix.syschktab_chknum_f on syschktab_fast (chknum);
    revoke all on informix.syschktab_fast from public as informix;
    grant select on informix.syschktab_fast to public as informix;

{ Fast version of Mirror Chunk Table }
    create table informix.sysmchktab_fast       { Internal Use Only             }
        (
        address         int8,           { address of chunk structure    }
        chknum          smallint,       { chunk number                  }
        nxchunk         smallint,       { number of next chunk in dbsp  }
        pagesize        integer,        { page size                     }
        fpage           integer,        { page offset of chunk in dbsp  }
        offset          int8,           { pages offset into device (in
                                          units of base page size)      }
        chksize         integer,        { pages in chunk                }
        nfree           integer,        { free pages in chunk           }
        mdsize          integer,        { metadata pages in chunk       }
        udsize          integer,        { user data pages in chunk      }
        udfree          integer,        { free user data pages in chunk }
        dbsnum          smallint,       { dbspace number                }
        overhead        integer,        { blob freemap overhead         }
        flags           integer,        { chunk flags                   }
        namlen          smallint,       { length of device pathname     }
        fname           char(256),      { device pathname               }
        reads           integer,        { number of read ops            }
        writes          integer,        { number of write ops           }
        pagesread       integer,        { number of pages read          }
        pageswritten    integer,        { number of pages written       }
        readtime        float,          { time spent reading (usecs)    }
        writetime       float           { time spent writing (usecs)    }
        );
    create unique index informix.sysmchktab_chknum_f on sysmchktab_fast(chknum);
    revoke all on informix.sysmchktab_fast from public as informix;
    grant select on informix.sysmchktab_fast to public as informix;

{ Log file info }
    create table informix.syslogfil		{ Internal Use Only		}
	(
	address		int8,		{ address of logfile structure	}
	number		smallint,	{ logfile number		}
	flags		smallint,	{ logfile flags			}
	fillstamp	integer,	{ stamp when last filled	}
	filltime	integer,	{ time when last filled		}
	uniqid		integer,	{ logfile uniqid		}
	chunk		integer,	{ physical address of start - chunk }
	offset		integer,	{ physical address of start - offset}
	size		integer,	{ pages in logfile		}
	used		integer		{ pages used in logfile		}
	);
    create unique index informix.syslogfilnum on syslogfil (number);
    revoke all on informix.syslogfil from public as informix;
    grant select on informix.syslogfil to public as informix;

{ Btclean Request info }
    create table informix.sysbtcreq		{ Internal Use Only		}
	(
	indx		integer,	{ index into btcreq table	}
	address		int8,		{ address of btclean structure	}
	hash		int8,		{ next in hash list		}
	next		int8,		{ next in busy/free list	}
	partnum		integer,	{ partnum of request		}
	pagenum		integer,	{ pagenum of request       	}
	keynum		smallint,	{ keynum of request		}
	putcnt		smallint	{ count of puts for this req	}
	);
    create unique index informix.sysbtcreqidx on sysbtcreq (indx);
    revoke all on informix.sysbtcreq from public as informix;
    grant select on informix.sysbtcreq to public as informix;

{ Trace buffer }
    create table informix.systraces		{ Internal Use Only		}
	(
	type		char(8),	{ event type			}
	file		char(14),	{ source file trace is from	}
	lineno		integer,	{ line # in source file		}
	stamp		integer,	{ stamp when traced		}
	time		integer,	{ time when traced		}
	userp		int8,		{ user who traced (rstcb *)	}
	trans		int8,		{ tx who traced (rtx *)		}
	data1		int8,		{ data value 1			}
	data2		int8,		{ data value 2			}
	data3		int8,		{ data value 3			}
	data4		int8,		{ data value 4			}
	data5		int8		{ data value 5			}
	);
    create index informix.systrac_stampidx on systraces (stamp);
    revoke all on informix.systraces from public as informix;
    grant select on informix.systraces to public as informix;

{ Open Partition Table }
    create table informix.sysptntab		{ Internal Use Only		}
	(
	address		int8,		{ address of partition structure}
	condp		int8,		{ ptr to condition struct 	}
	latchp		int8,		{ ptr to latch struct **	}
	flags		smallint,	{ partition flags	 	}
	ucount		integer,	{ usage count			}
	partnum		integer,	{ partition number		}
	tablock		integer,	{ table lock id			}
	chunk		integer,	{ physaddr of partition pg - chunk }
	offset		integer,	{ physaddr of partition pg - offset }
	lastrowszp	int8,		{ size of last alloc'ed row	}
	lastrowpnp	int8,		{ last page alloc'ed for row	}
	lastidxpn 	integer,	{ last page alloc'ed for index	}
	extnsp1		int8,		{ initial extent list address	}
	nextns1  	smallint,	{ number of initial extents	}
	extnsp2		int8,		{ new extent list address	}
	nextns2  	smallint,	{ number of new extents		}
	badkeys		integer,	{ badkey bitmap			}
	ptlist		int8,		{ next in list of dropped tables }
	altstmp		integer,	{ time stamp of last alter	}
	ocount		integer,	{ open count			}
	skstamp		integer,	{ stamp of last table update 	}
	glscollname	char(32),	{ GLS collation name		}
	localep		int8,		{ locale pointer		}
	pf_rqlock	bigint,		{ lock requests			}
	pf_wtlock	bigint,		{ lock waits			}
	pf_deadlk	bigint,		{ deadlocks   			}
	pf_lktouts	bigint,		{ lock timeouts 		}
        pf_dskreads     bigint,        	{ disk reads                    }
	pf_isread	bigint,		{ reads				}
        pf_dskwrites    bigint,        	{ disk writes                   }
	pf_iswrite	bigint,		{ writes			}
	pf_isrwrite	bigint,		{ rewrites 			}
	pf_isdelete	bigint,		{ deletes			}
	pf_bfcread	bigint,		{ buffer reads			}
	pf_bfcwrite	bigint,		{ buffer writes			}
	pf_seqscans	bigint,		{ sequential scans		}
	dbsnum 		integer 	{ dbspace number		}
	);
    create unique index informix.sysptntab_pnix on sysptntab (partnum);
    create index informix.sysptntab_pnix2 on sysptntab (dbsnum);
    revoke all on informix.sysptntab from public as informix;
    grant select on informix.sysptntab to public as informix;

{ Shared Memory }
    create table informix.sysshmem		{ Internal Use Only		}
	(
	address 	int8,		{ address in shmem to dump	}
	loc		char(10),	{ location representation	}
	hexdata		char(40),	{ 16 bytes hexdumped from addr	}
	ascdata		char(16)	{ 16 bytes ascii-dumped       	}
	);
    create unique index informix.sysshmemidx on sysshmem (address);
    revoke all on informix.sysshmem from public as informix;

{ Shared Memory Header }
    create table informix.sysshmhdr		{ Internal Use Only		}
	(
	number		integer,	{ unique identifier for element	}
	name		char(32),	{ name of rhead_t element	}
	value		int8		{ value of rhead_t element	}
	);
    create unique index informix.sysshmhdr_numix on sysshmhdr(number);
    revoke all on informix.sysshmhdr from public as informix;
    grant select on informix.sysshmhdr to public as informix;

{ Configuration parameters }
    create table informix.syscfgtab
	(
	cf_id		integer,	{ unique numeric identifier	}
	cf_name		char(128),	{ config parameter name 	}
	cf_flags	integer,	{ flags 			}
	cf_original	char(513),	{ value in ONCONFIG at boottime }
	cf_effective	char(513),	{ value effectively in use 	}
	cf_default	char(513)	{ value by default		}
	);
    create index informix.syscfgtabix1 on syscfgtab(cf_id);
    revoke all on informix.syscfgtab from public as informix;
    grant select on informix.syscfgtab to public as informix;

{ Session control blocks }
    create table informix.sysscblst		{ Internal Use Only		}
	(
	sid		integer,	{ session id			}
	address		int8,		{ address of session structure	}
	currheap	int8,		{ ptr to memory heap		}
	poolp		int8,		{ ptr to private session pool	}
	breakflag	integer,	{ stop current processing	}
	urgent		integer,	{ message from tbmode		}
	killflag	integer,	{ stop all processing		}
	neterrno	integer,	{ network error number		}
	flags		integer,	{ session flags			}
	local		smallint,	{ user is local	if set 		}
	tlatchp		int8,		{ latch protecting thread list	}
	threadlist	int8,		{ ptr to first thread in list	}
	next		int8,		{ next session in list		}
	uid		integer,	{ user id		 	}
	username	char(32),	{ user name			}
	gid		integer,	{ primary group id		}
	nsuppgids	integer,	{ number of supplementary gids	}
	suppgidsp	int8,		{ ptr to suppl'ry gids table	}
	clienttype	integer,	{ client type		 	}
	pid		integer,	{ process id of fe program	}
	progname	char(256),	{ fe program name		}
	ttyin		char(16),	{ tty name for users stdin	}
	ttyout		char(16),	{ tty name for users stdout	}
	ttyerr		char(16),	{ tty name for users stderr	}
	cwd		char(256),	{ users cwd			}
	hostname	char(256),	{ users host name		}
	connected	integer,	{ time that user connected	}
	argc		integer,	{ count of args sent		}
	argvp		int8,		{ ptr to arg table		}
	envvarp		int8,		{ ptr to env var table		}
	numenvvars	integer,	{ number of env vars		}
	sizeenvtab	integer,	{ size of env var table		}
	sqscb		int8,		{ ptr to sql control block	}
	netscb		int8,		{ ptr to net control block	}
	class		integer, 	{ VP class			}
	memtotal        integer,	{ Total memory for this session }
	memused		integer		{ Used memory for this session  }
{
   Temporarily disable this change to avoid CONVERSION issue between 
   interim releases due to rolling upgrade requirement.

	session_limit_locks integer,
	session_limit_memory integer,
	session_limit_tempspace integer,
	session_limit_logspace integer,
	session_limit_txn_time integer
}
	);
    create unique index informix.sysscblst_sidix on sysscblst(sid DESC);
    revoke all on informix.sysscblst from public as informix;
    grant select on informix.sysscblst to public as informix;

{ Thread control blocks }
    create table informix.systcblst		{ Internal Use Only		}
	(
	tid		integer,	{ thread id			}
	address		int8,		{ address of thread structure	}
	stackp		int8,		{ ptr to threads stack		}
	tnext		int8,		{ next thread in global list  	}
	tprev		int8,		{ prev thread in global list	}
	lock		int8,		{ thread struct protection	}
	next		int8,		{ next thread in special list	}
	prev		int8,		{ prev thread in special list	}
	joinlist	int8,		{ head of joined threads list	}
	joinnext	int8,		{ next in joined threads list	}
	joinee		int8,		{ thread this thread joined	}
	joinresult	integer,	{ result of join		}
	initialroutine	int8,		{ initial thread procedure	}
	initialarg	int8,		{ initial arg			}
	name		char(12),	{ thread name			}
	self		int8,		{ this thread's address		}
	state		integer,	{ thread state			}
	statedesc	char(10),	{ thread state description	}
	statedetail	char(30),	{ thread state detailed descr	}
	flags		integer,	{ flags				}
	wait_time	integer,	{ wait time accumulator		}
	detach		integer,	{ thread detach mode		}
	priority	integer,	{ thread priority		}
	class		integer,	{ user defined thread class 	}
	vpid		integer,	{ vpid where thread is running	}
	bind_vp		int8,		{ vp on which thread must run	}
	bind_priv	integer,	{ thread has been bound		}
	private_data	int8,		{ ptr to private data		}
	wtmutexp	int8,		{ ptr to mutex waiting on	}
	wtcondp		int8,		{ ptr to condition waiting on	}
	sleep_time	integer,	{ seconds slept + start time	}
	start_wait	integer,	{ sleep start time		}
	pcount		integer,	{ number of valid p elements	}
	padrp		int8,		{ ptr to padr table		}
	pvalp		int8,		{ ptr to pval table		}
	last_run_time	integer,	{ total time thread has run	}
	wakeup_count	integer,	{ number of stacked wakeups	}
	tstatp		int8,		{ ptr to thread TSTAT_T struct	}
	wstatp		int8,		{ ptr to thread WSTAT_T struct	}
        wreason         integer,        { reason for thread to be wait  }
        wait_reason     char(18),       { reason for thread to be wait  }
	num_sched   	int8,		{ number of times thread sched  }
	cpu_time   	float		{ time spent running on vp      }
	);
    create unique index informix.systcblst_tidix on systcblst(tid);
    revoke all on informix.systcblst from public as informix;
    grant select on informix.systcblst to public as informix;

{ VP info }
    create table informix.sysvplst		{ Internal Use Only		}
	(
	vpid		 integer,	{ VP id				}
	address		 int8,		{ address of VP struct          }
	pid		 integer,	{ unix process id		}
	usecs_user	 float,		{ number of usecs of user time	}
	usecs_sys	 float,		{ number of usecs of system time}
	scputimep	 int8,		{ ptr to saved cputime (tms)	}
	rcputimep	 int8,		{ ptr to reset cputime (tms)	}
	class		 integer,	{ class of VP		 	}
	classname	 char(19),	{ classname of VP	 	}
	readyqueue	 int8,		{ ptr to ready queue tab (TCB_Q)}
	num_ready	 integer,	{ number of ready threads	}
	flags		 integer,	{ VP flags			}
	next		 int8,		{ next in idle list		}
	prev		 int8,		{ prev in idle list		}
	semid		 integer,	{ semid for this VP		}
	lock		 int8,		{ VP protection			}
	total_semops	 int8,		{ Total times VP slept on a semop }
	total_busy_wts   int8,		{ Total VP busy waits           }
	total_yields     int8,		{ Total VP yields		}
	total_spins      int8,		{ Total spins while busy waiting }
	steal_attempts   int8,		{                                }
	steal_attempts_suc int8,	{                                }
	idle_search      int8,		{                                }
	idle_search_suc  int8,		{                                }
	vp_poll_scheds   int8,		{                                }
	vp_mt_naps       int8,		{                                }
	vp_cache_size    int8,		{ size of the vp cache           }
	vp_cache_allocs  int8,		{                                }
	vp_cache_miss    int8,		{                                }
	vp_cache_frees   int8,		{                                }
	vp_cache_drain   int8,		{                                }
	vp_cache_nblocks int8,		{ current number of blocks       }
	thread_run       float,		{ total thread run time on vp   }
	thread_idle	 float,		{ total time running idle thread}
	thread_poll_idle float		{ inline poll thread idle time  }
	);
    create unique index informix.sysvplst_vpidix on sysvplst(vpid);
    revoke all on informix.sysvplst from public as informix;
    grant select on informix.sysvplst to public as informix;

{ Data Replication control block }
    create table informix.sysdrcb		{ Internal Use Only		}
	(
	address		int8,		{ address of drcb structure     }
	version		integer,	{ drcb version			}
	lock		int8,		{ drcb lock			}
	type		integer,	{ drcb server type		}
	name		char(128),	{ drcb server name;128=IDENTSIZE}
	intvl		integer,	{ dr buffer flush interval	}
	timeout		integer,	{ dr network timeout		}
	drauto		integer,	{ dr auto			}
	dridxauto	integer,	{ dr idxauto			}
	lostfound	char(256),	{ dr lost+found pathname	}
	state		integer,	{ dr server state		}
	failrecvr	integer,	{ dr failure recovery flags	}
	pingtime	integer,	{ dr last ping time		}
	sessiontid	int8,		{ dr session thread		}
	pingtid		int8,		{ dr ping thread                }
	applytid	int8,		{ dr apply thread               }
	recvrtid	int8,		{ logcial recovery thread       }
	scb		int8,		{ dr scb			}
	client_type	integer,	{ dr client type		}
	no_kill		integer,	{ dr no_kill flag		}
	no_clients	integer,	{ dr no_clients flag		}
	lgr_scb		int8,		{ logcical recovery scb         }
	lgr_rstcb	int8,		{ logcical recovery rstcb	}
	lgr_bufsize	integer,	{ logical recovery buffer size	}
	lgr_numbufs	integer,	{ number logcial recovery buffers}
	ckptaddr	int8,		{ dr last ckpt address          }
	cpflag		integer,	{ dr cpflag			}
	bufflag		integer,	{ dr bufflag			}
	lg_offs		integer,	{ dr lg_offs			}
	ll		int8,		{ dr logical log info		}
	bufcur		int8,		{ dr current buffer		}
	bqempty		int8,		{ dr empty q                    }
	bqfull		int8		{ dr full q			}
	);
    revoke all on informix.sysdrcb from public as informix;
    grant select on informix.sysdrcb to public as informix;

{ CDR queued info table }
    create table informix.syscdrq              { Internal Use Only }
	(
	srvid           integer,        { CDR server id }
	repid           integer,        { CDR replicate id }
	srcid       	integer,    	{ CDR source server id }
	srvname         char(128),      { target server name }
	replname	char(128),	{ collection or replicate name }
	srcname		char(128),	{ source server name }
	bytesqued       integer 	{ number of bytes queued }
	);

    create unique index informix.syscdrq_idx on syscdrq(srvid,repid,srcid);
    revoke all on informix.syscdrq from public as informix;
    grant select on informix.syscdrq to public as informix;

{ CDR trans. processed info table }
    create table informix.syscdrtx             { Internal Use Only }
	(
	srvid       integer,    { CDR server id }
	srvname     char(128),  { target server name }
	txprocssd   integer,    { number of trans. processed }
	txcmmtd     integer,    { number of trans. committed }
	txabrtd     integer,    { number of trans. aborted }
	rowscmmtd   integer,    { number of rows committed }
	rowsabrtd   integer,    { number of rows aborted }
	txbadcnt    integer     { number of trans. bad commit time }
	);

    create unique index informix.syscdrtx_idx on syscdrtx(srvid);
    revoke all on informix.syscdrtx from public as informix;
    grant select on informix.syscdrtx to public as informix;

{ CDR server }
    create table informix.syscdrs              { Internal Use Only }
	(
	servid          integer,        { server id }
	servname        char(128),      { server name }
	cnnstate        char(1),        { connection state }
	cnnstatechg     integer,        { time connection status changed }
	servstate       char(1),        { server state }
	ishub           char(1),        { hub flag }
	isleaf          char(1),        { leaf flag }
	rootserverid    integer,        { root server id }
	forwardnodeid   integer,        { forward node server id }
	timeout         integer         { idle connection timeout }
	);

    create unique index informix.syscdrs_idx on syscdrs(servid);
    revoke all on informix.syscdrs from public as informix;
    grant select on informix.syscdrs to public as informix;

{ CDR in memory send progress table }
    create table informix.syscdrprog		{ Internal Use Only }
	(
	dest_id		integer,
	group_id	integer,
	source_id	integer,
	key_acked_srv   integer,
	key_acked_lgid  integer,
	key_acked_lgpos integer,
	key_acked_seq   integer,
	tx_stamp_1	integer,
	tx_stamp_2	integer
	);
    create unique index informix.syscdrprog_idx
	 on syscdrprog(dest_id, group_id, source_id);
    revoke all on informix.syscdrprog from public as informix;

{ CDR in memory receive progress table }
    create table informix.syscdrrecvqprog		{ Internal Use Only }
	(
	dest_id		integer,
	group_id	integer,
	source_id	integer,
	key_acked_srv   integer,
	key_acked_lgid  integer,
	key_acked_lgpos integer,
	key_acked_seq   integer,
	tx_stamp_1	integer,
	tx_stamp_2	integer,
	total_failed_rows integer
	);
    create unique index informix.syscdrrecvqprog_idx
	 on syscdrrecvqprog(dest_id, group_id, source_id);
    revoke all on informix.syscdrrecvqprog from public as informix;

{ CDR in memory control queue progress table }
    create table informix.syscdrcntrlqprog		{ Internal Use Only }
	(
	dest_id		integer,
	group_id	integer,
	source_id	integer,
	key_queued_srv   integer,
	key_queued_lgid  integer,
	key_queued_lgpos integer,
	key_queued_seq   integer,
	key_acked_srv   integer,
	key_acked_lgid  integer,
	key_acked_lgpos integer,
	key_acked_seq   integer
	);
    create unique index informix.syscdrcntrlqprog_idx
	 on syscdrcntrlqprog(dest_id, group_id, source_id);
    revoke all on informix.syscdrcntrlqprog from public as informix;
    grant select on informix.syscdrcntrlqprog to public as informix;

{ CDR in memory queues }
    create table informix.syscdrsend_txn		{ Internal Use Only }
	(
	ctkeyserverid	integer,
	ctkeyid		integer,
	ctkeypos   	integer, 
	ctkeysequence	integer,
	ctstamp	        bigint,
	ctcommittime	integer,
	ctuserid	integer,
	ctfromid	integer 
	);
    create unique index informix.syscdrsend_tidx	on syscdrsend_txn(ctstamp);
    revoke all on informix.syscdrsend_txn from public as informix;

{ Reorganizations that are active or finished }
    create table informix.sysreorgops		{Customer use }
	(
	id		integer,
	dbname		char(128),
	owner		char(32),
	tabname		char(128),
	threadid	integer,
	dbsnum		integer,
	partnum		integer,
	status		char(20),
	substatus	integer,
	errno		integer	
	);
    revoke all on informix.sysreorgops from public as informix;
    grant select on informix.sysreorgops to public as informix; 

    create table informix.syscdrack_txn		{ Internal Use Only }
	(
	ctkeyserverid	integer,
	ctkeyid		integer,
	ctkeypos   	integer, 
	ctkeysequence	integer,
	ctstamp 	bigint,
	ctcommittime	integer,
	ctuserid	integer,
	ctfromid	integer 
	);
    create unique index informix.syscdrack_tidx	on syscdrack_txn(ctstamp);
    revoke all on informix.syscdrack_txn from public as informix;

    create table informix.syscdrctrl_txn		{ Internal Use Only }
	(
	ctkeyserverid	integer,
	ctkeyid		integer,
	ctkeypos   	integer, 
	ctkeysequence	integer,
	ctstamp	        bigint,
	ctcommittime	integer,
	ctuserid	integer,
	ctfromid	integer 
	);
    create unique index informix.syscdrctrl_tidx	on syscdrctrl_txn(ctstamp);
    revoke all on informix.syscdrctrl_txn from public as informix;

    create table informix.syscdrsync_txn		{ Internal Use Only }
	(
	ctkeyserverid	integer,
	ctkeyid		integer,
	ctkeypos   	integer, 
	ctkeysequence	integer,
	ctstamp 	bigint,
	ctcommittime	integer,
	ctuserid	integer,
	ctfromid	integer 
	);
    create unique index informix.syscdrsync_tidx	on syscdrsync_txn(ctstamp);
    revoke all on informix.syscdrsync_txn from public as informix;

    create table informix.syscdrrecv_txn		{ Internal Use Only }
	(
	ctkeyserverid	integer,
	ctkeyid		integer,
	ctkeypos   	integer, 
	ctkeysequence	integer,
	ctstamp	        bigint,
	ctcommittime	integer,
	ctuserid	integer,
	ctfromid	integer 
	);
    create unique index informix.syscdrsrecv_tidx on syscdrrecv_txn(ctstamp);
    revoke all on informix.syscdrrecv_txn from public as informix;

    create table informix.syscdrsend_buf		{ Internal Use Only }
	(
	cbflags		integer,
	cbsize		integer,
	cbkeyserverid	integer,
	cbkeyid		integer,
	cbkeypos   	integer, 
	cbkeysequence	integer,
	cbgroupid	integer,
	cbcommittime	integer
	);
    create unique index informix.syscdrsend_bidx
	on syscdrsend_buf(cbkeyserverid, cbkeyid, cbkeypos, cbkeysequence);
    revoke all on informix.syscdrsend_buf from public as informix;

    create table informix.syscdrack_buf		{ Internal Use Only }
	(
	cbflags		integer,
	cbsize		integer,
	cbkeyserverid	integer,
	cbkeyid		integer,
	cbkeypos   	integer, 
	cbkeysequence	integer,
	cbgroupid	integer,
	cbcommittime	integer
	);
    create unique index informix.syscdrack_bidx 
	on syscdrack_buf(cbkeyserverid, cbkeyid, cbkeypos, cbkeysequence);
    revoke all on informix.syscdrack_buf from public as informix;

    create table informix.syscdrctrl_buf		{ Internal Use Only }
	(
	cbflags		integer,
	cbsize		integer,
	cbkeyserverid	integer,
	cbkeyid		integer,
	cbkeypos   	integer, 
	cbkeysequence	integer,
	cbgroupid	integer,
	cbcommittime	integer
	);
    create unique index informix.syscdrctrl_bidx
	on syscdrctrl_buf(cbkeyserverid, cbkeyid, cbkeypos, cbkeysequence);
    revoke all on informix.syscdrctrl_buf from public as informix;

    create table informix.syscdrsync_buf		{ Internal Use Only }
	(
	cbflags		integer,
	cbsize		integer,
	cbkeyserverid	integer,
	cbkeyid		integer,
	cbkeypos   	integer, 
	cbkeysequence	integer,
	cbgroupid	integer,
	cbcommittime	integer
	);
    create unique index informix.syscdrsync_bidx 
	on syscdrsync_buf(cbkeyserverid, cbkeyid, cbkeypos, cbkeysequence);
    revoke all on informix.syscdrsync_buf from public as informix;

    create table informix.syscdrrecv_buf		{ Internal Use Only }
	(
	cbflags		integer,
	cbsize		integer,
	cbkeyserverid	integer,
	cbkeyid		integer,
	cbkeypos   	integer, 
	cbkeysequence	integer,
	cbgroupid	integer,
	cbcommittime	integer
	);
    create unique index informix.syscdrrecv_bidx 
	on syscdrrecv_buf(cbkeyserverid, cbkeyid, cbkeypos, cbkeysequence);
    revoke all on informix.syscdrrecv_buf from public as informix;
    grant select on informix.syscdrrecv_buf to public as informix;

    create table informix.syscdrrecv_stats       { Internal Use Only }
        (
        source          integer,        { source server - cdrid }
        txncnt          integer,        { number of txn from this source }
        pending         integer,        { txn this source currently pending }
        active          integer,        { txn this source currently active }
        maxpending      integer,        { max txn pending this source }
        maxactive       integer,        { max txn active form this source }
        avg_pending     float,          { avg pending txn this source }
        avg_active      float,          { avg active txn this source }
        cmtrate         float           { avt commit rate from this source }
        );
    create unique index informix.syscdrrecv_sidx
        on syscdrrecv_stats (source);
    revoke all on informix.syscdrrecv_stats from public as informix;
    grant select on informix.syscdrrecv_stats to public as informix;

    create table informix.syscdrlatency          { Internal Use Only }
        (
        source          integer,        { source of txn - cdrid }
        replid          integer,        { replicate id }
        txncnt          integer,        { num txn this source - repl }
        inserts         integer,        { insert count }
        deletes         integer,        { delete count }
        updates         integer,        { update count }
        last_tgt_apply  integer,        { last target apply - cdrtime }
        last_src_apply  integer         { last source apply - cdrtime }
        );
    create unique index informix.syscdrlatency_idx
        on syscdrlatency (source, replid);
    revoke all on informix.syscdrlatency from public as informix;
    grant select on informix.syscdrlatency to public as informix;

    create table informix.syscdrtsapply          { Internal Use Only }
        (
        source          integer,        { source of txn - cdrid }
        replid          integer,        { replicate id }
        txncnt          integer,        { num txn this source - repl }
        tsinserts       integer,        { ts insert count }
        tsdeletes       integer,        { ts delete count }
        tscmd        integer,           { ts command count }
        last_tgt_apply  integer,        { last target apply - cdrtime }
        last_src_apply  integer         { last source apply - cdrtime }
        );
    create unique index informix.syscdrtsapply_idx
        on syscdrtsapply (source, replid);
    revoke all on informix.syscdrtsapply from public as informix;
    grant select on informix.syscdrtsapply to public as informix;


    create table informix.syscdr_rqm     { Internal Use Only }
    (
    rqm_idx     integer,        { index number }
    rqm_name    char(128),      { queue name }
    rqm_flags   integer,        { flags }
    rqm_txn     integer,        { transactions in queue }
    rqm_event   integer,        { events in queue }
    rqm_txn_in_memory integer,  { transaction in memory }
    rqm_txn_in_spool_only integer, { spool only txn }
    rqm_txn_spooled integer,    { spooled only txn }
    rqm_unspooled_bytes int8,   { unspooled bytes }
    rqm_data_in_queue int8,     { data in queue }
    rqm_inuse_mem int8,         { real memory in use }
    rqm_pending_buffer integer, { pending buffers }
    rqm_pending_data int8,      { pending buffers }
    rqm_maxmemdata int8,        { max memory in use - data }
    rqm_maxmemhdr int8,         { max memory in use - headers }
    rqm_totqueued int8,         { total data queued }
    rqm_tottxn  integer,        { total transactions queued }
    rqm_totspooled integer,     { total txn spooled }
    rqm_totrestored integer,    { total txn restored }
    rqm_totrecovered integer,   { total txn recovered }
    rqm_totspoolread integer,   { total rows read from spool }
    rqm_totdeleted integer,     { total txn deleted }
    rqm_totduplicated integer,  { total txn duplicate }
    rqm_totlookup integer       { total txn lookups }
    );
    create unique index informix.syscdr_rqmidx
    on syscdr_rqm (rqm_idx);
    revoke all on informix.syscdr_rqm from public as informix;
    grant select on informix.syscdr_rqm to public as informix;

{ CDR state }
    create table informix.syscdr_state
		(
		er_state	    	char(24),	{ State of ER }
		er_capture_state	char(24),	{ Current state of DDR (capture) }
		er_network_state	char(64),	{ Current state of Nif }
		er_apply_state		char(24)	{ Current state receive manager (apply)}
		);
   	revoke all on informix.syscdr_state from public as informix;
   	grant select on informix.syscdr_state to public as informix;

    create table informix.syscdr_ddr
		(
		ddr_state	char(24),	{ Current stage of DDR (capture) }
		ddr_snoopy_loguniq  integer,	{ Current Snooped Unique Logid   }
		ddr_snoopy_logpos   integer,	{ Current Snooped log pos }
		ddr_replay_loguniq  integer,	{ Current Replay Unique Logid }
		ddr_replay_logpos   integer,	{ Current Replay log pos }
		ddr_curr_loguniq    integer,    { Current Unique Logid }
		ddr_curr_logpos	    integer,	{ Current log pos }
		ddr_logsnoop_cached integer,	{ Logs snooped from cache }
		ddr_logsnoop_disk   integer, 	{ Logs snooped from disk }
		ddr_logs_tossed     integer,    { Logs not cached due to full cache }
		ddr_logs_ignored    integer, 	{ unknown log pages encountered }
		ddr_dlog_requests   integer,    { Number of dynamic logs allocated }
		ddr_total_logspace  integer,	{ System log space in pages}
		ddr_logpage2wrap	integer,	{ Number of pages until log wrap }
		ddr_logpage2block	integer,	{ Number of log pages until log lag state }
		ddr_logneeds		integer,	{ DDR log needs }
		ddr_logcatchup 		integer, 	{ log pages needed to releas log lag state }
		ddr_loglag_state	char(10), 	{ DDR Log Lag State: on/off }
		ddr_cur_loglag_act	char(24), 	{ Current catchup phase action }
		ddr_logstage_diskusage	float,	{ log staging disk space usage in %}
		ddr_logstage_hwm4disk integer,{ Maximum disk space allowed for log staging in KB}
		ddr_logstage_maxused float,{ Maximum disk space ever used for log staging in KB}
		ddr_logstage_lfile_cnt integer { Staged log file count }
		);
    revoke all on informix.syscdr_ddr from public as informix;
    grant select on informix.syscdr_ddr to public as informix;

    create table informix.syscdr_nif
		(
		nif_connid		integer,	{ CDRID of connected node }
		nif_connname	char(24),	{ Name of connected node }
		nif_state		char(24),	{ State of NIF }
		nif_connstate	char(24),	{ State of this connection }
		nif_version		integer,	{ NIF protocol used this connection }
		nif_msgsent 	integer,	{ Number of msg sent this connection }
		nif_bytessent	integer,	{ Bytes sent this connection }
		nif_msgrcv		integer,	{ Number of msg received this conn}
		nif_bytesrcv	integer,	{ Bytes received this connection }
		nif_compress	integer,	{ Compression level this connection }
		nif_sentblockcnt integer,   { Number of times that we sent flow block }
		nif_rcvblockcnt integer,    { Number of times we received flow block }
		nif_trgsend_stamp bigint, { Stamp of last transaction msg sent }
		nif_acksend_stamp bigint, { Stamp of last ACK msg sent }
		nif_ctrlsend_stamp bigint, { Stamp of last control msg sent }
		nif_syncsend_stamp bigint, { Stamp of last metadata sync msg sent }
		nif_starttime   datetime year to second,
       	nif_lastsend    datetime year to second
		);
    revoke all on informix.syscdr_nif from public as informix;
    grant select on informix.syscdr_nif to public as informix;

    create table informix.syscdr_rcv
		(
		rm_state	    	char(100),	{ Current state of RM }
		rm_num_sleepers     integer,	{ number of sleeping threads }
		rm_num_dsthreads    integer, 	{ Number of DS threads }
		rm_min_dsthreads    integer,	{ Minimum number of DS threads }
		rm_max_dsthreads    integer, 	{ Maximum number of DS threads }
		rm_ds_block	    	integer,	{ Is Datasync currently blocked }
		rm_ds_parallel	    integer, 	{ Current degree of parallelism }
		rm_ds_failrate	    float, 	{ Ratio of datasync failures }
		rm_ds_numrun	    integer, 	{ Number of transactions applied }
		rm_ds_num_locktout  integer,    { Number of DS lock timeouts }
		rm_ds_num_lockrb    integer,    { Number of forced RB due to locks }
		rm_ds_num_deadlocks integer,	{ Number of deadly embraces }
		rm_ds_num_pcommits  integer, 	{ Number of out-of-order commits }
		rm_ack_waiting      integer, 	{ Number of ACK threads waiting }
		rm_totsleep	    	integer,    { number of sleep requests }
		rm_sleeptime        integer,    { time of sleeps }
		rm_workload	    	integer,    { indicator of potential workload }
		rm_optscale         integer,    { optscale value }
		rm_min_fthreads     integer,    { min number of floating threads }
		rm_max_fthreads     integer,    { max number of floating threads }
		rm_ackthreads       integer,    { number of ack threads running }
		rm_ackwaiters       integer,    { number of ack threads waiting }
		rm_ack_start        char(64),   { time that ack threads started }
		rm_ds_start	    	char(64),   { time that datasync started }
		rm_pending_acks     integer,    { acks not yet processed }
		rm_blob_error_bufs  integer     { blob error buff count }
		);
    revoke all on informix.syscdr_rcv from public as informix;
    grant select on informix.syscdr_rcv to public as informix;

	create table informix.syscdr_atsdir
		(
		atsd_rid			integer, 	{ identifier of this row }
		atsd_file			char(128),	{ ats file }
		atsd_mode			integer,	{ mode of this file }
		atsd_size			integer, 	{ size of this file }
		atsd_atime			datetime year to second,
		atsd_mtime			datetime year to second,
		atsd_ctime			datetime year to second
		);

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

    create table informix.syscdr_risdir
        (
        risd_rid             integer,    { identifier of this row }
        risd_file            char(128),  { ats file }
        risd_mode            integer,    { mode of this file }
        risd_size            integer,    { size of this file }
        risd_atime           datetime year to second,
        risd_mtime           datetime year to second,
        risd_ctime           datetime year to second
        );
                                                                                
    revoke all on informix.syscdr_risdir from public as informix;
	grant select on informix.syscdr_risdir to public as informix;

	create table informix.syscdr_ats
		(
		ats_rid				integer,
		ats_file			char(128),
		ats_sourceid		integer,
		ats_source			char(128),
		ats_committime		char(20),
		ats_targetid		integer,
		ats_target			char(128),
		ats_receivetime		char(20),
		ats_risfile			char(128),
		ats_line1			char(200),
		ats_line2			char(200),
		ats_line3			char(200),
		ats_line4			char(200),
		ats_line5			char(200),
		ats_line6			char(200),
		ats_line7			char(200),
		ats_line8			char(200),
		ats_line9			char(200),
		ats_line10			char(200)
		);

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

    create table informix.syscdr_ris
        (
        ris_rid             integer,
        ris_file            char(128),
        ris_sourceid        integer,
        ris_source          char(128),
        ris_committime      char(20),
        ris_targetid        integer,
        ris_target          char(128),
        ris_receivetime     char(20),
        ris_atsfile         char(128),
        ris_line1           char(200),
        ris_line2           char(200),
        ris_line3           char(200),
        ris_line4           char(200),
        ris_line5           char(200),
        ris_line6           char(200),
        ris_line7           char(200),
        ris_line8           char(200),
        ris_line9           char(200),
        ris_line10          char(200)
        );
                                                                                
    revoke all on informix.syscdr_ris from public as informix;
    grant select on informix.syscdr_ris to public as informix;

	create table informix.syscdr_rqmstamp
		(
		rqms_qidx		integer,
		rqms_stamp		bigint,
		rqms_cstamp		bigint
		);

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

	create table informix.syscdr_rqmhandle
		(
		rqmh_qidx			integer,
		rqmh_thread			char(18),
		rqmh_stamp			bigint,
		rqmh_servid			integer,
		rqmh_logid			integer,
		rqmh_logpos			integer,
		rqmh_seq			integer
		);

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

{ Compression Dictionaries }
    create table informix.syscompdicts_full
	(
	dict_partnum	      integer,	
		{ Partition number this dictionary applies to }
	dict_code_version     integer,	
		{ Version of the code creating this dict (1 in 1st version) }
	dict_dbsnum	      integer,	
		{ Dbspace number this dictionary resides in }
	dict_create_timestamp integer,  
		{ Timestamp of dictionary creation }
	dict_create_loguniqid integer,	
		{ Logical log unique ID when dictionary was created }
	dict_create_logpos    integer,	
		{ Position within that log when dictionary was created }
	dict_drop_timestamp   integer,	
		{ Timestamp of dictionary drop }
	dict_drop_loguniqid   integer,	
		{ Logical log unique ID when dictionary was "dropped" }
	dict_drop_logpos      integer,
		{ Position within that log when dictionary was "dropped" }
	dict_dictionary	  byte
		{ Compression dictionary object binary }
	);
    create index informix.syscompdict_pnum on syscompdicts_full(dict_partnum);
    revoke all on informix.syscompdicts_full from public as informix;
    grant select on informix.syscompdicts_full to informix as informix;

{ Physical Log }
    create table informix.sysplog
	(
	pl_mutex	int8,		{ physical log mutex            }
	pl_b1mutex	int8,		{ buf1's mutex 			}
	pl_b1condition	int8,		{ buf1's condition for wait/signal }
	pl_b1used	smallint,	{ buf1's log buffer used	}
	pl_b1copied	smallint,	{ buf1's log pages actually bcopied }
	pl_b1buffer	int8,		{ buf1's log buffer             }
	pl_b1wtlist	int8,		{ buf1's waiting for used = copied }
	pl_b2mutex	int8,		{ buf2's mutex 			}
	pl_b2condition	int8,		{ buf2's condition for wait/signal }
	pl_b2used	smallint,	{ buf2's log buffer used	}
	pl_b2copied	smallint,	{ buf2's log pages actually bcopied }
	pl_b2buffer	int8,		{ buf2's log buffer             }
	pl_b2wtlist	int8,		{ buf2's waiting for used = copied }
	pl_curbp	int8,		{ current bp                    }
	pl_otherbp	int8,		{ other bp			}
	pl_bufsize	smallint,	{ log buffer size in pages	}
	pl_stamp	integer,	{ log flush timestamp 		}
	pl_chunk	integer,	{ log file disk address - chunk	}
	pl_offset	integer,	{ log file disk address - offset}
	pl_physize	integer,	{ log file size in pages	}
	pl_phypos	integer,	{ log file position in pages	}
	pl_phyused	integer,	{ log file used in pages	}
	pl_phyarch	integer		{ on-line archive position	}
	);
    revoke all on informix.sysplog from public as informix;
    grant select on informix.sysplog to public as informix;

{ Thread Wait Stats }
    create table informix.systwaits		{ Internal Use Only		}
	(
	tid		integer,	{ thread id of these stats	}
	starttime	bigint,		{ start time of wait or run	}
	startrtime	bigint,		{ start time of ready state	}
	reason		integer,	{ reason for current wait	}
	wreason		smallint,	{ reason for wstats		}
	wnum		integer,	{ number of waits for wreason	}
	wcumtime	float,		{ cumulative time for wreason	}
	wmaxtime	integer		{ maximum time for wreason	}
	);
    create unique index informix.systwaits_tid on systwaits(tid, wreason);
    revoke all on informix.systwaits from public as informix;
    grant select on informix.systwaits to public as informix;

{ Mutexes }
    create table informix.sysmtxlst		{ Internal Use Only		}
	(
	mtx_id		integer,	{ id of this mutex		}
        mtx_address	int8,        { address of this mutex         }
        mtx_next	int8,        { pointer to next mutex in list }
        mtx_prev	int8,        { pointer to prev mutex in list }
	mtx_lock	integer,	{ mutex lock 0=avail, 1=held	}
	mtx_wtlock	integer,	{ protects wait list		}
	mtx_holder	int8,	{ thread holding lock		}
	mtx_wtlist	int8,	{ address of first thread on list}
	mtx_type	smallint,	{ type of mutex			}
	mtx_flags	smallint,	{ flags				}
	mtx_lkcnt	smallint,	{ relock or share lock count    }
	mtx_name	char(12),	{ name of mutex			}
	mtx_nwaits	integer,	{ number of waits on this mutex }
	mtx_nservs	integer,	{ number of services		}
	mtx_curlen	integer,	{ current length		}
	mtx_totlen	integer,	{ total queue length		}
	mtx_maxlen	integer,	{ maximum queue length		}
	mtx_waittime	float,		{ cumulative wait time (usecs)	}
	mtx_servtime	float,		{ cumulative service time (usecs)}
	mtx_maxwait	integer		{ maximum wait time (usecs)	}
	);
    create unique index informix.sysmtxlstix1 on sysmtxlst(mtx_id);
    create unique index informix.sysmtxlstix2 on sysmtxlst(mtx_address);		
    revoke all on informix.sysmtxlst from public as informix;
    grant select on informix.sysmtxlst to public as informix;

{ Conditions }
    create table informix.sysconlst		{ Internal Use Only		}
	(
	con_id		integer,	{ id of this condition		}
	con_address	int8,		{ address of this condition     }
	con_next	int8,		{ pointer to next condition in list}
	con_prev	int8,		{ pointer to prev condition in list}
	con_lock	integer,	{ condition lock 0=avail, 1=held}
	con_wtlist	int8,		{ address of first thread on wait list}
	con_type	integer,	{ type of condition (const)	}
	con_name	char(12),	{ name of condition		}
	con_nwaits	integer,	{ number of waits on this mutex }
	con_nservs	integer,	{ number of services		}
	con_curlen	integer,	{ current length		}
	con_totlen	integer,	{ total queue length		}
	con_maxlen	integer,	{ maximum queue length		}
	con_waittime	float,		{ cumulative wait time (usecs)	}
	con_servtime	float,		{ cumulative service time (usecs)}
	con_maxwait	integer		{ maximum wait time (usecs)	}
	);
    create unique index informix.sysconlstix1 on sysconlst(con_id);
    create unique index informix.sysconlstix2 on sysconlst(con_address);		
    revoke all on informix.sysconlst from public as informix;
    grant select on informix.sysconlst to public as informix;

{ Pools }
    create table informix.syspoollst		{ Internal Use Only		}
	(
	po_id		integer,	{ id of this pool		}
	po_address	int8,	        { address of this pool		}
        po_next		int8,           { pointer to next pool in list  }
        po_prev		int8,           { pointer to prev pool in list  }
	po_lock		integer,	{ lock to synchronise		}
	po_name		char(12),	{ name of pool			}
	po_class	smallint,	{ pool class 1=resident, 2=virtual, 3=message}
	po_flags	smallint,	{ notify if forget to free	}
	po_freeamt	int8,    	{ total amount in free list	}
	po_usedamt	int8,    	{ total amount in used list	}
        po_freelist	int8,           { address of free block list    }
        po_list         int8,           { address of pools block list   }
	po_sid		integer		{ sid of the owner of this pool }
	);
    revoke all on informix.syspoollst from public as informix;
    grant select on informix.syspoollst to public as informix;

{ Segments }
    create table informix.sysseglst		{ Internal Use Only		}
	(
	seg_address	int8,		{ address of segment structure  }
	seg_next	int8,		{ pointer to next segment       }
	seg_prev	int8,		{ pointer to prev segment       }
	seg_class	smallint,	{ segment class			}
	seg_size	int8,		{ size of this segment		}
	seg_osshmid	integer,	{ id of this OS segment in this seg}
	seg_osmaxsize	int8,		{ size of maximum OS segment in this seg}
	seg_osshmkey	integer,	{ shmkey for first OS segment	}
	seg_procid	integer,	{ process id of creator		}
	seg_userid	smallint,	{ usr id of creator		}
	seg_shmaddr	int8,		{ address of segment            }
	seg_ovhd	int8,		{ amount of overhead bytes	}
	seg_lock	int8,		{ lock to synchronise bitmap access}
	seg_nextid	integer,	{ segment id of next seg	}
	seg_bmapsz	int8,		{ size of block map		}
	seg_blkused	int8,		{ no. of used blocks in segment	}
	seg_blkfree	int8		{ no. of free blocks in segment	}
	);
    revoke all on informix.sysseglst from public as informix;
    grant select on informix.sysseglst to public as informix;

{ Dictionary Hash }
    create table informix.sysdic  		{ Internal Use Only		}
	(
	dic_hashno 	smallint,	{ hash-value 			}
	dic_chainno 	smallint,	{ position in hash chain 	}
	dic_partnum 	integer,	{ partition number 		}
	dic_fextsize 	integer,	{ first extent size 		}
	dic_nextsize 	integer,	{ next extent size 		}
	dic_locklevel 	smallint,	{ lock level 			}
	dic_flags 	integer,	{ table flags 			}
	dic_ps          integer,        { table permissions             }
        dic_heapptr	int8,		{ struct heap *DD memory heap   }
	dic_altcount 	integer,	{ alt count 			}
	dic_ncols 	smallint,	{ number of columns 		}
	dic_rowsize 	smallint,	{ row size in bytes 		}
	dic_nindexes 	smallint,	{ number of indexes 		}
	dic_type 	char(1),	{ table type 			}
	dic_nrows 	float,		{ number of rows 		}
	dic_npused 	float,		{ # pages in table 		}
	dic_tabid 	integer,	{ dictionary table id 		}
	dic_majversion 	integer,	{ table major version number 	}
	dic_minversion 	integer,	{ table minor version number 	}
	dic_refcount 	integer,	{ # of references to this entry }
	dic_servername 	char(128),	{ like ddt_servername 		}
	dic_dbname 	char(128),	{ like ddt_dbname 		}
	dic_ownername 	char(32),	{ like ddt_owner 		}
	dic_tabname 	char(128)	{ like ddt_name 		}
	);
    create unique index informix.sysdicidx on sysdic(dic_hashno, dic_chainno);
    revoke all on informix.sysdic from public as informix;
    grant select on informix.sysdic to public as informix;

{ Distribution Hash }
    create table informix.sysdsc  		{ Internal Use Only 		}
	(
	dis_hashno 	integer,	{ hash values of name 		}
	dis_chainno 	smallint,	{ position in chain 		}
	dis_id 		integer,	{ id - other than name 		}
	dis_refcnt 	integer,	{ number of users using entry 	}
	dis_delete 	integer,	{ marked for delete 		}
	dis_heapptr	int8,		{ heap for entry                }
	dis_heapsz 	integer,	{ soze of heap 			}
	dis_servername 	char(128),	{ get  ce_name.fn_servername 	}
	dis_dbname 	char(128),	{ get  ce_name.fn_dbname 	}
	dis_ownername 	char(32),	{ get  ce_name.fn_ownername 	}
	dis_name   	char(257)	{ get  ce_name.fn_name 		}
	);
    create unique index informix.sysdscidx on sysdsc(dis_hashno, dis_chainno);
    revoke all on informix.sysdsc from public as informix;
    grant select on informix.sysdsc to public as informix;

{ Procedure Hash }
    create table informix.sysprc  		{ Internal Use Only 		}
	(
	prc_hashno 	integer,	{ hash values of name 		}
	prc_chainno 	smallint,	{ position in chain 		}
	prc_id 		integer,	{ id - other than name 		}
	prc_refcnt 	integer,	{ number of users using entry 	}
	prc_delete 	integer,	{ marked for delete 		}
	prc_heapptr	int8,		{ heap for entry                }
	prc_heapsz 	integer,	{ soze of heap 			}
	prc_servername 	char(128),	{ get  ce_name.fn_servername 	}
	prc_dbname 	char(128),	{ get  ce_name.fn_dbname 	}
	prc_ownername 	char(32),	{ get  ce_name.fn_ownername 	}
	prc_name   	char(257)	{ get  ce_name.fn_name 		}
	);
    create unique index informix.sysprcidx on sysprc(prc_hashno, prc_chainno);
    revoke all on informix.sysprc from public as informix;
    grant select on informix.sysprc to public as informix;

{ Sqscb }
    create table informix.syssqscb  		{ Internal Use Only 		}
	(
	scb_sessionid	integer,	{ session id			}
        scb_address	int8,        { self address                  }
	scb_feversion	char(4),	{ see sqscb.fevers 		}
	scb_lockmode	smallint,	{ -1: wait, 0: not wait, else: # sec }
					{ see sqscb.waitflag and sqscb.waitsec }
	scb_sqerrno	smallint,	{ see sqtcb.sqerrno 		}
	scb_iserrno	smallint	{ see sqtcb.sqiserrno 		}
	);
    create unique index informix.syssqscbidx on syssqscb ( scb_sessionid DESC );
    revoke all on informix.syssqscb from public as informix;
    grant select on informix.syssqscb to public as informix;

{ Sdblock }
    create table informix.syssdblock  			{ Internal Use Only 			}
	(
	sdb_sessionid		integer,	{ session id                    	}
	sdb_sdbno 		integer,	{ position in array 			}
	sdb_iscurrent 		char(1),	{ current statement? 			}
	sdb_name   		char(128),	{ front-end's name for statement	}
	sdb_id 			smallint,	{ back-end's id for statement 		}
	sdb_flags 		integer,	{ defined below 			}
	sdb_executions		integer,	{ total # of executions			}
	sdb_cumtime		float,		{ total cumulative execution time	}
	sdb_bufreads		integer,	{ total # of buffers read		}
	sdb_pagereads		integer,	{ total # of pages read from disk	}
	sdb_bufwrites		integer,	{ total # of buffers written 		}
	sdb_pagewrites		integer, 	{ total # of pages written		}
	sdb_totsorts		integer,	{ total # of sorts performed 		}
	sdb_dsksorts		integer,	{ total # of sorts requiring disk io 	}
	sdb_sortspmax		integer,	{ max disk space required by a sort 	}
	sdb_cb 			int8,		{ conblock for statement 		}
	sdb_cblist 		int8,		{ list of all cb's in statement 	}
	sdb_heap 		int8,		{ memory heap for this statement	}
	sdb_partnum 		integer,	{ part num for temp blob table 		}
	sdb_isfd 		smallint,	{ file descriptor for the table 	}
	sdb_recnum 		integer,	{ row for blob descriptors 		}
	sdb_sqerrno 		smallint,	{ for fetching, if rows need to be 	}
	sdb_sqiserrno 		smallint,	{ returned to the user first, but 	}
	sdb_sqoffset 		integer,	{ need to set the error in the next 	}
	sdb_errstr 		char(64),	{ fetch statement 			}
	sdb_ntables 		integer,	{ number of table descriptors 		}
	sdb_sqttab 		int8,		{ thread specific tab info 		}
	sdb_asynch_sqerrno 	integer,	{ error reported by asynch thread 	}
	sdb_asynch_sqiserr 	integer,	{ error reported by asynch thread 	}
	sdb_pool 		int8,		{ statement memory pool 		}
	sdb_mutex 		int8,		{ misc lock (to check sd_sqerrno) 	}
	sdb_tgcblist 		int8,		{ list of cbs to be use to build 	}
	sdb_pdq_prio_req 	smallint,	{ requested priority 			}
	sdb_pdq_priority	smallint,	{ currently allowed pdq_priority 	}
	sdb_max_scans 		integer		{ currently allowd # scans 		}
	);
    create unique index informix.syssdblockidx on syssdblock (sdb_sessionid DESC, sdb_sdbno);
    revoke all on informix.syssdblock from public as informix;
    grant select on informix.syssdblock to public as informix;

{ Conblock }
    create table informix.sysconblock  		{ Internal Use Only 		}
	(
	cbl_sessionid	integer,	{ session id                    }
	cbl_sdbno	integer,	{ position in sdblock array 	}
	cbl_conbno	smallint,	{ position in conblock list 	}
	cbl_ismainblock	char(1),	{ main block for statement? 	}
	cbl_selflag	smallint,	{ see cb_selflag (SQ_*) 	}
	cbl_estcost	integer,	{ see cb_estcost 		}
	cbl_estrows	integer,	{ see cb_estsize 		}
	cbl_flags	integer,	{ see cb_flags 			}
	cbl_flags2	integer,	{ see cb_flags2 		}
	cbl_seqscan	smallint,	{ # of SEQUENTIAL SCANs 	}
	cbl_srtscan	smallint,	{ # of SORT SCANs 		}
	cbl_autoindex	smallint,	{ # of AUTOINDEX PATHs 		}
	cbl_index	smallint,	{ # of INDEX PATHs 		}
	cbl_remsql	smallint,	{ # of REMOTE PATHs 		}
	cbl_mrgjoin	smallint,	{ # of MERGE JOINs 		}
	cbl_dynhashjoin	smallint,	{ # of DYNAMIC HASH JOINs 	}
	cbl_keyonly	smallint,	{ # of (Key-Only)s 		}
	cbl_tempfile	smallint,	{ # of Temporary Files 		}
	cbl_tempview	smallint,	{ # of Temp Tables For View 	}
	cbl_secthreads	smallint,	{ # of Secondary Threads 	}
	cbl_stmt	char(32000)	{ current statement 		}
	);
    create unique index informix.sysconblockidx on sysconblock ( cbl_sessionid DESC, cbL_sdbno, cbl_conbno);
    revoke all on informix.sysconblock from public as informix;

{ Opendb }
    create table informix.sysopendb  		{ Internal Use Only 		}
	(
	odb_sessionid	integer,	{ session id                    }
	odb_odbno 	integer,	{ position in opendb array 	}
	odb_dbname      char(128),	{ database name 		}
	odb_iscurrent 	char(1), 	{ no==sdb_current ? 'Y' : 'N' 	}
	odb_islog 	char(1),	{ !logflg ? 'Y' : 'N' 		}
	odb_isansi	char(1),	{ ansiflg ? 'Y' : 'N' 		}
	odb_isolation 	smallint,	{ isolation level see xtype 	}
	odb_usrtype 	char(1),	{ user type (DBA, CONNECT .. 	}
	odb_prior 	smallint,	{ priority 			}
	odb_tmstamp 	integer,	{ timestamp for last access 	}
	odb_lc_collate 	char(36),	{ value for LC_COLLATE 		}
	odb_dbflags 	smallint	{ 1 if DB_EXCLUSIVE 		}
	);
    create unique index informix.sysopendbidx on sysopendb (odb_sessionid DESC, odb_odbno);
    revoke all on informix.sysopendb from public as informix;
    grant select on informix.sysopendb to public as informix;

{ SQL cache profile information }
    create table informix.syssqlcacheprof        { Internal Use Only           }
        (
        id            integer,        { cache id                      }
        name          char(128),      { Cache Name                    }
        hits          int8,           { # of times item found in cache }
        misses        int8,           { # of times retrieved from disk}
        removed       int8,           { # of times an item is remove  }
        mem_used      int,            { Used memory by cache          }
        mem_free      int,            { Unused memory by cache        }
        total_entries int,            { total # of entries in cache   }
        inuse_entries int             { # of entries in use by cache  }
        );
    create unique index informix.syssqlcacheprofidx on syssqlcacheprof (id);
    revoke all on informix.syssqlcacheprof from public as informix;
    grant select on informix.syssqlcacheprof to public as informix;

{ SQL state and statement }
    create table informix.syssqlstat		{ Internal Use Only             }
	(
	sqs_sessionid 	integer,	{ session id       		}
	sqs_dbname	char(128),	{ database name 		}
	sqs_iso		smallint,	{ Isolation level		}
	sqs_lockmode	smallint,	{ lock mode			}
	sqs_sqlerror	smallint,	{ sql error of last SQL stmt	}
	sqs_isamerror	smallint,	{ isam error of last SQL stmt	}
	sqs_feversion	char(4),	{ FE Version			}
	sqs_statement	char(200)	{ last SQL statement		}
	);
    revoke all on informix.syssqlstat from public as informix;

{ LRU buffers }
    create table informix.syslrus
      (
        bpoolindx   smallint,           { Bufferpool index              }
        lru_num     int,                { Number of Lru Queue           }
        lru_nfree   int,                { Free Buffers in Lru Queue     }
        lru_nmod    int                 { Modified Buffers in Lru Queue }
      );
    create unique index informix.syslrusix1 on syslrus(bpoolindx, lru_num);
    revoke all on informix.syslrus from public as informix;
    grant select on informix.syslrus to public as informix;

{ Checkpoint history }
    create table informix.syscheckpoint
    (
         intvl		   int,		{checkpoint interval		}
	 type		   char(12),	{checkpoint type		}
	 caller		   char(10),	{caller				}
	 clock_time	   int,		{time of day of ckpt	        }
	 crit_time	   float,	{time spent in wait4critex 	}
	 flush_time	   float,	{time spent flushing pages to disk }
	 cp_time 	   float,	{time from cpkt_pending to done	}
	 n_dirty_buffs	   int,		{number of dirty buffers	}
	 plogs_per_sec	   int,		{avg # pages plogged		}
	 llogs_per_sec	   int,		{avg # pages logged		}
	 dskflush_per_sec  int,		{avg # pages dskflushed		}
	 ckpt_logid	   int,		{LSN of ckpt			}
	 ckpt_logpos	   int,		{LSN of ckpt			}
	 physused	   int,		{total pages plogged in ckpt	}
	 logused	   int,		{total pages llogged in ckpt	}
	 n_crit_waits	   int,		{ # of crit section waiters	}
	 tot_crit_wait	   float,	{total time spent waiting for crit }
	 longest_crit_wait float,	{longest crit wait		}
	 block_time	   float	{blocked time			}
    );       
    revoke all on informix.syscheckpoint from public as informix;
    grant select on informix.syscheckpoint to public as informix;

{ network IO Information }
    create table informix.sysnetworkio
      (
        net_id           int,                { Net ID                        }
        sid              int,                { session id                    }
        net_netscb       int8,               { address of netscb             }
        net_client_type  int,                { client type                   }
        net_client_name  char(12),           { client protocal name          }
        net_read_cnt     int8,               { number of read operations     }
        net_read_bytes   int8,               { # of bytes txfr to server     }
        net_write_cnt    int8,               { number of write operations    }
        net_write_bytes  int8,               { # of bytes txfr to client     }
        net_open_time    int,                { time connection was made      }
        net_last_read    int,                { time of last network read     }
        net_last_write   int,                { time of last network write    }
        net_state        int,                { state of network connection   }
        net_options      int,                { sqlhost options               }
        net_prot_id      int,                { id for protocal name          }  
        net_protocol     char(10),           { prtocal name                  }
        net_server_fd    int,                { poll server fd                }
        net_poll_thread  int                 { poll thread id                }
      );
    create unique index informix.sysnetworkio1 on sysnetworkio(net_id);
    revoke all on informix.sysnetworkio from public as informix;
    grant select on informix.sysnetworkio to public as informix;

    create table informix.sysnetglobal                { Internal Use Only            }
      (
        ng_reads         int8,               { Number of network reads       }
        ng_writes        int8,               { Number of network writes      }
        ng_his_read_count     int8,          { Network reads by discon users }
        ng_his_write_count    int8,          { Network writes by discon users }
        ng_his_reads_bytes    int8,          { Amount of data txfr by discon users }
        ng_his_writes_bytes   int8,          { Amount of data txfr by discon users }
        ng_connects      int8,               { Number of connections         }
        ng_num_netscbs   int,                { Number of netscbs             }
        ng_max_netscbs   int,                { Max number of netscbs         }
        ng_free_thres    int,                { Free threshold                }
        ng_free_cnt      int,                { Free count                    }
        ng_wait_thres    int,                { Wait threshold                }
        ng_wait_cnt      int,                { Wait count                    }
        ng_pvt_thres     int,                {                               }
        ng_netbuf_size   int,                { Network buffer size           }
        ng_buf_alloc     int,                {                               }
        ng_buf_alloc_max int,                {                               }
        ng_netscb_id     int                 { Next netscb id                }
      );
    revoke all on informix.sysnetglobal from public as informix;
    grant select on informix.sysnetglobal to public as informix;

    create table informix.sysnetclienttype              { Internal Use Only          }
      (
        nc_cons_allowed    int,                { Are connections allowed    }
        nc_accepted        int,                { Number of connections      }
        nc_rejected        int,                { Number of cons rejected    }
        nc_reads           int8,               { Number of network reads    }
        nc_writes          int8,               { Number of network writes   }
        nc_name            char(18)            { Name                       }
      );
    revoke all on informix.sysnetclienttype from public as informix;
    grant select on informix.sysnetclienttype to public as informix;

{ system environment variable }
    create table informix.sysenv
        (
        env_id          integer,        { unique numeric identifier }
        env_name        char(128),      { env. variable name        }
        env_value       char(512)       { env. variable value       }
        );
    create unique index informix.sysenvix1 on sysenv(env_id);
    revoke all on informix.sysenv from public as informix;
    grant select on informix.sysenv to public as informix;

{ session environment variable }
    create table informix.sysenvses
        (
        envses_sid         integer,        { session id                }
        envses_id          integer,        { env. variable  identifier }
        envses_name        char(128),      { env. variable name        }
        envses_value       char(512)       { env. variable value       }
        );
    create unique index informix.sysenvsesix1 on sysenvses(envses_sid, envses_id);
    revoke all on informix.sysenvses from public as informix;
    grant select on informix.sysenvses to public as informix;

{ online log }
    create table informix.sysonlinelog
        (
        offset             int8,           { file offset               }
        next_offset        int8,           { file offset to next msg   }
        line               char(4096)      { message file line         }
        );
    create unique index informix.sysonlinelogix1 on sysonlinelog(offset);
    revoke all on informix.sysonlinelog from public as informix;
    grant select on informix.sysonlinelog to public as informix;

{ bar act log }
    create table informix.sysbaract_log
        (
        offset             int8,           { file offset               }
        next_offset        int8,           { file offset to next msg   }
        line               char(4096)      { message file line         }
        );
    create unique index informix.sysbaract_logix1 on sysbaract_log(offset);
    revoke all on informix.sysbaract_log from public as informix;
    grant select on informix.sysbaract_log to public as informix;

{ Index page logging status at source server}
    create table informix.sysipl		
	(
	ipl_status	integer,{ Index page logging status }
	ipl_time	integer	{time at which index page logging was enabled}
	);
    revoke all on informix.sysipl from public as informix;
    grant select on informix.sysipl to public as informix;

{ SMX connection information}
    create table informix.syssmx
    (
    address  int8,{ SMX pipe address }
    name    char(128), {Target server name}
    encryption_status  char(20), {Enabled/Disabled}
    buffers_sent  integer,{ Number of buffers sent }
    buffers_recv  integer,{ Number of buffers received }
    bytes_sent  int8,{ Number of bytes sent }
    bytes_recv  int8,{ Number of bytes received }
    reads  integer,{ Number of read calls}
    writes  integer,{ Number of write calls}
    retries  integer{ Number of write call retries }
    );
    revoke all on informix.syssmx from public as informix;
    grant select on informix.syssmx to public as informix;

{ SMX session information}
    create table informix.syssmxses
    (
    name    char(128), {Target server name}
    address  int8,{ SMX session address }
    client_type  char(20),{ SMX client type }
    reads  integer,{ Number of read calls }
    writes  integer{ Number of write calls }
    );
    revoke all on informix.syssmxses from public as informix;
    grant select on informix.syssmxses to public as informix;

{RSS Server related statistics at RSS primary server}
    create table informix.syssrcrss
    (
    address  int8,{ RSS control block address }
    server_name   		char(128),	{Database server name}
    server_status		char(20),	{Quiescent/Active/Inactive}
    connection_status	char(20),	{Connected/Disconnected}
    log_transmission_status	char(20),	{Active/Blocked}
    next_page_tosend_log_uniq	integer,{log unique id of next page to send }
    next_page_tosend_log_page	integer,{page number of next page to send}
    seq_tosend			integer,	{Sequence id of last buffer sent}
    last_seq_acked		integer		{Sequence id of last buffer acked}
    );
    revoke all on informix.syssrcrss from public as informix;
    grant select on informix.syssrcrss to public as informix;

{RSS Server related statistics at RSS server}
    create table informix.systrgrss
    (
    address  			int8,		{ RSS control block address }
    source_server		char(128),	{Source server serving the RSS server}
    connection_status	char(20),	{Connected/Disconnected}
    last_received_log_uniq	integer,	{log unique id of last log page received}
    last_received_log_page	integer,	{page number of last log page received}
    last_seq_received		integer,	{Sequence id of last buffer received}
    last_seq_acked		    integer		{Sequence id of last buffer acked}
    );
    revoke all on informix.systrgrss from public as informix;
    grant select on informix.systrgrss to public as informix;

{Log capture information for RSS server at RSS primary server}
    create table informix.sysrsslog
    (
    server_name			char(128),	{ Server name }
    from_cache	        integer,{Total pages read from cache}
    from_disk	        integer,{Total pages read from disk}
    logpages_tossed		integer		{Total number of log pages not written to log buffer cache}
    );
    revoke all on informix.sysrsslog from public as informix;
    grant select on informix.sysrsslog to public as informix;

{SDS related statistics at source server}
    create table informix.syssrcsds
    (
    address  int8,{ SDS server control block address }
    server_name         char(128),  {Database server name}
    server_status       char(20),   {Quiescent/Active/Inactive}
    connection_status   char(20),   {Connected/Disconnected}
    last_sent_log_uniq   integer,{log unique id of last page to sent }
    last_sent_logpage   integer,{page number of last page to sent}
    last_flushed_log_uniq   integer,{log unique id of last page flushed }
    last_flushed_logpage   integer,{page number of last page flushed}
    last_acked_lsn_uniq   integer,{log unique id of last LSN acked}
    last_acked_lsn_pos   integer,{log position of last LSN acked}
    seq_tosend          integer,    {Sequence id of next buffer to send}
    last_seq_acked      integer,     {Sequence id of last buffer acked}
    timeof_lastack      integer,    {time of last LSN acked}
    totallsn_posted     integer,     {total LSNs posted}
    totallsn_sent     integer,     {total LSNs sent}
    totalpageflush_posted     integer,    {total page flush buffers posted}
    totalpageflush_sent     integer     {total page flush buffers sent}
    );
    revoke all on informix.syssrcsds from public as informix;
    grant select on informix.syssrcsds to public as informix;

{SDS related statistics at SDS server}
    create table informix.systrgsds
    (
    address             int8,       { SDS server control block address }
    source_server       char(128),  {Source server serving the SDS server}
    connection_status   char(20),   {Connected/Disconnected}
    last_received_log_uniq  integer,    {log unique id of last log page received
 }
    last_received_log_page  integer,    {page number of last log page received}
    next_lpgtoread_log_uniq  integer,   {log unique id of next log page to read}
    next_lpgtoread_log_page  integer,    {page number of next log page to read}
    last_acked_lsn_uniq   integer,{log unique id of last LSN acked}
    last_acked_lsn_pos   integer,{log position of last LSN acked}
    last_seq_received       integer,    {Sequence id of last buffer received}
    last_seq_acked          integer,     {Sequence id of last buffer acked}
    cur_pagingfile      char(640),   {current paging file name}
    cur_pagingfile_size int8,   {current paging file size}
    old_pagingfile      char(640),   {old paging file name}
    old_pagingfile_size int8   {old paging file size}
    );
    revoke all on informix.systrgsds from public as informix;
    grant select on informix.systrgsds to public as informix;

{Cluster related statistics}
create table informix.syscluster
    (
    name                char(128),  { node name }
    role                char(1),    { primary/secondary role }
    syncmode            char(8),    { sync/async }
    nodetype            char(8),    { HDR/RSS/SDS ? }
    supports_updates    char(1),    { updatable }
    server_status       char(32),   { current status of node }
    connection_status   char(32),   { current connection status }
    delayed_apply       integer,    { time delay of apply }
    stop_apply          char(24),   { stop apply value }
    logid_sent          integer,    { unique log id of last log page sent}
    logpage_sent        integer,    { page number of last log page sent }
    logid_acked         integer,    { unique log id of last ACK }
    logpage_acked       integer,    { page number of last ACK }
    ack_time            datetime year to second, { time of last ACK }
    sdscycle            integer,    { sds cycle number }
    sdscycle_acked      integer
    );
    revoke all on informix.syscluster from public as informix;
    grant select on informix.syscluster to public as informix;

{stream of replication statistics}
create table informix.sysrepstats(
    repstats_type        integer,    { Event class id } 
    repstats_subtype     integer,    { Sub event id}
    repstats_time        integer,    { Time at which event was fired}
    repstats_ver         integer,    { Version number for event data}
    repstats_desc        lvarchar    { Event data }
    );
    revoke all on informix.sysrepstats from public as informix;
    grant select on informix.sysrepstats to public as informix;
    grant insert on informix.sysrepstats to public as informix;

{register for replication event stream }
create table informix.sysrepevtreg(
    evt_bitmap    integer,  {Event class id bitmap } 
    evt_timeout	  integer,  {Maximum time that client can wait for event data}
    evt_hwm       integer,  {Pending event list high water mark }
    evt_info      char(256) {Event information. Not yet implemented}
    );
    revoke all on informix.sysrepevtreg from public as informix;
    grant insert on informix.sysrepevtreg to public as informix;

{ HA server type }
    create table informix.sysha_type
    (
    ha_type      int,
    ha_primary   char(128 ),
    ha_secondary char(128 )
    );
    revoke all on informix.sysha_type from public as informix;
    grant select on informix.sysha_type to public as informix;

{ HA server latency information }
    create table informix.sysha_lagtime
	(
	lt_secondary		char (128),
	lt_time_last_update	int,
	lt_lagtime_1		float,
	lt_lagtime_2		float,
	lt_lagtime_3		float,
	lt_lagtime_4		float,
	lt_lagtime_5		float,
	lt_lagtime_6		float,
	lt_lagtime_7		float,
	lt_lagtime_8		float,
	lt_lagtime_9		float,
	lt_lagtime_10		float,
	lt_lagtime_11		float,
	lt_lagtime_12		float,
	lt_lagtime_13		float,
	lt_lagtime_14		float,
	lt_lagtime_15		float,
	lt_lagtime_16		float,
	lt_lagtime_17		float,
	lt_lagtime_18		float,
	lt_lagtime_19		float,
	lt_lagtime_20		float
	);
    revoke all on informix.sysha_lagtime from public as informix;
    grant select on informix.sysha_lagtime to public as informix;

{ HA server workload information }
    create table informix.sysha_workload
    (
	wl_secondary		char (128),
	wl_time_last_update	int,
	wl_ttype			char(12),
	wl_workload_1		float,
	wl_workload_2		float,
	wl_workload_3		float,
	wl_workload_4		float,
	wl_workload_5		float,
	wl_workload_6		float,
	wl_workload_7		float,
	wl_workload_8		float,
	wl_workload_9		float,
	wl_workload_10		float,
	wl_workload_11		float,
	wl_workload_12		float,
	wl_workload_13		float,
	wl_workload_14		float,
	wl_workload_15		float,
	wl_workload_16		float,
	wl_workload_17		float,
	wl_workload_18		float,
	wl_workload_19		float,
	wl_workload_20		float
    );
    revoke all on informix.sysha_workload from public as informix;
    grant select on informix.sysha_workload to public as informix;

{ Connection Manager }
    create table informix.syscmsmtab
	(
	address 		int8,       	{ CM address 		}
	sid			integer,	{ CM session id		}
	name			char(128),	{ CM name 		}
	host			char(256),	{ host name		}
	flag			integer		{ Arbitrator flag	}
	);
    revoke all on informix.syscmsmtab from public as informix;
    grant select on informix.syscmsmtab to public as informix;

    create table informix.syscmsmunit
	(
	address 		int8,       	{ CM cluster address 	}
	sid			integer,	{ CM session id		}
	unit			char(128),	{ unit name		}
	type			char(128),	{ unit type 		}
	servers			char(1024),	{ unit servers		}
	foc			char(1024),	{ FOC define 		}
	flag			integer		{ Arbitrator flag	}
	);
    revoke all on informix.syscmsmunit from public as informix;
    grant select on informix.syscmsmunit to public as informix;

    create table informix.syscmsmsla
	(
	address 		int8,       	{ CMSLA address 	}
	sid			integer,	{ CM session id		}
	unit			char(128),	{ cluster name 		}
	sla_name		char(128),	{ SLA name 		}
	sla_define		char(1024),	{ SLA define		}
	connections		integer		{ redirected connections}
	);
    revoke all on informix.syscmsmsla from public as informix;
    grant select on informix.syscmsmsla to public as informix;

{ dual table }
    create table informix.sysdual
        (
        dummy             char(1)
        );
    revoke all on informix.sysdual from public as informix;
    grant select on informix.sysdual to public as informix;

{ sqlhosts }
    create table informix.syssqlhosts
        (
        dbsvrnm             char(129),
        nettype             char(17),
        svrtype             char(9),
        netprot             char(9),
        hostname            char(257),
        svcname             char(129),
        options             char(257),
        svrsecurity         smallint,
        clntsecurity        smallint,
        netoptions          smallint,
        netbuf_size         integer,
        connmux_option      smallint,
        svrgroup            char(129),
        endofgroup          char(129),
        redirector          smallint,
        svrid               integer,
        pamauth             integer,
        authtoken           char(256)
        );
    revoke all on informix.syssqlhosts from public as informix;
    grant select on informix.syssqlhosts to public as informix;

    create table  informix.syslicenseinfo      { Internal Use Only      }
        (
        version           char(12),    { IDS version }
        week              smallint,    { Week of the information }
        year              smallint,    { Year of the information }
        max_cpu_vps       smallint,    { Max number of cpu vps }
        max_vps           smallint,    { Max number of vps }
        max_conns         integer,     { Max # of user connected }
        max_sec_conns     integer,     { Max # of secondary user }
        max_sds_conns     integer,     { Max # of sds users }
        max_sds_clones    smallint,    { Max # of sds clones }
        max_rss_clones    smallint,    { Max # of rss clones }
        total_size        integer,     { max disk space (MB) }
        total_size_used   integer,     { max disk space used (MB) }
        max_memory        integer,     { Max memory allocated (MB) }
        max_memory_used   integer,     { Max memory used (MB) }
        feature_flags     integer,     { Feature Flags }
        feature_flags2    integer      { Feature Flags2 }
        );
    revoke all on informix.syslicenseinfo from public as informix;
    grant select on informix.syslicenseinfo to public as informix;

{ machine and operating information }
    create table informix.sysmachineinfo
        (
        os_name             char(128),
        os_release          char(128),
        os_nodename         char(128),
        os_version          char(128),
        os_machine          char(128),
        os_num_procs        smallint,
        os_num_olprocs      smallint,
        os_pagesize         int8,
        os_mem_total        int8,
        os_mem_free         int8,
        os_open_file_lim    int8,
        os_shmmax           int8,
        os_shmmin           int8,
        os_shmids           int8,
        os_shmnumsegs       int8,
        os_semmap           int8,
        os_semids           int8,
        os_semnum           int8,
        os_semundo          int8,
        os_semnumperid      int8,
        os_semops           int8,
        os_semundoperproc   int8,
        os_semundosize      int8,
        os_semmaxvalue      int8
        );
    revoke all on informix.sysmachineinfo from public as informix;
    grant select on informix.sysmachineinfo to public as informix;

{ I/O historical information }
    create table informix.sysiohistory
        (
        address             bigint,
        gfd                 int,
        iskaio              int,
        open_mode           int,
        open_time           bigint,
        path                char(256),
        minute              int,
        time                bigint,
        total_read_ops      bigint,
        total_read_time     float,
        read_ops_minute     bigint,
        read_time_minute    float,
        avg_read_time_minute  float,

        total_write_ops     bigint,
        total_write_time    float,
        write_ops_minute    bigint,
        write_time_minute   float,
        avg_write_time_minute  float,
        
        total_lseek_time    float,
        lseek_time_minute   float
        );
    revoke all on informix.sysiohistory from public as informix;
    grant select on informix.sysiohistory to public as informix;

{ low memory manger information }
    create table syslowmemorymgr
        (
        lmm_memory_limit        int8,
        lmm_memory_used         int8,
        lmm_start_threshold     int8,
        lmm_stop_threshold      int8,
        lmm_idle_time           int8,
        lmm_thread_id           int8,
        lmm_total_killed        int8,
        lmm_segments_over       int8,
        lmm_kill_active         int8,
        lmm_kill_count          int8,
        lmm_kill_last_time      int8,
        lmm_killall_active      int8,
        lmm_killall_count       int8,
        lmm_killall_last_time   int8,
        lmm_reduce_active       int8,
        lmm_reduce_count        int8,
        lmm_reduce_last_time    int8,
        lmm_restore_active      int8,
        lmm_restore_count       int8,
        lmm_restore_last_time   int8
        );
    revoke all on syslowmemorymgr from public;
    grant select on syslowmemorymgr to public;

{ storage manager operations }
    create table informix.sysstoragemgr
        (
        smgr_id             int8,	{ StorageMGR op id }
        smgr_dbname         char(128),	{ Database name }
        smgr_owner          char(32),	{ Owner name }
        smgr_tabname        char(128),	{ Table name }
        smgr_dblocale       char(32),	{ Database locale }
        smgr_optype         int8,	{ StorageMGR op type }
        smgr_opdesc         char(128),	{ StorageMGR op desc }
        smgr_numrows        int8,	{ # Rows at start of this cmd }
        smgr_numpages       int8,	{ # Pages at start of this cmd }
        smgr_rowsprocessed  int8,	{ # Rows processed }
        smgr_numfrags       int8,	{ # Fragments }
        smgr_fraglist       char(1024),	{ List of fragments }
        smgr_curpartnum     int8,	{ Current partnum }
        smgr_curpage        int8,	{ Current page }
        smgr_currowid       int8,	{ Current rowid }
        smgr_starttime      int8,	{ Start time }
        smgr_elapsedtime    int8,	{ Elapsed time }
        smgr_remainingtime  int8,	{ Estimated time remaining }
        smgr_rowspersec     float	{ # Rows processed per sec }
        );
    revoke all on informix.sysstoragemgr from public as informix;
    grant select on informix.sysstoragemgr to public as informix;

{ SQL History Trace Summary Info }
    create table informix.syssqltrace_info       { Internal Use Only             }
        (
        flags           integer,        { sql trace flags }
        ntraces         integer,        { Number of trace items }
        tracesize       integer,        { Size of each trace item }
        duration        integer,        { Trace buffer duration in Sec }
        sqlseen         int8,           { sql process by trace buffer }
        starttime       integer,        { start time of the trace buffer }
        memoryused      int8            { Memory used by SQL tracing }
        );
    revoke all on informix.syssqltrace_info from public as informix;

{ SQL History Trace }
    create table informix.syssqltrace
        (
        sql_id            int8,
        sql_address       int8,
        sql_sid           int,
        sql_uid           int,
        sql_stmttype      int,
        sql_stmtname      char(40),
        sql_finishtime    int,
        sql_begintxtime   int,
        sql_runtime       float,
        sql_pgreads       int,
        sql_bfreads       int,
        sql_rdcache       float,
        sql_bfidxreads    int,
        sql_pgwrites      int,
        sql_bfwrites      int,
        sql_wrcache       float,
        sql_lockreq       int,
        sql_lockwaits     int,
        sql_lockwttime    float,
        sql_logspace      int,
        sql_sorttotal     int,
        sql_sortdisk      int,
        sql_sortmem       int,
        sql_executions    int,
        sql_totaltime     float,
        sql_avgtime       float,
        sql_maxtime       float,
        sql_numiowaits    int,
        sql_avgiowaits    float,
        sql_totaliowaits  float,
        sql_rowspersec    float,
        sql_estcost       int,
        sql_estrows       int,
        sql_actualrows    int,
        sql_sqlerror      int,
        sql_isamerror     int,
        sql_isollevel     int,
        sql_sqlmemory     int,
        sql_numiterators  int,
        sql_database      char(128),
        sql_numtables     int,
        sql_tablelist     char(4096),
        sql_statement     char(16000),
        sql_stmtlen       int,
        sql_stmthash      int8,
        sql_pdq           smallint,
        sql_num_hvars     smallint,
        sql_dbspartnum    int,
        sql_aqt           char(40),     { AQT name if matching succeeded }
        sql_aqtinfo       int           { > 0 : AQT matching / offloading time
                                          < 0 : matching / offloading error code
                                          = 0 : no matching }
        );
    revoke all on informix.syssqltrace from public as informix;
    create unique index informix.syssqltrace_idx1 on syssqltrace(sql_id DESC);
    create unique index informix.syssqltrace_idx2 on syssqltrace(sql_address DESC);

{ SQL History Trace iterator list}
    create table informix.syssqltrace_iter
        (
        sql_id            int8,
        sql_address       int8,
        sql_itr_address   int8,
        sql_itr_id        int,
        sql_itr_left      int,
        sql_itr_right     int,
        sql_itr_cost      int,
        sql_itr_estrows   int,
        sql_itr_numrows   int,
        sql_itr_type      int,
        sql_itr_misc      int,
        sql_itr_info      char(256),
        sql_itr_time      float,
        sql_itr_partnum   int,
	sql_itr_sender    int,
	sql_itr_nxtsender int
        );
    revoke all on informix.syssqltrace_iter from public as informix;
    create index informix.syssqltrace_iter_idx1 on 
                        syssqltrace_iter(sql_id DESC);
    create index informix.syssqltrace_iter_idx2 on 
                        syssqltrace_iter(sql_address DESC);

{ SQL History Trace Host Variables list}
    create table informix.syssqltrace_hvar
        (
        sql_id            int8,
        sql_address       int8,
        sql_hvar_id       int,
        sql_hvar_flags    int,
        sql_hvar_typeid   int,
        sql_hvar_xtypeid  int,
        sql_hvar_ind      int,
        sql_hvar_type     char(128),
        sql_hvar_data     char(8192)
        );
    revoke all on informix.syssqltrace_hvar from public as informix;
    create index informix.syssqltrace_hvar_idx1 on 
                        syssqltrace_hvar(sql_id DESC);
    create index informix.syssqltrace_hvar_idx2 on 
                        syssqltrace_hvar(sql_address DESC);

{ session applicaiton variables }
    create table informix.syssesappinfo
        (
        sesapp_sid         integer,        { session id                }
        sesapp_name        char(128),      { appl variable name        }
        sesapp_value       char(512)       { appl variable value       }
        );
    create index informix.sysesesapp_idx1 on syssesappinfo(sesapp_sid DESC);
    revoke all on informix.syssesappinfo from public as informix;
    grant select on informix.syssesappinfo to public as informix;

{ Shared memory values }   
    create table informix.sysshmvals
      (
         sh_mode           int,         { turbo mode number             }
         sh_boottime       int,         { boot time of day              }
         sh_pfclrtime      int,         { time profilers were last clr  }
         sh_curtime        int,         { current mt_time               }
         sh_bootstamp      int,         { boot time stamp               } 
         sh_stamp          int,         { current time stamp            }
         sh_mainlooptcb    int8,	{ address of main daemon thread }
         sh_sysflags       int,         { system operating flags        }
         sh_maxchunks      int,         { size of chunk table           }
         sh_maxdbspaces    int,         { size of dbspace table         }
         sh_maxuserthreads int,         { max # of user structures      }
         sh_maxtrans       int,         { max # of trans structures     }
         sh_maxlocks       int,         { # of locks total              }
         sh_maxlogs        int,         { size of log table             }
         sh_nbuffs         int,         { # of buffers total            }
         sh_pagesize       int,         { buffer size in bytes          }
         sh_nlrus          int,         { # of lru queues               }
         sh_maxdirty       float,       { LRU can have this % dirty pages }
         sh_mindirty       float,       { LRU has % dirty pages after clean }
         sh_ncleaners      int,         { # of cleaning/flushing procs  } 
         sh_longtx         int,         { the long transaction flag     }
         sh_optstgbsnum    int,         { Subsystem Staging Blobspace   }
         sh_cpflag         int,         { TRUE => doing checkpoint      }
         sh_rapages        int,         { Number of pages to read ahead }
         sh_rathreshold    int,         { When to start next read ahead }
         sh_lastlogfreed   int,         { last log (id) written to tape }
         sh_rmdlktout      int,         { max timeout when distributed  }
         sh_narchivers     int,         { number of active archives     }
         sh_maxpdqpriority int,		{ max pdqpriority               }
	 sh_curmaxcons     int,         { max #connections in this run  }
	 sh_ovlmaxcons     int          { max #connections since server init  }
         );       
    revoke all on informix.sysshmvals from public as informix;
    grant select on informix.sysshmvals to public as informix;

{ MGM Info }

  create table informix.sysmgminfo               { Internal Use Only             }
        (
        max_priority      integer,      { max pdqpriority allowed }
        max_query         integer,      { max # of active queries allowed }
        active            integer,      { # of active mgm queries }
        ready             integer,      { # of ready mgm queries }
        early_act_cnt     integer,      { # of queries activate early }
        total_mem         integer,      { Total mgm memory }
        avail_mem         integer,      { Free mgm memory }
        quantum_mem       integer,      { mgm memory quantum }
        ds_query_mem      integer,      { non-pdq query memory }
        total_seq         integer,      { Total number of sequential scans }
        avail_seq         integer,      { Unused sequential scans }
        quantum_seq       integer,      { sequential scans quantum }
        min_free_mem      integer,      { min free mgm memory }
        avg_free_mem      float,        { avg free mgm memory }
        std_free_mem      float,        { std free mgm memory }
        min_free_seq      integer,      { min free mgm sequential scans }
        avg_free_seq      float,        { avg free mgm sequential scans }
        std_free_seq      float,        { std free mgm sequential scans }
        max_active        integer,      { max active mgm sql operations }
        cnt_active        integer,      { number of active mgm sql operations }
        avg_active        float,        { max active mgm sql operations } 
        std_active        float,        { std active mgm sql operations }
        max_ready         integer,      { max ready mgm sql operations }
        cnt_ready         integer,      { number of ready mgm sql operations }
        avg_ready         float,        { avg ready mgm sql operations }
        std_ready         float         { std ready mgm sql operations }
        );
    revoke all on informix.sysmgminfo from public as informix;

{ MGM Gates }

  create table informix.sysmgmgates		{ Internal Use Only }
        (
	gate_no		integer,	{ gate number }
	gate_name	char(12),	{ gate name }
	gate_wait_cnt	integer		{ number of queries waiting at gate }
        );
    revoke all on informix.sysmgmgates from public as informix;
    create unique index informix.sysmgmgatesidx on sysmgmgates(gate_no);

{ MGM Queries }

  create table informix.sysmgmquery		{ Internal Use Only }
        (
	address		int8,		{ address of query }
	sid		integer,	{ session id }
	tid		integer,	{ thread id }
	gate		integer,	{ Gate # at which query is waiting }
	priority	integer,	{ priority of query }
	granted_mem	integer,	{ memory currently granted }
	reserved_mem	integer,	{ memory reserved }
	granted_seq	integer,	{ # of scan threads currently used }
	reserved_seq	integer		{ # of scan threads allocated }
        );
    revoke all on informix.sysmgmquery from public as informix;

{ C2 Audit info }
  create table informix.sysadtinfo		{ Internal Use Only		}
	(
	adtmode		integer,	{ Current audit level		}
	adterr		integer,	{ Action on errors		}
	adtsize		integer,	{ Max size of audit trail }
	adtpath  	char(256),	{ Dir to send audit records to	}
	adtfile		integer,	{ File within dir to write to	}
	adtrows		integer		{ granularity to audit - regular}
					{ or FGA 			}
	);
    revoke all on informix.sysadtinfo from public as informix;
    grant select(adtrows) on informix.sysadtinfo to public as informix; 

{ C2 Audit call }
    create table informix.syscrtadt		{ Internal Use Only		}
	(
	event		integer,	{ Event to audit		}
	result		integer,	{ Success or Failure		}
	data		char(256)	{ Additional data to audit	}
	);
    revoke all on informix.syscrtadt from public as informix;

{Proxy Distributor information}
	create table informix.sysproxydistributors
	(
	node_name  		  char(128), {Name of connected node}
	proxy_id 		  integer,   {Proxy distributor ID}
	transaction_count integer,   {Number of transactions currently being processed}
	hot_row_total     integer    {Total number of hot rows ever handled by the distributor}
	);
	revoke all on informix.sysproxydistributors from public as informix;
	grant select on informix.sysproxydistributors to public as informix;

{Proxy Agent information}
	create table informix.sysproxyagents
	(
	tid 				integer,  {ID of the proxy agent thread}
	flags 				integer,  {flags of thread}
	proxy_id 			integer,  {Proxy distributor ID}
	source_session_id 	integer,  {ID of session on source node}
	proxy_txn_id 		integer,  {Number of current transaction}
	current_seq 		integer,  {Sequence number of current operation}
	sqlerrno 			smallint, {SQL error number}
	iserrno 			smallint  {ISAM/RSAM error number}
	);
	revoke all on informix.sysproxyagents from public as informix;
	grant select on informix.sysproxyagents to public as informix;

{Proxy Session Information}
     create table informix.sysproxysessions
     (
     session_id       integer, {Proxy session ID}
     proxy_id 		  integer, {Proxy distributor ID}
     proxy_tid 	  	  integer, {ID of the proxy agent thread}
     proxy_txn_id 	  integer, {Number of current transaction}
     current_seq 	  integer, {Sequence number of current operation}
     pending_ops 	  integer, {Number of pending operations}
     reference_count  integer  {Reference count}
     );
	revoke all on informix.sysproxysessions from public as informix;
	grant select on informix.sysproxysessions to public as informix;

{Proxy transactions information}
	create table informix.sysproxytxns
	(
	proxy_id         integer, {Proxy distributor ID}
	proxy_txn_id     integer, {Number of current transaction}
	reference_count  integer, {Reference count}
	pending_ops      integer, {Number of pending operations}
	proxy_sid        integer  {}
	);
	revoke all on informix.sysproxytxns from public as informix;
	grant select on informix.sysproxytxns to public as informix;

{Proxy transaction operations information}
	create table informix.sysproxytxnops
	(
	proxy_id         integer,   {Proxy distributor ID}
	proxy_txn_id     integer,   {Number of current transaction}
	sequence_number  integer,   {Sequence number of current operation}
	operation_type   CHAR(10),  {Type of operation}
	rowidn           integer,   {Row ID}
	table            CHAR(128), {Table name}
	sqlerrno         smallint   {SQL error number}
	);
	revoke all on informix.sysproxytxnops from public as informix;
	grant select on informix.sysproxytxnops to public as informix;

{ file information }
    create table informix.sysfileinfo
        (
        fileid              integer,
        directory           char(256),
        filename            char(256),
        version             char(256),
        fullname            char(512)
        );
    create index informix.sysfileinfo_idx1 on sysfileinfo (fileid);
    revoke all on informix.sysfileinfo from public as informix;
    grant select on informix.sysfileinfo to public as informix;

{ AQTs }
    create table informix.sysaqt
        (
        aqt                 char(40),  { AQT name }
        aqttabid            integer,   { AQT tabid }
        dbname              char(128), { database name }
        dwa                 char(128), { DWA instance name }
        factid              integer,   { tabid of fact table }
        ntab                integer,   { number of tables in AQT }
        matched             integer,   { number of times AQT has been matched }
        enabled             char(1),   { AQT is enabled (1) or disabled (0) }
        virtual             char(1)    { AQT is virtual (1) or not (0) }
        );
    revoke all on informix.sysaqt from public as informix;
    grant select on informix.sysaqt to public as informix;

{ AQT join predicates }
    create table informix.sysaqtjp
        (
        aqt                 char(40),  { AQT name }
        dbname              char(128), { database name }
        jd                  integer,   { join descriptor sequence number }
        jp                  integer,   { join predicate sequence number }
        side                char(1),   { side: left (L) or right (R) }
        tabid               integer,   { tabid of table }
        colno               integer,   { column number of column }
        alias               char(128)  { table alias in AQT definition }
        );
    revoke all on informix.sysaqtjp from public as informix;
    grant select on informix.sysaqtjp to public as informix;

{ DWA probe tables }
    create table informix.sysprobetables
        (
        dbname              char(128), { database name }
        sql_id              int8,      { statement id in syssqltrace }
        tabid               integer,   { table id }
        fact                char(1)    { table is fact table (y/n) }
        );
    revoke all on informix.sysprobetables from public as informix;
    grant select on informix.sysprobetables to public as informix;

{ DWA probe columns }
    create table informix.sysprobecolumns
        (
        dbname              char(128), { database name }
        sql_id              int8,      { statement id in syssqltrace }
        tabid               integer,   { table id }
        colno               smallint   { column number }
        );
    revoke all on informix.sysprobecolumns from public as informix;
    grant select on informix.sysprobecolumns to public as informix;

{ DWA probe join descriptors }
    create table informix.sysprobejds
        (
        dbname              char(128), { database name }
        sql_id              int8,      { statement id in syssqltrace }
        jd                  integer,   { join descriptor sequence number }
        ctabid              integer,   { child table id }
        ptabid              integer,   { parent table id }
        type                char(1),   { join type: inner (i), left outer (l) }
        uniq                char(1)    { parent table has unique index (y/n) }
        );
    revoke all on informix.sysprobejds from public as informix;
    grant select on informix.sysprobejds to public as informix;

{ DWA probe join predicates }
    create table informix.sysprobejps
        (
        dbname              char(128), { database name }
        sql_id              int8,      { statement id in syssqltrace }
        jd                  integer,   { join descriptor sequence number }
        jp                  integer,   { join predicate sequence number }
        ccolno              smallint,  { child table column number }
        pcolno              smallint   { parent table column number }
        );
    revoke all on informix.sysprobejps from public as informix;
    grant select on informix.sysprobejps to public as informix;

{ get info out of catalogs and save it }

select tabid from systables
where tabname in (
'sysdbspartn',  'systabnames',  'sysrawdsk',    'syspaghdr',
'sysslttab',    'syssltdat',    'syschfree',    'sysptnhdr',
'sysptnkey',    'sysptnext',    'sysptncol',    'sysptnbit',
'sysactptnhdr',
'sysrstcb',     'systxptab',    'syslcktab',    'sysbufhdr',
'sysdbstab',    'syschktab',    'sysmchktab',   'syschktab_fast',
'sysmchktab_fast', 'syslogfil',
'sysbtcreq',    'systraces',    'sysptntab',    'sysshmem',
'sysshmhdr',    'sysscblst',    'systcblst',    'sysvplst',
'systwaits',    'sysdrcb',              'sysadtinfo',   'syscrtadt',
'sysmtxlst',    'sysconlst',    'syspoollst',   'sysseglst',
'sysdic',               'sysprc',               'sysdsc',               'syssqscb',
'syssdblock',   'sysconblock',  'sysopendb',    'syssqlstat',
'syslrus',      'sysshmvals',   'sysplog', 'syscompdicts_full', 'syscfgtab',
'syscdrs',              'syscdrq',              'syscdrtx',     'syscdrprog',
'syscdrsend_txn', 'syscdrack_txn','syscdrctrl_txn', 'syscdrsync_txn',
'syscdrrecv_txn', 'syscdrsend_buf','syscdrack_buf', 'syscdrctrl_buf',
'syscdrsync_buf', 'syscdrrecv_buf', 'syscdrrecv_stats', 'syscdrlatency', 'syscdrtsapply',
'syscdr_rqm',   'sysbufpool',    'syssymtab',   'sysproftab',
'syscheckpoint','sysnetworkio', 'sysenv', 'sysenvses', 'sysonlinelog',
'sysdual',      'sysmgminfo',   'syssqlcacheprof', 'syssqltrace',
'syssqltrace_iter', 'syssqltrace_info', 'sysnetglobal', 'sysnetclienttype',
'sysbaract_log', 'sysipl',              'syssmx',               'syssmxses',
'syssrcrss',    'systrgrss',    'sysrsslog',    'syssrcsds',
'systrgsds',    'sysrepstats',  'syssqlhosts',  'sysha_type',
'sysha_lagtime', 'sysha_workload', 'sysxatab',  'sysrepevtreg',
'syscmsmtab',   'syscmsmsla',   'syssqltrace_hvar', 'sysmachineinfo',
'syslicenseinfo', 'syssesappinfo', 'sysmgmgates', 'sysmgmquery',
'sysproxydistributors', 'sysproxyagents',               'sysproxysessions',
'sysproxytxns', 'sysproxytxnops', 'syscdr_state', 'syscdr_ddr',
'syscdr_nif',   'syscdr_rcv',   'syscdr_atsdir', 'syscdr_risdir',
'syscdr_ats',   'syscdr_ris',   'syscdr_rqmstamp',      'syscdr_rqmhandle',
'sysstoragemgr', 'sysreorgops', 'sysfileinfo', 'syscdrcntrlqprog',
'syscluster',   'syscdrrecvqprog', 'sysaqt', 'sysaqtjp' ,
'sysstoragemgr','syslowmemorymgr', 'syscmsmunit',
'sysprobetables', 'sysprobecolumns', 'sysprobejds', 'sysprobejps',
'sysiohistory' ) 
into temp temptabid with no log;

select * from systables  where tabid in (select tabid from temptabid)
into temp tempsystab with no log;

select * from syscolumns where tabid in (select tabid from temptabid)
into temp tempsyscol with no log;

select * from sysindices where tabid in (select tabid from temptabid)
into temp tempsysidx with no log;

select * from systabauth where tabid in (select tabid from temptabid)
into temp tempsysauth with no log;

select * from sysobjstate where tabid in (select tabid from temptabid)
into temp tempsysobj with no log;

select * from sysblobs where tabid in (select tabid from temptabid)
into temp tempsysblobs with no log;

{ reset partnums so internally we recognize these as pseudo tables, also
  update nrows to clue in the optimizer }

update tempsystab set (partnum,nrows) = (257,10) where tabname = 'sysdbspartn';

update tempsystab set (partnum,nrows) = (1,100000)  where tabname = 'sysrawdsk';
update tempsystab set (partnum,nrows) = (2,100000)  where tabname = 'syspaghdr';
update tempsystab set (partnum,nrows) = (3,1000000) where tabname = 'sysslttab';
update tempsystab set (partnum,nrows) = (4,1000000) where tabname = 'syssltdat';
update tempsystab set (partnum,nrows) = (5,100)     where tabname = 'syschfree';
update tempsystab set (partnum,nrows) = (6,100)     where tabname = 'syscfgtab';
update tempsystab set (partnum,nrows) = (7,100000)  where tabname = 'syssymtab';
update tempsystab set (partnum,nrows) = (8,1000000) where tabname = 'sysproftab';

update tempsystab set (partnum,nrows) = (10,1000)  where tabname = 'sysptnhdr';
update tempsystab set (partnum,nrows) = (11,1000)  where tabname = 'sysptnkey';
update tempsystab set (partnum,nrows) = (12,1000)  where tabname = 'sysptnext';
update tempsystab set (partnum,nrows) = (13,1000)  where tabname = 'sysptncol';
update tempsystab set (partnum,nrows) = (14,10000) where tabname = 'sysptnbit';
update tempsystab set (partnum,nrows) = (15,1000)  where tabname = 'systabnames';
update tempsystab set (partnum,nrows) = (16,10000) where tabname = 'sysactptnhdr';

update tempsystab set (partnum,nrows) = (20,100)   where tabname = 'sysptntab';
update tempsystab set (partnum,nrows) = (21,10000) where tabname = 'syslcktab';
update tempsystab set (partnum,nrows) = (22,1000)  where tabname = 'sysbufhdr';
update tempsystab set (partnum,nrows) = (23,10)    where tabname = 'sysdbstab';
update tempsystab set (partnum,nrows) = (24,10)    where tabname = 'syschktab';
update tempsystab set (partnum,nrows) = (25,10)    where tabname = 'sysmchktab';
update tempsystab set (partnum,nrows) = (26,100)   where tabname = 'sysrstcb';
update tempsystab set (partnum,nrows) = (27,100)   where tabname = 'systxptab';
update tempsystab set (partnum,nrows) = (28,10)    where tabname = 'syslogfil';
update tempsystab set (partnum,nrows) = (29,100)   where tabname = 'sysbtcreq';

update tempsystab set (partnum,nrows) = (30,100)   where tabname = 'sysshmem';
update tempsystab set (partnum,nrows) = (31,100)   where tabname = 'sysshmhdr';
update tempsystab set (partnum,nrows) = (32,1000)  where tabname = 'systraces';
update tempsystab set (partnum,nrows) = (33,1)     where tabname = 'sysdrcb';
update tempsystab set (partnum,nrows) = (34,100)   where tabname = 'syslrus';
update tempsystab set (partnum,nrows) = (35,1)     where tabname = 'sysplog';
update tempsystab set (partnum,nrows) = (36,16)	   where tabname = 'sysbufpool';
update tempsystab set (partnum,nrows) = (38,1)     where tabname = 'sysshmvals';
update tempsystab set (partnum,nrows) = (39,10)    where tabname = 'syschktab_fast';

update tempsystab set (partnum,nrows) = (40,100)   where tabname = 'sysscblst';
update tempsystab set (partnum,nrows) = (41,100)   where tabname = 'systcblst';
update tempsystab set (partnum,nrows) = (42,100)   where tabname = 'sysvplst';
update tempsystab set (partnum,nrows) = (43,100)   where tabname = 'systwaits';

update tempsystab set (partnum,nrows) = (44,100000) where tabname = 'sysmtxlst';
update tempsystab set (partnum,nrows) = (45,1000)  where tabname = 'sysconlst';
update tempsystab set (partnum,nrows) = (46,10000) where tabname = 'syspoollst';
update tempsystab set (partnum,nrows) = (47,10)    where tabname = 'sysseglst';
update tempsystab set (partnum,nrows) = (48,10)    where tabname = 'sysmchktab_fast';
update tempsystab set (partnum,nrows) = (50,1)     where tabname = 'sysmgminfo';
update tempsystab set (partnum,nrows) = (51,5)     where tabname = 'sysmgmgates';
update tempsystab set (partnum,nrows) = (52,100)     where tabname = 'sysmgmquery';

update tempsystab set (partnum,nrows) = (60,100)   where tabname = 'sysdic';
update tempsystab set (partnum,nrows) = (61,10)    where tabname = 'sysprc';
update tempsystab set (partnum,nrows) = (62,10)    where tabname = 'sysdsc';
update tempsystab set (partnum,nrows) = (63,100)   where tabname = 'syssqscb';
update tempsystab set (partnum,nrows) = (64,1000)  where tabname = 'syssdblock';
update tempsystab set (partnum,nrows) = (65,1000)  where tabname = 'sysconblock';
update tempsystab set (partnum,nrows) = (66,100)   where tabname = 'sysopendb';
update tempsystab set (partnum,nrows) = (68,10)    where tabname = 'syssqlcacheprof';
update tempsystab set (partnum,nrows) = (69,100)   where tabname = 'syssqlstat';

update tempsystab set (partnum,nrows) = (70,100)   where tabname = 'syscdrs';
update tempsystab set (partnum,nrows) = (71,100)   where tabname = 'syscdrq';
update tempsystab set (partnum,nrows) = (72,100)   where tabname = 'syscdrtx';
update tempsystab set (partnum,nrows) = (73,100)   where tabname = 'syscdrprog';

update tempsystab set (partnum,nrows) = (74,100)   where tabname = 'syscdrsend_txn';
update tempsystab set (partnum,nrows) = (75,100)   where tabname = 'syscdrack_txn';
update tempsystab set (partnum,nrows) = (76,100)   where tabname = 'syscdrctrl_txn';
update tempsystab set (partnum,nrows) = (77,100)   where tabname = 'syscdrsync_txn';
update tempsystab set (partnum,nrows) = (78,100)   where tabname = 'syscdrrecv_txn';
update tempsystab set (partnum,nrows) = (79,100)   where tabname = 'syscdrsend_buf';
update tempsystab set (partnum,nrows) = (80,100)   where tabname = 'syscdrack_buf';
update tempsystab set (partnum,nrows) = (81,100)   where tabname = 'syscdrctrl_buf';
update tempsystab set (partnum,nrows) = (82,100)   where tabname = 'syscdrsync_buf';
update tempsystab set (partnum,nrows) = (83,100)   where tabname = 'syscdrrecv_buf';
update tempsystab set (partnum,nrows) = (84, 100) where tabname = 'syscdrrecv_stats';
update tempsystab set (partnum,nrows) = (85,100) where tabname = 'syscdrlatency';
update tempsystab set (partnum, nrows) = (86,100) where tabname = 'syscdr_rqm';
update tempsystab set (partnum, nrows) = (87,12) where tabname = 'syscheckpoint';
update tempsystab set (partnum, nrows) = (88,100) where tabname = 'sysnetworkio';
update tempsystab set (partnum, nrows) = (89,100) where tabname = 'sysenv';
update tempsystab set (partnum, nrows) = (90,100) where tabname = 'sysenvses';
update tempsystab set (partnum, nrows) = (91,100000) where tabname = 'sysonlinelog';
update tempsystab set (partnum, nrows) = (92,1) where tabname = 'sysdual';
update tempsystab set (partnum, nrows) = (93,10000) 
                  where tabname = 'syssqltrace';
update tempsystab set (partnum, nrows) = (94,10000) 
                  where tabname = 'syssqltrace_iter';
update tempsystab set (partnum, nrows) = (95,50) 
                  where tabname = 'syssqltrace_info';
update tempsystab set (partnum, nrows) = (96,1) 
                  where tabname = 'sysnetglobal';
update tempsystab set (partnum, nrows) = (97,20) 
                  where tabname = 'sysnetclienttype';
update tempsystab set (partnum, nrows) = (98,1000) 
                  where tabname = 'sysbaract_log';
update tempsystab set (partnum, nrows) = (99,1000) 
                  where tabname = 'sysipl';
update tempsystab set (partnum, nrows) = (100,1000) 
                  where tabname = 'syssmx';
update tempsystab set (partnum, nrows) = (101,1000) 
                  where tabname = 'syssmxses';
update tempsystab set (partnum, nrows) = (102,1000) 
                  where tabname = 'syssrcrss';
update tempsystab set (partnum, nrows) = (103,1000) 
                  where tabname = 'systrgrss';
update tempsystab set (partnum, nrows) = (104,1000) 
                  where tabname = 'sysrsslog';
update tempsystab set (partnum, nrows) = (105,1000) 
                  where tabname = 'syssrcsds';
update tempsystab set (partnum, nrows) = (106,1000) 
                  where tabname = 'systrgsds';
update tempsystab set (partnum, nrows) = (108,1000) 
                  where tabname = 'syssqlhosts';
update tempsystab set (partnum, nrows) = (109,1000) 
                  where tabname = 'sysha_type';
update tempsystab set (partnum, nrows) = (110,1000) 
                  where tabname = 'sysha_lagtime';
update tempsystab set (partnum, nrows) = (111,1000) 
                  where tabname = 'sysha_workload';
update tempsystab set (partnum, nrows) = (112,100) where tabname = 'sysxatab';
update tempsystab set (partnum, nrows) = (113,100) where tabname = 'syscmsmtab';
update tempsystab set (partnum, nrows) = (114,100) where tabname = 'syscmsmsla';

update tempsystab set (partnum, nrows) = (115,100) 
                  where tabname = 'syssqltrace_hvar';
update tempsystab set (partnum, nrows) = (116,1) 
                  where tabname = 'sysmachineinfo';
update tempsystab set (partnum, nrows) = (117,1000) 
                  where tabname = 'syslicenseinfo';
update tempsystab set (partnum, nrows) = (118,100) 
                  where tabname = 'syssesappinfo';
update tempsystab set (partnum,nrows) = (119,1000) where tabname = 'sysproxydistributors';
update tempsystab set (partnum,nrows) = (120,100)  where tabname = 'sysproxyagents';
update tempsystab set (partnum,nrows) = (121,1000) where tabname = 'sysproxysessions';
update tempsystab set (partnum,nrows) = (122,1000) where tabname = 'sysproxytxns';
update tempsystab set (partnum,nrows) = (123,1000) where tabname = 'sysproxytxnops';
update tempsystab set (partnum,nrows) = (124,1000) where tabname = 'sysfileinfo';
update tempsystab set (partnum, nrows) = (125,100) where tabname = 'syscmsmunit';
update tempsystab set (partnum, nrows) = (130, 1) where tabname = 'syscdr_state';
update tempsystab set (partnum, nrows) = (131, 1) where tabname = 'syscdr_ddr';
update tempsystab set (partnum, nrows) = (132, 50) where tabname = 'syscdr_nif';
update tempsystab set (partnum, nrows) = (133, 1) where tabname = 'syscdr_rcv';
update tempsystab set (partnum, nrows) = (134, 5000)
                                where tabname = 'syscdr_atsdir';
update tempsystab set (partnum, nrows) = (135, 5000)
                                where tabname = 'syscdr_risdir';
update tempsystab set (partnum, nrows) = (136, 5000)
                                where tabname = 'syscdr_ats';
update tempsystab set (partnum, nrows) = (137, 5000)
                                where tabname = 'syscdr_ris';
update tempsystab set (partnum, nrows) = (138, 5)
                                where tabname = 'syscdr_rqmstamp';
update tempsystab set (partnum, nrows) = (139, 30)
                                where tabname = 'syscdr_rqmhandle';
update tempsystab set (partnum, nrows) = (140, 10)
                                where tabname = 'sysstoragemgr';
update tempsystab set (partnum, nrows) = (141, 1)
                                where tabname = 'syslowmemorymgr';
update tempsystab set (partnum, nrows) = (142, 1000)
                                where tabname = 'sysreorgops';
update tempsystab set (partnum,nrows) = (143,100)   
                                where tabname = 'syscdrcntrlqprog';
update tempsystab set (partnum,nrows) = (144,1) 
                                where tabname = 'syscluster';
update tempsystab set (partnum,nrows) = (145,1000) where tabname = 'sysaqt';
update tempsystab set (partnum,nrows) = (146,10000) where tabname = 'sysaqtjp';
update tempsystab set (partnum,nrows) = (147,100) 
                                where tabname = 'syscdrrecvqprog';
update tempsystab set (partnum,nrows) = (148,100)
				where tabname = 'sysprobetables';
update tempsystab set (partnum,nrows) = (149,1000)
				where tabname = 'sysprobecolumns';
update tempsystab set (partnum,nrows) = (150,100)
				where tabname = 'sysprobejds';
update tempsystab set (partnum,nrows) = (151,1000)
				where tabname = 'sysprobejps';
update tempsystab set (partnum,nrows) = (152,100) 
                where tabname = 'syscdrtsapply';
update tempsystab set (partnum, nrows) = (153,300) 
                  where tabname = 'sysiohistory';
update tempsystab set (partnum,nrows) = (1025, 1)  where tabname = 'sysadtinfo';
update tempsystab set (partnum,nrows) = (1026, 1)  where tabname = 'syscrtadt';
update tempsystab set (partnum,nrows) = (1027,1000) 
                  where tabname = 'sysrepstats';
update tempsystab set (partnum,nrows) = (1028,1000) 
                  where tabname = 'sysrepevtreg';
update tempsystab set (partnum,nrows) = (37,100000) 
		  where tabname = 'syscompdicts_full';

drop table sysdbspartn;
drop table sysrawdsk;
drop table syspaghdr;
drop table sysslttab;
drop table syssltdat;
drop table syschfree;
drop table syssymtab;
drop table sysproftab;
drop table sysptnhdr;
drop table sysptnkey;
drop table sysptnext;
drop table sysptncol;
drop table sysptnbit;
drop table sysactptnhdr;
drop table systabnames;
drop table sysptntab;
drop table syslcktab;
drop table sysbufhdr;
drop table sysbufpool;
drop table sysdbstab;
drop table syschktab;
drop table sysmchktab;
drop table syschktab_fast;
drop table sysmchktab_fast;
drop table sysrstcb;
drop table systxptab;
drop table sysxatab;
drop table syslogfil;
drop table sysbtcreq;
drop table sysshmem;
drop table sysshmhdr;
drop table syscfgtab;
drop table systraces;
drop table sysscblst;
drop table systcblst;
drop table sysvplst;
drop table sysreorgops;
drop table systwaits;
drop table sysdrcb;
drop table sysplog;
drop table syscompdicts_full;
drop table sysadtinfo;
drop table syscrtadt;
drop table sysmtxlst;
drop table sysconlst;
drop table syspoollst;
drop table sysseglst;
drop table sysmgminfo;
drop table sysmgmgates;
drop table sysmgmquery;
drop table sysdic;
drop table sysprc;
drop table sysdsc;
drop table syssqscb;
drop table syssdblock;
drop table sysconblock;
drop table sysopendb;
drop table syssqlcacheprof;
drop table syssqlstat;
drop table syslrus;
drop table sysshmvals;
drop table syscdrs;
drop table syscdrq;
drop table syscdrtx;
drop table syscdrprog;
drop table syscdrsend_txn;
drop table syscdrack_txn;
drop table syscdrctrl_txn;
drop table syscdrsync_txn;
drop table syscdrrecv_txn;
drop table syscdrsend_buf;
drop table syscdrack_buf;
drop table syscdrctrl_buf;
drop table syscdrsync_buf;
drop table syscdrrecv_buf;
drop table syscdrrecv_stats;
drop table syscdrlatency;
drop table syscdrtsapply;
drop table syscdr_rqm;
drop table syscheckpoint;
drop table sysnetworkio;
drop table sysenv;
drop table sysenvses;
drop table sysonlinelog;
drop table sysdual;
drop table syssqltrace;
drop table syssqltrace_iter;
drop table syssqltrace_info;
drop table sysnetglobal;
drop table sysnetclienttype;
drop table sysbaract_log;
drop table sysipl;
drop table syssmx;
drop table syssmxses;
drop table syssrcrss;
drop table systrgrss;
drop table sysrsslog;
drop table syssrcsds;
drop table systrgsds;
drop table sysrepstats;
drop table sysrepevtreg;
drop table syssqlhosts;
drop table sysha_type;
drop table sysha_lagtime;
drop table sysha_workload;
drop table syscmsmtab;
drop table syscmsmunit;
drop table syscmsmsla;
drop table syssqltrace_hvar;
drop table sysmachineinfo;
drop table sysstoragemgr;
drop table syslowmemorymgr;
drop table syslicenseinfo;
drop table syssesappinfo;
drop table sysproxydistributors;
drop table sysproxyagents;
drop table sysproxysessions;
drop table sysproxytxns;
drop table sysproxytxnops;
drop table syscdr_state;
drop table syscdr_ddr;
drop table syscdr_nif;
drop table syscdr_rcv;
drop table syscdr_atsdir;
drop table syscdr_risdir;
drop table syscdr_ats;
drop table syscdr_ris;
drop table syscdr_rqmstamp;
drop table syscdr_rqmhandle;
drop table sysfileinfo;
drop table syscdrcntrlqprog;
drop table syscluster;
drop table syscdrrecvqprog;
drop table sysaqt;
drop table sysaqtjp;
drop table sysprobetables;
drop table sysprobecolumns;
drop table sysprobejds;
drop table sysprobejps;
drop table sysiohistory;

{ reinsert modified catalog info into catalogs }

insert into systables  select * from tempsystab;
insert into syscolumns select * from tempsyscol;
insert into sysindices select * from tempsysidx;
insert into systabauth select * from tempsysauth;
insert into sysobjstate select * from tempsysobj;
insert into sysblobs select * from tempsysblobs;

drop table tempsystab;
drop table tempsyscol;
drop table tempsysidx;
drop table tempsysauth;
drop table tempsysobj;
drop table tempsysblobs;

{ create a table to associate strings wither various tables' flags/types
  columns }

    create table informix.flags_text (tabname char(128), flags int, txt varchar(200));
    create unique index informix.flags_text_ix1 on flags_text(tabname, flags);
    revoke all on informix.flags_text from public as informix;
    grant select on informix.flags_text to public as informix;


{ Session waits reasons }
    insert into flags_text values ('systwaits', 0, 'unspecified');
    insert into flags_text values ('systwaits', 1, 'buffer');
    insert into flags_text values ('systwaits', 2, 'lock');
    insert into flags_text values ('systwaits', 3, 'aio');
    insert into flags_text values ('systwaits', 4, 'mt yield 0');
    insert into flags_text values ('systwaits', 5, 'mt yield n');
    insert into flags_text values ('systwaits', 6, 'mt yield');
    insert into flags_text values ('systwaits', 7, 'checkpoint');
    insert into flags_text values ('systwaits', 8, 'log i/o');
    insert into flags_text values ('systwaits', 9, 'log block');
    insert into flags_text values ('systwaits', 10, 'condition');
    insert into flags_text values ('systwaits', 11, 'lock mutex');
    insert into flags_text values ('systwaits', 12, 'lockfree mutex');
    insert into flags_text values ('systwaits', 13, 'deadlock mutex');
    insert into flags_text values ('systwaits', 14, 'lrus mutex');
    insert into flags_text values ('systwaits', 15, 'tblsp mutex');
    insert into flags_text values ('systwaits', 16, 'not used');
    insert into flags_text values ('systwaits', 17, 'ckpt mutex');
    insert into flags_text values ('systwaits', 18, 'mutex');
    insert into flags_text values ('systwaits', 19, 'mt ready');
    insert into flags_text values ('systwaits', 20, 'mt yield x');
    insert into flags_text values ('systwaits', 21, 'running');
    insert into flags_text values ('systwaits', 22, 'msg ready');
    insert into flags_text values ('systwaits', 23, 'buf ready');
    insert into flags_text values ('systwaits', 24, 'empty Q');
    insert into flags_text values ('systwaits', 25, 'full Q');
    insert into flags_text values ('systwaits', 26, 'OS read');
    insert into flags_text values ('systwaits', 27, 'OS write');
    insert into flags_text values ('systwaits', 28, 'IO idle');
    insert into flags_text values ('systwaits', 29, 'Fork');
    insert into flags_text values ('systwaits', 30, 'log buffer');
    insert into flags_text values ('systwaits', 31, 'not used');
    insert into flags_text values ('systwaits', 32, 'sort io');
    insert into flags_text values ('systwaits', 33, 'misc');
    insert into flags_text values ('systwaits', 34, 'join thread');
    insert into flags_text values ('systwaits', 35, 'vp mem sync');

{ VP Classes }
    insert into flags_text values ('sysvplst', 0, 'cpu');
    insert into flags_text values ('sysvplst', 1, 'aio');
    insert into flags_text values ('sysvplst', 2, 'tli');
    insert into flags_text values ('sysvplst', 3, 'shm');
    insert into flags_text values ('sysvplst', 4, 'lio');
    insert into flags_text values ('sysvplst', 5, 'pio');
    insert into flags_text values ('sysvplst', 6, 'adm');
    insert into flags_text values ('sysvplst', 7, 'opt');
    insert into flags_text values ('sysvplst', 8, 'soc');
    insert into flags_text values ('sysvplst', 9, 'msc');
    insert into flags_text values ('sysvplst', 10, 'adt');
    insert into flags_text values ('sysvplst', 11, 'kio');
    insert into flags_text values ('sysvplst', 12, 'str');
    insert into flags_text values ('sysvplst', 13, 'csm');
    insert into flags_text values ('sysvplst', 14, 'jvp');
    insert into flags_text values ('sysvplst', 15, 'cdr');
    insert into flags_text values ('sysvplst', 16, 'crypto');
    insert into flags_text values ('sysvplst', 17, 'sqli_dbg');
    insert into flags_text values ('sysvplst', 18, 'encrypt');
    insert into flags_text values ('sysvplst', 19, 'drda_dbg');
    insert into flags_text values ('sysvplst', 20, 'ntk');
    insert into flags_text values ('sysvplst', 21, 'ssl');
    insert into flags_text values ('sysvplst', 22, 'cdrsmi');

{ Lock types }
    insert into flags_text values ('syslcktab', 0, 'NONE');
    insert into flags_text values ('syslcktab', 1, 'BYTE');
    insert into flags_text values ('syslcktab', 2, 'IS');
    insert into flags_text values ('syslcktab', 3, 'S');
    insert into flags_text values ('syslcktab', 4, 'SR');
    insert into flags_text values ('syslcktab', 5, 'U');
    insert into flags_text values ('syslcktab', 6, 'UR');
    insert into flags_text values ('syslcktab', 7, 'IX');
    insert into flags_text values ('syslcktab', 8, 'SIX');
    insert into flags_text values ('syslcktab', 9, 'X');
    insert into flags_text values ('syslcktab', 10,'XR');

{ Data Replication }

    insert into flags_text values ('sysdrcb', 0, 'Not Initialized');
    insert into flags_text values ('sysdrcb', 1, 'Standard');
    insert into flags_text values ('sysdrcb', 2, 'Primary');
    insert into flags_text values ('sysdrcb', 3, 'Secondary');
    insert into flags_text values ('sysdrcb', 16, 'Off');
    insert into flags_text values ('sysdrcb', 32, 'On');
    insert into flags_text values ('sysdrcb', 64, 'Connecting');
    insert into flags_text values ('sysdrcb', 128, 'Failed');
    insert into flags_text values ('sysdrcb', 256, 'Failed Recovery');
    insert into flags_text values ('sysdrcb', 288, 'On');
    insert into flags_text values ('sysdrcb', 544, 'Draining');

{ Index Page Logging status }

    insert into flags_text values ('sysipl', 0, 'Inactive');
    insert into flags_text values ('sysipl', 1, 'Active');

{ Isolation Level }

    insert into flags_text values ('sysopendb', 0, 'NOTRANS');
    insert into flags_text values ('sysopendb', 1, 'DIRTY READ');
    insert into flags_text values ('sysopendb', 2, 'COMMITTED READ');
    insert into flags_text values ('sysopendb', 3, 'CURSOR STABILITY');
    insert into flags_text values ('sysopendb', 5, 'REPEATABLE READ');
    insert into flags_text values ('sysopendb', 7, 'DIRTY READ RETAIN UPDATE LOCKS');
    insert into flags_text values ('sysopendb', 8, 'COMMITTED READ RETAIN UPDATE LOCKS');
    insert into flags_text values ('sysopendb', 9, 'CURSOR STABILITY RETAIN UPDATE LOCKS');
    insert into flags_text values ('sysopendb', 11, 'COMMITTED READ LAST COMMITTED');

{ SQL statement types: see incl/sqlstype }

    insert into flags_text values ('sqltype',  1, 'SQ_DATABASE');
    insert into flags_text values ('sqltype',  2, 'SQ_SELECT');
    insert into flags_text values ('sqltype',  3, 'SQ_SELINTO');
    insert into flags_text values ('sqltype',  4, 'SQ_UPDATE');
    insert into flags_text values ('sqltype',  5, 'SQ_DELETE');
    insert into flags_text values ('sqltype',  6, 'SQ_INSERT');
    insert into flags_text values ('sqltype',  7, 'SQ_UPDCURR');
    insert into flags_text values ('sqltype',  8, 'SQ_DELCURR');
    insert into flags_text values ('sqltype',  9, 'SQ_LDINSERT');
    insert into flags_text values ('sqltype', 10, 'SQ_LOCK');
    insert into flags_text values ('sqltype', 11, 'SQ_UNLOCK');
    insert into flags_text values ('sqltype', 12, 'SQ_CREADB');
    insert into flags_text values ('sqltype', 13, 'SQ_DROPDB');
    insert into flags_text values ('sqltype', 14, 'SQ_CRETAB');
    insert into flags_text values ('sqltype', 15, 'SQ_DRPTAB');
    insert into flags_text values ('sqltype', 16, 'SQ_CREIDX');
    insert into flags_text values ('sqltype', 17, 'SQ_DRPIDX');
    insert into flags_text values ('sqltype', 18, 'SQ_GRANT');
    insert into flags_text values ('sqltype', 19, 'SQ_REVOKE');
    insert into flags_text values ('sqltype', 20, 'SQ_RENTAB');
    insert into flags_text values ('sqltype', 21, 'SQ_RENCOL');
    insert into flags_text values ('sqltype', 22, 'SQ_CREAUD');
    insert into flags_text values ('sqltype', 23, 'SQ_STRAUD');
    insert into flags_text values ('sqltype', 24, 'SQ_STPAUD');
    insert into flags_text values ('sqltype', 25, 'SQ_DRPAUD');
    insert into flags_text values ('sqltype', 26, 'SQ_RECTAB');
    insert into flags_text values ('sqltype', 27, 'SQ_CHKTAB');
    insert into flags_text values ('sqltype', 28, 'SQ_REPTAB');
    insert into flags_text values ('sqltype', 29, 'SQ_ALTER');
    insert into flags_text values ('sqltype', 30, 'SQ_STATS');
    insert into flags_text values ('sqltype', 31, 'SQ_CLSDB');
    insert into flags_text values ('sqltype', 32, 'SQ_DELALL');
    insert into flags_text values ('sqltype', 33, 'SQ_UPDALL');
    insert into flags_text values ('sqltype', 34, 'SQ_BEGWORK');
    insert into flags_text values ('sqltype', 35, 'SQ_COMMIT');
    insert into flags_text values ('sqltype', 36, 'SQ_ROLLBACK');
    insert into flags_text values ('sqltype', 37, 'SQ_SAVEPOINT');
    insert into flags_text values ('sqltype', 38, 'SQ_STARTDB');
    insert into flags_text values ('sqltype', 39, 'SQ_RFORWARD');
    insert into flags_text values ('sqltype', 40, 'SQ_CREVIEW');
    insert into flags_text values ('sqltype', 41, 'SQ_DROPVIEW');
    insert into flags_text values ('sqltype', 42, 'SQ_DEBUG');
    insert into flags_text values ('sqltype', 43, 'SQ_CREASYN');
    insert into flags_text values ('sqltype', 44, 'SQ_DROPSYN');
    insert into flags_text values ('sqltype', 45, 'SQ_CTEMP');
    insert into flags_text values ('sqltype', 46, 'SQ_WAITFOR');
    insert into flags_text values ('sqltype', 47, 'SQ_ALTIDX');
    insert into flags_text values ('sqltype', 48, 'SQ_ISOLATE');
    insert into flags_text values ('sqltype', 49, 'SQ_SETLOG');
    insert into flags_text values ('sqltype', 50, 'SQ_EXPLAIN');
    insert into flags_text values ('sqltype', 51, 'SQ_SCHEMA');
    insert into flags_text values ('sqltype', 52, 'SQ_OPTIM');
    insert into flags_text values ('sqltype', 53, 'SQ_CREPROC');
    insert into flags_text values ('sqltype', 54, 'SQ_DRPPROC');
    insert into flags_text values ('sqltype', 55, 'SQ_CONSTRMODE');
    insert into flags_text values ('sqltype', 56, 'SQ_EXECPROC');
    insert into flags_text values ('sqltype', 57, 'SQ_DBGFILE');
    insert into flags_text values ('sqltype', 58, 'SQ_CREOPCL');
    insert into flags_text values ('sqltype', 59, 'SQ_ALTOPCL');
    insert into flags_text values ('sqltype', 60, 'SQ_DRPOPCL');
    insert into flags_text values ('sqltype', 61, 'SQ_OPRESERVE');
    insert into flags_text values ('sqltype', 62, 'SQ_OPRELEASE');
    insert into flags_text values ('sqltype', 63, 'SQ_OPTIMEOUT');
    insert into flags_text values ('sqltype', 64, 'SQ_PROCSTATS');
    insert into flags_text values ('sqltype', 65, 'SQ_GRANTGRP');
    insert into flags_text values ('sqltype', 66, 'SQ_REVOKGRP');
    insert into flags_text values ('sqltype', 67, 'SQ_SKINHIBIT');
    insert into flags_text values ('sqltype', 68, 'SQ_SKSHOW');
    insert into flags_text values ('sqltype', 69, 'SQ_SKSMALL');
    insert into flags_text values ('sqltype', 70, 'SQ_CRETRIG');
    insert into flags_text values ('sqltype', 71, 'SQ_DRPTRIG');
    insert into flags_text values ('sqltype', 72, 'SQ_UNKNOWN');
    insert into flags_text values ('sqltype', 73, 'SQ_SETDATASKIP');
    insert into flags_text values ('sqltype', 74, 'SQ_PDQPRIORITY');
    insert into flags_text values ('sqltype', 75, 'SQ_ALTFRAG');
    insert into flags_text values ('sqltype', 76, 'SQ_SETOBJMODE');
    insert into flags_text values ('sqltype', 77, 'SQ_START');
    insert into flags_text values ('sqltype', 78, 'SQ_STOP');
    insert into flags_text values ('sqltype', 79, 'SQ_SETMAC');
    insert into flags_text values ('sqltype', 80, 'SQ_SETDAC');
    insert into flags_text values ('sqltype', 81, 'SQ_SETTBLHI');
    insert into flags_text values ('sqltype', 82, 'SQ_SETLVEXT');
    insert into flags_text values ('sqltype', 83, 'SQ_CREATEROLE');
    insert into flags_text values ('sqltype', 84, 'SQ_DROPROLE');
    insert into flags_text values ('sqltype', 85, 'SQ_SETROLE');
    insert into flags_text values ('sqltype', 86, 'SQ_PASSWD');
    insert into flags_text values ('sqltype', 87, 'SQ_RENDB');
    insert into flags_text values ('sqltype', 88, 'SQ_CREADOM');
    insert into flags_text values ('sqltype', 89, 'SQ_DROPDOM');
    insert into flags_text values ('sqltype', 90, 'SQ_CREANRT');
    insert into flags_text values ('sqltype', 91, 'SQ_DROPNRT');
    insert into flags_text values ('sqltype', 92, 'SQ_CREADT');
    insert into flags_text values ('sqltype', 93, 'SQ_CREACT');
    insert into flags_text values ('sqltype', 94, 'SQ_DROPCT');
    insert into flags_text values ('sqltype', 95, 'SQ_CREABT');
    insert into flags_text values ('sqltype', 96, 'SQ_DROPTYPE');
    insert into flags_text values ('sqltype', 97, 'SQ_ALTERROUTINE');
    insert into flags_text values ('sqltype', 98, 'SQ_CREATEAM');
    insert into flags_text values ('sqltype', 99, 'SQ_DROPAM');
    insert into flags_text values ('sqltype', 100, 'SQ_ALTERAM');
    insert into flags_text values ('sqltype', 101, 'SQ_CREATEOPC');
    insert into flags_text values ('sqltype', 102, 'SQ_DROPOPC');
    insert into flags_text values ('sqltype', 103, 'SQ_CREACST');
    insert into flags_text values ('sqltype', 104, 'SQ_SETRES');
    insert into flags_text values ('sqltype', 105, 'SQ_CREAGG');
    insert into flags_text values ('sqltype', 106, 'SQ_DRPAGG');
    insert into flags_text values ('sqltype', 107, 'SQ_PLOADFILE');
    insert into flags_text values ('sqltype', 108, 'SQ_CHKIDX');
    insert into flags_text values ('sqltype', 109, 'SQ_SCHEDULE');
    insert into flags_text values ('sqltype', 110, 'SQ_SETENV');
    insert into flags_text values ('sqltype', 111, 'SQ_XPS_RES2');
    insert into flags_text values ('sqltype', 112, 'SQ_XPS_RES3');
    insert into flags_text values ('sqltype', 113, 'SQ_XPS_RES4');
    insert into flags_text values ('sqltype', 114, 'SQ_XPS_RES5');
    insert into flags_text values ('sqltype', 116, 'SQ_RENIDX');
    insert into flags_text values ('sqltype', 124, 'SQ_CRESEQ');
    insert into flags_text values ('sqltype', 125, 'SQ_DRPSEQ');
    insert into flags_text values ('sqltype', 126, 'SQ_ALTERSEQ');
    insert into flags_text values ('sqltype', 127, 'SQ_RENSEQ');
    insert into flags_text values ('sqltype', 129, 'SQ_COLLATION');
    insert into flags_text values ('sqltype', 130, 'SQ_NOCOLLATION');
    insert into flags_text values ('sqltype', 131, 'SQ_SETDEFROLE');
    insert into flags_text values ('sqltype', 132, 'SQ_ENCRYPTION');
    insert into flags_text values ('sqltype', 134, 'SQ_CRXASRCTYPE');
    insert into flags_text values ('sqltype', 135, 'SQ_CRXADTSRC');
    insert into flags_text values ('sqltype', 136, 'SQ_DROPXATYPE');
    insert into flags_text values ('sqltype', 137, 'SQ_DROPXADTSRC');
    insert into flags_text values ('sqltype', 138, 'SQ_TRUNCATE');
    insert into flags_text values ('sqltype', 139, 'SQ_CRESECCMP');
    insert into flags_text values ('sqltype', 140, 'SQ_ALTSECCMP');
    insert into flags_text values ('sqltype', 141, 'SQ_DRPSECCMP');
    insert into flags_text values ('sqltype', 142, 'SQ_RENSECCMP');
    insert into flags_text values ('sqltype', 143, 'SQ_CRESECPOL');
    insert into flags_text values ('sqltype', 144, 'SQ_DRPSECPOL');
    insert into flags_text values ('sqltype', 145, 'SQ_RENSECPOL');
    insert into flags_text values ('sqltype', 146, 'SQ_CRESECLAB');
    insert into flags_text values ('sqltype', 147, 'SQ_DRPSECLAB');
    insert into flags_text values ('sqltype', 148, 'SQ_RENSECLAB');
    insert into flags_text values ('sqltype', 149, 'SQ_GRTSECADM');
    insert into flags_text values ('sqltype', 150, 'SQ_RVKSECADM');
    insert into flags_text values ('sqltype', 151, 'SQ_GRTSECEXMP');
    insert into flags_text values ('sqltype', 152, 'SQ_RVKSECEXMP');
    insert into flags_text values ('sqltype', 153, 'SQ_GRTSECLAB');
    insert into flags_text values ('sqltype', 154, 'SQ_RVKSECLAB');
    insert into flags_text values ('sqltype', 155, 'SQ_GRTSESAUTH');
    insert into flags_text values ('sqltype', 156, 'SQ_RVKSESAUTH');
    insert into flags_text values ('sqltype', 157, 'SQ_SETSESIMP');
    insert into flags_text values ('sqltype', 158, 'SQ_FREELOC');
    insert into flags_text values ('sqltype', 159, 'SQ_SETSVPT');
    insert into flags_text values ('sqltype', 160, 'SQ_RELSVPT');
    insert into flags_text values ('sqltype', 161, 'SQ_RBACKSVPT');
    insert into flags_text values ('sqltype', 162, 'SQ_MERGE');
    insert into flags_text values ('sqltype', 163, 'SQ_OPTSTORAGE');
    insert into flags_text values ('sqltype', 164, 'SQ_GRTACCESS');
    insert into flags_text values ('sqltype', 165, 'SQ_RVKACCESS');
    insert into flags_text values ('sqltype', 166, 'SQ_SELINTOEXT');
    insert into flags_text values ('sqltype', 167, 'SQ_CRETCONTEXT');
    insert into flags_text values ('sqltype', 168, 'SQ_ALTTCONTEXT');
    insert into flags_text values ('sqltype', 169, 'SQ_DRPTCONTEXT');
    insert into flags_text values ('sqltype', 170, 'SQ_RENTCONTEXT');
    insert into flags_text values ('sqltype', 171, 'SQ_CRTUSER');
    insert into flags_text values ('sqltype', 172, 'SQ_ALTUSER');
    insert into flags_text values ('sqltype', 173, 'SQ_DRPUSER');
    insert into flags_text values ('sqltype', 174, 'SQ_RNMUSER');
    insert into flags_text values ('sqltype', 175, 'SQ_USRPW');

{ SQL trace info: see sql/sqcache.h }

    insert into flags_text values ('syssqltrace_info', '0x0001', 'Tracing On');
    insert into flags_text values ('syssqltrace_info', '0x0002', 'Tracing Off');
    insert into flags_text values ('syssqltrace_info', 
		'0x0010', 'Global Tracing');
    insert into flags_text values ('syssqltrace_info', 
		'0x0020', 'User Tracing');
    insert into flags_text values ('syssqltrace_info', 
		'0x0100', 'Trace Database Name');
    insert into flags_text values ('syssqltrace_info', 
		'0x0200', 'Trace Statement Text');
    insert into flags_text values ('syssqltrace_info', 
		'0x0400', 'Trace Statistics');
    insert into flags_text values ('syssqltrace_info', 
		'0x0800', 'Trace Procedures');
    insert into flags_text values ('syssqltrace_info', 
		'0x1000', 'Trace Iterators');
    insert into flags_text values ('syssqltrace_info', 
		'0x2000', 'Trace Host Variables');
    insert into flags_text values ('syssqltrace_info', 
		'0x4000', 'Trace Table Name');

{ Page Header }
   
    insert into flags_text values ('syspaghdr',
    1,'Data Page');
    insert into flags_text values ('syspaghdr',
    2,'Partition Descriptor Page'); 
    insert into flags_text values ('syspaghdr',
    4,'Partition Free List Page'); 
    insert into flags_text values ('syspaghdr',
    8,'Chunk Free List Page'); 
    insert into flags_text values ('syspaghdr',
    9,'Remainder Data Page'); 
    insert into flags_text values ('syspaghdr',
    11,'Partition Resident BLOB Page'); 
    insert into flags_text values ('syspaghdr',
    12,'Blobspace Resident BLOB Page'); 
    insert into flags_text values ('syspaghdr',
    13,'BLOB Chunk Free List Bit Page'); 
    insert into flags_text values ('syspaghdr',
    14,'BLOB Chunk BLOB Map Page'); 
    insert into flags_text values ('syspaghdr',
    16,'B-Tree Node Page'); 
    insert into flags_text values ('syspaghdr',
    32,'B-Tree Root Node'); 
    insert into flags_text values ('syspaghdr',
    64,'B-Tree Twig Node'); 
    insert into flags_text values ('syspaghdr',
    128,'B-Tree Leaf Node'); 
    insert into flags_text values ('syspaghdr',
    256,'Logical Log Page'); 
    insert into flags_text values ('syspaghdr',
    512,'Last Page of Log Log'); 
    insert into flags_text values ('syspaghdr',
    1024,'Sync Page of Log Log'); 
    insert into flags_text values ('syspaghdr',
    2048,'Physical Log Page'); 
    insert into flags_text values ('syspaghdr',
    4096,'Reserved Page'); 
    insert into flags_text values ('syspaghdr',
    8192,'Temporarily no physical logging required'); 
    insert into flags_text values ('syspaghdr',
    16384,'Temporarily no physical logging required'); 
    insert into flags_text values ('syspaghdr',
    32768,'B-Tree Leaf Page containing deleted Items'); 

{ SQLHosts Network Options }

    insert into flags_text values ('syssqlhosts_netoptions', 0, 'None');
    insert into flags_text values ('syssqlhosts_netoptions', 1, 'Keep Alive');

{ SQLHosts Server Security Options}
    insert into flags_text values ('syssqlhosts_svrsecurity', 0, 'None');
    insert into flags_text values ('syssqlhosts_svrsecurity', 1, 'Enable host.equiv');
    insert into flags_text values ('syssqlhosts_svrsecurity', 2, 'Enable rhosts');
    insert into flags_text values ('syssqlhosts_svrsecurity', 4, 'AIX authentication');
    insert into flags_text values ('syssqlhosts_svrsecurity', 8, 'Enable Informix host.equiv');

{ SQLHosts Client Security Options}
    insert into flags_text values ('syssqlhosts_clntsecurity', 0, 'None');
    insert into flags_text values ('syssqlhosts_clntsecurity', 1, 'Enable netrc lookup');

{ SQLHosts Redirector Options}
    insert into flags_text values ('syssqlhosts_redirector', 0, 'Off');
    insert into flags_text values ('syssqlhosts_redirector', 1, 'On');

{ Partition Header }

    insert into flags_text values ('sysptnhdr', 1, 'Page Level Locking');
    insert into flags_text values ('sysptnhdr', 2, 'Row Level Locking');
    insert into flags_text values ('sysptnhdr', 4, 'System Catalog Table');
    insert into flags_text values ('sysptnhdr', 8, 'Replicated Table');

    insert into flags_text values ('sysptnhdr', 32,'System created Temp Table');
    insert into flags_text values ('sysptnhdr', 64,'User created Temp Table');
    insert into flags_text values ('sysptnhdr', 128,'Sort File');

    insert into flags_text values ('sysptnhdr', 256,'Contains Varchar Data Type');
    insert into flags_text values ('sysptnhdr', 512,'Contains BLOBSpace BLOBS');
    insert into flags_text values ('sysptnhdr', 1024,'Contains TBLSpace BLOBS');
    insert into flags_text values ('sysptnhdr', 2048,'Contains either Varchars,BLOBS or Rows > PAGESIZE-32');

    insert into flags_text values ('sysptnhdr', 4096,'Contains optical Sub-System BLOBS');
    insert into flags_text values ('sysptnhdr', 8192,'Permanent System created Table ( undroppable )');
    insert into flags_text values ('sysptnhdr', 16384,'Special Function Temp Tables, no Bitmap Maintenance');
    insert into flags_text values ('sysptnhdr', 32768,'Light Append Partition');

    insert into flags_text values ('sysptnhdr', 131072,'Hash Table');
    insert into flags_text values ('sysptnhdr', 262144,'Index Partition');
    insert into flags_text values ('sysptnhdr', 524288,'Sequence Object');
    insert into flags_text values ('sysptnhdr', 1048576,'Page free space cache disabled');
    insert into flags_text values ('sysptnhdr_flags2', 1,'Automatic Compression');	
{ Bitmap }

    insert into flags_text values ('sysptnbit',0,'Free Page');
    insert into flags_text values ('sysptnbit',1,'Remainder Page - free Space = Pagesize');
    insert into flags_text values ('sysptnbit',2,'PBLOB Page - free Space = Pagesize');
    insert into flags_text values ('sysptnbit',4,'Data Page with Room for another Row');
    insert into flags_text values ('sysptnbit',5,'Remainder Page - free Space between Pagesize and 2/3*Pagesize');
    insert into flags_text values ('sysptnbit',6,'PBLOB Page - free Space between Pagesize and 2/3*Pagesize');
    insert into flags_text values ('sysptnbit',8,'Index Page or Bitmap Page');
    insert into flags_text values ('sysptnbit',9,'Remainder Page - free Space between 2/3*Pagesize and 1/10*Pagesize');
    insert into flags_text values ('sysptnbit',10,'PBLOB Page - free Space between 2/3*Pagesize and 1/10*Pagesize');
    insert into flags_text values ('sysptnbit',12,'Data Page without Room for another Row');
    insert into flags_text values ('sysptnbit',13,'Remainder Page full - free Space < 1/10*Pagesize');
    insert into flags_text values ('sysptnbit',14,'PBLOB Page full - free Space < 1/10*Pagesize');

{ License Info }

    insert into flags_text values ('syslicenseinfo',1,
         'HDR Primary');
    insert into flags_text values ('syslicenseinfo',2,
        'HDR Secondary');
    insert into flags_text values ('syslicenseinfo',4,
        'HDR RSS');
    insert into flags_text values ('syslicenseinfo',8,
        'HDR SDS');
    insert into flags_text values ('syslicenseinfo',16,
        'Enterprise Replication');
    insert into flags_text values ('syslicenseinfo',512,
        'Parallel Data Query');
    insert into flags_text values ('syslicenseinfo',1024,
        'Database Mirroring');
    insert into flags_text values ('syslicenseinfo',8192,
        'Database Compression');
    insert into flags_text values ('syslicenseinfo',16384,
        'Database Defragmentation');
    insert into flags_text values ('syslicenseinfo',32768,
        'Enhanced de-duplication');

{ Create a table where the build status for 'sysutils' and any other system
  activity associated with building 'sysmaster' can be recorded }
 
    create table informix.smi_build_status (message_num integer);

{ Create a table where conversion messages can be entered by front end
  shell scripts for displaying into the logmessage file }
 
    create table informix.logmessage (message_num integer);

{ 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;

{ Stored procedure for converting unix time() long to date }
    create procedure informix.l2date ( l int ) returning date;
	return trunc((l/86400) + 25568 );
    end procedure;
    grant execute on informix.l2date to public as informix;

create procedure informix.physchunk( physaddr int ) returning int;
  return trunc(physaddr/1048576);
end procedure;
grant execute on informix.physchunk to public as informix;

create procedure informix.physpage( physaddr int ) returning int;
  return(mod(physaddr,1048576));
end procedure;
grant execute on informix.physpage to public as informix;

create procedure informix.physaddr(chunknum int, pagenum int) returning int;
  return((chunknum*1048576)+pagenum);
end procedure;
grant execute on informix.physaddr to public as informix;

create procedure informix.partdbsnum (partnum int ) returning int;
  return trunc(partnum/1048576);
end procedure;
grant execute on informix.partdbsnum to public as informix;

create procedure informix.partpagenum(partnum int) returning int;
  return(mod(partnum,1048576));
end procedure;
grant execute on informix.partpagenum to public as informix;

create procedure informix.partaddr(dbspnum int, pagenum int) returning int;
  return((dbspnum*1048576)+pagenum);
end procedure;
grant execute on informix.partaddr to public as informix;

{ Session Waits profile }
    create view informix.sysseswts (sid, reason, numwaits, cumtime, maxtime)
	as
	select a.sid, c.txt, b.wnum, b.wcumtime, b.wmaxtime
	  from sysrstcb a, systwaits b, flags_text c
	 where a.tid = b.tid
	   and b.wreason = c.flags
	   and c.tabname = 'systwaits';
    grant select on informix.sysseswts to public as informix;

{ Chunk Free List }
    create view informix.syschkextents ( ce_chknum, ce_extnum, ce_physaddr, ce_size )
    as select chknum, extnum, start, leng  from syschfree;
    grant select on informix.syschkextents to public as informix;

{ Partition Bit Maps }
    create view informix.systabpagtypes ( tp_partnum, tp_pagenum, tp_type )
    as select pb_partnum, pb_pagenum, pb_bitmap  from sysptnbit;
    grant select on informix.systabpagtypes to public as informix;

{ Logical Logs }
    create view informix.syslogs (number, uniqid, size, used, 
			 is_used, is_current, is_backed_up, 
			 is_new, is_archived, is_temp, is_pre_dropped, flags)
	as
	select number, uniqid, size, used, 
	       bitval(flags, '0x1'), bitval(flags, '0x2'), bitval(flags, '0x4'),
	       bitval(flags, '0x8'), bitval(flags, '0x10'), 
	       bitval(flags, '0x20'), bitval(flags, '0x40'), flags
	  from syslogfil
	 where number > 0;
    grant select on informix.syslogs to public as informix;

{ Chunks }
    create view informix.syschunks(chknum, dbsnum, nxchknum, pagesize, chksize, offset, 
			  nfree, mdsize, udsize, udfree,
			  is_offline, is_recovering, is_blobchunk, is_sbchunk,
			  is_inconsistent, is_extendable, flags, fname, 
			  mfname, moffset, mis_offline, mis_recovering, mflags)
	as
	select a.chknum, a.dbsnum, a.nxchunk, a.pagesize, a.chksize, a.offset, 
		a.nfree, a.mdsize, a.udsize, a.udfree,
		bitval(a.flags, '0x20'), bitval(a.flags, '0x80'), 
		bitval(a.flags, '0x200'), bitval(a.flags, '0x4000'),
		bitval(a.flags, '0x1000'), bitval(a.flags, '0x2000'),
		a.flags, a.fname, b.fname, b.offset, 
		bitval(b.flags, '0x20'), bitval(b.flags, '0x80'), 
		b.flags
	  from syschktab a, outer sysmchktab b
	 where a.chknum = b.chknum
	   and a.chknum > 0;
    grant select on informix.syschunks to public as informix;

{ Chunks (fast view) }
    create view informix.syschunks_fast(chknum, dbsnum, nxchknum, pagesize, chksize, offset,
                          nfree, mdsize, udsize, udfree,
                          is_offline, is_recovering, is_blobchunk, is_sbchunk,
                          is_inconsistent, is_extendable, flags, fname,
                          mfname, moffset, mis_offline, mis_recovering, mflags)
        as
        select a.chknum, a.dbsnum, a.nxchunk, a.pagesize, a.chksize, a.offset,
                a.nfree, a.mdsize, a.udsize, a.udfree,
                bitval(a.flags, '0x20'), bitval(a.flags, '0x80'),
                bitval(a.flags, '0x200'), bitval(a.flags, '0x4000'),
                bitval(a.flags, '0x1000'), bitval(a.flags, '0x2000'),
                a.flags, a.fname, b.fname, b.offset,
                bitval(b.flags, '0x20'), bitval(b.flags, '0x80'),
                b.flags
          from syschktab_fast a, outer sysmchktab_fast b
         where a.chknum = b.chknum
           and a.chknum > 0;
    grant select on informix.syschunks_fast to public as informix;

{ Dbspaces }
    create view informix.sysdbspaces(dbsnum, name, owner, pagesize, fchunk, nchunks, 
                            create_size, extend_size, max_size, is_mirrored, is_blobspace,
                            is_sbspace, is_temp, is_encrypted, flags)
        as
        select dbsnum, name, owner, pagesize, fchunk, nchunks, create_size, 
                extend_size, max_size, bitval(flags, '0x2'), bitval(flags, '0x10'), 
                bitval(flags, '0x8000'), bitval(flags, '0x2000'), 
		bitval(flags, '0x10000000'), flags
        from sysdbstab
        where dbsnum > 0;
    grant select on informix.sysdbspaces to public as informix;

{ Locks (keep for 6.0 compatibility) }
    create view informix.syslocks (dbsname, tabname, rowidlk, keynum, type,
			  owner, waiter)
	as
	select dbsname, b.tabname, rowidr, keynum, e.txt[1,4], d.sid, f.sid
	  from syslcktab a, systabnames b, systxptab c, sysrstcb d, 
	       flags_text e, outer sysrstcb f
	 where a.partnum = b.partnum
	   and a.owner = c.address
	   and c.owner = d.address
	   and a.wtlist = f.address
	   and e.tabname = 'syslcktab'
	   and e.flags = a.type;
    grant select on informix.syslocks to public as informix;

{ Locks }
    create view informix.syslocktab ( lk_id, lk_addr, lk_same, lk_wtlist, lk_owner,
                             lk_list, lk_type, lk_flags, lk_bsize, lk_keynum,
                             lk_rowid, lk_partnum, lk_kvobj, lk_dipnum, 
                             lk_grtime )
    as select indx, address, same, wtlist, owner, list, type, flags, bsize,
	keynum, rowidr, partnum, rowidn, dipnum, grtime from syslcktab;
    grant select on informix.syslocktab to public as informix;

{ Active sessions }
    create view informix.syssessions ( sid, username, uid, pid,
			      hostname, tty, connected, feprogram,
                              pooladdr,
			      is_wlatch, is_wlock, is_wbuff, is_wckpt,
			      is_wlogbuf, is_wtrans, is_monitor, is_incrit,
			      state
			      )
	as
	select a.sid, a.username, a.uid, a.pid,
		a.hostname, a.ttyerr, a.connected, a.progname, a.poolp,
		bitval(b.flags, '0x2'), bitval(b.flags, '0x4'),
		bitval(b.flags, '0x8'), bitval(b.flags, '0x10'), 
		bitval(b.flags, '0x1000'), bitval(b.flags, '0x40000'), 
		bitval(b.flags, '0x80'), bitval(b.flags, '0x100'), b.flags
	  from sysscblst a, sysrstcb b
	 where a.address = b.scb
	   and bitval(b.flags, '0x80000') = 1;	{ primary thread }
    grant select on informix.syssessions to public as informix;

{ Session activity profile }
    create view informix.syssesprof(sid, lockreqs, locksheld, lockwts, 
			   deadlks, lktouts, logrecs, isreads, 
			   iswrites, isrewrites, isdeletes, iscommits, 
			   isrollbacks, longtxs, bufreads, bufwrites, 
			   seqscans, pagreads, pagwrites, total_sorts, 
                           dsksorts, max_sortdiskspace, logspused, maxlogsp )
	as
        select sid,sum( upf_rqlock),sum(nlocks),sum(upf_wtlock),sum(upf_deadlk),
                   sum(upf_lktouts),sum(upf_lgrecs),sum(upf_isread),
                   sum(upf_iswrite),sum(upf_isrwrite),sum(upf_isdelete),
                   sum(upf_iscommit),sum(upf_isrollback),sum(upf_longtxs),
                   sum(upf_bufreads),sum(upf_bufwrites),sum(upf_seqscans),
                   sum(nreads), sum(nwrites), sum(upf_totsorts),
                   sum(upf_dsksorts),sum(upf_srtspmax),sum(upf_logspuse),
                   sum(upf_logspmax)
          from sysrstcb 
        	 where sid > 0
                group by sid;

    grant select on informix.syssesprof to public as informix;

{ system profil() data }

    -- Functions with most time:
    create view informix.sysprof_func (vpid, start_address, name, total_count) as
    select   vpid, s.address, s.name, sum(p.count) total_count
    from     syssymtab s, sysproftab p
    where    p.address between s.address and s.last_address
    group by vpid, s.address, name;
    grant select on informix.sysprof_func to public as informix;

    -- Addresses with the most time:
    create view informix.sysprof_address (vpid, start_address, name, address, count) as
    select  vpid, s.address start_address, s.name,  p.address, p.count
    from    syssymtab s, sysproftab p
    where   p.address between s.address and s.last_address;
    grant select on informix.sysprof_address to public as informix;

    -- Addresses with the most time as percentage:
    create view informix.sysprof_percentage(vpid, start_address, name, address, percentage) as
    select  vpid,
            s.address start_address, s.name, p.address,
            p.count / (select sum(p.count) from sysproftab p) percentage
    from    syssymtab s, sysproftab p
    where   p.address between s.address and s.last_address;
    grant select on informix.sysprof_percentage to public as informix;

{ User and system time by VP }
    create view informix.sysvpprof (vpid, class, usercpu, syscpu)
	as
	select a.vpid, a.classname, a.usecs_user, a.usecs_sys
	  from sysvplst a
	 where a.flags != 6;
    grant select on informix.sysvpprof to public as informix;

{ Partition profile }
    create view informix.sysptprof (dbsname, tabname, partnum,
    			   lockreqs, lockwts, deadlks, lktouts, 
			   isreads, iswrites, isrewrites, isdeletes,
                           bufreads, bufwrites, seqscans, pagreads,
                           pagwrites )
	as
	select a.dbsname, a.tabname, b.partnum,
		b.pf_rqlock, b.pf_wtlock, b.pf_deadlk, b.pf_lktouts, 
		b.pf_isread, b.pf_iswrite, b.pf_isrwrite, b.pf_isdelete,
                b.pf_bfcread, b.pf_bfcwrite, b.pf_seqscans, b.pf_dskreads,
                b.pf_dskwrites 
	  from systabnames a, sysptntab b
	 where a.partnum = b.partnum;
    grant select on informix.sysptprof to public as informix;

{ Profile listing }
    create view informix.sysprofile ( name, value )
	as
	select name[4,16], value
	  from sysshmhdr
	 where name matches 'pf_*';
    grant select on informix.sysprofile to public as informix;

{ Buffer Profile listing. }
    create view informix.sysbufprofile ( name, value )
	as
	select name[1,16], value
	  from sysshmhdr
	 where name matches '*_*K';
    grant select on informix.sysbufprofile to public as informix;

{ Auto checkpoint control block }
    create view informix.sysckptinfo (name, value)
        as
	select name[5,16], value
	from sysshmhdr
	where name matches 'acp_*';
    grant select on informix.sysckptinfo to public as informix;

{ Listing of supported configuration parameters }
    create view informix.sysconfig ( cf_id, cf_name, cf_flags, cf_original, cf_effective, cf_default )
        as   
        select cf_id, cf_name, cf_flags, cf_original, cf_effective, cf_default
          from syscfgtab
         where bitval(cf_flags, '0x1') = 0;
    grant select on informix.sysconfig to public as informix;

{ Extent listings (keep for 6.0 compatibility) }
    create view informix.sysextents ( dbsname, tabname, chunk, offset, size)
	as
	select dbsname, tabname, pe_chunk, pe_offset, pe_size
	  from systabnames a, sysptnext b
	 where a.partnum = b.pe_partnum;
    grant select on informix.sysextents to public as informix;

{ Extent listings }
    create view informix.systabextents ( te_partnum, te_extnum, te_chunk, te_offset,
                                te_size, te_pagenum )
    as select pe_partnum, pe_extnum, pe_chunk, pe_offset, pe_size, pe_log  
    from sysptnext;
    grant select on informix.systabextents to public as informix;

{ Data Replication info }
    create view informix.sysdri (type, state, name, intvl, timeout, lostfound, drauto,
			dridxauto)
	as select b.txt, c.txt, a.name, a.intvl, a.timeout, a.lostfound,
		  a.drauto, a.dridxauto
	  from sysdrcb a, flags_text b, flags_text c
	 where a.type = b.flags
	   and b.tabname = 'sysdrcb'
	   and a.state = c.flags
	   and c.tabname = 'sysdrcb';
    grant select on informix.sysdri to public as informix;

    create view informix.sysha_nodes (server, type, status)
        as
        select ha_primary, 'Primary', 'Active' from sysha_type
        union
        select server_name, 'SDS', server_status from syssrcsds
        union
        select name, 'HDR', state from sysdri
            where name != ''
            and 1 in (select ha_type from sysha_type)
        union
        select server_name, 'RSS', server_status type from syssrcrss
        union
        select sysha_type.ha_secondary, 'SDS', systrgsds.connection_status
            from systrgsds, sysha_type
            where sysha_type.ha_secondary != ''
            and 3 in (select ha_type from sysha_type)
        union
        select sysha_type.ha_secondary, 'HDR', sysdri.state from
            sysdri, sysha_type
            where sysha_type.ha_secondary != ''
            and 2 in (select ha_type from sysha_type)
        union
        select sysha_type.ha_secondary, 'RSS', systrgrss.connection_status
            from systrgrss, sysha_type
            where sysha_type.ha_secondary != ''
            and 4 in (select ha_type from sysha_type);

    grant select on informix.sysha_nodes to public as informix;

{ Databases }
    create view informix.sysdatabases (name, partnum, owner, created,
			      is_logging, is_buff_log, is_ansi, 
			      is_nls, is_case_insens, flags)
	as
	select name, partnum, owner, date(dbinfo('UTC_TO_DATETIME', created)),
	       bitval(flags, 1), bitval(flags, 2), bitval(flags, 4),
	       bitval(flags, 16), bitval(flags, 128), flags
	  from sysdbspartn;
    grant select on informix.sysdatabases to public as informix;

{ Threads view }
    create view informix.systhreads (th_id, th_addr, th_joinlist, th_joinnext,
		th_joinee, th_name, th_state, th_priority, th_class, th_vpid,
		th_mtxwait, th_conwait, th_waketime, th_startwait,
		th_startrun )
	as
	select  tid,  address,  joinlist, joinnext, joinee, name, state,
		priority, class, vpid, wtmutexp, wtcondp, sleep_time, 
		start_wait, last_run_time
	  from 	systcblst;
    grant select on informix.systhreads to public as informix;

{ Mutexes view }
    create view informix.sysmutexes (mtx_id, mtx_address, mtx_lock, mtx_holder, 
			mtx_wtlist, mtx_name )
	as
	select  mtx_id, mtx_address, mtx_lock, mtx_holder, mtx_wtlist,
		mtx_name
	  from 	sysmtxlst;
    grant select on informix.sysmutexes to public as informix;

{ Conditions view }
    create view informix.sysconditions (con_id, con_address, con_lock, 
			con_wtlist, con_name )
	as
	select  con_id, con_address, con_lock, con_wtlist, con_name
	  from 	sysconlst;
    grant select on informix.sysconditions to public as informix;

{ Pools view }
    create view informix.syspools (po_id, po_address, po_name, po_class, 
			po_freeamt, po_usedamt )
	as
	select  po_id, po_address, po_name, po_class, po_freeamt, po_usedamt
	  from 	syspoollst;
    grant select on informix.syspools to public as informix;

{ Segments view }
    create view informix.syssegments (seg_address, seg_class, seg_size, seg_osshmid,
			seg_osshmkey, seg_shmaddr, seg_ovhd,
			seg_blkused, seg_blkfree)
	as
	select  seg_address, seg_class, seg_size, seg_osshmid,seg_osshmkey,
		seg_shmaddr, seg_ovhd, seg_blkused, seg_blkfree
	  from 	sysseglst;
    grant select on informix.syssegments to public as informix;

{ Threads Wait Stats view }
    create view informix.systhreadwaits (tw_tid,	tw_reason, tw_num, tw_cumtime,
			tw_maxtime)	
	as
	select tid, wreason, wnum, wcumtime, wmaxtime
	  from	systwaits;
    grant select on informix.systhreadwaits to public as informix;

{ Mutex queue view }
    create view informix.sysmutq (mq_mtxid, mq_nwaits, mq_nservs, mq_curlen, mq_totlen,
			mq_maxlen, mq_waittime, mq_servtime, mq_maxwait)
	as
	select mtx_id, mtx_nwaits, mtx_nservs, mtx_curlen, mtx_totlen, 
		mtx_maxlen, mtx_waittime, mtx_servtime, mtx_maxwait
	  from	sysmtxlst;
    grant select on informix.sysmutq to public as informix;

{ Condition queue view }
    create view informix.sysconq (cq_conid, cq_nwaits, cq_nservs, cq_curlen, cq_totlen,
			cq_maxlen, cq_waittime, cq_servtime, cq_maxwait)
	as
	select con_id, con_nwaits, con_nservs, con_curlen, con_totlen,
		con_maxlen, con_waittime, con_servtime, con_maxwait
	  from	sysconlst;
    grant select on informix.sysconq to public as informix;

{ Userthreads }

  create view informix.sysuserthreads (
    us_indx, us_address, us_txp, us_txwait, us_txlist, us_iserrno,
    us_isrecnum, us_isfragnum, us_uid, us_name, us_sid,
    us_scb, us_tid, us_mttcb, us_nxtthread, us_flags, 
    us_nlocks, us_lastlktype, us_lktout, us_lkwait, us_lklist,
    us_lkwttype, us_lkthreadlist, us_lktolist, us_bfwait, us_bflist,
    us_bfwtflag, us_bfheldcnt, us_lbufwake, us_lgbuffered, us_rqlock,
    us_wtlock, us_deadlk, us_lktouts, us_lgrecs, us_isread, us_iswrite,
    us_isrwrite, us_isdelete, us_iscommit, us_isrollback, us_longtxs,
    us_bufreads, us_bufwrites, us_pagreads, us_pagwrites, us_seqscans, 
    us_totsorts, us_dsksorts, us_srtspmax, us_logspuse, us_logspmax
    ) as select
     indx, address, txp, txwait, txlist, iserrno, isrecnum, isfragnum, 
     uid, username, sid, scb, tid, mttcb, nxtthread, flags, nlocks,
     lastlock, lktout, lkwait, wtlist, lkwttype, lkthreadlist, tolist,
     bfwait, bflist, bfwtflag, bfheld_count, lbufwake, bufferlogging,
     upf_rqlock, upf_wtlock, upf_deadlk, upf_lktouts, upf_lgrecs,
     upf_isread, upf_iswrite, upf_isrwrite, upf_isdelete, upf_iscommit,
     upf_isrollback, upf_longtxs, upf_bufreads, upf_bufwrites, nreads,
     nwrites, upf_seqscans, upf_totsorts, upf_dsksorts, upf_srtspmax, 
     upf_logspuse, upf_logspmax from sysrstcb;

     grant select on informix.sysuserthreads to public as informix;

{ systrans }

  create view informix.systrans (
    tx_id, tx_addr, tx_flags, tx_mutex, tx_logbeg, tx_loguniq, tx_logpos,
    tx_lklist, tx_lkmutex, tx_owner, tx_wtlist, tx_ptlist, tx_nlocks,
    tx_lktout, tx_isolevel, tx_longtx, tx_coordinator, tx_nremotes )
    as select
	indx, address, flags, latchp, logbeg, loguniq, logpos, lklist,
        lklatchp, owner, wtlist, ptlist, nlocks, lkwait, isolevel, longtx,
	istar_coord, nremotes
    from systxptab;

    grant select on informix.systrans to public as informix;
 
{ Partition Headers }
    create view informix.systabinfo 
	(
	ti_partnum, ti_flags, ti_rowsize, ti_ncols, ti_nkeys, ti_nextns,
	ti_pagesize, ti_created, ti_serialv, ti_fextsiz, ti_nextsiz, ti_nptotal,
	ti_npused, ti_npdata, ti_octptnm, ti_nrows 
	)
        as select
	partnum, flags, rowsize, ncols, nkeys, nextns, pagesize, created, serialv,
	fextsiz, nextsiz, nptotal, npused, npdata, octptnm, nrows
        from sysptnhdr;

        grant select on informix.systabinfo to public as informix;

{ Page Headers 
    create view informix.systabpaghdrs 
	( pg_partnum, pg_pagenum, pg_chunk, pg_offset, pg_stamp, pg_chksum,
	pg_pagesize, pg_nslots, pg_flags, pg_frptr, pg_frcnt, pg_next, pg_prev )
        as select pg_partnum, pg_pagenum, pg_chunk, pg_offset, pg_stamp,
        pg_chksum, pg_pagesize, pg_nslots, pg_flags, pg_frptr,
        pg_frcnt, pg_next from syspaghdr where 
        pg_partnum > 1048576;

        grant select on informix.systabpaghdrs to public as informix;
}

{ Page Headers }
    create view informix.sysphyspaghdrs
	( pg_pagenum, pg_chunk, pg_offset, pg_stamp, pg_chksum,
	pg_pagesize, pg_nslots, pg_flags, pg_frptr, pg_frcnt, pg_next, pg_prev )
        as select
	pg_pagenum, pg_chunk, pg_offset, pg_stamp, pg_chksum,
	pg_pagesize, pg_nslots, pg_flags, pg_frptr, pg_frcnt, pg_next, pg_prev 
        from syspaghdr where
        pg_partnum = 0;

        grant select on informix.sysphyspaghdrs to public as informix;

{ C2 Audit mask table }
  create table informix.sysaudit
        (
        username        char(32),       { user name                     }
        succ1           integer,        { success bitmask 1             }
        succ2           integer,        { success bitmask 2             }
        succ3           integer,        { success bitmask 3             }
        succ4           integer,        { success bitmask 4             }
        succ5           integer,        { success bitmask 5             }
        succ6           integer,        { success bitmask 6             }
        succ7           integer,        { success bitmask 7             }
        succ8           integer,        { success bitmask 8             }
        fail1           integer,        { failure bitmask 1             }
        fail2           integer,        { failure bitmask 2             }
        fail3           integer,        { failure bitmask 3             }
        fail4           integer,        { failure bitmask 4             }
        fail5           integer,        { failure bitmask 5             }
        fail6           integer,        { failure bitmask 6             }
        fail7           integer,        { failure bitmask 7             }
        fail8           integer         { failure bitmask 8             }
        ) lock mode row;
    create unique index informix.sysaudit_ix1 on sysaudit(username) in table;
    revoke all on informix.sysaudit from public as informix;

{ Chunk io stats }
    create view informix.syschkio (chunknum, 
			    reads, pagesread,
			    writes, pageswritten,
			    mreads, mpagesread,
			    mwrites, mpageswritten)
	as
        select a.chknum, 
		a.reads, a.pagesread,
		a.writes, a.pageswritten,
		b.reads, b.pagesread,
		b.writes, b.pageswritten
	  from syschktab_fast a, outer sysmchktab_fast b
	 where a.chknum > 0
	   and a.chknum = b.chknum;
    grant select on informix.syschkio to public as informix;

{ Locale in which the database was created in }
    create view informix.sysdbslocale (dbs_dbsname, dbs_collate)
	as
        select b.name, a.collate
	  from systabnames a, sysdbspartn b
	 where a.partnum = b.partnum;
    grant select on informix.sysdbslocale to public as informix;

{ SQL Dictionary cache }
    create view informix.sysdiccache (dic_hashno, dic_chainno,
		    dic_refcount, dic_dirtyflag, dic_heapptr,
		    dic_dbname, dic_servername, dic_ownername, dic_tabname)
        as
        select dic_hashno, dic_chainno,
		dic_refcount, bitval(dic_flags, '0x00800000'), hex(dic_heapptr),
		dic_dbname, dic_servername, dic_ownername, dic_tabname
        from sysdic;
    grant select on informix.sysdiccache to public as informix;

{ SQL Distribution cache }
    create view informix.sysdistcache (dis_hashno, dis_chainno,
		    dis_id, dis_refcount, dis_dropped, dis_heapptr,
		    dis_dbname, dis_servername, dis_ownername, dis_distname)
        as
        select dis_hashno, dis_chainno,
		dis_id, dis_refcnt, dis_delete, hex(dis_heapptr),
		dis_dbname, dis_servername, dis_ownername, dis_name
        from sysdsc;
    grant select on informix.sysdistcache to public as informix;

{ SQL Procedure cache }
    create view informix.sysproccache (prc_hashno, prc_chainno,
		    prc_id, prc_refcount, prc_dropped, prc_heapptr,
		    prc_dbname, prc_servername, prc_ownername, prc_procname)
        as
        select prc_hashno, prc_chainno,
		prc_id, prc_refcnt, prc_delete, hex(prc_heapptr),
		prc_dbname, prc_servername, prc_ownername, prc_name
        from sysprc;
    grant select on informix.sysproccache to public as informix;

{ SQL Statements }
    create view informix.syssqlcurall (
	sqc_sessionid, sqc_currdb, sqc_isolationlevel, sqc_lockmode, sqc_sqerrno, 
		sqc_isamerr, sqc_fevers)
	as
        select scb_sessionid, odb_dbname, ft.txt, scb_lockmode,
	       scb_sqerrno, scb_iserrno, scb_feversion
        from syssqscb, outer ( sysopendb, flags_text ft )
	where scb_sessionid == odb_sessionid
 	  and odb_iscurrent == 'Y'
	  and ft.tabname == 'sysopendb'
	  and ft.flags   == odb_isolation
	  and scb_feversion > '0.00';

{ SQL Current session }
    create view informix.syssqlcurses (
	scs_sessionid, scs_currdb, scs_isolationlevel, scs_lockmode,
		scs_executions, scs_cumtime, scs_bufreads, scs_pagereads,
		scs_bufwrites, scs_pagewrites, scs_totsorts, scs_dsksorts,
		scs_sortspmax, scs_sqerrno, scs_isamerr, scs_fevers, 
		scs_sqlstatement)
    as
    select scb_sessionid,  odb_dbname, ft.txt, scb_lockmode, sdb_executions, 
		sdb_cumtime, sdb_bufreads, sdb_pagereads, sdb_bufwrites, 
		sdb_pagewrites, sdb_totsorts, sdb_dsksorts, sdb_sortspmax,
		scb_sqerrno, scb_iserrno, scb_feversion, cbl_stmt
    from syssqscb, syssdblock, outer sysconblock,
	outer ( sysopendb, flags_text ft )
        where scb_sessionid == odb_sessionid
          and scb_sessionid == sdb_sessionid
          and scb_sessionid == cbl_sessionid
	  and scb_feversion > '0.00'
          and sdb_sdbno == cbl_sdbno
          and sdb_iscurrent == 'Y'
          and odb_iscurrent == 'Y'
          and cbl_ismainblock == 'Y'
	  and ft.tabname == 'sysopendb'
	  and ft.flags   == odb_isolation
          ;

{ Show feature information }
    create view sysfeatures (		{ Internal Use Only		}
            week, year, version, max_cpu_vps, max_vps,
            max_conns, max_sec_conns, max_sds_clones, max_rss_clones,
            total_size, total_size_used,
            max_memory, max_memory_used, is_primary, is_secondary,
            is_sds, is_rss, is_er, is_pdq )
    AS
    select  week, year, version, max_cpu_vps, max_vps,
            max_conns, max_sec_conns, max_sds_clones, max_rss_clones,
            format_units(total_size,'M'), 
            format_units(total_size_used,'M'),
            format_units(max_memory,'M'), 
            format_units(max_memory_used,'M'),
            decode(bitand(feature_flags, 1),0,0,1),
            decode(bitand(feature_flags, 2),0,0,1),
            decode(bitand(feature_flags, 8),0,0,1),
            decode(bitand(feature_flags, 4),0,0,1),
            decode(bitand(feature_flags, 16),0,0,1),
            decode(bitand(feature_flags, 512),0,0,1)
    from syslicenseinfo 
    ;

{ Show sqexplain information }
    create view informix.syssqexplain (		{ Internal Use Only		}
		sqx_sessionid, sqx_sdbno, sqx_iscurrent, sqx_executions, 
		sqx_cumtime, sqx_bufreads, sqx_pagereads, sqx_bufwrites, 
		sqx_pagewrites, sqx_totsorts, sqx_dsksorts, sqx_sortspmax,
		sqx_conbno, sqx_ismain, sqx_selflag, sqx_estcost, sqx_estrows,
		sqx_seqscan, sqx_srtscan, sqx_autoindex, sqx_index, sqx_remsql,
		sqx_mrgjoin, sqx_dynhashjoin, sqx_keyonly, sqx_tempfile,
		sqx_tempview, sqx_secthreads, sqx_sqlstatement)
    as
    select sdb_sessionid,  sdb_sdbno, sdb_iscurrent, sdb_executions,
	    sdb_cumtime, sdb_bufreads, sdb_pagereads, sdb_bufwrites, 
	    sdb_pagewrites, sdb_totsorts, sdb_dsksorts, sdb_sortspmax,
	   cbl_conbno, cbl_ismainblock, ft.txt, cbl_estcost, cbl_estrows,
	    cbl_seqscan, cbl_srtscan, cbl_autoindex, cbl_index, cbl_remsql,
	    cbl_mrgjoin, cbl_dynhashjoin, cbl_keyonly, cbl_tempfile,
	    cbl_tempview, cbl_secthreads, cbl_stmt
    from syssdblock, outer ( sysconblock, flags_text ft )
        where sdb_sessionid == cbl_sessionid
          and sdb_sdbno     == cbl_sdbno
	  and ft.tabname    == 'sqltype'
	  and ft.flags      == cbl_selflag
          ;

{ Connection Manager }
    create view informix.syscmsm ( sid, name, host, 
			unit, type, servers, foc, flag, 
			sla_name, sla_define, connections)
	as select syscmsmtab.sid, syscmsmtab.name, syscmsmtab.host, 
		  syscmsmunit.unit, syscmsmunit.type, syscmsmunit.servers, 
			syscmsmunit.foc, syscmsmunit.flag, 
		  sla_name, sla_define, connections
	from syscmsmtab, syscmsmunit, syscmsmsla 
	where syscmsmtab.sid = syscmsmsla.sid
		and syscmsmtab.sid = syscmsmunit.sid
		and syscmsmunit.unit = syscmsmsla.unit;
    grant select on informix.syscmsm to public as informix;

{ Compression Dictionaries }
    create view informix.syscompdicts (dict_partnum, dict_code_version, dict_dbsnum, 
			      dict_create_timestamp, dict_create_loguniqid,
			      dict_create_logpos, dict_drop_timestamp,
			      dict_drop_loguniqid, dict_drop_logpos)
	as
	select dict_partnum, dict_code_version, dict_dbsnum, 
	       dict_create_timestamp, dict_create_loguniqid,
	       dict_create_logpos, dict_drop_timestamp,
	       dict_drop_loguniqid, dict_drop_logpos
	  from syscompdicts_full;
    grant select on informix.syscompdicts to public as informix;

{ AQT tables }
    create view informix.sysaqttab (aqt,dbname,tabid,fact) as
        select distinct a.aqt, a.dbname, tabid,
		case when factid = tabid then 1 else 0 end
            from sysaqt a, sysaqtjp j
            where a.aqt = j.aqt;
    grant select on informix.sysaqttab to public as informix;

{ DWA coordinators }
    create view informix.sysdwacoord (dwa, coord, ip, port) as
        select trim(i.dbsvrnm),trim(c.dbsvrnm),trim(c.hostname),trim(c.svcname)
            from syssqlhosts i,syssqlhosts c
            where i.nettype = 'group'
              and i.dbsvrnm = c.svrgroup
              and c.svrtype = 'dw';
    grant select on informix.sysdwacoord to public as informix;

{ Grid ID }
    create view informix.sysgrid(gridid, gridname) as
        select ifx_node_id(), ifx_node_name()  
          from systables where tabid = 1;
    revoke all on informix.sysgrid from public as informix;
    grant select on informix.sysgrid to public as informix;

{ Extspaces Table }
    create table informix.sysextspaces           { Internal Use Only }
        (
        id              integer,        { external space id             }
        name            char(128),      { extspace name                 }
        owner           char(32),       { extspace owner                }
        flags           integer,        { extspace flags                }
        refcnt          integer,        { extspace reference count      }
        locsize         integer,        { size of location              }
        locatio         char(256)       { external space location       }
        );
    create unique index informix.sysextspace_ix1 on sysextspaces(name) in table;
    revoke all on informix.sysextspaces from public as informix;
    grant select on informix.sysextspaces to public as informix;

{ Create stored procedure start_onpload }
create procedure informix.start_onpload(args char(200)) returning int;
    define command char(255); -- build command string here
    define rtnsql  int;       -- place holder for exception sqlcode setting
    define rtnisam int;       -- isam error code. Should be onpload exit status 
    define osname  char(128); -- Operating System name.

   {If $INFORMIXDIR/bin/onpload not found try /usr/informix/bin/onpload}
    on exception in (-668) set rtnsql, rtnisam
       if (osname != 'Windows') then
         if rtnisam = -1 then
            let command = '/usr/informix/bin/onpload ' || args;
            system (command);
            return 0;
         end if
       end if
       return rtnisam;
    end exception

    if  (args matches '*[;$%]*') then
         return -1;
    end if;

    SELECT os_name into osname from sysmachineinfo;

    if (osname = 'Windows') then
	let command = 'cmd /c %INFORMIXDIR%\bin\onpload ' || args;
    else
    	let command = '$INFORMIXDIR/bin/onpload ' || args;
    end if
    system (command);
    return 0;
end procedure;

grant execute on informix.start_onpload to public as informix;

{ Create UDR functions for memory resident tables }
create DBA function informix.ifx_make_res(integer)
returning integer
external name '(ifx_res_pnum)'
language c;

create DBA function informix.ifx_make_unres(integer)
returning integer
external name '(ifx_unres_pnum)'
language c;

create DBA function informix.ifx_make_res(char(256))
returning integer
external name '(ifx_res_name)'
language c;

create DBA function informix.ifx_make_unres(char(256))
returning integer
external name '(ifx_unres_name)'
language c;

create function string_to_utf8(string lvarchar(4096), source_locale lvarchar)
returns lvarchar
external name '(string_to_utf8)'
language C;

create function informix.yieldn()
returns informix.integer
external name '(admin_yield)'
language C;

create function informix.yieldn(informix.integer)
returns informix.integer
external name '(admin_yieldn)'
language C;

create function informix.getcurtime()
returns informix.integer
external name '(admin_current_time)'
language C;

create DBA function informix.ifx_get_service(lvarchar)
returning integer
external name '(ifxGetService)'
language c;

grant execute on informix.ifx_get_service to public as informix;

create DBA function informix.ifx_get_hostaddr(lvarchar)
returning lvarchar
external name '(ifxGetHostAddr)'
language c;

grant execute on informix.ifx_get_hostaddr to public as informix;

create DBA function informix.vpclass_to_classid(lvarchar) 
returns integer
external name '(vpclass_to_classid)' 
language c;

grant execute on informix.vpclass_to_classid to public as informix;

update sysprocedures set mode = 'o' where procname = 'bitval';
update sysprocedures set mode = 'o' where procname = 'l2date';
update sysprocedures set mode = 'o' where procname = 'physchunk';
update sysprocedures set mode = 'o' where procname = 'physpage';
update sysprocedures set mode = 'o' where procname = 'physaddr';
update sysprocedures set mode = 'o' where procname = 'partdbsnum';
update sysprocedures set mode = 'o' where procname = 'partpagenum';
update sysprocedures set mode = 'o' where procname = 'partaddr';
update sysprocedures set mode = 'o' where procname = 'start_onpload';
update sysprocedures set mode = 'd' where procname = 'ifx_make_res';
update sysprocedures set mode = 'd' where procname = 'ifx_make_unres';

grant connect to public;

update statistics;

close database;

{*******************************************************************}
{  See if we need to add the cdr views as part of the (re)build of  }
{  sysmaster database.                                              }
{*******************************************************************}

database sysmaster;

create procedure syscheckcdr()
define rslt integer;
define cmd char(1000);
define osname char(128);
define version decimal;

select count(*) into rslt from sysmaster:sysdatabases
        where name = 'syscdr';

if  (rslt == 1) then
    select os_name into osname from sysmachineinfo;

    select verstamp into version from syscdr:'informix'.gcversion;

    { Note: Keep this in sync with syscdr.sql }
    if  (version == 9) then
        if  (osname = 'Windows') then
          let cmd = 'cmd /c dbaccess sysmaster %INFORMIXDIR%\etc\syscdrview.sql';
        else
          let cmd = 'dbaccess sysmaster $INFORMIXDIR/etc/syscdrview.sql';
        end if;
        system(cmd);
    end if;
end if;
end procedure;

execute procedure syscheckcdr();

drop procedure syscheckcdr;

close database;

