Best Practices

This chapter compiles best practices from throughout the FairCom documentation so you have it in a convenient location. Learn the best ways to achieve your goals of performance and data integrity.

Selecting FairCom DB Features Used in the System

FairCom DB supports many options that affect the availability of the server’s data over time and the state of the data in the event of a catastrophic failure. Many of the features discussed in this section can be configured on a file-by-file basis. The system architects and application designers can determine the appropriate features to use for each data and index file based on the role the files play in the system and by understanding the effect of these features on availability and recovery of the data.

Data and Index Caching Options

FairCom DB maintains data and index caches in which it stores the most recently used data images and index nodes. The caches provide high-speed memory access to data record images and index nodes, reducing demand for slower disk I/O on data and index files. The server writes data and index updates first to cache and eventually to disk. Data and index reads are satisfied from the server’s cache if a cache page contains the requested data record image or index node. When necessary, the server writes pages to disk. The server also supports background flushing of cache buffers during system idle times.

The size of the data and index caches are determined by server configuration file settings. It is also possible to disable caching of specific data files and to dedicate a portion of the data cache to specific data files.

Caching of Data Records

The FairCom DB data cache uses the following approach to cache data record images: if the data record fits entirely within one or two cache pages, then the entire record is stored in the cache.

If the data record image covers more than two cache pages, then the first and last segments of the record are stored in the cache, but the middle portion is read from or written to the disk. This direct I/O is generally efficient as the reads are aligned and are for a multiple of the cache page size.

The nature of this approach can be modified. If the server keyword MPAGE_CACHE is set to a value greater than zero, say N, then records that fall within N+2 cache pages will be stored entirely within the cache. The default value is zero. This causes the cache to behave as described above.

Caching of Index Nodes

To understand FairCom DB’s caching of index nodes, it is necessary to review the relationship between the server’s PAGE_SIZE setting, the size of index cache pages, and the size of index nodes.

The server’s PAGE_SIZE setting determines the size of index cache pages and the size of index nodes for index files created by the server. Because the server must be able to fit an index node into a single index cache page, the server’s PAGE_SIZE setting determines the maximum size of index nodes supported by the server. The server can access index files that were created with an index node size less than or equal to the server’s PAGE_SIZE setting, but attempting to open an index whose index node size exceeds the server’s PAGE_SIZE fails with error KSIZ_ERR (40, index node size too large).

Properties of Cached Files

Although caching data benefits server performance, it is important to be aware of the effect of caching data on the recoverability of updates. The state of a cached file after an abnormal server termination depends on FairCom DB options in effect for that file. Below is a summary of the effect of caching on each file type. The following sections discuss this topic in detail.

• TRNLOG files: Caching does not affect recoverability. The server’s transaction processing logic ensures that all committed transactions are recovered in the event of an abnormal server termination.
• PREIMG or non-transaction files: Caching can lead to loss of unwritten cached data in the event of an abnormal server termination. For these file types, the server does not guarantee a persistent version of the unwritten updated cache images exists on disk, so any unwritten cached data is lost in the event of an abnormal server termination.
• WRITETHRU (applies to PREIMG and non-transaction files): To minimize loss of cached data, the WRITETHRU attribute can be applied to a PREIMG or non-transaction file. WRITETHRU causes writes to be written through the server’s cache to the file system (for low-level updates) or flushed to disk (for ISAM updates).

Configuring Caching

The memory allocated to the data and index caches is set in the FairCom DB configuration file, ctsrvr.cfg, using the DAT_MEMORY and IDX_MEMORY options. For example, to allocate 1 GB data cache and 1 GB index cache, specify these settings in ctsrvr.cfg:

DAT_MEMORY 1 GB
IDX_MEMORY 1 GB

The server allocates this memory at startup and partitions it into cache pages. The server’s PAGE_SIZE setting determines the size of the data and index cache pages.

The FairCom DB Server’s data and index cache configuration options support specifying a scaling factor used when interpreting cache memory sizes. The supported scaling factors are:

• KB: interpret the specified value as a number of kilobytes.
• MB: interpret the specified value as a number of megabytes.
• GB: interpret the specified value as a number of gigabytes.

You are always free to specify the exact numerical value as well. The following web page lists those keywords that accept the scaling factors and their limits:

Scaling Factors for Configuration Keyword Values

Per File Cache Options

In some cases it may be advantageous to turn off data file caching, especially for files with very long variable length records. Configuration entries of the form:

NO_CACHE <data file name>

result in caching for the specified data files to be disabled. Note that <data file name> may specify a wildcard pattern. You can specify multiple entries for more than one file.

Cache memory can be dedicated to specific data files using the following server configuration option:

SPECIAL_CACHE_FILE <data file name>#<bytes dedicated>

This option is useful for files that you wish to always remain in memory. For example, when occasionally reading other large files, this avoids required files being pushed out of cache.

For even more control, you can also specify percentage of total cache to be reserved for the specially cached files.

SPECIAL_CACHE_PERCENT <percentage>

This option specifies the overall data cache space that may be dedicated to individual files.

Priming Cache

The FairCom DB database engine optionally maintains a list of data files and the number of bytes of data cache to be primed at file open. When priming cache, FairCom DB reads the specified number of bytes for the given data file into data cache when physically opening the data file.

Data files are added to the priming list with configuration entries of the form:

PRIME_CACHE   <data file name>#<bytes primed>

The <bytes primed> parameter accepts scaling factors (for example 2GB).

Example: The following keyword instructs the Server to read the first 100,000 bytes of data records from customer.dat into the data cache at file open:

PRIME_CACHE   customer.dat#100000

A dedicated thread performs cache priming, permitting the file open call to return without waiting for the priming to be completed.

Use PRIME_CACHE with the SPECIAL_CACHE_FILE keyword to load dedicated cache pages at file open.

To prime index files, use configuration entries of the form:

PRIME_INDEX  <index file name>#<bytes primed>[#<member no>]

If the optional <member no> is omitted, all index members are primed. If an index member number is specified, only that member is primed.

For example, the following keyword instructs the Server to read the first 100,000 bytes of index nodes in customer.idx into the index buffer space at file open:

PRIME_INDEX   customer.idx#100000

The nodes are read first for the host index, and then for each member until the entire index is primed or the specified number of bytes has been primed.

The following example restricts the priming to the first member (the index after the host index):

PRIME_INDEX   customer.idx#100000#1

The <data file name> or <index file name> can be a wild card specification using a ‘?’ for a single character and a ‘*’ for zero or more characters.

FairCom DB also supports priming the data cache in forward AND reverse order by index.

PRIME_CACHE_BY_KEY <data_file_name>#<data_record_bytes_to_prime>#<index_number>

For example

PRIME_CACHE_BY_KEY mark.dat#100000000#-1

Primes up to 100,000,000 bytes from mark.dat reading by the first index in reverse key order.

Control of Cache Flushing

By default, FairCom DB creates two idle flush threads at startup. One thread flushes transaction file buffers, and the other flushes non-transaction file buffers. The threads wake up periodically and check if the server is idle. If so, the flush is begun. If subsequent server activity causes the server to no longer be idle, then the flushes are terminated. Low priority background threads, such as the delete node thread, do not affect the server’s idle state. FairCom DB clients and FairCom DB SQL clients and checkpoints do cause the idle status to be modified.

The configuration file can modify the characteristics of these idle flush threads:

IDLE_TRANFLUSH <idle check-interval for tran files>
IDLE_NONTRANFLUSH <idle check-interval for nontran files>

The idle check interval values are specified in seconds. Setting the interval to zero or a negative value disables the thread. The defaults for these intervals are each set at 15 seconds.

Transaction-Controlled Files

An application can use FairCom DB’s transaction capabilities to guarantee atomicity and, optionally, recoverability of data. FairCom DB supports the following transaction processing options:

• PREIMG (provides atomicity without recoverability)
• TRNLOG (provides atomicity with recoverability)
• TRNLOG with LOGIDX indexes (provides atomicity with recoverability, with logging of index reorganization operations to speed automatic recovery).

The application selects the transaction processing options that are in effect for the file when it creates data and index files using FairCom DB file creation API functions. The following sections discuss the properties of each transaction processing option, including their effect on the state of files in the event of an abnormal server termination.

PREIMG Transaction Files

The PREIMG (“pre-image”) transaction mode supports transaction atomicity but not transaction recoverability. PREIMG files may be updated only within an active transaction. The server stores PREIMG file updates in memory known as ‘preimage space’ until the transaction is committed or aborted. This use of preimage space guarantees atomicity and isolation of transaction operations: changes are made on an all or nothing basis and other clients do not see changes until the transaction is committed.

Properties of PREIMG Files

The state of a PREIMG file at a point in time is stored in a combination of the following locations:

• Updated buffers held in preimage space. (Pending updates held in server memory: this is volatile storage)
• Updated server data/index cache pages. (Committed updates held in server memory: this is volatile storage)
• Filesystem cache pages. (Committed updates written to filesystem but not flushed to disk held in system memory: this is volatile storage)
• Disk file contents. (Committed updates written to filesystem and flushed to disk: this is non-volatile storage)

Only the disk file contents are persistent and unlike TRNLOG transaction file updates (discussed below), PREIMG file updates are not logged to the server’s transaction logs. For this reason, PREIMG files are not recoverable in the event of an abnormal server termination. In such a situation a PREIMG file is in an unknown state because an unknown number of updates may have not yet been written to disk at the time of the abnormal server termination. Also, because automatic recovery does not process PREIMG files, a PREIMG file rebuilt after an abnormal server termination is not guaranteed to be in a consistent transaction state. In such a situation, PREIMG files could contain data that was in the process of being committed but for which the commit had not yet been completed.

Backup and Restore Options for PREIMG Files

Backup copies of PREIMG files can be made using the following approaches:

Online backup using dynamic dump

Although automatic recovery is not available to PREIMG files, it is possible to perform periodic dynamic backups of PREIMG files using FairCom DB’s dynamic dump feature. Using the PREIMAGE_DUMP dynamic dump script option promotes PREIMG files to full TRNLOG files during the dynamic dump process. The promotion to a TRNLOG file means that a full transaction log is maintained during the dump process. This process guarantees that any changes made to the files during the backup are saved in these specially maintained transaction logs. The ability to dynamically back up user data files minimizes the loss of the automatic recovery feature with this mode. The dynamic dump produces a dump stream file containing the disk contents of the PREIMG files and the changes made during the dynamic dump. If it becomes necessary to restore the backup copy of the PREIMG files, the ctrdmp utility can be used to extract the PREIMG files from the dump stream file, restoring them to their state at the time of the backup.

Offline backup using file copy

An offline backup of PREIMG files can be performed using system file copy utilities when the server is shut down or when the server is running and the files to be backed up are closed. It is important to ensure that the server is shut down or the files are closed before making a backup copy of files using a system file copy utility in order to ensure that all updated buffers are flushed to disk. Otherwise, data may be lost and the file may be in an inconsistent state.

When to Use PREIMG Files

The benefit of PREIMG is that it avoids the overhead associated with writing to and flushing the transaction logs. If atomicity is required for a file but recoverability is not, PREIMG may be an appropriate choice. Some specific cases in which PREIMG may be appropriate include:

• Using TRNLOG data files and PREIMG indexes if you are willing to rebuild the indexes after an abnormal server termination.
• Using PREIMG on files that can be re-created in the event of an abnormal server termination.

To minimize loss of unwritten cached PREIMG file updates in the event of an abnormal server termination, consider using WRITETHRU for PREIMG files or periodically calling the FairCom DB API function CtreeFlushFile() to flush PREIMG data and index cache pages to disk.

Creating and Using PREIMG Files

To create a PREIMG data or index file, include the PREIMG filemode in the filemode value specified when creating the file.

• If using an ISAM-level file creation function, include PREIMG in the dfilmod and ifilmod fields of the IFIL structure supplied to the function.
• If using a low-level file creation function, include PREIMG in the filemode parameter supplied to the function.

FairCom DB allows updates to PREIMG files only within an active transaction. To start a transaction, call the FairCom DB API function Begin() with a transaction mode of either PREIMG or TRNLOG. A PREIMG file may participate in either a PREIMG or a TRNLOG transaction. Regardless of which transaction type is specified, updates to PREIMG files are not logged to the server’s transaction logs. An update on a PREIMG file that is attempted outside a transaction fails with FairCom DB error TTYP_ERR (99, transaction type/filmod conflict).

TRNLOG Transaction Files

The TRNLOG (“tran-log”) transaction mode supports both transaction atomicity and transaction recoverability. TRNLOG files may be updated only within an active transaction. The server stores TRNLOG file updates in memory known as “preimage space” until the transaction is committed or aborted and logs transaction begin and commit operations and file updates to disk-based transaction log files. The use of preimage space guarantees atomicity and isolation of transaction operations: changes are made on an all-or-nothing basis and other clients do not see changes until the transaction is committed. The use of transaction logs guarantees recoverability of transactions in the event of an abnormal server termination.

Properties of TRNLOG Files

Because FairCom DB logs updates made to TRNLOG files to its transaction logs, TRNLOG files are fully recoverable in the event of an abnormal server termination. The server ensures TRNLOG files are in a consistent state by performing automatic recovery at server startup. The server’s automatic recovery procedure involves scanning the transaction log to determine which transactions must be undone and which must be redone, based on the transaction log entries and the state of the files involved in the transactions.

The state of a TRNLOG file at a point in time is stored in a combination of the following locations:

• Updated buffers held in preimage space. (Pending updates held in server memory: this is volatile storage)
• Updated server data/index cache pages. (Committed updates held in server memory: this is volatile storage)
• Filesystem cache pages. (Committed updates written to filesystem but not flushed to disk held in system memory: this is volatile storage)
• Disk file contents. (Committed updates written to filesystem and flushed to disk: this is non-volatile storage)
• Transaction log contents. (Transaction state entries and file updates flushed to disk: this is non-volatile storage)

Only the disk file and transaction log contents are persistent. FairCom DB can guarantee recoverability of TRNLOG files in the event of an abnormal server termination because it logs to disk the transaction state and updated buffers necessary to guarantee recoverability. At startup, the automatic recovery procedure applies the necessary changes to TRNLOG data and index files to ensure the system is in a consistent transaction state.

Backup and Restore Options for TRNLOG Files

Backup copies of TRNLOG files can be made using the following approaches:

Online backup using dynamic dump

FairCom DB’s dynamic dump feature can be used to make a consistent point in time backup of TRNLOG files. The server maintains a full transaction log during the dump process and produces a dump stream file containing the disk contents of the TRNLOG files and the changes made during the dynamic dump. If it becomes necessary to restore the backup copy of the TRNLOG files, the ctrdmp utility can be used to extract the TRNLOG files from the dump stream file, restoring them to their state at the time of the backup.

Online backup using system disk snapshot

Some systems provide the ability to perform an instantaneous transparent copy of the contents of a disk. This approach can be used to backup TRNLOG files provided that the copy operation produces a point in time image of the disk and the server’s transaction logs are included with the TRNLOG files. If this is done, the TRNLOG files can be restored to a consistent transaction state corresponding to the time the backup was taken by restoring the saved files (transaction logs and TRNLOG files), then starting the server and allowing it to perform automatic recovery on the TRNLOG files. The disk-level snapshot is typically a feature offered by intelligent disk storage subsystems, such as a Storage Area Network (SAN).

Offline backup using file copy

An offline backup of TRNLOG files can be performed using system file copy utilities when the server is shut down or when the server is running and the files to be backed up are closed. It is important to ensure that the server is shut down or the files are closed before making a backup copy of files using a system file copy utility in order to ensure that all updated buffers are flushed to disk. Otherwise, data may be lost and the file may be in an inconsistent state.

When to Use TRNLOG Files

Create data and index files as TRNLOG files when operations on the files must be atomic and updates must be recoverable in the event of an abnormal server termination. If only atomicity is needed, PREIMG may be more appropriate.

The performance impact of TRNLOG due to checkpoint operations, transaction log flushing and transaction file buffer flushing can be minimized using transaction-related server configuration options such as:

CHECKPOINT_FLUSH
CHECKPOINT_INTERVAL
COMMIT_DELAY
LOG_SPACE
LOG_TEMPLATE
TRANSACTION_FLUSH

Creating and Using TRNLOG Files

To create a TRNLOG data or index file, include the TRNLOG filemode in the filemode value specified when creating the file.

• If using an ISAM-level file creation function, include TRNLOG in the dfilmod and ifilmod fields of the IFIL structure supplied to the function.
• If using a low-level file creation function, include TRNLOG in the filemode parameter supplied to the function.

FairCom DB allows updates to TRNLOG files only within an active TRNLOG transaction. To start a TRNLOG transaction, call the FairCom DB API function Begin() with a transaction mode of TRNLOG. An update on a TRNLOG file that is attempted outside a TRNLOG transaction fails with FairCom DB error TTYP_ERR (99, transaction type/filmod conflict).

TRNLOG Transaction Files with LOGIDX Indexes

TRNLOG indexes can optionally be created with the LOGIDX attribute. This attribute causes the server to log index reorganization operations to the transaction logs in order to facilitate automatic recovery of large indexes.

During automatic recovery, FairCom DB scans the transaction logs in order to determine which TRNLOG index files must be processed by automatic recovery. For TRNLOG index files that were not created with the LOGIDX attribute, the server scans the leaf nodes, verifying links and rebuilding the upper part of the index tree. For large indexes, scanning the leaf nodes can be time-consuming.

Creating TRNLOG indexes with the LOGIDX attribute allows the server to make local repairs to index nodes (made possible by the additional LOGIDX transaction log entries) rather than requiring a full index leaf node scan, verification, and reconstruction. For this reason, using LOGIDX indexes can significantly speed up recovery time in the case of large TRNLOG indexes.

Using the LOGIDX attribute for an index file causes FairCom DB to perform the following additional steps for index reorganization operations on that index:

• Write begin/end entries to the transaction log for index reorganizations (i.e., node splits or underflow node recovery). Before the “end” entry is written to the log, the index updates are saved to disk. If a begin is found without a corresponding end during automatic recovery, then the index must be examined in the region of the reorganization.
• Write vulnerable node entries to the transaction log to identify nodes which must be cleaned during automatic recovery. It is not necessary to place images of nodes in the log. Instead, the server logs the node position and optionally a key value which identifies the node’s logical position in the index.

Properties of LOGIDX Index Files

The format of a TRNLOG index created with the LOGIDX attribute is identical to that of a TRNLOG index created without the LOGIDX attribute. The only difference is the generation of additional log entries for index reorganization operations, which automatic recovery uses to speed recovery of the indexes as described above.

When to Use LOGIDX Index Files

The LOGIDX attribute is appropriate to use when a database contains large TRNLOG indexes and the application requires fast automatic recovery. The tradeoff for faster automatic recovery is that using LOGIDX might introduce a slight performance penalty for index update operations due to the generation of additional log entries and forced flushing of updated index buffers.

Creating and Using LOGIDX Index Files

To create a TRNLOG index file with LOGIDX support enabled, include the TRNLOG and LOGIDX filemodes in the filemode value specified when creating the index file.

• If using an ISAM-level file creation function, include TRNLOG | LOGIDX in the ifilmod field of the IFIL structure supplied to the function.
• If using a low-level file creation function, include TRNLOG | LOGIDX in the filemode parameter supplied to the function.

Because LOGIDX affects only the server’s transaction processing behavior and does not affect the format of the index file, LOGIDX can be enabled at run-time for all TRNLOG indexes by specifying the following server configuration option in the server configuration file, ctsrvr.cfg:

FORCE_LOGIDX ON

The FORCE_LOGIDX keyword provides a simple way to enable LOGIDX processing for TRNLOG indexes, including those that were not created with the LOGIDX attribute. When this option is specified in the server configuration file, all TRNLOG indexes are treated as LOGIDX files. Using this keyword is a good way to ensure that LOGIDX processing is in effect for all TRNLOG indexes in order to ensure fast recovery.

Delayed Durability

FairCom DB offers a mode of transaction processing that allows you to fine-tune the balance between performance and recoverability. Many applications can benefit significantly from relaxing recoverability and still maintain an acceptable data risk tolerance.

Delayed Durability Transaction Processing

With full transaction control for complete ACID compliance, transaction logs are synced to disk with each commit operation, ensuring absolute data integrity with complete recoverability. Full durable ACID transaction control enables many powerful features not available without the availability of recoverable log data:

• Automatic database recovery
• Live database backups without rebuild on restore
• Replication
• Transaction auditing

The most critical of these is automatic recovery in case of system failure. However, full transaction control remains a critical area of database performance tuning. Database updates must be secured in write-ahead logs for guaranteed recoverability. This comes with a performance impact due to the synchronous I/O requirements ensuring data is safely persisted.

Many applications could benefit from a "relaxed" mode of transaction log writes. With today's hardware and power redundancies, it is conceivable to slightly relax full durability constraints and still maintain an acceptable data risk tolerance. The balance becomes "how much loss of recoverability can these systems tolerate?"

Allowing database administrators to balance their window of vulnerability against online performance, FairCom DB provides a new Delayed Durability feature for transaction logs.

Full Recoverability	Preimage (Atomicity Only)	Non-Transaction Mode

This new transaction operation mode allows its FairCom DB transaction log updates to remain cached in its in-memory transaction log buffer as well as in file system cache, even after a transaction has committed. The challenge is to avoid index and disk updates from reaching disk before the transaction entries do. FairCom has managed to delay transaction log writes to persisted storage while guaranteeing these transaction log entries for a given transaction write to disk before any data file updates associated with that transaction are written to file system cache or to persistent storage with a modified log sync strategy. In addition, a background thread guarantees an upper bound on the total amount of time any transaction remains in a non-synced state.

This feature is enabled with the following configuration entry and takes as an argument the maximum number seconds, N, that will elapse between log syncs. This ensures that, after a commit, the transaction log will be synced in no more than N seconds, thereby allowing you to define your window of vulnerability risk.

DELAYED_DURABILITY <N>

The end result can approach non-transaction performance while ensuring committed transactions to persisted storage to within less than N seconds of vulnerability. Values as low as one (1) second are shown to provide the best performance. In selected test cases, up to 300% faster transaction throughput has been observed. Higher values have been found to offer little, if any, additional benefit.

For complete information about this mode, see the section titled Delayed Durability Transaction Processing in the the FairCom DB Programmers Reference Guide.

6-Byte Transaction Numbers

FairCom DB associates a unique transaction number with every transaction. The transaction numbers assigned by the server start with transaction number 1 and are ever-increasing during the server’s operation.

When a FairCom DB client begins a transaction, the return value is the transaction number assigned to that transaction (or 0 in the case of an error). The server writes transaction numbers to the following persistent locations:

• Transaction logs (the start files S*.FCS, and the log files L*.FCS)
• TRNLOG indexes (in leaf nodes and header)
• Superfile hosts (including the server’s FAIRCOM.FCS)

Transaction Number Limitations Prior to V8

Versions 6 and 7 of the c-tree Server support a 4-byte transaction number, of which 30 bits form the transaction number. For this reason, the maximum transaction number these versions of the c-tree Server support is 1,073,741,808 (0x3ffffff0).

When the server detects that the current transaction number is approaching the transaction number limit, it logs messages to the server status log, CTSTATUS.FCS, to inform the server administrator of an impending transaction number overflow. When the server detects a transaction number overflow it shuts down.

In the event of an impending transaction number overflow, the server administrator can follow these steps to restart the transaction numbering:

• Perform a clean shutdown of the c-tree Server as indicated by the “Perform system checkpoint” in the server status log.
• Delete the transaction logs: files S0000000.FCS, S0000001.FCS, and L*.FCS.
• Use the ctclntrn utility on all TRNLOG indexes and superfile hosts (this includes the c-tree Server files FAIRCOM.FCS and SYSLOGIX.FCS) to reset the transaction numbers in the leaf nodes and index header.
• Restart the c-tree Server. The server creates new transaction logs and starts numbering transactions from 1 again.

c-tree Server V8 Transaction Number Enhancements

For systems that must sustain a high transaction rate, the transaction number limit of the version 6 and version 7 c-tree Server can have significant implications for the availability of the c-tree Server. As an example, a system with a sustained rate of 1000 transactions per second would exceed the 4-byte transaction number limit in about 12 days of continuous operation.

To overcome this limitation, FairCom added support for 6-byte transaction numbers (also known as extended transaction numbers) to version 8 of the c-tree Server. The use of 6-byte transaction numbers (46 bits of which are used for the transaction number) provides the server the ability to sustain a rate of 1000 transactions per second for over 2000 years (as compared to the 12 day limit when using 4-byte transaction numbers).

The V8 c-tree Server always stores transaction numbers in the transaction logs as 6-byte values and stores transaction numbers as either 4-byte or 6-byte numbers depending on the index creation options. All indexes that the server creates for its own internal use are created as 6-byte transaction number indexes, so the only possible source of 4-byte transaction numbers is an application that creates index files as 4-byte transaction number files. If the application creates all its indexes as 6-byte transaction number files, the server will not be subject to the 4-byte transaction number limit.

When to Use 6-Byte Transaction Number Files

Use 6-byte transaction number files for high transaction rate systems to avoid the 4-byte transaction number limit, because the server must be shut down and indexes and superfile hosts cleaned when the transaction number limit is reached.

Creating 6-Byte Transaction Number Files

To create 6-byte transaction number files (this applies to superfile hosts and indexes), create an array of XCREblk (extended create block) structures, one for each physical data and index file to be created. Include the ct6BTRAN attribute in the x8mode field of each extended create block structure corresponding to an index file or superfile host. Call an Xtd8 create function such as CreateIFileXtd8(), passing the extended create block array to the function.

In order to ensure that only 6-byte transaction number files are opened by the server, specify the following keyword in the server configuration file:

COMPATIBILITY EXTENDED_TRAN_ONLY

This keyword causes a R6BT_ERR (745, 6BTRAN file required) on an attempt to create or open a non-extended-transaction-number file. (Note that a read-only open is not a problem since the file cannot be updated.)

Configurable Transaction Number Overflow Warning Limit

When FairCom DB supports 6‑byte transaction numbers it does not display transaction overflow warnings until the current transaction number approaches the 6‑byte transaction number limit. But if 4‑byte transaction number files are in use, a key insert or delete will fail if the current transaction number exceeds the 4‑byte transaction number limit (however, FairCom DB will continue operating).

To allow a server administrator to determine when the server’s transaction number is approaching the 4‑byte transaction number limit, the following configuration option was added:

TRAN_OVERFLOW_THRESHOLD <transaction_number>

This keyword causes the c-tree Server to log the following warning message to CTSTATUS.FCS and to standard output (or the message monitor window on Windows systems) when the current transaction number exceeds the specified transaction number:

WARNING: The current transaction number (####) exceeds the user-defined threshold.

The message is logged every 10000 transactions once this limit has been reached. The TRAN_OVERFLOW_THRESHOLD limit can be set to any value up to 0x3ffffffffffff, which is the highest 6‑byte transaction number that FairCom DB supports.

Non-Transaction Files

Depending on the application’s requirements, it may be appropriate to create certain data and index files without transaction control. Non-transaction files avoid the overhead associated with transaction processing but do not guarantee atomicity of operations or recoverability of updates.

Properties of Non-Transaction Files

The state of a non-transaction file at a point in time is stored in a combination of the following locations:

• Updated server data/index cache pages. (Updates held in server memory: this is volatile storage)
• Filesystem cache pages. (Updates written to filesystem but not flushed to disk held in system memory: this is volatile storage)
• Disk file contents. (Updates written to filesystem and flushed to disk: this is non-volatile storage)

The server does not guarantee that unwritten updated data and index cache pages are backed by a persistent copy on disk, so non-transaction files are not recoverable in the event of an abnormal server termination. In such a situation a non-transaction file is in an unknown state because an unknown number of updates may have not yet been written to disk at the time of the abnormal server termination.

Backup and Restore Options for Non-Transaction Files

Backup copies of non-transaction files can be made using the following approaches:

Online backup using dynamic dump

FairCom DB’s dynamic dump feature can be used to backup non-transaction files. However, unlike PREIMG and TRNLOG files the dynamic dump cannot guarantee a consistent point in time backup for non-transaction files. Non-transaction files are flushed if possible at the beginning of the dynamic dump. If successfully flushed and not updated during the dynamic dump, the file is marked clean in the dynamic dump stream file; otherwise it is marked dirty. At dump restore time, clean files have their update flags cleared but the update flag remains set for dirty files. A dirty restored file could be missing updates and dirty data and index files could be out of sync. Dirty restored files must be rebuilt to clear the update flag and to ensure consistency between data and index files.

Offline backup using file copy

An offline backup of TRNLOG files can be performed using system file copy utilities when the server is shut down or when the server is running and the files to be backed up are closed. It is important to ensure that the server is shut down or the files are closed before making a backup copy of files using a system file copy utility in order to ensure that all updated buffers are flushed to disk. Otherwise, data may be lost and the file may be in an inconsistent state.

When to Use Non-Transaction Files

Use non-transaction files when the files are of a temporary nature and can be re-created or the data in the files can be restored from another source in the event of an abnormal server termination.

To minimize loss of unwritten cached non-transaction file updates in the event of an abnormal server termination, consider using WRITETHRU for non-transaction files or periodically calling the FairCom DB API function CtreeFlushFile() to flush non-transaction data and index cache pages to disk.

Creating Non-Transaction Files

To create a non-transaction data or index file, do not include the TRNLOG or PREIMG file modes in the filemode value specified when creating the file.

• If using an ISAM-level file creation function, do not include TRNLOG or PREIMG in the dfilmod and ifilmod fields of the IFIL structure supplied to the function.
• If using a low-level file creation function, do not include TRNLOG or PREIMG in the filemode parameter supplied to the function.

WRITETHRU Files

For non-WRITETHRU files, the server stores data and index updates in its internal cache and does not write updates immediately to disk. For TRNLOG files, this is not a concern because committed updates to TRNLOG files are logged to the transaction logs. For non-TRNLOG files, however, in the event of an abnormal server termination the contents of the cache (and hence any unwritten updates to data and index files) will be lost.

In this situation, the server marks non-TRNLOG files as corrupt to indicate that the file was opened, updated, and not properly closed, so its state is unknown. Attempting to open such a file fails with error FCRP_ERR (14, file corrupt at open). Rebuilding the data file and its associated indexes resets the update flag and allows the application to open the file, but all cached updates that had not yet been written to disk are lost.

The WRITETHRU file mode can be applied to FairCom DB data and index files to cause the server to write updates through the server’s cache to the file system cache or to disk, thereby minimizing the potential for loss of cached updates in the event of an abnormal server termination. While ensuring the updates are written to the file system or to disk, WRITETHRU preserves the updates in the server’s cache so that reads can be satisfied from the server’s cache.

A data or index file can be created as a WRITETHRU file (in which case WRITETHRU is a permanent attribute of the file), or it can be opened as a WRITETHRU file (in which case the file is treated as WRITETHRU until it is closed).

Properties of WRITETHRU Files

For non-transaction WRITETHRU files, all updates are written through the server’s cache to the file system cache. In addition, the server flushes file system buffers on each ISAM update operation for WRITETHRU files. (Low-level updates on WRITETHRU files are written through the server’s cache to the file system cache but are not flushed to disk.)

For PREIMG files opened or created with the WRITETHRU attribute, the server behaves as follows:

• PREIMG indexes are placed into standard WRITETHRU mode except that changes in the number of index entries are output at transaction commit time.
• PREIMG data files are placed into a modified mode in which file updates and header updates are only output at transaction commit time.

WRITETHRU minimizes the possibility of lost updates in the event of a catastrophic event because it writes updated cache pages to disk at the time of the update operation. However, WRITETHRU does not provide the guarantee of recoverability that TRNLOG provides. It is still possible when using WRITETHRU for some updates to be lost or for data and index file inconsistencies to exist following a catastrophic event.

Backup and Restore Options for WRITETHRU Files

The backup and restore options for WRITETHRU files are the same as for non-WRITETHRU files. The distinction is that using WRITETHRU can help minimize data loss that could occur due to unwritten updated buffers for PREIMG and non-transaction files (although WRITETHRU does not provide an absolute guarantee of data/index consistency or recoverable updates as TRNLOG does).

When to Use WRITETHRU Files

WRITETHRU is useful for ensuring that updates to data and index files are written to the file system cache (or to disk in the case of ISAM updates) at the time of the update (or commit in the case of PREIMG WRITETHRU files). PREIMG and non-transaction files that do not use WRITETHRU can experience significant data loss due to unwritten cached data in the event of an abnormal server termination.

Creating WRITETHRU Files

To create a WRITETHRU data or index file, include the WRITETHRU filemode in the filemode value specified when creating the file.

• If using an ISAM-level file creation function, include WRITETHRU in the dfilmod and ifilmod fields of the IFIL structure supplied to the function.
• If using a low-level file creation function, include WRITETHRU in the filemode parameter supplied to the function.

A data or index file that was not created using WRITETHRU can be opened as a WRITETHRU file by specifying WRITETHRU in the filemode when opening the file.

The following server configuration settings can be used to alter the server’s WRITETHRU behavior:

• The COMPATIBILITY FORCE_WRITETHRU configuration option causes the server to enable WRITETHRU semantics for all non-TRNLOG data and index files. This option provides a simple way to enable WRITETHRU for all files that are not under full transaction control.
• The COMPATIBILITY WTHRU_UPDFLG configuration option causes the server to avoid setting the update flag for updated WRITETHRU files. This option provides a simple way to avoid error FCRP_ERR (14) for WRITETHRU files in the event of an abnormal server termination. Note that this option should be used with caution, as an abnormal server termination could leave a data file out of sync with its corresponding indexes (if the data flush completed but the index flush had not yet completed when the server terminated abnormally).

Large File Support

When FairCom first released FairCom DB, 10 MB hard drives were considered large. A 2 GB maximum file size was sufficient, if not unimaginable. For this reason, the c tree Server was originally designed to use 4-byte file offsets, which limited the size of files to 2 GB or 4 GB depending on the platform.

Today, with 64-bit operating systems becoming common and 4 GB hard drives being considered bottom-of-the-line, the need has developed to support very large physical files. Starting with version 7 of the c-tree Server V7, FairCom introduced large file support. FairCom DB supports large files in two ways:

• Huge files: Physical files that use 8-byte offsets that can grow to over 4 GB in size. Huge files can be used to support large files up to the maximum physical file size supported by modern filesystems.
• Segmented files: Logical files distributed across multiple physical files, which can optionally be huge. Huge segmented files can be used to overcome system limitations on physical file sizes.

Huge Files

A FairCom DB data or index file that is created with 8-byte offset support is known as a HUGE file. A non-HUGE file is limited to 2 or 4 GB in size, depending on system file limits. An 8-byte file offset provides a maximum file size of more than 1.844 x 10¹⁹ bytes per file, so a HUGE file effectively eliminates practical constraints on the file size.

When to Use Huge Files

Use HUGE files when the maximum size of a file is expected to exceed 2 GB. The consequence of using a non-HUGE file is that the system-imposed 4-byte offset file limit places a hard limit on the supported file size. When a non-huge file reaches the system file size limit, operations that require the file size to increase (such as adding records or updating variable-length records) will fail. Only deleting records or creating a new file will allow additional records to be added to a non-huge file when it reaches this state. Using huge files avoids this limitation.

Note that even when using huge files, the maximum size of a file FairCom DB can create is limited by operating system and file system limits on file sizes. For example, on many Unix systems, the ulimit or limit command can set arbitrary limits on the size of a physical file. When developing a system, review operating system and file system file size limits to ensure they support the system’s physical file size requirements. If the system’s file size limits are too restrictive, consider using the c tree Server’s segmented file support (described later) to overcome the file size limitations.

Creating Huge Files

To create HUGE data and index files, create an array of XCREblk (extended create block) structures, one for each physical data and index file to be created. Include the ctFILEPOS8 attribute in the x8mode field of each extended create block structure. Call an Xtd8 create function such as CreateIFileXtd8(), passing the extended create block array to the function.

Segmented Files

Segmented file support allows a single logical file to occupy multiple physical files. This allows data files to exceed the physical file size limits imposed by the operating system, and when combined with Huge File Support, provides the added benefit of very large file sizes. The physical file segments can be kept together or distributed over multiple volumes.

When to Use Segmented Files

Use segmented files in the following situations:

• When the total physical size of a FairCom DB file is expected to exceed operating system or file system limits. In this case, the file can be segmented into multiple physical files, each of which is within the system’s file size limitations.
• When enough total disk space is available for the expected physical file size, but the disk space is spread over multiple volumes. In this case, the file’s various segments can be placed where the disk space is available.

Creating Segmented Files

Files can be segmented automatically or the size and location of specific segments can be defined programmatically. Follow these steps to create segmented files:

1. Create an array of extended create block (XCREblk) structures, one for each physical file to be created. Include the following flags as appropriate in the x8mode field of the XCREblk structures:
   1. The ctFILEPOS8 file mode permits huge files. This mode is required if the logical file will exceed 4GB total file size, but is not required for segmented files in general.
   2. The ctSEGAUTO file mode allows automatic segment generation. When this file mode is used, SetFileSegments() is not required. Simply set the host segment size and maximum segments (the segsize and segmax elements of the Extended File Creation Block). All additional segments will be the same size as the host file and will be stored in the same directory. The segment names generated for a file start by adding “.001” to the existing file name, then incrementing the numeric extension. For example: The automatic segment names for the file sample.dat would start with sample.dat.001 and continues with sample.dat.002, and so on.
2. Call a FairCom DB Xtd8 file creation function, passing it the XCREblk structure array.
3. If not using automatic segments, define a SEGMDEF structure detailing the segments to be used. To establish the initial segments, call SetFileSegments(), passing it the SEGMDEF structure.

The extended creation functions can only provide minimal information for segmented files. The XCREblk structure only holds the size of the host segment and the maximum number of segments. To fill in the details, the SetFileSegments() function specifies segment definitions for newly created files dynamically while the file is open. Also, SetFileSegments() can optionally set or change the size limit on the host segment. However, SetFileSegments() can only be called for files created with the Xtd8 API, which causes the file to have an extended header.

The Xtd8 create functions always create Extended files, even if no extended features are requested (unless the ctNO_XHDRS file mode is turned on). Files created with the original API (e.g., CreateIFileXtd()) are in the Standard FairCom DB format.

Memory Files

Memory files are data and index files that are purely memory-resident files that do not exist on disk.

The memory in which memory file data records and index nodes are stored is allocated using the server’s internal memory allocation routines and is separate from the server’s data and index caches. The server uses its memory-management subsystem to allocate and free memory associated with memory files. If the request for memory is at or below 2K bytes, the server uses its own memory sub sub-manager; otherwise, it calls the system memory-allocation routine. When a record or node is deleted, the memory is returned.

Properties of Memory Files

Because memory files exist purely in server memory (the data never goes to disk), an abnormal server termination or final close of the file destroys the memory file and its existing memory records.

Memory files can be created as PREIMG files or non-transaction files. PREIMG memory files support atomic operations just as disk-based PREIMG files do.

The maximum size of a memory file is limited by the file size specified when creating the memory file. When selecting memory file sizes, take into account the amount of available physical memory on the system. Creating memory files whose size exceeds the amount of available physical memory on the system will cause the operating system to swap memory to disk, which degrades performance.

A memory-resident file cannot be mirrored, partitioned or segmented.

A memory file cannot be backed up by a dynamic dump.

A memory file on a 32-bit server must use 32-bit file offsets (so it cannot be huge); a memory file on a 64-bit server must use 64-bit file offsets (so it must be huge).

• When creating a memory file on a 64-bit server at the FairCom DB API level: It is recommended to explicitly include the HUGEFILE attribute in the create statement. This will ensure consistent operation and avoid triggering a potential 582 error.
• When using memory files on a 64-bit server: If you create any indexes that allow duplicates, be sure to include 8 bytes in the total key length (rather than the typical 4 bytes) for the record offset indicator. This will avoid potentially triggering a 582 error.

When to Use Memory Files

Memory files are appropriate to use for data that does not need to be stored to disk. Examples include temporary data that is useful during server operation but would normally be discarded when the server is shutdown and restarted and data that can be restored from another source in the event of an abnormal server termination.

Creating Memory Files

To create a FairCom DB data or index file as a memory file, create an array of XCREblk (extended create block) structures, one for each data and host index file to be created. For each extended create block structure, include the ctMEMFILE attribute in the x8mode field and set the mxfilzhw and mxfilzlw fields to the maximum file size (mxfilzhw is the high-order word and mxfilzlw is the low-order word). Call an Xtd8 create function such as CreateIFileXtd8(), passing the extended create block array to the function.

An attempt to add a new record that causes the memory file to exceed this limit fails, and returns a MAXZ_ERR (667, attempt to exceed maximum file size).

The final close of a memory file causes the server to delete the file. A final close is a file close that causes the user count of the file to drop to zero. In order to ensure that the contents of a memory file are not lost due to an unintentional close of the file, it is possible to make the data file persist even after the final close by including the ctKEEPOPEN bit in the splval member of the file’s extended create block when creating the file. Then the final close leaves the file open (but without any user attached to the file). It can then be opened again, and the data still exists. The file will be closed when the server terminates, or when a call is made to the FairCom DB API function CloseCtFileByName().

Partitioned Files

FairCom DB supports a unique feature known as Partitioned Files. A partitioned file logically appears to be one file (or more accurately one data file and its associated index files), but is actually a set of files whose contents are partitioned by the value of the partition key. Both the data files and index files are partitioned. This permits data with a defined range of values for the partition key to be rapidly purged or archived (instead of having to delete record-by-record each record within this range).

A partitioned file is comprised of a host data file and its associated indexes combined with a rule. The rule determines how to map the partition key value (e.g., a date, or invoice number, or some other characteristic) into a particular partition member of the host. The host file does not contain any actual data, but serves as a logical file that appears to contain all the data.

A partition member file has the same definition as the host, but only holds data whose partition key maps to the member. For example, customer orders may be partitioned by calendar quarter. All orders booked in the same quarter are stored in the same member. A member is comprised of a standard FairCom DB data file and its associated indexes.

To add data to the partitioned file, simply call an add routine for the host. The code will add the record in the proper partition member, creating the partition member if necessary. Rewriting a data record may move the record between partitions if the partition key entry for the record is changed. Under transaction control, such a delete/add record operation is done atomically.

Searching the partitioned file in key order is fairly straightforward and efficient if the key is the partition key (the key used to determine which partition should contain the record). Searches on other, non-partition, keys are less efficient than normal because the record may exist in any of the partition members. The more active partition members there are in the logical file, the less efficient the non-partition key search will be.

It is possible to manage the partition members so that archiving or purging partitions is very quick and efficient.

When to Use Partitioned Files

Use partitioned files when the ability to easily purge or archive individual member files is desired. For example, partitioned files can be used to store records by date, so that all records in a given month are stored in one partition. Then, when the month’s activity is complete, the partition can be taken offline and archived or purged.

File Mirroring

FairCom DB’s mirroring facility makes it possible to create and maintain copies of important files on different drive volumes, partitions or physical drives. If the primary storage location is lost due to some form of catastrophe (i.e., head crash) the mirroring logic can automatically detect the lost connection and switch to the secondary or “mirrored” storage area.

By default, read and write operations on mirrored files will continue without returning an error if one of the files fails, but the other succeeds. When this happens, the failed file is shut down, all subsequent I/O operations continue only with the remaining file and the file name for the shut down file is logged in the server status log, CTSTATUS.FCS.

Both non-transaction controlled and transaction-controlled files can be mirrored. For a transaction controlled file, if a primary and mirror file get out-of-sync beyond the ability of automatic recovery to make them both good, the most up-to-date file is recovered.

When to Use Mirrored Files

Use mirrored files in a system where disk I/O errors might be expected to occur. Placing a mirrored copy of a file on a different drive than the primary copy of the file can protect against failure of either of the drives. Following a failure of the primary or mirror, the server automatically switches to using only the remaining copy of the file and clients can continue to access the file. Restoring primary and mirror copies involves taking the remaining copy off-line and using a system file copy utility to restore the second copy of the file from the remaining good copy.

Update speed for mirrored files is no different than for non-mirrored files when the updates are written to the server’s cache. When updates are written to disk, however, both the primary and the mirror files are updated, which may cause updates to be slower for a mirrored file compared to a non-mirrored file.

Another option is to use a mirroring approach that operates at the disk level, such as RAID, rather than using the server’s mirroring support. Such an approach may provide the following abilities not supported by FairCom DB’s mirroring facility:

• Easily mirroring the entire contents of a disk.
• Maintaining more than two copies of disk contents.
• Better performance due to parallel disk transfer operations.

Creating Mirrored Files

To create a mirrored file, call a FairCom DB file creation function specifying a name of the form “<primary>|<mirror>”, where <primary> is the name of the primary file and <mirror> is the name of the mirror file. The server automatically applies updates made to the primary file to the mirror file.

FairCom DB also supports mirroring the user/group definition file, FAIRCOM.FCS, and the transaction logs and transaction start files. For details on enabling mirroring for these files, see the Mirroring Control options in the “Advanced Configuration Options” section of FairCom DB Administrator’s Guide.

Summary of FairCom DB Features

Below is a summary of the recommended usage of FairCom DB’s features described in this section:

• When designing a system that uses FairCom DB, first consider using TRNLOG for all files, then determine if requirements can be relaxed to include the use of PREIMG or non-transaction files in some cases. Understand the effect of using PREIMG and non-transaction files.
• Use TRNLOG files for critical data whose updates must be fully recoverable in the event of an abnormal server termination. TRNLOG provides the best fault tolerance but transaction logging has performance implications.
• If using TRNLOG index files, use LOGIDX to speed automatic recovery. LOGIDX may slightly impact performance but can significantly improve recovery times for large TRNLOG indexes.
• Use PREIMG files for atomic operations on files without the assurance of recoverable updates. In the event of an abnormal server termination, lost updates are likely for a PREIMG file and the file must be re-created, rebuilt, or restored from backup.
• Use non-transaction files for data not requiring atomic operations without the assurance of recoverable updates. In the event of an abnormal server termination, lost updates are likely for a non-transaction file and the file must be re-created, rebuilt, or restored from backup.
• Use WRITETHRU (or periodic cache flush calls) to minimize unwritten cached data loss for PREIMG and non-transaction files in the event of an abnormal server termination, but be aware of the limitations of WRITETHRU: lost updates and out of sync data and index files are still possible, and there is no guarantee of consistency for PREIMG files.
• Create FairCom DB data and index files as huge files when the size of the file is expected to exceed 2 GB. Otherwise, at some point the file will reach its maximum size and updates that would cause the file to grow will fail. Also be aware of system limitations on file sizes (operating system and filesystem settings).
• Create FairCom DB data and index files as segmented files when the file size is expected to exceed the available space on a single disk volume or when the file size is expected to exceed the supported maximum file size on the system. Otherwise, at some point the file will exhaust available disk space or will exceed the maximum file size and updates that would cause the file to grow will fail.
• Use mirrored files when disk I/O errors are likely to occur. Mirroring protects against the failure of one copy of the file. Another alternative is to use a disk-level mirroring approach such as RAID.
• Use memory files to create memory-based data and index files that exist only while the c tree Server is active. The final close of a memory file frees its memory, destroying its contents. Because memory file data is not persistent, a common use of memory files is to store data that can be reloaded from an external source.
• Use partitioned files when the ability to easily purge or archive portions of a file is desired.

FairCom DB Files to Include for a Successful Backup Strategy

A FairCom DB SQL dictionary is composed of several files. You will need to back up all of these files if you want to be able to restore the entire SQL dictionary from your backup. By backing up the correct set of files, you will be able to do a full restore and have your SQL dictionary ready-to-go.

The following files need to be backed up if you want to be able to restore the entire SQL dictionary:

• FAIRCOM.FCS
• ctdbdict.fsd
• *.dat in the ctreeSQL.dbs folder
• *.idx in the ctreeSQL.dbs folder
• ctreeSQL.fdd in ctreeSQL.dbs\SQL_SYS

The !FILES section of your dynamic dump script will look like this:

!FILES 
FAIRCOM.FCS
ctdbdict.fsd
ctreeSQL.dbs\*.dat
ctreeSQL.dbs\*.idx
ctreeSQL.dbs\SQL_SYS\ctreeSQL.fdd
!END

More generally, the following files are FairCom internal files that need to be included in backups to allow recovery to function without SKIP_MISSING_FILES YES (in the event these files are changed during the backup interval):

• FAIRCOM.FCS
• SYSLOG*.FCS
• SEQUENCE*.FCS
• DFRKSTATE*.FCS
• ctdbdict.fsd
• *.dbs\SQL_SYS\*.fdd
• RSTPNT*.FCS
• REPLSTATE*.FCS (created on the target server by the Replication Agent)

Testing the Backup

The following test should demonstrate that you have backed up everything you need:

1. Use the dynamic dump utility, ctdump, to back up your files into SYSTEM.BAK.
   

   The !FILES section of your dynamic dump script should include the entries shown earlier.

    
2. Shut down your FairCom Server and rename your C:\<faircom>\server\data folder to a new (unused) folder name, such as data.old:
   C:\<faircom>\server\data.old
3. Create a new data folder and copy the following files to this location:
   ctrdmp.exe
   SYSTEM.BAK
   Your backup script (the text file that contains the !FILES section shown above)
4. Run ctrdmp to restore your files in place.
5. Now start your FairCom Server and connect using FairCom DB Explorer. You should be able to see your restored SQL tables.

Automatic Recovery

Never interrupt the FairCom DB process during automatic recovery. Data and indexes will be left in an unknown state, resulting in probable data loss. This situation may require restoring data from a clean backup to ensure absolute data integrity.

File ID Overflow during Recovery

When a transaction controlled file is opened by the server, it is assigned a unique fileid which is used to reference it in the transaction logs. This number is stored as a 4-byte integer. If large numbers of files are opened and closed repeatedly, available file numbers can be rapidly consumed, causing a "Pending File ID overflow" message to be logged. This message is logged once every 10,000 file opens. The fileid can be reset by shutting down the server cleanly (so no recovery is needed), and removing existing transaction logs (L*.FCS and S000*.FCS).

An FUSE_ERR error (22) during recovery indicates that the FILES setting should be increased to complete recovery. A RECOVER_FILES keyword (NO by default) is also available that controls this specifically.

RECOVER_FILES <number of files | NO>

Restrict Access to FairCom DB Server Files

The FairCom DB Server engine requires exclusive access to the files under its control. Therefore do not attempt to copy, delete or in any way replace a file being controlled by the FairCom DB Server engine while the Server is running. This can lead to data corruption and Server instability.

External Third-Party Utilities

FairCom DB server technology requires that it is the only process operating over any data and index files under its control. Third-party backup (and some virus scan) utilities should never touch any files while FairCom DB has the files open. Doing so on the transaction logs or on a transaction controlled file may terminate the server process. When FairCom DB encounters a write error on these types of files it can no longer guarantee integrity or recovery and must terminate to protect the state of the current system.

If a third-party backup is desired, either use the FairCom DB Dynamic Dump backup facility to create a copy of the files and point the third-party backup to the resulting backup file, or shut down FairCom DB. and then perform the backup.

It is also possible to use a disk-level copy as an acceptable backup strategy, provided FairCom DB is put into a quiet state (using either “Quiesce” or File Block) before the hardware-level copy.

FairCom DB V9.2 and later for Windows also includes Windows VSS support (Volume Shadow Service) for enhanced integration with third-party backup tools.

Safely Copying FairCom DB Controlled Files

The following are important points for FairCom DB administrators to observe:

• Do not use system copy utilities to copy FairCom DB data and index files or server administration files (*.FCS) while they are in use by FairCom DB. To safely copy files while the server is operational follow the approaches discussed in the “Online Backup Options” of this document.
• Do not use third-party file scan or backup utilities on FairCom DB data and index files or server administration files (*.FCS) while they are in use by FairCom DB.

Failing to observe the above recommendations could prevent FairCom DB from accessing the files, which could lead to errors returned to client applications or could cause the server to terminate abnormally. Consider setting file permissions on the c tree data and index files and server administrative files to ensure that only FairCom DB can access these files while the server is running.

Administrators should also be aware of the use of file IDs in the header of FairCom DB data and index files. When a file open is attempted, FairCom DB checks to see if either a file with the same name has already been opened, or if a file with the same unique ID has already been opened. In either case, the match means that a physical file open is not required. Instead the open count for the file is incremented. Checking the unique file ID permits different applications and/or client nodes to refer to the same file with different names: maybe different clients have different drive or path mappings.

However, if two different files have the same ID (a 12-byte value comprised of a Server ID, a time stamp, and a transaction log sequence number), problems could arise because the second file would not actually be opened. The ID is constructed so that no two files could have the same ID unless someone copies one file on top of another. See the warning listed below.

When a file without a matching name does match the unique file ID, a message is sent to the system console indicating the names of the two files involved. This message can be suppressed by adding the following entry to FairCom DB configuration file:

MONITOR_MASK MATCH_FILE_ID

• ctfileid.c is located in ctree\samples\special\utils and replaces the previous informal and undocumented utilities, updateid.c and newid.c.

Copying Over Transaction-Controlled Files

Copying over transaction-controlled files poses an additional concern. Even if the server is stopped, it may still need to go through recovery on restart. If files were copied over existing files that need to go through recovery then two outcomes are possible:

1. If the file IDs do NOT match, the server will detect a file ID mismatch during recovery and fail to start, with messages logged to CTSTATUS.FCS detailing the problem.
2. If the file IDs match, the server will apply changes from the transaction logs to the new version of the file which are not valid, resulting in the silent corruption of the new data/index.

To verify that the server does not need to go through recovery before copying over existing transaction controlled files, check that CTSTATUS.FCS ends with the normal shutdown sequence, and includes the messages "Perform system checkpoint" and "Server shutdown completed." (The system checkpoint occurred when all connections were closed and the server wrote a clean checkpoint at shutdown so it will not need to perform a recovery upon restarting.)

Tue Sep 23 16:58:08 2014
 - User# 00015	Server shutdown initiated
Tue Sep 23 16:58:09 2014
 - User# 00015	Communications terminated
Tue Sep 23 16:58:09 2014
 - User# 00015	Perform system checkpoint
Tue Sep 23 16:58:09 2014
 - User# 00015	Server shutdown completed
Tue Sep 23 16:58:09 2014
 - User# 00015	Maximum memory used was 1544324594 bytes
Tue Sep 23 16:58:09 2014
 - User# 00015	Maximum number of lock hash bins for a file was 16384
Tue Sep 23 16:58:09 2014
 - User# 00015	Maximum number of lock hash bins for a user was 16384
Tue Sep 23 16:58:09 2014
 - User# 00015	Maximum number of preimg hash bins for a user was 16384

File Rebuilds

In the event a problem occurs and requires files to be rebuilt, it is possible to rebuild the files using either a stand-alone utility or the FairCom DB Server. To avoid the potential for wrong PAGE_SIZE settings, and creating indexes without the TRNLOG file mode, FairCom recommends performing all rebuilds through the FairCom DB Server.

• The FairCom DB V9 Server includes a new keyword, MAX_K_TO_USE, controlling the amount of memory utilized for file rebuilds. The default is 64 KB. Increasing this value to as large as practical (not starving the operating system for memory) can provide faster rebuilds than using single-user rebuild utilities.
• The stand-alone utility has traditionally been a faster method to rebuild files; however, it is important to ensure that the index page size is set appropriately (using the PAGE_SIZE switch). The FairCom DB Server defaults to 8192 byte page sizes (64 sectors - 128 bytes/sector), while FairCom DB standalone technology defaults to 2048 bytes (16 sectors). The stand-alone rebuild utility should also be built with FairCom DB Transaction Processing Logic enabled such that files are created with the appropriate TRNLOG file mode.

Java Requirements for FairCom DB SQL

Here are the Java requirements for past and current versions of FairCom DB SQL. Remember, Java is used for Stored Procedures, Triggers, and User Defined Functions. If the Java runtime engine (JRE) is not available, then these features are unsupported. The Java SDK is required to compile the source Java modules prior to running.

Better Performance with NXTVREC() vs GTVREC()

The FairCom DB SNAPSHOT feature provides a wealth of performance and tuning information, including function call timing. Profiling FairCom DB function calls can provide a very precise tuning method for looking at performance issues. Consider an application that spends an inordinate amount of time processing GTVREC() calls; 61% of the total FairCom DB call time is spent in GTVREC():

function    count  elapsed  average  % of tot 
GTVREC    1562390    47368  0.0303   61% 
TRANEND    230398    13092  0.0568   17% 
DELVREC    883241    11051  0.0125   14% 
EQLVREC    998513     4251  0.0043   6% 
TRANBEG    230399      661  0.0029   1% 
LTVREC     230445      489  0.0021   1% 

Here's a suggestion that can improve performance in this situation. There may be GTVREC() calls that can be replaced with NXTVREC(). For example, the function monitor log showed the following sequence of calls:

TRANBEG 
EQLVREC ../CURRENTFILESCANPAGES/Listings.idx 
DELVREC ../CURRENTFILESCANPAGES/Listings.dat 
GETCURK ../CURRENTFILESCANPAGES/Listings.idx M#1 
GTVREC ../CURRENTFILESCANPAGES/Listings.idx M#1 
TRANEND 

In this case, if you reading the key of the record that you just deleted and then reading the next record you could simply call NXTVREC() like this instead:

TRANBEG 
EQLVREC ../CURRENTFILESCANPAGES/Listings.idx 
DELVREC ../CURRENTFILESCANPAGES/Listings.dat 
NXTVREC ../CURRENTFILESCANPAGES/Listings.idx M#1 
TRANEND 

Here's why we suggest you use NXTVREC() instead of GTVREC() when possible:

GTVREC() does a full key search starting from the root node. NXTVREC() already knows which leaf node corresponds to your current ISAM position (that was set when you called EQLVREC()), and so it goes directly to that node and searches from there for the next key value.

What can happen in some cases is that leaf nodes become empty as keys were deleted, and before the delete node thread had removed them from the index tree, the application made a number of calls to GTVREC() which visited those nodes and added them to the delete node queue again. With these simple code changes above, no duplicate entries will be added to the delete node queue, however, there is slight overhead in performing the tree search and checking if the entry is already in the delete node queue. We expect NXTVREC() to be more efficient than GTVREC() as it starts at the current leaf node.

8 Steps to a Fast Data Load

The following eight steps can be used to speed up the process of inserting data into your FairCom DB database:

1. Turn off transaction processing
   Transaction processing control can be turned off during these steps. Assuming you have the data preserved where you can start over in the event of a problem, you don't need transaction processing control for this process. If you desire to have transaction processing control down the road, then ensure you create the data and index files with TRNLOG file mode active. Once you create the file initially with TRNLOG enabled, you can disable TRNLOG programmatically to speed up the operations as indicated in the FairCom DB Programmer Reference Guide in the topic titled Transaction Processing On/Off. Or you can call the cttrnmod program, explained in the topic titled cttrnmod - Change Transaction Mode Utility.
2. SHARED MEMORY protocol
   If at all possible, run the data load program on the same machine hosting the FairCom DB data. This will allow the Server to use the shared memory communication protocol which is much faster than TCP/IP.
   If you need to use TCP/IP, increase the number of threads to multiple threads per CPU core to compensate for the network latency.
3. Direct I/O (V11 and later only)
   When using FairCom DB V11 and later, please review Direct I/O support. This will provide some help when building and working with larger files. See Linux Direct I/O (UNBUFFERED I/O) Performance Support in the V11 Update Guide.
4. Multi-thread the inserts
   The next way to boost performance is to use one of the non-relational FairCom DB APIs, such as the ISAM or the FairCom DB API API.
   If you can break the data coming into the program into multiple chunks, these APIs allow you to take advantage of multi-threading to do the inserts. A good rule of thumb is to use one or two threads for each virtual CPU core.
5. Disable indexes using CTOPEN_DATAONLY file mode
   You can drop the index support when you are doing the data load. This will get the data into the data file in the fastest manner and will avoid the time it takes to update your indexes on the fly.
   • See Opening a Table in the FairCom DB API C API Developer's Guide.
6. Insert in batches
   With V10 and newer, we've added batch inserts. This is quicker than individual adds because we can maximize the OS packet size and get the maximum amount of data fed into the FairCom Server process with each batch call.
   • Review FairCom DB API batches.
7. Turn transaction processing back on
   See the links given previously in Tip 1 for instructions for turning TRNLOG back on:
   • Transaction Processing On/Off
   • cttrnmod - Change Transaction Mode Utility
8. Rebuild to create indexes
   Once you have all of the data loaded into the data files, do a rebuild to generate the indexes. This is the fastest way to build the indexes because you now have all of the data in the FairCom DB data files, so the indexes can be built from scratch with a known set of data. To generate your indexes, use the function call ctdbRebuildTable discussed in the FairCom DB API Developer's Guide. Or you can call the ctrbldif program discussed in the FairCom DB Programmer's Reference. To improve the performance of an index rebuild through the Server, increase these two settings in your ctsrvr.cfg file:

MAX_HANDLES
SORT_MEMORY

The tips given above should help you complete the data load process in much less time than a single-threaded program using ctdbWriteRecord() inserts. In one customer case where we have used this process, the time to load 2.2 billion records, with several indexes, went from approximately 2 weeks, to less than 2 days.

Calculating Memory Usage

The FairCom Server startup memory requirements can be reasonably approximated with the following equation:

Base line Memory =
    Server EXE size + 1 MB +
    Communications Library size (if applicable) +
    Data cache (DAT_MEMORY) +
    Index buffer (IDX_MEMORY) +
    (900 bytes * Number of files (FILES)) +
    (325 bytes * Maximum number of connections (CONNECTIONS)) +
    (16000 bytes * Number of actual connections) +
    (256 bytes per connection per file actually opened))

Note the following points:

• DAT_MEMORY and IDX_MEMORY default to 225,000 bytes each.
• FILES defaults to 100.
• CONNECTIONS default to 128.
• IDX_MEMORY is the MAX of:
  • IDX_MEMORY or
  • 3 * CONNECTIONS * (PAGE_SIZE + 400), where PAGE_SIZE defaults to 8192.

The following locking/transaction processing related items should be considered when approximating the FairCom Server dynamic memory requirements:

• Each record locked consumes 24 bytes.

For transaction processing files only:

• Each data record written consumes (record length + 42) bytes.
• Each index operation consumes (key length + 42) bytes.

See also:

• Server Memory Calculations

Controlling Server Memory

This section is a summary of the FairCom Server memory limit behavior and associated transaction controls. The Server has two means of controlling memory use:

• User memory limits
• Total memory limit

The user memory limit is specified through system-wide defaults using the USR_MEMORY and GUEST_MEMORY configuration keywords, which take as their arguments the memory threshold defaults at the user level. The GUEST_MEMORY limit applies to applications that connect to the FairCom Server without a user ID. The USR_MEMORY limit applies to applications that do provide a user ID as part of their Server logon. These thresholds default to zero, which implies no limit on user memory. The system-wide user memory defaults can be overridden by specifying a user memory limit when adding a user ID to the Server administrative database with the ctadmn program.

The user memory limit can be either a guideline or an absolute limit. The memory limit defaults to a guideline. In the guideline mode, when a user exceeds the user memory limit (if set), an attempt is made to flush preimage memory to disk. However, the preimage control block still resides in memory. In any event, the user is able to continue getting more memory even if the user limit is exceeded. The effect of exceeding the guideline limit is two-fold:

1. Preimage memory is swapped to disk, and
2. The OPS_MEMORY_SWP bit in the user’s FairCom DB status word is turned on, (see SetOperationState in the FairCom DB Programmers Reference Guide). This bit stays on until a new transaction is started. In absolute mode, memory is denied the user if the limit is exceeded, which will lead to aborted transactions and/or errors during update operations.

TOT_MEMORY is a deprecated keyword. FairCom does not recommend setting a total memory limit.

To avoid large amounts of memory use by an application performing large transactions, a special automatic commit mode can be enabled through Begin().

Calculating File Storage Space

Approximate the file storage space used by the FairCom Server with the following equation:

Storage requirement in bytes =
    server size +
    communication libraries (if applicable) +
    utility program size(s) +
    transaction log files +
    data files +
    index files

The transaction log files initially increase in size as adds, deletes and updates are performed on the FairCom Server controlled files. The FairCom Server configuration keyword LOG_SPACE, which defaults to 10MB, indicates the amount of disk space allocated to store active transaction logs. Generally, the active transaction log files will not exceed the LOG_SPACE value. If transaction processing is used, it is not recommended to decrease the LOG_SPACE keyword.

Calculating Index Sizes

Index sizes are tricky to accurately predict. However, there are a few calculations to give you the bounds to expect on index size based on the following criteria:

• index node size (page size)
• key length
• four or eight byte record offset value
• six-byte transaction number for transaction controlled files

Consider an index with a four byte key size and the following information provided by the ctflvrfy utility:

• Disk size was nearly 1GB
• 1 root node
• 17 internal nodes at the first level
• 2692 internal nodes at the second level
• 451,645 leaf nodes. (All key values are stored in the leaf nodes)
• 49,604,909 key values

There are 454,355 total index nodes in this example.

As this is a transaction controlled, HUGE file (eight bye offsets), actual metrics can be computed as follows:

454,355 * 2048 node size = 930,519,040 bytes (~1GB)

49,604,909 key values / 451,645 leaf nodes = ~109 keys / node

key length (4) + 8 (record offset) + 6 (transaction #) = 18 bytes / key

109 * 18 = 1962 bytes / node on average.

Each leaf node can hold a maximum of 112 keys.

2048 byte node size - 28 byte node header = 2020 available bytes.

2020 bytes / 18 = 112.

Note in this example that the leaf nodes are nearly full. In normal key distributions, you can expect index nodes to be 1/2 full on average, thus, this index could theoretically be much larger with the same number of keys.

Migrating Data Between Platforms and Operational Models

FairCom created FairCom DB with the intent to provide cross-platform support with a standardized database, no matter which operational model is used or what mix of platforms are involved. In some cases, this capability is invisible to the developer, in others careful consideration is required to allow various applications to work together.

This section provides an overview of topics covered in more detail in later sections. It is intended to provide background information to help you choose the appropriate operational model for your present and future implementations.

The considerations are broken down by operational model: Single-user standalone, Standalone Multi-user, and client/server. Applications from different models should not share files concurrently, though files can be exchanged as described in File Migration.

Single-User Standalone

Single-user applications should not share FairCom DB files simultaneously. There is no mechanism in the single-user library to prevent multi-user interference. However, files can be exchanged between different instances of an application for sequential, not simultaneous, use. See File Migration for details.

Multi-User Standalone

The multi-user standalone model has the same issues with exchanging files described in File Migration in the FairCom DB Programmers Reference Guide.

However, sharing files between platforms simultaneously introduces additional complications with byte order and alignment, and adds the issue of record locking.

• Byte order is critical. Applications expecting different byte orders cannot seamlessly share files in this model.

However, the UNIFRMAT define allows HIGH_LOW applications to store and retrieve data in LOW_HIGH format instead of HIGH_LOW. This allows the files to be shared. If a record schema is present in the form of a DODA, data records will be adjusted automatically. See the FairCom DB Features chapter for more information on UNIFRMAT. Record Schemas in the FairCom DB Programmers Reference Guide describes the DODA.

• All applications sharing FairCom DB files must use the same alignment settings. If not, the application developer is responsible for ensuring record layouts are consistent.
• Locking between cross-platform applications introduces the complications of different locking standards and network file sharing mechanisms.
  • In multi-user standalone mode, FairCom DB uses three different locking systems to deal with the different locking standards used by various operating systems. Any combination of systems sharing files must use the same locking method with the same settings or they will not properly identify each other’s locks. See Multi-User Concepts in the FairCom DB Programmers Reference Guide for information on locking methods and their implementation.
  • Different network file sharing systems handle locks in different ways. Sometimes, this leads to incompatible locking systems between applications. When mixing applications from different environments, verify that locks are working. A simple test is to use a sample application, such as ctixmg, a FairCom DB example program. In one instance of the application, enable locking and add a record (which locks that record automatically). On the other platform, open ctixmg, enable locking and attempt to read the record added by the other application. If ctixmg returns an error DLOK_ERR (42), locking is functioning properly. Otherwise, there is a locking issue requiring troubleshooting.
  • Note, the FairCom Server relieves the programmer of many of the issues regarding heterogeneous networking and data integrity as described in the following sections.

Client/Server

The client/server model must also deal with byte order, but the FairCom Server handles this automatically when a record schema, in the form of a DODA, is present in the data file. Clients manipulate the data in their native format, and the FairCom Server manipulates the data in its native format. Translation takes place automatically during communication between the clients and the server.

This automatic translation allows a variety of clients using a variety of standards to cooperate with minimal effort on the part of the developer. For example, FairCom Servers running on Mac, Windows, and Unix systems can communicate simultaneously with any combination of clients on Mac, Windows, and Unix systems. This allows the customer flexibility in their selection of client and server systems, and allows clients in a mixed environment to share data.

See Record Schemas in the FairCom DB Programmers Reference Guide for more information about the DODA.

The client/server models also require the extended initialization functions, such as InitISAMXtd(), which pass logon information and trigger automatic target key transformation. See TransformKey in the FairCom DB Programmers Reference Guide for more information.

File Migration

Applications of any model can exchange files, sharing them sequentially but not simultaneously. Ensure the applications involved do not have the files open when they are copied or moved. If the application in question is the FairCom Server, this is especially important. See Copying Server Controlled Files in the FairCom DB Programmers Reference Guide for additional information.

Issues that affect file exchanging include byte ordering of numeric values and alignment of data structures. FairCom DB uses the same default for byte order as the hardware CPU and the default structure alignment dictated by the compiler.

• Byte Ordering is a processor-related issue. Intel-based processors store numeric values least-significant byte first (LOW_HIGH), while other processors store numeric values most-significant byte first (HIGH_LOW). So a four-byte integer containing the value “16” stored on an LOW_HIGH system as the hexadecimal value 10 00 00 00 would be stored on a HIGH_LOW system as the hexadecimal value 00 00 00 10. The order of the bits is consistent within the bytes, but the order of the bytes in the numeric value is reversed. By default, FairCom DB files are stored in the native format to the operating system, so files created on a HIGH_LOW machine cannot be exchanged directly with applications on a LOW_HIGH machine. See Portable Data Through UNIFRMAT Support, in the FairCom DB Programmers Reference Guide, which allows HIGH_LOW systems to work with LOW_HIGH files.
• Different compilers align data structures in different ways, but most have compile-time options allowing the alignment to be adjusted. Some compilers default to 1-byte alignment, which means structures are not adjusted in any way. With 2-byte or higher alignment, variables are aligned to the boundary matching the alignment or the variable’s size, whichever is smaller. There is a potential for the compiler to add dead space within a data structure. See Buffer Regions in the FairCom DB Programmers Reference Guide for more information on alignment.

Some of the common problems caused by alignment differences are:

1. Record lengths when different applications use different amounts of padding;
2. Indexing when padding shifts key segments to different locations than specified in the ISEG structure;
3. Data corruption when applications align the record buffer differently.

Use the FairCom DB ctunf1 reformat utility to adjust the alignment and byte ordering for a given file before exchanging it with another platform. See CTUNF1 - File Reformatting Utility in the FairCom DB Programmers Reference Guide for more information on this utility.

Migrating Your Application Between Operational Models

FairCom DB is designed to simplify the transition between different platforms or operational models. For the most part, changing models is simply a matter of compiling new libraries and re-linking your application.

However, some concepts are specific to one operational model. The transition can be made smoother by reading the entire section titled Operational Models in the FairCom DB Developer's Reference Guide.

Single-User to Multi-User Standalone or Client/Server

Changing from a single-user application to multi-user (either standalone or client/server) introduces the possibility of multi-user interference. This is prevented by proper locking techniques and/or transaction processing (available with the FairCom Server). See Multi-User Concepts and Data Integrity in the FairCom DB Programmers Reference Guide for more information.

A single-user standalone application can support transaction processing. If switching to multi-user standalone, transaction processing will not be available. Transaction processing is possible when migrating from single-user standalone to multi-user client/server, since both support transaction processing. In fact, this simplifies the locking issue since locking can easily be added to existing transaction processing calls. For example, replacing Begin(ctTRNLOG) with Begin(ctTRNLOG | ctENABLE). See Adding Transaction Processing and Data Integrity in the FairCom DB Programmers Reference Guide for more information.

Multi-user Standalone to Client/Server

A frustrating scenario, relating to users migrating from FairCom DB Multi-User FPUTFGET model to the FairCom Server, needs to be discussed. The dilemma relates to users of the FairCom Server receiving FCRP_ERR (14) error codes when opening server based files. The message text for the FCRP_ERR (14) error states “File corrupt at open”. Keep in mind that this scenario only exists due to a power loss, a machine being tuned off without stopping the server, or a miscoded program that does not close its files, only when the FairCom DB developer chooses NOT TO implement transaction control as suggested for the FairCom Server.

The FCRP_ERR (14) did not exist in the multi-user mode, so therefore a scenario was created in which customers, who have been running successfully for years, upgrade to the FairCom Server for stronger data integrity, start to receive “corrupt data errors”. The error is due to either the user shutting off the power to their server computer or an application not properly closing its files. Under FPUTFGET, the end-user (or the application) could get away with this. Now they get errors. Can you imagine the end-user frustration?

First, it is in order to discuss the FCRP_ERR (14) error. In FairCom DB single-user mode and under the FairCom Server, a file update flag is maintained within the header of each file. When a file is written to, in any manner, this flag is set to indicate the file has been updated. When the file is closed, this flag is re-set, indicating that the application has properly closed the file. Any attempt to open a file which has been marked update and which has not been properly closed, will result in a FCRP_ERR (14) error. The FairCom Server has no way of knowing the state of this file, and therefore must return this state back to the application as an integrity error/warning.

In FairCom DB’s multi-user FPUTFGET mode, the write operation is immediate secured to disk and therefore this flag is not maintained nor considered. An error 14 simply did not exist under FPUTFGET. An application could exit without properly closing a file, or a computer or file server (i.e.: NetWare Server) could be powered off (switched off by end-user) without causing any immediate catastrophic results for the customer. Perhaps the last “enter-key” entry at the time of power loss could be out of sync, yet the entire file would not be in question.

So now, take the scenario where one has a user, who has been running for years under FPUTFGET, who upgrades to the FairCom Server. As a reasonable half-step, the developer chooses to start with the application running in the same matter as it did with FPUTFGET, and intends to add the necessary code changes for transaction processing at a later date. The developer’s efforts are easy, simply re-link the application with the FairCom DB Client library instead of the FPUTFGET library, and it is ready to go. Now, the updated application and new FairCom Server are deployed at the customer’s site. The customer proceeds to operate in the same manner before, now complimented by the Client/Server architecture, experiencing less network traffic, and better performance due to the Server’s data caches. Now, as before, the customer “flips the switch” at the end of the day, just like they have done for years (without you knowing it). CATASTROPHIC situation. Now all data/index files open at the time are in question. Because of the FairCom Server’s caches, not just the last “enter-key”, but the entire file is now undefined. A FCRP_ERR (14) is properly returned the next time the file is opened. The user, who is used to coming in the next day, switching on the machine and going back to work, was now calling the developer’s tech support line reporting numerous error 14 messages and corrupt data.

The following solutions are available to solve this dilemma:

1. Of course, the ultimate solution is to secure the data using transaction control. This way all entries are 100% secure, up to the point of the last transaction commit. When migrating from FPUTFGET, this solution requires application code changes to implement the transaction control, as transaction processing does not exist in FPUTFGET.
2. When the FairCom Server was introduced, a file mode was added to FairCom DB called ctWRITETHRU. If a developer defined a file as ctWRITETHRU, all its write operations would be flushed to disk in the same manner of the FPUTFGET model. What is different about server ctWRITETHRU versus the FPUTFGET is that the server continues to maintain the update flag in the file’s header as described above. Therefore, if the machine is switched off, the data is in the same state as the FPUTFGET model. The exception is that the open request returns the 14 error, where under FPUTFGET it does not. The server keyword COMPATIBILITY WTHRU_UPDFLG exists for the FairCom Server to instructs the server not to maintain this update flag for ctWRITETHRU files. Therefore to completely “clone” the FPUTFGET results, a developer needs to add the ctWRITETHRU file mode to all file definitions within the program, and activate the COMPATIBILITY WTHRU_UPDFLG keyword in the server’s configuration file (ctsrvr.cfg). (Actually, this is better than a clone, for all read operations may safely remain cached under the FairCom Server.)
3. There is an additional feature which prevents the need to change the client application. Once the server configuration keyword COMPATIBILITY FORCE_WRITETHRU has been activated, all files opened in non-transaction processing mode (nonTRNLOG), are automatically set to ctWRITETHRU. In other words, this has the same effect as the developer changing all the file modes to include ctWRITETHRU, but because this is automatically done on the server side, the client side application does not need to be touched. This solution is the best and easiest for developers who want to take an easy step from FPUTFGET to Client/Server without changing the application.
   By activating the following keywords in your server’s configuration file (ctsrvr.cfg), the bases are covered:

   COMPATIBILITY  FORCE_WRITETHRU
   COMPATIBILITY  WTHRU_UPDFLG

In addition, a warning message helps the developer recognize this vulnerability. If a server does NOT have COMPATIBILITY WTHRU_UPDFLG in its configuration file, and if non-transaction files are opened without ctWRITETHRU in the file mode, then a warning will be issued in CTSTATUS.FCS concerning the vulnerability to FCRP_ERR (14) if a server is not shut down properly. The warning is only entered into CTSTATUS.FCS one time after each server startup when a vulnerable file is detected. The keyword STATUS_MASK WARNING_FCRP_ERR may be added to the configuration file to eliminate this warning message.

See the COMPATIBILITY PREV610A_FLUSH keyword for a good balance between performance and recoverability.

See also:

• COMPATIBILITY FORCE_WRITETHRU
• COMPATIBILITY WTHRU_UPDFLG
• COMPATIBILITY PREV610A_FLUSH

Adding Transaction Processing

Adding transaction processing to an application permits complex, atomic updates and automatic recovery in the case of software or hardware failures. Transaction processing can be added with the transaction processing sub-API or with SetOperationState().

To add efficient transaction processing, use the transaction processing sub-API described in Data Integrity in the FairCom DB Programmer's Reference Guide. If your application is already prepared for multi-user use, replacing LockISAM(ctENABLE) calls with Begin(ctTRNLOG | ctENABLE) and replacing LockISAM(ctFREE) calls with Commit(ctFREE) or Abort(ctFREE) calls, depending on the status of the transaction at that point, is a simple method of implementing transaction processing. See ctixmg.c for an example and Data Integrity for more details.

To add transaction processing quickly, though not in the most efficient manner, use SetOperationState() with the OPS_AUTOISAM_TRN mode. This performs a transaction around each ISAM update. See SetOperationState for more details. This method does not effectively lock updates. You will still need to use some form of locking control. Consider the OPS_LOCKON_GET mode for SetOperationState() to minimize network traffic.

Once the application supports transaction processing, add either of the transaction processing file modes, ctTRNLOG or ctPREIMG, to the data files requiring transaction processing using the UpdateFileMode() function. Index files must be rebuilt to add or remove transaction processing. For additional information on Transaction Processing, please see Data Integrity.

Single-Threaded to Multi-Threaded

Switching to a multi-threaded, ctThrd, library is similar to adding transaction processing: superficially very easy, but with many implications.

As a minimum when using the ctThrd library, you must implement the ctThrd API. You must call ctThrdInit() before initializing FairCom DB, just as you must initialize c‑tree before calling any other FairCom DB API functions.

Any new threads that call FairCom DB API functions must be created with ctThrdCreate() or attached with ctThrdAttach().

In the Multi-threaded Standalone model, do not mix the FairCom DB Instance API (RegisterCtree(), etc.) with the ctThrd API. Each thread is its own instance of FairCom DB, and handles instance switching via the ctThrd functions. This is not an issue with the Multi-threaded Client Model.

See ctThrd Function Overview in the FairCom DB Programmers Reference Guide for more detail.

SQL Data Load Tips

ISQL is very useful for running interactive SQL statements. As an option, it allows creating and executing scripts to automate certain operations. These scripts can be used for running DDL statements (Data Definition Language: create table, create index, alter table, etc.) and DML statements (Data Manipulation Language: insert, update, etc.). DML statements can become quite large if they must insert or update every record in a large table. ISQL is not intended for massive data load operations which require a large number of Insert statements.

FairCom provides tools that are more appropriate for manipulating large numbers of records:

• dbload loads records from an input data file into tables of a database.
• dbdump unload utility writes the data in a database to a file.