Directives 

Format

KEYED filename$,[,extkey_len][,key_def$][,max_recs[,rec_size]][,fileopt]

Where:

Key Definition Attributes 

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

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. Note: Only one of the auto-increment options ("#", "+" or "I") can be used per file.
"+"	Auto-increment - Zero filled string. Note: Only one of the auto-increment options ("#", "+" or "I") can be used per file.
"-"	Signed binary integers key segments. Note: 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 '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. (The "B" segment attribute was added in PxPlus v6.30.)
"C"	Force uppercase for individual key segments to create case insensitive keys. Example: 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 PxPlus 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: 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. Note: Only one of the auto-increment options ("#", "+" or "I") can be used per file.
"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, nor with numeric keys.
"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, nor with a numeric key segment.
"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 will find this useful for sorting multilingual characters into a single unified sort sequence. Example: keyed "filename",[1:1:40:"T"],[2:1:40:"TCD"] Note: The conversion tables for accent translations are located in the PxPlus 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. PxPlus 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.)

Description

Use the KEYED 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. 

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

Note:
By default, the KEYED directive will create the type of file indicated by the value of the 'KF' parameter. Normally, this is 0; thus, a variable length record (VLR) data file or a fixed length record (FLR) formatted file will be created.

Setting 'KF' to 1 creates an EFF file with a 2GB limit. Setting the 'KF' system parameter to 2 (or by setting OPT="2") in the syntax, KEYED can create Enhanced File Format (EFF) files on platforms that support Large File System (LFS), 64-bit addressing.

For information on EFF, see CREATE TABLE directive.

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 single key. For EFF, there is a maximum of 255 keys allowed on a file with a maximum of 255 data components making up the 255 keys.

If a given filename already exists, PxPlus 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. PxPlus writes records to the file with field type and length headers. This feature can provide better results in overall file performance. 

Note:
WindX in PxPlus supports the use of this directive via the [WDX] or [LCL] tags; e.g. KEYED "[WDX]somefile.ext". Non-PxPlus versions require you to encapsulate the command in an EXECUTE directive with a [WDX] tag (i.e. EXECUTE "[WDX]..."). See [WDX] Direct Action to Client Machine or [LCL] Access to Users Local Machine.

See DIRECT Create File with Keyed Access and Multi-Keyed Files.

If you see the message Corrected Missing Key when PxPlus 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 *UFAC or try to recover it using *UFAR (PxPlus utilities).

Note:
As of PxPlus 2016, the *UFAC file check utility no longer supports split files, multi-segmented files and files greater than 2GB, as rebuilding the entire file is a more efficient process.

Key Block Size and Performance 

PxPlus stores a maximum of 16 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, PxPlus 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 4 Kb 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.

Note:
For VLR and EFF files, the BSZ= must be large enough to accommodate the record size; e.g. 4Kb block size for a 4000-byte record. As of Version 4.20, you no longer have to use BSZ= in a Keyed file definition if defining record sizes over 4000 bytes. PxPlus now calculates the minimum block size necessary.

Variable Length Records

When using variable length records or EFF files, PxPlus 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. See 'MB'= system parameter.

Extended Length Records

When a file is created with the Extended Record Size option enabled ("X"), the system will allow records of any size up to 2GB to be written to the file. Physically within the file, the logical record and any associated keys will be broken down into multiple segments with each segment being up to the record size defined for the file.

Example:

If you create an extended record size file with a 2000-byte record size, storing a 9000-byte record would result in 5 physical segments being written to the file. These segments will be reconstructed into the full record when read, and all segments will be modified (or deleted) when the record is changed (or deleted).

Numeric Key Segments 

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

Update Plus™

This PxPlus feature allows for partial updates to records within keyed files. When a file is created with the UpdatePlus™ 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.

Example:

     erase "testfile",err=*next
     keyed "testfile",[1:1:6],0,0,opt="U" ! Create with UpdatePlus enabled
!
! This IOLIST defines the record as having five fields which
! includes a new field CST_EMAIL$ that may not have existed before
!
NEW_IOL: \
     iolist CST_ID$,CST_NAME$,CST_ADDR$,CST_AMT,CST_EMAIL$
!
     open (1)"testfile"
     CST_ID$="123456"
     CST_NAME$="ABC Corp"
     CST_ADDR$="123 Main Street"
     CST_AMT=1000.00
     CST_EMAIL$="john.doe@abc_corp.com"
     write (1)iol=NEW_IOL ! Write 5 fields
     close (1)
!
! This following represents the original IOLIST in older programs
! that only defines four fields -- no CST_EMAIL$ is defined
!
OLD_IOL: \
     iolist CST_ID$,CST_NAME$,CST_ADDR$,CST_AMT ! ** Four fields
!
     begin ! Clear everything
     open (1)"testfile"
     read (1,key="123456")iol=OLD_IOL ! Read four fields
     CST_AMT=CST_AMT+500
     write (1)iol=OLD_IOL ! Write four fields
     close (1)
!
! Now if we re-read back the 'updated' record requesting all five
! fields using the new iolist, we see that the fifth field (CST_EMAIL$)
! has been preserved
!
     begin
     open (1)"testfile"
     read (1,key="123456")iol=NEW_IOL ! Read five field
     print "Cst_id :",CST_ID$
     print "Cst_amt :",CST_AMT
     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 and FLR files as of build 9180 (no EFF support).

(The UpdatePlus™ capability was added in PxPlus v7.65.)

Note:
Data Dictionary Maintenance provides a check box option to enable UpdatePlus™ logic for a specified file. See Data Dictionary Maintenance.

(The Enable UpdatePlus™ logic option in Data Dictionary Maintenance was added in PxPlus 2017 Update 0002.)

Enhanced File Format (EFF)

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:

Multi-Segmented VLR

Controls Segment Size in Multi-Segmented Files

The 'MB' system parameter must be set to a value to activate the VLR multi-segmented file feature. When this option is enabled, large data sets can be split into multiple physical files or segments. This allows you to avoid potential issues with file systems or drives that limit the size of a physical file. When the file grows and hits the segment size set by the MB parameter, a new physical file will be created with a 3-digit sequence number appended to the file name (e.g. INVOICES, INVOICES.001, INVOICES.002).

PxPlus calculates the approximate size of each segment in megabytes with the exact value based on the block size of a file using the following algorithm:

Segment size in bytes = 512 + ((bksz - 6) * bksz)

The value bksz above represents the block size of the file -- normally between 1 and 8 kilobytes. You can use the BSZ=option when you create the file, to override the default block size for a file.

The following table lists the maximum number of segments allowed for a file based on valid block sizes (1 to 31 kilobytes):

See Also

CREATE TABLE Create Keyed File (EFF)
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

Examples

Example 1:

keyed A$+"_"+B$,10,100,50,err=1090

Creates a Keyed file with this structure:

Example 2:

keyed "CSTFLE1",6,,140

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

Example 3:

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

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

Example 4:

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

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

Example 5:

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

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

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