Tables

Tables are the objects in which databases store data. Pervasive PSQL contains two types of tables: data and system. Data tables are user-created. Newly created ones are empty and must be populated with data. System tables are created and populated as required by the Pervasive PSQL database management system.

Refer to the chapter Table Editor for a detailed discussion of data tables. That chapter also contains the tasks pertaining to tables, such as creating one, deleting one, working with columns, foreign keys, and so forth.

Note: To create a table in a database, database security must be turned off or you must have access rights to create tables.

System tables appear in the Pervasive PSQL Explorer under the System Objects node. You may view properties of them as explained in To view properties of a table.

Table Properties

Table properties provides information about the table. Separate tabs let you view general properties, columns information, and indexes information. The following table describes the parameters listed on the General tab.

Table 11

Table Properties on General Tab

Shows the name of the table as it appears in the database definition.

Shows the physical location of the data file associated with the table.

Dictionary Path

Shows where the database's DDF files are located.

Shows the file format version of the data file.

Shows the length of the data file's records.

Shows the page size (in bytes) of the data file. The page size determines the maximum number of index segments that can be defined in a table.

Number or Records

Shows the number of records currently contained in the data file.

Number of Indexes

Shows the number of indexes defined for the table.

Number of Duplicate Pointers (Ptrs)

Shows the number of linked duplicate indexes that can be added.

Number of Unused Pages

Shows the number of pre-allocated pages available. If pre-allocation is enabled, the MicroKernel pre-allocates a specified number of pages when it creates the data file. Pre-allocation guarantees that disk space for the data file is available when the MicroKernel needs it.

Variable Records

Shows whether the data file contains variable-length records.

Variable Record Blank Truncation

Shows whether blank truncation is enabled. If it is, the MicroKernel truncates the blanks in variable-length records. Blank truncation is applicable only if the Variable Records statistic is Yes and Data Compression is set to No.

Record Compression

Shows whether record compression is enabled. If it is, the MicroKernel compresses each record it inserts into the data file. See Record and Page Compression in Advanced Operations Guide.

Shows the name of the key-only file for the table, if any. A key-only file contains no data records but serves as an index to other Btrieve files.

Index Balancing

Shows whether balanced indexing is enabled.

FreeSpace Threshold

Shows a percentage (5%, 10%, 20% or 30%) if the data file has a free space threshold. The database engine stores the variable-length portions of records on their own pages (called variable pages), separate from the fixed-length portions (which are stored on data pages).

The database engine uses the threshold to determine whether to add data to an existing variable page or to create a new one. A higher free space threshold reduces fragmentation of variable-length records across several pages but uses more disk space.

Uses Alternate Collating Sequence

Shows whether the table uses an alternate collating sequence for sorting.

System Data Key

Shows whether the data file has system data keys enabled.

Page Compression

Shows whether page compression is enabled. See Record and Page Compression in Advanced Operations Guide.

►

To view properties of a table

1

In Pervasive PSQL Explorer, expand the Tables node for a particular database.

If you are interested in system tables, expand the Tables node under System Objects.

2

Right-click on the desired table, and click Properties.

Tip: You can use the table properties to view a list of the indexed columns for the table.

►

To open a table

1

In Pervasive PSQL Explorer, expand the Tables node for a particular database.

If you are interested in system tables, expand the Tables node under System Objects.

2

Right-click on the desired table, and click Open or Open in New Window.

The open action performs a SELECT * from table_name query, where table_name is the name of the table that you selected.

►

To delete a table

1

In Pervasive PSQL Explorer, expand the Tables node for a particular database.

2

Right-click on the desired table, and click Delete.