System Tables Structure

System Tables Structure

This section discusses the structure of the system tables:

•

V1 Metadata System Tables

•

V2 Metadata System Tables

V1 Metadata 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:

Table 130

X$Attrib System Table Structure

Case Insensitive

Corresponds to Xe$Id in X$Field.

L (logical positioning)

O (column collation)

C (character); H (heading); M (mask); R (range); or V (value)1

Length of text in Xa$Attrs.

LONGVARCHAR
(NOTE)

Text that defines the column attribute.

1Attribute type C, H, M, R and V are legacy validation types valid only in a Pervasive.SQL 7 or Scalable SQL environment. PSQL releases newer than Pervasive.SQL 7 use only the D (default), L (logical positioning), and O (column collation) attributes.

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:

Table 131

X$Attrib System Table Index Definitions

Case Insensitive

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:

Table 132

X$Depend System Table Structure

Case Insensitive

Name of trigger. It corresponds to Xt$Name in X$Trigger.

1 for Table, 2 for View, 3 for Procedure.

Name of dependency with which the trigger is associated. It corresponds to either Xf$Name in X$File, Xv$Name in X$View, or Xp$Name in X$Proc.

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

Table 133

X$Depend System Table Index Definitions

Case Insensitive

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:

Table 134

X$Field System Table Structure

Case Insensitive

Internal ID PSQL assigns, unique for each field in the database.

ID of table to which this column or named index belongs. It corresponds to Xf$Id in X$File.

Column name or index name.

Control field:
0 through 26: column data type
227: constraint name
255: index name

Column offset in table; index number if named index. Offsets are zero-relative.

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

Column size, representing the internal storage, in bytes, required for the field.

Size does not include the NULL byte for TRUE NULL fields.

Column decimal place (for DECIMAL, NUMERIC, NUMERICSA, NUMERICSTS, MONEY, or CURRENCY types). Relative bit positions for contiguous bit columns. Fractional seconds for TIMESTAMP data type.

Bit 0 is the case flag for string data types.

If bit 0 = 1, the field is case insensitive.

If bit 2 = 1, the field allows null values.

Bit 3 of Xe$flag is used to differentiate a Pervasive.SQL v7 1-byte TINYINT (B_TYPE_INTEGER unsigned) from Relational Engine's 1-byte TINYINT (B_TYPE_INTEGER, but signed).

If bit 3 = 1 and Xe$datatype = 1 and Xe$size =1, then it means that TINYINT column is created by the Relational Engine and is a signed 1-byte TINYINT.

If bit 3 = 0 and Xe$datatype = 1 and xe$size = 1 then it means that TINYINT column is created by the legacy SQL engine and is an unsigned 1-byte TINYINT.

If bit 11 = 1, the field is interpreted as a wide character NLONGVARCHAR field rather than a character LONGVARCHAR field.

If bit 12 = 1, the field is interpreted as BINARY.

If bit 13 = 1, the field is interpreted as DECIMAL with even-digit precision.

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:

SELECT "X$Field".*

FROM X$File,X$Field

WHERE Xf$Id=Xe$File AND Xf$Name = 'Billing' AND Xe$DataType <= 26

ORDER BY Xe$Offset

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:

Table 135

X$Field System Table Index Definitions

Case Insensitive

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:

Table 136

X$File System Table Structure

Case Insensitive

Internal ID PSQL assigns.

File location (path name).

File flags. If bit 4 = 1, the file is a dictionary file. If bit 4 = 0, the file is user-defined. If bit 6=1, the table supports true nullable columns.

Two indexes are defined for the X$File table.

Table 137

X$File System Table Index Definitions

Case Insensitive

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:

Table 138

X$Index System Table Structure

Case Insensitive

Unique ID of the table to which the index belongs. It corresponds to Xf$Id in X$File.

Unique ID of the index column. It corresponds to Xe$Id in X$Field.

Index number (range 0 – 119).

Segment number (range 0 – 119).

Index attribute flags.

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.

Table 139

Xi$Flags Bit Positions

Decimal Equivalent

Index allows duplicates.

Index is modifiable.

Indicates an alternate collating sequence.

Null values are not indexed (refers to Btrieve NULLs, not SQL true NULLS).

Another segment is concatenated to this one in the index.

Index is case-insensitive.

Index is collated in descending order.

Index is a named index if bit 0 is 0. If bit 0 is 1 and bit 7 is 1, the index uses the repeating duplicates key method. If bit 0 is 1 and bit 7 is 0, the index uses the linked duplicates key method. See also LINKDUP. For a detailed discussion of linked duplicates method and repeating duplicates method, see Methods for Handling Duplicate Keys in Advanced Operations Guide.)

Index is a Btrieve extended key type.

Index is partial.

Index is a foreign key.

Index is a primary key referenced by some foreign key.

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

Table 140

X$Index System Table Index Definitions

Case Insensitive

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:

SELECT Xe$Name,Xe$Offset, "X$Index".*

FROM X$File,X$Index,X$Field

WHERE Xf$Id=Xi$File and Xi$Field=Xe$Id and Xf$Name = 'Billing'

ORDER BY Xi$Number,Xi$Part

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:

Table 141

X$Proc System Table Structure

Case Insensitive

Stored procedure name.

Version ID. This is reserved for future use.

0-based Sequence Number.

1 for stored statement, 2 for stored procedure or 3 for external procedure.

LONGVARCHAR
(LVAR)

Internal representation of stored procedure.

Note: Stored statements and external procedures were supported in versions of Pervasive.SQL prior to Pervasive.SQL 2000i. Only stored procedures have been supported since Pervasive.SQL 2000i.

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

Table 142

X$Proc System Table Index Definitions

Case Insensitive

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:

Table 143

X$Relate System Table Structure

Case Insensitive

Primary table ID.

Index number of primary key in primary table.

Dependent table ID.

Index number of foreign key in dependent table.

Foreign key name.

1 for restrict.

1 for restrict, 2 for cascade.

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

Table 144

X$Relate System Table Index Definitions

Case Insensitive

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:

Table 145

X$Rights System Table Structure

Case Insensitive

Table or column rights flag

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.

Note: For any row in the system table that describes table rights, the value for Xr$Column is null.

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.

Table 146

Xr$Rights System Table Bit Position Definitions

Decimal Equivalent

Reorganization in progress.

References rights to table.

Alter Table rights.

Select rights to table or column.

Update rights to table or column.

Insert rights to table or column.

Delete rights to table or column.

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:

Table 147

X$Rights System Table Index Definitions

Case Insensitive

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:

Table 148

X$Trigger System Table Structure

Case Insensitive

Trigger version. A 4 indicates Scalable SQL v4.

File on which trigger is defined. Corresponds to Xf$Id in X$File.

0 for INSERT, 1 for DELETE, 2 for UPDATE.

0 for BEFORE, 1 for AFTER.

0 for ROW (default), 1 for STATEMENT.

Order of execution of trigger.

0-based sequence number.

LONGVARCHAR
(LVAR)

Internal representation of trigger.

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:

Table 149

X$Trigger System Table Index Definitions

Case Insensitive

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:

Table 150

X$User System Table Structure

Case Insensitive

Internal ID assigned to the user or group.

User or group name.

User password (encrypted)

User or group flags.

Note: For any row in the X$User system table that describes a group, the column value for Xu$Password is NULL.

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.

Table 151

Xu$Flags System Table Bit Position Definitions

Decimal Equivalent

Name is a group name.

User or group has the right to define tables in the dictionary.

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:

Table 152

X$User System Table Index Definitions

Case Insensitive

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:

Table 153

X$View System Table Structure

Case Insensitive

Version ID. This is reserved for future use.

Sequence number.

LONGVARCHAR
(LVAR)

PSQL internal definitions.

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

Table 154

X$View System Table Index Definitions

Case Insensitive

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

V2 Metadata System Tables

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:

Table 155

X$Attrib System Table Structure for V2 Metadata

Case Insensitive

Corresponds to Xe$Id in X$Field

L (logical positioning)

O (column collation)

Length of text in Xa$Attrs

LONGVARCHAR
(NOTE)

Text that defines the column attribute

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:

Table 156

X$Attrib System Table Index Definitions for V2 Metadata

Case Insensitive

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:

Table 157

X$Depend System Table Structure for V2 Metadata

Case Insensitive

Name of trigger. It corresponds to Xt$Name in X$Trigger.

1 for Table, 2 for View, 3 for Procedure.

Name of dependency with which the trigger is associated. It corresponds to either Xf$Name in X$File, Xv$Name in X$View, or Xp$Name in X$Proc.

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

Table 158

X$Depend System Table Index Definitions for V2 Metadata

Case Insensitive

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:

Table 159

X$Field System Table Structure for V2 Metadata

Case Insensitive

Internal ID PSQL assigns, unique for each field in the database.

ID of table to which this column or named index belongs. It corresponds to Xf$Id in X$File.

Column name or index name.

0 through 26: column data type
227: constraint name
255: index name

Column offset in table; index number if named index. Offsets are zero-relative.

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

Column size, representing the internal storage, in bytes, required for the field.

Column decimal place (for DECIMAL, NUMERIC, NUMERICSA, NUMERICSTS, MONEY, or CURRENCY types). Relative bit positions for contiguous bit columns. Fractional seconds for TIMESTAMP data type.

Bit 0 is the case flag for string data types.

If bit 0 = 1, the field is case insensitive.

If bit 2 = 1, the field allows null values.

Bit 3 of Xe$flag is used to differentiate a Pervasive.SQL v7 1-byte TINYINT (B_TYPE_INTEGER unsigned) from Relational Engine's 1-byte TINYINT (B_TYPE_INTEGER, but signed).

If bit 3 = 1 and Xe$datatype = 1 and Xe$size =1, then it means that TINYINT column is created by the Relational Engine and is a signed 1-byte TINYINT.

If bit 3 = 0 and Xe$datatype = 1 and xe$size = 1 then it means that TINYINT column is created by the legacy SQL engine and is an unsigned 1-byte TINYINT.

If bit 11 = 1, the field is interpreted as a wide character NLONGVARCHAR field rather than a character LONGVARCHAR field.

If bit 12 = 1, the field is interpreted as BINARY.

If bit 13 = 1, the field is interpreted as DECIMAL with even-byte precision.

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:

SELECT "X$Field".*

FROM X$File,X$Field

WHERE Xf$Id=Xe$File AND Xf$Name = 'Billing' AND Xe$DataType <= 26

ORDER BY Xe$Offset

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:

Table 160

X$Field System Table Index Definitions for V2 Metadata

Case Insensitive

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 as follows:

Table 161

X$File System Table Structure for V2 Metadata

Case Insensitive

Internal ID PSQL assigns.

File location (path name).

File flags. If bit 4 = 1, the file is a dictionary file. If bit 4 = 0, the file is user-defined. If bit 6=1, the table supports true nullable columns.

Two indexes are defined for the X$File table.

Table 162

X$File System Table Index Definitions for V2 Metadata

Case Insensitive

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:

Table 163

X$Index System Table Structure for V2 Metadata

Case Insensitive

Unique ID of the table to which the index belongs. It corresponds to Xf$Id in X$File.

Unique ID of the index column. It corresponds to Xe$Id in X$Field.

Index number (range 0 – 119).

Segment number (range 0 – 119).

Index attribute flags.

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.

Table 164

Xi$Flags Bit Positions for V2 Metadata

Decimal Equivalent

Index allows duplicates.

Index is modifiable.

Indicates an alternate collating sequence.

Null values are not indexed (refers to Btrieve NULLs, not SQL true NULLS).

Another segment is concatenated to this one in the index.

Index is case-insensitive.

Index is collated in descending order.

Index is a named index if bit 0 is 0. If bit 0 is 1 and bit 7 is 1, the index uses the repeating duplicates key method. If bit 0 is 1 and bit 7 is 0, the index uses the linked duplicates key method. See also “LINKDUP.” For a detailed discussion of linked duplicates method and repeating duplicates method, see Methods for Handling Duplicate Keys in Advanced Operations Guide.)

Index is a Btrieve extended key type.

Index is a foreign key.

Index is a primary key referenced by some foreign key.

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

Table 165

X$Index System Table Index Definitions for V2 Metadata

Case Insensitive

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:

SELECT Xe$Name,Xe$Offset, "X$Index".*

FROM X$File,X$Index,X$Field

WHERE Xf$Id=Xi$File and Xi$Field=Xe$Id and Xf$Name = 'Billing'

ORDER BY Xi$Number,Xi$Part

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:

Table 166

X$Proc System Table Structure for V2 Metadata

Case Insensitive

Stored procedure name

Version ID. This is reserved for future use.

Internal ID assigned by PSQL

1 for stored statement, 2 for stored procedure or 3 for external procedure

0 for a trusted stored procedure and -1 for a non-trusted stored procedure. See Trusted and Non-Trusted Objects.

A sequence number. A procedure that exceeds 32,765 bytes requires multiple entries in PVPROC.DDF to handle the overflow. Each entry has the same procedure name in the Xp$Name field and is assigned a sequence number. The Xp$Sequence field is used to correctly order the multiple entries.

The sequence starts at zero (the first sequence number is zero).

LONGVARCHAR
(LVAR)

Internal representation of stored procedure

Note: Stored statements and external procedures were supported in versions of PSQL prior to Pervasive.SQL 2000i. Only stored procedures have been supported since Pervasive.SQL 2000i.

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

Table 167

X$Proc System Table Index Definitions for V2 Metadata

Case Insensitive

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:

Table 168

X$Relate System Table Structure for V2 Metadata

Case Insensitive

Primary table ID.

Index number of primary key in primary table.

Dependent table ID.

Index number of foreign key in dependent table.

Foreign key name.

1 for restrict.

1 for restrict, 2 for cascade.

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

Table 169

X$Relate System Table Index Definitions for V2 Metadata

Case Insensitive

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:

Table 170

X$Rights System Table Structure for V2 Metadata

Case Insensitive

Table identification corresponding to Xf$Id, view identification corresponding to Xv$Id or stored procedure identification corresponding to Xp$Id

1 for Tables, 3 for Procedures and 4 for Views

Rights flag for table, column, views or stored procedures

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:

•

Xf$Id column in the X$File table

•

Xv$Id column in X$Views table

•

Xp$Id column in X$Proc table.

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

Note: For any row in the system table that describes table rights, view rights, or stored procedure rights, the value for Xr$Column is null.

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.

Table 171

Xr$Rights Bit Positions for V2 Metadata

Decimal Equivalent

Object owner right

References rights to table

Alter table rights

Select rights to view, table or column

Update rights to view, table or column

Insert rights to view, table or column

Delete rights to table or column

Execute and call rights to a stored procedure

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:

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

Table 172

X$Rights System Table Index Definitions for V2 Metadata

Case Insensitive

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:

Table 173

X$Trigger System Table Structure for V2 Metadata

Case Insensitive

Trigger version. A 4 indicates Scalable SQL v4.

File on which trigger is defined. Corresponds to Xf$Id in X$File.

0 for INSERT, 1 for DELETE, 2 for UPDATE.

0 for BEFORE, 1 for AFTER.

0 for ROW (default), 1 for STATEMENT.

Order of execution of trigger.

A sequence number. A trigger that exceeds 4,054 bytes requires multiple entries in PVTRIG.DDF to handle the overflow. Each entry has the same procedure name in the Xt$Name field and is assigned a sequence number. The Xt$Sequence field is used to correctly order the multiple entries.

The sequence starts at zero (the first sequence number is zero).

LONGVARCHAR
(LVAR)

Internal representation of trigger.

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

Table 174

X$Trigger System Table Index Definitions for V2 Metadata

Case Insensitive

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:

Table 175

X$User System Table Structure for V2 Metadata

Case Insensitive

Internal ID assigned to the user or group.

User or group name.

User password (encrypted)

User or group flags.

Note: For any row in the X$User system table that describes a group, the column value for Xu$Password is NULL.

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.

Table 176

Xu$Flags Bit Positions for V2 Metadata

Decimal Equivalent

Name is a group name.

User or group has the right to define tables in the dictionary

User or group has the right to define view in the dictionary

User or group has the right to define stored procedures in the dictionary

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:

Table 177

X$User System Table Index Definitions for V2 Metadata

Case Insensitive

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:

Table 178

X$View System Table Structure for V2 Metadata

Case Insensitive

Version ID. This is reserved for future use.

Internal ID assigned by PSQL

0 for a trusted view and -1 for a non-trusted view. See Trusted and Non-Trusted Objects.

A sequence number. A view that exceeds 32,765bytes requires multiple entries in PVVIEW.DDF to handle the overflow. Each entry has the same view name in the Xv$Name field and is assigned a sequence number. The Xv$Sequence field is used to correctly order the multiple entries.

The sequence starts at zero (the first sequence number is zero).

LONGVARCHAR
(LVAR)

PSQL internal definitions.

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

Table 179

X$View System Table Index Definitions

Case Insensitive