The following topics cover PSQL system tables:

The information used by PSQL and its components is stored in special tables called system tables.

Do not write your applications to query system tables directly. Some columns in system tables may not be documented. Your application can retrieve information stored in system tables by using any of the following methods:

The PSQL APIs are documented in the PSQL Developer Reference. The development components are designed to remain compatible with the database engine from release to release. The format of the system tables depends on the internal architecture of the PSQL database engine, which may change from release to release. Applications that directly access undocumented columns of system tables may have to be changed if the internal architecture of PSQL changes.

The following list of system tables gives the names of associated files and identifies system table contents.

PSQL creates all of the system tables when you create a database.

Two other system tables that you may encounter are VARIANT.DDF and OCCURS.DDF (for a V1 database) and PVVARIANT.DDF and PVOCCURS.DDF (for a V2 database).These two system files are used for COBOL support and do not require any direct intervention by a user. Future versions of the utilities for COBOL may implement a different architecture, in which case these system tables may no longer be required. See also SQL Access for COBOL Applications.

This section discusses the structure of the system tables:

The X$Attrib system table is associated with the file ATTRIB.DDF. X$Attrib contains information about the column attributes of each column in the database; there is an entry for each column attribute you define. The structure of X$Attrib is described in X$Attrib System Table Structure:

When you define multiple attributes for a single column, the X$Attrib system table contains multiple entries for that column ID — one for each attribute you define. If you do not define column attributes for a particular column, that column has no entry in the X$Attrib table. The text in the Xa$Attrs column appears exactly as you define it with PSQL. One index is defined for the X$Attrib table, as explained in X$Attrib System Table Index Definitions:

The X$Depend system table is associated with the file DEPEND.DDF. X$Depend contains information about trigger dependencies such as tables, views, and procedures. The structure of X$Depend is as follows:

Two indexes are defined for the X$Depend table, as follows:

Index Number corresponds to the value stored in the Xi$Number column in the X$Index system table. Segment Number corresponds to the value stored in the Xi$Part column in the X$Index system table.

The X$Field system table is associated with the file FIELD.DDF. X$Field contains information about all the columns and named indexes defined in the database. The structure of X$Field is as follows:

Column Xe$File corresponds to column Xf$Id in the X$File system table and is the link between the tables and the columns they contain. For example, the following query returns all field definitions in order for the Billing table:

The integer values in column Xe$DataType are codes that represent the PSQL data types. See PSQL Supported Data Types for the codes.

Five indexes are defined for the X$Field table, as follows:

The X$File system table is associated with the file FILE.DDF. For each table defined in the database, X$File contains the table name, the location of the associated table, and a unique internal ID number that PSQL assigns. The structure of X$File is as follows:

Two indexes are defined for the X$File table.

The X$Index system table is associated with the file INDEX.DDF. X$Index contains information about all the indexes defined on the tables in the database. The structure of X$Index is as follows:

The Xi$File column corresponds to the Xf$Id column in the X$File system table. The Xi$Field column corresponds to the Xe$Id column in the X$Field system table. Thus, an index segment entry is linked to a file and to a field.

The Xi$Flags column contains integer values that define the index attributes. The following table describes how PSQL interprets each bit position when the bit has the binary value of 1. Bit position 0 is the rightmost bit in the integer.

The value in the Xi$Flags column for a particular index is the sum of the decimal values that correspond to the index attributes. Three indexes are defined for the X$Index table, as follows:

Index Number corresponds to the value stored in the Xi$Number column in the X$Index system table. Index numbering starts at zero. Segment Number corresponds to the value stored in the Xi$Part column in the X$Index system table.

To see the information about the index segments defined for the Billing table, for example, issue the following query:

The X$Proc system table is associated with the file PROC.DDF. X$Proc contains the compiled structure information for every stored procedure defined. The structure of X$Proc is as follows:

One index is defined for the X$Proc table, as follows:

A single stored procedure may be stored in multiple entries in X$Proc, linked by Xp$Name.

The X$Relate system table is associated with the file RELATE.DDF. X$Relate contains information about the referential integrity (RI) constraints defined on the database. X$Relate is automatically created when the first foreign key is created, since this results in a relationship being defined.

The structure of X$Relate is as follows:

Five indexes are defined for the X$Relate table, as follows:

The X$Rights system table is associated with the file RIGHTS.DDF. X$Rights contains access rights information for each user. PSQL uses this table only when you enable the security option. The structure of X$Rights is as follows:

The Xr$User column corresponds to the Xu$Id column in the X$User table. The Xr$Table column corresponds to the Xf$Id column in the X$File table. The Xr$Column column corresponds to the Xe$Id column in the X$Field table.

The Xr$Rights column contains integer values whose rightmost 8 bits define the user access rights. The following table describes how PSQL interprets the value. Values from this table may be combined into a single Xr$Rights value.

A decimal equivalent of 0 implies no rights.

The value in the Xr$Rights column for a particular user is the bit-wise intersection of the hex values corresponding to the access rights that apply to the user. It is not the sum of the decimal values.

For example, the value in Xr$Rights for a user with all rights assigned would be represented as follows:

144 | 160 | 64 | 130 | 132 | 136 = 254

Three indexes are defined for the X$Rights table, as follows:

The X$Trigger system table is associated with the file TRIGGER.DDF. X$Trigger contains information about the triggers defined for the database. The structure of X$Trigger is as follows:

A trigger that is long enough may require multiple entries in Trigger.DDF. Each entry has the same trigger name in the Xt$Name field, and is used in the order specified by the Xt$Sequence field.

Three indexes are defined for the X$Trigger table, as follows:

The trigger may be stored in more than one entry in X$Trigger, linked by Xt$Name and ordered by Xt$Sequence.

The X$User system table is associated with the file USER.DDF. X$User contains the name and password of each user and the name of each user group. PSQL uses this table only when you enable the security option. The structure of X$User is as follows:

The Xu$Flags column contains integer values whose rightmost 8 bits define the user or group attributes. The following table describes how PSQL interprets each bit position when the bit has the binary value of 1. Bit position 0 is the rightmost bit in the integer.

The value in the Xu$Flags column for a particular user or group is the sum of the decimal values corresponding to the attributes that apply to the user or group.

Two indexes are defined for the X$User table, as shown in the following table.

The X$View system table is associated with the file VIEW.DDF. X$View contains view definitions, including information about joined tables and the restriction conditions that define views. You can query the X$View table to retrieve the names of the views that are defined in the dictionary.

The first column of the X$View table contains the view name; the second and third columns describe the information found in the LVAR column, Xv$Misc. The structure of X$View is as follows:

Two indexes are defined for the X$View table, as follows:

A single view may be stored in multiple X$View entries, linked by Xv$Name and ordered by Xv$Id.

The X$Attrib system table is associated with the file PVATTRIB.DDF. X$Attrib contains information about the column attributes of each column in the database; there is an entry for each column attribute you define. The structure of X$Attrib is as follows:

When you define multiple attributes for a single column, the X$Attrib system table contains multiple entries for that column ID — one for each attribute you define. If you do not define column attributes for a particular column, that column has no entry in the X$Attrib table. The text in the Xa$Attrs column appears exactly as you define it with PSQL. One index is defined for the X$Attrib table, as follows:

The X$Depend system table is associated with the file PVDEPEND.DDF. X$Depend contains information about trigger dependencies for such objects as tables, views, and procedures. The structure of X$Depend is as follows:

Two indexes are defined for the X$Depend table, as follows:

The X$Field system table is associated with the file PVFIELD.DDF. X$Field contains information about all the columns and named indexes defined in the database. The structure of X$Field is as follows:

Column Xe$File corresponds to column Xf$Id in the X$File system table and is the link between the tables and the columns they contain. For example, the following query returns all field definitions in order for the Billing table:

The integer values in column Xe$DataType are codes that represent the PSQL data types. See PSQL Supported Data Types for the codes.

Five indexes are defined for the X$Field table, as shown in the following table.

The X$File system table is associated with the file PVFILE.DDF. For each table defined in the database, X$File contains the table name, the location of the associated table, and a unique internal ID number that PSQL assigns. The structure of X$File is shown in the following table.

Two indexes are defined for the X$File table.

The X$Index system table is associated with the file PVINDEX.DDF. X$Index contains information about all the indexes defined on the tables in the database. The structure of X$Index is as follows:

The Xi$File column corresponds to the Xf$Id column in the X$File system table. The Xi$Field column corresponds to the Xe$Id column in the X$Field system table. Thus, an index segment entry is linked to a file and to a field.

The Xi$Flags column contains integer values that define the index attributes. The following table describes how PSQL interprets each bit position when the bit has the binary value of 1. Bit position 0 is the rightmost bit in the integer.

The value in the Xi$Flags column for a particular index is the sum of the decimal values that correspond to the index attributes. Three indexes are defined for the X$Index table, as follows:

Index Number corresponds to the value stored in the Xi$Number column in the X$Index system table. Index numbering start at zero. Segment Number corresponds to the value stored in the Xi$Part column in the X$Index system table.

To see the information about the index segments defined for the Billing table, for example, issue the following query:

The X$Proc system table is associated with the file PVPROC.DDF. X$Proc contains the compiled structure information for every stored procedure defined. The structure of X$Proc is as follows:

Four indexes are defined for the X$Proc table, as follows:

The X$Relate system table is associated with the file PVRELATE.DDF. X$Relate contains information about the referential integrity (RI) constraints defined on the database. X$Relate is automatically created when the first foreign key is created, since this results in a relationship being defined.

The structure of X$Relate is as follows:

Five indexes are defined for the X$Relate table, as follows:

The X$Rights system table is associated with the file PVRIGHTS.DDF. X$Rights contains access rights information for each user. PSQL uses this table only when you enable the security option. The structure of X$Rights is as follows:

The Xr$User column corresponds to the Xu$Id column in the X$User table. The Xr$Object column corresponds to one of the following:

The Xr$Column column corresponds to the Xe$Id column in the X$Field table.

The Xr$Rights column contains integer values whose rightmost 8 bits define the user access rights. The following table describes how PSQL interprets the value. Values from this table may be combined into a single Xr$Rights value.

A decimal equivalent of 0 implies no rights.

The value in the Xr$Rights column for a particular user is the bit-wise intersection of the hex values corresponding to the access rights that apply to the user. It is not the sum of the decimal values.

For example, the value in Xr$Rights for a user with all rights assigned is represented as follows:

144 | 160 | 64 | 130 | 132 | 136 = 254

The value in Xr$Rights for a user with all rights assigned for a view is represented as follows:

64 | 130 | 132 | 136 = 206

The value in Xr$Rights for a user with all rights assigned for a stored procedure is represented as follows:

192 = 192

Three indexes are defined for the X$Rights table, as follows:

The X$Trigger system table is associated with the file PVTRIG.DDF. X$Trigger contains information about the triggers defined for the database. The structure of X$Trigger is as follows:

Three indexes are defined for the X$Trigger table, as follows:

The X$User system table is associated with the file PVUSER.DDF. X$User contains the name and password of each user and the name of each user group. PSQL uses this table only when you enable the security option. The structure of X$User is as follows:

The Xu$Flags column contains integer values whose rightmost 8 bits define the user or group attributes. The following table describes how PSQL interprets each bit position when the bit has the binary value of 1. Bit position 0 is the rightmost bit in the integer.

The value in the Xu$Flags column for a particular user or group is the sum of the decimal values corresponding to the attributes that apply to the user or group.

Two indexes are defined for the X$User table, as follows:

The X$View system table is associated with the file PVVIEW.DDF. X$View contains view definitions, including information about joined tables and the restriction conditions that define views. You can query the X$View table to retrieve the names of the views that are defined in the dictionary.

The first column of the X$View table contains the view name; the second and third columns describe the information found in the LVAR column, Xv$Misc. The structure of X$View is as follows:

Three indexes are defined for the X$View table, as follows: