MicroKernel Engine Fundamentals
 
MicroKernel Engine Fundamentals
The following topics describe the features of the MicroKernel Engine:
Overview of the MicroKernel Engine
Pages
File Types
Data Types
Key Attributes
Database URIs
Double-Byte Character Support
Record Length
Data Integrity
Event Logging
Performance Enhancement
Disk Usage
Overview of the MicroKernel Engine
The Btrieve API is a low-level interface to the MicroKernel Engine that embodies functional aspects of the database design that might be otherwise transparent in higher-level interfaces such as SQL, Java, or ODBC. For example, the SQL interface operates independently of how the data is physically stored. However, the MicroKernel Engine developer must consider lower level aspects such as page size, physical and logical currency, type verification, and data validation. Despite these low-level considerations, the Btrieve API provides excellent flexibility and control over the data.
The MicroKernel Engine stores information in files, which can be up to terabytes in size for the 13.0 version file format, (256 GB for the 9.5 version, 128 GB for earlier 9.x versions, and 64 GB for other earlier versions). Inside each data file are records, which contain bytes of data. A file can contain billions of records.
The data in a record might represent an employee name, ID, address, phone number, rate of pay, and so on. However, the MicroKernel Engine interprets a record only as a collection of bytes; it does not recognize logically discrete pieces of information within a record. To the MicroKernel Engine, a last name, first name, employee ID, and so on do not exist inside a record.
The only discrete portions of information that the MicroKernel Engine recognizes in a record are keys. Keys provide both fast, direct access to records and a means of sorting records by key values. Because the MicroKernel Engine has no way of knowing the structure of the records in each file, you define each key by identifying the following:
Number. This is the key’s order in the list of keys. Version 6.0 and later files can have gaps between key numbers. That is, the MicroKernel Engine does not require keys to be numbered consecutively. When you add a key, you can specify a key number or let the MicroKernel Engine assign the lowest available key number. When you drop a key, you can leave the remaining key numbers as is or let the MicroKernel Engine renumber them consecutively.
Position. This is the key’s offset in bytes from the beginning of the record.
Length. This is the number of bytes to use for the key.
Type. This is the key’s data type.
Attributes. These provide additional information about how you want the MicroKernel Engine to handle the key values. The MicroKernel Engine supports these key attributes: segmentation, duplicatability, modifiability, sort order, case sensitivity, alternate collating sequence, and null value.
You can create or drop keys at any time. For each key defined in a data file, the MicroKernel Engine builds an index. The index is stored inside the data file itself. The index maps each key value in the file to an offset in the actual data. Normally, when accessing or sorting data, the MicroKernel Engine does not search through all the records in the file. Instead, it searches the index and then manipulates only those records appropriate to the request.
You can create indexes when you create the data file, or any time thereafter. When you create a data file, you can define one or more keys for the MicroKernel Engine to use in building indexes.
You can also define external indexes after creating a file. An external index file is a standard data file that contains records sorted by the key you specify. Each record consists of the following:
An address identifying the physical position of the record in the original data file
A key value
Positioning rules (guidelines governing which record is current, which is next, and so on) are the same, regardless of when you create an index.
If you create an index at the same time that you create the file, the MicroKernel Engine stores duplicate key values in the chronological order in which the records are inserted into the file. If you create an index for a file that already exists, the MicroKernel Engine stores duplicate key values in the physical order of the corresponding records in the file at the time the index is created. How the MicroKernel Engine stores duplicate key values in an index also depends on whether the key is linked-duplicatable or repeating-duplicatable. For more information, see Duplicatability.
Note The chronological ordering of records can change when you update records and change their key values, when you drop and rebuild an index, or when you rebuild the file. Therefore, you should not assume that the order of records in a file always reflects the order in which the records were inserted. If you want to track the order of record insertion, use a key of type AUTOINCREMENT.
You can delete, or drop, an index when your application no longer needs it. The space that the index used in the file is freed for data or for other index pages. (However, this free space remains allocated to the file; you will not see a reduction in physical file size after dropping an index.)
See Designing a Database for specific information about defining keys.
MicroKernel Engine Environment
Before an end user can run your MicroKernel Engine application, a version of MicroKernel Engine must be available to the end user’s computer. You should provide the end user with information about any prerequisite the MicroKernel Engine software versions and configurations that your application requires.
Configuration Notes
End users may need to know the following information about your MicroKernel Engine application. Include this information in the documentation you provide with your MicroKernel Engine application.
The amount of memory your application requires.
Your application may require more memory or disk space than the MicroKernel Engine requires on its own. Establish the disk space and memory requirements of your application and communicate this information to your users. For information about system requirements of the MicroKernel Engine, see Getting Started with PSQL and the Actian website.
Whether the application requires the MicroKernel Engine configuration settings other than the defaults. In particular, consider whether end users need to change these MicroKernel Engine options:
Create File Version. Does your application need backward compatibility with a previous version of the MicroKernel Engine? If so, instruct your end users to set an appropriate value for this option.
Handles. Does your application need to use more than 60 logical file handles at one time? If so, instruct your end users to set this option to an appropriate value.
Index Balancing. Does your application set the Index Balancing file attribute on every file it creates? If so, your end users can use the default of Index Balancing turned off. If not, you may need to instruct your end users to turn Index Balancing on at the MicroKernel level. For more information, see Index Balancing.
Largest Compressed Record Size. Does your application use compressed records? If so, see Record and Page Compression in Advanced Operations Guide, and Choosing a Page Size, Estimating File Size, and Record Compression.
System Data. Do all files in your database have unique keys? If so, the files are transaction durable. If not, your end users may want to set System Data to If Needed or Always in order to make the files transaction durable.
For descriptions of configuration options, see Advanced Operations Guide.
Pages
This section includes the following information about pages and how the MicroKernel Engine handles them:
Page Types
Page Size
Page Types
Files consist of a series of pages. A page is the unit of storage that the database transfers between memory and disk. A file is composed of the following types of pages:
File Control Record (FCR)
Contains information about the file, such as the file size, page size, and other characteristics of the file. The first two pages in every 6.0 and later data file are FCR pages. At any given time, the MicroKernel Engine considers one of the FCR pages to be current. The current FCR page contains the latest file information.
Page Allocation Table (PAT)
Part of the internal implementation of the MicroKernel Engine for tracking pages in a file.
Data
Contains the fixed-length portion of records. The MicroKernel Engine does not split a single fixed-length record across two data pages. If a file does not allow variable-length records or use data compression, the file has data pages and no variable pages.
Variable
Contains the variable-length portion of records. If the variable-length portion of a record is longer than the remaining space on a variable page, the MicroKernel Engine splits the variable-length portion over multiple variable pages. If a file allows variable-length records or uses data compression, the file has both data and variable pages.
Index
Contains key values used in retrieving records.
Alternate Collating Sequence (ACS)
Contains alternate collating sequences for the keys in a file.
All 6.0 and later files have FCR and PAT pages. Standard files also contain data and index pages, and optionally, variable and ACS pages. Data-Only Files contain no index pages. Key-Only Files contain no data pages.
Page Size
You specify a fixed page size when you create a file. The page size you can specify, the file overhead, and so forth, depends on a variety of factors, including the file format. See Chapter 5 Designing a Database for information on page sizes. The following sections provide an overview:
Page Size Criteria
Large vs. Small Page Size
Page Size Criteria
The page size you specify should satisfy the following criteria:
Enables data pages appropriate to the file’s record length.
Each data page contains a certain number of bytes for overhead. See Table 15. After that, the MicroKernel Engine stores as many records as possible in each data page, but does not break the fixed-length portion of a record across pages.
The optimum page size accommodates the most records while minimizing the amount of space left over in each data page. Larger page sizes usually result in more efficient use of disk space. If the internal record length (user data + record overhead) is small and the page size is large, the wasted space could be substantial.
Allows index pages appropriate to the file key definitions.
Each index page contains a certain number of bytes for overhead. See Table 15. After that, the file’s index pages must be large enough to accommodate eight keys, plus overhead information for each key (see Tables 13, 14, 15, 16, and 17 for information the number of bytes of overheard per your configuration.)
Allows the number of key segments that the file needs.
As discussed in Segmentation, the page size you define for a file limits the number of key segments you can specify for that file.
Optimizes performance.
For optimum performance, set the page size to an even power of two, such as 512, 1024, 2048, 4096, 8192, or 16384 bytes. The internal MicroKernel Engine cache can store multiple size pages at once, but it is divided in powers of 2. Page sizes of 1536, 2560, 3072, and 3584 actually waste memory in the MicroKernel Engine cache. Page sizes that are powers of 2 result in a better use of cache.
Large vs. Small Page Size
To make the most efficient use of modern operating systems, you should choose a larger page size. The smaller page sizes were used when DOS was the prominent operating system (when a sector was 512 bytes and all I/O occurred in multiples of 512). This is no longer the case. Both 32-bit and 64-bit operating systems move data around their cache in blocks of 4096 bytes or larger. CD ROM drives are read in blocks of 2048 bytes.
The MicroKernel Engine indexes are most efficient when a page size of 4096 bytes or larger is used. The key will have more branches per node and thus will require fewer reads to find the correct record address. This is important if the application is doing random reads using a key. This is not important when an application accesses the file in a sequential manner either by key or by record.
A good reason for having smaller page sizes is to avoid contention. With fewer records in each page it becomes less likely that different engines or transactions will need the same page at the same time. If a file has relatively few records, and the records are small, you may want to choose a small page size. The larger the file, the less likely contention will happen.
Another potential problem with large page sizes is specific to version 7.0 and later files. There is a maximum of 256 records or variable-length sections that can fit on the same data page. If you have short or compressed records, or short variable-length sections, you can easily reach the limit while you still have hundreds of bytes available on every page. The result is a much larger file than needed. By knowing your record size, you can calculate how big of an issue this is.
Factors To Consider When Determining Page Size
Keys work better with larger pages. There are more branches per B-tree node and thus fewer levels to the B-tree. Fewer levels means fewer disk reads and writes. Fewer disk reads means better performance.
Concurrency works better with smaller pages, especially when client transactions are used. Since the MicroKernel Engine locks some pages changed during the transaction, all other clients must wait for locked pages until the transaction is ended or aborted. With a lot of clients trying to access the same pages concurrently, the less that is found on each page is better.
Random access to pages works better with smaller pages since more of the stuff you actually use is in cache. If you access anything again, it is more likely to be still in cache.
Sequential access to a large volume of records works better with larger pages since more is read at once. Since you are using most everything on each page read, there will definitely be fewer reads.
The database designer must choose between these conflicting needs. A reference table that is not changed very often, but is searched or scanned most of the time, should have larger page sizes. A transaction file which is inserted and updated within transactions should have smaller page sizes.
Only careful consideration of all factors can give the right answer to what the page size should be. For more information about choosing a page size, see Choosing a Page Size.
File Types
The Btrieve API supports three data file types, large files to a maximum file size of terabytes for the 13.0 version file format, 256 GB for 9.5 files, 128 GB for earlier 9.x versions, and 64 GB for other earlier versions, as well as long file names. The following topics cover these features:
Standard Data Files
Data-Only Files
Key-Only Files
Large Files
Long File Names
Note For users of Btrieve 6.x and earlier, the MicroKernel Engine can create files in 8.x and 7.x formats. These newer formats allow for enhancements and new features.

Btrieve 6.x and earlier cannot open files for versions 7.0 or later. However, later Btrieve releases can open pre-7.0 files. When it opens these files, it does not convert them to later file formats. Also, you can configure MicroKernel Engine compatibility to create 7.x or 6.x files if you need newly created files in those formats.
Standard Data Files
A standard 7.x or later data file contains two FCR pages followed by a number of PAT pages, index pages, data pages, and possibly variable and ACS pages. You can create a standard file for use with either fixed- or variable-length records. Because standard files contain all the index structures and data records, The MicroKernel Engine can dynamically maintain all the index information for the records in the file.
Data-Only Files
When you create a data-only file, you do not specify any key information, and PSQL does not allocate index pages for the file. This results in a smaller initial file size than for standard files. You can add keys to a data-only file after creating the file.
Key-Only Files
Key-only files contain only FCR pages followed by a number of PAT pages and index pages. In addition, if you have defined referential integrity constraints on the file, the file may contain one or more variable pages.
Key-only files include only one key, and the entire record is stored with the key, so no data pages are required. Key-only files are useful when your records contain a single key and that key takes up most of each record. Another common use for a key-only file is as an external index for a standard data file.
The following restrictions apply to key-only files:
Each file can contain only a single key.
The maximum record length you can define is 253 bytes (255 bytes for a pre-6.0 file).
Key-only files do not allow data compression.
Large Files
The MicroKernel Engine supports file sizes up to terabytes for the 13.0 version file format, 256 GB for 9.5 files, 128 GB for earlier 9.x versions, and 64 GB for other earlier versions. However, many operating systems do not support single files this large. In order to support files larger than the operating system file size limit, the MicroKernel Engine breaks up large files into smaller files that the operating system can support. A large, logical file is called an extended file. The smaller, physical files that comprise an extended file are called extension files. The base file is an original data file that has become too large to support as a single, physical file. Non-extended (that is, non-segmented) files provide more efficient I/O and, therefore, increased performance.
You can choose to not to automatically extend 9.x format files or later at 2 GB. To change the segment operation setting access the configuration settings in the PSQL Control Center (PCC) as described in Configuration Using PCC in Advanced Operations Guide. From there you can set the Limit Segment Size to 2 GB option. This setting as no effect on 13.0 format files, which are never segmented.
If this option is unselected, PSQL 9.x files will not be segmented automatically at 2 GB. Version 8.x and earlier data files will continue to be extended when they reach 2 GB. If your files are already extended, they will remain segmented.
Regardless of the configuration setting, all files will continue to be extended based on the file size limitations of the current operating system.
For information about backing up files, including extended files, see Backing Up Your Files.
Long File Names
The MicroKernel Engine supports long file names whose length is less than or equal to 255 bytes. The following items must conform to this upper limit:
The localized multi-byte or single byte version of the string
The UNC version of the file name that is created by the requesters, which is in UTF-8 UNICODE format.
The file name can contain spaces unless the Embedded Spaces client configuration option is disabled. The default setting is On. See Advanced Operations Guide ( Long File Names and Embedded Spaces Support).
When the MicroKernel Engine generates new files based on an existing file name, such as with Large Files or during Archival Logging or Continuous Operations (for more information, see Logging, Backup, and Restore in Advanced Operations Guide), the new file name includes as much of the original file name as possible and an appropriate file extension, as in the following examples:
Original File Name
Generated File Name in Continuous Operation
LONG-NAME-WITHOUT-ANY-DOTS
LONG-NAME-WITHOUT-ANY-DOTS.^^^
VERYLONGNAME.DOT.DOT.MKD
VERYLONGNAME.DOT.DOT.^^^
Data Types
When using 7.x or a later file format, you can use the following data types when you define a key:
AUTOINCREMENT
BFLOAT
CURRENCY
DATE
DECIMAL
FLOAT
INTEGER
LSTRING
MONEY
NUMERIC
NUMERICSA
NUMERICSTS
TIME
TIMESTAMP
UNSIGNED BINARY
ZSTRING
WSTRING
WZSTRING
NULL INDICATOR
 
 
If you are using the 6.x file format, you can use all the preceding types to define a key except for CURRENCY and TIMESTAMP.
If you are using a file format before 6.x, NUMERICSA and NUMERICSTS are not available as data or key types.
For more information, see Data Types in SQL Engine Reference.
Key Attributes
The following sections describe the attributes you can assign when you define a key:
Key Attributes Description
Key Specification
Key Attributes Description
These topics containsinformation on attributes that you can assign to keys:
Segmentation
Duplicatability
Modifiability
Sort Order
Case Sensitivity
Null Value
Alternate Collating Sequences
Segmentation
Keys can consist of one or more segments in each record. A segment can be any set of contiguous bytes in the record. The key type and sort order can be different for each segment in the key.
he number of index segments that you may use depends on the file’s page size.
Page Size (bytes)
Maximum Key Segments by File Version
8.x and earlier
9.0
9.5
13.0
512
8
8
Rounded up2
Rounded up2
1024
23
23
97
Rounded up2
1536
24
24
Rounded up2
Rounded up2
2048
54
54
97
Rounded up2
2560
54
54
Rounded up2
Rounded up2
3072
54
54
Rounded up2
Rounded up2
3584
54
54
Rounded up2
Rounded up2
4096
119
119
2043
1833
8192
n/a1
119
4203
3783
16384
n/a1
n/a1
4203
3783
1”n/a” stands for “not applicable”
2”Rounded up” means that the page size is rounded up to the next size supported by the file version. For example, 512 is rounded up to 1024, 2560 is rounded up to 4096, and so forth.
3While a 9.5 format or later file can have more than 119 segments, the number of indexes is limited to 119.
See status codes 26: The number of keys specified is invalid and 29: The key length is invalid for related information about index segments and the MicroKernel Engine.
The total length of a key is the sum of the length of the key segments, and the maximum length is 255 bytes. Different key segments can overlap each other in the record.
When a segmented key is a nonduplicatable key, the combination of the segments must form a unique value; however, individual segments may contain duplicates. When you are defining this type of segmented key, each segment has duplicates=no as a key-level attribute even though that particular segment may have duplicates. To ensure that a particular segment is always unique, define it as a separate nonduplicatable key in addition to the segmented key definition.
When issuing a call to the MicroKernel Engine, the format of the key buffer must be able to accommodate the key specified by the key number. So, if defined keynumber=0 and key 0 is a 4-byte integer, the key buffer parameter can be any of the following:
A pointer to a 4-byte integer
A pointer to a structure where the first (or only) element is a 4-byte integer
A pointer to a 4-byte (or longer) string or byte array
Basically, the MicroKernel Engine gets a pointer to a memory location to be used as a key buffer. The MicroKernel Engine expects that memory location to have a data value corresponding to the specified key number for certain operations, such as Get Equal. In addition, the MicroKernel Engine may write data out to that location, and the data written will be a key value corresponding to the specified key number. In this situation, the memory location must be allocated large enough to accommodate the entire key value.
To the MicroKernel Engine, a key is a single collection of data, even if it is made up of multiple segments. The segment feature allows you to combine noncontiguous bytes of data together as a single key. It also allows you to apply different sorting rules (as dictated by the supported data types) to different portions of the key data. The data type associated with a key segment is used typically as a sorting rule – it tells the MicroKernel Engine how to compare two values to determine which one is larger. Data types are not used to validate data.
The MicroKernel Engine always deals with an entire key, not a key segment. To work with any key, set up a key buffer that is large enough to hold the entire key. Some applications define a generic 255 byte buffer to use on all calls to the MicroKernel Engine; the maximum size of a key is 255 bytes, which is a sufficient size. When data is returned in this key buffer, the application usually copies data out of the generic buffer into an application variable or structure declared as the same type(s) as the key segment(s). Alternatively, pass a key buffer parameter (simple variable or structure variable) that directly corresponds to the key.
For example, suppose you want to read a record and only know the value of the first segment of the key, but not all segments. You can still utilize that key to find the data. However, you still have to pass in an entire key buffer corresponding to all segments. Because you only know part of the key value, you cannot use the Get Equal call. You have to use the Get Greater Or Equal call. In this case, initialize the key buffer with as many key values as you know and then specify low or null values for the unknown key segments.
For example, given a key 1 definition of three segments corresponding to data values ulElement2, ulElement3, and ulElement5, if you know what value you want for ulElement2, you would initialize your key buffer as:
SampleKey1.ulElement2 = <search value>;
SampleKey1.ulElement3 = 0;
SampleKey1.ulElement5 = 0;
and then pass &SampleKey1 as the key buffer parameter on a Get Greater Or Equal call. When the MicroKernel Engine completes the call and a record is found, status code 0 is returned, the corresponding data record is returned, and the key buffer is set to have the key value including all three segments.
Duplicatability
PSQL supports two methods for handling duplicate key values: linked (the default) and repeating. With linked-duplicatable keys, the MicroKernel Engine uses a pair of pointers on the index page to identify the chronologically first and last records with the same key value. Additionally, the MicroKernel Engine uses a pair of pointers in each record on the data page to identify the chronologically previous and next records with the same key value. The key value is stored once, and only on the index page.
With repeating-duplicatable keys, the MicroKernel Engine uses a single pointer on the index page to identify the corresponding record on the data page. The key value is stored on both the index page and the data page. For more information on duplicate keys, see Duplicatable Keys.
Modifiability
If you define a key as modifiable, the MicroKernel Engine enables you to change the value of a key even after the record is inserted. If one segment of a key is modifiable, all the segments must be modifiable.
Sort Order
By default, the MicroKernel Engine sorts key values in ascending order (lowest to highest). However, you can specify that the MicroKernel Engine order the key values in descending order (highest to lowest).
Note Use caution when using descending keys with the MicroKernel Engine Get operations (Get Greater (8), Get Greater or Equal (9), Get Less Than (10), and Get Less Than or Equal (11)). In this context, Greater (or Less) refers to the order with respect to the key; in the case of a descending key, this order is the opposite of the corresponding ascending key.
When you perform a Get Greater (8) operation on a descending key, the MicroKernel Engine returns the record corresponding to the first key value that is lower than the key value you specify in the key buffer. For example, consider a file that has 10 records and a descending key of type INTEGER. The actual values stored in the 10 records for the descending key are the integers 0, 1, 2, 3, 4, 5, 6, 7, 8, and 9. If the current record’s key value is 5 and you perform a Get Greater operation, the MicroKernel Engine returns the record containing the key value 4.
Similarly, when you perform a Get Less Than (10) operation using a descending key, the MicroKernel Engine returns the record with the next higher value than the one you specify in the key buffer. Using the preceding example, if the current record’s descending key has a value of 5 and you perform a Get Less Than operation, the MicroKernel Engine returns the record containing the key value 6.
Case Sensitivity
By default, the MicroKernel Engine is case sensitive when sorting string keys; that is, it sorts uppercase letters before lowercase letters. When you define a key to be case insensitive, the MicroKernel Engine sorts values without distinguishing case. Case sensitivity does not apply if the key has an alternate collating sequence (ACS).
Null Value
PSQL contains two ways of identifying a column of data as a Null value. The original type of null value, called a legacy null, has been used in the MicroKernel Engine for years. The newer type of null is the true null. This topic describes the legacy null and then details use of true nulls in the MicroKernel Engine.
Legacy Null
The original method of defining a nullable field is referred to as pseudo-null or legacy-null. It is based on the premise that if the entire field is full of a particular byte value, typically ASCII zero, then the field is considered Null. The byte value is defined in the key definition supplied when creating the index. Using the MicroKernel Engine, the only thing that the MicroKernel Engine can do with this knowledge is to decide whether or not to include the field in an index. There are no special sorting rules for Legacy Nulls since they are just values that sort just like all the other values, despite their special meaning.
If a key description contains the flag for All Segments Null (0x0008), then the key value is not put into the index if each and every segment in the key is considered to be null by having the "null byte" in every byte of the field. Likewise, if a key description contains the flag for Any Segment Null (0x0200), then the key value is not put into the index if any one or more of the key segments is considered to be Null by the same rule
The Relational Engine never uses these flags in the indices that it defines. The Relational Engine does not use these flags, since in order to make joins between tables, all records in the table must be accessible through the index.
True Null Indexes
Starting with Pervasive.SQL 2000, a new type of Null Indicator was introduced called true null.
True nulls are implemented in the MicroKernel Engine by preceding a nullable field with a one byte Null Indicator Segment (NIS). This is an extra byte of data outside the normal column width that indicates whether the column is Null or not. A value of zero in this byte indicates that the column associated with it is normal, or Not Null. Any other value in this byte indicates that the column value is Null.
With true nulls, unlike legacy nulls, you can tell the difference between an integer that is zero and one that is Null. This is true for any type of number field. You can even distinguish a string field that has a zero length string in it from one that should be identified as null, if there is a need for such a distinction.
The Relational Engine can identify and use true null columns whether or not there is an index defined on them, but a basic data file only identifies the fields that are included in keys.
You can define true null fields within MicroKernel Engine keys by adding a Null Indicator Segment (NIS) before the nullable field in the key definition of a Create (14) or Create Index (31) operation. See Rules for True Null Keys for the rules regarding true null keys.
The MicroKernel Engine does not enforce any restrictions on the offset of the NIS whereas the Relational Engine assumes that it is immediately preceding the nullable field. As such, it is recommended that you structure the fields within your record to make room for the NIS in the byte preceding any field that will use an NIS. This preserves your ability to access these tables through SQL should you need to do so.
Rules for True Null Keys
The following rules must be followed when using this new key type:
1 The field length must be 1.
2 The field must precede another field in the index. In other words, this must be a multi-segmented index with the NIS being defined immediately before another segment. The NIS cannot be the last or only key segment.
3 The field immediately following it is affected by the contents of the NIS. If the NIS is zero, then the following field is considered non-null. If this field is anything other than zero, the field is considered NULL.
4 The offset of the NIS should be the byte preceding the following field. This is the way the PSQL relational engine expects these fields to align. Therefore, if a data dictionary is created for this index, the NIS should be immediately preceding the field it controls. That said, there is nothing in the transactional API that makes this a requirement.
NIS Values
Any nonzero value is considered an indicator that the following segment is null. By default, the MicroKernel Engine makes no distinction between nonzero numbers. The PSQL relational engine currently uses only a value of 1 in this field to indicate a null. It is possible, however, to make a distinction between different types of nulls. This can be done by using the Case Insensitive flag on the NIS. Since this key flag is normally only applicable to the various string and character fields, it is overloaded to have the special meaning of DISTINCT when used with an NIS. It means that different NIS values should be treated distinctly and sorted separately. Actian Corporation reserves the use of the first 15 values for future use. If you want to apply a special meaning to various types of nulls in your application, please use NIS values greater than 16. For example, more specific null definitions could be:
Not applicable
To be determined
Cannot be determined
Undetectable
No value yet, but needed soon
When you add the DISTINCT flag (Case Insensitive) to the NIS, these nonzero values will be sorted separately as distinct values.
Sorting of True Null Values
A true null field has a non-determinate value. In other words, its value cannot be known. According to this definition, no two Null values are equal to each other, nor are they equal to any other key value. Yet the MicroKernel Engine must group these Null values together and you must be able to find key values that are equal to Null. To accomplish this, the MicroKernel Engine interprets true null values as if they are equal to each other, depending on the purpose of the comparison. When sorting, and finding a place for the Null values in an index, true null values are grouped together as if they were equal to each other. But when trying to determine if a value already exists in a unique index, true nulls are not equal to each other.
Any non-zero value in the NIS means the following field is Null. The default behavior is to treat all non-zero values in the NIS as if they were the same value and interpret them to indicate that the nullable field is Null. As such, if you insert records that contain a variety of non-zero values in the NIS and a variety of values in the nullable field that follows, they will all be interpreted as the same value, and will be sorted as a collection of duplicates.
Linked Duplicate Keys and True Nulls
This section discusses the results of inserting several Null values into a Linked Duplicate key into a Linked Duplicate key.
Linked Duplicates contains a single key entry for each unique value, with two record address pointers; one for the first duplicate record and one for the last record in the duplicate chain. Each record contains 8 bytes of overhead consisting of pointers to the previous and next records in the chain. Each new duplicate value is added at the end of the chain, thus ensuring that the duplicate records are linked in the order they were inserted. All true null values are considered duplicates for the purpose of adding them to an index, so they all will be linked to the same chain in the order they were inserted. Even if each record contained different byte values in the NIS and the associated nullable field, there will only be one key entry pointing to the first and last record in this chain. If the NIS key segment is defined as descending, this key entry will occur first in the index. Otherwise, it will occur last.
Repeating Duplicate Keys and True Nulls
Repeating Duplicate Keys contain an actual key entry for each record represented in the index. There is no overhead in the record itself and for each record, there is a key entry that points to it. Duplicate values in this kind of index are sorted by the physical record address to which they point. This means that the order of duplicates is unpredictable, especially in a highly concurrent environment where random records are being inserted and deleted by many clients.
True Null values are interpreted as if they are duplicates and are sorted not by the bytes found in the nullable field, but rather by the record address. So when using repeating duplicate keys, the records containing true null values are grouped together, but in a random fashion. If the NIS segment is descending, they will occur first in the index, otherwise, they will occur last.
Unique Keys and True Nulls
In the MicroKernel Engine, if you define an index without either duplicate flag, the index must contain only unique values. But since the value of a true null field is indeterminate, they should not be considered duplicates. For this reason, the MicroKernel Engine allows multiple true null values to be entered into a unique key, assuming that once the value is assigned with an update operation, then the uniqueness of the key can be determined. But for the purposes of sorting these values in the index, the MicroKernel Engine groups them all together as if they were duplicates. So the section of the index containing the true null values resembles a Repeating Duplicate index. The nulls are sorted together according to the physical record address, the order of which cannot be predicted.
Non-Modifiable Keys and True Nulls
Once you put a value into a non-modifiable key, it cannot be changed. But because a true null value does not have an actual value, the MicroKernel Engine allows you to insert a record with a true null value in any or all fields defined in true null indexes, and then later change those field values in an update operation from null to non-null. But once any field has become non-null, the non-modifiability is enforced and it cannot be changed again, even if to establish the field as null again.
Get Operations and True Nulls
Even though true null values are indeterminate and are not considered equal to each other, it is possible to locate a record with a true null key segment.
The various Get operations can address true null keys by using this sequence:
1 Place the non-zero value in the NIS byte
2 Place the full key into the Key Buffer
3 Perform a Get operation as if true null values are equal to each other.
The following list shows the expected behavior from the Get operations:
Get Equal and Get Greater Than or Equal will return the first record with a null in the forward direction.
Get Less Than or Equal will return the last record with a null as viewed from the forward direction.
Get Less Than will return the record before the null values
Get Greater Than will return the record after the null values.
This is consistent with the behavior of the Get operations for normal duplicate values.
Distinct True Nulls
It is possible to distinguish between different values in the NIS byte. The default behavior, as indicated, is that all non-zero values in the NIS are considered to be the same thing, and whatever the NIS contains, if it is not zero, the nullable field is Null. The Relational Engine currently uses this default behavior on all true null index segments that it creates.
However, if you want to store different kinds of Null values in your table, then you can add the NOCASE flag (0x0400) to the key definition of the NIS segment. Hereafter, we will call this the DISTINCT flag. When you do this, the MicroKernel Engine will treat different NIS values as different or distinct from each other.
Distinct True Null segments are sorted in groups by their NIS value. The same rules apply as discussed above when building the various types of indexes. A linked duplicate key will have a single entry for each distinct NIS value with a pointer to the first and last occurrence of that type of Null. Repeating Duplicates and Unique keys will also group the null records by their distinct NIS value. Descending Keys have the highest NIS values grouped first, sorted down to the zero, or non-null values. Ascending keys sort the non-null records first, followed by NIS values of 1, then 2, and so on. Get operations pay attention to the value of the NIS. If you do a GetEQ using a key buffer where the NIS is 20, and all the NIS values in a Distinct True Null index are 1, then the MicroKernel Engine will not find any matching values.
Although the Relational Engine nor any PSQL access method currently uses the DISTINCT flag when creating true null indexes, they might in the future. For this reason, NIS values 2 through 16 are reserved for future use, in case PSQL needs to assign specific meanings to these 'types' of nulls. So if you use distinct null values for records accessed through the transactional Btrieve API, use values greater than 16.
Multi-Segmented True Null Keys
Consider a multi-segmented True Null index containing two nullable string columns. The key would actually be defined as a four segment index. The first segment is an NIS, followed by the first nullable field, then the second NIS followed by the second nullable field. Now consider what would happen if the following records were put into the file.
"AAA", NULL "BBB", NULL "CCC", NULL NULL, NULL
"AAA", "AAA" "BBB", "AAA" "CCC", "AAA" NULL, "AAA"
"AAA", "BBB" "BBB", "BBB" "CCC", "BBB" NULL, "BBB""
"AAA", "CCC" "BBB", "CCC" "CCC", "CCC" NULL, "CCC"
plus a couple more of these records; "BBB", NULL
The Relational Engine always creates True Null index segments such that the NULL values will occur first. It does this by adding the Descending flag (0x0040) to each NIS segment. Let's assume that the descending flag is used on each NIS and on the second nullable field, but not the first nullable field. If so, these records would be sorted like this.
1 NULL, NULL
2 NULL, "CCC "
3 NULL, "BBB""
4 NULL, "AAA "
5 "AAA", NULL
6 "AAA", "CCC"
7 "AAA", "BBB"
8 "AAA", "AAA"
9 "BBB", NULL
10 "BBB", NULL
11 "BBB", NULL
12 "BBB", "CCC "
13 "BBB", "BBB"
14 "BBB", "AAA "
15 "CCC", NULL
16 "CCC", "CCC "
17 "CCC", "BBB"
18 "CCC", "AAA "
The nulls always occur before the non-nulls since both NIS are descending. But when the NIS is zero, i.e, the fields are non-null, the first field is sorted ascending and the second is sorted descending.
The following is what would be returned by various Get operations;
GetLT "BBB", NULL returns record 8 "AAA", "AAA"
GetLE "BBB", NULL returns record 11 "BBB", NULL
GetEQ "BBB", NULL returns record 9 "BBB", NULL
GetGE "BBB", NULL returns record 9 "BBB", NULL
GetGT "BBB", NULL returns record 12 "BBB", "CCC "
The GetLE has the implication that you are looking to traverse the file in the reverse direction, so it returns the first occurrence of a key value that "matches" in the reverse direction. GetEQ and GetGE imply that you are moving in the forward direction.
Excluding Records from an Index
As with legacy nulls, you can also apply the flag for "All Segments Null" (0x0008) or "Any Segment Null" (0x0200) to each segment of any index containing an NIS. When you insert a record, the MicroKernel Engine will determine of the nullable field is Null using the NIS. The same rules apply to determine if the key entry will be put into the index or not.
Note Files created by the Relational Engine do not use these flags.
So you should not use these flags if you think that you might at some point want to access these files from SQL, where a goal might be to find any records "where column IS NULL". The Relational Engine will use the index to find the null records, but they will not be accessible through the index.
Use of Null Indicator Segment in Extended Operations.
Extended operations allow your application to access fields in a table even if they do not have indexes created for them. You can apply a filter to the fields in your record, defining fields on the fly, using knowledge of the record from any source. Thus it is possible to define True Null fields in an extended operation and have the MicroKernel Engine apply the same comparison rules that it would when sorting these fields into an index.
You must define the extended operations filter just like you would define a key. Include a filter segment for the NIS followed by the nullable field. You must include the nullable field in the filter even if you are searching for a null value, where the content of the nullable field does not matter. The MicroKernel Engineneeds both filter segments so that a GetNextExtended can be optimized against an index path and it enforces this with status 62, indicating that a filter expression for an NIS was not followed by a non-NIS.
The only comparison operator you can use for an NIS is EQ or NE. You will get status 62 if you try to use any of the other comparison operators; GT, GE, LT, and LE.
A status 62 occurring from a badly formed extended operation descriptor adds a system error PSQL Event Log. These system errors are listed in table 2 to help you identify the reason for the status 62.
If you want to treat different NIS values distinctly, then add 128 to the comparison operator on the NIS field. This is the same bias value that you would use to indicate case insensitivity. And just like when defining an index, the case insensitive flag has been overloaded for Null Indicator key types to indicate that the non-zero values should be compared distinctly meaning that they should be distinguished from one another instead of treating them all the same.
If you are using the extended operations to get the best performance possible, you will be trying to search along a key path for specific limited ranges of key values. First, establish currency at the beginning of the range by using GetGE. Then follow that with GetNextExtended. Or, you can do a GetLE followed by GetPrevExtended. These extended operations can stop searching automatically when there is no more chance of finding any more values that match the filter. This is called extended operation optimization. If your filter can use optimization, it will be much more efficient because there may be a huge number of records that can be skipped and not read from the file. In order to create an optimized search, you need to be traversing the index in a direction where a limit exists. Also, your filter must exactly match the index, using AND instead of OR as segment connectors.
If you do a GetNextExtended on an ascending index, then an optimized filter can stop at the limit when the conditional operator is EQ, LT or LE. A search will have to look to the end of the file for values greater than a particular value going forward along an ascending index. Likewise, if it is a descending index, then it can stop at a limit when the conditional operator is EQ, GT or GE. This can get much more complicated when there are multiple fields in the search criteria. The simple way to think about it is that in order to optimize a filter, only the last segment can have any other conditional operator than EQ. This includes the NIS. If the conditional operator on an NIS is NE, the filter can only be optimized up to the previous filter segment.
Exactly matching the index means that each filter expression should follow the order of the segments in the index, have the same offset, length, key type, case sensitivity (or distinct flag), and ACS specification. Without these things matching the index, extended operations cannot be optimized.
True Nulls and the SQL Engine
True nulls are implemented in the Relational Engine through the use of the Null Indicator key type and follow the rules described above. The MicroKernel Engine applications can also use this key type to identify the nullness of a nullable field regardless of its contents. This provides a way to identify null integers and other number data types, and fully manage these nullable fields.
True Nulls and Extended Operations
Status 62 occurring on an extended operation indicates that the descriptor is incorrect. Sometimes, it may be difficult to determine what exactly is wrong with the descriptor. The database engine adds a line in the PSQL event log that can be used to determine the exact problem. The event log entry will look similar to the following:
12-12-2018 11:12:45 W3MKDE 0000053C W3dbsmgr.exe MY_COMPUTER E System Error: 301.36.0 File: D:\WORK\TEST.MKD
The following numbers immediately after the system error 301 through 318 identify the problem.
Table 2 System Error Codes
System Error
Description
301
The descriptor length is incorrect.
302
The descriptor ID must be either EG or UC.
303
One of the field types is not valid.
304
The NOCASE flag on the operator can only be used with string and Null Indicator types.
305
The ACS flags (0x08 and 0x20) on the operator can only be used with string types.
306
An unbiased operator is equal to zero.
307
An unbiased operator is greater than six.
308
An invalid expression connector was found. Only 0, 1, and 2 are allowed.
309
The ACS is not defined.
310
The last expression needs a terminator.
311
A terminator was found before the last expression. The filter segment count may be wrong.
312
The number of records to extract is zero.
313
One of the extractor field lengths is zero.
314
A Null Indicator Segment must be followed by another field.
315
A Null Indicator Segment must be connected to the next segment with an AND
316
A Null Indicator Segment can only be used with EQ or NE.
317
A Null Indicator Segment can not follow another NIS.
318
A field following a Null Indicator Segment can not be longer than 255 bytes.
Alternate Collating Sequences
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 ACS files, 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 nonalphanumeric characters (such as #).
By an international sorting rule (ISR) that accommodates language-specific collations, including multibyte collating elements (such as ll in Spanish), diacritics (such as ô in French), and character expansions and contractions (such as ß expanding to ss in German).
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 ACS specified for that key or no ACS at all. For a file in which a key has an ACS designated for some segments but not for others, the MicroKernel Engine sorts only the segments that specify the ACS.
User-Defined ACS
To create an ACS to sort string values differently from the ASCII standard, use the format shown in the following table.
Offset
Length
Description
0
1
Signature byte. Specify 0xAC.
1
8
A unique 8-byte name that identifies the ACS to the MicroKernel Engine.
9
256
A 256-byte map. Each 1-byte position in the map corresponds to the code point having the same value as the position’s offset in the map. The value of the byte at that position is the collating weight assigned to the code point. For example, to force code point 0x61 (a) to sort with the same weight as code point 0x41 (A), place the same values at offsets 0x61 and 0x41.
Because ACS files are created using a hex editor or defined in a MicroKernel Engine application, user-defined ACS files are useful mainly to application developers and not typically created by end users.
Here is an example of a 9-byte header and a 256-byte body that represent a collating sequence named UPPER. The header appears as follows:
AC 55 50 50 45 52 20 20 20
The 256-byte body appears as follows, with the exception of the offset values in the leftmost column:
00: 00 01 02 03 04 05 06 07 08 09 0A 0B 0C 0D 0E 0F
10: 10 11 12 13 14 15 16 17 18 19 1A 1B 1C 1D 1E 1F
20: 20 21 22 23 24 25 26 27 28 29 2A 2B 2C 2D 2E 2F
30: 30 31 32 33 34 35 36 37 38 39 3A 3B 3C 3D 3E 3F
40: 40 41 42 43 44 45 46 47 48 49 4A 4B 4C 4D 4E 4F
50: 50 51 52 53 54 55 56 57 58 59 5A 5B 5C 5D 5E 5F
60: 60 41 42 43 44 45 46 47 48 49 4A 4B 4C 4D 4E 4F
70: 50 51 52 53 54 55 56 57 58 59 5A 7B 7C 7D 7E 7F
80: 80 81 82 83 84 85 86 87 88 89 8A 8B 8C 8D 8E 8F
90: 90 91 92 93 94 95 96 97 98 99 9A 9B 9C 9D 9E 9F
A0: A0 A1 A2 A3 A4 A5 A6 A7 A8 A9 AA AB AC AD AE AF
B0: B0 B1 B2 B3 B4 B5 B6 B7 B8 B9 BA BB BC BD BE BF
C0: C0 C1 C2 C3 C4 C5 C6 C7 C8 C9 CA CB CC CD CE CF
D0: D0 D1 D2 D3 D4 D5 D6 D7 D8 D9 DA DB DC DD DE DF
E0: E0 E1 E2 E3 E4 E5 E6 E7 E8 E9 EA EB EC ED EE EF
F0: F0 F1 F2 F3 F4 F5 F6 F7 F8 F9 FA FB FC FD FE FF
The header and body forming this ACS are shipped with PSQL as the file UPPER.ALT. UPPER.ALT provides a way to sort keys without regard to case. (You can define a key to be case-insensitive; even so, UPPER provides a good example when writing your own ACS.)
Offsets 0x61 through 0x7A in the example have been altered from the standard ASCII collating sequence. For standard ASCII, offset 0x61 contains a value of 0x61 (lowercase a). When a key is sorted with the UPPER ACS, the MicroKernel Engine sorts lowercase a (0x61) with the collation weight at offset 0x61: 0x41. Thus, the lowercase a is sorted as if it were uppercase A (0x41). Therefore, for sorting purposes UPPER converts all lowercase letters to their uppercase equivalents when sorting a key.
The following 256-byte body performs the same function as the UPPER.ALT body except that ASCII characters preceding the ASCII space (0x20) are now sorted after all other ASCII characters:
00: E0 E1 E2 E3 E4 E5 E6 E7 E8 E9 EA EB EC ED EE EF
10: F0 F1 F2 F3 F4 F5 F6 F7 F8 F9 FA FB FC FD FE FF
20: 00 01 02 03 04 05 06 07 08 09 0A 0B 0C 0D 0E 0F
30: 10 11 12 13 14 15 16 17 18 19 1A 1B 1C 1D 1E 1F
40: 20 21 22 23 24 25 26 27 28 29 2A 2B 2C 2D 2E 2F
50: 30 31 32 33 34 35 36 37 38 39 3A 3B 3C 3D 3E 3F
60: 40 21 22 23 24 25 26 27 28 29 2A 2B 2C 2D 2E 2F
70: 30 31 32 33 34 35 36 37 38 39 3A 5B 5C 5D 5E 5F
80: 60 61 62 63 64 65 66 67 68 69 6A 6B 6C 6D 6E 6F
90: 70 71 72 73 74 75 76 77 78 79 7A 7B 7C 7D 7E 7F
A0: 80 81 82 83 84 85 86 87 88 89 8A 8B 8C 8D 8E 8F
B0: 90 91 92 93 94 95 96 97 98 99 9A 9B 9C 9D 9E 9F
C0: A0 A1 A2 A3 A4 A5 A6 A7 A8 A9 AA AB AC AD AE AF
D0: B0 B1 B2 B3 B4 B5 B6 B7 B8 B9 BA BB BC BD BE BF
E0: C0 C1 C2 C3 C4 C5 C6 C7 C8 C9 CA CB CC CD CE CF
F0: D0 D1 D2 D3 D4 D5 D6 D7 D8 D9 DA DB DC DD DE DF
In this body, different collating weights have been assigned so that a character’s weight no longer equals its ASCII value. For example, offset 0x20, representing the ASCII space character, has a collating weight of 0x00; offset 0x41, representing the ASCII uppercase A, has a collating weight of 0x21.
To sort keys without regard to case, offsets 0x61 through 0x7A in the last example have been altered. As in the body for UPPER.ALT, offset 0x61 has the same collating weight as offset 0x41: 0x21. By having the same collating weight, offset 0x41 (A) sorts the same as offset 0x61 (a).
International Sort Rules
To specify an ACS that sorts string values using an ISO-defined, language-specific collating sequence, you must specify an ISR table name, as shown in the following examples:
Table 3 ISR Table Names  
Locale/Language
Code Page
ISR Table Name
US/English
437 MS-DOS Latin-US
850 MS-DOS Latin-1
PVSW_ENUS00437_0
PVSW_ENUS00850_0
France/French
437 MS-DOS Latin-US
850 MS-DOS-Latin-1
PVSW_FRFR00437_0
PVSW_FRFR00850_0
Germany/German
437 MS-DOS Latin-US
850 MS-DOS Latin-1
PVSW_DEDE00437_0
PVSW_DEDE00850_0
Spain/Spanish
437 MS-DOS Latin-US
850 MS-DOS Latin-1
PVSW_ESES00437_0
PVSW_ESES00850_0
Japan/Japanese
932 Shift-JIS
PVSW_JPJP00932_1
The ISR tables are installed with PSQL and based on ISO-standard locale tables. ISR tables are stored in the collate.cfg file, which is installed with the PSQL database engine. Data files can share a single ISR.
For examples, see Sample Collations Using International Sorting Rules.
Key Specification
When you create an index using either Create (14) or Create Index (31), you must provide the key specification structure.
Key Specification Block
The following table shows the data buffer structure of the key specification. Each key specification block is 16 bytes for BTRV type entry points or 24 bytes for BTRVEX types. Where two data types are given, but first is used with BTRV and the second with BTRVEX.
Field
Data Type1
Length
NIS Segment
Description
Key Position
Short Int2
2
Any offset in fixed-length part of record.
The relative position of the key within the record.
Key Length
Short Int2
2
1
The length of the key.
Key Flags
Short Int2
2
xxxxxxx1xxx1xxxx
FEDCBA9876543210
Reserved (wide)
Byte
0 or 2
N/A
Absent for BTRV type entry points, and 2 bytes for BTRVEX types. Not used for Create (14). Initialize to 0 to maintain backward compatibility.
Unique Keys
Int or Long Long Int2
4 or 8
N/A
Four bytes for BTRV type entry points or 8 bytes for BTRVEX types. Not used for Create (14). Initialize to 0 to maintain backward compatibility.
Extended Data Type
Byte or Short Int2
1 or 2
255 (0xFF)
Specify one of the extended data types. A new data type is defined for NULL_INDICATOR. One byte of type Byte for BTRV type entry points or 2 bytes of type Short Int for BTRVEX types.
Null Value (non-indexing value)
Byte
1
N/A
Specify an exclusion value for the key.
Reserved
Short Int2 or Byte
2 or 1
N/A
Two bytes for BTRV type entry points or 1 byte for BTRVEX types. Not used for Create (14). Initialize to 0 to maintain backward compatibility.
Manually Assigned Key Number
Byte or Short Int2
1 or 2
 
A key number. One byte of type Byte for BTRV type entry points or 2 bytes of type Short Int for BTRVEX types.
ACS Number
Byte
1
N/A
The Alternate Collating Sequence (ACS) number.
Reserved (wide)
Byte
0 or 1
N/A
Absent for BTRV type entry points, and 1 byte for BTRVEX types. Not used for Create (14). Initialize to 0 to maintain backward compatibility.
1Unless specified otherwise, all data types are unsigned.
2Integers must be stored in little-endian byte order, which is the low-to-high ordering of Intel-class computers.
Key Flag Values
The following table gives values for key flags in the key specification block.
 
Attribute
Binary
Hex
Description
Duplicate
0000 0000 0000 0001
0x0001
 
Modifiable
0000 0000 0000 0010
0x0002
 
Binary
0000 0000 0000 0100
0x0004
 
Null Key (All Segments)
0000 0000 0000 1000
0x0008
 
Segmented
0000 0000 0001 0000
0x0010
 
ACS
0000 0000 0010 0000
0x0020
 
Sort Order
0000 0000 0100 0000
0x0040
 
Repeating Duplicates
0000 0000 1000 0000
0x0080
 
Extended Data Type
0000 0001 0000 0000
0x0100
 
Null Key (Any Segment)
0000 0010 0000 0000
0x0200
 
Case Sensitivity (Distinct)
0000 0100 0000 0000
0x0400
 
Existing ACS
0000 1000 0000 0000
0x0800
Internal Use Only
Reserved
0001 0000 0000 0000
0x1000
 
Page Compression
0010 0000 0000 0000
0x2000
Pending Key
1000 0000 0000 0000
0x8000
Internal Use Only
The following segment-specific key flags should be on, with the NIS: SEGMENTED (0x0010), EXTENDED DATA TYPE (0x0100).
The following flags should be off: None.
The following flags are ignored: BINARY (0x0004), ACS (0x0020).
Limitations and Side Effects
True null support comes with a few limitations:
Referential Integrity. Current MicroKernel Engine supports only CASCADE and RESTRICT actions on delete, RESTRICT action on update. While SQL-92 defines CASCADE, RESTRICT, SET DEFAULT, and SET NULL on both delete and update.
Limited number of segments. The number of index segments used for key indexing will increase because each nullable column occupies two segments, while the maximum number of index segments per data file is the same, as shown in the following table.
 
Page Size (bytes)
Maximum Key Segments by File Version
8.x and earlier
9.0
9.5
13.0
512
8
8
Rounded up2
Rounded up2
1024
23
23
97
Rounded up2
1536
24
24
Rounded up2
Rounded up2
2048
54
54
97
Rounded up2
2560
54
54
Rounded up2
Rounded up2
3072
54
54
Rounded up2
Rounded up2
3584
54
54
Rounded up2
Rounded up2
4096
119
119
2043
1833
8192
n/a1
119
4203
3783
16384
n/a1
n/a1
4203
3783
1”n/a” stands for “not applicable”
2”Rounded up” means that the page size is rounded up to the next size supported by the file version. For example, 512 is rounded up to 1024, 2560 is rounded up to 4096, and so forth.
3While a 9.5 format or later file can have more than 119 segments, the number of indexes is limited to 119.
Database URIs
A key concept in using the Btrieve Login API or the implicit login functionality via the Create or Open functions is the database Uniform Resource Identifier (URI). The URI provides a syntax to describe the address of a database resource on a server.
This topic describes the syntax and semantics of the URIs used in Btrieve APIs.
Syntax
URIs use the following syntax:
access_method://user@host/dbname?parameters
The special characters separating the elements are required, even if an element is omitted.
 
 
Table 4 Elements of a Database URI  
Element
Definition
access_method
Method used to access the database. This element is required. Currently, only btrv is supported.
user@
Optional user name. The password for the user is specified in parameters if needed. An at sign must be used to delimit the user name even if no host is specified.
host
Server where the database is located. The local machine is assumed if host is not specified. Host can be a machine name, an IP address, or the keyword “localhost.”
Note: The host element is required if the URI is accessing a database on Linux, macOS, or Raspbian.
dbname
Optional database name, which corresponds to an entry in the DBNAMES.CFG file for the PSQL database engine. If no database name is specified, then the default database DefaultDB is assumed.
parameters
Additional, optional parameters, which are delimited by an ampersand character.
table=table – SQL table name. The table name must exist in DDFs for the database.
dbfile=file – Name of a file whose location is relative to the data file location entry in DBNAMES.CFG for the current database. Since a relative location is specified, the use of drive letters, full or UNC paths is not permitted. The database engine resolves the full file name. The PSQL client does not manipulate file in any manner. Embedded spaces are permitted and are escaped by the database engine.
file=file – Data file name. The PSQL client normalizes file and replaces the input name with the resultant fully qualified UNC name in the URI before sending the request to the database engine. Drive letters may be used and are interpreted as client-side drives. Use of a UNC path is also permitted, as are embedded spaces.
pwd=password – Clear text password. The PSQL client changes clear text passwords into encrypted passwords before transmission.
prompt=[ yes | no ] – Tells the PSQL client how the application wants to handle the login dialog box pop-up when the database engine returns status 170 (Login failed due to missing or invalid user name) or 171 (Login failed because of invalid password). If prompt=yes is specified, the requester always displays the login dialog even if the Prompt for Client Credentials setting is Off. If prompt=no is specified, the requester assumes that the application wants to receive the status 170/171 directly and does not want the requester to display the dialog. This is useful if you want your applications to handle the prompting for credentials in response to any 170 or 171 status codes. Values other than “yes” or “no” are ignored and the requester displays the login dialog based on the Prompt for Client Credentials setting. This option is ignored on Linux and macOS systems that are acting in the role of a client.
Parameter Precedence
The database engine enforces a precedence level on the parameters file, table, and dbfile when more than one of them is specified in a URI. That is, after parsing, the database engine leaves the parameter with the highest precedence. If two or more have the same precedence, the last parameter in the URI is returned after parsing.
The order of precedence from highest to lowest is file, table, and dbfile.
Precedence Examples
 
Initial URI String
Parsed URI String
btrv:///?file=MyFile.btr&table=MyTable&dbfile=DataFile.btr
btrv:///?file=MyFile.btr
btrv:///?table=MyTable&dbfile=DataFile.btr
btrv:///?table=MyTable
btrv:///?dbfile=DataFile.btr&file=MyFile.btr
btrv:///?file=MyFile.btr
btrv:///?dbfile=DataFile.btr
btrv:///?dbfile=DataFile.btr
btrv:///?file=FileOne&file=FileTwo
btrv:///?file=FileTwo
btrv:///?table=TableOne&table=TableTwo&file=MyFile.btr
btrv:///?file=MyFile.btr
Special Characters
As with any URI, certain nonalphanumeric characters have special significance in the URI syntax. If you wish to use one of these characters within one of the elements in the URI, you must use an escape sequence to identify the character as actual text rather than a special character. An escape sequence is simply another special character or character combination that represents the plain text equivalent of a special character.
The table below shows the special characters supported by the MicroKernel Engine URI syntax, and their associated escape sequences (represented by the percent sign and the hexadecimal value for the specified character).
Table 5 Special Characters in a Database URI
Character
Meaning
Hexadecimal Value
/
Separates directories and subdirectories
%2F
?
Separates the base URI from its associated parameters
%3F
%
Specifies a special character
%25
#
Indicates a bookmark or anchor
%23
&
Separates the parameters in the URI
%26
" "
Indicates the entire content enclosed by the double quotes
%22
=
Separates a parameter and its value
%3D
space
No special meaning, but is reserved.
%20
:
Separates host from port (reserved, but not currently supported). The colon is also used in some IPv6 addresses. See IPv6.
%3A
Although the space character is reserved in the URI specification, it can be used without quotes and without escape sequencing because it is not used as a delimiter. The other symbols in the table above are used as delimiters and therefore must be escaped.
Examples
This section shows examples of URIs using escape sequences to identify special characters used within the field values.
Table 6 URI Examples with Escape Sequences
URI
Meaning
btrv://Bob@myhost/demodata?pwd= This%20Is%20Bob
User “Bob” with password “This Is Bob.”
btrv://Bob@myhost/demodata?pwd= This Is Bob
User “Bob” with password “This Is Bob.”
btrv://myhost/mydb?file=c:/data%20files/pvsw/mydb/c.mkd
The %20 represents a space character. File to be opened is “C:\data files\pvsw\mydb\c.mkd”
btrv://Bob@myhost/demodata?pwd= mypass%20Is%20%26%3f
User Bob with password “mypass Is &?”
Remarks
Note that an empty user name or password is different than no user name or password. For example, btrv://@host/ has an empty user name, while btrv://host/ has no user name, and btrv://sam@host/?pwd= has a user name sam with an empty password.
Some URIs allow the use of user:password syntax. However, the password is then transmitted as clear text. To prevent the transmission of the password as clear text, the PSQL database URI ignores the password if one is provided using the user:password syntax. Use the pwd= parameter to provide a password, which the PSQL client changes into an encrypted password before transmission.
Some URIs allow for server based naming authority with a syntax of user@host:port. The PSQL database URI does support specifying a port element.
Examples
A URL (Uniform Resource Locator) is simply the address of a file or resource on the Internet. The database URI uses the same notion to address a database on a server. This topic gives examples of the syntax and semantics of URIs for PSQL databases, particularly using the MicroKernel Engine access.
Table 7 Examples of the MicroKernel Engine URIs 
Example
Meaning
btrv://myhost/demodata
Database demodata on server myhost. The server operating system can be any of the ones supported by PSQL
btrv:///demodata
Database demodata on the local machine. The local machine is running a Windows operating system. The host element is required for Linux and macOS systems (see the example above).
btrv://Bob@myhost/demodata
User Bob without a password accessing database demodata on server myhost.
btrv://Bob@myhost/mydb?pwd=a4
User Bob with password “a4” accessing database mydb on server myhost.
btrv://myhost/demodata?table=class
Unspecified user accessing database table named class in database “demodata” on server myhost.
btrv://myhost/?table=class
Unspecified user accessing database table named class in default database (DefaultDB) on server myhost.
btrv://myhost/mydb?file=f:/mydb/a.mkd
Unspecified user accessing the data file F:/mydb/a.mkd as seen by the client using the security credentials of the database mydb on server myhost.
Note that the client normalizes drive F, which means that it must be mapped at the client to server myhost.
btrv://mydb?file=c:/mydb/a.mkd
Unspecified user accessing the data file C:/mydb/a.mkd under database mydb on the local machine.
Drive C is a local drive on the local machine. The local machine is running a Windows operating system.
btrv://myhost/demodata?dbfile=class.mkd
Unspecified user accessing data file class.mkd within one of the data directories defined for the demodata database on server myhost. Because the file name is specified with dbfile= (and not file=) the client requester does not normalize class.mkd. Only the server engine normalizes class.mkd into a full path.
IPv6
The URI and UNC syntax does not allow certain special characters, such as colons. Since raw IPv6 addresses use colons, different methods of handling UNC paths and URI connections are available. PSQL supports IPv6-literal.net Names and Bracketed IPv6 Addresses.
See Drive-based Formats in Getting Started with PSQL.
Double-Byte Character Support
PSQL accepts Shift-JIS (Japanese Industrial Standard) encoded double-byte characters in file paths. (Shift-JIS is an encoding technique commonly used for Japanese computers.) In addition, you can store Shift-JIS double-byte characters in records and sort them using the Japanese ISR table described in International Sort Rules. Other multi-byte characters can be stored in records, although ISR tables are currently not available to sort these records according to culturally correct rules. Your use of double-byte characters does not affect the operation of PSQL applications.
Record Length
All records contain the record length, a fixed-length portion which must be large enough to contain all the data (including keys) for a record, plus the overhead required to store a record on a data page.
See Record Overhead in Bytes Without Record Compression and Record Overhead in Bytes With Record Compression for how many bytes of overhead you must add to the logical record length to obtain a physical record length.
The following table lists the maximum record size for fixed-length records.
Table 8 Maximum Record Size in Bytes for Fixed-length Records
File version
Without System Data1
With System Data2
7.x
4088 (4096 - 8)
4080 (4088 - 8)
8.x
4086 (4096 - 10)
4078 (4086 - 8)
9.0 through 9.4
8182 (8192 - 10)
8174 (8182 - 8)
9.5
16372 (16384 - 12)
16364 (16372 - 8)
13.0
16364 (16384 - 20)
16356 (16384 - 8)
1The page overhead and the record overhead are subtracted from the maximum page size to determine the maximum record size. The per record overhead is 2 bytes for each file format.
2System data requires an additional overhead of 8 bytes.
Note that the database engine turns on data compression for the file if the file uses system data and the record length exceeds the limit shown in the table above.
Optionally, the records in a file can contain a variable-length portion. A variable-length record has a fixed-length portion that is the same size in every record and a variable-length portion that can be a different size in each record. When you create a file that uses variablelength records, the fixed-length amount is the minimum length of each record; you do not define the maximum record length.
Theoretically, the maximum length of variable-length records is limited only by the MicroKernel Engine file size limit: up to terabytes for the 13.0 version file format, 256 GB for 9.5 files, 128 GB for earlier 9.x versions, and 64 GB for other earlier versions. In reality, the maximum is limited by such factors as the operating system, system resources, and the record access method you choose. If you retrieve, update, or insert whole records, then the data buffer length parameter, because it is a 16-bit unsigned integer, limits the record length to 65535.
A data buffer is a MicroKernel Engine function parameter that you use to transfer various information depending on the operation being performed. A data buffer can contain all or part of a record, a file specification, and so forth. See Table 11 under Designing a Database for more information on data buffers.
Note The total bytes of data plus internal header information cannot exceed 64 KB (0x10000) bytes. The MicroKernel Engine reserves 1024 (0x400) bytes for internal purposes, meaning you can have 64512 (0xFC00) bytes of data.
If your file uses very large records, consider using variable-tail allocation tables (VATs) in the file. A VAT, which is implemented as a linked list, is an array of pointers to the variable-length portion of the record. VATs accelerate random access to portions of very large records. Some examples of very large records are binary large objects (BLOBs) and graphics.
For files that contain very large variable-length records, the MicroKernel Engine splits the record over many variable pages and connects the pages using a linked list called a variable tail. If an application uses chunk operations to access a part of a record and that part of the record begins at an offset well beyond the beginning of the record itself, the MicroKernel Engine may spend considerable time reading the variable-tail linked list to seek that offset. To limit such seek time, you can specify that the file use VATs. the MicroKernel Engine stores the VAT on variable pages. In a file containing a VAT, each record that has a variable-length portion has its own VAT.
The MicroKernel Engine uses VATs not only to accelerate random access, but also to limit the size of the compression buffer used during data compression. If your files use data compression, you may want to use VATs in the file.
Data Integrity
The following features support concurrent access while ensuring the integrity of your files in a multi-user environment:
Record Locks
Transactions
Transaction Durability
System Data
Shadow Paging
Backing Up Your Files
Record Locks
Applications can explicitly lock either one record at a time (single record lock) or multiple records at once (multiple record lock). When an application specifies a record lock, the application can also apply a wait or no-wait condition. When an application requests a no-wait lock on a record that is currently unavailable (either the record is already locked by another application or the whole file is locked by an exclusive transaction), the MicroKernel Engine does not grant the lock.
When an application requests a wait lock on a record that is unavailable, the MicroKernel Engine checks for a deadlock condition. You can configure the MicroKernel Engine to wait before returning a deadlock detection status code. Doing so improves performance in multi-user situations by allowing the MicroKernel Engine to wait internally, rather than forcing the application to retry the operation.
Transactions
If you have a number of modifications to make to a file and you must be sure that either all or none of those modifications are made, include the operations for making those modifications in a transaction. By defining explicit transactions, you can force the MicroKernel Engine to treat multiple operations as an atomic unit. Other users cannot see the changes made to a file until the transaction ends. The MicroKernel Engine supports two types of transactions: exclusive and concurrent.
Exclusive Transactions
In an exclusive transaction, the MicroKernel Engine locks the entire data file when you insert, update, or delete a record in that file. Other applications (or other instances of the same application) can open the file and read its records, but they cannot modify the file. The file remains locked until the application ends or aborts the transaction.
Concurrent Transactions
In a concurrent transaction, the MicroKernel Engine can lock either records or pages in the file, depending on the operation you are performing. The MicroKernel Engine enables multiple applications (or multiple instances of the same application) to perform modifications inside concurrent transactions in different parts of the same file simultaneously, as long as those modifications do not affect other previously locked portions of the file. The record or page remains locked until the application ends or aborts the transaction. Concurrent transactions are available only for 6.0 and later files.
Exclusive vs. Concurrent
Clients can still read records from a file even if a concurrent transaction has locked the requested record. However, these clients cannot be operating from within an exclusive transaction. Also, they cannot apply a lock bias to their read operation if the file containing the requested record is currently locked by an exclusive transaction, or if a concurrent transaction has locked the requested record.
When a client reads a record using an exclusive lock, the MicroKernel Engine locks only the individual record; the rest of the page on which the record resides remains unlocked.
Note Simply opening a file from within a transaction does not lock any records, pages, or files. In addition, the MicroKernel Engine does not lock files that you flag as read-only or files that you open in read-only mode.
When you use exclusive transactions, the MicroKernel Engine causes other clients to implicitly wait on the locked file unless the No Wait bias is added to the Begin Transaction (19 or 1019) operation. The application seems to hang during this implicit wait state. If these exclusive transactions are short lived, you may not notice the wait time. However, the overall effect of many clients involved in implicit waits results in using a large amount of CPU time. Additionally, multiple position blocks in the same file share locks.
Exclusive transactions involved in implicit waits also waste network bandwidth. The MicroKernel Engine waits about one second before returning to the requester. The requester recognizes a wait condition and returns the operation to the MicroKernel Engine. Thus, exclusive transactions also can cause extra network traffic.
The amount of extra CPU cycles and network traffic increase exponentially with the number of clients waiting on the locked file combined with the length of time involved in the exclusive transaction.
Transaction Durability
You can configure the MicroKernel Engine to guarantee Transaction Durability and atomicity by logging all operations to a single transaction log. Transaction durability is the assurance that the MicroKernel Engine finishes writing to the log when a client issues the End Transaction operation and before the MicroKernel Engine returns a successful status code to the client. Atomicity ensures that if a given statement does not execute to completion, then the statement does not leave partial or ambiguous effects in the database, thereby ensuring the integrity of your database by keeping it in a stable state.
If you want atomicity without the overhead of Transaction Durability, you can use Transaction Logging feature in PSQL V8 and later releases. See Advanced Operations Guide for more information on Transaction Logging.
In a default installation, the transaction log is in C:\ProgramData\Actian\PSQL\logs. The log must exist on the same machine as the PSQL engine. You can change the location using the transaction log directory configuration option in PCC by right-clicking the MicroKernel Engine, selecting Properties, and clicking Directories.
The MicroKernel Engine maintains the transaction log in one or more physical files, called log segments. The MicroKernel Engine starts a new log segment when the current log segment reaches a user-defined size limit, no files have pending changes, and the MicroKernel Engine has finished a system transaction.
All transaction log segments have a .log extension. The MicroKernel Engine names log segments with consecutive, 8-character hexadecimal names, such as 00000001.log, 00000002.log, and so on.
To improve performance on specific files, you can open a file in Accelerated mode. (Version 6.x MicroKernel Engine accepted Accelerated open requests, but interpreted them as Normal open requests.) When you open a file in Accelerated mode, the MicroKernel Engine does not perform transaction logging on the file.
Note If a system failure occurs, there will be some log segments that are not deleted. These segments contain changes that did not get fully written to the data files. Do not delete these log segments. You do not know which files are represented in these log segments. No action is necessary because those data files will automatically get rolled forward the next time they are opened.
System Data
PSQL uses a 7.x transaction log file format. In order for the MicroKernel Engine to log transactions on a file, the file must contain a log key, which is a unique (non-duplicatable) key that the MicroKernel Engine can use to track the record in the log. For files that have at least one unique (non-duplicatable) key, the MicroKernel Engine uses one of the unique keys already defined in the file.
For files that do not have any unique keys, the MicroKernel Engine can include system data upon file creation. The MicroKernel Engine includes system data in a file only if the file uses the 7.x file format or later and if at the time the file is created, the MicroKernel Engine is configured to include system data in files upon creation. System data is defined as an 8-byte binary value with the key number 125. Even if a file has a unique, user-defined key, you may want to use system data (also called the system-defined log key), to protect against a user dropping an index.
The database engine turns on data compression for the file if the file uses system data and the record length exceeds the limit shown in Table 8.
The MicroKernel Engine adds system data only at file creation. If you have existing files for which you want to add system data, turn on the System Data configuration option, then use the Rebuild utility.
Note When the MicroKernel Engine adds system data, the resulting records may be too large to fit in the file’s existing page size. In such cases, the MicroKernel Engine automatically increases the file’s page size to the next accommodating size.
Shadow Paging
The MicroKernel Engine uses shadow paging to protect 6.0 and later files from corruption in case of a system failure. When a client needs to change a page (either inside or outside a transaction), the MicroKernel Engine selects a free, unused physical location in the data file itself and writes a new page image, called a shadow page, to this new location. During a single MicroKernel Engine operation, the MicroKernel Engine might create several shadow pages all the same size as the original logical pages.
When the changes are committed (either when the operation is complete or the transaction ends), the MicroKernel Engine makes the shadow pages current, and the original pages become available for reuse. The MicroKernel Engine stores a map, which is the Page Allocation Table, in the file to keep track of the valid and reusable pages. If a system failure occurs before the changes are committed, the MicroKernel Engine does not update the PAT, thereby dropping the shadow pages and reverting to using the current pages, which are still in their original condition.
Note This description simplifies the shadow paging process. For improved performance, the MicroKernel Engine does not commit each operation or user transaction individually, but groups them in a bundle called a system transaction.
When a client operates inside a transaction, the shadow pages corresponding to the original logical pages are visible only to that client. If other clients need to access the same logical pages, they see the original (unchanged) pages – that is, they do not see the first client’s uncommitted changes. Shadow paging thus enhances reliability because the original file is always valid and internally consistent.
Backing Up Your Files
Backing up your files regularly is an important step in protecting your data.
For information on backing up your files, please see the following topic in Advanced Operations Guide: Logging, Backup, and Restore.
Event Logging
Event logging is a feature in PSQL that uses a log file to store informational, warning, and error messages from the MicroKernel Engine, SQL interface, and utility components.
See Reviewing Message Logs in Advanced Operations Guide for details.
Performance Enhancement
The MicroKernel Engine provides the following features for enhancing performance:
System Transactions
Memory Management
Page Preallocation
Extended Operations
System Transactions
To gain better performance and to aid data recovery, the MicroKernel Engine includes one or more committed operations (both transactional and non-transactional) into a bundle of operations called a system transaction. The MicroKernel Engine creates a system transaction bundle for each file. A system transaction can contain operations and user transactions from one or more clients running on the same engine.
Note Do not confuse system transactions with exclusive or concurrent transactions. Throughout this manual, the term transaction refers to an exclusive or concurrent transaction (also known as a user transaction). User transactions affect how changes become incorporated into the pages in cache, while system transactions affect how dirty pages in cache become a part of the files on disk. The MicroKernel Engine controls the initiation and process of system transactions.
Both user transactions and system transactions are atomic. In other words, they happen in such a way that either all or none of the changes occur. If a system failure occurs, the MicroKernel Engine recovers all files involved in the failed system transaction as it reopens the files. All changes made during a failed system transaction (that is, all operations to a file by all clients on that engine since the last completed system transaction) are lost; however, the file is restored to a consistent state, enabling the operations to be attempted again after resolving the cause of the system failure.
PSQL guarantees transaction durability for all loggable files except those opened in Accelerated mode. (A file can be logged if it contains at least one unique, or non-duplicatable, key. The key can be system-defined.) Transaction durability is the assurance that before the MicroKernel Engine returns a successful status code to the client for an End Transaction it finishes writing to the transaction log. When you open a file in Accelerated mode, the MicroKernel Engine does not log the file; therefore, the MicroKernel Engine does not log entries to the file. Therefore, the MicroKernel Engine cannot guarantee transaction durability for that file.
After the MicroKernel Engine rolls back a file’s system transaction, it replays the log when it next opens the file. Doing so restores those committed operations that were stored in the log but were not written to the file because of the system transaction rollback.
Each system transaction consists of two phases: preparation and writing.
Preparation Phase
During the preparation phase, the MicroKernel Engine executes all operations in the current system transaction, but writes no pages to the files. The MicroKernel Engine reads uncached pages from the files as needed and creates new page images only in cache.
Any of the following actions can trigger the end of the preparation phase, which marks the beginning of the writing phase:
The MicroKernel Engine reaches the Operation Bundle Limit.
The MicroKernel Engine reaches the Initiation Time Limit.
The ratio of pages prepared for writing to the total number of cache pages reaches a system threshold.
Note Generally, the preparation phase ends after a completed MicroKernel Engine operation. However, it is possible that the time limit or the cache threshold could be reached during an incomplete user transaction; the MicroKernel Engine switches to the writing phase, regardless.
Writing Phase
During the writing phase, the MicroKernel Engine writes to disk all pages prepared in the preparation phase. It first writes all data, index, and variable pages. These are actually shadow pages. While these are being written, the consistency of the files on disk remains the same.
However, the critical part of the system transaction occurs while the PAT pages are being written, because they point to the shadow page as the current page. To protect this phase, the MicroKernel Engine writes a flag in the FCR. When all PAT pages are written, the final FCR is written and the file is now consistent. If a system failure happens during this phase, the MicroKernel Engine recognizes it the next time the file is opened and rolls the file back to the previous state. Then, all durable user transactions in the transaction log file will be implemented in the file.
Frequency of System Transactions
Less Frequent
Doing system transactions less often provides a performance benefit to most configurations. These include client/server, single engine workstation and multi-engine workstation environments where files are opened exclusively.
When the MicroKernel Engine initiates system transactions less often, dirty pages, or pages that need to be written, stay in memory longer. If the application is doing a lot of change operations, these pages might get updated multiple times before being written to disk. This means fewer disk writes. In fact, the most efficient engine is one that writes only when it must.
There are three limits that, when reached, can cause the system transactions to be initiated: Operation Bundle Limit, Cache Size, and Initiation Time Limit. When any of these limits are reached, the MicroKernel Engine initiates a system transaction. See Advanced Operations Guide for more information about these settings.
The best way to do system transactions less often is to set the Operation Bundle Limit and Initiation Time Limit to higher values. You can also increase the size of the cache.
More Frequent
A disadvantage of causing the MicroKernel Engine to perform fewer system transactions is more data in machine memory at any point in time that needs to be written to disk. If a system failure occurs, such as a power outage, more data is lost. Although the MicroKernel Engine is designed to keep files in a consistent usable state, that state may not include the most recent changes. Of course, the use of user transactions with transaction durability will minimize this risk. You should carefully consider the risks of decreasing the frequency of system transactions versus the performance gains.
For example, if your application is using a workstation engine to update a remote file over a slow or non-reliable network connection, you should perform system transactions often so that changes are put onto disk at soon as possible.
Memory Management
The cache is an area of memory the MicroKernel Engine reserves for buffering the pages that it reads. When an application requests a record, the MicroKernel Engine first checks the cache to see if the page containing that record is already in memory. If so, the MicroKernel Engine transfers the record from the cache to the application’s data buffer. If the page is not in the cache, the MicroKernel Engine reads the page from the disk into a cache buffer before transferring the requested record to the application. The MicroKernel Engine cache is shared by local clients and used across multiple operations.
If every cache buffer is full when the MicroKernel Engine attempts to transfer a new page into memory, a least-recently-used (LRU) algorithm determines which page in the cache the MicroKernel Engine overwrites. The LRU algorithm reduces processing time by keeping the most recently referenced pages in memory.
When the application inserts or updates a record, the MicroKernel Engine first makes a shadow image of the corresponding page, modifies the page in the cache, and then writes that page to disk. The modified page remains in the cache until the LRU algorithm determines that the MicroKernel Engine can overwrite the image of that page in cache with a new page.
Generally, a larger cache improves performance because it enables more pages to be in memory at a given time. The MicroKernel Engine enables you to specify the amount of memory to reserve for the I/O cache buffers. To determine this amount, consider the application’s memory requirements, the total memory installed on your computer, and the combined size of all files that all concurrent PSQL applications will access. The configuration setting for this cache is “Cache Allocation Size.”
In PSQL V8 and later releases, a secondary dynamic L2 cache is also available. The configuration setting for this dynamic cache is “Max MicroKernel Engine Memory Usage.” See Advanced Operations Guide for more information on configuring these settings.
Note Increasing cache above the available physical memory can actually cause a significant performance decrease because part of the cache memory in virtual memory will be swapped out onto disk. We recommend that you set the MicroKernel Engine cache to about 60 percent of available physical memory after the operating system is loaded.
Page Preallocation
Page preallocation guarantees that disk space is available when the MicroKernel Engine needs it. You can enhance the speed of file operations if a data file occupies a contiguous area on the disk. The increase in speed is most noticeable on very large files. For more information about this feature, see Page Preallocation.
Extended Operations
Using the extended operations – Get Next Extended (36), Get Previous Extended (37), Step Next Extended (38), Step Previous Extended (39), and Insert Extended (40) – can greatly improve performance. Extended operations can reduce the number of MicroKernel Engine requests by 100 to 1 or more, depending on the application. These operations have the ability to filter the records returned so that records not needed by the application are not sent to it. This optimization technique has the best results in client-server environments that have to send data back and forth over a network.
See Multi-Record Operations for detailed information on these operations.
Disk Usage
The MicroKernel Engine provides the following features for minimizing disk usage requirements:
Free Space List
Index Balancing
Data Compression
Blank Truncation
Free Space List
When you delete a record, the disk space it formerly occupied is put on a Free Space List. When you insert new records, the MicroKernel Engine uses pages on the Free Space List before creating new variable pages. The Free Space Threshold tells the MicroKernel Engine how much free space must remain on a variable page in order for that page to appear on the Free Space List.
This method of reusing free space eliminates the need to reorganize files to reclaim disk space. Also, the Free Space List provides a means of reducing the fragmentation of variable-length records across several pages. A higher Free Space Threshold reduces fragmentation at the cost of requiring more disk space for the file.
Index Balancing
When an index page becomes full, the MicroKernel Engine (by default) automatically creates a new index page and moves some of the values from the full index page to the new index page. Turning on the Index Balancing option lets you avoid creating a new index page every time an existing one becomes full. With index balancing, the MicroKernel Engine looks for available space in sibling index pages each time an index page becomes full. The MicroKernel Engine then rotates values from the full index page onto the pages that have space available.
Index balancing increases index page utilization, results in fewer pages, and produces an even distribution of keys among nodes on the same level, thus enhancing performance during read operations. However, using this feature also means that the MicroKernel Engine requires extra time to examine more index pages and may require more disk I/O during write operations. Although the exact effects of balancing indexes vary in different situations, performance on write operations typically degrades by about 5 to 10 percent if you use index balancing.
Index Balancing impacts the performance of a “steady-state” file by making the average change operation a little slower while making the average get operation faster. It does this by fitting more keys in an average index page. A normal index page may be 50 to 65 percent full where an index balanced page is 65 to 75 percent full. This means there are less index pages to search.
If you create indexes with Create Index (31), the index pages will be nearly 100 percent full, which optimizes these files for reading not writing.
Note You can also specify index balancing on a file-by-file basis by setting the Index Balanced File bit in the File Flag field in the file.

If you enable the Index Balancing option, the MicroKernel Engine performs index balancing on every file, regardless of the balanced file flag specification that the application may have set. For a description of how to specify the Index Balancing configuration option, see PSQL User's Guide.
Data Compression
With data compression, the MicroKernel Engine compresses file records before inserting or updating them and uncompresses the records when it retrieves them. Because the final length of a compressed record cannot be determined until the record is written to the file, the MicroKernel Engine always designates a compressed file as a variable-record-length file. However, if you use data compression on a fixed-record-length file, the MicroKernel Engine prevents insert and update operations from producing a record that is longer than the fixed-record length specified for the data file.
Because the MicroKernel Engine stores compressed records as variablelength (even if you created the file as not allowing variable-length records), individual records may become fragmented across several data pages if you perform frequent insertions, updates, and deletions. This fragmentation can result in slower access times, because the mMicroKernel EngineWy need to read multiple file pages to retrieve a single record. However, data compression can also result in a significant reduction of the disk space needed to store records that contain many repeating characters. the cMicroKernel EngineWmpresses five or more of the same contiguous characters into 5 bytes.
For more information on this feature, see Record Compression.
Blank Truncation
Blank truncation conserves disk space. It is applicable only to files that allow variable-length records and that do not use data compression. For more information on this feature, see Blank Truncation.