Copyright Notice. Information in this document is subject to change without notice.

(1)

(2)

(3)

Copyright Notice

Copyright © 1992-2016 FairCom Corporation. All rights reserved. No part of this publication may be stored in a retrieval system, or transmitted in any form or by any means, electronic, mechanical, photocopying, recording or otherwise without the prior written permission of FairCom Corporation. Printed in the United States of America.

Information in this document is subject to change without notice.

Trademarks

c-treeACE, c-treeRTG, c-treeAMS, c-tree Plus, c-tree, r-tree, FairCom and FairCom’s circular disc logo are trademarks of FairCom, registered in the United States and other countries.

The following are third-party trademarks: AMD and AMD Opteron are trademarks of Advanced Micro Devices, Inc. Macintosh, Mac, Mac OS, and Xcode are trademarks of Apple Inc., registered in the U.S. and other countries.

Embarcadero, the Embarcadero Technologies logos and all other Embarcadero Technologies product or service names are trademarks, service marks, and/or registered trademarks of Embarcadero Technologies, Inc. and are protected by the laws of the United States and other countries. Business Objects and the Business Objects logo, BusinessObjects, Crystal Reports, Crystal Decisions, Web Intelligence, Xcelsius, and other Business Objects products and services mentioned herein as well as their respective logos are trademarks or registered trademarks of Business Objects Software Ltd. Business Objects is an SAP company. HP and HP-UX are registered trademarks of the Hewlett-Packard Company. AIX, IBM, POWER6, POWER7, and pSeries are trademarks or registered trademarks of International Business Machines Corporation in the United States, other countries, or both. Intel, Intel Core, Itanium, Pentium and Xeon are trademarks or registered trademarks of Intel Corporation or its subsidiaries in the United States and other countries. Microsoft, the .NET logo, the Windows logo, Access, Excel, SQL Server, Visual Basic, Visual C++, Visual C#, Visual Studio, Windows, Windows Server, and Windows Vista are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. Novell and SUSE are registered trademarks of Novell, Inc. in the United States and other countries. Oracle and Java are registered trademarks of Oracle and/or its affiliates. QNX and Neutrino are

registered trademarks of QNX Software Systems Ltd. in certain jurisdictions. CentOS, Red Hat, and the Shadow Man logo are registered trademarks of Red Hat, Inc. in the United States and other countries, used with permission. UNIX and UnixWare are registered trademarks of The Open Group in the United States and other countries. Linux is a trademark of Linus Torvalds in the United States, other countries, or both. Python and PyCon are trademarks or registered trademarks of the Python Software Foundation. OpenServer is a trademark or registered trademark of Xinuos, Inc. in the U.S.A. and other countries.

Btrieve is a registered trademark of Actian Corporation.

ACUCOBOL-GT, MICRO FOCUS, RM/COBOL, and Visual COBOL are trademarks or registered trademarks of Micro Focus (IP) Limited or its subsidiaries in the United Kingdom, United States and other countries.

isCOBOL and Veryant are trademarks or registered trademarks of Veryant in the United States and other countries.

All other trademarks, trade names, company names, product names, and registered trademarks are the property of their respective holders.

Portions © 1987-2016 Dharma Systems, Inc. All rights reserved. This software or web site utilizes or contains material that is © 1994-2007 DUNDAS DATA VISUALIZATION, INC. and its licensors, all rights reserved.

(4)

FairCom Typographical Conventions

Before you begin using this guide, be sure to review the relevant terms and typographical conventions used in the documentation.

The following formatted items identify special information.

Formatting convention Type of Information

Bold Used to emphasize a point or for variable expressions such

as parameters

CAPITALS Names of keys on the keyboard. For example, SHIFT,

CTRL, or ALT+F4

FairCom Terminology FairCom technology term

FunctionName() c-treeACE Function name Parameter c-treeACE Function Parameter

Code Example Code example or Command line usage

utility c-treeACE executable or utility

filename c-treeACE file or path name

CONFIGURATION KEYWORD c-treeACE Configuration Keyword

(12)

(13)

1. c-treeACE Architectural Concepts

1.1 Server Advantages vs. Multiuser Standalone

Applications that rely on the c-tree “standalone” architecture use file and data management operations to directly manipulate groups of files and information in those files. The focus is on controlling files, and data, directly from an application. In a multi-user environment, as users and needs grow, the simple data access afforded by the multi-user standalone architecture

(sometimes referred to as “FPUTFGET”) become cumbersome, unstable, or impossible to implement and maintain in a practical manner due to the fact that files and user applications reside on physically separate systems. The data integrity of the application is largely dependent on the file locking provided by the operating system -- complexities that are amplified when client machines are not on the exact same version and patch level.

The client/server architecture divides a logical application into a front-end client and a back-end database server. The client interacts with the application user to determine specific needs and only sends relevant requests to the server asking that the needed service be performed. The server accepts this request, performs the needed service, and relays the results back to the client. The client can then present the information to the user through whatever interface is appropriate. The server acts as a central traffic cop and can support many clients simultaneously. In addition, clients may even be different applications for a truly robust application suite.

The key advantage becomes a division of labor between the client application and the centralized database server. Clients focus solely on the tasks they're intended to carry out for the user, while the server is solely dedicated to the most efficient data handling possible. This also eliminates the duplication of valuable resources such as memory and disk space that exists when these data management capabilities are incorporated into each application.

The most visible advantage, however, is pure performance. The complex issues of multiple users storing and retrieving data can be isolated within a single server process. As users increase in a strictly file-based environment, contention for those file resources quickly adds up. The server can consolidate those resources and take advantage of in-memory caching of data and indexes for scalability up to hundreds and even thousands of concurrent client connections. This is simply not possible with the standalone multi-user model.

(14)

Client/server and multi-tier computing have become the models of choice in database systems for the most basic of reasons: increased speed, control, and efficiency in data management.

c-treeACE brings these benefits over traditional standalone models:

 Performance: In memory data and index caches mean data is immediately available to clients without expensive disk I/O. Standalone multi-user applications must flush every read and write operation to disk.

 Scalability: Scale from a few users to many users with a simple activation key. The

multi-threaded server engine provides enhanced concurrency control,and allows many more concurrent users to efficiently operate than is possible in a standalone multi-user

environment.

 Security: Centralized data management provided by the server makes available many additional security features that are not practical in a standalone environment, such as user and file passwords; user, group, and file permissions; file and communication encryption; and logon control options. With only one process requiring access to data and index files, OS file permissions can be applied to restrict access to these files from casual users providing another layer of security. This is critically beneficial in health and financial industries in meeting HIPPA and other compliance measures.

 Data Integrity: Transaction processing makes available a multitude of advanced features not possible in multi-user standalone applications. Automatic-recovery with roll-forward/rollback control, dynamic "hot" backups, and a unique transaction history feature for elevated security tracking of changes are among these. Heavily used indices are automatically maintained for optimal speed and size, eliminating the need for rebuilding for performance.

 Flexibility: Heterogeneous support means Unix/Windows/Mac clients can share data across any combination of client/server platforms. Connect any client to any Server.

 SQL: c-treeACE SQL provides complete relational capabilities and is only available as an integral component of server-based models. SQL extends access to data through a variety of popular programming interfaces including ODBC, JDBC, ADO.NET, and PHP. These industry standard protocols allow data integration among multiple systems in the enterprise

(15)

environment, vastly enhancing the availability of information through multiple channels including the web. Server-side stored procedures and triggers enforce business integrity rules on data and access for the ultimate in data integrity and security.

1.2 c-treeACE Storage System Support

Background

Database systems are by nature designed to store and maintain data in a durable manner. This requires a storage medium, typically a hard disk drive. More data required more storage and as a result, storage has become cheaper and vastly more scalable. Other important considerations of a storage architecture are ease of backup, partitioning, and mirroring of data. As storage systems continue to emerge and diversify, FairCom is frequently asked about support for various storage architectures. The following information is provided to address these most common inquiries. Database Caching

The c-treeACE database engine provides in-memory caching of updated data and index information for performance. This data is periodically flushed to disk with standard IO filesystem calls.

Filesystem Semantics

Depending on the nature of the data, such as the case with an index node page or a transaction log buffer, these may be requested to go directly to disk to guarantee absolute data integrity. To provide these integrity guarantees, it is important the data storage system provides adequate capability. The database engine must know that a write request returned successfully to ensure the data is secured to the storage medium. Not only must the write request succeed, the ordering of the requests must be respected. This may not be the case with all storage architectures. Two major categories of shared storage are described below.

Storage Array Networks (SAN)

SAN devices are a proven and powerful data storage technology that can offer impressive performance characteristics. SAN devices share the raw physical storage device. For large organizations requiring sophisticated data storage and fast response times, a SAN architecture is often the recommended approach. In general, SAN technologies satisfy the ordering of writes and guarantees data integrity once a write request returns, even if the SAN device itself provides internal buffering of data for enhanced performance. The most secure SAN devices provide battery backup of their internal caches for the utmost of protection.

c-treeACE is successfully deployed in many SAN environments. While relatively expensive compared to local storage, FairCom recommends and supports SAN storage architectures for large capacity solutions in high throughput systems.

Remote File Systems

Remote filesystems typically share a filesystem. Contrast this to SAN devices which share the physical storage medium. Several varieties of remote filesystems are typically encountered:

(16)

NAS systems are shared filesystems attached to a client via a standard network connection, usually TCP/IP.

 Network Filesystems (NFS)

NFS systems are hierarchically organized filesystems most often found in Unix environments. There are many standard and proprietary implementations, usually available to the system as a remote network mount in the filesystem table. The most notable concept of which is the stateless client.

 Windows Network Shares

Microsoft Windows provides a file sharing protocol (SMB, and beginning with Windows Vista, SMB2) that allows users to easily share folders and files between workstations.

While remote file systems are relatively much cheaper than their SAN cousins, in general, they do not provide an adequate level of IO capability for database integrity as they do not fully support the filesystem semantics required. That is, there is no guarantee that a write request has succeeded in most cases and the ordering of IO operations is not respected to guarantee data protection for a database. In addition, there is often a significant network IO latency for these storage systems that can severely impact performance.

NFS systems also implement file locking mechanisms which are not compatible with performance sensitive distributed lock management.

With these considerations in mind, FairCom does not recommend nor support remote filesystems for c-treeACE database storage.

1.3 Positioning the c-treeACE in the System Architecture

When designing a system, the system architect determines the appropriate positioning of c-treeACE in the system architecture based upon system requirements. In some cases simplicity is the primary requirement. In other cases, scalability and fault tolerance are the primary

requirements. This section discusses two options for integrating c-treeACE into a system architecture: a single c-treeACE architectural model and the multiple c-treeACE architectural model.

(17)

1.3.1 Single c-treeACE Architecture

The single c-tree Server architectural model is the simpler of the two models. In this model, one c-tree Server serves all clients. The advantage of this model is its simplicity. Because this model involves only one c-tree Server, there is no application routing logic and no synchronization of data among multiple servers. The application simply connects to the server and accesses the data.

(18)

The tradeoff for the simplicity of this approach is that the client load and scalability of the database system is limited to the capacity of the machine on which the c-tree Server runs. This choice of system architecture limits the ability of the system to scale to meet increased capacity requirements over time. Also, the availability of the system is determined by the availability of the single c-tree Server process and the machine on which it runs. If a software or hardware

component failure renders the c-tree Server process or its data inaccessible, the availability of the system is directly affected.

Figure 2: Effect of Unavailable Server in Single c-tree Server Architecture

1.3.2 Multiple c-treeACE Architecture

Given the scalability and fault tolerance implications of a single c-tree Server model, many enterprise-level systems choose to implement a multiple c-treeACE architectural model. In this model, multiple c-treeACE servers serve clients, and the architecture frequently includes load balancing and data synchronization components.

Load balancing components route incoming requests to c-treeACE in such a way as to spread the load evenly among the servers. The use of load balancing enables efficient use of multiple systems, enhancing scalability of the system.

(19)

In a system with more than one c-treeACE server, each server manages its own data set. The system architect decides how to partition data sets among c-treeACE. For example, the design could specify that each server maintains a full copy of the data set, or that each server maintains a subset of the data set. Data synchronization components apply changes made to one data set to other data sets as required by the system.

(20)

In the event of a failure in one portion of the system, client access is automatically rerouted ensuring continuous operations.

(21)

2. System Requirements

This section summarizes the hardware and software required for running c-treeACE server. If you have special requirements that are not addressed here, such as embedded systems or multiple servers, please contact FairCom for assistance.

2.1 Platform Support

Supported platforms include:

Platform Version

Windows Windows Vista through Windows 8

Windows Compiler VC 6, Visual Studio 2003 through 2012

Borland Builder v5.x, v7.x (CodeGear C++ Builder 2006/2007)

HP Unix (HP-UX) HP-UX v11.x (HP-UX v10.x remains

supported as a legacy operating system)

IBM Unix (AIX) AIX Unix V Rel 5.3 (and earlier releases

back to AIX Unix V Rel 4.2)

Solaris Solaris 10 (includes support for 9 and

earlier)

QNX latest

SCO SCO OpenServer versions 5 and 6.

Linux Linux Kernel 2.4, 2.6 or later (and

legacy support for some earlier releases)

FreeBSD latest

Apple Mac OS X 10.8 (Mountain Lion), Mac

OS X 10.5 (Leopard), Mac OS X 10.4 (Tiger), Mac OS X 10.3 (Panther), Mac OS X 10.2, and earlier

2.2 Minimum Software Requirements for c-treeACE V10

 Windows XP/2003 or newer

(22)

 For the c-treeACE SQL JDBC Driver: To develop using JDBC, you will need JDK 1.6 or newer and the c-treeACE SQL Server.

 Stored procedures for the c-treeACE SQL Server:

• To develop a stored procedure, you will need JDK 1.6 or newer.

• To execute a stored procedure, you will need JRE 1.6 or newer.

2.2.1 Using Java 1.7 or Later on Windows

To use stored procedures, triggers and user-defined functions on Windows with Java 1.7 or later, you will need the MSVCR100.DLL, which is required by the Java Virtual Machine (jvm.dll). Because the Java installer does not provide the required DLL, the Visual C++ 2010 Redistributable package must be installed to get it.

Notice that 32-bit Java SDK installations require the 32-bit redistributable and 64-bit Java SDK installations require the 64-bit redistributable. To download:

32-bit redistributable - http://www.microsoft.com/en-us/download/details.aspx?id=5555 (http://www.microsoft.com/en-us/download/details.aspx?id=5555)

64-bit redistributable - http://www.microsoft.com/download/en/details.aspx?id=14632 (http://www.microsoft.com/download/en/details.aspx?id=14632)

2.3 Minimum Hardware Requirements for c-treeACE V10

c-treeACE SQL Server (SQL Version)

The minimum CPU and memory requirements for operating the SQL version of the c-treeACE SQL Server for Windows are:

 Pentium 1 GHz CPU  512 MB RAM

 500 MB Disk space + space for your data + index files (assuming default 120MB transaction LOG_SPACE setting in ctsrvr.cfg)

c-treeACE Server (ISAM Version)

The minimum CPU and memory requirements for operating the ISAM version of the c-treeACE Server for Windows are:

 Pentium 300 MHz CPU  300 MB RAM

 250 MB Disk space + space for your data + index files (assuming default 120MB transaction LOG_SPACE setting in ctsrvr.cfg)

Note: Additional memory will be needed for additional users beyond 16 concurrent users and larger data and index caches. 1+ GB of RAM is recommended for c-treeACE SQL Server.

(23)

2.4 Java Version

c-treeACE SQL V10 and V11 require the Java 1.6 JDK and JRE environment for Stored Procedures, Triggers, and User Defined Scalar Functions (UDFs), JDBC, and c-treeDB Java. Java is readily available from the Oracle Java downloads

(http://www.oracle.com/technetwork/java/javase/downloads/index.html) website. c-treeACE supports Java on any platform that the Java environment is currently available, including Windows, AIX, Oracle Sun, and Linux.

Note that Oracle has announced an end of life

(http://www.oracle.com/technetwork/java/javase/eol-135779.html) policy for Java 1.6 beginning February 2013. Check the FairCom http://www.faircom.com/ web site for the latest Java compatibility announcements and availability of the latest Java support.

2.5 .NET Framework Requirements

c-treeACE V10 requires at least Framework Version 3.5 SP1 complete (e.g., the complete version, not just the "client" version).

As of V10.3, all tools and assemblies have been updated to support the latest Microsoft .NET 4.0 Framework.

c-treeACE V11 will work with .NET Framework V4.X.

2.5.1 .NET Tools for VS2010 - Updated all projects to use .NET Framework

v4.0

All the .NET projects for VS2010 have been updated to use the v4.0 .NET framework. If you target the .NET v3.5 framework, it uses the VS2008 compiler 'tool chain' which can result in an internal compiler error in VS2010. The target framework was changed to v4.0 in the *_v10.sln files to eliminate that error.

See Also

 http://support.microsoft.com/kb/976656

 http://connect.microsoft.com/VisualStudio/feedback/details/644532/internal-compiler-error-c1 001-using-c-cli-on-vs2010

2.5.2 .NET - Removed STRONGSIGN from assemblies

To be more compliant with standard practice for C# programmers, STRONGSIGN has been removed from .NET assemblies. We no longer force the assembly to be signed with the FairCom key. This allows developers to sign with their own key, which they can keep secret.

(24)

2.6 ADO.NET Entity Framework V2 - V4 Support

The c-treeACE SQL ADO.NET Data Provider has support for Entity Framework V2 through V4 (for EF6, see Entity Framework 6 Support in the c-treeACE SQL ADO.NET Data Provider

http://docs.faircom.com/doc/ado_net/ manual). When Visual Studio 2008 or later is detected

during c-treeACE Professional installation, support is integrated by default. System Requirements

The minimum development system requirements for c-treeACE SQL ADO.NET Entity Framework support are:

1. Visual Studio 2008 Service Pack 1

2. Microsoft .NET Framework 3.5 Service Pack 1 Auto Incrementing Field Type Restriction

Entity Framework Models allow Int16, Int32 or Int64 field types to be specified as Auto

Incrementing in model design. Auto Incrementing fields are also known as Identity fields in some schemas.

c-treeACE SQL now allows one user Auto Incrementing field type. Note that c-treeACE already supported a serial segment field, currently used by default as the ROWID value. As there is a limitation of one SRLSEG field type per data file (table), this precluded the addition of a user-defined field. An IDENTITY attribute is now available for this purpose.

Other Known Limitations

The following are other known c-treeACE SQL limitations that can be encountered when using Entity Framework support. These are in various stages of development. Contact your nearest FairCom office for the latest information concerning specific support for these items.

 The SKIP operator is not currently supported. The SKIP operator is commonly used with the TOP operator for “paging” purposes.

 The EXCEPT operator is not currently supported.

 Parameters are not currently supported in functions and in the TOP operator.

 BIT (Boolean) columns can currently only be tested against 1 or 0 (that is, if ( bitColumn == 1 ). Entity Framework requires a test against true/false (for example, if ( bitColumn == true ) or more simply if ( bitColumn )

(25)

3. c-tree OLTP Solutions

This document provides guidance in selecting optimal hardware for a high-speed transaction processing environment utilizing the c-treeACE Server and c-treeACE SQL Server, FairCom's high performance database technology. Performance metrics from real-world implementations are also presented to demonstrate the transaction processing power available from FairCom c-tree database engine technology.

3.1 Background

The c-tree database engine has been designed from the ground up to provide the fastest possible database insert and retrieval operations. For the greatest performance, FairCom empowers advanced application developers an ability to precisely control their database operations. Some of the features incorporated into FairCom technology that allow this level of control include:

 A finely tuned re-entrant threaded database kernel  Tunable database and index caches

 Programmable control over the transaction processing engine  High speed index operations

 Flexible APIs

These features function together to make c-tree one of the most powerful and flexible database engines available for high volume online transaction processing (OLTP) systems such as those found in the financial and telecommunications sectors.

3.2 Hardware Component Sizing

Database performance is dictated by a number of factors including:  Architecture of the application

 Composition of the data

 Hardware underlying the system

Because there are multiple factors involved, there is no single answer to the question of what hardware will yield the fastest application performance. However, the usual best practices for optimizing data intensive applications mostly apply to tuning a c-tree Server hardware environment. The primary computer hardware subsystems to be cognizant of when selecting hardware for a high speed c-tree database installation are:

 Disk I/O subsystem  Network infrastructure  System memory  Number of CPU's

(26)

 CPU clock speed

Because the c-tree Server is a very efficient database engine, the baseline hardware

requirements are minimal - far lower than with most enterprise-level database technology. Yet as more resources are available, the c-tree Server can be configured to use those resources, making it very scalable.

The best mechanism for properly sizing a system is to perform a real-world test simulating peak numbers of concurrent users, record sizes and data flow. Then, using operating system tools and tools provided with c-tree (e.g., Snapshot and server administration utilities), one can observe where the performance bottlenecks are occurring. Determinations can then be made as to whether adjustments to the hardware configuration or c-tree Server configuration are warranted. FairCom has extensive experience in this regard, and our technicians may be able to engage with the application development team to help identify and overcome bottlenecks.

Absent the ability to perform such a real-world simulation, most transaction-laden applications tend to be I/O bound, usually within the disk subsystem. Therefore FairCom customers tend to focus their hardware budgets on first optimizing the disk subsystem. Items such as large memory caches on the I/O controller, fiber optic backplanes, high RPM drives, etc. tend to be wise investments. Additionally, storing the c-tree Server transaction logs on a different hard drive spindle than the data and index files can also provide a data throughput improvement. The network infrastructure is also performance sensitive and the network should be sized to support the peak data throughput requirements without developing any latency.

The c-tree Server 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)) + (4 bytes * Maximum number of connections (CONNECTIONS) * Maximum number of connections (CONNECTIONS)) + (16000 bytes * Number of actual connections) + (256 bytes per connection per file actually opened))

The c-tree Server is able to utilize very large data and index caches (tens to hundreds of gigabytes each). In addition, the cache subsystem is very granular so the system can be precisely tuned to optimize the data throughput. For example, one can elect to cache an entire file, cache a percentage of a file, exclude specific files from the cache, etc.

The c-tree Server is able to automatically scale across multi-CPU systems. Although the CPU requirements for the c-tree Server are a fraction of other database engines, multiple CPU's can provide performance benefits. Furthermore, many systems are architected such that the c-tree Server is run on the same machine as the core components of the application in order to minimize network traffic.

3.3 c-tree Customer Performance Metrics

FairCom's c-tree database technology is a popular choice for any marketplace that deals with a large volume of data and requires real-time data access. Two such markets are the Financial and

(27)

Telecommunication markets. In these markets, FairCom technology has been implemented in applications such as:

 High speed data repositories for stock markets the world over

 Back-end processing of market analysis data by investment companies  Enterprise level data switches for credit card companies

 High speed phone number lookup and phone usage information for telecom companies  Fraud detection analysis

Typical hardware configurations in these markets are mid-range enterprise boxes from

manufactures such as Sun Microsystems, IBM, Hewlett Packard and Stratus. Machines typically have from 4 to as many as 48 CPUs, many gigabytes of RAM, and state of the art disk array subsystems from manufacturers such as EMC and HP. The disk subsystems are typically NAS or SAN devices connected to the network via fiber channel and utilize the latest in disk array

stripping technology to provide the appropriate blend of redundancy and performance. FairCom metrics that we see in these markets across an entire system (which may include multiple c-tree Servers) are:

# of Files File Sizes Total Data Storage

400-30,000 files per application

100+ GB per single data file

4.3 TB-10 TB

Data Volume/Day Transaction/Day Transaction/Seco nd

20GB - 1,000 GB per day

25 million - 300 million per day

400 - 100,000

For example, one high-end financial application was tested on a Stratus 5700 machine, running Red Hat Linux with 4 Dual Core Xeon 2.8GHz processors, on an internal ATA disk drive with a single c-tree Server operating at a sustained rate of 20,000 transactions per second.

(28)

4. Best Practices

4.1 Selecting c-treeACE Features Used in the System

c-treeACE 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.

4.1.1 Data and Index Caching Options

Figure 5: c-tree Server Caching Options

The c-tree Server 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 using a least recently used (LRU) scheme. 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 c-tree 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.

(29)

Note: Even a record smaller than a single cache page may require two cache pages as the record position is generally not aligned on a cache page boundary.

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.

Note: Setting MPAGE_CACHE greater than zero does NOT ensure faster system operation. It is more likely to be slower than faster. It does cause more of a record to be in cache, but there is increased overhead managing each individual cache page. The cache pages for consecutive segments of a record (where a segment fills a cache page) are completely independent of each other. They are not stored in consecutive memory. And I/O is performed separately for each cache page. This configuration option should only be used for special circumstances with careful, realistic testing.

Caching of Index Nodes

Figure 6: Data Length to Page Size

To understand the c-tree Server’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.

(30)

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 the c-tree 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 c-treeACE 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 c-treeACE 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 http://docs.faircom.com/doc/ctserver/48623.htm

(31)

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 c-treeACE 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, c-treeACE 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.

(32)

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.

c-treeACE 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#-1#100000000

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, c-treeACE 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. c-tree clients and c-treeACE 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.

4.1.2 Transaction-Controlled Files

An application can use the c-tree Server’s transaction capabilities to guarantee atomicity and, optionally, recoverability of data. The c-tree Server 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 c-tree Plus 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

(33)

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

Figure 7: c-tree Server PreImage File Handling

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.

(34)

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 the c-tree Server’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 c-tree 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.

The c-tree Server allows updates to PREIMG files only within an active transaction. To start a transaction, call the c-tree Plus 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.

(35)

server’s transaction logs. An update on a PREIMG file that is attempted outside a transaction fails with c-tree 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 the c-tree Server 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.

Figure 8: c-tree Server TRANLOG File Handling

(36)

 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. The c-tree Server 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

The c-tree Server’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.

(37)

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.

The c-tree Server allows updates to TRNLOG files only within an active TRNLOG transaction. To start a TRNLOG transaction, call the c-tree Plus API function Begin() with a transaction mode of

TRNLOG. An update on a TRNLOG file that is attempted outside a TRNLOG transaction fails

with c-tree 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, the c-tree Server 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 the c-tree Server 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,

Copyright Notice. Information in this document is subject to change without notice.