FairCom Database Engine Partitioned Files

How Do Partition Files Compare to FILESETs?

A Partitioned File allows a set of files to appear to the application as a single file (called the "Partition Host") with its associated index files. The partition host and its member files are all physical files that exist in the filesystem.

The FairCom DB FILESET feature creates a partition host with connection-level dynamic members. The FILESET is in essence a “runtime” partition host, allowing the user to select at runtime which c-tree files to umbrella under the host file.

The key architectural difference between the two technologies is as follows:

A Partition Host of a traditional Partitioned File maintains a single partition member list at the system-file level. All connections access the same set of partition members. The member accessed is determined by the partition rule.
The Partition Host of a FILESET contains no members at the system level. Each connection that opens the file uses the PTADMIN() API function to associate members with that open instance of the partition host. A FILESET does not have a partition rule.

Other differences exist, some of which are transparent to the application and some of which need to be understood by the developer.

The next sections explain more about these technologies:

Partitioned Files
FILESETs
Comparison Chart

In This Section

Partitioned Files

FILESETs

Comparison Chart

Partitioned Files

The FairCom Server 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 each record within this range record-by-record).

A partitioned file is composed of a host data file and its associated indices 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 the application 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 composed of a standard c-tree data file and its associated indices.

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.

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

For more information, see the Partitioned Files chapter in the FairCom DB Programmer's Reference Guide.

PTADMIN

The Partition Administration function, PartitionAdmin() or PTADMIN, allows on-the-fly adjustment to the partitions associated with a given host file, such as:

Add, remove, or archive partition(s)
Activate archived member(s)
Return a member file status
Modify lower limit of the raw partition number
Modify limit on the number of partitions
Reuse the raw partition number of a purged member

FILESETs

In FairCom DB V11 and later, the FILESET syntax allows simple SQL queries to operate on one or many files with little to no loss in performance. Bridging the gap between SQL and the more-efficient direct record access, this extension to SQL gives you the performance you need for challenging statistical analysis and other complex queries.

In many situations, traditional SQL views are not feasible due to the sheer number of tables involved and the ad hoc characteristics of the queries:

Data files are sometimes created “on-the-fly” by the application.
Traditional SQL queries require static SQL dictionary management consisting of defined entities (i.e., all entities are required to be present in the SQL Dictionary).
Performance concerns arise when building SQL views over 1,000s of tables.

The Dynamic FILESET

FairCom DB V11 introduces a concept of a dynamic FILESET. It allows you to treat a number of files as a single source when making SQL queries.

The SQL grammar has been extended to allow specifying the dynamic list of partitions when running queries on dynamic partitioned files. FairCom DB accesses the necessary files and makes them appear as a single table to SQL, thereby eliminating the overhead of creating a SQL view over a large number of files. The FILESET is created dynamically—on-the-fly—so that SQL sees the results as a simple, static table.

FILESETs Help You Find the “Needle in a Haystack”

The FILESET concept can be used to simplify SQL queries. Rather than writing a complex query across multiple files—possibly thousands—FILESETs allow you to write a simple query as though you were searching only a single table. A new function allows you to define a list of dynamic partition members so you can set the partition table dynamic members when using FILESETs.

For more information, see the FILESET topic in the FairCom DB V11 Update Guide.

Supported FILESET Features

The initial FILESET release supports reading data from dynamic partition members through the partition host file. It does not support the following operations on a partition host that has dynamic members:

ISAM record add, delete, and update
Index rebuild
File compact
SQL alter table
Adding a new permanent or temporary index

Comparison Chart

The chart below highlights some of the most important differences between Partitioned Files and FILESETs:

	Partitioned File	FILESET
Partition Rule	Yes, the rule determines which member file a record belong is	No. Members are specified at runtime
Definition of Member Files	The Partition Rule determines members	The members are specified when a connection is made
Member File List	Maintained in the host and handled with a specific API	Specified at runtime when the connection is made
Full Read-Write Access	Yes	No. Read-only
Direct Access to Members	A partition member can be opened directly only if you specify read-only access, otherwise the open fails with error 733.	Dynamic members of the FILESET can be opened for write access because access through the host is read-only.
File Block	File block cannot be applied to a single partition member (you need to file bock the partition host to add/remove a partition from a partitioned file)	File block cannot be applied to a single member file while it is used in a FILESET
Adding Member Files	To add a partition member, add data that belongs to that partition member based on the partition rule	The member files included in a FILESET are determined when the FILESET is opened
Archive/Purge Members	Archiving a partition member makes it inactive; that member still exists on disk. Purging actually deletes that partition member file from disk.	Individual member files can be archived and purged (because the FILESET is read-only, archiving is not an issue)
Common Schema with Variations	All members must share the same schema—we do not support variations on a shared common schema	All members must share the same schema—we do not support variations on a shared common schema
Full-Text Search	Not presently supported.	Yes. On the individual member files, but not on the FILESET
Replication	The Partitioned File can be replicated	Replication is not an issue for FILESETs because they are read-only