$INFORMIXDIR/etc/sysmaster.sql
Summary
- Tables
-
arc_archive_event
arc_db_file
arc_dbspace
arc_dbspace_set
arc_diskspace_mgr
arc_directory
arc_file
arc_file_copy
arc_pending_req
arc_phys_dev
arc_req_vset
arc_request
arc_save_set
arc_replicate
arc_server
arc_rep_archive
arc_rep_parent
arc_rep_table
arc_version
arc_vol_lock
arc_volume
arc_vset
arc_vset_user
flags_text
logmessage
smi_build_status
sysdbspartn
syschfree
sysbufhdr
sysdbstab
syschktab
sysbtcreq
sysadtinfo
sysaudit
syscfgtab
sysdrcb
syscdrack_buf
syscdrack_txn
syscdrctrl_buf
syscdrctrl_txn
syscdrq
syscdrprog
syscdrtx
syscdrs
syscdrsend_txn
syscdrsync_txn
syscdrrecv_txn
syscdrsend_buf
syscdrsync_buf
syscdrrecv_buf
sysconlst
sysdic
sysdsc
sysconblock
syscrtadt
sysextspaces
syslcktab
sysmchktab
syslogfil
sysmtxlst
syslrus
syspaghdr
sysplog
syspoollst
sysprc
sysptncol
sysptnbit
sysptnext
sysptnhdr
sysptnkey
sysptntab
sysopendb
sysrawdsk
systabnames
syssltdat
sysslttab
sysrstcb
systraces
systxptab
sysscblst
sysshmem
sysshmhdr
systcblst
systwaits
sysseglst
syssdblock
sysshmvals
syssqlstat
syssqscb
sysvplst
- Views
-
arc_ae_view
arc_db_file_view
arc_directory_view
arc_file_copy_view
arc_file_copy_view
arc_file_view
arc_pendreq_view
arc_req_vset_view
arc_request_view
arc_save_set_view
arc_vol_lock_view
arc_volume_view
arc_vset_view
arc_vset_user_view
syschkextents
syschunks
sysdbspaces
sysconfig
sysextents
sysdatabases
sysdri
syschkio
sysconditions
sysconq
sysdbslocale
sysdiccache
sysdistcache
syslocks
syslocktab
syslogs
sysmutexes
sysmutq
sysphyspaghdrs
syspools
sysproccache
sysprofile
sysptprof
sysseswts
systabpagtypes
syssesprof
syssessions
systabextents
systhreads
syssegments
systhreadwaits
sysvpprof
sysuserthreads
systabinfo
systabpaghdrs
systrans
syssqexplain
syssqlcurall
syssqlcurses
- Routines
-
bitval
dbexp
dbimp
ifx_make_res
ifx_make_res
ifx_make_unres
ifx_make_unres
l2date
partaddr
partdbsnum
partpagenum
physaddr
physchunk
physpage
start_onpload
Source
{ ************************************************************************* }
{ }
{ INFORMIX SOFTWARE, INC. }
{ }
{ Title: sysmaster.sql }
{ Sccsid: @(#)sysmaster.sql 9.35 12/3/93 18:06:28 }
{ 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/sysmaster.sql.ODS, }
{ 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;
{ databases }
CREATE TABLE 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 sysdbs_nameix ON sysdbspartn(name);
REVOKE ALL ON sysdbspartn FROM PUBLIC;
GRANT SELECT ON sysdbspartn TO PUBLIC;
{ join for partnums to table names }
CREATE TABLE 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 }
);
CREATE UNIQUE INDEX systabs_pnix ON systabnames(partnum);
REVOKE ALL ON systabnames FROM PUBLIC;
GRANT SELECT ON systabnames TO PUBLIC;
{ Raw Disk }
CREATE TABLE sysrawdsk { Internal Use Only }
(
pagenum INTEGER, { physical page address }
offset SMALLINT, { bytes into page }
loc CHAR(14), { location representation }
hexdata CHAR(40), { 16 bytes hexdumped from offset}
ascdata CHAR(16) { 16 bytes ascii-dumped }
);
CREATE UNIQUE INDEX sysrawdskidx ON sysrawdsk (pagenum, offset);
REVOKE ALL ON sysrawdsk FROM PUBLIC;
{ Page Headers }
CREATE TABLE syspaghdr
(
pg_partnum INTEGER, { partition number of page }
pg_pagenum INTEGER, { logical page number in partn }
pg_physaddr INTEGER, { pg_addr }
pg_stamp INTEGER, { pg_stamp }
pg_stamp2 INTEGER, { pg_stamp2 }
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 }
);
CREATE UNIQUE INDEX syspaghdridx ON syspaghdr (pg_partnum, pg_pagenum);
REVOKE ALL ON syspaghdr FROM PUBLIC;
GRANT SELECT ON syspaghdr TO PUBLIC;
{ Slot Tables }
CREATE TABLE 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 sysslttabidx ON sysslttab (partnum, pagenum, slotnum);
REVOKE ALL ON sysslttab FROM PUBLIC;
GRANT SELECT ON sysslttab TO PUBLIC;
{ Slot Data }
CREATE TABLE 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(20), { location representation }
hexdata CHAR(40), { 16 bytes hexdumped from offset}
ascdata CHAR(16) { 16 bytes ascii dumped }
);
CREATE UNIQUE INDEX syssltdatidx ON
syssltdat (partnum, pagenum, slotnum, slotoff);
REVOKE ALL ON syssltdat FROM PUBLIC;
{ Chunk Free List }
CREATE TABLE 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 syschfreeidx ON syschfree (chknum, extnum);
REVOKE ALL ON syschfree FROM PUBLIC;
GRANT SELECT ON syschfree TO PUBLIC;
{ Partition Headers }
CREATE TABLE 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 }
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 INTEGER { number of data rows }
);
CREATE UNIQUE INDEX sysptnhdridx ON sysptnhdr (partnum);
REVOKE ALL ON sysptnhdr FROM PUBLIC;
GRANT SELECT ON sysptnhdr TO PUBLIC;
{ Partition Key Descriptions }
CREATE TABLE sysptnkey { Internal Use Only }
(
partnum INTEGER, { partnum for partition }
keynum SMALLINT, { keynumber for key }
flags SMALLINT, { key flags }
rootnode INTEGER, { 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 sysptnkeyidx ON sysptnkey (partnum, keynum, kpartno);
REVOKE ALL ON sysptnkey FROM PUBLIC;
GRANT SELECT ON sysptnkey TO PUBLIC;
{ Partition Extent Descriptions }
CREATE TABLE sysptnext { Internal Use Only }
(
pe_partnum INTEGER, { partnum for this partition }
pe_extnum SMALLINT, { extent number }
pe_phys INTEGER, { physical addr for this extent }
pe_size INTEGER, { size of this extent }
pe_log INTEGER { logical page for start }
);
CREATE UNIQUE INDEX sysptnextidx ON sysptnext (pe_partnum, pe_extnum);
REVOKE ALL ON sysptnext FROM PUBLIC;
GRANT SELECT ON sysptnext TO PUBLIC;
{ Partition Column Descriptions }
CREATE TABLE 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 sysptncolidx ON sysptncol (partnum, colnum);
REVOKE ALL ON sysptncol FROM PUBLIC;
GRANT SELECT ON sysptncol TO PUBLIC;
{ Partition Bit Maps }
CREATE TABLE sysptnbit
(
pb_partnum INTEGER, { partnum for this partition }
pb_pagenum INTEGER, { logical pagenum represented }
pb_bitmap INTEGER { bitmap value for page }
);
CREATE UNIQUE INDEX sysptnbitidx ON sysptnbit (pb_partnum, pb_pagenum);
REVOKE ALL ON sysptnbit FROM PUBLIC;
GRANT SELECT ON sysptnbit TO PUBLIC;
{ rsam thread control blocks }
CREATE TABLE sysrstcb { Internal Use Only }
(
indx INTEGER, { index into rstcb table (rs_number) }
address INTEGER, { addr of rstcb structure }
txp INTEGER, { addr of txp }
localtxp INTEGER, { addr of local txp (XA only) }
tmptxp INTEGER, { addr of temp file txp }
savetxp INTEGER,
next INTEGER, { 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 SMALLINT, { user id }
asyncerr INTEGER, { address of async error }
username CHAR(32), { user name }
timeout SMALLINT, { lock timeout counter }
nxtthread INTEGER, { addr of next if >1 threads }
sid INTEGER, { session id }
scb INTEGER, { addr of scb }
ostcb INTEGER, { addr of ostcb }
gentcb INTEGER, { addr of gentcb }
tid INTEGER, { thread id }
join SMALLINT, { thread will join others }
mttcb INTEGER, { addr of mt thread cb }
aio INTEGER, { addr of aio request struct }
mirror_aio INTEGER, { addr of mirror req struct }
lkwait INTEGER, { waiting for this lock }
lkwttype INTEGER, { lock type waiting for }
bfwait INTEGER, { waiting for this buffer }
bfwtflag SMALLINT, { buffer wait type flag }
txwait INTEGER, { waiting for this transaction }
txsusp INTEGER, { suspended transaction }
wtlist INTEGER, { addr of next waiter on lock }
wtthrlist INTEGER, { addr of next thread in trans }
bflist INTEGER, { addr of next waiter on buff }
txlist INTEGER, { addr of next waiter on tx }
lbufwake INTEGER, { addr of log buffer for wake up}
nreads INTEGER, { number of reads }
nwrites INTEGER, { number of writes }
tolist INTEGER, { addr of next in timeout list }
nopens INTEGER, { size of open table }
nfiles INTEGER, { size of file table }
opentab INTEGER, { open table }
opfree INTEGER,
nextopen INTEGER,
filetab INTEGER, { file table }
hfiles INTEGER,
flfree INTEGER,
nextfile INTEGER,
tmprow INTEGER, { temp space for row }
tmpsize INTEGER, { size of tmprow space }
cmprow INTEGER, { temp space for compressed row }
rcmprow INTEGER, { temp space for reading cmprow }
rowupdatep INTEGER, { address of row update info ** }
isbisfd INTEGER, { isfd for isb* routines }
blobptr INTEGER, { ptr to current open blob }
blobrarea INTEGER, { blobpage buffer read area }
blobrszbuf INTEGER, { size of blob buffer read area }
blobrbufp INTEGER, { addr of blob buffer read }
blobwarea INTEGER, { blobpage buffer write area }
blobwszbuf INTEGER, { size of blob buffer write area}
blobwbufp INTEGER, { addr of blob buffer write }
blobcarea INTEGER, { blobpage buffer copy area }
blobcszbuf INTEGER, { size of blob buffer copy area }
blobcbufp INTEGER, { addr of blob buffer copy }
blobpiecesp INTEGER, { addr of blobpieces struct ** }
affp INTEGER,
afid INTEGER,
afcnt 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 INTEGER, { auxiliary bcpy table }
abcpytabmult INTEGER, { size multiple for aux bcpy tab}
logbuff INTEGER, { normal log buffer }
logbuff_beg INTEGER,
undologbuff INTEGER, { undo log buffer }
tmplogrec INTEGER,
btp INTEGER, { addr of current bt struct ** }
rkeysfree INTEGER,
turbonum INTEGER,
lastrsfd INTEGER, { longest rsfd open }
svptnum INTEGER, { savepoint number }
numsorts INTEGER, { number of open sorts allowed }
srttab INTEGER, { addr of sort table }
srttmpdir INTEGER, { ptr to sort temp dir pathname }
srtfileid INTEGER, { file id for sort file }
privdata INTEGER, { generic pointer to private mem}
precnum INTEGER,
rootaddr INTEGER, { root node of idx being built }
relocking INTEGER, { relock on recovery }
pitstop INTEGER,
pitaction INTEGER,
debugerrno INTEGER,
bfheld_count INTEGER,
logbu INTEGER,
arcbu INTEGER,
physrecvr INTEGER,
logrecvr INTEGER,
recvryflag INTEGER,
nlogs INTEGER,
logs INTEGER,
auditp INTEGER,
nexttrace INTEGER,
ntraces INTEGER,
traces INTEGER,
trflags INTEGER,
opsubbuf INTEGER,
opbuf INTEGER,
opbufsize INTEGER,
blobtxtabp INTEGER,
nblobtxs INTEGER,
maxnbtxs INTEGER,
onut_bufflist INTEGER,
onut_cb INTEGER,
upf_rqlock INTEGER, { number of locks requested }
upf_wtlock INTEGER, { ... lock waits }
upf_deadlk INTEGER, { ... deadlocks detected }
upf_lktouts INTEGER, { ... lock timeouts }
upf_lgrecs INTEGER, { ... log records written }
upf_isread INTEGER, { ... reads }
upf_iswrite INTEGER, { ... writes }
upf_isrwrite INTEGER, { ... rewrites }
upf_isdelete INTEGER, { ... deletes }
upf_iscommit INTEGER, { ... commits }
upf_isrollback INTEGER, { ... rollbacks }
upf_longtxs INTEGER, { ... long transactions }
upf_bufreads INTEGER, { ... buffer reads }
upf_bufwrites INTEGER, { ... buffer writes }
upf_logspuse INTEGER, { % log space currently used }
upf_logspmax INTEGER, { max % of logspace ever used }
upf_seqscans INTEGER, { number of sequential scans }
upf_totsorts INTEGER,
upf_dsksorts INTEGER,
upf_srtspmax INTEGER,
nlocks INTEGER, { number of locks currently held}
lktout SMALLINT, { lock timeout counter }
lkthreadlist INTEGER { next thread in same tx wtg }
);
CREATE UNIQUE INDEX sysrstcbidx ON sysrstcb (indx);
CREATE UNIQUE INDEX sysrstcbaddr ON sysrstcb (address);
REVOKE ALL ON sysrstcb FROM PUBLIC;
GRANT SELECT ON sysrstcb TO PUBLIC;
{ Transactions }
CREATE TABLE systxptab { Internal Use Only }
(
indx INTEGER, { index into transaction table }
address INTEGER, { address of transaction struct }
latchp INTEGER, { address of transaction latch }
next INTEGER, { addr of next on active or free list }
gtridp INTEGER, { address of gtrid }
txid INTEGER, { id of transaction }
flags INTEGER, { transaction flags }
logbeg INTEGER, { loguniq containing BEGIN }
loguniq INTEGER, { loguniq of last record }
logpos INTEGER, { logpos of last record }
dlklist INTEGER, { used for deadlk detection }
deadflag SMALLINT, { flag for deadlock detection }
mgmquery INTEGER, { tx_query }
lkwaitcnt SMALLINT, { # of threads waiting on locks }
lklist INTEGER, { list of locks held }
lklatchp INTEGER, { addr of private lock latch ** }
owner INTEGER, { addr of owner (rstcb_t *) }
wtlist INTEGER, { users waiting for this tx }
ptlist INTEGER, { list of partitions dropped }
nlocks INTEGER, { number of locks held }
lkwait SMALLINT, { lock wait timeout }
splevel SMALLINT, { savepoint level }
isolevel SMALLINT, { isolation level }
locktablep INTEGER, { addr of table locks table ** }
svptcnt INTEGER, { number of savepoints alloc'ed }
svuniqp INTEGER, { addr of savepoint loguniqs ** }
svposp INTEGER, { 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 INTEGER, { ptr to dynamic shm block }
sqlptr INTEGER, { ptr to first ismalloc piece }
nremotes SMALLINT, { # of rem thread activations }
begstamp INTEGER { stamp at time of BEGIN WORK }
);
CREATE UNIQUE INDEX systxpidx ON systxptab (indx);
CREATE UNIQUE INDEX systxpaddr ON systxptab (address);
REVOKE ALL ON systxptab FROM PUBLIC;
GRANT SELECT ON systxptab TO PUBLIC;
{ Locks }
CREATE TABLE syslcktab { Internal Use Only }
(
indx INTEGER, { index into lock table }
address INTEGER, { addr of lock structure }
hash INTEGER, { addr of next in hash bucket }
same INTEGER, { list of same locks }
wtlist INTEGER, { list of waiters (rstcb *) }
owner INTEGER, { owner of lock (rtx *) }
list INTEGER, { 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 syslckidx ON syslcktab (indx);
CREATE UNIQUE INDEX syslckaddr ON syslcktab (address);
REVOKE ALL ON syslcktab FROM PUBLIC;
GRANT SELECT ON syslcktab TO PUBLIC;
{ Buffer Headers }
CREATE TABLE sysbufhdr { Internal Use Only }
(
indx INTEGER, { index into buffer table }
address INTEGER, { address of buffer structure }
latchp INTEGER, { ptr to buffer latch }
bhforw INTEGER, { forward pointer in hash list }
bhback INTEGER, { backward pointer in hash list }
blforw INTEGER, { forward pointer in lru list }
blback INTEGER, { backward pointer in lru list }
bflags SMALLINT, { buffer flags }
berror SMALLINT, { i/o error }
reusecnt SMALLINT, { how often buff is reused }
lrunum SMALLINT, { last lru this buff was on }
pagenum INTEGER, { physical page addr on disk }
pagemem INTEGER, { ptr to page in shared memory }
owner INTEGER, { owner with lock (rstcb *) }
wtlist INTEGER, { list of waiters for buff lock }
sharecnt SMALLINT, { count of users sharing buff }
xflags SMALLINT { type of lock on buffer }
);
CREATE UNIQUE INDEX sysbufhdridx ON sysbufhdr (indx);
CREATE UNIQUE INDEX sysbufhdraddr ON sysbufhdr (address);
REVOKE ALL ON sysbufhdr FROM PUBLIC;
GRANT SELECT ON sysbufhdr TO PUBLIC;
{ Dbspace Table }
CREATE TABLE sysdbstab { Internal Use Only }
(
address INTEGER, { 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 INTEGER, { ptr to partp in partition tab }
bpagesize INTEGER, { BLOB page size }
bcolcnt INTEGER, { number of blob columns ref }
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 }
);
CREATE UNIQUE INDEX sysdbstab_dbsnum ON sysdbstab (dbsnum);
REVOKE ALL ON sysdbstab FROM PUBLIC;
GRANT SELECT ON sysdbstab TO PUBLIC;
{ Chunk Table }
CREATE TABLE syschktab { Internal Use Only }
(
address INTEGER, { address of chunk structure }
chknum SMALLINT, { chunk number }
nxchunk SMALLINT, { number of next chunk in dbsp }
offset INTEGER, { pages offset into device }
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 SMALLINT, { blob freemap overhead }
flags SMALLINT, { 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 syschktab_chknum ON syschktab (chknum);
REVOKE ALL ON syschktab FROM PUBLIC;
GRANT SELECT ON syschktab TO PUBLIC;
{ Mirror Chunk Table }
CREATE TABLE sysmchktab { Internal Use Only }
(
address INTEGER, { address of chunk structure }
chknum SMALLINT, { chunk number }
nxchunk SMALLINT, { number of next chunk in dbsp }
offset INTEGER, { pages offset into device }
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 SMALLINT, { blob freemap overhead }
flags SMALLINT, { 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 sysmchktab_chknum ON sysmchktab (chknum);
REVOKE ALL ON sysmchktab FROM PUBLIC;
GRANT SELECT ON sysmchktab TO PUBLIC;
{ Log file info }
CREATE TABLE syslogfil { Internal Use Only }
(
indx INTEGER, { index into log table }
address INTEGER, { 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 }
physloc INTEGER, { physical address of start }
size INTEGER, { pages in logfile }
used INTEGER { pages used in logfile }
);
CREATE UNIQUE INDEX syslogfilidx ON syslogfil (indx);
CREATE UNIQUE INDEX syslogfiladdr ON syslogfil (address);
REVOKE ALL ON syslogfil FROM PUBLIC;
GRANT SELECT ON syslogfil TO PUBLIC;
{ Btclean Request info }
CREATE TABLE sysbtcreq { Internal Use Only }
(
indx INTEGER, { index into btcreq table }
address INTEGER, { address of btclean structure }
hash INTEGER, { next in hash list }
next INTEGER, { 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 sysbtcreqidx ON sysbtcreq (indx);
REVOKE ALL ON sysbtcreq FROM PUBLIC;
GRANT SELECT ON sysbtcreq TO PUBLIC;
{ Trace buffer }
CREATE TABLE 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 INTEGER, { user who traced (rstcb *) }
trans INTEGER, { tx who traced (rtx *) }
data1 INTEGER, { data value 1 }
data2 INTEGER, { data value 2 }
data3 INTEGER, { data value 3 }
data4 INTEGER, { data value 4 }
data5 INTEGER { data value 5 }
);
CREATE INDEX systrac_stampidx ON systraces (stamp);
REVOKE ALL ON systraces FROM PUBLIC;
GRANT SELECT ON systraces TO PUBLIC;
{ Open Partition Table }
CREATE TABLE sysptntab { Internal Use Only }
(
address INTEGER, { address of partition structure}
condp INTEGER, { ptr to condition struct }
latchp INTEGER, { ptr to latch struct ** }
flags SMALLINT, { partition flags }
ucount SMALLINT, { usage count }
partnum INTEGER, { partition number }
tablock INTEGER, { table lock id }
physaddr INTEGER, { physical addr of partition pg }
lastrowszp INTEGER, { size of last alloc'ed row }
lastrowpnp INTEGER, { last page alloc'ed for row }
lastidxpn INTEGER, { last page alloc'ed for index }
extnsp INTEGER, { extent list address }
badkeys INTEGER, { badkey bitmap }
ptlist INTEGER, { next in list of dropped tables }
altstmp INTEGER, { time stamp of last alter }
ocount SMALLINT, { open count }
skstamp INTEGER, { stamp of last table update }
glscollname CHAR(32), { GLS collation name }
localep INTEGER, { locale pointer }
pf_rqlock INTEGER, { lock requests }
pf_wtlock INTEGER, { lock waits }
pf_deadlk INTEGER, { deadlocks }
pf_lktouts INTEGER, { lock timeouts }
pf_dskreads INTEGER, { disk reads }
pf_isread INTEGER, { reads }
pf_dskwrites INTEGER, { disk writes }
pf_iswrite INTEGER, { writes }
pf_isrwrite INTEGER, { rewrites }
pf_isdelete INTEGER, { deletes }
pf_bfcread INTEGER, { buffer reads }
pf_bfcwrite INTEGER, { buffer writes }
pf_seqscans INTEGER { sequential scans }
);
CREATE UNIQUE INDEX sysptntab_pnix ON sysptntab (partnum);
REVOKE ALL ON sysptntab FROM PUBLIC;
GRANT SELECT ON sysptntab TO PUBLIC;
{ Shared Memory }
CREATE TABLE sysshmem { Internal Use Only }
(
address INTEGER, { 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 sysshmemidx ON sysshmem (address);
REVOKE ALL ON sysshmem FROM PUBLIC;
{ Shared Memory Header }
CREATE TABLE sysshmhdr { Internal Use Only }
(
number INTEGER, { unique identifier for element }
name CHAR(16), { name of rhead_t element }
value INTEGER { value of rhead_t element }
);
CREATE UNIQUE INDEX sysshmhdr_numix ON sysshmhdr(number);
REVOKE ALL ON sysshmhdr FROM PUBLIC;
GRANT SELECT ON sysshmhdr TO PUBLIC;
{ Configuartion parameters }
CREATE TABLE syscfgtab
(
cf_id INTEGER, { unique numeric identifier }
cf_name CHAR(128), { config parameter name }
cf_flags INTEGER, { flags }
cf_original CHAR(256), { value in ONCONFIG at boottime }
cf_effective CHAR(256), { value effectively in use }
cf_default CHAR(256) { value by default }
);
CREATE UNIQUE INDEX syscfgtabix1 ON syscfgtab(cf_id);
REVOKE ALL ON syscfgtab FROM PUBLIC;
GRANT SELECT ON syscfgtab TO PUBLIC;
{ Session control blocks }
CREATE TABLE sysscblst { Internal Use Only }
(
sid INTEGER, { session id }
address INTEGER, { address of session structure }
currheap INTEGER, { ptr to memory heap }
poolp INTEGER, { 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 INTEGER, { latch protecting thread list }
threadlist INTEGER, { ptr to first thread in list }
next INTEGER, { next session in list }
uid SMALLINT, { user id }
username CHAR(32), { user name }
gid SMALLINT, { primary group id }
nsuppgids INTEGER, { number of supplementary gids }
suppgidsp INTEGER, { ptr to suppl'ry gids table }
clienttype INTEGER, { client type }
pid INTEGER, { process id of fe program }
progname CHAR(16), { 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(32), { users cwd }
hostname CHAR(16), { users host name }
connected INTEGER, { time that user connected }
argc INTEGER, { count of args sent }
argvp INTEGER, { ptr to arg table }
envvarp INTEGER, { ptr to env var table }
numenvvars INTEGER, { number of env vars }
sizeenvtab INTEGER, { size of env var table }
sqscb INTEGER, { ptr to sql control block }
netscb INTEGER, { ptr to net control block }
class INTEGER { VP class }
);
CREATE UNIQUE INDEX sysscblst_sidix ON sysscblst(sid DESC);
REVOKE ALL ON sysscblst FROM PUBLIC;
GRANT SELECT ON sysscblst TO PUBLIC;
{ Thread control blocks }
CREATE TABLE systcblst { Internal Use Only }
(
tid INTEGER, { thread id }
address INTEGER, { address of thread structure }
stackp INTEGER, { ptr to threads stack }
tnext INTEGER, { next thread in global list }
tprev INTEGER, { prev thread in global list }
lock INTEGER, { thread struct protection }
next INTEGER, { next thread in special list }
prev INTEGER, { prev thread in special list }
joinlist INTEGER, { head of joined threads list }
joinnext INTEGER, { next in joined threads list }
joinee INTEGER, { thread this thread joined }
joinresult INTEGER, { result of join }
initialroutine INTEGER, { initial thread procedure }
initialarg INTEGER, { initial arg }
name CHAR(12), { thread name }
self INTEGER, { this thread's address }
state INTEGER, { thread state }
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 INTEGER, { vp on which thread must run }
bind_priv INTEGER, { thread has been bound }
private_data INTEGER, { ptr to private data }
wtmutexp INTEGER, { ptr to mutex waiting on }
wtcondp INTEGER, { 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 INTEGER, { ptr to padr table }
pvalp INTEGER, { ptr to pval table }
run_time INTEGER, { total time thread has run }
wakeup_count INTEGER, { number of stacked wakeups }
tstatp INTEGER, { ptr to thread TSTAT_T struct }
wstatp INTEGER { ptr to thread WSTAT_T struct }
);
CREATE UNIQUE INDEX systcblst_tidix ON systcblst(tid);
REVOKE ALL ON systcblst FROM PUBLIC;
GRANT SELECT ON systcblst TO PUBLIC;
{ VP info }
CREATE TABLE sysvplst { Internal Use Only }
(
vpid INTEGER, { VP id }
address INTEGER, { 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 INTEGER, { ptr to saved cputime (tms) }
rcputimep INTEGER, { ptr to reset cputime (tms) }
class INTEGER, { class of VP }
readyqueue INTEGER, { ptr to ready queue tab (TCB_Q)}
num_ready INTEGER, { number of ready threads }
flags INTEGER, { VP flags }
next INTEGER, { next in idle list }
prev INTEGER, { prev in idle list }
semid INTEGER, { semid for this VP }
lock INTEGER { VP protection }
);
CREATE UNIQUE INDEX sysvplst_vpidix ON sysvplst(vpid);
REVOKE ALL ON sysvplst FROM PUBLIC;
GRANT SELECT ON sysvplst TO PUBLIC;
{ Data Replication control block }
CREATE TABLE sysdrcb { Internal Use Only }
(
address INTEGER, { address of drcb structure }
version INTEGER, { drcb version }
lock INTEGER, { 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 }
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 INTEGER, { dr session thread }
pingtid INTEGER, { dr ping thread }
applytid INTEGER, { dr apply thread }
recvrtid INTEGER, { logcial recovery thread }
scb INTEGER, { dr scb }
client_type INTEGER, { dr client type }
no_kill INTEGER, { dr no_kill flag }
no_clients INTEGER, { dr no_clients flag }
lgr_scb INTEGER, { logcical recovery scb }
lgr_rstcb INTEGER, { logcical recovery rstcb }
lgr_bufsize INTEGER, { logical recovery buffer size }
lgr_numbufs INTEGER, { number logcial recovery buffers}
ckptaddr INTEGER, { dr last ckpt address }
cpflag INTEGER, { dr cpflag }
bufflag INTEGER, { dr bufflag }
lg_offs INTEGER, { dr lg_offs }
ll INTEGER, { dr logical log info }
bufcur INTEGER, { dr current buffer }
bqempty INTEGER, { dr empty q }
bqfull INTEGER { dr full q }
);
REVOKE ALL ON sysdrcb FROM PUBLIC;
GRANT SELECT ON sysdrcb TO PUBLIC;
{ CDR queued info table }
CREATE TABLE 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 syscdrq_idx ON syscdrq(srvid,repid,srcid);
REVOKE ALL ON syscdrq FROM PUBLIC;
GRANT SELECT ON syscdrq TO PUBLIC;
{ CDR trans. processed info table }
CREATE TABLE 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 syscdrtx_idx ON syscdrtx(srvid);
REVOKE ALL ON syscdrtx FROM PUBLIC;
GRANT SELECT ON syscdrtx TO PUBLIC;
{ CDR server }
CREATE TABLE 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 syscdrs_idx ON syscdrs(servid);
REVOKE ALL ON syscdrs FROM PUBLIC;
GRANT SELECT ON syscdrs TO PUBLIC;
{ CDR in memory send progress table }
CREATE TABLE 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 syscdrprog_idx
ON syscdrprog(dest_id, group_id, source_id);
REVOKE ALL ON syscdrprog FROM PUBLIC;
{ CDR in memory queues }
CREATE TABLE syscdrsend_txn { Internal Use Only }
(
ctkeyserverid INTEGER,
ctkeyid INTEGER,
ctkeypos INTEGER,
ctkeysequence INTEGER,
ctstamp1 INTEGER,
ctstamp2 INTEGER,
ctcommittime INTEGER,
ctuserid INTEGER,
ctfromid INTEGER
);
CREATE UNIQUE INDEX syscdrsend_tidx ON syscdrsend_txn(ctstamp1,ctstamp2);
REVOKE ALL ON syscdrsend_txn FROM PUBLIC;
CREATE TABLE syscdrack_txn { Internal Use Only }
(
ctkeyserverid INTEGER,
ctkeyid INTEGER,
ctkeypos INTEGER,
ctkeysequence INTEGER,
ctstamp1 INTEGER,
ctstamp2 INTEGER,
ctcommittime INTEGER,
ctuserid INTEGER,
ctfromid INTEGER
);
CREATE UNIQUE INDEX syscdrack_tidx ON syscdrack_txn(ctstamp1,ctstamp2);
REVOKE ALL ON syscdrack_txn FROM PUBLIC;
CREATE TABLE syscdrctrl_txn { Internal Use Only }
(
ctkeyserverid INTEGER,
ctkeyid INTEGER,
ctkeypos INTEGER,
ctkeysequence INTEGER,
ctstamp1 INTEGER,
ctstamp2 INTEGER,
ctcommittime INTEGER,
ctuserid INTEGER,
ctfromid INTEGER
);
CREATE UNIQUE INDEX syscdrctrl_tidx ON syscdrctrl_txn(ctstamp1,ctstamp2);
REVOKE ALL ON syscdrctrl_txn FROM PUBLIC;
CREATE TABLE syscdrsync_txn { Internal Use Only }
(
ctkeyserverid INTEGER,
ctkeyid INTEGER,
ctkeypos INTEGER,
ctkeysequence INTEGER,
ctstamp1 INTEGER,
ctstamp2 INTEGER,
ctcommittime INTEGER,
ctuserid INTEGER,
ctfromid INTEGER
);
CREATE UNIQUE INDEX syscdrsync_tidx ON syscdrsync_txn(ctstamp1,ctstamp2);
REVOKE ALL ON syscdrsync_txn FROM PUBLIC;
CREATE TABLE syscdrrecv_txn { Internal Use Only }
(
ctkeyserverid INTEGER,
ctkeyid INTEGER,
ctkeypos INTEGER,
ctkeysequence INTEGER,
ctstamp1 INTEGER,
ctstamp2 INTEGER,
ctcommittime INTEGER,
ctuserid INTEGER,
ctfromid INTEGER
);
CREATE UNIQUE INDEX syscdrsrecv_tidx ON syscdrrecv_txn(ctstamp1,ctstamp2);
REVOKE ALL ON syscdrrecv_txn FROM PUBLIC;
CREATE TABLE syscdrsend_buf { Internal Use Only }
(
cbflags INTEGER,
cbsize INTEGER,
cbkeyserverid INTEGER,
cbkeyid INTEGER,
cbkeypos INTEGER,
cbkeysequence INTEGER,
cbgroupid INTEGER,
cbcommittime INTEGER
);
CREATE UNIQUE INDEX syscdrsend_bidx
ON syscdrsend_buf(cbkeyserverid, cbkeyid, cbkeypos, cbkeysequence);
REVOKE ALL ON syscdrsend_buf FROM PUBLIC;
CREATE TABLE syscdrack_buf { Internal Use Only }
(
cbflags INTEGER,
cbsize INTEGER,
cbkeyserverid INTEGER,
cbkeyid INTEGER,
cbkeypos INTEGER,
cbkeysequence INTEGER,
cbgroupid INTEGER,
cbcommittime INTEGER
);
CREATE UNIQUE INDEX syscdrack_bidx
ON syscdrack_buf(cbkeyserverid, cbkeyid, cbkeypos, cbkeysequence);
REVOKE ALL ON syscdrack_buf FROM PUBLIC;
CREATE TABLE syscdrctrl_buf { Internal Use Only }
(
cbflags INTEGER,
cbsize INTEGER,
cbkeyserverid INTEGER,
cbkeyid INTEGER,
cbkeypos INTEGER,
cbkeysequence INTEGER,
cbgroupid INTEGER,
cbcommittime INTEGER
);
CREATE UNIQUE INDEX syscdrctrl_bidx
ON syscdrctrl_buf(cbkeyserverid, cbkeyid, cbkeypos, cbkeysequence);
REVOKE ALL ON syscdrctrl_buf FROM PUBLIC;
CREATE TABLE syscdrsync_buf { Internal Use Only }
(
cbflags INTEGER,
cbsize INTEGER,
cbkeyserverid INTEGER,
cbkeyid INTEGER,
cbkeypos INTEGER,
cbkeysequence INTEGER,
cbgroupid INTEGER,
cbcommittime INTEGER
);
CREATE UNIQUE INDEX syscdrsync_bidx
ON syscdrsync_buf(cbkeyserverid, cbkeyid, cbkeypos, cbkeysequence);
REVOKE ALL ON syscdrsync_buf FROM PUBLIC;
CREATE TABLE syscdrrecv_buf { Internal Use Only }
(
cbflags INTEGER,
cbsize INTEGER,
cbkeyserverid INTEGER,
cbkeyid INTEGER,
cbkeypos INTEGER,
cbkeysequence INTEGER,
cbgroupid INTEGER,
cbcommittime INTEGER
);
CREATE UNIQUE INDEX syscdrrecv_bidx
ON syscdrrecv_buf(cbkeyserverid, cbkeyid, cbkeypos, cbkeysequence);
REVOKE ALL ON syscdrrecv_buf FROM PUBLIC;
GRANT SELECT ON syscdrrecv_buf TO PUBLIC;
{ Physical Log }
CREATE TABLE sysplog
(
pl_mutex INTEGER, { physical log mutex }
pl_b1mutex INTEGER, { buf1's mutex }
pl_b1condition INTEGER, { 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 INTEGER, { buf1's log buffer }
pl_b1wtlist INTEGER, { buf1's waiting for used = copied }
pl_b2mutex INTEGER, { buf2's mutex }
pl_b2condition INTEGER, { 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 INTEGER, { buf2's log buffer }
pl_b2wtlist INTEGER, { buf2's waiting for used = copied }
pl_curbp INTEGER, { current bp }
pl_otherbp INTEGER, { other bp }
pl_bufsize SMALLINT, { log buffer size in pages }
pl_stamp INTEGER, { log flush timestamp }
pl_physaddr INTEGER, { log file disk address }
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 sysplog FROM PUBLIC;
GRANT SELECT ON sysplog TO PUBLIC;
{ Thread Wait Stats }
CREATE TABLE systwaits { Internal Use Only }
(
tid INTEGER, { thread id of these stats }
starttime INTEGER, { start time of current wait }
startrtime INTEGER, { start reset time of current }
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 systwaits_tid ON systwaits(tid, wreason);
REVOKE ALL ON systwaits FROM PUBLIC;
GRANT SELECT ON systwaits TO PUBLIC;
{ Mutexes }
CREATE TABLE sysmtxlst { Internal Use Only }
(
mtx_id INTEGER, { id of this mutex }
mtx_address INTEGER, { address of this mutex }
mtx_next INTEGER, { pointer to next mutex in list }
mtx_prev INTEGER, { pointer to prev mutex in list }
mtx_lock INTEGER, { mutex lock 0=avail, 1=held }
mtx_wtlock INTEGER, { protects wait list }
mtx_holder INTEGER, { thread holding lock }
mtx_wtlist INTEGER, { address of first thread on list}
mtx_type SMALLINT, { type of mutex }
mtx_flags SMALLINT, { flags }
mtx_lkcnt SMALLINT, { count of locks by same thread }
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 sysmtxlstix1 ON sysmtxlst(mtx_id);
CREATE UNIQUE INDEX sysmtxlstix2 ON sysmtxlst(mtx_address);
REVOKE ALL ON sysmtxlst FROM PUBLIC;
GRANT SELECT ON sysmtxlst TO PUBLIC;
{ Conditions }
CREATE TABLE sysconlst { Internal Use Only }
(
con_id INTEGER, { id of this condition }
con_address INTEGER, { address of this condition }
con_next INTEGER, { pointer to next condition in list}
con_prev INTEGER, { pointer to prev condition in list}
con_lock INTEGER, { condition lock 0=avail, 1=held}
con_wtlist INTEGER, { 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 sysconlstix1 ON sysconlst(con_id);
CREATE UNIQUE INDEX sysconlstix2 ON sysconlst(con_address);
REVOKE ALL ON sysconlst FROM PUBLIC;
GRANT SELECT ON sysconlst TO PUBLIC;
{ Pools }
CREATE TABLE syspoollst { Internal Use Only }
(
po_id SMALLINT, { id of this pool }
po_address INTEGER, { address of this pool }
po_next INTEGER, { pointer to next pool in list }
po_prev INTEGER, { 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 INTEGER, { total amount in free list }
po_usedamt INTEGER, { total amount in used list }
po_freelist INTEGER, { address of free block list }
po_list INTEGER { address of pools block list }
);
CREATE UNIQUE INDEX syspoollstix1 ON syspoollst(po_id);
CREATE UNIQUE INDEX syspoollstix2 ON syspoollst(po_address);
REVOKE ALL ON syspoollst FROM PUBLIC;
GRANT SELECT ON syspoollst TO PUBLIC;
{ Segments }
CREATE TABLE sysseglst { Internal Use Only }
(
seg_address INTEGER, { address of segment structure }
seg_next INTEGER, { pointer to next segment }
seg_prev INTEGER, { pointer to prev segment }
seg_class SMALLINT, { segment class }
seg_size INTEGER, { size of this segment }
seg_osshmid INTEGER, { id of this OS segment in this seg}
seg_osmaxsize INTEGER, { 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 INTEGER, { address of segment }
seg_ovhd INTEGER, { amount of overhead bytes }
seg_lock INTEGER, { lock to synchronise bitmap access}
seg_nextid INTEGER, { segment id of next seg }
seg_bmapsz INTEGER, { size of block bitmap }
seg_blkused INTEGER, { no. of used blocks in segment }
seg_blkfree INTEGER { no. of free blocks in segment }
);
REVOKE ALL ON sysseglst FROM PUBLIC;
GRANT SELECT ON sysseglst TO PUBLIC;
{ Dictionary Hash }
CREATE TABLE 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 INTEGER, { struct heap *DD memory heap }
dic_altcount SMALLINT, { 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 INTEGER, { number of rows }
dic_npused INTEGER, { # 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 sysdicidx ON sysdic(dic_hashno, dic_chainno);
REVOKE ALL ON sysdic FROM PUBLIC;
GRANT SELECT ON sysdic TO PUBLIC;
{ Distribution Hash }
CREATE TABLE 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 INTEGER, { 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 sysdscidx ON sysdsc(dis_hashno, dis_chainno);
REVOKE ALL ON sysdsc FROM PUBLIC;
GRANT SELECT ON sysdsc TO PUBLIC;
{ Procedure Hash }
CREATE TABLE 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 INTEGER, { 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 sysprcidx ON sysprc(prc_hashno, prc_chainno);
REVOKE ALL ON sysprc FROM PUBLIC;
GRANT SELECT ON sysprc TO PUBLIC;
{ Sqscb }
CREATE TABLE syssqscb { Internal Use Only }
(
scb_sessionid INTEGER, { session id }
scb_address INTEGER, { 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 syssqscbidx ON syssqscb ( scb_sessionid DESC );
REVOKE ALL ON syssqscb FROM PUBLIC;
GRANT SELECT ON syssqscb TO PUBLIC;
{ Sdblock }
CREATE TABLE 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 INTEGER, { conblock for statement }
sdb_cblist INTEGER, { list of all cb's in statement }
sdb_heap INTEGER, { 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 SMALLINT, { need to set the error in the next }
sdb_errstr CHAR(64), { fetch statement }
sdb_ntables INTEGER, { number of table descriptors }
sdb_sqttab INTEGER, { thread specific tab info }
sdb_asynch_sqerrno INTEGER, { error reported by asynch thread }
sdb_asynch_sqiserr INTEGER, { error reported by asynch thread }
sdb_pool INTEGER, { statement memory pool }
sdb_mutex INTEGER, { misc lock (to check sd_sqerrno) }
sdb_tgcblist INTEGER, { 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 syssdblockidx ON syssdblock (sdb_sessionid DESC, sdb_sdbno);
REVOKE ALL ON syssdblock FROM PUBLIC;
GRANT SELECT ON syssdblock TO PUBLIC;
{ Conblock }
CREATE TABLE 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 sysconblockidx ON sysconblock ( cbl_sessionid DESC, cbL_sdbno, cbl_conbno);
REVOKE ALL ON sysconblock FROM PUBLIC;
GRANT SELECT ON sysconblock TO PUBLIC;
{ Opendb }
CREATE TABLE 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 sysopendbidx ON sysopendb (odb_sessionid DESC, odb_odbno);
REVOKE ALL ON sysopendb FROM PUBLIC;
GRANT SELECT ON sysopendb TO PUBLIC;
{ SQL state and statement }
CREATE TABLE 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 syssqlstat FROM PUBLIC;
GRANT SELECT ON syssqlstat TO PUBLIC;
{ LRU buffers }
CREATE TABLE syslrus
(
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 syslrusix1 ON syslrus(lru_num);
REVOKE ALL ON syslrus FROM PUBLIC;
GRANT SELECT ON syslrus TO PUBLIC;
{ Shared memory values }
CREATE TABLE 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 INT, { 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 INT, { LRU can have this % dirty pages }
sh_mindirty INT, { 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 }
REVOKE ALL ON sysshmvals FROM PUBLIC;
GRANT SELECT ON sysshmvals TO PUBLIC;
{ C2 Audit info }
CREATE TABLE 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 }
);
REVOKE ALL ON sysadtinfo FROM PUBLIC;
{ C2 Audit call }
CREATE TABLE syscrtadt { Internal Use Only }
(
event INTEGER, { Event to audit }
result INTEGER, { Success or Failure }
data CHAR(256) { Additional data to audit }
);
REVOKE ALL ON syscrtadt FROM PUBLIC;
{ 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',
'sysrstcb', 'systxptab', 'syslcktab', 'sysbufhdr',
'sysdbstab', 'syschktab', 'sysmchktab', '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', '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')
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;
{ 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) = (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) = (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) = (38,1) WHERE tabname = 'sysshmvals';
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) = (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) = (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) = (1025, 1) WHERE tabname = 'sysadtinfo';
UPDATE tempsystab SET (partnum,nrows) = (1026, 1) WHERE tabname = 'syscrtadt';
DROP TABLE sysdbspartn;
DROP TABLE sysrawdsk;
DROP TABLE syspaghdr;
DROP TABLE sysslttab;
DROP TABLE syssltdat;
DROP TABLE syschfree;
DROP TABLE sysptnhdr;
DROP TABLE sysptnkey;
DROP TABLE sysptnext;
DROP TABLE sysptncol;
DROP TABLE sysptnbit;
DROP TABLE systabnames;
DROP TABLE sysptntab;
DROP TABLE syslcktab;
DROP TABLE sysbufhdr;
DROP TABLE sysdbstab;
DROP TABLE syschktab;
DROP TABLE sysmchktab;
DROP TABLE sysrstcb;
DROP TABLE systxptab;
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 systwaits;
DROP TABLE sysdrcb;
DROP TABLE sysplog;
DROP TABLE sysadtinfo;
DROP TABLE syscrtadt;
DROP TABLE sysmtxlst;
DROP TABLE sysconlst;
DROP TABLE syspoollst;
DROP TABLE sysseglst;
DROP TABLE sysdic;
DROP TABLE sysprc;
DROP TABLE sysdsc;
DROP TABLE syssqscb;
DROP TABLE syssdblock;
DROP TABLE sysconblock;
DROP TABLE sysopendb;
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;
{ 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;
DROP TABLE tempsystab;
DROP TABLE tempsyscol;
DROP TABLE tempsysidx;
DROP TABLE tempsysauth;
DROP TABLE tempsysobj;
{ create a table to associate strings wither various tables' flags/types
columns }
CREATE TABLE flags_text (tabname CHAR(128), flags INT, txt CHAR(50));
CREATE UNIQUE INDEX flags_text_ix1 ON flags_text(tabname, flags);
{ 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 copy');
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, 'log mutex');
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');
{ 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, 'ntk');
{ 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', 288, 'Read-Only');
{ 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');
{ 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');
{ 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');
{ 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', 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');
{ 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');
{ Create a table where the build status for 'sysutils' and any other system
activity associated with building 'sysmaster' can be recorded }
CREATE TABLE 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 logmessage (message_num INTEGER);
{ Stored procedure for setting boolean 'columns' in views for flags values }
CREATE PROCEDURE 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 bitval TO PUBLIC;
{ Stored procedure for converting unix time() long to date }
CREATE PROCEDURE l2date ( l INT ) RETURNING DATE;
RETURN TRUNC((l/86400) + 25568 );
END PROCEDURE;
GRANT EXECUTE ON l2date TO PUBLIC;
CREATE PROCEDURE physchunk( physaddr INT ) RETURNING INT;
RETURN TRUNC(physaddr/1048576);
END PROCEDURE;
GRANT EXECUTE ON physchunk TO PUBLIC;
CREATE PROCEDURE physpage( physaddr INT ) RETURNING INT;
RETURN(MOD(physaddr,1048576));
END PROCEDURE;
GRANT EXECUTE ON physpage TO PUBLIC;
CREATE PROCEDURE physaddr(chunknum INT, pagenum INT) RETURNING INT;
RETURN((chunknum*1048576)+pagenum);
END PROCEDURE;
GRANT EXECUTE ON physaddr TO PUBLIC;
CREATE PROCEDURE partdbsnum (partnum INT ) RETURNING INT;
RETURN TRUNC(partnum/1048576);
END PROCEDURE;
GRANT EXECUTE ON partdbsnum TO PUBLIC;
CREATE PROCEDURE partpagenum(partnum INT) RETURNING INT;
RETURN(MOD(partnum,1048576));
END PROCEDURE;
GRANT EXECUTE ON partpagenum TO PUBLIC;
CREATE PROCEDURE partaddr(dbspnum INT, pagenum INT) RETURNING INT;
RETURN((dbspnum*1048576)+pagenum);
END PROCEDURE;
GRANT EXECUTE ON partaddr TO PUBLIC;
{ Session Waits profile }
CREATE VIEW 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 sysseswts TO PUBLIC;
{ Chunk Free List }
CREATE VIEW syschkextents ( ce_chknum, ce_extnum, ce_physaddr, ce_size )
AS SELECT chknum, extnum, start, leng FROM syschfree;
GRANT SELECT ON syschkextents TO PUBLIC;
{ Partition Bit Maps }
CREATE VIEW systabpagtypes ( tp_partnum, tp_pagenum, tp_type )
AS SELECT pb_partnum, pb_pagenum, pb_bitmap FROM sysptnbit;
GRANT SELECT ON systabpagtypes TO PUBLIC;
{ Logical Logs }
CREATE VIEW syslogs (number, uniqid, size, used,
is_used, is_current, is_backed_up,
is_new, is_archived, is_temp, 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'), flags
FROM syslogfil
WHERE number > 0;
GRANT SELECT ON syslogs TO PUBLIC;
{ Chunks }
CREATE VIEW syschunks(chknum, dbsnum, nxchknum, chksize, offset, nfree,
mdsize, udsize, udfree,
is_offline, is_recovering, is_blobchunk, is_sbchunk,
is_inconsistent, flags, fname,
mfname, moffset, mis_offline, mis_recovering, mflags)
AS
SELECT a.chknum, a.dbsnum, a.nxchunk, 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'), 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 syschunks TO PUBLIC;
{ Dbspaces }
CREATE VIEW sysdbspaces(dbsnum, name, owner, fchunk, nchunks, is_mirrored,
is_blobspace, is_sbspace, is_temp, flags)
AS
SELECT dbsnum, name, owner, fchunk, nchunks,
bitval(flags, '0x2'), bitval(flags, '0x10'),
bitval(flags, '0x8000'), bitval(flags, '0x2000'), flags
FROM sysdbstab
WHERE dbsnum > 0;
GRANT SELECT ON sysdbspaces TO PUBLIC;
{ Locks (keep for 6.0 compatibility) }
CREATE VIEW 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 syslocks TO PUBLIC;
{ Locks }
CREATE VIEW 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 syslocktab TO PUBLIC;
{ Active sessions }
CREATE VIEW 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 syssessions TO PUBLIC;
{ Session activity profile }
CREATE VIEW 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 syssesprof TO PUBLIC;
{ User and system time by VP }
CREATE VIEW sysvpprof ( vpid, class, usercpu, syscpu)
AS
SELECT a.vpid, b.txt, a.usecs_user, a.usecs_sys
FROM sysvplst a, flags_text b
WHERE a.flags != 6
AND a.class = b.flags
AND b.tabname = 'sysvplst';
GRANT SELECT ON sysvpprof TO PUBLIC;
{ Partition profile }
CREATE VIEW 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 sysptprof TO PUBLIC;
{ Profile listing }
CREATE VIEW sysprofile ( name, value )
AS
SELECT name[4,16], value
FROM sysshmhdr
WHERE name MATCHES 'pf_*';
GRANT SELECT ON sysprofile TO PUBLIC;
{ Listing of supported configuration parameters }
CREATE VIEW 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 cf_flags = 0;
GRANT SELECT ON sysconfig TO PUBLIC;
{ Extent listings (keep for 6.0 compatibility) }
CREATE VIEW sysextents ( dbsname, tabname, start, size)
AS
SELECT dbsname, tabname, pe_phys, pe_size
FROM systabnames a, sysptnext b
WHERE a.partnum = b.pe_partnum;
GRANT SELECT ON sysextents TO PUBLIC;
{ Extent listings }
CREATE VIEW systabextents ( te_partnum, te_extnum, te_physaddr,
te_size, te_pagenum )
AS SELECT pe_partnum, pe_extnum, pe_phys, pe_size, pe_log
FROM sysptnext;
GRANT SELECT ON systabextents TO PUBLIC;
{ Data Replication info }
CREATE VIEW sysdri (type, state, name, intvl, timeout, lostfound)
AS
SELECT b.txt, d.txt, a.name, a.intvl, a.timeout, a.lostfound
FROM sysdrcb a, flags_text b, sysdrcb c, flags_text d
WHERE a.type = b.flags
AND b.tabname = 'sysdrcb'
AND c.state = d.flags
AND d.tabname = 'sysdrcb';
GRANT SELECT ON sysdri TO PUBLIC;
{ Databases }
CREATE VIEW sysdatabases (name, partnum, owner, created,
is_logging, is_buff_log, is_ansi,
is_nls, flags)
AS
SELECT name, partnum, owner, DATE(DBINFO('UTC_TO_DATETIME', created)),
bitval(flags, 1), bitval(flags, 2), bitval(flags, 4),
bitval(flags, 16), flags
FROM sysdbspartn;
GRANT SELECT ON sysdatabases TO PUBLIC;
{ Threads view }
CREATE VIEW 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, run_time
FROM systcblst;
GRANT SELECT ON systhreads TO PUBLIC;
{ Mutexes view }
CREATE VIEW 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 sysmutexes TO PUBLIC;
{ Conditions view }
CREATE VIEW 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 sysconditions TO PUBLIC;
{ Pools view }
CREATE VIEW 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 syspools TO PUBLIC;
{ Segments view }
CREATE VIEW 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 syssegments TO PUBLIC;
{ Threads Wait Stats view }
CREATE VIEW systhreadwaits (tw_tid, tw_reason, tw_num, tw_cumtime,
tw_maxtime)
AS
SELECT tid, wreason, wnum, wcumtime, wmaxtime
FROM systwaits;
GRANT SELECT ON systhreadwaits TO PUBLIC;
{ Mutex queue view }
CREATE VIEW 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 sysmutq TO PUBLIC;
{ Condition queue view }
CREATE VIEW 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 sysconq TO PUBLIC;
{ Userthreads }
CREATE VIEW 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 sysuserthreads TO PUBLIC;
{ systrans }
CREATE VIEW 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 systrans TO PUBLIC;
{ Partition Headers }
CREATE VIEW systabinfo
(
ti_partnum, ti_flags, ti_rowsize, ti_ncols, ti_nkeys, ti_nextns,
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, created, serialv,
fextsiz, nextsiz, nptotal, npused, npdata, octptnm, nrows
FROM sysptnhdr;
GRANT SELECT ON systabinfo TO PUBLIC;
{ Page Headers }
CREATE VIEW systabpaghdrs
( pg_partnum, pg_pagenum, pg_physaddr, pg_stamp, pg_stamp2,
pg_nslots, pg_flags, pg_frptr, pg_frcnt, pg_next, pg_prev )
AS SELECT * FROM syspaghdr WHERE
pg_partnum > 1048576;
GRANT SELECT ON systabpaghdrs TO PUBLIC;
{ Page Headers }
CREATE VIEW sysphyspaghdrs
( pg_pagenum, pg_physaddr, pg_stamp, pg_stamp2,
pg_nslots, pg_flags, pg_frptr, pg_frcnt, pg_next, pg_prev )
AS SELECT
pg_pagenum, pg_physaddr, pg_stamp, pg_stamp2,
pg_nslots, pg_flags, pg_frptr, pg_frcnt, pg_next, pg_prev
FROM syspaghdr WHERE
pg_partnum = 0;
GRANT SELECT ON sysphyspaghdrs TO PUBLIC;
{ C2 Audit mask table }
CREATE TABLE 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 }
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 }
) LOCK MODE ROW;
CREATE UNIQUE INDEX sysaudit_ix1 ON sysaudit(username) IN table;
REVOKE ALL ON sysaudit FROM PUBLIC;
{ Chunk io stats }
CREATE VIEW 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 a, OUTER sysmchktab b
WHERE a.chknum > 0
AND a.chknum = b.chknum;
GRANT SELECT ON syschkio TO PUBLIC;
{ Locale in which the database was created in }
CREATE VIEW sysdbslocale (dbs_dbsname, dbs_collate)
AS
SELECT b.name, a.collate
FROM systabnames a, sysdbspartn b
WHERE a.partnum = b.partnum;
GRANT SELECT ON sysdbslocale TO PUBLIC;
{ SQL Dictionary cache }
CREATE VIEW 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 sysdiccache TO PUBLIC;
{ SQL Distribution cache }
CREATE VIEW 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 sysdistcache TO PUBLIC;
{ SQL Procedure cache }
CREATE VIEW 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 sysproccache TO PUBLIC;
{ SQL Statements }
CREATE VIEW 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';
GRANT SELECT ON syssqlcurall TO PUBLIC;
{ SQL Current session }
CREATE VIEW 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
;
GRANT SELECT ON syssqlcurses TO PUBLIC;
{ Show sqexplain information }
CREATE VIEW 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
;
GRANT SELECT ON syssqexplain TO PUBLIC;
{ Extspaces Table }
CREATE TABLE 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 sysextspace_ix1 ON sysextspaces(name) IN table;
REVOKE ALL ON sysextspaces FROM PUBLIC;
GRANT SELECT ON sysextspaces TO PUBLIC;
{ Archive-related Tables }
{ Volume set information }
CREATE TABLE arc_vset
(
vset_vid SMALLINT PRIMARY KEY,
vset_name CHAR(17) NOT NULL UNIQUE,
vset_class CHAR(1) NOT NULL,
vset_onsite CHAR(1) NOT NULL,
vset_imported CHAR(1) NOT NULL,
vset_foreign CHAR(1) NOT NULL,
vset_perm_mounted CHAR(1) NOT NULL,
vset_transit CHAR(1) NOT NULL,
vset_accessibility SMALLINT NOT NULL,
vset_owner_node CHAR(255) NOT NULL,
vset_dev_node CHAR(255) NOT NULL,
vset_dev_type CHAR(128) NOT NULL,
vset_dev_driver CHAR(10) NOT NULL,
vset_nb_volumes SMALLINT NOT NULL,
vset_density CHAR(4),
vset_location CHAR(80),
vset_parameters CHAR(20),
vset_protection CHAR(3),
vset_comment CHAR(80)
);
REVOKE ALL ON arc_vset FROM PUBLIC;
GRANT ALL ON arc_vset TO root;
GRANT SELECT ON arc_vset TO PUBLIC;
{ Volume information }
CREATE TABLE arc_volume
(
vol_vid SMALLINT NOT NULL,
vol_vno SMALLINT NOT NULL,
vol_max_space INTEGER NOT NULL,
vol_used_space INTEGER NOT NULL,
vol_space_exact CHAR(1) NOT NULL,
vol_full CHAR(1) NOT NULL,
vol_nb_svst INTEGER NOT NULL,
vol_nb_svst_phys INTEGER NOT NULL,
vol_virtual CHAR(255),
vol_parameters CHAR(20),
vol_protection CHAR(3),
vol_rewind_date DATETIME YEAR TO DAY,
vol_comment CHAR(80),
vol_label CHAR(6),
vol_remote_virtual CHAR(255),
PRIMARY KEY (vol_vid, vol_vno)
);
REVOKE ALL ON arc_volume FROM PUBLIC;
GRANT ALL ON arc_volume TO root;
GRANT SELECT ON arc_volume TO PUBLIC;
{ Volume-set, User relationship information }
CREATE TABLE arc_vset_user
(
vu_vid SMALLINT NOT NULL,
vu_user_node CHAR(255) NOT NULL,
vu_user_name CHAR(40) NOT NULL,
PRIMARY KEY (vu_vid, vu_user_name)
);
REVOKE ALL ON arc_vset_user FROM PUBLIC;
GRANT ALL ON arc_vset_user TO root;
GRANT SELECT ON arc_vset_user TO PUBLIC;
{ Disk usage information }
CREATE TABLE arc_diskspace_mgr
(
dsm_vid SMALLINT NOT NULL,
dsm_vno SMALLINT NOT NULL,
dsm_pid INTEGER NOT NULL,
dsm_spaces_alloc INTEGER NOT NULL);
CREATE INDEX arc_dsk_i1 ON arc_diskspace_mgr
(dsm_vid, dsm_vno, dsm_pid);
REVOKE ALL ON arc_diskspace_mgr FROM PUBLIC;
GRANT ALL ON arc_diskspace_mgr TO root;
GRANT SELECT ON arc_diskspace_mgr TO PUBLIC;
{ Request information (for archives, restores, backups) }
CREATE TABLE arc_request
(
req_rid INTEGER PRIMARY KEY,
req_type CHAR(2) NOT NULL,
req_status CHAR(2) NOT NULL,
req_issue_date DATETIME YEAR TO SECOND,
req_user_node CHAR(255) NOT NULL,
req_user_name CHAR(40) NOT NULL,
req_user_lang CHAR(1) NOT NULL,
req_qlf_string CHAR(1200) NOT NULL,
req_password BYTE IN TABLE,
req_former_rid INTEGER,
req_former_sid SMALLINT,
req_expiry_date DATETIME YEAR TO SECOND,
req_execution_date DATETIME YEAR TO SECOND,
req_dflt_dir_name CHAR(255)
);
CREATE INDEX arc_req_i1 ON arc_request
(req_user_name);
REVOKE ALL ON arc_request FROM PUBLIC;
GRANT ALL ON arc_request TO root;
GRANT SELECT ON arc_request TO PUBLIC;
{ Volume-Request-PID-usage lock & relationship information }
CREATE TABLE arc_vol_lock
(
vlck_vid SMALLINT NOT NULL,
vlck_vno SMALLINT NOT NULL,
vlck_rid INTEGER NOT NULL,
vlck_pid INTEGER NOT NULL,
vlck_exclusive CHAR(1) NOT NULL,
vlck_operation CHAR(1) NOT NULL,
PRIMARY KEY (vlck_vid, vlck_vno, vlck_pid)
);
REVOKE ALL ON arc_vol_lock FROM PUBLIC;
GRANT ALL ON arc_vol_lock TO root;
GRANT SELECT ON arc_vol_lock TO PUBLIC;
{ Pending requests }
CREATE TABLE arc_pending_req
(
pend_rid INTEGER NOT NULL,
pend_lock_pid INTEGER NOT NULL,
pend_parent_rid INTEGER,
pend_dflt_dir_name CHAR(255),
PRIMARY KEY (pend_rid)
);
REVOKE ALL ON arc_pending_req FROM PUBLIC;
GRANT ALL ON arc_pending_req TO root;
GRANT SELECT ON arc_pending_req TO PUBLIC;
{ Volume set-Request relationship information }
CREATE TABLE arc_req_vset
(
rv_rid INTEGER NOT NULL,
rv_vid SMALLINT NOT NULL,
PRIMARY KEY (rv_rid, rv_vid)
);
REVOKE ALL ON arc_req_vset FROM PUBLIC;
GRANT ALL ON arc_req_vset TO root;
GRANT SELECT ON arc_req_vset TO PUBLIC;
{ Save set information }
CREATE TABLE arc_save_set
(
svst_rid INTEGER NOT NULL,
svst_vid SMALLINT NOT NULL,
svst_complete CHAR(1) NOT NULL,
svst_attached_rid INTEGER,
svst_transit_date DATETIME YEAR TO DAY,
svst_nb_volumes SMALLINT NOT NULL,
PRIMARY KEY (svst_rid, svst_vid)
);
REVOKE ALL ON arc_save_set FROM PUBLIC;
GRANT ALL ON arc_save_set TO root;
GRANT SELECT ON arc_save_set TO PUBLIC;
{ File information (all file types) }
CREATE TABLE arc_file
(
file_rid INTEGER NOT NULL,
file_fno INTEGER NOT NULL,
file_type CHAR(1) NOT NULL,
file_name CHAR(255) NOT NULL,
file_dno INTEGER NOT NULL,
file_nb_copies SMALLINT NOT NULL,
file_compressed CHAR(1) NOT NULL,
file_encrypted CHAR(1) NOT NULL,
file_creation_date DATETIME YEAR TO DAY NOT NULL,
file_modify_date DATETIME YEAR TO DAY,
file_expiry_date DATETIME YEAR TO DAY,
file_comment CHAR(80),
PRIMARY KEY (file_rid, file_fno)
);
REVOKE ALL ON arc_file FROM PUBLIC;
GRANT ALL ON arc_file TO root;
GRANT SELECT ON arc_file TO PUBLIC;
{ File directory information }
CREATE TABLE arc_directory
(
dir_rid INTEGER NOT NULL,
dir_dno INTEGER NOT NULL,
dir_name CHAR(255) NOT NULL,
PRIMARY KEY (dir_rid, dir_dno)
);
REVOKE ALL ON arc_directory FROM PUBLIC;
GRANT ALL ON arc_directory TO root;
GRANT SELECT ON arc_directory TO PUBLIC;
{ DB extract output file information }
{ Not used by Informix; included for consistency with Computertime }
CREATE TABLE arc_db_file
(
dbf_rid INTEGER NOT NULL,
dbf_fno INTEGER NOT NULL,
dbf_db_name CHAR(30) NOT NULL,
dbf_user_name CHAR(30) NOT NULL,
dbf_table_name CHAR(30) NOT NULL,
dbf_nb_rows INTEGER NOT NULL,
dbf_retrieved_rows INTEGER,
dbf_sql CHAR(255),
PRIMARY KEY (dbf_rid, dbf_fno)
);
REVOKE ALL ON arc_db_file FROM PUBLIC;
GRANT ALL ON arc_db_file TO root;
GRANT SELECT ON arc_db_file TO PUBLIC;
{ Copy information }
CREATE TABLE arc_file_copy
(
fc_rid INTEGER NOT NULL,
fc_fno INTEGER NOT NULL,
fc_sno SMALLINT NOT NULL,
fc_vid SMALLINT NOT NULL,
fc_vno SMALLINT NOT NULL,
fc_last_sno CHAR(1) NOT NULL,
PRIMARY KEY (fc_vid, fc_vno, fc_rid, fc_fno)
);
CREATE INDEX arc_file_copy_i1 ON arc_file_copy
(fc_rid, fc_fno);
REVOKE ALL ON arc_file_copy FROM PUBLIC;
GRANT ALL ON arc_file_copy TO root;
GRANT SELECT ON arc_file_copy TO PUBLIC;
{ Dbspace set definitions; used only by Informix }
CREATE TABLE arc_dbspace_set
(
ds_dsid SERIAL PRIMARY KEY,
ds_name CHAR(128) NOT NULL
);
CREATE UNIQUE INDEX arc_dbspace_set_i1 ON arc_dbspace_set
(ds_name);
REVOKE ALL ON arc_dbspace_set FROM PUBLIC;
GRANT ALL ON arc_dbspace_set TO root;
GRANT SELECT ON arc_dbspace_set TO PUBLIC;
{ Dbspace-dbspace set relationships; used only by Informix }
CREATE TABLE arc_dbspace
(
dbs_name CHAR(128) NOT NULL,
dbs_dsid INTEGER NOT NULL REFERENCES arc_dbspace_set
);
CREATE INDEX arc_dbspace_i1 ON arc_dbspace
(dbs_name);
REVOKE ALL ON arc_dbspace FROM PUBLIC;
GRANT ALL ON arc_dbspace TO root;
GRANT SELECT ON arc_dbspace TO PUBLIC;
{ Archive event information; used only by Informix }
CREATE TABLE arc_archive_event
(
ae_rid INTEGER NOT NULL REFERENCES arc_request,
ae_dsid INTEGER NOT NULL,
ae_level SMALLINT NOT NULL,
ae_timestamp INTEGER NOT NULL,
ae_prior_rid INTEGER,
ae_logid INTEGER
);
REVOKE ALL ON arc_archive_event FROM PUBLIC;
GRANT ALL ON arc_archive_event TO root;
GRANT SELECT ON arc_archive_event TO PUBLIC;
CREATE TABLE arc_version
(
av_version CHAR(128) NOT NULL,
av_name BYTE IN TABLE
);
REVOKE ALL ON arc_version FROM PUBLIC;
GRANT ALL ON arc_version TO root;
{ Views are used during inserts and modifies for integrity checking }
CREATE VIEW arc_vset_view
AS
SELECT * FROM arc_vset
WHERE vset_class IN ('S', 'U')
AND vset_onsite IN ('Y', 'N', 'U')
AND vset_imported IN ('Y', 'N')
AND vset_perm_mounted IN ('Y', 'N')
AND vset_transit IN ('Y', 'N')
AND vset_nb_volumes >= 0
WITH CHECK OPTION;
REVOKE ALL ON arc_vset_view FROM PUBLIC;
GRANT SELECT ON arc_vset_view TO root;
GRANT INSERT ON arc_vset_view TO root;
GRANT DELETE ON arc_vset_view TO root;
GRANT UPDATE ON arc_vset_view TO root;
CREATE VIEW arc_volume_view
AS
SELECT * FROM arc_volume
WHERE vol_max_space >= 0
AND vol_used_space >= 0
AND vol_nb_svst >= 0
AND vol_nb_svst_phys >= 0
AND vol_space_exact IN ('Y', 'N')
AND vol_full IN ('Y', 'N')
AND vol_vid IN (SELECT vset_vid FROM arc_vset)
WITH CHECK OPTION;
REVOKE ALL ON arc_volume_view FROM PUBLIC;
GRANT SELECT ON arc_volume_view TO root;
GRANT INSERT ON arc_volume_view TO root;
GRANT DELETE ON arc_volume_view TO root;
GRANT UPDATE ON arc_volume_view TO root;
CREATE VIEW arc_vset_user_view
AS
SELECT * FROM arc_vset_user
WHERE vu_vid IN (SELECT vset_vid FROM arc_vset)
WITH CHECK OPTION;
REVOKE ALL ON arc_vset_user_view FROM PUBLIC;
GRANT SELECT ON arc_vset_user_view TO root;
GRANT INSERT ON arc_vset_user_view TO root;
GRANT DELETE ON arc_vset_user_view TO root;
GRANT UPDATE ON arc_vset_user_view TO root;
CREATE VIEW arc_vol_lock_view
AS
SELECT * FROM arc_vol_lock
WHERE vlck_vid IN (SELECT vset_vid FROM arc_vset)
AND (vlck_vno = 0
OR vlck_vno IN (SELECT vol_vno FROM arc_volume
WHERE vol_vid = vlck_vid))
AND (vlck_rid = 0
OR vlck_rid IN (SELECT req_rid FROM arc_request))
AND vlck_pid > 0
AND vlck_exclusive IN ('Y', 'N')
AND vlck_operation IN ('R', 'W', 'D')
WITH CHECK OPTION;
REVOKE ALL ON arc_vol_lock_view FROM PUBLIC;
GRANT SELECT ON arc_vol_lock_view TO root;
GRANT INSERT ON arc_vol_lock_view TO root;
GRANT DELETE ON arc_vol_lock_view TO root;
GRANT UPDATE ON arc_vol_lock_view TO root;
CREATE VIEW arc_request_view
AS
SELECT * FROM arc_request
WHERE req_type IN ('AR', 'BK', 'CO', 'RT', 'RM', 'RB')
AND req_status IN ('NE', 'EX', 'PA', 'CA', 'FA', 'SU', 'UC')
AND req_user_lang IN ('E', 'F')
AND ( (req_expiry_date IS NOT NULL
AND (req_type IN ('AR', 'BK', 'CO')))
OR req_expiry_date IS NULL)
WITH CHECK OPTION;
REVOKE ALL ON arc_request_view FROM PUBLIC;
GRANT SELECT ON arc_request_view TO root;
GRANT INSERT ON arc_request_view TO root;
GRANT DELETE ON arc_request_view TO root;
GRANT UPDATE ON arc_request_view TO root;
CREATE VIEW arc_pendreq_view
AS
SELECT * FROM arc_pending_req
WHERE pend_rid IN (SELECT req_rid FROM arc_request
WHERE req_status IN ('NE', 'EX', 'PA'))
AND pend_lock_pid >= 0
AND (pend_parent_rid IS NULL
OR pend_parent_rid IN (SELECT req_rid FROM arc_request
WHERE req_status IN ('PA', 'EX')))
WITH CHECK OPTION;
REVOKE ALL ON arc_pendreq_view FROM PUBLIC;
GRANT SELECT ON arc_pendreq_view TO root;
GRANT INSERT ON arc_pendreq_view TO root;
GRANT DELETE ON arc_pendreq_view TO root;
GRANT UPDATE ON arc_pendreq_view TO root;
CREATE VIEW arc_req_vset_view
AS
SELECT * FROM arc_req_vset
WHERE rv_rid IN (SELECT req_rid FROM arc_request)
AND rv_vid IN (SELECT vset_vid FROM arc_vset)
WITH CHECK OPTION;
REVOKE ALL ON arc_req_vset_view FROM PUBLIC;
GRANT SELECT ON arc_req_vset_view TO root;
GRANT INSERT ON arc_req_vset_view TO root;
GRANT DELETE ON arc_req_vset_view TO root;
GRANT UPDATE ON arc_req_vset_view TO root;
CREATE VIEW arc_save_set_view
AS
SELECT * FROM arc_save_set
WHERE svst_rid IN (SELECT req_rid FROM arc_request
WHERE (req_type IN ('AR', 'BK')))
AND svst_vid IN (SELECT vset_vid FROM arc_vset)
AND svst_complete IN ('Y', 'N')
AND svst_nb_volumes >= 0
WITH CHECK OPTION;
REVOKE ALL ON arc_save_set_view FROM PUBLIC;
GRANT SELECT ON arc_save_set_view TO root;
GRANT INSERT ON arc_save_set_view TO root;
GRANT DELETE ON arc_save_set_view TO root;
GRANT UPDATE ON arc_save_set_view TO root;
CREATE VIEW arc_file_view
AS
SELECT * FROM arc_file
WHERE file_type IN ('F', 'D')
AND file_rid IN (SELECT req_rid FROM arc_request
WHERE (req_type IN ('AR', 'BK')))
AND file_compressed IN ('Y', 'N')
AND file_encrypted IN ('Y', 'N')
AND ((file_dno = 0) OR
(file_dno IN (SELECT dir_dno FROM arc_directory
WHERE dir_rid = file_rid)))
WITH CHECK OPTION;
REVOKE ALL ON arc_file_view FROM PUBLIC;
GRANT SELECT ON arc_file_view TO root;
GRANT INSERT ON arc_file_view TO root;
GRANT DELETE ON arc_file_view TO root;
GRANT UPDATE ON arc_file_view TO root;
CREATE VIEW arc_directory_view
AS
SELECT * FROM arc_directory
WHERE dir_rid IN (SELECT req_rid FROM arc_request)
WITH CHECK OPTION;
REVOKE ALL ON arc_directory_view FROM PUBLIC;
GRANT SELECT ON arc_directory_view TO root;
GRANT INSERT ON arc_directory_view TO root;
GRANT DELETE ON arc_directory_view TO root;
GRANT UPDATE ON arc_directory_view TO root;
CREATE VIEW arc_db_file_view
AS
SELECT * FROM arc_db_file
WHERE dbf_fno = (SELECT file_fno FROM arc_file
WHERE file_rid = dbf_rid
AND file_fno = dbf_fno
AND file_type = 'D')
WITH CHECK OPTION;
REVOKE ALL ON arc_db_file_view FROM PUBLIC;
GRANT SELECT ON arc_db_file_view TO root;
GRANT INSERT ON arc_db_file_view TO root;
GRANT DELETE ON arc_db_file_view TO root;
GRANT UPDATE ON arc_db_file_view TO root;
CREATE VIEW arc_file_copy_view
AS
SELECT * FROM arc_file_copy
WHERE fc_fno = (SELECT file_fno FROM arc_file
WHERE file_rid = fc_rid
AND file_fno = fc_fno)
AND fc_vno = (SELECT vol_vno FROM arc_volume
WHERE vol_vid = fc_vid
AND vol_vno = fc_vno)
AND fc_last_sno IN ('Y', 'N')
WITH CHECK OPTION;
REVOKE ALL ON arc_file_copy_view FROM PUBLIC;
GRANT SELECT ON arc_file_copy_view TO root;
GRANT INSERT ON arc_file_copy_view TO root;
GRANT DELETE ON arc_file_copy_view TO root;
GRANT UPDATE ON arc_file_copy_view TO root;
CREATE VIEW arc_ae_view
AS
SELECT * FROM arc_archive_event
WHERE ae_level IN (0, 1, 2)
WITH CHECK OPTION;
REVOKE ALL ON arc_ae_view FROM PUBLIC;
GRANT SELECT ON arc_ae_view TO root;
GRANT INSERT ON arc_ae_view TO root;
GRANT DELETE ON arc_ae_view TO root;
GRANT UPDATE ON arc_ae_view TO root;
{ Physical device table }
CREATE TABLE arc_phys_dev
(
dp_name CHAR(128) PRIMARY KEY,
dp_path CHAR(260), { size matches FNAMELENGTH }
dp_driver CHAR(5),
dp_block_sz INTEGER,
dp_max_space INTEGER
);
REVOKE ALL ON arc_phys_dev FROM PUBLIC;
GRANT ALL ON arc_phys_dev TO root;
GRANT SELECT ON arc_phys_dev TO PUBLIC;
{ arc_replicate : replicate definition table }
CREATE TABLE arc_replicate (
rep_repid INTEGER NOT NULL PRIMARY KEY,
rep_name CHAR(128) NOT NULL UNIQUE,
rep_dxs CHAR(255) NOT NULL,
rep_db CHAR(37) NOT NULL,
rep_refresh CHAR(1) NOT NULL,
rep_extract CHAR(1024) NOT NULL,
rep_creation_date DATETIME YEAR TO SECOND NOT NULL
);
{ arc_server : remote servers per replicate }
CREATE TABLE arc_server (
as_server CHAR(128) NOT NULL,
as_db CHAR(128) NOT NULL,
as_repid INTEGER NOT NULL REFERENCES arc_replicate,
PRIMARY KEY (as_server, as_db, as_repid)
);
{ arc_rep_archive : successful replicate extractions }
CREATE TABLE arc_rep_archive (
ra_repid INTEGER NOT NULL REFERENCES arc_replicate,
ra_rid INTEGER NOT NULL REFERENCES arc_request,
ra_prior_rid INTEGER NOT NULL,
ra_refresh CHAR (1) NOT NULL,
PRIMARY KEY (ra_repid, ra_rid)
);
{ arc_rep_table : replicate distribution status }
CREATE TABLE arc_rep_table (
rt_table CHAR(128) NOT NULL,
rt_db CHAR(128) NOT NULL,
rt_server CHAR(128) NOT NULL,
rt_last_rid INTEGER NOT NULL,
rt_active CHAR(1) NOT NULL,
rt_repid INTEGER NOT NULL
);
{ arc_change_log : change log table mapping }
{
create table arc_change_log (
cl_db char(128) not null,
cl_server char(128) not null,
cl_table char(128) not null,
cl_change_log char(128) not null,
primary key (cl_db, cl_server, cl_table, cl_change_log)
);
}
{ arc_rep_parent : parent replication distribution }
CREATE TABLE arc_rep_parent (
rp_repid INTEGER NOT NULL,
rp_rid INTEGER NOT NULL,
rp_parent_rid INTEGER NOT NULL
);
{ Drop view arc_file_copy_view }
DROP VIEW arc_file_copy_view;
{ Modification of arc_file_copy table }
ALTER TABLE arc_file_copy
ADD fc_device CHAR(120);
{ Recreate view arc_file_copy_view }
CREATE VIEW arc_file_copy_view
AS
SELECT * FROM arc_file_copy WHERE fc_fno = (SELECT file_fno FROM arc_file
WHERE file_rid = fc_rid AND file_fno = fc_fno)
AND fc_vno = (SELECT vol_vno FROM arc_volume
WHERE vol_vid = fc_vid
AND vol_vno = fc_vno)
AND fc_last_sno IN ('Y', 'N')
WITH CHECK OPTION;
REVOKE ALL ON arc_file_copy_view FROM PUBLIC;
{ 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
{If $INFORMIXDIR/bin/onpload not found try /usr/informix/bin/onpload}
{ or NT style}
ON EXCEPTION IN (-668) SET rtnsql, rtnisam
IF rtnisam = -2 THEN
{ If onpload.exe not found by default UNIX style-environment}
LET command = 'cmd /c %INFORMIXDIR%\bin\onpload ' || args;
SYSTEM (command);
RETURN 0;
END IF
IF rtnisam = -1 THEN
LET command = '/usr/informix/bin/onpload ' || args;
SYSTEM (command);
RETURN 0;
END IF
RETURN rtnisam;
END EXCEPTION
LET command = '$INFORMIXDIR/bin/onpload ' || args;
SYSTEM (command);
RETURN 0;
END PROCEDURE;
GRANT EXECUTE ON informix.start_onpload TO PUBLIC;
{ 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 stored procedure dbexp used by IECC }
CREATE PROCEDURE informix.dbexp(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
ON EXCEPTION IN (-668) SET rtnsql, rtnisam
IF rtnisam = -2 THEN
{ If dbexport.exe not found by default NT style-environment }
LET command = 'cmd /c %INFORMIXDIR%\bin\dbexport ' || args;
SYSTEM (command);
RETURN 0;
END IF
RETURN rtnisam;
END EXCEPTION
LET command = '$INFORMIXDIR/bin/dbexport ' || args;
SYSTEM (command);
RETURN 0;
END PROCEDURE;
GRANT EXECUTE ON informix.dbexp TO PUBLIC;
{ Create stored procedure dbimp used by IECC }
CREATE PROCEDURE informix.dbimp(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
ON EXCEPTION IN (-668) SET rtnsql, rtnisam
IF rtnisam = -2 THEN
{ If dbimport.exe not found by default UNIX style-environment}
LET command = 'cmd /c %INFORMIXDIR%\bin\dbimport ' || args;
SYSTEM (command);
RETURN 0;
END IF
RETURN rtnisam;
END EXCEPTION
LET command = '$INFORMIXDIR/bin/dbimport ' || args;
SYSTEM (command);
RETURN 0;
END PROCEDURE;
GRANT EXECUTE ON informix.dbimp TO PUBLIC;
GRANT CONNECT TO PUBLIC;
UPDATE STATISTICS;
CLOSE DATABASE;