The following tasks pertain to databases.
To create a new database
To modify properties of a database To delete a database
For conceptual information on named databases, see Pervasive PSQL Database Concepts in Advanced Operations Guide.
³
To create a new databaseNote On Linux, the owner of the directory where you want to create the database must be psql. If not, error message 7039:
Dictionary path is invalid results. Use the chown command to
change owner of the directory. For example, chown psql
directoryname.
1 In PCC Pervasive PSQL Explorer, right-click on the database engine for which you want the new database.
2 Click New Database.
The Create New Database dialog box appears (see Figure 23).
3 Provide a name for the database and a location (see Table 1, Identifier Restrictions by Identifier Type).
The name cannot be the same as an existing DSN.
Also, no two files can share the same file name and differ only in their file name extension if both files are in the same directory. For example, do not name a data file Invoice.btr and another one Invoice.mkd in the same directory. This restriction applies because the database engine uses the file name for various areas of functionality while ignoring the file name extension. Since only the file name is used to differentiate files, files that differ only in their file name extension look identical to the database engine.
4 Specify the additional options on the dialog as required for the database. See Table 9 for a discussion of the options.
Pervasive PSQL Database Tasks
³
To modify properties of a database1 In PCC Pervasive PSQL Explorer, right-click the database engine for which you want to modify the properties, then click
Properties.
2 On the Properties dialog, click the tree node for which you want to specify properties:
Directories General
Relational Constraints Security
3 Set the specific properties as required.
³
To delete a databaseYou cannot delete the database to which you are currently logged in. See To log out from a database engine in Pervasive PSQL User's
Guide.
If database security is set to “mixed” or “database,” you must first remove the security. You cannot delete a database if security on it is set to “mixed” or “database.” See To turn off security using Pervasive PSQL Explorer and To turn off security using SQL.
1 In PCC Pervasive PSQL Explorer, right-click on the database engine that you want to delete.
3 Click Yes to confirm the deletion.
If the setting Always remove associated DSN entries is turned
off, the Confirm DSN Removal dialog appears.
4 If you want to remove a DSN associated with the database, ensure that the DSN is check marked in the list.
Note that multiple DSNs can be associated with one database. In such cases, you may remove the DSNs that you want and retain the ones that you want.
5 Optionally, select the option Always remove associated DSN
entries if you want PCC to set DSN removal as the default action.
Note If you select the Always remove associated DSN entries option, PCC no longer prompts you for DSN removal when you delete a database. PCC will automatically remove all DSNs associated with the database being deleted.
If you decide later to turn off the option, in PCC click Window, expand the Pervasive node if it is not already expanded, then click General.
Tables
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.
Data Tables
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
System tables appear in the Pervasive PSQL Explorer under theSystem 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 10 Table Properties on General Tab
Parameter Description
Table Name Shows the name of the table as it appears in the database definition.
Table Location Shows the physical location of the data file associated with the table.
Dictionary Path Shows where the database's DDF files are located.
File Version Shows the file format version of the data file.
Record Length Shows the length of the data file's records.
Page Size 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 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.
Key Only File 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.
Table 10 Table Properties on General Tab continued
Tables
³
To view properties of a table1 In Pervasive PSQL Explorer, expand the Tables node.
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.
Data
The tables that you create with PCC are initially empty. You can add data to them through PCC or by importing data. PCC provides a wizard to export data and one to import data.