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.

Dynamic partition member support

Note: The dynamic partition member support described below can be enabled only for FairCom Server.

A traditional partitioned host file maintains a single partition member list at the system-file level. All connections access the same set of partition members. Now FairCom Server supports creating a partition host with connection-level dynamic members. This type of partitioned file 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.

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

The operations listed above fail with error 990 (DPRT_NSUP_ERR, this operation is not supported for a dynamic partitioned file).

A partition host that allows dynamic members is created by setting the ctPARTAUTO bit in the XCREblk's x8mode field, setting the prtkey field to -1, and setting the ctDYNPARTMBR bit in the splval field:

xcreblk[0].x8mode |= ctPARTAUTO;
xcreblk[0].prtkey = -1;
xcreblk[0].splval = ctDYNPARTMBR;

These XCREblk values are not needed for the individual members, only for the host file.

As currently implemented, prtkey is ‑1, meaning that the dynamic partition host has no partition key. Both physical and index-order record reads are supported. All indexes are treated as non-partition indexes, so FRSKEY/NXTKEY operations on the indexes require reading the next key from each partition and returning the next key in the collation sequence.

FILESET SQL syntax for dynamic partitions

A new SQL syntax allows specifying the dynamic list of partitions when running queries on dynamic partitioned files. The SQL grammar has been extended to support a new way of specifying the table name for SQL queries as follows:

table_ref ::
           table_name [ AS ] [ alias [ ( column_alias [ , ... ] ) ] ]
    |  ( query_expression ) [ AS ] alias [ ( column_alias [ , ... ] ) ]
    |  [ ( ] joined_table [ ) ]
    |  FILESET(table_name [,"file_list"])[ AS ] [ alias [ ( column_alias [ , ... ] ) ] ]

If the same partitioned file is opened within two different FILESET lists, they are considered as separate files.

FairCom DB API Function for defining list of dynamic partition members

A new function was added to FairCom DB API to provide the ability to define a list of dynamic partition members. This allows you to set the partition table dynamic members when using the FILESET() syntax in SQL.

Declaration

CTDBRET ctdbSetTablePartitionDynMbrs(CTHANDLE Handle, pTEXT partdesc)

Description

ctdbSetTablePartitionDynMbrs sets the dynamic member list for a partitioned file. It must be called before calling ctdbOpenTable.

Parameters

• Handle [IN] - Table Handle.
• partdesc [IN] - The extension string describing the partition members. FairCom DB API does not keep a copy of the string which then must persist in memory until the end of the ctdbOpen call.

Return

Error code

SQL - Dynamic file list for dynamic partitions syntax change

The SQL grammar has been extended to support a new way of specifying the table name for SQL queries as follows. Originally, the grammar was to use the keyword cttbl, which was rather obscure and not self-explanatory as other SQL keywords are. This keyword name has been changed from cttbl to fileset so the syntax is as follows:

table_ref ::
           table_name [ AS ] [ alias [ ( column_alias [ , ... ] ) ] ]
    |  ( query_expression ) [ AS ] alias [ ( column_alias [ , ... ] ) ]
    |  [ ( ] joined_table [ ) ]
    |  fileset(table_name [,"file_list"])[ AS ] [ alias [ ( column_alias [ , ... ] ) ] ]

FILESET host creation utility

To take advantage of FILESET functionality, it is necessary to have a host file created with IFIL and DODA attributes matching the files to be dynamically linked together.

A new utility has been added to create a FILESET host file based on a template table.

CreateFilesetHost -t template [-n host name] [-u user] [-p pwd] [-s server]

• -u - user name (default: admin)
• -p - password (default: ADMIN)
• -s - Server name (default: FAIRCOMS)
• -t - Existing data file to clone schema information
• -n - host table name (default: host_TEMPLATENAME)

After this file is created, import into FairCom DB SQL with the ctsqlimp SQL Import utility.