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. The maximum block size for a VLR file is 31KB and the maximum record size is 31000 bytes. Attempting to create a VLR file with a record size more than 31000 bytes results in an FLR file with the requested record size.
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. "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). "Z" Set ZLib Compression for VLR and EFF Files. Note: "Z" and "C"together will result in an Error #32. "U" Enable UpdatePlus™ logic for file. (Only supported on VLR & FLR files as on build 9180 - No EFF support) UpdatePlus™ is a +PxPlus Exclusive SEP=char$ |* Default field separator character. Hex or ASCII string value.

Optional key definition segment attribute characters "attr" can be used to refine key segment definitions;

For Example:

KEYED "MES_FACTURES",[1:1:40:"T"],[2:1:40:"TCD"].

The following table defines the various attribute definition characters:

Character	Definition
"#"	Auto-increment - Space filled string. (See note below)
"+"	Auto-increment - Zero filled string. (See note below)
"-"	Signed binary integers key segments. This option cannot be used in conjunction with the following segment options on the same segment: "#", "+", "B", "C", "D", "I", "L", "T", "U", "Z".string.
"A"	Ascending key (assumed).
"B[.n]"	Indicates that the segment contains a numeric value. Field will internally be converted to its binary equivalent and stored into the data file. Negative numbers will occur before position numbers in ordinary numeric value sequence. If '.n' is provided, the value is 'n' is assumed to be the number of digits to the right of the decimal point that are to be maintained. Before the internal value is created, the value will be TRUNCATED to the number of decimal places specified. See Numeric Key Segments below. The "B" segment attribute is a +PxPlus Exclusive
"C"	Force uppercase for individual key segments to create case-insensitive keys; e.g., KEYED "filename",[1:1:10:"C"],[2:1:40:"L"]+[1:1:10:"C"] Note: The conversion tables for upper/lowercase conversions are located in the ProvideX message file *MLFILE.EN. You can set these tables using the DEF UCS and DEF LCS directives and retrieve values using the UCS(*) and LCS(*) functions.
"D"	Descending order (default is ascending).
"I"	Binary auto-increment. Field (1, 2, or 4 bytes in length) will be auto-incremented as records are added to the file. Key must be record-based; i.e., field is 0 and offset is location in record. (See note below)
"K[:n]"	Null key suppression. n is the hex value of the character to suppress if all characters match (defaults to $00$ if not specified). If the entire key evaluates to null, the key will not be a part of this key tree. This cannot be used on a primary key or in conjunction with the "N" option.
"L"	Force lowercase for individual key segments to create case-insensitive keys. See the example and note in "C", above.
"N[:n]"	Null segment suppression. n is the hex value of the character to suppress if all characters match. If any segment within the key evaluates to null, the key will not be a part of this key tree. This cannot be used on a primary key or in conjunction with the "K" option.
"S"	Swapped (same as SWP( ) type 7, Intel x86 style swapping only).
"T"	Force automatic accent translation for individual key segments (i.e., to translate accented/extended characters to their non-accented counterparts). You'll find this useful for sorting multilingual characters into a single unified sort sequence;e.g. KEYED "filename",[1:1:40:"T"],[2:1:40:"TCD"] Note: The conversion tables for accent translations are located in the ProvideX message file *MLFILE.EN. You can set this table using the DEF CVS directive and retrieve values using the CVS(*) function.
"U"	If you declare an alternate key segment as unique, then no duplicate keys are allowed on writing a record. ProvideX generates Error #11: Record not found or Duplicate key on write. If you designate any segment in a key definition as unique, then the entire key will be unique. Note: The primary key must always be unique.
"Z"	Zero terminated strings. This will consider a null byte ($00$) in a string as the end of the data within the field, and will ignore any data between the null byte and the end of the key field. (Also known in C as a Z-String.)

ProvideX considers the first key specified for a Keyed file to be the primary key. Every record must have a unique primary key. You can have duplicate secondary keys from record to record.

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

Use the SEP= option to set the default separator for a given file. Use any character from $00$ to $FF$, or use SEP=*. When the asterisk is your value, the fields will not be delimited. ProvideX writes records to the file with field type and length headers. This feature can provide better results in overall file performance.

If you see the message Corrected Missing Key when ProvideX attempts to INSERT, UPDATE, or REMOVE a record in a Keyed file where a change to the primary or alternate key table is needed (e.g., for an alternate key to a customer name when the name changes or for removal of all keys when a record is deleted), the message indicates that the key entry to be changed was not found. This is not a fatal message, but usually means that the file has been corrupted in some way. You should check the file using *UFACor try to recover it using *UFAR (ProvideX utilities).

Key Block Size and Performance

ProvideX stores a maximum of 255 keys per block, with block sizes ranging from 1Kb to 32Kb. The maximum key block size for a variable record length file is 31Kb, while the maximum for a fixed record length file is 32Kb and an EFF file is 63Kb. The size of the key block also governs the size of the blocks used to store data records. As with key blocks, the maximum number of records per data block is limited to 255.

By default, ProvideX uses the size of the primary key to determine the optimum key block size to a maximum of 4Kb when creating a file. This calculation involves taking the (size of the primary key + 5 bytes) multiplied by a maximum of 255 entries, then rounding up to the nearest Kb to a maximum of 4Kb.

The block size can be overridden by using the BSZ= option on the KEYED directive. This can be used to optimize the usage of key and/or data blocks to improve performance. For example, using a BSZ=1 improves the performance on WANs (Wide Area Networks) by reducing the overall network traffic when accessing Keyed files.

The number of keys stored per block also affects the number of active levels on the key tree. Storing a smaller number of keys per block results in additional levels of blocks on the key tree, whereas a larger number of keys often results in fewer levels. Having fewer levels translates into improved performance as this reduces overall network traffic. For example, a key size of 50 would yield a maximum of 74 keys per block using a 4Kb block size while an 8Kb block size would accommodate 148 keys per block. In a 4K file, three levels on the key tree would be capable of managing 405,224 records (74*74*74) and 3,241,792 records (148*148*148) for an 8K block size. 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.

When using variable length records or EFF files, ProvideX will not pad the record to the record size with extra null characters (as is done with fixed length record files). The record data is stored using its exact length. This allows for much denser files, by avoiding the null padding on each record. Files are smaller thus when doing reads of large portions of the file, less disk accesses have to be performed because the records fit into a much smaller space and disk cache is better utilized.

In addition, with a variable length keyed file, the maximum record size is a logical maximum and not a physical one. A variable length file will simply allow you to write records from 0 to the maximum record size defined. Anything beyond the maximum record size causes an Error #1: Logical END-OF-RECORD reached. This design allows the maximum record size for a variable length record file to be increased at any time through the use of system utilities, *UFAM.

Only variable length record files support the file segmentation, which allows you to have files that total a logical size of up to about 248 GB. Refer to the 'MB'= System Parameter.

Key segments can be numeric in which case the system will maintain the keys in sorted numeric order starting with the lowest negative value through the highest positive value. The key itself will be generated by taking the numeric value indicated in the segment descriptor and right justifying it in the key. The number of decimal places will be adjusted to match the scale specified in the descriptor or zero if not specified. Invalid / Non-numeric values will result in a key value of zero.

When reading a file using a numeric key segment, a null key is considered the lowest key value thus will position before all other values (positive or negative).

Example:

Data Layout:	IOLIST CUSTNO$,NAME$,OWED
Key definition:	[1:1:6],[3:10:1:"B.2"]
Sample Data	Data order using Key number 0 Custno Name Owed "000011" "ABC Corporation" 1234.56 "000022" "XYZ Mortgages" 0.00 "000033" "DEF Ping Pong Tables" 250.11 "000044" "IOU World Bank" -1.15
	Data order using Key number 1 Custno Name Owed "000044" "IOU World Bank" -1.15 "000022" "XYZ Mortgages" 0.00 "000033" "DEF Ping Pong Tables" 250.11 "000011" "ABC Corporation" 1234.56

UpdatePlus™ is a +PxPlus Exclusive

This PxPlus exclusive feature allows for partial updates to records within keyed files. When a file is created with the Update-Plus feature enabled, record rewrites will only update the fields specified in the WRITE or UPDATE directive.

For instance if a record on the file currently has 10 fields and a WRITE directive is issued that specifies only 8 fields, the last two fields from the original record will be preserved. This feature is exceptionally useful when adding fields to data files where the record layout has been hard-coded within existing application programs. The new fields can be added to the end of the record and when the old programs update the records, the new fields will not be lost.

For Example:

0010 ERASE "testfile",ERR=*NEXT
0020 KEYED "testfile",[1:1:6],0,0,OPT="U" ! Create with UpdatePlus enabled
0030 !
0040 ! This IOLIST defines the record as having five fields which
0050 ! includes a new field CST_EMAIL$ that may not have existed before
0060 !
0070 NEW_IOL:IOLIST CST_ID$,CST_NAME$,CST_ADDR$,CST_AMT,CST_EMAIL$
0080 !
0090 OPEN (1)"testfile"
0100 LET CST_ID$="123456"
0110 LET CST_NAME$="ABC Corp"
0120 LET CST_ADDR$="123 Main Street"
0130 LET CST_AMT=1000.00
0140 LET CST_EMAIL$="john.doe@abc_corp.com"
0150 WRITE (1)IOL=NEW_IOL ! Write 5 fields
0160 CLOSE (1)
0170 !
0180 ! This following represents the original IOLIST in older programs
0190 ! that only defines four fields -- no CST_EMAIL$ is defined
0200 !
0210 OLD_IOL:IOLIST CST_ID$,CST_NAME$,CST_ADDR$,CST_AMT ! ** Four fields
0220 !
0230 BEGIN ! Clear everything
0240 OPEN (1)"testfile"
0250 READ (1,KEY="123456")IOL=OLD_IOL ! Read four fields
0260 LET CST_AMT=CST_AMT+500
0270 WRITE (1)IOL=OLD_IOL ! Write four fields
0280 CLOSE (1)
0290 !
0300 ! Now if we re-read back the 'updated' record requesting all five
0310 ! fields using the new iolist, we see that the fifth field (CST_EMAIL$)
0320 ! has been preserved
0330 !
0340 BEGIN
0350 OPEN (1)"testfile"
0360 READ (1,KEY="123456")IOL=NEW_IOL ! Read five field
0370 PRINT "Cst_id :",CST_ID$
0380 PRINT "Cst_amt :",CST_AMT
0390 PRINT "Cst_email:",CST_EMAIL$ ! Fifth field preserved

To enable UpdatePlus™ for a file it needs to be defined with an OPT="U" and is only supported on VLR & FLR files as on build 9180 - No EFF support.

Creates a Keyed file with this structure:

• Maximum Record size ..........: 50
• Maximum # records ............: 100
• Current # records ............: 0
• Size of key block ............: 3072 bytes
• External key size ............: 10

KEYED "CSTFLE1",6,,140

Creates a file with an external primary key (6 bytes).

KEYED "CSTFLE2",6,[1:1:10],,500

Creates a file with an external primary key (6 bytes) and a single internal alternate key.

KEYED "CSTFLE3",[1:1:6],[2:1:10],,500

Creates a multi-keyed file with 2 internal alternate keys as described below. Note alternate key 0 (field 1) is the primary key:

• Keyed file: C:\OTHER\MANUALS\PVX\CST\CSTFLE3
• Maximum Record size ..........: 500
• Maximum # records ............: (No limit)
• Current # records ............: 0
• Size of key block ............: 2048 bytes
• External key size ............: 0
• Alt. key 0 ...................: [1:1:6]
• Alt. key 1 ...................: [2:1:10]

KEYED "CSTFLE4",6,[2:1:10]+[1:1:6]

Creates a file with an external primary key, and a composite internal alternate key:  

• Keyed file: C:\OTHER\MANUALS\PVX\CST\CSTFLE4
• Maximum Record size ..........: 256 (Variable)
• Maximum # records ............: (No limit)
• Current # records ............: 0
• Size of key block ............: 2048 bytes
• Record Expansion factor ......: 10%
• External key size ............: 6
• Alt. key 1 ...................: [2:1:10]+[1:1:6]

To use a variable for the key definition:

X$="[1:1:6],[2:1:10]"
KEYED "CSTFLE",X$,,500

X$="6,[1:1:6],[2:1:10]"
KEYED "CSTFLE",X$,,500