Documentation

SQL Reference Guide

Previous Topic

Next Topic

Storage Attributes

A quoted string that specifies specific FairCom DB SQL table attributes. FairCom DB SQL supports the following STORAGE_ATTRIBUTES parameters:

  • 'ENCR=AES32' - Turns on encryption for the table. Omit 'ENCR=AES32' from the STORAGE_ATTRIBUTES clause when you do not want to encrypt the table. Note: if encryption is not yet enabled for the database, this setting will cause table creation to fail with error(-17454): CT - Service not supported. To enable encryption for the database, see Advanced Data Encryption in the Server Administrators Guide.
    • AES16 (Rijndael)
    • AES24
    • AES32
    • DES8
    • DES16
    • DES24
    • BLF8 through BLF56 (Blowfish)
    • TWF16 (Twofish)
    • TWF24
    • TWF32
  • 'RECBYT_IDX' - Creates the table with a RECBYT index. This index is required for physical backward traversal of variable-length type files. The RECBYT index does impose a minimal amount of overhead, however, when inserting/updating/deleting records. This index is not required for usual operations, and as such, is off by default.
  • 'NORECBYT_IDX' - (Default) Creates the table without a RECBYT index.
  • 'ROWID_FLD' - (Default) The ROWID field is an auto-incrementing number which takes advantage of the c-tree serial segment index mode. This hidden field is a sequential number added to each record insert, and maintained with an associated index. This field is not required for proper SQL operation, however, is referenced directly by the ROWID related scalar functions. In the case of no ROWID field, the record RECBYT value is returned, which may not maintain uniqueness over time. As there is a limit of only one serial segment index per c-tree data file, this value is unavailable for other fields when a ROWID is present. Conversely, no ROWID is available when an existing c-tree data file with a serial segment field is imported into FairCom DB SQL. The IDENTITY field attribute should be used for auto-incrementing values as it is better performing with greater field type flexibility.
  • 'NOROWID_FLD' - Creates a table without the ROWID serial segment field and index.
  • 'PREIMG' - This option disables transaction logging for the table. This can be useful for temporary FairCom DB SQL tables that are not required to be recovered in the event of catastrophic failure, yet retain atomicity of transactions.
  • 'HOTALTER' - Creates the table with Hot Alter Table support. (Supported in V11.5 and later.)
  • 'NOHOTALTER' - (default) - Create the table with Hot Alter Table support disabled. (Supported in V11.5 and later.)
  • 'HUGE' - (Default) Denotes that a table should be created as a c-tree HUGE file (64-bit file offset addressing).
  • 'NOHUGE' - Denotes that a table should be created as a c-tree non-HUGE file (32-bit file offset addressing). These files have a limit of 2Gb or 4Gb depending on your OS platform.

    Note: See the SQL_OPTION NO_HUGEFILE FairCom Server configuration option to make NOHUGE the default.

Examples

Setting a single storage attribute only requires the attribute name to be included between the single quotes following the STORAGE_ATTRIBUTES clause. Double quotes cannot be used:

CREATE TABLE test1 ( id integer ) STORAGE_ATTRIBUTES 'HUGE';

To set multiple storage attributes, separate them with a semicolon. You may include space before or after the semicolon, but it is unnecessary. The last attribute may be followed by a semicolon or not:

CREATE TABLE test2 ( id integer ) STORAGE_ATTRIBUTES 'RECBYT_IDX;ROWID_FLD';

Storage attributes are not case sensitive.

CREATE TABLE test3 ( id integer ) STORAGE_ATTRIBUTES 'recbyt_idx;rowid_fld;hotalter;huge;';

The following attributes cannot be used together:

  • RECBYT_IDX and NORECBYT_IDX
  • ROWID_FLD and NOROWID_FLD
  • HOTALTER and NOHOTALTER
  • HUGE and NOHUGE

The following example throws error(-26014): conflicting or duplicate attributes found:

CREATE TABLE test4 ( id integer ) STORAGE_ATTRIBUTES 'huge;noHuge';

Encryption must be enabled in the database before you can use an encryption attribute. When encryption is not enabled, the following example throws error(-17454): CT - Service not supported:

CREATE TABLE test5 ( id integer ) STORAGE_ATTRIBUTES 'ENCR=AES32';

NOTE: The default storage attributes are best unless you need to turn on encryption or turn off transaction processing for a table. You have limited ability to change storage attributes after a table is created because most attributes define how the table is physically created. You may change transaction logging for a table (i.e. PREIMG) using the cttrnmod utility or call the stored procedure fc_set_file_tran_state. This is useful when you want to temporarily suspend transaction processing for a bulk load or to turn it on temporarily for replication. You can also create a new table with the desired storage attributes, copy the old table’s data into it, rename the old table, and rename the new table. When all is working as desired, you can drop the old table. In the SQL Reference Guide, see CREATE TABLE, ALTER TABLE, and DROP TABLE.

Deprecated Attributes

  • ‘CAMO’ - A lightweight option for obscuring data. This option gives a small amount of protection from casual inspection of the c-tree data and index files. CAMO or "Camouflage" is an older, legacy method of hiding data, which is not a standards-conforming encryption scheme, such as AES. It is not intended as a replacement for Advanced Encryption or other security systems.
  • 'ENCR=crypt' - The following crypt values for 'ENCR=crypt' are deprecated.
    • AES16
    • AES24
    • DES8
    • DES16
    • DES24
    • BLF8
    • BLF56
    • TWF16
    • TWF24
    • TWF32

TOCIndex