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).

Caution: 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.

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

Figure 19 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 70 describes the controls in this box.

Table 70

File Specification Controls

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 PSQL Programmer's Guide, which is part of the PSQL Developer Reference.

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

# 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 Engine is 119.

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

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

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

Key

At the bottom left in the dialog box is the Key group box. Table 71 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 71

Specifies that the key can have duplicate values (linked duplicates).

See Methods for Handling Duplicate Keys.

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.

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 72 describes the controls in this area. These controls are specific to the segment highlighted in the Segment list),

Table 72

Key Segment Specification Controls

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 multisegment 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.

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.

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.

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 MicroKernel Engine 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:

•

•

•

•

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 20 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:

•

Loading Information from an Existing Data File

•

Creating a New File

•

Compacting Btrieve Data Files

•

Specifying a Key’s Alternate Collating Sequence

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 21 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 22 Create File Dialog Box

Specify the controls in the Create File dialog box, which are described in Table 73.

Table 73

Create File Dialog Controls

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 23 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 (see To compact a Btrieve data file).

►

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 multibyte 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 PSQL and are based on ISO-standard locale tables. ISR tables are stored in the COLLATE.CFG file, which is installed with the PSQL database engine. Multiple data files can share a single ISR.

►

To specify a key’s alternate collating sequence