Developer Reference : Data Access Methods : SQL Engine Reference : System Tables
 
System Tables
Zen System Tables Reference
The following topics cover Zen system tables:
•Overview
•System Tables Structure
Overview
The information used by Zen and its components is stored in special tables called system tables.
Caution Do not attempt to modify system tables with DELETE, UPDATE, or INSERT statements, or user-defined triggers. System tables should never be altered directly.
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:
•System Stored Procedures
•Transact-SQL statements and functions
•Functions provided in the Zen APIs
The Zen APIs are documented in the developer documentation. 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 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 Zen changes.
The following list of system tables gives the names of associated files and identifies system table contents.
Note Some data in the system tables cannot be displayed. User passwords, for example, are displayed in their encrypted form.
 
Table 126 System Tables 
System Table
Dictionary File
Contents
V11
V22
X$Attrib
ATTRIB.DDF
PVATTRIB.DDF
Column attributes definitions.
X$Depend
DEPEND.DDF
PVDEPEND.DDF
Trigger dependencies such as tables, views, and procedures
X$Field
FIELD.DDF
PVFIELD.DDF
Column and named index definitions.
X$File
FILE.DDF
PVFILE.DDF
Names and locations of the tables in your database.
X$Index
INDEX.DDF
PVINDEX.DDF
Index definitions.
X$Proc
PROC.DDF
PVPROC.DDF
Stored procedure definitions.
X$Relate
RELATE.DDF
PVRELATE.DDF
Referential integrity (RI) information.
X$Rights
RIGHTS.DDF
PVRIGHTS.DDF
User and group access rights definitions.
X$Trigger
TRIGGER.DDF
PVTRIG.DDF
Trigger information.
X$User
USER.DDF
PVUSER.DDF
User names, group names, and passwords.
X$View
VIEW.DDF
PVVIEW.DDF
View definitions.
1Applies to version 1 (V1) metadata. See Zen Metadata.
2Applies to version 2 (V2) metadata. See Zen Metadata.
Zen 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.
System Tables Structure
This topic discusses the structure of the system tables:
•V1 Metadata System Tables
•V2 Metadata System Tables
V1 Metadata System Tables
X$Attrib
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 127 X$Attrib System Table Structure
Column Name
Type
Size
Case Insensitive
Description
Xa$Id
USMALLINT
2
not applicable
Corresponds to Xe$Id in X$Field.
Xa$Type
CHAR
1
No
D (default)
L (logical positioning)
O (column collation)
C (character); H (heading); M (mask); R (range); or V (value)1
Xa$ASize
USMALLINT
2
not applicable
Length of text in Xa$Attrs.
Xa$Attrs
LONGVARCHAR
(NOTE)
<=2048
not applicable
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. Zen 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 Zen. One index is defined for the X$Attrib table, as explained in X$Attrib System Table Index Definitions:
Table 128 X$Attrib System Table Index Definitions
Index Number
Segment Number
Column Name
Duplicates
Case Insensitive
Segmented
0
0
Xa$Id
No
not applicable
Yes
0
1
Xa$Type
No
No
No
X$Depend
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 129 X$Depend System Table Structure
Column Name
Type
Size
Case Insensitive
Description
Xd$Trigger
CHAR
30
Yes
Name of trigger. It corresponds to Xt$Name in X$Trigger.
Xd$DependType
UNSIGNED
1
not applicable
1 for Table, 2 for View, 3 for Procedure.
Xd$DependName
CHAR
30
Yes
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 130 X$Depend System Table Index Definitions
Index Number
Segment Number
Column Name
Duplicates
Case Insensitive
Segmented
0
0
Xd$Trigger
No
Yes
Yes
0
1
Xd$DependType
No
not applicable
Yes
0
2
Xd$DependName
No
Yes
No
1
0
Xd$DependType
Yes
not applicable
Yes
1
1
Xd$DependName
Yes
Yes
No
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.
X$Field
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 131 X$Field System Table Structure  
Column Name
Type
Size
Case Insensitive
Description
Xe$Id
USMALLINT
2
not applicable
Internal ID assigned by Zen, unique for each field in the database
Xe$File
USMALLINT
2
not applicable
ID of table to which this column or named index belongs. It corresponds to Xf$Id in X$File.
Xe$Name
CHAR
20
Yes
Column name or index name
Xe$DataType
UTINYINT
1
not applicable
Control field:
0 through 26: column data type
227: constraint name
255: index name
Xe$Offset
USMALLINT
2
not applicable
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.
Xe$Size
USMALLINT
2
not applicable
Column size, representing the internal storage, in bytes, required for the field.
Size does not include the NULL byte for TRUE NULL fields.
Xe$Dec
UTINYINT
1
not applicable
Column decimal place (for DECIMAL, NUMERIC, NUMERICSA, NUMERICSTS, MONEY, or CURRENCY types). Relative bit positions for contiguous bit columns. Fractional seconds for AUTOTIMESTAMP, TIMESTAMP, and TIMESTAMP2 data types.
Xe$Flags
USMALLINT
2
not applicable
Flags word.
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 Zen data types. See Zen Supported Data Types for the codes.
Five indexes are defined for the X$Field table as follows:
Table 132 X$Field System Table Index Definitions
Index Number
Segment Number
Column Name
Duplicates
Case Insensitive
Segmented
0
0
Xe$Id
No
not applicable
No
1
0
Xe$File
Yes
not applicable
No
2
0
Xe$Name
Yes
Yes
No
3
0
Xe$File
No
not applicable
Yes
3
1
Xe$Name
No
Yes
No
4
0
Xe$File
Yes
not applicable
Yes
4
1
Xe$Offset
Yes
not applicable
Yes
4
2
Xe$Dec
Yes
not applicable
No
X$File
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 Zen assigns. The structure of X$File is as follows:
Table 133 X$File System Table Structure
Column Name
Type
Size
Case Insensitive
Description
Xf$Id
USMALLINT
2
not applicable
Internal ID assigned by Zen
Xf$Name
CHAR
20
Yes
Table name
Xf$Loc
CHAR
64
No
File location (path name)
Xf$Flags
UTINYINT
1
not applicable
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.
Xf$Reserved
CHAR
10
No
Reserved
Two indexes are defined for the X$File table.
Table 134 X$File System Table Index Definitions
Index Number
Segment Number
Column Name
Duplicates
Case Insensitive
Segmented
0
0
Xf$Id
No
not applicable
No
1
0
Xf$Name
No
Yes
No
X$Index
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 135 X$Index System Table Structure
Column Name
Type
Size
Case Insensitive
Description
Xi$File
USMALLINT
2
not applicable
Unique ID of the table to which the index belongs. It corresponds to Xf$Id in X$File.
Xi$Field
USMALLINT
2
not applicable
Unique ID of the index column. It corresponds to Xe$Id in X$Field.
Xi$Number
USMALLINT
2
not applicable
Index number (range 0 – 119).
Xi$Part
USMALLINT
2
not applicable
Segment number (range 0 – 119).
Xi$Flags
USMALLINT
2
not applicable
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 Zen interprets each bit position when the bit has the binary value of 1. Bit position 0 is the rightmost bit in the integer.
Table 136 Xi$Flags Bit Positions  
Bit Position
Decimal Equivalent
Description
0
1
Index allows duplicates.
1
2
Index is modifiable.
2
4
Indicates an alternate collating sequence.
3
8
Null values are not indexed (refers to Btrieve NULLs, not SQL true NULLS).
4
16
Another segment is concatenated to this one in the index.
5
32
Index is case-insensitive.
6
64
Index is collated in descending order.
7
128
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.
8
256
Index is a Btrieve extended key type.
9
512
Index is partial.
13
8192
Index is a foreign key.
14
16384
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 the index attributes. Three indexes are defined for the X$Index table as follows:
Table 137 X$Index System Table Index Definitions
Index Number
Segment Number
Column Name
Duplicates
Case Insensitive
Segmented
0
0
Xi$File
Yes
not applicable
No
1
0
Xi$Field
Yes
not applicable
No
2
0
Xi$File
No
not applicable
Yes
2
1
Xi$Number
No
not applicable
Yes
2
2
Xi$Part
No
not applicable
No
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
X$Proc
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 138 X$Proc System Table Structure
Column Name
Type
Size
Case Insensitive
Description
Xp$Name
CHAR
30
Yes
Stored procedure name.
Xp$Ver
UTINYINT
1
not applicable
Version ID. This is reserved for future use.
Xp$Id
USMALLINT
2
not applicable
0-based Sequence Number.
Xp$Flags
UTINYINT
1
not applicable
1 for stored statement, 2 for stored procedure or 3 for external procedure.
Xp$Misc
LONGVARCHAR
(LVAR)
<=990
not applicable
Internal representation of stored procedure.
Note Stored statements and external procedures were supported in versions 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 139 X$Proc System Table Index Definitions
Index Number
Segment Number
Column Name
Duplicates
Case Insensitive
Segmented
0
0
Xp$Name
No
Yes
Yes
0
1
Xp$Id
No
not applicable
No
A single stored procedure may be stored in multiple entries in X$Proc, linked by Xp$Name.
X$Relate
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 140 X$Relate System Table Structure
Column Name
Type
Size
Case Insensitive
Description
Xr$PId
USMALLINT
2
not applicable
Primary table ID.
Xr$Index
USMALLINT
2
not applicable
Index number of primary key in primary table.
Xr$FId
USMALLINT
2
not applicable
Dependent table ID.
Xr$FIndex
USMALLINT
2
not applicable
Index number of foreign key in dependent table.
Xr$Name
CHAR
20
Yes
Foreign key name.
Xr$UpdateRule
UTINYINT
1
not applicable
1 for restrict.
Xr$DeleteRule
UTINYINT
1
not applicable
1 for restrict, 2 for cascade.
Xr$Reserved
CHAR
30
No
Reserved.
Five indexes are defined for the X$Relate table as follows:
Table 141 X$Relate System Table Index Definitions
Index Number
Segment Number
Column Name
Duplicates
Case Insensitive
Segmented
0
0
Xr$PId
Yes
not applicable
No
1
0
Xr$FId
Yes
not applicable
No
2
0
Xr$Name
No
Yes
No
3
0
Xr$Pld
No
not applicable
Yes
3
1
Xr$Name
No
Yes
No
4
0
Xr$Fld
No
not applicable
Yes
4
1
Xr$Name
No
Yes
No
X$Rights
The X$Rights system table is associated with the file RIGHTS.DDF. X$Rights contains access rights information for each user. Zen uses this table only when you enable the security option. The structure of X$Rights is as follows:
Table 142 X$Rights System Table Structure
Column Name
Type
Size
Case Insensitive
Description
Xr$User
USMALLINT
2
not applicable
User ID
Xr$Table
USMALLINT
2
not applicable
Table ID
Xr$Column
USMALLINT
2
not applicable
Column ID
Xr$Rights
UTINYINT
1
not applicable
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 Zen interprets the value. Values from this table may be combined into a single Xr$Rights value.
 
Table 143 Xr$Rights System Table Bit Position Definitions
Hex Value
Decimal Equivalent
Description
1
1
Reorganization in progress.
0x90
144
References rights to table.
0xA0
160
Alter Table rights.
0x40
64
Select rights to table or column.
0x82
130
Update rights to table or column.
0x84
132
Insert rights to table or column.
0x88
136
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 144 X$Rights System Table Index Definitions
Index Number
Segment Number
Column Name
Duplicates
Case Insensitive
Segmented
0
0
Xr$User
Yes
not applicable
No
1
0
Xr$User
No
not applicable
Yes
1
1
Xr$Table
No
not applicable
Yes
1
2
Xr$Column
No
not applicable
No
2
0
Xr$Table
Yes
not applicable
Yes
2
1
Xr$Column
Yes
not applicable
No
X$Trigger
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 145 X$Trigger System Table Structure
Column Name
Type
Size
Case Insensitive
Description
Xt$Name
CHAR
30
Yes
Trigger name.
Xt$Version
USMALLINT
2
not applicable
Trigger version. A 4 indicates Scalable SQL v4.
Xt$File
USMALLINT
2
not applicable
File on which trigger is defined. Corresponds to Xf$Id in X$File.
Xt$Event
UNSIGNED
1
not applicable
0 for INSERT, 1 for DELETE, 2 for UPDATE.
Xt$ActionTime
UTINYINT
1
not applicable
0 for BEFORE, 1 for AFTER.
Xt$ForEach
UTINYINT
1
not applicable
0 for ROW (default), 1 for STATEMENT.
Xt$Order
USMALLINT
2
not applicable
Order of execution of trigger.
Xt$Sequence
USMALLINT
2
not applicable
0-based sequence number.
Xt$Misc
LONGVARCHAR
(LVAR)
<=4054
not applicable
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 146 X$Trigger System Table Index Definitions  
Index Number
Segment Number
Column Name
Duplicates
Case Insensitive
Segmented
0
0
Xt$Name
No
Yes
Yes
0
1
Xt$Sequence
No
not applicable
No
1
0
Xt$File
No
not applicable
Yes
1
1
Xt$Name
No
Yes
Yes
1
2
Xt$Sequence
No
not applicable
No
2
0
Xt$File
Yes
not applicable
Yes
2
1
Xt$Event
Yes
not applicable
Yes
2
2
Xt$ActionTime
Yes
not applicable
Yes
2
3
Xt$ForEach
Yes
not applicable
Yes
2
4
Xt$Order
Yes
not applicable
Yes
2
5
Xt$Sequence
Yes
not applicable
No
The trigger may be stored in more than one entry in X$Trigger, linked by Xt$Name and ordered by Xt$Sequence.
X$User
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. Zen uses this table only when you enable the security option. The following table shows the structure of X$User.
Table 147 X$User System Table Structure
Column Name
Type
Size
Case Insensitive
Description
Xu$Id
USMALLINT
2
not applicable
Internal ID assigned to the user or group.
Xu$Name
CHAR
30
Yes
User or group name.
Xu$Password
CHAR
9
No
User password (encrypted)
Xu$Flags
USMALLINT
2
not applicable
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 Zen interprets each bit position when the bit has the binary value of 1. Bit position 0 is the rightmost bit in the integer.
Table 148 Xu$Flags System Table Bit Position Definitions
Bit Position
Decimal Equivalent
Description
0
1
Reserved.
1
2
Reserved.
2
4
Reserved.
3
8
Reserved.
4
16
Reserved.
5
32
Reserved.
6
64
Name is a group name.
7
128
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 shown in the following table.
Table 149 X$User System Table Index Definitions
Index Number
Segment Number
Column Name
Duplicates
Case Insensitive
Segmented
0
0
Xu$Id
Yes
not applicable
No
1
0
Xu$Name
No
Yes
No
X$View
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 150 X$View System Table Structure
Column Name
Type
Size
Case Insensitive
Description
Xv$Name
CHAR
20
Yes
View name.
Xv$Ver
UTINYINT
1
not applicable
Version ID. This is reserved for future use.
Xv$Id
UTINYINT
1
not applicable
Sequence number.
Xv$Misc
LONGVARCHAR
(LVAR)
<=2000
not applicable
Zen internal definitions.
Two indexes are defined for the X$View table as follows:
Table 151 X$View System Table Index Definitions
Index Number
Segment Number
Column Name
Duplicates
Case Insensitive
Segmented
0
0
Xv$Name
Yes
Yes
No
1
0
Xv$Name
No
Yes
Yes
1
1
Xv$Ver
No
not applicable
Yes
1
2
Xv$Id
No
not applicable
No
A single view may be stored in multiple X$View entries, linked by Xv$Name and ordered by Xv$Id.
V2 Metadata System Tables
X$Attrib
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 following table shows the structure of X$Attrib.
Table 152 X$Attrib System Table Structure for V2 Metadata
Column Name
Type
Size
Case Insensitive
Description
Xa$Id
UINTEGER
4
not applicable
Corresponds to Xe$Id in X$Field
Xa$Type
CHAR
4
No
D (default)
L (logical positioning)
O (column collation)
Xa$ASize
USMALLINT
2
Not applicable
Length of text in Xa$Attrs
Xa$Attrs
LONGVARCHAR
(NOTE)
32,763
not applicable
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 Zen. One index is defined for the X$Attrib table as shown in the next table.
Table 153 X$Attrib System Table Index Definitions for V2 Metadata
Index Number
Segment Number
Column Name
Duplicates
Case Insensitive
Segmented
0
0
Xa$Id
No
not applicable
Yes
0
1
Xa$Type
No
No
No
X$Depend
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 154 X$Depend System Table Structure for V2 Metadata
Column Name
Type
Size
Case Insensitive
Description
Xd$Trigger
CHAR
128
Yes
Name of trigger. It corresponds to Xt$Name in X$Trigger.
Xd$DependType
UTINYINT
1
not applicable
1 for Table, 2 for View, 3 for Procedure.
Xd$DependName
CHAR
128
Yes
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 155 X$Depend System Table Index Definitions for V2 Metadata
Index Number
Segment Number
Column Name
Duplicates
Case Insensitive
Segmented
0
0
Xd$Trigger
No
Yes
Yes
0
1
Xd$DependType
No
not applicable
No
1
0
Xd$DependType
Yes
not applicable
Yes
1
1
Xd$DependName
Yes
Yes
No
X$Field
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 156 X$Field System Table Structure for V2 Metadata  
Column Name
Type
Size
Case Insensitive
Description
Xe$Id
UINTEGER
4
not applicable
Internal ID assigned by Zen, unique for each field in the database.
Xe$File
UINTEGER
4
not applicable
ID of table to which this column or named index belongs. It corresponds to Xf$Id in X$File.
Xe$Name
CHAR
128
Yes
Column name or index name.
Xe$Datatype
UTINYINT
1
not applicable
0 through 26: column data type
227: constraint name
255: index name
Xe$Offset
UINTEGER
4
not applicable
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.
Xe$Size
UINTEGER
4
not applicable
Column size, representing the internal storage, in bytes, required for the field.
Xe$Dec
USMALLINT
2
not applicable
Column decimal place (for DECIMAL, NUMERIC, NUMERICSA, NUMERICSTS, MONEY, or CURRENCY types). Relative bit positions for contiguous bit columns. Fractional seconds for AUTOTIMESTAMP, TIMESTAMP, and TIMESTAMP2 data types.
Xe$Flags
UINTEGER
4
not applicable
Flags word.
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 Zen data types. See Zen Supported Data Types for the codes.
Five indexes are defined for the X$Field table, as shown in the following table.
Table 157 X$Field System Table Index Definitions for V2 Metadata
Index Number
Segment Number
Column Name
Duplicates
Case Insensitive
Segmented
0
0
Xe$Id
No
not applicable
No
1
0
Xe$File
Yes
not applicable
No
2
0
Xe$Name
Yes
Yes
No
3
0
Xe$File
No
not applicable
Yes
3
1
Xe$Name
No
Yes
No
4
0
Xe$File
Yes
not applicable
Yes
4
1
Xe$Offset
Yes
not applicable
Yes
4
2
Xe$Dec
Yes
not applicable
No
X$File
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 Zen assigns. The structure of X$File is shown in the following table.
Table 158 X$File System Table Structure for V2 Metadata
Column Name
Type
Size
Case Insensitive
Description
Xf$Id
UINTEGER
4
not applicable
Internal ID assigned by Zen
Xf$Name
CHAR
128
Yes
Table name
Xf$Loc
CHAR
250
No
File location (path name)
Xf$Flags
UINTEGER
4
not applicable
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.
Xf$Reserved
CHAR
16
No
Reserved
Two indexes are defined for the X$File table.
Table 159 X$File System Table Index Definitions for V2 Metadata
Index Number
Segment Number
Column Name
Duplicates
Case Insensitive
Segmented
0
0
Xf$Id
No
not applicable
No
1
0
Xf$Name
No
Yes
No
X$Index
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 160 X$Index System Table Structure for V2 Metadata
Column Name
Type
Size
Case Insensitive
Description
Xi$File
UINTEGER
4
not applicable
Unique ID of the table to which the index belongs. It corresponds to Xf$Id in X$File.
Xi$Field
UINTEGER
4
not applicable
Unique ID of the index column. It corresponds to Xe$Id in X$Field.
Xi$Number
UINTEGER
4
not applicable
Index number (range 0 – 119).
Xi$Part
UINTEGER
4
not applicable
Segment number (range 0 – 119).
Xi$Flags
UINTEGER
4
not applicable
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 Zen interprets each bit position when the bit has the binary value of 1. Bit position 0 is the rightmost bit in the integer.
Table 161 Xi$Flags Bit Positions for V2 Metadata
Bit Position
Decimal Equivalent
Description
0
1
Index allows duplicates.
1
2
Index is modifiable.
2
4
Indicates an alternate collating sequence.
3
8
Null values are not indexed (refers to Btrieve legacy nulls, not SQL true NULLs).
4
16
Another segment is concatenated to this one in the index.
5
32
Index is case-insensitive.
6
64
Index is collated in descending order.
7
128
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.
8
256
Index is a Btrieve extended key type.
13
8,192
Index is a foreign key.
14
16,384
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 the index attributes. Three indexes are defined for the X$Index table as follows:
Table 162 X$Index System Table Index Definitions for V2 Metadata
Index Number
Segment Number
Column Name
Duplicates
Case Insensitive
Segmented
0
0
Xi$File
Yes
not applicable
No
1
0
Xi$Field
Yes
not applicable
No
2
0
Xi$File
No
not applicable
Yes
2
1
Xi$Number
No
not applicable
Yes
2
2
Xi$Part
No
not applicable
No
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
X$Proc
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 163 X$Proc System Table Structure for V2 Metadata
Column Name
Type
Size
Case Insensitive
Description
Xp$Name
CHAR
128
Yes
Stored procedure name
Xp$Ver
UTINYINT
1
not applicable
Version ID. This is reserved for future use.
Xp$Id
UINTEGER
4
not applicable
Internal ID assigned by Zen
Xp$Flags
UINTEGER
4
not applicable
1 for stored statement, 2 for stored procedure or 3 for external procedure
Xp$Trustee
INTEGER
4
not applicable
0 for a trusted stored procedure and -1 for a non-trusted stored procedure. See Trusted and Non-Trusted Objects.
Xp$Sequence
USMALLINT
2
not applicable
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).
Xp$Misc
LONGVARCHAR
(LVAR)
32,765
not applicable
Internal representation of stored procedure
Note Stored statements and external procedures were supported in versions of Zen 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 164 X$Proc System Table Index Definitions for V2 Metadata
Index Number
Segment Number
Column Name
Duplicates
Case Insensitive
Segmented
0
0
Xp$Name
Yes
Yes
No
1
0
Xp$Name
No
Yes
Yes
1
1
Xp$Ver
No
not applicable
Yes
1
2
Xp$Sequence
No
not applicable
No
2
0
Xp$Id
Yes
not applicable
No
3
0
Xp$Id
No
not applicable
Yes
3
1
Xp$sequence
No
not applicable
No
X$Relate
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 165 X$Relate System Table Structure for V2 Metadata
Column Name
Type
Size
Case Insensitive
Description
Xr$PId
UINTEGER
4
not applicable
Primary table ID.
Xr$Index
UINTEGER
4
not applicable
Index number of primary key in primary table.
Xr$FId
UINTEGER
4
not applicable
Dependent table ID.
Xr$FIndex
UINTEGER
4
not applicable
Index number of foreign key in dependent table.
Xr$Name
CHAR
128
Yes
Foreign key name.
Xr$UpdateRule
UTINYINT
1
not applicable
1 for restrict.
Xr$DeleteRule
UTINYINT
1
not applicable
1 for restrict, 2 for cascade.
Xr$Reserved
CHAR
250
No
Reserved.
Five indexes are defined for the X$Relate table as follows:
Table 166 X$Relate System Table Index Definitions for V2 Metadata
Index Number
Segment Number
Column Name
Duplicates
Modifiable
Case Insensitive
Segmented
0
0
Xr$PId
Yes
No
not applicable
No
1
0
Xr$FId
Yes
No
not applicable
No
2
0
Xr$Name
No
No
Yes
No
3
0
Xr$Pld
No
Yes
not applicable
Yes
3
1
Xr$Name
No
Yes
Yes
No
4
0
Xr$Fld
No
Yes
not applicable
Yes
4
1
Xr$Name
No
Yes
Yes
No
X$Rights
The X$Rights system table is associated with the file PVRIGHTS.DDF. X$Rights contains access rights information for each user. Zen uses this table only when you enable the security option. The structure of X$Rights is as follows:
Table 167 X$Rights System Table Structure for V2 Metadata
Column Name
Type
Size
Case Insensitive
Description
Xr$User
UINTEGER
4
not applicable
User ID
Xr$Object
UINTEGER
4
not applicable
Table identification corresponding to Xf$Id, view identification corresponding to Xv$Id or stored procedure identification corresponding to Xp$Id
Xr$Type
UINTEGER
4
not applicable
1 for Tables, 3 for Procedures and 4 for Views
Xr$Column
UINTEGER
4
not applicable
Column ID
Xr$Rights
UINTEGER
4
not applicable
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 Zen interprets the value. Values from this table may be combined into a single Xr$Rights value.
Table 168 Xr$Rights Bit Positions for V2 Metadata
Hex Value
Decimal Equivalent
Description
1
1
Object owner right
0x90
144
References rights to table
0xA0
160
Alter table rights
0x40
64
Select rights to view, table or column
0x82
130
Update rights to view, table or column
0x84
132
Insert rights to view, table or column
0x88
136
Delete rights to table or column
0xC0
192
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:
192 = 192
Three indexes are defined for the X$Rights table as follows:
Table 169 X$Rights System Table Index Definitions for V2 Metadata
Index Number
Segment Number
Column Name
Duplicates
Case Insensitive
Segmented
0
0
Xr$User
Yes
not applicable
No
1
0
Xr$User
No
not applicable
Yes
1
1
Xr$Object
No
not applicable
Yes
1
2
Xr$Type
No
not applicable
Yes
1
3
Xr$Column
No
not applicable
No
2
0
Xr$Object
Yes
not applicable
Yes
2
1
Xr$Type
Yes
not applicable
Yes
2
2
Xr$Column
Yes
not applicable
No
X$Trigger
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 170 X$Trigger System Table Structure for V2 Metadata
Column Name
Type
Size
Case Insensitive
Description
Xt$Name
CHAR
128
Yes
Trigger name.
Xt$Version
UTINYINT
1
not applicable
Trigger version. A 4 indicates Scalable SQL v4.
Xt$File
UINTEGER
4
not applicable
File on which trigger is defined. Corresponds to Xf$Id in X$File.
Xt$Event
UTINYINT
1
not applicable
0 for INSERT, 1 for DELETE, 2 for UPDATE.
Xt$ActionTime
UTINYINT
1
not applicable
0 for BEFORE, 1 for AFTER.
Xt$ForEach
UTINYINT
1
not applicable
0 for ROW (default), 1 for STATEMENT.
Xt$Order
USMALLINT
2
not applicable
Order of execution of trigger.
Xt$Sequence
USMALLINT
2
not applicable
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).
Xt$Misc
LONGVARCHAR
(LVAR)
4,054
not applicable
Internal representation of trigger.
Three indexes are defined for the X$Trigger table.
Table 171 X$Trigger System Table Index Definitions for V2 Metadata
Index Number
Segment Number
Column Name
Duplicates
Case Insensitive
Segmented
0
0
Xt$Name
No
Yes
Yes
0
1
Xt$Sequence
No
not applicable
No
1
0
Xt$Name
No
Yes
Yes
1
1
Xt$File
No
not applicable
Yes
1
2
Xt$Sequence
No
not applicable
No
2
0
Xt$File
Yes
not applicable
Yes
2
1
Xt$Event
Yes
not applicable
Yes
2
2
Xt$ActionTime
Yes
not applicable
Yes
2
3
Xt$ForEach
Yes
not applicable
Yes
2
4
Xt$Order
Yes
not applicable
Yes
2
5
Xt$Sequence
Yes
not applicable
No
X$User
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. Zen uses this table only when you enable the security option. The structure of X$User is as follows:
Table 172 X$User System Table Structure for V2 Metadata
Column Name
Type
Size
Case Insensitive
Description
Xu$Id
UINTEGER
4
not applicable
Internal ID assigned to the user or group.
Xu$Name
CHAR
128
Yes
User or group name.
Xu$Password
CHAR
153
No
User password (encrypted)
Xu$Flags
UINTEGER
4
not applicable
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 Zen interprets each bit position when the bit has the binary value of 1. Bit position 0 is the rightmost bit in the integer.
Table 173 Xu$Flags Bit Positions for V2 Metadata
Bit Position
Decimal Equivalent
Description
0
1
Reserved.
1
2
Reserved.
2
4
Reserved.
3
8
Reserved.
4
16
Reserved.
5
32
Reserved.
6
64
Name is a group name.
7
128
User or group has the right to define tables in the dictionary
8
256
User or group has the right to define view in the dictionary
9
512
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 174 X$User System Table Index Definitions for V2 Metadata
Index Number
Segment Number
Column Name
Duplicates
Case Insensitive
Segmented
0
0
Xu$Id
Yes
not applicable
No
1
0
Xu$Name
No
Yes
No
X$View
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 175 X$View System Table Structure for V2 Metadata
Column Name
Type
Size
Case Insensitive
Description
Xv$Name
CHAR
128
Yes
View name
Xv$Version
UTINYINT
1
not applicable
Version ID. Reserved for future use.
Xv$Id
UINTEGER
4
not applicable
Internal ID assigned by Zen
Xv$Trustee
INTEGER
4
not applicable
0 for a trusted view and -1 for a non-trusted view. See Trusted and Non-Trusted Objects.
Xv$Sequence
USMALLINT
2
not applicable
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).
Xv$Misc
LONGVARCHAR
(LVAR)
32,765
not applicable
Zen internal definitions.
Three indexes are defined for the X$View table as follows:
Table 176 X$View System Table Index Definitions
Index Number
Segment Number
Column Name
Duplicates
Case Insensitive
Segmented
0
0
Xv$Name
Yes
Yes
No
1
0
Xv$Name
No
Yes
Yes
1
1
Xv$Version
No
not applicable
Yes
1
2
Xv$Sequence
No
not applicable
No
2
0
Xv$Id
Yes
not applicable
No
3
0
Xv$Id
No
not applicable
Yes
3
1
Xv$Sequence
No
not applicable
No