Skip to main content

What are schema tables?



 Schema tables are metadata:  i.e data about data

 These are real tables, stored in the database.

 It describe what is stored in the database.It includes tables, indexes, sequences, views, etc.,

 Describe database physical storage like Areas, extents, transaction logs etc.

 Describe users and access rights

 4GL and SQL share most schema tables

 SQL has additional “catalog tables” and views.


What are Virtual System Tables (aka VST’s)?


 Are NOT schema tables

 NOT real tables

 VST data are NOT stored anywhere

 VST records are created “on the fly” when retrieved

 VST tables describe database status and activity

 The schema does contain descriptions of VST’s


Following will describe some important schema tables which comes with a OE database

Physical Storage Schema Tables





Basic 4GL Schema Tables






How 4GL finds records


When we write a query to fetch a record , schema tables play in the sequence of operations which takes in background to fetch back the queried record.

Following are the sequence of operations which happen when we fetch a record


 Compiler determines which index (perhaps cust-num)
• R-code has index information
• Query used to form equality or range bracket

 At runtime, load schema cache into memory

 Look up index number (_index.idx-num, # 113)

 Find _storage-object record for index 113

 Load into “om cache” so we can use it again

 Get location of index root block from _storage-object

 Traverse index b-tree to leaf, perhaps cust-num = 20

 Get record’s rowid from index leaf block

 Read data block containing record

 Copy record to 4GL buffer or network buffer







We can utilize these schema tables to take report on various database components.

Below are few helpful 4GL queries using the schema table


List All Tables


for each _file
where (0 < _file-num) and
(_file-num < 32000):

display _file-num _file-name
.
end.


List Basic Schema Tables


for each _file
where (_file-num < 0) and
(_file-num > -80)
by _file-num descending:

display _file-num _file-name
.
end.


List VST Tables


for each _file
where (_file-num < -16384)
by _file-num descending:

display _file-num _file-name
.
end.


List SQL Catalogue Tables


for each _file
where (_file-name begins “_SYS”):
display _file-num _file-name
.
end.



List Tables and Thier Fields


output to tables.txt.
for each _file
where (0 < _file-num):

put _file-name skip.

for each _field of _file:

put “ “ _field-name skip.

end.
put “” skip.

end.
output close.



List Indexes by Table


output to index.txt.
for each _file where _file-num > 0:
put _file-name skip.
for each _index of _file:
put " " _index-name skip.
for each _index-field of _index:
find _field where recid(_field) = _field-recid.
put " " _field-name skip.
end.
end.
put "" skip.
end.
output close.


Space Used by Each Area


for each _AreaStatus where(not _AreaStatus-Areaname matches "*AfterImageArea*") no-lock:
display
_AreaStatus-Areanum format ">>>" column-label "Num"
_AreaStatus-Areaname format "x(20)" column-label "AreaName"
_AreaStatus-Totblocks column-label "Tot Block"
_AreaStatus-Hiwater column-label "High Water"
_AreaStatus-Hiwater / _AreaStatus-Totblocks * 100 column-label "%use"
_AreaStatus-Extents format ">>>" column-label "NumExtents"
_AreaStatus-Freenum column-label "Freenum"
_AreaStatus-Rmnum column-label "RMnum".
end.


Find Table Currently in Use


find first _MyConnection no-lock.
for each _UserTableStat where _UserTableStat-Conn = _MyConnection._MyConn-UserId no-lock:
find _file where _file-num = _UserTableStat-Num no-lock no-error.
if available _file
then
display
_UserTableStat-Num
_file-name format "x(20)"
_UserTableStat-read
format ">>>>>>>>>>"
_UserTableStat-create
format ">>>>>>>>>>"
_UserTableStat-update
format ">>>>>>>>>>"
_UserTableStat-delete
format ">>>>>>>>>>".
end.



Table - Area List


for each _Area, each _Storageobject where (_Storageobject._Area-number = _Area._Area-number),each _File where (_File._File-Number = _Storageobject._Object-number) and (_File._File-Number > 0) break
by _File._File-name:
display _Area._Area-name _File._File-name.
end.



No Of BI Clusters


find _AreaStatus where _AreaStatus-Areanum = 3.
find _dbStatus.
display _AreaStatus-Hiwater *
_dbStatus._DbStatus-BiBlkSize /
_dbStatus-BiClSize /
1024.



Comments