SQL

Home	Starting	Environment	DBMS	VisualPQL	Procedures	SQL	Forms	Host/API	Index

SQL

System Tables

System tables contain information about the database(s) and tabfile(s) currently connected. 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.

Database System Tables

Database system tables are all views and access information for a single database at a time. These views can be referenced on the FROM clause of the SELECT statement as: [database.]viewname

$DBCASE

Case schema information. $DBDOC

Case and record document text. $DBSTATS

Database general information. $REC

Record schema information. $SORTID

Record sort-ids or keys. $VALLABEL

Variable value labels. $VALVALUE

Variable valid values. $VAR

Variable schema information. $VARLABEL

Variable labels.

Tabfile Views and Tables

$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

Column schema information. $INDEX

Index definitions. $INDEXCOL

Index column definitions. $PASSWORD

Group and user names and passwords. $SECURITY

Tabfile and table permissions. $TAB

Table schema information. $TFSTATS

Tabfile general information. $TRANGE

Column valid and missing ranges. $VALUE_LABEL

Value labels.

Generic Tables

These tables do not reference a record or table and can be used at anytime. They are mainly for ODBC conformance.

$SQLTYPE

Data type information.

$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

CASEIDTY

CASEID

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 SIR/XS 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 SIR/XS words. MAXDATSZ

Maximum size of data record in SIR/XS words. ACTINDB

Number of active index blocks. INAINDB

Number of inactive data blocks. INDBLKSZ

Size of index block in SIR/XS words. INDEXLEN

Length of index in SIR/XS 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 SIR/XS 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

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.

$SQLTYPE - Generic Data Type Information

Contains one row for each type of variable used in SirSQL. This information is static within SirSQL. The columns are:

TYPE_NAME
DATA_TYPE
PRECISION
LITERAL_PREFIX
LITERAL_SUFFIX
CREATE_PARAMS
NULLABLE
CASE_SENSITIVE
SEARCHABLE
UNSIGNED_ATTRIBUTE
MONEY
AUTO_INCREMENT
LOCAL_TYPE_NAME
MINIMUM_SCALE
MAXIMUM_SCALE

  TYPE_NAME   DATA_TYPE  PRECISION  LITERAL_PREFIX  LITERAL_SUFFIX  CREATE_PARAMS    NULLABLE  CASE_SENSITIVE  SEARCHABLE  UNSIGNED_ATTRIBUTE  MONEY     AUTO_INCREMENT
  ----------  ---------  ---------  --------------  --------------  ---------------  --------  --------------  ----------  ------------------  --------  --------------
  TINYINT            -6          3  '               '               
  CHARACTER           1        254  '               '               length                  1               1           4                   0         0               0
  NUMERIC             2         15  '               '               length         
  NUMERIC             3         15  '               '               length,decimal          1               0           3                   0         0               0
  INT                 4         10  '               '               length,decimal          1               0           3                   0         0               0
  SMALLINT            5         15  '               '               length,decimal 
  FLOAT               6         15  '               '               length,decimal 
  REAL                7          7  '               '               length,decimal 
  DOUBLE              8         15  '               '               length,decimal 
  DATE                9         10  '               '               length                  1               0           3                   1         0               0
  TIME               10          8  '               '               'date_map'              1               0           3                   1         0               0
  CHARACTER          12        254  '               '               length                  1               1           4                   1         0               0

This table exists primarily for ODBC clients that may request this information.