Product Documentation

FairCom ISAM for C

Previous Topic

Next Topic

Hot Alter Table

Application downtime during schema changes almost disappears with FairCom’s new Hot Alter Table. This feature minimizes the impact to your customer when you roll out changes to your database schema.

FairCom's Hot Alter Table feature can dramatically increase efficiency and minimize downtime when rolling out changes to your application data schema. Known in the industry by various names such as "Dynamic Alter Table," "Real Time Alter," and "Online Alter," Hot Alter Table allows you, the developer, to provide "rolling upgrades" to your product deployed in the field. This means that, when your customer installs upgrades to your schema, they will not have to go through a lengthy file migration process, which previously could require many hours of downtime. Instead, they can use the new Hot Alter Table feature to upgrade records upon read or write.

The conventional Alter Table statement converts all records in the affected table at the time the statement is issued, working its way through the table processing one record at-a-time. It must create a new table, convert every record in the file and copy it into the new file, re-index, etc. This process can be time consuming if the table contains a large number of records. The table must remain locked, and therefore inaccessible to the application, during this process, resulting in a lengthy downtime when users cannot be connected to the database.

Hot Alter Table Performs Updates On-the-Fly

Hot Alter Table allows records to be automatically converted to a new format as they are read so you can effectively roll out schema changes on-the-fly. Your customers can upgrade their database without the need to make all of their users disconnect from the database during a time consuming change. Application and database updates that previously required hours to accomplish can now be completed in seconds.

By relaxing the requirement to alter a table's schema while the users are locked out of that table, application downtime is minimized. Now you can roll out application updates that require database changes without worrying about significant downtime.

Multiple Schemas

When using Hot Alter Table, the table is allowed to contain records with different schemas: the original schema, a new schema requested by an Alter Table, and potentially more schemas requested by additional Alter Table statements.

Each record contains one extra piece of information in the “header” of the record (the record’s metadata): a schema ID that indicates which schema applies to the record (S0=the original schema, S1=the first altered schema, S2=the next altered schema, etc.).

Read: Conversion “On-the-Fly”

If someone is reading a record that does not use the current schema, it is converted on-the-fly to make it appear current. The table's metadata contains the record converters necessary to convert records to each schema. The current schema version is always presented back to the reader, regardless of the version stored on disk.

Write: Uses the Current Schema

The write uses the current format when it writes a record to disk. Write operations remain efficient because there is very little special work to be done: records are always written in the current format.

Reduced Time of Exclusive Access

Although exclusive access to the file is required when Alter Table is requested (dynamic or otherwise), the Hot Alter Table feature will dramatically reduce the time spent in that state. The new functionality simply updates the record converter information in a resource. It does not have to update any records.

FLEXREC – Flexible Record Support

A new record format is now introduced that includes provisions for supporting Hot Alter Table. This is a permanent attribute of a file at create time. Files cannot undergo a Hot Alter Table until they have been converted to FLEXREC support.


Hot Alter Table cannot presently modify or add indexed values.

In This Chapter

Support for dynamically altering a table's schema on-the-fly

Adding Hot Alter Table support to existing files

Rebuilding data files with Hot Alter Table support

Previous Topic

Next Topic

Support for dynamically altering a table's schema on-the-fly

To use the Hot Alter Table feature, use one of the following procedures, based on the API in use:

c-treeDB API

The c-treeDB API is the recommended way to use Hot Alter Table.

To perform Hot Alter Table using the c-treeDB API:

  1. Create a file that uses the flexible record (FLEXREC) support by including CTCREATE_FLEXREC in the create mode. For example:

    NINT rc = ctdbCreateTable(hTable, "mytable", CTCREATE_FLEXREC);

  2. To perform a Hot Alter Table, follow one of these procedures:
    1. A call to the ctdbAlterTable() function with mode of CTDB_ALTER_NORMAL determines if the Alter Table can be performed using Hot Alter Table. If so, it does. If not, it uses regular Alter Table.
    2. If the mode is CTDB_ALTER_HOT, a Hot Alter Table will be performed if possible (which requires the file to use the FLEXREC feature and for the changes to the schema to be possible to accomplish using Hot Alter Table). When using this mode, if a Hot Alter Table cannot be performed, ctdbAlterTable() returns error CTDBRET_NOTSUPPORTED.


To perform Hot Alter Table using the ISAM API, follow these steps:

  1. Create a file that uses the flexible record (FLEXREC) support by including the ctFLEXREC bit in the splval field of the extended create block structure for the data file. For example:

    IFIL myifil = { ... };
    XCREBLK xcreblk[2];
    NINT rc;

    xcreblk[0].splval = ctFLEXREC;
    rc = CREIFILX8(&myifil, NULL, NULL, 0, NULL, NULL, xcreblk);

  2. Use the ctAlterSchema() function to begin updating records on the fly as they are rewritten to the database. Its prototype is as follows:

    extern ctCONV NINT ctDECL ctAlterSchema(FILNO datno,pRCDSC precdsc);

    • datno is the user file number of the data file, which must be open in exclusive mode.
    • precdsc is a pointer to a record descriptor, which indicates what changes are being made to the schema.

See also:

  • For using Hot Alter Table in SQL, see ALTER TABLE in the SQL Reference Guide.

Previous Topic

Next Topic

Adding Hot Alter Table support to existing files

CompactIFile() and the ctcmpcif utility support options to create compacted files with FLEXREC on or off. Note the flexible record schema (FLEXREC) is the internal flag that is required for Hot Alter Table support.

By default, compacting a FLEXREC file creates a FLEXREC file with all records updated to the most current schema.

When using the ctcmpcif utility, if the original data file does not use FLEXREC and you want the new compacted data file to use FLEXREC, specify the -flexrec option. If the original data file uses FLEXREC and you want the new compacted data file to not use FLEXREC, specify the -noflexrec option.

When using CompactIFile(), specify chgflexrecIFILoption to create a non-FLEXREC data file from a FLEXREC format data file, and vice-versa. Use the setIFILoptions() function to set the compact options in the tfilno field of the IFIL structure that you pass to CompactIFile(). For example:

pifil->tfilno = setIFILoptions(chgflexrecIFILoption);

Previous Topic

Next Topic

Rebuilding data files with Hot Alter Table support

Support has been implemented within the normal c-tree index rebuild support for rebuilding files that use the flexible record schema (FLEXREC) feature, which is an internal part of the new Hot Alter Table. The changes for index rebuild are internal and therefore no extra provisions need to be considered when performing an index rebuild.