Combining FairCom DB SQL with ISAM

This chapter details the considerations associated with accessing FairCom DB SQL created data from a c-tree ISAM application and using existing c-tree ISAM-created data from FairCom DB SQL.

Accessing FairCom DB SQL Files from ISAM Applications

To highlight the factors to consider when accessing FairCom DB SQL data from ISAM applications, consider the following example. Here we create a table using FairCom DB SQL and then examine the c-tree file specifications. This section also discusses the reverse case: accessing a c-tree ISAM application file from FairCom DB SQL.

Defining a Table Using FairCom DB SQL

The following FairCom DB SQL commands define a table consisting of three fields and an index defined on one of the fields:

CREATE TABLE example (
    dbl   float,
    int4  INTEGER,
    ch    CHAR(8));
 
CREATE INDEX example_chidx ON example (ch);
 
COMMIT WORK;

Examining Resulting File Specifications

Now that the table and index have been created, let’s look at the resulting field, table, and index definitions.

Field Definitions

First, let’s examine the field definitions, stored as a DODA resource in the data file. The SQL command specified three columns, but FairCom DB SQL created three additional fields for internal use:

(a) $DELFLD$ is a placeholder field at the beginning of the record for c-tree deleted record flag. If the first field in a record is not a binary field or the file is VARIABLE LENGTH, $DELFLD$ is not necessary. It can be modified, however, the first byte cannot be set to 0xFF or 0xFE for fixed-length data files, as these are reserved values.

In V9.5 and later, the $DELFLD$ in any new or altered table is either 5 or 9 (HUGE) bytes by default and the TRUNCATE clause causes a new table to be created. A configuration keyword, SQL_OPTION OLD_DELFLD_LEN, forces the use of the old size.

(b) $NULFLD$ is a bit mask indicating which fields contain NULL values to support IFNULL functionality. If this field is not present, then all fields of a record will always be considered NOT NULL, and any operation to set the null flag of a particular field will be ignored. Select queries based on NULL field expressions may not be resolved as expected.

(c) $ROWID$ is an automatic serial number to support ROWID functionality. If this field is not present, then the SQL engine will use the record’s RECBYT. Please note that RECBYT may not be a unique row identifier. As records are deleted and reused, the RECBYT of a deleted record will be used for an inserted record. Select queries based on ROWID may not be resolved as expected when the RECBYT is used.

Considerations for ISAM Specifications

The record starts with 16 bytes of internal field data. The fields accessible to applications via FairCom DB SQL begin at offset 16. FairCom DB SQL automatically maintains the contents of the internal fields using c-treeDB API functions. At the ISAM level, the internal fields can be viewed and modified. If updating records at the ISAM level, care should be taken to preserve the semantics of these fields, as described above.

The field values in a FairCom DB SQL table are set using c-treeDB API functions and can be easily retrieved in the format of the underlying data type using c-treeDB API functions. Some data types (e.g., Date, Time, etc.) will be encoded by c-treeDB API and will be difficult to interpret at the ISAM level.

Table and Index Definitions

This section examines the table and index definitions defined using FairCom DB ISAM resources: IFIL, IIDX, ISEG, and XCREblk.

For the sample file created using the FairCom DB SQL commands shown in this chapter, the resulting ISAM resources are as follows:

IFIL examplefil = { // Data file definition.
        ".\ctreeSQL.dbs\example",    // data file name (a)
        -1,                          // data file number
        36,                          // data record length
        0,                           // data extension size
        ctFIXED | ctTRNLOG,          // data file mode (b)
        3,                           // number of indices (c)
        0,                           // index extension size
        ctFIXED | ctTRNLOG,          // index file mode (b)
        exampleidx                   // index pointer
};
 
IIDX exampleidx[] = { // Index definitions.
    { // Index #1: Internal (RECBYT) index. e
                  5,                                  // key length  (d)
                  0,                                  // key type
                  1,                                  // duplicate flag
                  0,                                  // null key flag
                  0,                                  // empty character
                  1,                                  // number of segments
                  &recbytseg,                         // segment pointer
                  "$RECBYT$",                         // r-tree symbolic index
                  NULL                                // alternate index name (a)
             },
             { // Index #2: Internal (ROWID) index. e
                  4,                                  // key length
                  0,                                  // key type
                  0,                                  // duplicate flag
                  0,                                  // null key flag
                  0,                                  // empty character
                  1,                                  // number of segments
                  &rowidseg,                          // segment pointer
                  "$ROWID$",                          // r-tree symbolic index
                  NULL                                // alternate index name (a)
    },
              { // Index #3: User-created index on ch field. f
                  12,                                 // key length
                  0,                                  // key type
                  1,                                  // duplicate flag
                  0,                                  // null key flag
                  0,                                  // empty character
                  1,                                  // number of segments
                  &chidxseg,                          // segment pointer
                  "example_chidx",                    // r-tree symbolic index
                  ".\ctreeSQL.dbs\example_chidx.idx"  // alternate index name (a)
    }
        };
 
ISEG recbytseg =
    {0, 1, RECBYT};  // One-byte record position segment.
 
ISEG rowidseg =
    {8, 4, SRLSEG};  // Four-byte auto serial number segment.
 
ISEG chidxseg =
    {5, 8, SCHSEG};  // User-created segment on ch field. (f)
 
XCREblk xcre = { // Extended creation information.
ctTRANDEP | ctRSTRDEL // x8mode (g)
};

Notes:

(a) The SQL CREATE TABLE and CREATE INDEX commands cause FairCom DB SQL to create three files in the current database directory, which in this example is ctreeSQL.dbs. The files are:
example.dat - contains file definition resources and data records.
example.idx - contains key values for the two internal indices.
example_chidx.idx - contains key values for the user-created index on the ch field.
If additional indices are created, they are created as additional physical files.

(b) The files are created as transaction-processed files.

(c) Three indices are created (two internal indices plus the user-created index on the ch field).

(d) The first index is an internal index known as a RECBYT index used to support enhanced space management capabilities.

(e) The second index is an internal index known as a ROWID index used to support SQL ROWID functionality.

(f) The third index is the user-created index on the ch field. The key is defined over the full length of the field, uses the SCHSEG segment mode, and allows duplicates. To create an index that does not allow duplicates use the SQL command CREATE UNIQUE INDEX.

(g) The files are created using special extended file modes. ctTRANDEP specifies a transaction-dependent file and ctRSTRDEL specifies restorable delete capabilities. These properties enable rollback of file create and delete operations (at both the SQL and ISAM level).

 

Note: FairCom DB ISAM functions can be used to create data and index files, and if the file definitions adhere to the special properties required of FairCom DB SQL files as detailed here, the files can be easily imported to FairCom DB SQL using the ctsqlimp utility. One reason that a developer might choose this option is to create files with special properties (such as ctADD2END, to create chronological files) that FairCom DB SQL currently does not provide a way to specify at file create time.

 

Note: Fields of type LVARBINARY and LVARCHAR cannot be included in a SQL index due to their size.

If PRIMARY KEY is specified, FairCom DB SQL creates an additional index file with an automatically-generated name, such as sys_001_000001078.idx, and adjusts the IFIL resource in the data file to include an additional index definition.

The same is true if FOREIGN KEY is specified: FairCom DB SQL creates an additional index file with an automatically-generated name and adjusts the IFIL resource in the data file to include an additional index definition.

 

Note: FairCom DB ISAM has no knowledge of FairCom DB SQL referential integrity constraints. It’s possible to violate referential integrity constraints so use caution when updating records directly using ISAM functions.

Using Existing ISAM Data with FairCom DB SQL

In addition to creating new files under the FairCom DB SQL, it is possible to use existing c-tree Plus files under the FairCom DB SQL.

Table Definition Requirements

• Tables must contain IFIL and DODA structures. These can be added after the fact for existing files and are inserted automatically for files created by c-treeDB API and FairCom DB SQL.
• The ISAM application must use the c-tree Plus data types (as defined in the DODA) as in the c-tree - SQL data type mapping. For example, a CT_CHAR is used in the SQL to store a 1-byte integer.
  • There is an incompatibility between the use of CT_ARRAY in the current c-tree Plus ODBC Driver and the use of CT_ARRAY in c-treeDB API and c-FairCom DB SQL, including the FairCom DB SQL ODBC Driver.
• The table must have either TRNLOG or PREIMG in its file mode to use the ROLLBACK WORK and integrity constraint capabilities.
• To properly handle NULL, the table must contain the $NULFLD$ field, a hidden field generated by c-treeDB API at creation time. Tables created with the c-treeDB API Interface (used under FairCom DB SQL) have a hidden field, $NULFLD$, which is used to determine if each user-created field in the record buffer has a NULL value. FairCom DB SQL requires this capability to implement the "not null" constraint. c-treeDB API and FairCom DB SQL will access tables without the $NULFLD$ field, but the table’s fields will always return a non-NULL status.
• Avoid referencing a ROWID in a JOIN. To properly handle JOINS referencing ROWID, the table would need to contain the $ROWID$ field (a hidden field generated by the c-treeDB API at creation time). c-treeDB API and FairCom DB SQL should work with tables without the $ROWID$ field by using the record offset as the ROWID tuple identifier. SQL statements like “select * from table where ROWID > '4'” will fail because using record offset as ROWID will give us record offsets instead of sequential numbers.
  • When c-tree updates a variable-length record, the record offset for the record may change if the updated record size is larger than the original record. In this particular case, the ROWID for this ROW will not be unique, as required by the SQL standard.

Index Definition Requirements

• If an index contains a segment made by a “partial field” (i.e., does not use Schema segment modes or the segment starting offset and the segment length are different from the field starting offset and the field length) FairCom DB SQL cannot access this index, even though the index is still properly updated by c-tree.
  • There is one noticeable exception to this rule for segments on string fields that are exactly 1 byte less than the string size. In this scenario, when importing the index, the -z option identifies the situation and makes SQL aware and capable of using the index.
• If there is more than one logical index in one physical index file, the DROP INDEX and the DROP TABLE commands may not work properly.
• Alter table may not work correctly if tables contain index segments that do not start at field boundaries and/or span over several fields.
  • If a field is deleted from the table, and this field is part of an index segment that spans over several fields, c-treeDB API may not know how to adjust the index segment length after the field is deleted from the table. The resulting index definition may not be correct. Tables with unusual characteristics may also not work correctly and the altered table may inherit c-treeDB API characteristics that will prevent them from working in the original application.
• NULL key support disqualifies an index from being used for SQL.
• Conditional index is not used by SQL.
• Temporary indexes are not eligible for SQL use.
• An alternate collating sequence disqualifies an index from being used for SQL.

Adding DODAs to Existing Files

To use FairCom DB SQL with existing ISAM files that do not already have DODAs, add a DODA to each file. This is most easily done with a developer-created utility that opens each file and calls PutDODA() to insert the required resource into that file. The utility should:

1. Include a data object definition array (DODA), simply an array of DATOBJ structures, defined below.
2. Open each data file in ctEXCLUSIVE mode.
3. Call PutDODA() for each file to insert the DODA resource.
4. Close the files.

A DODA is a data object definition array. Each element of the array is comprised of a structure of type DATOBJ. Only three of the first four fields of the DATOBJ are required for c-tree Plus.

DATOBJ is defined as follows:

typedef struct {
  pTEXT     fsymb;  /* ptr to symbol name            */
  pTEXT     fadr;   /* adr of field in record buffer */
  UCOUNT    ftype;  /* type indicator                */
  UCOUNT    flen;   /* field length                  */
  ...
} DATOBJ;

• fsymb points to a unique symbolic name for the field and should not be NULL.
• fadr is not used by c-tree Plus (its value is ignored).
• ftype is one of the field types specified in the “Field Types” table.
• flen is set to the field’s length for fixed length fields, or the known maximum for varying length fields with a known maximum length, or zero for varying length fields without a known maximum length. If the field type has an intrinsic length, which is true for types CT_CHAR through CT_DFLOAT, a zero length is automatically replaced by the intrinsic length.

Given a data record with the structure:

struct {
  TEXT      zipcode[10]; /* Zip code           */
  LONG      ssn;         /* social security #  */
  TEXT      name[50];    /* name               */
} DATA_FORMAT;

The corresponding DODA would be defined as:

DATOBJ doda[] = {
    {"ZipCode",NULL,CT_FSTRING,10},
    {"SocialSecurity",NULL,CT_INT4},
    {"Name",NULL,CT_STRING,50}
};

Note: The two string fields show the difference between fixed-length and variable-length strings. zipcode, CT_FSTRING, takes up a fixed space in the record (10 bytes) and does not require a NULL to terminate the string. name, CT_STRING, takes up a variable amount of space up to a maximum of 50 bytes and is NULL terminated.

Available field types are described in “Record Schema - Field Types” in the c-tree Plus Programmer’s Reference Guide.

PutDODA() assigns the contents of a data object definition array (DODA) to data file datno, which must be opened in ctEXCLUSIVE mode. DODA points to the beginning of the DODA as described above. The numfld parameter indicates how many data fields are in the DODA, three in the example above. PutDoda() has the following syntax:

COUNT PUTDODA(COUNT datno,pDATOBJ doda,UCOUNT numfld)

See the PutDODA() function description in the c-tree Plus Function Reference Guide and “Record Schemas” in the c-tree Plus Programmer’s Reference Guide for additional details. Contact your nearest FairCom office for assistance if needed.

Mapping of c-tree to FairCom DB SQL Types

* In V11.5, maxlen is currently 8192.

Identify "Bad" Records with ctoption(badrec)

When bringing ISAM data into SQL, it's possible to encounter a situation where there is an error interpreting field content. The FairCom DB SQL engine has an aid to assist with identifying "bad" records for all applications.

Example:

SELECT * FROM <table> ctoption(badrec)

When using the ctoption(badrec) syntax, only failing records are returned and error information is logged in sqlserver.log.