FairCom DB API for C

Working with Multiple Files (FILESET)

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.

In This Section Dynamic partition member support