File Information Editor

This section provides general information about the File Information Editor with which you can create new files based on file and key specifications you construct. Because this Editor allows you to load information based on an existing file, it is also useful for viewing file and key specifications on existing data files. You can also create a new file based on the file and key specifications of an existing file (similar to the CLONE command for BUTIL, the command-line Maintenance utility).

Open the File Information Editor by clicking Options 4 Show Information Editor.

Figure 13-2 File Information Editor

File Information Editor Dialog Elements

At the top of the Editor, the following buttons appear:


Load Information	Loads information based on an existing file. When you load information, you are not editing the existing file. Instead, you are loading a copy of the information about that file.
Create File	Creates a new file based on current information in the dialog box.
Set To Defaults	Sets the controls to default values.
Description Comments	If you are creating a description file, allows you to enter notes about the file.
Help	Displays help for the File Information Editor dialog box.

Data File Info

The Data File Info area, also at the top of the File Information Editor, contains the following controls:


Owner Name	Provides a text box you can use to specify the owner name, if applicable, for an existing file.
Version	Earliest version of the database engine that can read all the attributes of the file. For example, if you created a file using the 9.5 database engine but did not use any attributes specific to 0.5, the Maintenance utility displays 9.0 as the version number. See File Version Notes for additional information about file format versions.
Total Records	Total number of records in the file.

File Specification

The File Specification area is in the middle of the File Information Editor. Table 13-2 describes the controls in this box.

Table 13-2 File Specification Controls
Control	Description	Range	Default
Record Length	Specifies the logical data record length (in bytes) of the fixed-length records in a file. For information about record length and overhead, see "Record Length" in Pervasive PSQL Programmer's Guide, which is part of the Pervasive PSQL software development kit (SDK).	Minimum is 4 bytes. Maximum is variable. If the record length specified exceeds the page size minus overhead, the database engine automatically tries the next available page size for the file format. If the record length exceeds the maximum page size minus overhead, the engine turns on record compression.	100
Page Size	Specifies the physical page size (in bytes) for the file.	512 - 4096 for file versions prior to 9.0 (a multiple of 512 bytes up to 4096) 512, 1024, 1536, 2048, 2560, 3072, 3584, 4096, or 8192 for file version 9.0. 1024, 2048, 4096, 8192, or 16384 for file versions 9.5 and newer.	4,096
# Keys	Indicates the number of distinct keys (as opposed to key segments) currently defined in the Editor. Reflects the number of keys in the Key list.	0 - 119	0
# Segments	Indicates the number of key segments currently defined in the Editor. Reflects the number of segments in the Segment list.	0 - 119 for file versions prior to 9.5. 0 - 420 for file versions 9.5 and newer. Note that, for all file versions, the maximum number of segments for the relational interface is 119.	0
Available Linked Keys	Specifies how many 8-byte place holders you want to reserve for future linked-duplicatable keys. If you are loading information based on an existing data file, this value reflects the number of place holders currently available in that file. (The number of originally reserved place holders is not stored in the file.)	0 - 119	3
Key-Only	Indicates whether the file is key-only. Not applicable if you turn Record Compression on, if you turn Variable Records on, or if you define more than one key for the file.	On or Off	Off
Balanced Indexing	Specifies that the file uses the balanced indexing method of managing key pages.	On or Off	Off
Pre-allocation	Specifies that the file uses preallocated pages.	On or Off	Off
# Pages	Specifies the number of pages you want preallocated when you create the file. Applicable only if Pre-allocation is turned on. If you are loading information based on an existing data file, this value reflects the number of unused, preallocated pages left in that file. (The number of originally preallocated pages is not stored in the file.)	1 - 65,535	0
Record Compression	Specifies that the file uses record compression. Not applicable for key-only files or files that use blank truncation. See also Record and Page Compression .	On or Off	Off
Page Compression	Specifies that the file uses page compression. See also Record and Page Compression .	On or Off	Off
Variable Records	Specifies that the file can contain variable-length records.	On or Off	Off
Blank Truncation	Specifies whether the file uses blank truncation on variable records to conserve disk space. Applicable only if Variable Records is turned on.	On or Off	Off
Include VATs	Specifies whether the file supports Variable-tail Allocation Tables for faster access to data in very long records. Applicable only if Variable Records is turned on.	On or Off	Off
% Free Space	Specifies the amount of unused space a file's variable pages must have available before the database engine creates a new variable page. Applicable only if Record Compression or Variable Records are turned on.	5, 10, 20, or 30	5

Key

At the bottom left in the dialog box is the Key group box. Table 13-3 describes the controls in this area. These controls are specific to the key highlighted in the Key list, not just to the current key segment. When you change the setting for one of these controls, the change affects all segments of the specified key.

Table 13-3 Key Controls
Control	Description	Default
Duplicates	Specifies that the key can have duplicate values (linked duplicates). See Methods for Handling Duplicate Keys .	On
Modifiable	Specifies that the key value can be modified after creation. Allowing modification of key values does not affect performance. Key pages are only updated if the actual key value changes, not if non-key fields in a particular record are changed.	On
Repeating Duplicates	Specifies that the database engine uses the repeating duplicates method of storing duplicate key values. See Methods for Handling Duplicate Keys .	Off
Sparse Key (Null Key)	A sparse key contains fewer key values than the number of record in the file. To specify which key values are excluded from the index, see the next two controls. Applicable only to keys that contain nullable segments.	Off
All Segments (Null)	Specifies that if all key segments in the record contain a null value, the database engine does not include that record in the index. Applicable only if Sparse Key (Null Key) is turned on. Equivalent to key flag 0x0008. Whether a segment is evaluated as null is determined solely by the null indicator segment for that field; the contents of the field are not evaluated.	Off
Any Segment (Manual)	Specifies that if one or more key segments contains a null value, the database engine does not include that record in the index. Applicable only if Sparse Key (Null Key) is turned on. Equivalent to key flag 0x0200. Whether a segment is evaluated as null is determined solely by the null indicator segment for that field; the contents of the field are not evaluated.	Off
ACS Information	Allows you to specify an alternate collating sequence (ACS) for the key. Applicable only if the Use ACS check box is selected for a segment of the key.	Off
Unique Values	Indicates the number of unique key values in the file. Applicable only if you are loading information based on an existing data file.	N/A

Key List and Segment List

At the bottom middle of the dialog box, the Key list shows the key numbers defined in a file. (For 6.x and later files, these key numbers do not have to be consecutive; they can have gaps between them.) The Maintenance utility displays the highlighted key's specifications in the Key box at the bottom left of the dialog box.

Also at the bottom middle of the dialog box, the Segment list shows the key segment numbers defined for the key highlighted in the Key list. The Maintenance utility displays the highlighted segment's specifications in the Segment box at the bottom right of the dialog box.

In addition, the following buttons appear under the Key and Segment lists:


Insert	Defines a new key or segment.
Delete	Removes the highlighted key or segment specification.
Compress	Renumbers the keys consecutively. You can use this button to remove gaps that result from deleting a key specification.

Because these buttons control key specifications for a file you want to create, you cannot use them to operate on keys in an existing file. If you want to create or drop an index on an existing file, refer to Index Tasks .

Key Segment

At the bottom right in the dialog box is the Key Segment group box. Table 13-4 describes the controls in this area. These controls are specific to the segment highlighted in the Segment list),

Table 13-4 Key Segment Specification Controls
Control	Description	Default
Data Type	Specifies a data type for the key segment. The NULL data type indicates that the index is one byte Null indicator segment. It must be in a multi-segment key and it must precede another key segment that is not a NULL type. The number used in the Btrieve API for this key type is 255.	String
Position	Specifies by number the relative starting position of the beginning of this key segment in the record. The value cannot exceed the record length.	1
Length	Specifies the length (in bytes) of the key segment. This value cannot exceed the limit dictated by the data type for the segment. The total of key position and key length cannot exceed the record length.	10
Null Value (Hex)	Specifies the null character value (in hexadecimal) for the key segment. Applicable only if the Null Key check box is selected for the key.	Binary zero
Case Insensitive	Specifies whether the segment is sensitive to case. Applicable only for STRING, LSTRING, and ZSTRING data types or for keys that do not use an ACS.	On
Descending	Specifies that the database engine sort the key segment values in descending order (that is, from highest to lowest).	Off
Use ACS	Specifies that the segment uses the alternate collating sequence defined for the key. Applicable only for string, lstring and zstring data types that are case sensitive.	Off
NULL Value Discrete Ordering	NULL Value Discrete Ordering is used for the null indicator segment (NIS) to determine whether the MKDE should treat the NIS as a boolean value, where any non-zero value is considered NULL, or as a one byte integer, where zero is considered non-null and all other values are considered different types of null. In this case they are sorted as discrete values. The Btrieve API uses the NO_CASE flag, 0x0400, to indicate discrete ordering should be performed, because that flag was previously unused for integer values.	Off

Methods for Handling Duplicate Keys

Multiple records may carry the same duplicated value for index keys. The two methods to keep track of the records with duplicate key values are called linked duplicates and repeating duplicates.

Linked Duplicates

The linked duplicates method uses a chain technique in which each record in the group connects to its neighbors by means of pointers. Each entry on an index page contains a pair of record pointers that indicate the first and last links in the chain of records that duplicate that key's value. This makes each key page entry 4 bytes longer than a repeating duplicates index. In addition, each record on the data page requires an extra 8 bytes of overhead for each linked duplicates index. These 8 bytes consist of two record pointers that point to the next and previous records in the chain.

The first record pointer holds the address of the first, or oldest, record stored. The second pointer holds the address of the most recent, or newest record. After the first record is written but before any others are added, both pointers on the key page entry hold the first record's address. Subsequent records cause the second pointer to be changed to point to each record as it is added. This permits the record pointer for the last record to be used as the previous-record link of the chain built in the data page when the record is added, and also to be used to locate that previous record.

Repeating Duplicates

With the repeating duplicates method, each duplicate key value is stored on both the index page and within the record on the data page. Each key value has only one record pointer instead of two. This method requires no chaining within the data records and saves the 8 bytes of overhead per index within each record. Since the key value is repeated for each duplicate record, the indexes affected increase in size.

Method Comparisons

The linked duplicates and repeating duplicates methods can be compared based on the following criteria:

Ordering

Storage

Performance

Concurrency

Ordering

A linked duplicates index retrieves duplicates in the order in which they were inserted. A repeating duplicates index retrieves duplicates in the order in which they are located within the file. Since location with a file cannot be controlled, the ordering must be considered as random.

Storage

A linked duplicates index requires 12 more bytes for the first occurrence of each duplicate key value. That includes 8 extra bytes on each record and 4 extra bytes for the key page entry. But each duplicate record requires no additional space in the key page, and adds only 8 bytes per record. Therefore, as the number of duplicates per key value increases, and as the size of the key value increases, linked duplicate indexes can save significant storage space used by key pages. However, storage space can increase if your file contains very few records with duplicate keys, the key length is very short, or both.

The following figure exemplifies the amount of storage space saved using linked duplicate indexes. Note that linked duplicate indexes take more space if duplicate records per key value are few. As the number of duplicate records per key value increases, however, linked duplicate indexes require less pages, providing significant space savings.

Figure 13-3 Comparison of Page Counts for Duplicate Key Methods


Legend:	= linked duplicates	= repeating duplicates
	Top two lines represent a key length of 100 Middle two lines represent a key length of 50 Bottom two lines represent a key length of 4

Performance

Faster performance results when fewer pages are involved in an index search because fewer pages must be read from disk. The linked duplicates method generally uses less physical storage space and therefore provides faster performance. The repeating duplicates method provides a performance advantage if only a small number of keys have duplicates.

Concurrency

The database engine provides page-level concurrency when several concurrent transactions are active on the same file at the same time. This applies to most changes to key pages and for all changes to data pages. The concurrency means that the same page can contain pending changes from separate transactions at the same time, and the transactions can be committed in any order. Repeating duplicate indexes take the most advantage of this concurrency.

Linked duplicate indexes add another limitation on concurrency that does not exist with repeating duplicates. When a new duplicate is created, the new record is linked to another record at the end of the list. This record linking causes two records to be locked instead of one. Since all duplicates are added to the end of the chain of linked records, only one duplicate can be inserted at a time.

Such a record lock conflict usually causes other clients to wait until the first transaction is committed. In a concurrent environment, if all new records use the same duplicate value, then concurrency can effectively be reduced to one transaction at a time. And if transactions are large or long lasting, this serialization can affect performance tremendously.

Performance is typically better if you use repeating duplicate indexes for databases that are updated in a concurrent environment. Therefore, unless you have a compelling reason to use the linked duplicates method, you should use repeating duplicate indexes for databases that are updated in a concurrent environment.

Information Editor Tasks

You perform the following tasks with the File Information Editor:

Load information from an existing data file (page 13-18)

Create a new file (page 13-18)

Compact a Btrieve file (page 13-20)

Specify a key's alternate collating sequence (page 13-21)

Loading Information from an Existing Data File

When you load information from an existing file, you are not editing the existing file. Instead, you are loading a copy of the information about that file. Generally, you want to load a data file before performing other tasks with the File Information Editor, but this is not mandatory.

To load information from an existing data file into the File Information Editor

Click Load Information at the top of the File Information Editor. The Select File dialog box appears.

Figure 13-4 Select File Dialog Box

Specify the name and path of the file for which you want to load information. (By default, data files have the.mkd extension.)

The Maintenance utility first attempts to open the specified file as a data file. If the file requires an owner name, the utility prompts you for one. (Because owner names are optional, the file you open may not require an owner name.) If the specified file is not a data file, the utility then attempts to open the file as a description file.

Creating a New File

You can create a new file based on the current information in the File Information Editor or on new information you provide.

To create a new file based on the current information in the File Information Editor

Click Create File at the top of the File Information Editor dialog box. The Create File dialog box appears.

Figure 13-5 Create File Dialog Box

Specify the controls in the Create File dialog box, which are described in Table 13-5.

Table 13-5 Create File Dialog Controls
Control	Description	Default
File Name	Specifies a name and path for the file. By default, data files have the.mkd extension.	N/A
File Type	Specifies the type of file to create. If you are creating a description file, you can use the Index Only option, which creates a description file you can use with the BUTIL utility to add an index to an existing data file. (For more information, refer to Creating Indexes .)	MicroKernel-compatible
System Data	Determines whether the utility includes system data in the file. If you choose Use Engine Setting, the utility uses the setting for the System Data configuration option described . If you choose No System Data, the utility does not create system data, regardless of the engine configuration. If you choose Force System Data, the utility creates system data, regardless of the engine configuration. This is applicable only if the file type is MicroKernel-compatible.	Use Engine Setting

Adding Comments to a Description File

The comments are written to the top of the description file when you create the description file. For example, the comment, "This is my file," appears at the top of the description files as /* This is my file */. If you add additional comments after creating the description file, you need to create the file again to include the additional comments.

To add comments to a description file

Click Description Comments. The Description File Comments dialog box appears.

Figure 13-6 Description File Comments Dialog Box

Enter a block of comments up to 5,120 characters long.

Click OK when you are finished entering comments.

Compacting Btrieve Data Files

You can compact a Btrieve data file to remove unused space in it, which typically decreases the file size. You can also perform this procedure using the command-line Maintenance utility (page 13-62).

To compact a Btrieve file

Click Load Information in the File Information Editor and select the file you want to compact.

Click Create File, give the file a new name (which creates a clone) in the Create File dialog box, and click OK.

From the Data menu on the main window, select Save. In the Save Data dialog box, enter the name of the original file in the From MicroKernel File box and then specify a name for the output file (for example, <original file>.out) in the To Sequential File box.

Click Execute. The Save Data dialog box displays the results of the save. Click Close.

From the Data menu, select Load. In the Load Data dialog box, enter the name of the sequential data file you just saved in the From Sequential File box. Then enter the name of the clone file you created in Step 2 in the To MicroKernel File box.

Click Execute. The Loading Data dialog box displays the results of the load. Click Close.

You can now compare the size of the original file to the clone file to verify the reduction in size.

Specifying a Key's Alternate Collating Sequence

You can use an alternate collating sequence (ACS) to sort string keys (types STRING, LSTRING, and ZSTRING) differently from the standard ASCII collating sequence. By using one or more ACSs, you can sort keys as follows:

By your own user-defined sorting order, which may require a sorting sequence that mixes alphanumeric characters (A-Z, a-z, and 0-9) with non-alphanumeric characters (such as #).

By an international sorting rule (ISR) that accommodates language-specific collations, including multi-byte collating elements, diacritics, and character expansions and contractions.

Files can have a different ACS for each key in the file, but only one ACS per key. Therefore, if the key is segmented, each segment must use either the key's specified ACS or no ACS at all. For a file in which a key has an ACS designated for some segments but not for others, Btrieve sorts only the segments that specify the ACS.

The ISR tables are provided with Pervasive PSQL and are based on ISO-standard locale tables. ISR tables are stored in the COLLATE.CFG file, which is a Pervasive PSQL system file. This means that multiple data files can share a single ISR.

To specify a key's alternate collating sequence

Click ACS Information.

The Maintenance utility displays the Specify ACS Information dialog box.

Figure 13-7 Specify ACS Information Dialog Box

You can specify either a Country ID and Code Page, an ACS File name, or an International Sorting Rule (ISR) as follows:

Table 13-6 ACS Information Controls
Control	Description	Default
ACS Country/Code
Country ID	An Intel-format number that identifies your country. Refer to your operating system's documentation for specific information.	-1
Code Page	An Intel-format number that identifies the code page you want to use. Refer to your operating system's documentation for specific information.	-1
ACS File	Specifies the fully qualified file name of the alternate collating sequence file.	N/A
International Sorting Rule	When you click this radio button you can specify a specific ISR table for sorting international data. Pervasive PSQL provides a set of pre-generated ISR tables, which are listed in the Pervasive PSQL Programmer's Guide.

When you specify a Country ID and Code Page ID, the database engine stores the locale-specific collating sequence in the data file. Moreover, the database engine can insert new key values correctly, even if the locale changes.

When you specify an ACS file name for a data file, the database engine copies the contents of the ACS file into the data file. (That is, the data file does not contain the file name of the ACS file.) The ACS identifies itself using an eight-digit name (such as UPPER). Subsequently, when you view the ACS information for a data file, the Maintenance utility displays this eight-digit name, not the file name of the original ACS.

When you specify an ACS File for a description file, the Maintenance utility copies the actual path and file name of the ACS file into the description file. Subsequently, when you view the ACS information for a description file, the Maintenance utility attempts to locate the specified ACS file.

To specify an ACS that sorts string values using an ISO-defined, language-specific collating sequence, you must specify an ISR table name. The Table Name field is limited to 16 characters. For more information on ISRs, refer to the Pervasive PSQL Programmer's Guide, available with the Pervasive PSQL Software Developer's Kit (SDK).