Directives
CREATE TABLE Create Keyed File (EFF)
   
Format CREATE TABLE filename$,[,extkey_len][,key_def$][,max_recs][,rec_size][,fileopt]
 
Where:
filename$ Name of the Keyed file to create. String expression.
extkey_len Numeric expression. Length of the external key for all records in the file.
key_def$ String expression defining the key. The Keyed file can be single- or multi-keyed. A key definition is made up of one or more key field
definitions ranging 0 to 15 for FLR/VLR files or 0 to 255 for EFF files. Use integers for specific field numbers, or 0 (zero) for record-based offsets. The key definition formats are as follows:

Single key field:
[["keyname":]field:offset:len[:"attr"] ]

Composite key fields (using the + operator):
[["keyname":]field:offset:len[:"attr"] ]+[field:offset:len[:"attr"] ]

Multi-keyed alternate key fields are comma-delimited:
[["keyname":]field:offset:len[:"attr"] ], [["keyname":]field:offset:len[:"attr"] ]

Note: The outer set of square brackets in the above formats are part of the syntax; the inner brackets indicate optional syntax items (i.e., the brackets enclosing the optional [:"attr"] are not part of the syntax).

Where:

keyname Name of key assigned for use in KNO=name$ options.
field Integer, 1 to number of fields (0 = record-based offset).
offset  
len Length, number of characters in the key field (integer).
"attr" Attribute characters, see Key Definition Attributes
: Colon - the separator for elements in a key segment.
max_recs Maximum number of records the file is allowed. Optional numeric expression. The default is zero (no limit). (Use a comma with no value to set the default.) If a positive value is supplied, ProvideX creates and pre-allocates disk space for the file. With a negative value, ProvideX allocates sufficient disk space for the file, but will set the max_recs count back to zero (unlimited).
rec_size Maximum size of the data portion of the record (excluding the key). A negative value creates a variable-length record (VLR) data file with the maximum record length equal to the positive value of this field. A positive value creates a fixed-length record (FLR) formatted file. If you do not specify size, the default is VLR with a maximum record size of 256.
fileopt Supported file options (see also, File Options):
BSZ=num Block size. Numeric expression (1 - 63).
ERR=stmtref Error transfer.
OPT=char$ Single character parameter. See list below.
"C" Compressed. Adds simple compression to record data.
"X" Extended Record Size. Extends record sizes up to 2GB per record.
"Z" Set ZLib Compression for VLR and EFF Files.
"0" Create VLR/FLR files (default if 'KF'=0)
"1" Create EFF Files with 2GB limit.
"2" Create EFF Files without 2GB limit (supported platforms).

Note: OPT="2" generates Error #99:Featurenotsupported on platforms that do not offer Large File Support (LFS).

SEP=char$ |* Default field separator character. Hex or ASCII string value.
Description Use the CREATE TABLE directive to create a file with one or more keys. If the first field in the directive after the filename is a number, ProvideX creates an external file key (i.e., an index to the file). If the first field in the directive is a key definition enclosed in square brackets [ ], then ProvideX uses only internal key fields instead.
*Note* By default, the CREATE TABLE directive will create Enhanced File Format (EFF) files on platforms that support Large File System (LFS), 64-bit addressing; however, by setting OPT="0"in the syntax, CREATE TABLE can also be used to create variable-length record (VLR) data files or fixed-length record (FLR) formatted files. For more information on VLR/FLR, refer to the KEYED Directive.
  ProvideX considers the first key specified for an EFF file to be the primary key. Every record must have a unique primary key. You can have duplicate secondary keys from record to record. There is a maximum of 255 keys allowed on a file with a maximum of 255 data components making up these 255 keys. For VLR/FLR files, there is a maximum of 16 key fields allowed on a file with a maximum of 96 data components making up the 16 keys. There is no limit (other that the maximum of 96 key components) to the number of fields that comprise a key. The initial implementation of EFF (in Version 6) is limited to 96 keys and 96 segments.

If a given filename already exists, ProvideX returns Error #12: File does not exist (or already exists).

Keys have the following limits:

  • Maximum External key size is 127 bytes.
  • Maximum Segment size within any key is 127 bytes.
  • Total maximum multi-segmented key size is 240 bytes.

Enhanced File Format (EFF) Notes

EFF records are always variable length. In future, EFF files will support transactions. For more information, refer to the SYSTEM_JRNL Directive. The following file limitations exist for EFF files:

  1. 63Kb (64512 bytes) is the maximum block size.
  2. The maximum record size is 64000 bytes. When using extended records the record size is limited to 32000 bytes when created, although records can be as large as 2GB.
  3. EFF files do not support the multi-segmented techniques available for VLR files.
  4. Refer to chart below for sample file size limitations. The block size determines the maximum file size. As of Version 6, EFF files will only use 3-byte pointers. The ability to use 4-byte pointers is part of the design to allow larger file sizes and will be enabled in a future version of ProvideX.
Block size 3-Byte Pointers 4-Byte Pointers (future)
(KB) Max pages per Inventory segment Max addressable pages Size of file in MB Max pages per Inventory segment Max addressable pages Size of file in MB
4 1,022 2,056,264 8,032 818 1,645,816 6,429
8 2,046 8,388,607 65,536 1,637 9,998,796 78,116
12 3,070 8,388,607 98,304 2,456 25,061,024 293,684
16 4,094 8,388,607 131,072 3,275 46,832,500 731,758
20 5,118 8,388,607 163,840 4,094 75,313,224 1,470,961
24 6,142 8,388,607 196,608 4,914 110,525,688 2,590,446
28 7,166 8,388,607 229,376 5,733 152,429,004 4,167,981
32 8,190 8,388,607 262,144 6,552 201,041,568 6,282,549
36 9,214 8,388,607 294,912 7,371 256,363,380 9,012,775
40 10,238 8,388,607 327,680 8,190 318,394,440 12,437,283
44 11,262 8,388,607 360,448 9,010 387,177,720 16,636,543
48 12,286 8,388,607 393,216 9,829 462,631,372 21,685,846
52 13,310 8,388,607 425,984 10,648 544,794,272 27,665,334
56 14,334 8,388,607 458,752 11,467 633,666,420 34,653,632
60 15,358 8,388,607 491,520 12,286 729,247,816 42,729,364
63 16,126 8,388,607 516,096 12,901 805,383,628 49,549,969
See Also KEYED Create Single/Multi-Keyed File
DIRECT Create File with Keyed Access
SYSTEM_JRNL File System Journalization
ADD INDEX Add Key to Keyed File
DROP INDEX Drop Key from Keyed File
RENAME..INDEX Rename Keys in Keyed File
SORT Create File for Sorting
Accessing Data Files