top of page
Writer's pictureRajesh S Nair

OpenEdge Schema Tables

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.

1,151 views0 comments

Recent Posts

See All

Comments


bottom of page