| ||
| SQL | | System Tables |
SELECT can
be used to retrieve information from these in the same manner as from any other
table or view though the menus can be used to access much of the same information
in a more convenient manner.
As an example of using these tables, the following three SELECTs use the $REC system
view which describes records. The first query retrieves all the information
about each record in the default database; the second retrieves all the
information about each record in the COMPANY database;
the third retrieves all the information about the OCCUP record
in the COMPANY database.
SELECT * FROM $REC SELECT * FROM COMPANY.$REC SELECT * FROM COMPANY.$REC WHERE RECNAME EQ 'OCCUP'Most of the system tables are views. That is, they are not physical tables but are representations of the data presented by SQL as tables.
FROM clause of the
SELECT statement as: [database.]viewname$PASSWORD, $SECURITY and $VALUE_LABEL are
tables; all the others are views. The tabfile views and tables can be
referenced on the FROM clause of the SELECT
as: [tabfile.] viewname
$COL - Table Columns Schema
Contains one row for each column (or variable) in each table of the tabfile.
The columns are:TABFILE
Tabfile name.
TABLE
Table name.
VARNAME
Variable name.
VARTYPE
Variable type.
VARLEN
Variable length.
VARLABEL
Variable label.
SCALE
Variable scaling factor.
BIAS
Integer bias factor.
NRANGES
Number of missing or valid ranges.
MAP
Variable display map.
DECIMAL
Number of decimal places displayed.
FILL
Fill character for display.
LZERO
Leading zero character.
LNEG
Leading negative character for display.
LPOS
Leading positive character for display.
MISSING
Missing value character.
NULL
Missing value string.
FORMAT
Format for printing number.
SEPARATOR
Separator character to left of value.
THOUSANDS
Thousands separation character.
TNEG
Trailing negative character.
TPOS
Trailing positive character.
VALLABS
Value labels defined (Yes/No).
ZERO
String printed for hard zero.
$DBCASE - Database Case Schema
Contains only one row with general database
parameters. The columns are:DBNAME
Database name.
UPLEVEL
Update level.
CASEID
CASEID variable name.
CASEIDOR
CASEID order (ascending or descending).CASEIDTY
CASEID variable type.NCASES
Number of cases.
NRECS
Number of records.
NTEMPS
Number of temporary variables.
NVARS
Number of variables.
MAXCASES
Maximum number of cases.
MAXRECS
Maximum number of records.
MAXRECTY
Maximum number of defined records. (Different record definitions not individual
records in the database.)
$DBDOC - Database Documentation
Contains one row for each line of documentary
text describing the database. The columns are:RECNUM
Record number.
RECNAME
Record name.
LINENUM
Line number of text line.
LINE
Line of documentary text.
$DBSTATS - Database Statistics
Contains only one row with database statistics. The columns are:DBNAME
Database name.
UPLEVEL
Update level.
CREDATE
Creation date.
CRETIME
Creation time.
CHNGDATE
Date of most recent update.
CHNGTIME
Time of most recent update.
NCASES
Number of cases.
NRECS
Number of records.
NVARS
Number of variables.
NTEMPS
Number of temporary variables.
AVGRECS
Average number of records per case.
CASEIDSZ
Size of CASEID in bytes.
CIRLEN
Length of CIR in SIR2002 words.
KEYSIZE
Key size in bytes.
ACTDATB
Number of active data blocks.
INADATB
Number of inactive data blocks.
DATBLKSZ
Data block size.
MINDATSZ
Minimum size of data record in SIR2002 words.
MAXDATSZ
Maximum size of data record in SIR2002 words.
ACTINDB
Number of active index blocks.
INAINDB
Number of inactive data blocks.
INDBLKSZ
Size of index block in SIR2002 words.
INDEXLEN
Length of index in SIR2002 words.
MAXINENT
Maximum number of index entries in one block.
MAXRECVR
Maximum number of variables in any one record.
$INDEX - Tabfile Index Definitions
Contains one row for each index in the specified
tabfile. The columns are:TABFILE
Tabfile name.
TABLE
Table indexed.
INDEX
Name of index on table.
$INDEXCOL - Tabfile Index Column Definitions
Contains one row for each column in each index on tables in the specified
tabfile. The columns are:TABFILE
Tabfile name.
TABLE
Table indexed.
INDEX
Name of index on table.
COL
Name of the column in index.
$PASSWORD - Group User Names
Contains one row for each group and
group-user name. Only a DBA has the authority to view this table. The columns
are:GRPNAME
Group name.
USERNAME
Name of the user within a group.
$REC - Database Record Schema
Contains one row for each
defined type of record in the
database. The columns are:RECNUM
Record number.
RECNAME
Record name.
COUNT
Number of records of this type in the database.
IDCNT
Number of sort ids (including the case id).
LENGTH
Length of records in SIR2002 words.
LOCK
Lock status (YES/NO).
MAX
Max number of records of this type per case.
VARCNT
Number of record variables in this record.
$SECURITY - Tabfile and Table Permissions
Contains a row for the tabfile, and a row
per table and user permission. Only a DBA has the authority to view this table.
Each column which refers to a permission holds one of three values, N, Y or G.
These mean respectively, no permission, permission and permission with the
ability to grant this permission to others. The columns are:GRPNAME
Group name holding these permissions.
USERNAME
User holding these permissions.
TABLE
Table name.
COLADD
Able to add columns.
TABFILECONNECT
Able to connect tabfile.
TABCREATE
Able to create tables.
DBA
Able to act as Database administrator.
COLDELETE
Able to delete columns.
ROWDELETE
Able to delete rows.
TABLEDROP
Able to drop table.
INDEXCREATE
Able to create or drop an index.
ROWADD
Able to add rows.
COLMOD
Able to modify columns.
SELECT
Able to select rows.
ROWMOD
Able to modify rows.
GRANTERGRPNAME
Group granting permission to this group or user.
GRANTERUSERNAME
User granting permission to this group or user.
COLPERM
Columns permissions if a table permission entry in column order.
$SORTID - Sort Id Variables
Contains one row for each keyfield (sort id)
of every defined record in the database. The columns are:RECNUMB
Record number.
RECNAME
Record name.
VARNAME
Variable name.
ORDER
Sort order (Ascending or Descending) of the sort id variable.
TYPE
Variable type.
$TAB - Tables
Contains one row for each table on the tabfile. The columns are:TABFILE
Tabfile name.
TABLE
Table name.
UPLEVEL
Update level.
DATECREATE
Date of creation.
TIMECREATE
Time of creation.
DATEUPDATE
Date of last update.
TIMEUPDATE
Time of last update.
NROWS
Number of rows in table.
NCOL
Number of columns in table.
NINDEX
Number of indexes associated with table.
MAXROWS
Maximum length of each row in bytes.
LENGTH
Length of fixed area of each row in bytes.
NBLOCKS
Number of data blocks in the table.
NROWDELETE
Number of rows deleted.
PADDING
Padding percentage (1-99).
$TFSTATS - Tabfile Statistics
Contains one row for each tabfile currently connected. The columns are:TABFILE
Tabfile name.
DATECREATE
Date of creation.
TIMECREATE
Time of creation.
DATEUPDATE
Date of most recent update.
TIMEUPDATE
Time of most recent update.
NTABLES
Number of tables on tabfile.
TABFILELDI
Tabfile filename (logical dataset identifier).
BLOCKSZ
Tabfile block size.
JOURNAL
Internal filename of journal file if defined.
$TRANGE - Tabfile Column Ranges
Contains one row for each missing or valid range for any column in every
table of the specified tabfile. The columns are:TABFILE
Tabfile name.
TABLE
Table name.
VARNAME
Variable (or Column) name.
RANGETYPE
Type of range
'Valid' or 'Missing' for values
'Valid [,]' or 'Missing [,]' for ranges.LOW
Minimum value.
HIGH
Maximum value.
$VALLABEL - Database Value Labels
Contains one row for each value label of
each variable of every defined record for the database. The columns are:RECNUM
Record number.
RECNAME
Record name.
VARNAME
Variable name.
NVAL
Numeric value.
SVAL
String value.
LABEL
Value label.
$VALUE_LABEL - Tabfile Value Labels
Contains one row for each
value label in tables of the specified tabfile. The columns are:TABLE
Table name.
VARNAME
Column (or variable) name.
NVALUE
Value if the column is numeric.
SVALUE
Value if the column is a string.
LABEL
Value label.
MVALUE
Reserved for future use.
REFCOUNT
Reserved for future use.
$VALVALUE - Database Valid Values
Contains one row for each valid value of
each variable of every defined record for the current database. The columns are:RECNUM
Record number.
RECNAME
Record name.
VARNAME
Variable name.
NVAL
Numeric value.
SVAL
String value.
$VAR - Database Variables
Contains one row for each variable of every record
type of the current database. The columns are:RECNUM
Record number.
RECNAME
Record name.
VARNAME
Variable name.
LABEL
Variable label.
TYPE
Variable type.
LENGTH
Variable length in bytes.
NMIN
Minimum numeric value.
NMAX
Maximum numeric value.
SMIN
Minimum string value.
SMAX
Maximum string value.
MISS
Number of missing values.
NMISS1
Numeric missing value 1.
NMISS2
Numeric missing value 2.
NMISS3
Numeric missing value 3.
SMISS1
String missing value 1.
SMISS2
String missing value 2.
SMISS3
String missing value 3.
MAP
Date or time map.
SCALE
Scaling factor.
VALLABS
Whether value labels have been defined.
VVALS
Whether valid values have been defined.
$VARLABEL - Database Variable Labels
Contains one row for each line of label
information for each variable of every defined record for the database. The columns
are:RECNUM
Record number.
RECNAME
Record name.
VARNAME
Variable name.
LINENO
Line number.
LABEL
Label text.