This section provides formulas and guidelines for designing a Btrieve database. The topics covered should help you set up security and reduce the amount of disk space your files use. This section discusses the following topics:
When you create a file, you must specify the logical record length and the file's page size. The following sections help you determine these values. The logical record length is the number of bytes of fixed-length data you will allow a record to hold in the Btrieve file you are creating. To obtain this value, calculate how many bytes of data you need to store in the fixed-length portion of each record. For example, Table 15 shows how the bytes in each fixed-length portion of the record are added together to obtain the logical record length.
Determining Record and Page Size
Logical Record Length
Table 15. Calculating Logical Record Length for the Sample Record
| Value | Number of Bytes for Value |
|---|---|
Last name |
25 |
First name |
25 |
Middle initial |
1 |
Employee ID |
4 |
Street address |
30 |
City |
25 |
State |
11 |
Phone |
12 |
Rate of pay |
4 |
Department |
24 |
Permanent/Temporary |
1 |
Miscellaneous |
0 |
Logical Record Length |
162 |
The Miscellaneous portion of the record is variable length and contains notes about the employee (commendations, bonuses, and so on). Because Miscellaneous is variable length, it has no fixed length. Therefore, you do not need to reserve any bytes for its contents in each record.
If any record actually has information to store in Miscellaneous, that information is stored on variable pages and need not be included in the calculations for logical record length.
NOTE: For files that allow variable-length records, logical record length refers only to the fixed-length portion of the record.
All pages in a Btrieve data file are the same size. Therefore, when you determine what size the pages should be in your file, you must choose a size that meets the following criteria:
Once you have these values, you can select a page size that best fits your file. To find the smallest page size that will still hold the largest key definition in your file, you must perform the following tasks: Determine how many bytes the largest key in the file will require on an index page. This value is the sum of the user-specified key length and the overhead that Btrieve requires to store the key. The user-specified key length is a given quantity. To calculate the number of overhead bytes required, perform the following substeps:
Multiply the value you obtained in Step 1 by 8. (Btrieve requires room for a minimum of 8 keys on a page.) Add 12 to the value obtained in Step 2. Select the first page size that is equal to or greater than the value you obtained in Step 3. NOTE: Remember that the page size you select in Step 4 must accommodate the size of any keys created after file creation. Using the example record layout from Table 15, consider that your file will have 4 keys, as illustrated in Table 16.
Page Size
Minimum Page Size for Index Pages
Table 16. Keys Defined for the Sample Record
| Key Name | Size in Bytes | Number of Segments | Duplicatable? |
|---|---|---|---|
Last Name |
25 |
1 |
Yes |
Employee ID |
4 |
1 |
No |
Department |
24 |
1 |
Yes |
Permanent/Temporary |
1 |
1 |
Yes |
Given these values, you can calculate the minimum page size for your Btrieve file's index pages as follows:
In designing the file, you have decided that the last name key will allow duplicate values. At this point, you decide that those duplicate values should be stored as linked duplicates. Therefore, you would add 12 bytes for overhead to the user-specified key length of 25, giving you a total of 37 bytes.
Because the minimum Btrieve page size is 512 bytes, you would select 512.
Although you have not eliminated any possible page sizes in this example, you must verify the minimum page size for index pages before choosing the optimum page size for data pages. Failure to do so may result in selecting a data page size that is incompatible with the allowable index page size.
Before you can determine the optimum size of your Btrieve file's data pages, you must first calculate the file's physical record length. The physical record length is the sum of the logical record length and the overhead required to store a record on a data page of the Btrieve file.
Btrieve always stores a minimum of 2 bytes of overhead information in every record (as a usage count for that record). Btrieve also stores an additional number of bytes in each record, depending on how you define the records in your Btrieve file.
Table 17 shows how many bytes of overhead you must add to the logical record length to obtain the physical record length (based on how you define the records for your Btrieve file).
Table 17. Amount of Overhead Added to the Logical Record Length of the Sample Record
| Type of Overhead | Bytes |
|---|---|
Record usage count |
2 (only for files using Btrieve v6.x format |
Linked-duplicatable keys |
8 (per key) |
Reserved duplicate pointers |
8 (per pointer) |
Variable-length record pointers |
4 (if file allows variable-length records) |
Blank truncation |
2 (if file allows blank truncation but does not use VATs), or 4 (if file allows blank truncation and uses VATs) |
Record length |
4 (if file uses VATs) |
IMPORTANT: When calculating the number of bytes for duplicatable keys, include only 8 bytes for each linked-duplicatable key. Btrieve does not allocate duplicate pointer space for keys defined as repeating duplicatable at creation time.
Using the sample record defined in Table 8, assume that you are creating a Btrieve file with the following attributes, or characteristics:
To obtain the physical record length for this Btrieve file, you would add the values shown in Table 18.
Table 18. Obtaining the Physical Record Length of the Sample File
Logical record length |
162 |
Record usage count |
2 |
3 linked-duplicatable keys (3*8) |
24 |
Variable-length record pointer |
4 |
Blank truncation |
0 |
Physical record length |
194 |
Remember that for files with variable-length records, the logical record length refers only to the fixed-length portion of the record.
NOTE: For files with compressed records, the data page format is different. (Compressed records are discussed in Data Compression.) A compressed record's entry on a data page consists of 7 bytes of overhead information, pointers for duplicate keys, reserved duplicate pointers (if any), and a 4-byte record-length field (if the file uses VATs). The record's user-defined data is compressed and stored on variable pages.
Using the physical record length that you calculated in the preceding paragraphs, you now can determine the file's optimum page size for data pages.
Btrieve stores as many records as possible in each data page of the Btrieve file. However, it will not break the fixed-length portion of a record across pages. Also, in each data page, Btrieve stores six bytes of overhead information. You must account for this additional overhead when determining the data page size.
A Btrieve file will contain unused space if the page size you choose, minus six bytes (for overhead information), is not an exact multiple of the physical record length.
To optimize your file's use of disk space, select a page size that can buffer your records with the least amount of unused space. Page size must always be a multiple of 512 bytes, up to 4,096 bytes. Larger page sizes usually result in more efficient use of disk space.
Consider the example from Table 18, in which the physical record length added up to 194 bytes. Table 19 shows how many records can be stored on a page and how many bytes of unused space will remain on a page for each possible page size.
Table 19. Optimizing the Sample File's Use of Disk Space
| Page Size | Records per Page | Unused Bytes |
|
|---|---|---|---|
512 |
2 |
118 |
( 512 - 6 - ( 2 * 194)) |
1024 |
5 |
48 |
(1024 - 6 - ( 5 * 194)) |
1536 |
7 |
172 |
(1536 - 6 - ( 7 * 194)) |
2048 |
10 |
102 |
(2048 - 6 - (10 * 194)) |
2560 |
13 |
32 |
(2560 - 6 - (13 * 194)) |
3072 |
15 |
156 |
(3072 - 6 - (15 * 194)) |
3584 |
18 |
86 |
(3584 - 6 - (18 * 194)) |
4096 |
21 |
16 |
(4096 - 6 - (21 * 194)) |
As the table indicates, if you select a page size of 512, only 2 records can be stored per page and 118 bytes of each page will be unused. However, if you select a page size of 4,096, 21 records can be stored per page and only 16 bytes of each page will be unused.
NOTE: Btrieve requires that each index page in the file be large enough to hold at least eight keys. If an index page is not large enough, you must either increase the file's page size or decrease the key length. For more information, see Minimum Page Size for Index Pages.
You can estimate the number of pages, and therefore the number of bytes required to store a Btrieve file, with the formulas described in the following paragraphs. However, when using the formulas, keep in mind that at any given moment, they only approximate file size because of the way Btrieve dynamically manipulates pages. NOTE: The following discussion and the formulas for determining file size do not apply to files that use data compression, because the record length for those files depends on the number of repeating characters in each record.
While the formulas are based on the maximum storage required, they assume that only one Btrieve task is updating or inserting records into the file at a time. File size increases if more than one task updates or inserts records into the file during simultaneous concurrent transactions. The formulas also assume that no records have yet been deleted from the file. Even if you delete half the records in a file, the file remains the same size. Btrieve does not deallocate the pages that were occupied by the deleted records. Rather, Btrieve re-uses them as new records are inserted into the file (before allocating new pages). If the final outcome of your calculations contains a fractional value, round the number to the next highest whole number. To estimate the size of your Btrieve file, perform the following steps: Calculate the number of data pages, using the following formula: To find the physical record length, use the following formula: IMPORTANT: When calculating the number of bytes for duplicatable keys, include only 8 bytes for each linked-duplicatable key. Btrieve does not allocate duplicate pointer space for keys defined at creation time as repeating duplicatable.
Calculate the number of index pages for each defined key using one of the following formulas:
The B-tree index structure guarantees at least 50 percent usage of the index pages. Therefore, the index page calculations multiply the minimum number of index pages required by two to account for the maximum size. If your file contains variable-length records, calculate the number of variable pages in the file and add that number to the sum from the preceding steps. To do so, use the following formula: NOTE: You can gain only a very rough estimate of the number of variable pages due to the difficulty in estimating the average number of records whose variable-length portion will fit on the same page.
To the sum obtained in the preceding steps, add the following: This new sum represents the estimated total number of logical pages that the file will contain. Calculate the number of PAT pages, and add that number to the estimated number of logical pages from the preceding step. Every file has a minimum of two PAT pages. However, to calculate the number of PAT pages in a file, use the following formula: To the sum obtained in the preceding step, add 2 pages for the FCR pages. Finally, add the estimated size of the pool of unused pages in the file. Btrieve uses the pool for shadow paging. To calculate the size of the pool, use the following formula: This formula applies if Btrieve tasks will execute insert, update, and delete operations only outside transactions. If tasks will be executing these operations inside transactions, multiply the average number of insert, update, and delete operations expected in the transactions times the non-transactional figure determined by the formula. Similarly, you must further increase the estimated size of the pool of unused pages if tasks will be executing simultaneous concurrent transactions. Having calculated the number of pages needed by the file, use the following formula to calculate the maximum number of bytes required to store the file: NOTE: Add an additional 4,096 bytes to the size of the file if the file uses more than 30 PAT pages. Btrieve uses these additional bytes internally. Btrieve provides several features that allow you to conserve disk space and improve system performance. These features include file preallocation, blank truncation, data compression, and index compaction. Preallocation guarantees that disk space will be available when Btrieve needs it. Btrieve allows you to preallocate up to 65,535 pages to a file when you create a data file. Table 20 shows the maximum number of bytes Btrieve allocates for a Btrieve file of each possible page size (assuming you allocate a full 65,535 pages of disk space).
Estimating File Size
Conserving Disk Space
File Preallocation
Table 20. Maximum Number of Bytes Allocated per File by Page Size
| Page Size | Disk Space Allocated |
|
|---|---|---|
512 |
33,553,920 |
( 512 * 65,535) |
1024 |
67,107,840 |
(1024 * 65,535) |
1536 |
100,661,760 |
(1536 * 65,535) |
2048 |
134,215,680 |
(2048 * 65,535) |
2560 |
167,769,600 |
(2560 * 65,535) |
3072 |
201,323,520 |
(3072 * 65,535) |
3584 |
243,877,440 |
(3584 * 65,535) |
4096 |
268,431,360 |
(4096 * 65,535) |
If not enough space exists on the disk to preallocate the number of pages you specify, Btrieve returns Status Code 18 (Disk Full) and does not create the file.
The speed of file operations can be enhanced if a data file occupies a contiguous area on the disk. The increase in speed is most noticeable on very large files.
To preallocate contiguous disk space for a file, the device on which you are creating the file must have the required number of bytes of contiguous free space available. Btrieve preallocates the number of pages you specify, whether or not the space on the disk is contiguous.
Use the formulas described earlier in this appendix to determine the number of data and index pages the file requires. You should round any remainder from this part of the calculation to the next highest whole number.
When you preallocate pages for a file, that file actually occupies that area of the disk. No other data file can use the preallocated area of disk until you delete or replace that file.
As you insert records, Btrieve uses the preallocated space for data and indexes. When all the preallocated space for the file is in use, Btrieve expands the file as new records are inserted.
When you issue a Btrieve Stat (15) operation, Btrieve returns the difference between the number of pages you allocated at the time you created the file and the number of pages that Btrieve has currently in use. This number is always less than the number of pages you specify for preallocation because Btrieve considers a certain number of pages to be in use when a file is created, even if you have not inserted any records.
Once a file page is in use, it remains in use even if you delete all the records stored on that page. Thus, the number of unused pages that the Stat operation returns never increases. When you delete a record, Btrieve maintains a list of free space in the file and reuses the available space when you insert new records.
Even if the number of unused pages returned by the Stat operation is zero, the file might still have free space available. The number of unused pages can be zero if one of the following is true:
Blank truncation can be used only with variable-length records. When you define a file that allows variable-length records, you can specify that Btrieve use a blank truncation method for storing the records in order to conserve disk space. If you choose to truncate blanks, Btrieve does not store any trailing blanks in the variable-length portion of the record when it writes the record to the file. Blank truncation has no effect on the fixed-length portion of a record. Btrieve does not remove blanks that are embedded in the data. When you read a record that contains truncated trailing blanks, Btrieve expands the record to its original length. The value Btrieve returns in the data buffer length parameter includes any expanded blanks. Blank truncation adds either 2 bytes or 4 bytes of overhead to the physical size of the record (stored with the fixed-record portion): 2 if the file does not use VATs, 4 if it does. When you create a Btrieve file, you can specify if you want Btrieve to compress the data records when it stores them in the file. Data compression can result in a significant reduction of the space needed to store records that contain many repeating characters. Btrieve compresses 5 or more of the same contiguous characters into 5 bytes. You should consider using Btrieve data compression in the following circumstances:
When you perform record I/O on compressed files, Btrieve uses a compression buffer to provide a block of memory for the record compression and expansion process. To ensure sufficient memory to compress or expand a record, Btrieve requires enough buffer space to store twice the length of the longest record your Btrieve task inserts into the compressed file. This requirement can have an impact on the amount of free memory left in the computer after Btrieve loads. For example, if the longest record your task writes or retrieves is 2,000 bytes long, Btrieve requires 4,000 extra bytes of memory to compress and expand that record. NOTE: If your file uses VATs (see Storing Variable-Length Records: Variable Tails and VATs), Btrieve's requirement for buffer space is the product of 16 times the file's page size.
Because the final length of a compressed record cannot be determined until the record is written to the file, Btrieve always creates a compressed file as a variable-length record file. In the data page, Btrieve stores either 7 bytes (if the file does not use VATs) or 11 bytes (if it does), plus an additional 8 bytes for each duplicate key pointer. Btrieve then stores the record on the variable page. Since the compressed images of the records are stored as variable-length records, individual records may become fragmented across several file pages if your Btrieve task performs frequent insertions, updates, and deletions. The fragmentation can result in slower access times because Btrieve may need to read multiple file pages to retrieve a single record. The data compression option is most effective when each record has the potential for containing a large number of repeating characters. For example, a record may contain several fields, all of which may be initialized to blanks by your Btrieve task when it inserts the record into the file. Compression is more efficient if these fields are grouped together in the record, rather than being separated by fields containing other values. To use data compression, the file must have been created with the compression flag set. Note that key-only files do not allow compression. Btrieve allows you to further conserve disk space by employing index balancing. By default, Btrieve does not use index balancing, so that each time a current index page is filled, Btrieve must create a new index page. When index balancing is active, Btrieve can frequently avoid creating a new index page each time a current index page is filled. Index balancing forces Btrieve to look for available space on adjoining index pages. If space is available on one of those pages, Btrieve moves keys from the full index page to the page with free space. The balancing process not only results in fewer index pages but also produces more densely populated indexes, better overall disk usage, and faster response on most read operations. If you add keys to the file in sorted order, index page usage increases from 50 percent to nearly 100 percent when you use index balancing. If you add keys randomly, the minimum index page usage increases from 50 percent to 66 percent. On write operations, the balancing logic requires Btrieve to examine more pages in the file and might possibly require more disk I/O. The extra disk I/O slows down file updates. Although the exact effects of balancing indexes vary in different situations, using index balancing typically degrades performance on write operations by about 5 to 10 percent. Btrieve allows you to fine-tune your Btrieve environment by offering two levels of index balancing. For a description of how to specify the index balancing configuration option, see Perform Index Balancing in Chapter 3, "Installing and Configuring NetWare Btrieve."
To do so, set bit 5 (0x10) of the affected file's file flags at creation time. If the index balancing configuration option is off when Btrieve is started, Btrieve applies index balancing only to files with bit 5 of the file flags set. Btrieve ignores bit 5 in all files' file flags if the index balancing configuration option was on when Btrieve was started. In this situation, Btrieve applies index balancing to every Btrieve file.
Btrieve files remain compatible regardless of whether index balancing is active. Also, you do not have to specify index balancing to access files that contain balanced index pages. Btrieve provides two methods of setting up file security:
Btrieve allows you to restrict access to a file by specifying an owner name, using either the Maintenance utility command SETOWNER or through issuing the Set Owner (29) operation in an application. Once you assign an owner name to a file, Btrieve requires that name to be specified for all future modifications to the file. This prevents any unauthorized access or changing of a file's contents by users or applications that do not have access to the owner name. You can restrict access to the file in several ways:
In the first situation, a Btrieve task may read the file without being required to specify the owner name. However, neither a user nor a task can change the file's contents without specifying the owner name. In the second situation, Btrieve restricts all access to the file unless the owner name is specified. As a complement to these restrictions, Btrieve allows you to remove ownership restrictions from a file if you know the owner name assigned to it. To do so, use the Btrieve Maintenance command CLOWNER, or the application can issue a Clear Owner (30) operation. When you assign an owner name, you may also request that Btrieve encrypt the data in the disk file using the owner name as the encryption key. Encrypting the data on the disk ensures that unauthorized users cannot examine your data by using a debugger or a file dump utility. Since encryption requires additional processing time, select this option only if data security is important in your environment. If you want to limit access to a file to a single task, you can specify that Btrieve open the file in exclusive mode. When a task opens a file in exclusive mode, no other task can open the file until the task that opened the file in exclusive mode closes it.Blank Truncation
Data Compression
Index Balancing
Setting Up Security
Owner Names
Exclusive Mode