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 from 0 to 15 for FLR/VLR files or 0 to 255 for EFF files.

Use integers for specific field numbers, 0 (zero for record-based offsets) or KEY to reference a value based on an external key.

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) or KEY to reference a value based on an external key.
offset	Starting position within the field.
len	Length - number of characters in the key field (integer). Note: The maximum key field length, including the field offset value, is 3839.
"attr"	Attribute characters (see Key Definition Attributes).
:	A colon is 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, PxPlus creates and pre-allocates disk space for the file. With a negative value, PxPlus 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 File Options):

BSZ=num

Block size. Numeric expression (1 - 63).

ERR=stmtref

Error transfer.

SEP=char$ |*

Default field separator character. Hex or ASCII string value.

OPT=char$

Single character parameter:

"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: Feature not supported on platforms that do not offer Large File Support (LFS).

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, PxPlus 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 PxPlus 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, see KEYED directive.

PxPlus 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 than the maximum of 96 key components) to the number of fields that comprise a key.

If a given filename already exists, PxPlus 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. See SYSTEM_JRNL directive.

The following file limitations exist for EFF files:

63Kb (64512 bytes) is the maximum block size.

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.

EFF files do not support the multi-segmented techniques available for VLR files.

Refer to the table for sample file size limitations. The block size determines the maximum file size. EFF files will only use 3-byte pointers (as of PxPlus v6.00). 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 PxPlus.

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

Directives

Format

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).

Note:
The maximum key field length, including the field offset value, is 3839.

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

Description

See Also

Directives

Format

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).

Note: The maximum key field length, including the field offset value, is 3839.

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

Description

See Also

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).

Note:
The maximum key field length, including the field offset value, is 3839.

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