Field design

Overview

The field structures in Model 204 are at the heart of providing applications which perform the functions that you require in the most efecient manner possible.

This page discusses exactly how the data structures should be defined.>

Defining records, fieldgroups and fields

When setting up a Model 204 file, you must first decide what fields the records will contain. A record in a personnel file, for instance, might contain a social security number field, a last name field, a spouse's name field, and so on.

Variable record formats

The record in Model 204 is a very loose concept. Physically, it is simply a collection of fields (data items). Any field in the Table A Dictionary may appear in any record in the file. It is quite possible that you create a file with many different logical record types with only a few (or even none) appearing in all of the records. Thus, each record is not required to have a value for every field; so that record lengths are variable, and it can be argued that the entire concept of a record 'format' does not apply to Model 204 files. If an employee's address is unknown or doesn't exist in a particular logical record, for example, the address field will be left out of the record altogether. The exception to this is Preallocated fields. If they are are defined for a file, space is reserved in each record for all such fields, even if they would never exist for that logical record.

Multiply occurring fields

Most types of fields can be multiply occurring, that is, have more than one value per record. For example, the CHILD field can appear no times, once, or several times in one record.

Field descriptions and attributes

When you describe a field in a Model 204 file with the DEFINE FIELD or REDEFINE FIELD commands, you specify a field description that consists of the field name followed by a list of field attributes. These attributes determine how a field may be accessed and how it is stored internally. This section describes the attributes supported for Model 204 fields.

Within a record, there is usually no restriction on the number of different fields that can have the same attribute (the exception being the key fields for Sorted and Hash Key files). For example, several fields within the same record can have ORD CHAR indices built for them.

Multiply occurring fields

For most field attributes there is almost no restriction on the number of times a particular field can occur within a record. This makes file / record design very flexible. If you have a record which has child details as repeating fields or groups, for example, any number may be supported.

Singly occurring fields

There are a few attributes which require a field to have no more than one occurrence per record:

• NUMERIC RANGE
• Sort key fields
• Hash key fields
and others which require a field to have no more than one occurrence per record or fieldgroup (depending on the context in which they are defined):

AT-MOST-ONE

EXACTLY-ONE

Indexing Fields

Model 204 files can become quite large, and so it is almost always preferable to directly access the record or records which have particular characteristics. As part of your design process, you should have identified which field(s) are likely to be used for direct retrieval of sets of records.

For example, if you have a personnel file, you are likely to have a unique identifier for each employee, and it is also likely that you will often want to process only the record or records with that ID.

Model 204 supports a few different ways to implement these direct searches.

ORDERED and NON-ORDERED attributes

Fields used in retrieval specifications can be defined as either ORDERED or NON-ORDERED, the default.

B-Tree index structure

Fields defined with the ORDERED attribute are stored with their values in the Ordered Index, a B-Tree based index structure that maintains field values in an ordered sequence to facilitate range retrievals. A B-Tree is a tree structure residing in secondary storage (disk) whose elements of access and navigation are disk page size tables, or nodes (See Understanding the Ordered Index). It provides a method of accessing record keys in order for a given file.

Ordered Index processing

Records are first identified in the Ordered Index, then retrieved from the stored data records. The caller can retrieve keys and associated access information with an exact match specification or with a range specification. Only one Ordered Index exists per Model 204 file, containing all field-name-equals-value pairs (in sorted sequence) and record access information (pointers to Tables B or D) for all ORDERED fields defined to the file. For more information see Understanding the Ordered Index.

Note

You can use ORDERED fields in value loop processing. See the discussion FOR EACH VALUE (FRV) and NON-FRV attributes.

Manipulating the Ordered Index

The Ordered Index is created the first time an ORDERED field is defined and physically resides in Table D. Some of its characteristics can be modified through the REDEFINE command. If multiple deletions and updates reduce the page density and access efficiency, you can rebuild the Ordered Index using the REORGANIZE OI command.

Benefits of the ORDERED attribute

Choosing the ORDERED attribute provides the following benefits:

• Alphabetic, alphanumeric, and numeric range retrievals are typically resolved from the index and do not require a direct search of data records. The following examples show such range retrievals. An alphabetic range retrieval is followed by a numeric range retrieval:

  NAME: FIND ALL RECORDS FOR WHICH NAME IS ALPHA LESS THAN SMITH END FIND SAL: FIND ALL RECORDS FOR WHICH SALARY IS GREATER THAN 30000 END FIND

• Range searches through the index are supported for fields containing character strings as well as numeric values, for INVISIBLE fields, and for multiply occurring fields.
• Record-by-record processing in sequential order of a field value is simplified and made very efficient, because the Ordered Index maintains key values in order and highly clustered. Such processing is invoked by the User Language FOR EACH RECORD (FR) and FOR EACH VALUE (FRV) statements and by the Host Language Interface IFFDV, IFGET, and IFGETV functions.

  IFGET supports ORDER SPECIFICATION only in a multicursor program. You can change your program to a multicursor program, if necessary.

• Retrieval of record keys whose values match a given character pattern is optimized. This capability is called pattern matching, and is described in the Model 204 User Language Manual.

Choosing an order type

An ORDERED field can have an index with either CHARACTER or NUMERIC ordering. This is called its order type. The field's order type and updating characteristics are declared with the DEFINE command. You can specify only one order type for a field. ORDERED NUMERIC fields contain numeric values stored as standardized floating-point numbers in one section of the index and nonnumeric values in EBCDIC order in another section.

ORDERED CHARACTER fields

ORDERED CHARACTER fields use the standard EBCDIC collating sequence. If a field is ORDERED CHARACTER, all values of the field are stored in the Ordered Index in EBCDIC collating sequence order.

ORDERED NUMERIC fields

A string field value is considered NUMERIC if it consists of digits, with one optional decimal point, and an optional plus or minus sign at the beginning of the string. The value of the number cannot be greater than 1075 or less than -1075.

If an ORDERED NUMERIC field is also FLOAT, exponential format is allowed. Values for BINARY, FLOAT (8), and FLOAT (16) fields are rounded to 15 significant digits. Values for FLOAT (4) fields are rounded to 6 significant digits.

An invalid value is stored as an unconverted string.

In ORDERED NUMERIC fields, leading zeros are ignored. For example, 001 and 1 are considered equivalent and are maintained in the ORDERED NUMERIC portion of the B-Tree as the same value. For fields that require a meaningful leading zero (such as zip code), use ORDERED CHARACTER.

Values of an ORDERED NUMERIC field that do not satisfy the definition of NUMERIC are stored in a separate section of the Ordered Index and processed in EBCDIC order. During FOR EACH VALUE and FOR EACH RECORD IN ORDER BY FIELD processing of an ORDERED NUMERIC field, these values are processed after the valid NUMERIC values unless you restrict the range of values.

Default order types

If a field is defined as ORDERED without specifying which type of ordering to create, a default order type is used. The default depends on whether the field is STRING, BINARY, or FLOAT:

For...	The default order type is...
STRING	ORDERED CHARACTER
BINARY or FLOAT	ORDERED NUMERIC

ORDERED field definition

The ORDERED attribute has options that allow you to determine the type of ordering the Ordered Index is to have, as well as some of the characteristics of the Ordered Index tree as it expands or is rebuilt. These options are described in the discussions of order type and Ordered Index tree structure that follow.

Defining ORDERED field attributes

The DEFINE command is used to specify a field's attributes. (See .) The format of the DEFINE command with the ORDERED attribute options is:

Syntax

DEFINE FIELD fieldname WITH ORDERED [CHARACTER | NUMERIC][SPLITPCT s] [IMMED i] [LRESERVE r] [NRESERVE n]

Note

The default designation for a field is NON-ORDERED. Ordered Index tree parameters do not apply for a NON-ORDERED field.

CHARACTER and NUMERIC order types are described in ORDERED and NON-ORDERED attributes.

SPLITPCT, IMMED, LRESERVE, and NRESERVE parameters are described in Understanding the Ordered Index.

Choosing between ORDERED or NUMERIC RANGE

The ORDERED attribute has several advantages over the NUMERIC RANGE attribute as follows:

• Ordered Index allows for multiple occurrences of the same numeric field in a record. The NUMERIC RANGE attribute issues an error message whenever an attempt is made to store more than one occurrence of the same NUMERIC RANGE field in the same record.
• Cost of updating an ORDERED field is lower than that of a NUMERIC RANGE field. The NUMERIC RANGE field requires multiple attributes to be maintained to describe the characteristic of a number. The cost of an update to the Ordered Index is always proportional to the depth of the tree.
• In most cases, especially with large numbers, the Ordered Index uses less space to maintain the index information.
• Ordered Index allows for more accurate range requests, with numbers representable by IBM double-word floating-point numbers.

  Although range retrievals are usually faster for ORDERED fields than for NUMERIC RANGE fields, the size of the range interval and the density of the distribution of values over that range are the principal determining factors. NUMERIC RANGE retrievals are faster for very large ranges with densely distributed data values.

Choosing between ORDERED or FRV

The ORDERED attribute is an alternative to the FRV attribute. Although FRV fields usually perform faster in value loop processing, they lose this advantage if the found values are required to be in order. FRV ordered retrievals require additional sorting. For retrievals in ascending or descending order, ORDERED fields are more efficient. For right-adjusted character strings in order, ORDERED fields require additional sorting.

Compatibility ORDERED with other field attributes

Use the following guidelines when specifying the ORDERED attribute:

• Field cannot be both ORDERED and FRV.
• Field cannot be both ORDERED NUMERIC and NUMERIC RANGE.
• Field can be both KEY and ORDERED (but serves little or no purpose).
• Field can be both INVISIBLE and ORDERED.
• Field can be both DEFERRABLE and ORDERED. If the field is DEFERRABLE, the index updates can be written out to a (see DEFERRABLE and NON-DEFERRABLE attributes for details).
• UNIQUE field must be ORDERED.
• Hash and sort key fields and record security fields can be ORDERED.
• and... fields which are defined as KEY may be converted to ORD with no code changes required (as direct retrievals will work with either type of ORD field)

For an architectural view of the ordered index, Table D

KEY and NON-KEY attributes

The KEY and NON-KEY attributes govern whether to create a hash index for a field or not. If KEY is specified, an index is created for the field. Retrieval commands will access the index for that field. If NON-KEY is specified, every record in the table is examined. The NON-KEY attribute is a default field description. This section provides information about space and performance for each of these attributes.

KEY attribute

When storing fields that have the KEY attribute, Model 204 makes special entries in the hash index. During retrieval, Model 204 goes directly to the appropriate index entry to find which records satisfy the selection criteria, without searching through other records in the file. This makes retrieval based on KEY fields extremely fast.

However, these index entries must be updated, when User Language statements or Host Language Interface functions add, change, or delete fields. Processing might take slightly longer for KEY fields than for NON-KEY fields. In addition, the space occupied by the index entries can be considerable, especially in large files and in fields that take on many values.

NON-KEY attribute

No index entries are maintained in Tables C and D for NON-KEY fields. When a retrieval is performed based on the value of NON-KEY fields, the data in Table B must be searched directly. The direct search can have a significantly adverse effect on performance, if a large number of records must be searched.

However, the cost of the direct search can be greatly reduced in some cases where both KEY and NON-KEY fields are specified in the retrieval conditions. Model 204 reduces the number of records to be searched directly by performing the indexed selection first. Records that are definitely eliminated, based on the KEY conditions, are not searched directly.

Choosing between KEY or NON-KEY

Keep the following guidelines in mind when deciding to designate a field as KEY or NON-KEY:

• If space is not an overriding consideration, designate fields that are used frequently for retrievals of equality as KEY.
• If a field is updated frequently and only occasionally used for retrieval, define it as NON-KEY.
• If a field is used frequently for retrievals and updated frequently, the choice is less obvious. If you reserve sufficient space in Tables C and D for adding inversion entries, the field initially can be defined as NON-KEY and changed later with the REDEFINE command (see ).

FOR EACH VALUE (FRV) and NON-FRV attributes

The FRV attribute is useful when creating fields used in value loop processing. If the FRV attribute is not specified when a field is defined, the NON-FRV attribute is assumed. If this attribute is set, the field is usually assigned the KEY attribute as well.

Table space for FRV fields

Every value of an FRV field is automatically encoded in Table A, whether or not the value is stored in Table B (that is, INVISIBLE). In addition to more Table A space, FRV fields require slightly more Table C and Table D space than ordinary key fields. Because of these additional space requirements, the FRV attribute is most frequently defined for fields in which the number of different values does not exceed approximately 200.

Using the FRV attribute

The FRV attribute behaves in a special way when combined with the BINARY attribute. This is discussed in BINARY attribute.

Fields cannot be defined with the FRV attribute in First-Normal Form (1NF) files. See See 1NF file model for more information about them.

FRV attribute, FRV User Language Statement

Value loops

User Language provides a special construction called a value loop that executes a set of statements once for each different value of an indicated field. The loop is initiated by the FOR EACH VALUE statement; the field must have the FRV or ORDERED attribute. The FOR EACH VALUE statement has Host Language equivalents in IFFDV and IFGETV. A value loop is illustrated in the following example:

Example

EACH.STATE: FOR EACH VALUE OF STATE GET.RECS: FIND ALL RECORDS FOR WHICH STATE = VALUE IN EACH.STATE END FIND TOT.RECS: COUNT RECORDS IN GET.RECS PRINT.CT: PRINT VALUE IN EACH.STATE - WITH COUNT IN TOT.RECS AT COLUMN 20 END FOR

On the first pass through the loop, a value of STATE, such as DELAWARE, is selected. The last statement prints DELAWARE and the number of records with the field-name-equals-value pair: STATE = DELAWARE. On the next pass, another value is chosen, such as IOWA, and so on until all values of the STATE field have been processed exactly once.

Using the IN ORDER option

The IN ORDER option of the FOR EACH VALUE statement specifies that the values of an FRV KEY field be presented in an ordered sequence-ascending, descending, numerical, right adjusted. This option improves performance by avoiding Online sorts of a large number of records, because only the values are sorted instead of a large found set of records.

Value loop processing for ORDERED fields

You can also use value loop processing on fields with the ORDERED attribute. When the FOR EACH VALUE statement with the IN ORDER option is used for NON-FRV, ORDERED fields, ORDERED retrievals can be significantly faster than FRV ordered retrievals. See ORDERED and NON-ORDERED attributes for a description of the ORDERED attribute.

Value loop processing can be made more flexible and efficient by using pattern matching, described in the Model 204 User Language Manual.

Representational Attributes

The following attributes are described individually in the next sections. They are principally concerned with how a field is physically stored. The attribute defaults are underlined.

• VISIBLE, INVISIBLE
• STRING
• BINARY
• BLOB, CLOB, MINLOBE
• FLOAT
• DATETIME
• UTF-8
• CODED, NON-CODED
• OCCURS
• LENGTH
• PAD
• FEW-VALUED, MANY-VALUED
• UPDATE IN PLACE, UPDATE AT END
• DEFAULT-VALUE
• STORE-DEFAULT, STORE-NULL
• AT-MOST-ONE, REPEATABLE and EXACTLY-ONE

VISIBLE and INVISIBLE attributes

In general, you want to be able to retrieve, and then display or print fields, or use the field values in a report. These are fields that you want defined as VISIBLE.

If, however, you have a field that is used only to retrieve records, and space is a consideration, you may want to define the field as INVISIBLE.

Note

If the INVISIBLE attribute is not specified when the field is defined, the VISIBLE attribute is assumed.

Using the VISIBLE attribute

Once an application retrieves a record with a User Language FIND statement or a Host Language IFFIND call, it generally processes one or more fields in User Language PRINT, NOTE, or SORT statements, arithmetic expressions, or Host Language IFGET calls. For example:

CHECK.SALARY: IF SALARY GT 1000*AGE THEN PRINT NAME END IF

A field referred to by the PRINT statement must have the VISIBLE attribute in its field description. This means that the field-name-equals-value pair is actually stored in the logical record in Table B.

Using the INVISIBLE attribute

A field that has the INVISIBLE attribute, on the other hand, takes up no storage space in Table B and cannot be printed, noted, or sorted. These fields can be added, deleted, or changed in the logical record.

Choosing the INVISIBLE attribute: In a library catalog file, for example, each record can have an AUTHOR field that contains the author's full name. In addition, there can be a LAST NAME field that contains only the author's last name. This creates a convenient index to the catalog and relieves the user of the burden of knowing the entire name when making retrieval requests. Because the author's last name already is part of the (VISIBLE) AUTHOR field, some space can be saved in Table B by selecting the INVISIBLE attribute for LAST NAME.

Reorganizing INVISIBLE fields

Because INVISIBLE fields are not stored in Table B, they require special care if the file is ever reorganized. When designing a file, be sure to take reorganization requirements for these fields into consideration. See for a description of techniques that can be used to recreate INVISIBLE fields.

Maintaining INVISIBLE fields

INVISIBLE fields must also have the KEY, NUMERIC RANGE, or ORDERED attribute, and they are normally used only to retrieve records.

The User Language FILE RECORDS statement and the Host Language Interface IFFILE function can be used to rapidly add an INVISIBLE KEY field name = value pair to an entire set of records. For example:

ALL.RECS: FIND ALL RECORDS FOR WHICH NAME = SMITH OR SMYTHE OR SMYTH END FIND FILE: FILE RECORDS IN ALL.RECS UNDER HOMONYM = SMYTH

Model 204 makes entries in Tables C and D to allow retrievals with the specification HOMONYM = SMYTH but does not change Table B. For further information about adding, changing, or deleting INVISIBLE fields, see the Model 204 User Language Manual.

Table B considerations

The values of VISIBLE fields can be stored in the logical record in Table B in one of four formats depending upon the selection of STRING/BINARY, CODED/NON-CODED, or FLOAT field attributes. The choice affects space requirements and, except for a slight increase in the time required for updates and some types of retrievals (see CODED and NON-CODED attributes), is transparent to the user.

The values of fields that have the INVISIBLE attribute are not stored in Table B, and physical storage attributes cannot be specified in their descriptions.

Using INVISIBLE fields in 1NF files

The First-Normal form relational model cannot be enforced for INVISIBLE fields. However, INVISIBLE fields are useful within Model 204 for list processing and to support SQL multicolumn keys (see the Model 204 SQL Server User's Guide for more information). Therefore, the INVISIBLE field attribute is allowed, but INVISIBLE fields are not mapped to SQL-accessible columns. The following rules and restrictions apply to INVISIBLE fields in 1NF files:

• Because the INVISIBLE and AT-MOST-ONE attribute are incompatible, INVISIBLE fields must be defined as REPEATABLE.
• You cannot REDEFINE a field as INVISIBLE within a 1NF file; the INVISIBLE and REPEATABLE attributes can be specified only with a DEFINE command. In the case of a record security key field, use the INITIALIZE command to specify the field as INVISIBLE and REPEATABLE.
• INVISIBLE fields in 1NF files must also be ORDERED or KEY (NUMERIC RANGE is not legal in a 1NF file and results in an error message).

Compatibility with other attributes

The INVISIBLE attribute cannot be specified for a field defined with the FLOAT or AT-MOST-ONE attributes.

STRING attribute

STRING is the default attribute for all fields. If your data is character or alphanumeric, such as names and addresses, specify a data type of STRING. STRING fields are also useful if you plan to store mixed data types in an image.

The length of a STRING field is limited to 255 bytes, because the first byte of a STRING data field is a count byte-that is, a byte that contains the length of the rest of the string.

BINARY attribute

Fields whose values are decimal integers can alternatively be compressed and stored as 4-byte binary numbers with the BINARY attribute. Specifying BINARY saves Table B space if the average value of the field is from 4-9 digits long.

Acceptable BINARY values

To be compressed successfully, the value must be1-9 digits long and can contain a minus sign. It cannot contain a plus sign, a decimal point, or leading zeros. If the value cannot be compressed, Model 204 refers to the CODED/NON-CODED field attribute (which the BINARY attribute usually overrides) to decide how to store the value.

BINARY and FRV fields

The BINARY attribute overrides the FRV attribute, except for values that cannot be compressed. If the field cannot be compressed, it will be stored in Table A in either the FEW-VALUED or MANY-VALUED pages depending on the field definition. For instance, AGE has been defined with the BINARY and FRV attributes and takes on the values:

23, 04, -34578, 935., 20, +8754, and TWO

The statement FOR EACH VALUE OF AGE retrieves only the values:

04, 935., +8754 and TWO

If BINARY is not specified when a field is defined, the STRING attribute is assumed.

BINARY fields and NUMERIC VALIDATION files

If a BINARY field is defined in a NUMERIC VALIDATION file model file, Model 204 provides numeric data type validation. If you try to store nonnumeric or other nonconforming (that is, noncompressible) data in a BINARY field in a NUMERIC VALIDATION file, Model 204 cancels the request with the following message:

M204.2123: VALUE SPECIFIED VIOLATES BINARY DATA TYPE VALIDATION IN RECORD recno, FIELD IGNORED: fieldname = value

BLOB, CLOB and MINLOBE attributes

The BLOB and CLOB attributes describe Large Object data that may be used to store long strings in the database, for example, long XML strings. This means that Model 204 User Language threads can exchange Large Object data with a partner process via the Universal Buffer and/or the MQ/204 Interface.

• Via the Universal Buffer, Large Object data can be sent and received using User Language READ IMAGE and WRITE IMAGE statements.
• Via the MQ/204 Interface, Large Object data can be sent and received by MQPUT and MQGET statements, stored and retrieved from the database with User Language STORE, ADD, and assignment statements using the BUFFER reserved area name.

Files created in V6R1.0 and later are not compatible with earlier versions of Model 204, because of Large Object support and the concomitant changes to the FPL page.

Introducing Large Object field types

The Large Object field types includes the following Character Large Object (CLOB) and Binary Large Object (BLOB) attributes.

• CLOBs are character strings.
• BLOBs are binary strings. No translation is performed when the object is sent or received.

Limitations of Large Object support

• Currently there is a limit of 2,147,483,647 bytes (or two gigabytes) of data per Large Object.
• For CLOBs the translation of ASCII to EBCDIC and back is handled by the design of your application.
• For BLOBs no translation is performed.
• The User Language INSERT statement is not supported.

Handling Large Object data

The BLOB and CLOB attributes are supported as follows:

• You can create, open, and recover a file with a BLOB or CLOB attribute. The Large Object field can be multiply occurring.
• The DELETE fieldname statement supports the Large Object data. This statement frees the Table B and Table E data.
• You can store and add a Large Object data to the file you have created from the Universal Buffer, BUFFER. Using an assignment statement you can transfer Large Object data from a field into the Universal Buffer.
• You can set the number of Table E pages during file creation with PARAMETER ESIZE= n.
• You can view the EHIGHPG parameter to determine the highest number of Table E pages in use.
• You can modify the size of Table E with an INCREASE or DECREASE command.
• The DELETE FIELD fieldname command supports the Large Object field type, so you can delete a Large Object field name. However, the space in Table B and Table E is not automatically available for reuse.
• $LOBLEN(lob-field-name) function is available for your use.
• Dictionary/204 and the Cross-Reference facility support Large Object field types.
• NULL Large Object data is supported.
• Transaction back out is supported for Large Object field types.
• Field-level security is supported.

BLOB and CLOB compatibility with other attributes

Combining BLOB or CLOB with other field attributes, except OCCURS, MINLOBE, or UTF-8, is meaningless. Use of any other attributes produces the following message:

M204.0411: CONFLICTING ATTRIBUTES: ([BLOB | CLOB]) attribute2

Using the MINLOBE (MLBE) attribute

You can use the MINLOBE attribute only with a BINARY LARGE OBJECT (BLOB) or a CHARACTER LARGE OBJECT (CLOB) field in a file with FILEORG X'100'. The MINLOBE attribute cannot be paired with the OCCURS attribute. You can redefine the MINLOBE attribute value.

Syntax

MINLOBE nnn

The default and smallest value for MINLOBE is 0, which means that all values of the field are stored in TABLEE. The maximum value of MINLOBE is 200.

If a field has the MINLOBE attribute greater than 0:

• And the value of the field is not longer than MINLOBE, then the value is stored in Table B or Table X, instead of Table E.
• The User Language CHANGE statement is not allowed and the following error is displayed:

  M204.2869 CHANGE NOT ALLOWED: MINLOBE > 0 FOR FIELD fieldname

  Any CHARACTER LARGE OBJECT or BINARY LARGE OBJECT field which does not use the CHANGE statement benefits from setting MINLOBE 200.

• The RESERVE clause is ignored on the User Language ADD statement.

Storing and updating LOBs

All large object data (LOBs) in a FILEORG=X'100' file are chained. There are four bytes per Table E page overhead for chained LOBs. The pages used by a chained LOB are not contiguous.

Handling LOBs in FILEORG=X'100' files also has the following effects:

• The RESERVE clause is ignored in a LOB field ADD statement processing, as well as the STORE RECORD statement processing of fieldname=value pairs.

  Consequently, the CHANGE statement does not fail because of insufficient reserved space. If the CHANGE statement requires that a LOB field be extended, it is.

• The value of the EHIGHPG parameter is always one less than the high water mark of the number of pages used to hold LOBs. (Unless none were ever added, in which case it is zero, not -1).
• The value of the EPGSUSED parameter is always the number of pages currently being used to hold LOBs.
• The COMPACTE command does not process FILEORG=X'100' files, just as it does not process a file created in V6R1 or earlier. Thus, issuing a COMPACTE command for a FILEORG X'100' file produces an error message.
• The TABLEE command effectively does a VIEW ESIZE EHIGHPG EPGSUSED for a FILEORG X'100' file. Consequently there are no TABLEE overhead pages in a FILEORG=x'100' file.

FLOAT attribute

Any field whose values are always, or almost always, numeric might be more efficiently stored in a floating-point representation than in character form. Using FLOAT can also reduce the time spent in data type conversions for fields used frequently in User Language or Host Language Interface computations.

Very large or very small numbers, or numbers with many significant digits, occupy considerably less space in floating-point form.

Floating-point fields are fully supported by User Language, the Host Language Interface, and the File Load utility. For more information about storing values in floating-point fields, such as conversion, rounding, and precision rules, see the Model 204 User Language Manual.

Note

If FLOAT is not specified when a field is defined, the STRING attribute is assumed.

Defining floating point fields

Field values are stored in floating-point by specifying the FLOAT attribute in the DEFINE FIELD command and supplying a LENGTH of 4, 8, or 16. The length supplied determines the precision of the floating-point field being defined. For example, to define a long precision field, specify:

Syntax

DEFINE FIELD fieldname WITH FLOAT LEN 8

You can define three types of floating point fields through the FLOAT and LENGTH attributes.:

Type of floating-point field	Occupies...	Holds a maximum of...	Model 204 maintains a maximum precision of...
Short precision	4 bytes	6 significant digits
Long precision	8 bytes	15 significant digits	15 significant digits
Extended precision	16 bytes	31 significant digits	15 significant digits

Acceptable values

The floating-point data type uses IBM hardware's floating-point representation.

A value of negative zero (X'800...0') cannot be stored in a preallocated FLOAT field.

FLOAT with NUMERIC VALIDATION files

If you define a FLOAT field in a NUMERIC VALIDATION file model file, Model 204 provides numeric data type validation. Trying to store non-conforming data in a FLOAT field in a NUMERIC VALIDATION file causes Model 204 to cancel the request with the following message:

M204.2124: VALUE SPECIFIED VIOLATES FLOAT DATA TYPE VALIDATION IN RECORD recno, FIELD IGNORED: fieldname = value

Compatibility with other attributes

STRING, BINARY, NUMERIC RANGE, and INVISIBLE cannot be specified for a FLOAT field.

Storing invalid numeric data: FLOAT or ORDERED NUMERIC

Prior to Version 5.1, Model 204 did not enforce storage rules for floating point numbers outside the valid range and could incorrectly store invalid floating point data as small, but nonzero floating point numbers.

The valid range of floating point numbers is:

1E-74 through 7.2370E+75

• 7.2371E75 is outside the valid range for floating point numbers. Prior to Version 5.1, it was stored as a true floating point number of the order of E-78 following the error message:

  M204.0563: ARITHMETIC OVERFLOW

• 1E-75 is also out of the valid range; it was stored as a true floating point number 1E-75.

As of Version 5.1:

• Storing the value 7.2371E75 in a FLOAT field does not result in error M204.0563: ARITHMETIC OVERFLOW. The value is treated as an invalid floating point number; it is stored as a data string '7.2371E75'.
• 1E-75 is treated as an invalid floating point number and stored as a data string '1E-75'.

If an invalid value is stored for a field with the ORDERED NUMERIC attributes, the value stored in the Ordered Index is the data string. That data string is stored on the Invalid Numeric Data portion of the Ordered Index. This changes the output order of these values when the ordering is done against the Ordered Index, for example, when using the User Language IN ORDER BY or FOR EACH VALUE clauses.

Identifying pre-Version 5.1 floating point values

The changed storage of invalid floating point values affects only new values you store in Version 5.1. Values already stored in the file are not corrected. You must find and correct these values, if necessary. The following procedure may help you, if the field is also defined as ORDERED NUMERIC or FRV.

Floating point ordered numeric values outside the valid range may be explicitly identified, removed, and replaced with valid values. This process can be done either before or after migrating to Version 5.1. Migrating itself, without rebuilding files, does not remove the bad values; nor does issuing a REORGANIZE ORDERED INDEX command against the file, regardless of the release version of the REORGANIZE command.

It is safer to rebuild under Version 5.1, because invalid values are inserted into the Invalid Numeric section of the Ordered Index. If you rebuild under a prior version, the only safeguard against improper reinsertion of invalid values is their explicit identification and removal prior to the rebuild process. You can confirm the presence of invalid values by running a procedure such as the following. Note the following fields.

• problem_field

  For each occurrence of problem_field substitute a Model 204 field name, which you suspect stores invalid floating point values. problem_field must be an ORDERED NUMERIC or FRV field, which is already defined to the file.

• TEMP_FIELD

  A Model 204 field name of an ORDERED CHARACTER field defined for use in only the following procedure.

DEFINE TEMP_FIELD (ORD CHAR) BEGIN STORE RECORD * MAKE SURE THAT THERE IS A TRUE FLOATING PT 0 * FOR THE SUSPECTED FIELD problem_field = 0 TEMP_FIELD = X END STORE END BEGIN FDV: FIND ALL VALUES OF problem_field FR: FOR EACH VALUE IN FDV %X = VALUE IN FR IF %X = 0 THEN %COUNT_OF_ZEROS = %COUNT_OF_ZEROS + 1 END IF END FOR IF %COUNT_OF_ZEROS GT 1 THEN PRINT ' FIELD CONTAINS VALUES OUT OF RANGE' END IF END BEGIN FD: FD TEMP_FIELD = X END FIND FR IN FD * REMOVE THE DUMMY RECORD YOU ADDED TO THE FILE DELETE RECORD END FOR END DELETE FIELD TEMP_FIELD

Note: The previous procedure can find floating point values out of range only if the field is FRV or ORDERED. Model 204 customer support recommends that you add the ORDERED attribute to use the previous procedure.

DATETIME (DT) attribute

The DATETIME attributes specifies the format of the stored data in Table B, not its function. The other date/time attributes are automatically updated fields generated at the time of record or field group creation or update. A non-automatic field can have a DATETIME format, but all the automatic date/time fields are stored in DATETIME format. The DATETIME attribute requires at least eight significant digits.

Once you define a DATETIME value for a field, you cannot redefine the DATETIME value.

UTF-8 attribute

The UTF-8 attribute indicates that only valid Unicode Transformation Format (UTF-8) data can be stored in the field. You can define a UTF-8 field as follows:

DEFINE FIELD UTFDATA WITH [UTF-8 | UTF8] DEFINE FIELD UTFBLOB WITH BLOB [UTF-8 | UTF8]

The UTF-8 attribute is a constraint that rejects UTF-8 encoding of Unicode characters outside the Basic Multilingual Plane (BMP).

UTF-8 is the only field constraint that is valid for CLOB or BLOB fields.

Usage notes on mapping

Characters outside the BMP are extremely unlikely in business applications.

An EBCDIC blank (X'40') is a valid UTF-8 character.

All numerics (X'F0' through X'F9') are UTF-8 characters for mapping characters outside the BMP. Lower case characters generally fall into the continuation byte range and upper case characters fall into the sequence start range. Because of this, a short, mixed case sequence, such as Tom or Bo, might slip by UFT-8 validation. However, most attempts to store EBCDIC tests into a UFT-8 field will fail, and therefore catch application errors.

Creating UTF-8 data

You can create UTF-8 data using the User Language function $ASCII, because 7-bit ASCII is a subset of UTF-8. For example:

ADD UTFFIELD=$ASCII('Hello')

CODED and NON-CODED attributes

The CODED and NON-CODED attributes let you choose between saving disk space (with CODED) or speeding up update and retrieval time (with NON-CODED). This section discusses how these attributes work.

Note

If the CODED attribute is not specified when a field is defined, the NON-CODED attribute is assumed.

NON-CODED attribute

If a field is designated NON-CODED without an OCCURS clause (see the following discussion), its value is stored in Table B as a simple character string that has an additional byte to indicate its length.

NON-CODED is the best choice for any field whose values have an average length of 1-3 bytes whether or not they are compressible numbers.

CODED attribute

Choose the CODED option to save Table B space where the values of the field are not suitable for BINARY compression and where their average length is four or more characters.

When a value that has the CODED attribute is defined, the character string is stored in Table A-the internal file dictionary-and a 4-byte value code pointing to that character string is stored in the logical record in Table B. Space is saved when there are several records that contain the same value. The string is stored only once in Table A, and only the 4-byte code is stored in each of the several records in Table B.

Choosing CODED vs. NON-CODED

The coding and decoding of these value codes takes time, which can slow down updates and retrievals. If speed is important and if disk space is relatively plentiful, do not choose the CODED attribute.

If disk space is somewhat tight, choose the CODED attribute for fields where the values are long and where there are a small number of values relative to the number of records in the file. A STATE field is a prime example.

A more precise rule governing the choice of the CODED attribute is difficult to arrive at since first it must be determined (based on the number and length of the field values, and on the number of records) if any disk space is actually saved by the CODED attribute. Then, any space saved must be weighed against the extra time taken by CODED retrievals. And the relative importance of disk space and time are likely to vary with the application as well as with the installation.

The formulas in , show more explicitly the effects of the CODED field attribute on Table A and B size.

Using the $CODE and $DECODE functions

Field value encoding is entirely transparent to the user. Data is returned exactly as it was entered. Codes are system-generated, internal codes that have no meaning to a user. If access to both user codes and their translations is needed, use the User Language $CODE and $DECODE functions in Model 204 User Language Manual.

Preallocated fields

Any VISIBLE field can be a preallocated field. However, a VISIBLE field that appears a fixed number of times in most of the records in a file is most suited for preallocation in Table B. Preallocated fields, if selected appropriately, require less Table B space than other fields and can be updated and retrieved slightly faster.

Preallocation means that space is reserved in each new record added to a file. As occurrences of preallocated fields are stored in a record, they are placed in the reserved space. Space is used even for occurrences that have not been stored, whereas non-preallocated fields use no space for missing occurrences. Preallocation also imposes restrictions on the number and length of values that can be stored in a record. A field is preallocated if its description includes an OCCURS clause.

, provides specific rules for the amount of space used by preallocated and non-preallocated fields. Use these rules to determine whether or not to preallocate a particular field.

Using an OCCURS clause

A field is preallocated if its description includes an OCCURS clause. The OCCURS clause indicates the number of occurrences of the field that are preallocated or reserved in each Table B record. From 1 to 255 occurrences can be specified. Any number of values, up to the specified limit, n, can be stored in the record.

Only as many occurrences as have been stored can be retrieved. Space allocated for unstored values is wasted.

If OCCURS is not included in a field description, the field still can occur more than once in a record. Space for the occurrences is not preallocated. There is no limit to the number of values that can be stored for a non-preallocated field.

Restrictions for preallocated fields

The OCCURS option can be specified only for fields that can be preallocated with a fixed length in Table B. This includes only fields that are defined as CODED, BINARY, or LENGTH m (see LENGTH and PAD clauses). If a field is defined as BINARY but does not contain an OCCURS clause, values that cannot be compressed to a binary representation still can be stored in the field. If a field is defined as both BINARY and OCCURS, only compressible values can be stored in it.

Model 204 does not allow incompressible values to be stored in such fields. To avoid such compression problems, include CODED with BINARY and OCCURS in the field description.

Displaying the set of preallocated fields

The DISPLAY RECORD command will show all of the preallocated fields in a file. This information is stored on a page in Table D where it is used to create the preallocated field space at the beginning of a record when it is initially stored.

Note

Because Model 204 cannot distinguish values padded with X'00' from zero-length values explicitly stored in the file, Model 204 does not allow zero-length values to be explicitly stored in preallocated fields.

LENGTH and PAD clauses

Using the LENGTH clause

Use the LENGTH clause to specify the length of FLOAT fields, as well as NON-CODED preallocated (OCCURS) string fields.

the LENGTH must be specified for NON-CODED preallocated string fields: the LENGTH must be included in the description of a NON-CODED preallocated string field to set the maximum length of the field. The length must be in the range 1 through 255. The specified length is preallocated for the number of occurrence specified by the OCCURS clause.

LENGTH with FLOAT fields: LENGTH is required with FLOAT to indicate the precision of the floating-point field being defined. The LENGTH options in this instance are 4, 8, or 16. (See FLOAT attribute.)

Specifying the LENGTH clause: The LENGTH clause limits the length of values that can be stored in the field. Model 204 does not store values that are longer than the specified length. The LENGTH clause does not impose any minimum length. Values that are shorter than the specified length are padded with the PAD character to the maximum length as they are stored in Table B and are returned to their original length as they are retrieved. See the description of padding in LENGTH and PAD clauses.

Field occurrences that have been preallocated but not stored contain zero-length values.

Using the PAD clause

You can include a PAD clause in a field description to select the character that is used to pad field values that are shorter than the length specified in the LENGTH clause (see LENGTH and PAD clauses) for the field. If no PAD character is specified in a field description, the default character, X'00', is used.

Padding characters are invisible to the user and are not included in a retrieved value. Make sure that the padding character does not appear as part of a field value. If it does, incorrect results can occur.

FEW-VALUED and MANY-VALUED attributes

If a field that has the CODED or FRV attribute is expected to take on fewer than about 50 different values, choose the FEW-VALUED attribute. Otherwise, define the field as MANY-VALUED.

This option determines whether the value string is stored in the FEW-VALUED or MANY-VALUED section of Table A, and is invalid for fields that are neither CODED nor FRV. Keep the number of values stored in the FEW-VALUED section small to ensure that this section is kept in core during retrieval and update operations. This practice reduces the number of disk accesses and increases retrieval speed.

Note

If the FEW-VALUED attribute is not specified when a field is defined, the MANY-VALUED attribute is assumed.

UPDATE IN PLACE and UPDATE AT END attributes

Model 204 provides the following field attributes that control the way that the value of a field occurrence is changed in Table B:

If this attribute is specified...	Then a change in the value of a field occurrence...
UPDATE IN PLACE	Does not change its position relative to other occurrences of the same field in Table B. This attribute is the default.
UPDATE AT END	Is accomplished by deleting the existing occurrence and adding a new one following the others.

Choosing an update option

Specify either of these options as part of a field description. The UPDATE AT END approach can be useful in certain data aging applications: for example, in applications in which the first occurrence of a field represents the oldest update to the file, and the last occurrence is the most recent update. UPDATE IN PLACE generally is more efficient.

The choice of update option determines the position of a field occurrence within a record only in relation to other occurrences of the same field. The ordering of different fields within a record is determined by a variety of factors, of which the update method is only one. The order of fields within a record normally is not under the control of the file manager.

User Language update statements other than CHANGE are not affected by the choice of an update option. The ADD and STORE statements always cause values to be added as the last occurrences in a record. The DELETE statement always removes the deleted value while maintaining the relative ordering of the remaining occurrences.

Compatibility with other attributes

The values of fields that have the INVISIBLE attribute are not stored in Table B, and UPDATE IN PLACE or UPDATE AT END cannot be specified in their descriptions.

DEFAULT-VALUE (DV) attribute

A field defined with a DEFAULT-VALUE (DV) attribute value must also be defined with the EXACTLY-ONE or the AT-MOST-ONE attribute. It sets the value of the field if the value is not explicitly added in the STORE RECORD, ADD FIELDGROUP, or INSERT FIELDGROUP initial data block.

The value of the STORE-DEFAULT setting determines whether the DEFAULT-VALUE is physically stored on the record or if it is just used as the default value when the field is missing.

The DEFAULT-VALUE attribute allows values up to 32 bytes. An attempt to use more results in:

M204.2851: DEFAULT-VALUE VALUE '1234567890123456789012345678901234567890' TOO BIG, - MUST BE <32 BYTES

STORE-DEFAULT (SD) and STORE-NULL (SN) attributes

You can redefine the STORE-DEFAULT and/or STORE-NULL attribute values.

The STORE-DEFAULT and STORE-NULL attributes offer these options:

• LITERAL (the default)
• NONE
• ALL

Consider the following example that shows how a DEFAULT-VALUE is handled when the field is defined with the different STORE-DEFAULT types of ALL, NONE, and LITERAL.

Here we are defining field NAME_SD_NONE with the STORE-DEFAULT option of NONE, field NAME_SD_LIT with the STORE-DEFAULT option of LITERAL and field NAME_SD_ALL with the STORE-DEFAULT option of ALL. The DEFAULT-VALUE for all of the fields is 'NONAME'.

In this example, the fields belong to a field group but the STORE-DEFAULT (and STORE-NULL) attribute can also be assigned to a field that is not part of a field group. The only requirement is that the field may not be defined as REPEATABLE, i.e., it must be either AT-MOST-ONE or EXACTLY-ONE.

DEFINE FIELDGROUP X DEFINE FIELD NAME_SD_NONE WITH FIELDGROUP X AND - ORD AT-MOST-ONE - STORE-DEFAULT NONE DEFAULT-VALUE 'NONAME' DEFINE FIELD NAME_SD_LIT WITH FIELDGROUP X AND - ORD AT-MOST-ONE - STORE-DEFAULT LITERAL DEFAULT-VALUE 'NONAME' DEFINE FIELD NAME_SD_ALL WITH FIELDGROUP X AND - ORD AT-MOST-ONE - STORE-DEFAULT ALL DEFAULT-VALUE 'NONAME'

There are basically two ways to store a field in a record. It can be stored:

- using a literal:

STORE RECORD FIELD.A = 'ABC' END STORE

- or by using a variable:

%X = 'ABC' STORE RECORD FIELD.A = %X END STORE

Additionally, a field may simply be missing (in the following example, the record has a FIELD.B but FIELD.A is missing):

STORE RECORD FIELD.B = 'XXX' END STORE

The STORE-DEFAULT options determines how the field will be physically stored if the DEFAULT-VALUE is stored in the field.

This example shows what is physically stored in the record when the DEFAULT-VALUE is stored for each of these fields. The DEFAULT-VALUE in this case is 'NONAME'. First, we will add the actual DEFAULT-VALUE of 'NONAME' by using a variable, then by using the literal 'NONAME' and finally we will add a field group occurrence where the field is missing.

ADD.X: SUBROUTINE IN FILEX FRN %CURREC * add first field group occurrence using the -variable %NAME: %NAME='NONAME' ADD FIELDGROUP X NAME_SD_NONE = %NAME NAME_SD_LIT = %NAME NAME_SD_ALL = %NAME END ADD * add second field group occurrence using the - literal 'NONAME': ADD FIELDGROUP X NAME_SD_NONE = 'NONAME' NAME_SD_LIT = 'NONAME' NAME_SD_ALL = 'NONAME' END ADD * add third occurrence of the field group with all - values missing: ADD FIELDGROUP X * none of these fields added to this occurrence END ADD END FOR

Subsequent PAI of the field group shows what is physically stored on the record.

The field defined with...	is...
STORE-DEFAULT NONE	Never stored on the record.
STORE-DEFAULT ALL	Always stored on the record.
STORE-DEFAULT LITERAL	Not stored in the first occurrence, because a %variable was used. It was stored in the second occurrence, because the field was added using a literal string.

\X = 1 NAME_SD_ALL = NONAME /X = 1 \X = 2 NAME_SD_LIT = NONAME NAME_SD_ALL = NONAME /X = 2 \X = 3 /X = 3

Note that when the record above is printed the DEFAULT-VALUE is printed regardless of whether the field is physically stored on the record or not.

FR PRINT '# NAME_SD_NONE: NAME_SD_LIT: NAME_SD_ALL:' FEO: FEO FIELDGROUP X PRINT OCCURRENCE IN FEO AND NAME_SD_NONE AND - NAME_SD_LIT AT 16 AND NAME_SD_ALL AT 30 END FOR END FOR NAME_SD_NONE: NAME_SD_LIT: NAME_SD_ALL: NONAME NONAME NONAME NONAME NONAME NONAME NONAME NONAME NONAME

Note: The same behavior occurs whether the field is AT-MOST-ONE or EXACTLY-ONE. However, a PAI of an EXACTLY-ONE field always shows a value. The actual physical storage on the record is the same.

The options on the STORE-NULL attribute react similarly. When the fields are defined as follows:

DEFINE FIELDGROUP X DEFINE FIELD NAME_SN_NONE WITH FIELDGROUP X AND - ORD AT-MOST-ONE - STORE-NULL NONE DEFINE FIELD NAME_SN_LIT WITH FIELDGROUP X AND - ORD AT-MOST-ONE - STORE-NULL LITERAL DEFINE FIELD NAME_SN_ALL WITH FIELDGROUP X AND - ORD AT-MOST-ONE - STORE-NULL ALL

And code used to store the field groups is:

IN FILEX FRN %CURREC * add first occurrence using the variable %NAME: %NAME= ADD FIELDGROUP X NAME_SN_NONE = %NAME NAME_SN_LIT = %NAME NAME_SN_ALL = %NAME END ADD * add second occurrence using the literal : ADD FIELDGROUP X NAME_SN_NONE = NAME_SN_LIT = NAME_SN_ALL = END ADD * add third occurrence with missing values: ADD FIELDGROUP X END ADD END FOR

The resulting PAI appears as follows:

\X = 1 NAME_SN_ALL = /X = 1 \X = 2 NAME_SN_LIT = NAME_SN_ALL = /X = 2 \X = 3 /X = 3 BEGIN FR PRINT '# NAME_SN_NONE: NAME_SN_LIT: NAME_SN_ALL:' FEO: FEO FIELDGROUP X PRINT OCCURRENCE IN FEO AND - NAME_SN_NONE AND NAME_SN_LIT AT 16 - AND NAME_SN_ALL AT 30 END FOR END FOR END

Results in the following: nulls are shown for each field.

# NAME_SN_NONE: NAME_SN_LIT: NAME_SN_ALL: 1 2 3

AT-MOST-ONE, REPEATABLE and EXACTLY-ONE attributes

You can use the AT-MOST-ONE, REPEATABLE, and EXACTLY-ONE attributes, which are mutually exclusive, to define an individual field or a field that is part of a field group definition.

Using the AT-MOST-ONE attribute

The AT-MOST-ONE attribute allows you to ensure that there is only one occurrence of a field in any record, without preallocating space for the field.

In addition, the AT-MOST-ONE attribute is required for fields in First-Normal Form (1NF) files. AT-MOST-ONE and REPEATABLE fields in 1NF files are discussed in this section.

Note

In non-1NF files, if the AT-MOST-ONE attribute is not specified when a field is defined, the REPEATABLE attribute is assumed.

If a field is defined with the AT-MOST-ONE attribute, Model 204 prevents multiple occurrences of that field in any given record. The AT-MOST-ONE attribute is useful if, for example, you know that there will be one variable-length field per record, or if you have a field that is often left blank but does not have more than one value per record (such as SPOUSE NAME).

Unlike fields with the OCCURS attribute, AT-MOST-ONE fields are not specifically preallocated. The AT-MOST-ONE attribute lets you store these kinds of records without wasting the space required by OCCURS.

Using the REPEATABLE attribute

If a field is not defined as AT-MOST-ONE, then it is REPEATABLE. Fields with the REPEATABLE attribute can accept multiple occurrences of the field in the record. Unlike the OCCURS attribute, REPEATABLE does not preallocate fields, nor does REPEATABLE let you specify the number of occurrences of the field.

Using the EXACTLY-ONE (EXONE) attribute

As the name suggests an EXACTLY-ONE attribute in a field definition means that the field always has exactly one occurrence in its record or field group context after the initial data block for the record or field group (STORE RECORD, ADD FIELDGROUP, or INSERT FIELDGROUP). If no explicit value is stored for the field, its value has either the DEFAULT-VALUE for the field or the null string (' '), if no DEFAULT-VALUE was specified for the field.

You cannot add, insert, or delete an EXACTLY-ONE field, although you can change an EXACTLY-ONE field.

The EXACTLY-ONE attribute is not allowed when both the DEFAULT-VALUE and STORE-NULL NONE attributes are specified.

EXACTLY-ONE fields with a field group

The EXACTLY-ONE attribute for a field within a field group means that the field can occur only once inside a single field group occurrence. However, since field groups can occur multiple times in a field group record, an EXACTLY-ONE field inside a field group can occur multiple times inside a field group record.

Defaults for AT-MOST-ONE, REPEATABLE and EXACTLY- ONE fields

REPEATABLE is the default except for 1NF files, where AT-MOST-ONE is required on all fields, except INVISIBLE. See VISIBLE and INVISIBLE attributes for informatIon.

The AT-MOST-ONE attribute is required on all fields in a 1NF file because multiply occurring fields violate relational First-Normal Form. The one exception to this rule is for INVISIBLE/REPEATABLE fields in 1NF files. See See, See Using the File Model Feature for more information.

The EXACTLY-ONE attribute is the default frequency of occurrence for a field defined within a field group.

Enforcing AT-MOST-ONE constraints

Model 204 ensures that AT-MOST-ONE field constraints are not violated for the following User Language requests and the Host Language and FLOD counterparts:

• ADD
• INSERT
• STORE RECORD

Before the indexes are updated and a new field is added or inserted into the Model 204 record, all existing fields in the record are checked to ensure that the new field does not create a second occurrence:

• If a new field violates the AT-MOST-ONE rules, and there is no ON FCC unit, the request is canceled with the following error message:

  M204.2119: AT-MOST-ONE CONSTRAINT VIOLATION IN RECORD recordnumber, FIELD IGNORED: fieldname=value

• Model 204 validates a field if you REDEFINE it to be AT-MOST-ONE.
• If a violation occurs and there is an ON FCC unit, Model 204 processes the ON unit.

In the Host Language Interface, the 202 return code is passed back to Model 204; in FLOD, the file is marked logically inconsistent. However, Model 204 does not cancel the request in either case.

AT-MOST-ONE and transaction back out files

Transaction back out must be active for files when defining AT-MOST-ONE fields. If you try to define an AT-MOST-ONE field in a non-TBO file, the DEFINE fails and you receive an error message.

Once the field is defined, you can turn Transaction Back Out off in non-1NF files. However, Model 204 customer support strongly recommends that you keep Transaction Back Out set in files containing fields that can cause constraint conflicts (currently AT-MOST-ONE and UNIQUE). If you turn Transaction Back Out off in a file with an AT-MOST-ONE or UNIQUE field, Model 204 sends you a warning message and a "Do You Really Want To?" prompt.

Using FLOD or FILELOAD with AT-MOST-ONE fields

When you use FLOD or FILELOAD to load or modify records that have AT-MOST-ONE fields, Model 204 ensures that the new or modified records do not violate AT-MOST-ONE constraints. Model 204 detects and reports all violations of AT-MOST-ONE in one pass of the input data. If Model 204 finds any fields that violate the constraints, the following occurs:

Error is reported and the new or duplicate field is rejected.

1. FLOD continues processing the input data.
2. At the end of the run, the file is marked "logically inconsistent;" that is, the FISTAT parameter is set to X'40'. See See Logically inconsistent files for more information.
3. The following error message is issued for each violation:

   M204.2119: AT-MOST-ONE CONSTRAINT VIOLATION IN RECORD recordnumber, FIELD IGNORED: fieldname=value

AT-MOST-ONE in First Normal Form (1NF) files

Using OCCURS 1 in 1NF files: For 1NF files, where AT-MOST-ONE is required, use OCCURS 1 to save processing time if you are storing a large number of records in which the field is always exactly the same length (such as a Social Security Number).

INVISIBLE fields in 1NF files: The First-Normal form relational model cannot be enforced for INVISIBLE fields. However, they are useful within Model 204 for list processing and to support SQL multicolumn keys. (See the Model 204 SQL Server User's Guide for more information.) Therefore, the INVISIBLE field attribute is allowed, but INVISIBLE fields are not mapped to SQL-accessible columns. The following rules and restrictions apply to INVISIBLE fields in 1NF files:

• Because the INVISIBLE and AT-MOST-ONE attributes are incompatible, INVISIBLE fields must be defined as REPEATABLE.
• You cannot REDEFINE a field as INVISIBLE within a 1NF file; you can specify the INVISIBLE and REPEATABLE attributes only with a DEFINE command. In the case of a record security key field, specify the field as INVISIBLE and REPEATABLE using the INITIALIZE command.
• INVISIBLE fields in 1NF files must also be ORDERED or KEY. NUMERIC RANGE is not legal in a 1NF file and results in an error message)

Defining AT-MOST-ONE and REPEATABLE fields in non-1NF files

You can DEFINE or REDEFINE a field to be AT-MOST-ONE in any non-1NF Model 204 file created after Release 9.0.

When redefining a field to AT-MOST-ONE that does not currently have the OCCCURS 1 attribute, Model 204 scans Table B to verify that no multiple occurrences of the field exist in any record. If any field violates the AT-MOST-ONE constraints, the REDEFINE fails and the following error message is displayed for each violation:

M204.2122: AT-MOST-ONE CONSTRAINT VIOLATION FOR FIELD fieldname IN RECORD recordnumber

Using the AT-MOST-ONE or the OCCURS 1 attribute

Although both the AT-MOST-ONE and OCCURS 1 attributes prevent more than one occurrence of a field in a record, they achieve their goal in different ways. For non-1NF files, use either the AT-MOST-ONE or the OCCURS 1 attribute. Using both causes redundant processing. Understanding the advantages of each may help you decide which attribute you want to use.

AT-MOST-ONE attribute:

• Does not preallocate the field. Therefore, if the field values between records are of very different lengths or if you do not know the length of the longest record, use AT-MOST-ONE.
• Allows you to correct violations before the record is stored with the ON FCC unit. If you do not use an ON FCC unit, then a constraint violation cancels the request. Therefore, if it is important to allow users to correct violations, or it is important that the request not be canceled, use AT-MOST-ONE.

OCCURS 1 attribute:

• Preallocates space for the field in every record. If you are storing a large number of records and the length of the value is generally the same, pre-allocating the field with OCCURS 1 can save you processing time.
• Cancels the request when a violation occurs. With OCCURS 1, you cannot use an ON FCC unit to correct violations.

Compatibility with other field attributes

AT-MOST-ONE is not compatible with the following field attributes:

• INVISIBLE
• OCCURS greater than 1
• REPEATABLE

  REPEATABLE is compatible with all field attributes except AT-MOST-ONE.

Coordinating AT-MOST-ONE and UNIQUE attributes

Both AT-MOST-ONE and UNIQUE are examples of field-level constraints. Although the function of the UNIQUE and AT-MOST-ONE attributes appear similar, their actual operations differ.:

This attribute	Affects...
UNIQUE	Value of the field
AT-MOST-ONE	Number of fields per record

For example, if a Social Security field within an EMPLOYEE file is both UNIQUE and AT-MOST-ONE:

This attribute	Ensures that...
UNIQUE	Social security number for every employee is different.
AT-MOST-ONE	Each employee has only one social security number.

Coordinating the use of the AT-MOST-ONE and the UNIQUE attributes increases the integrity and accuracy of your records.

Field and Content Constraints

You may set up constraints to prevent 'invalid' updates to the file. The updates may be invalid because to permit them would violate rules about the field (a second occurrence to an AT-MOST-ONE field, for example). Or you may set up 'content' constraints to prevent values outside of those permitted to be stored.

Field Constraints

ON FIELD CONSTRAINT CONFLICT (ON FCC) unit

(should probably be mentioned only as a link to UL page)

To help control processing with UNIQUE and AT-MOST-ONE fields, use the User Language ON FCC unit. ON FCC is invoked whenever a field-level constraint conflict, such as an attempt to add a second occurrence of an AT-MOST-ONE field.

An ON FCC unit is used with a number of $functions that can help you determine exactly where the field constraint conflict has occurred. The $UPDSTAT function is discussed below. All other $functions for ON FCC are discussed in the Model 204 User Language Manual.

$UPDSTAT function

The $UPDSTAT function is used in conjunction with the ON FIELD CONSTRAINT CONFLICT unit and field attributes which have field-level constraints of UNIQUE and AT-MOST-ONE. You must use $UPDSTAT in the ON FCC unit when writing procedures for files that have (or might have in the future) more than one type of field-level constraint defined.

If $UPDSTAT is invoked from an ON FCC unit following the detection of a field-level constraint conflict, it returns a numeric value denoting the type of conflict that has occurred:

Value	Meaning
0	No violation occurred
1	Uniqueness violation occurred
2	AT-MOST-ONE violation occurred

The $UPDSTAT function takes no arguments.

See information about the AT-MOST-ONE and REPEATABLE attributes.

UNIQUE and ORDERED fields

Because Model 204 uses the Ordered Index to process UNIQUE values, fields defined as UNIQUE must also be defined as ORDERED. For more information about the Ordered Index see ORDERED and NON-ORDERED attributes.

UNIQUE and NON-UNIQUE attributes

Defining a field with the UNIQUE attribute allows Model 204 to ensure that a given field name = value pair occurs only once in a file. A Social Security number field is one example that might benefit from having the UNIQUE attribute. The UNIQUE attribute specifies that there can be only one occurrence of a field in the file. Multiple occurrences are not allowed.

When storing a field specified as UNIQUE, Model 204 first queries the Ordered Index, looking for an identical field-name-equals-value pair. If it finds an identical value, Model 204 issues an error message and does not perform the update. This is true both within a record for multiply occurring fields and between records.

If there is not a uniqueness conflict, that is, if the field-name-equals-value pair being stored is unique, Model 204 completes the update.

Note

If the UNIQUE attribute is not specified when a field is defined, the NON-UNIQUE attribute is assumed.

Unique values in multiply occurring fields

In a multiply-occurring field, Model 204 ensures that each occurrence of the field is unique.

Deleting UNIQUE fields

Because UNIQUE values use the Ordered Index, do not use the DELETE ALL RECORDS statement for deleting records. Use DELETE RECORD instead.

Compatibility with other attributes

A field can only be specified as UNIQUE with the following restrictions:

• UNIQUE fields must be specified as ORDERED.
• UNIQUE fields cannot be specified as DEFERRABLE.
• UNIQUE fields cannot be defined in non-TBO (Transaction Back Out) files.

If you attempt to define a UNIQUE key under any of these circumstances, you receive an error message and the field is not defined.

Defining a LEVEL clause

To secure a field against unauthorized access, include a LEVEL clause in the field's description. See discusses field-level security and describes the types of field access: SELECT, READ, UPDATE, and ADD.

Field-level security has a negligible impact on both performance and storage usage.

Content Constraints

Setting a pattern for a field value: the LIKE attribute

Setting a field pattern lets you specify a pattern that a field value must conform to, otherwise it cannot be stored. The patterns you can specify are the Model 204 LIKE patterns. See the chapter on pattern matching in the Model 204 User Language Guide. For example:

IN FILE PITCHERS DEFINE FIELD G WITH LIKE '@@@####'

This specifies that field G must start with three alphabetic characters followed by four numeric characters. An attempt to store a field value that does not conform to the field's pattern results in request cancellation.

You can limit a field value to a list of values. For, example, if a field must have a value of 'M' or 'F' you could code:

IN FILE PITCHERS DEFINE FIELD SEX WITH LIKE 'M,F'

And if you want a BALL field to have a value 'SPLITTER', 'CURVE', 'KNUCKLE', or 'CHANGE UP' you could code:

IN FILE PITCHERS DEFINE FIELD BALL WITH LIKE - 'SPLITTER,CURVE,KNUCKLE,CHANGE UP'

The maximum pattern length is 47 characters. The maximum default value length is 31 characters.

As with the IS LIKE operator in User Language, the patterns are always applied to numbers after conversion to a string.

You cannot use the LIKE attribute to define an automatic field.

Range constraints

You can set range constraints on fields using the range attributes. Each set of range attributes is comprised of three or four attributes that you can use to establish a range for field length values, integer values, float values, or date/time stamp values. The types of range attributes are mutually exclusive. For example, you cannot define a field with the LENGTH-GE and INTEGER-LE attributes.

The range constraints are self-editing. If you define a field with LENGTH LE 6 and LENGTH GE 8, the definition is rejected and an error message is issued.

When a range constraint is redefined, it replaces the existing field constraint.

Note:: The range constraints do not have to match the data type of the stored field. That is, you can have a date/time constraint for a STRING field or an integer constraint for a FLOAT field, and so on.

Defining fields with length constraints

Length constraint attributes	Range
LENGTH-EQ	Number from 0 to 255 that indicates the required length of a field
LENGTH-GE	Number from 0 to 255 that indicates the minimum length of a field
LENGTH-LE	Number from 0 to 255 that indicates the maximum length of a field

The following field definitions illustrate using the length constraint attributes, LENGTH-EQ, LENGTH-GE, and LENGTH-LE, with each other and/or with other attributes.

IN FILE REDSOX DEFINE FIELD A WITH LENGTH-EQ 8 BINARY OCCURS 1 IN FILE REDSOX DEFINE FIELD B WITH LENGTH-GE 4 FLOAT LEN 8 IN FILE REDSOX DEFINE FIELD C WITH LENGTH-LE 10 IN FILE REDSOX DEFINE FIELD D WITH LENGTH-LE 10 LENGTH-GE 3 IN FILE REDSOX DEFINE FIELD E WITH LENGTH-LE 6 LENGTH-GE 6 BINARY IN FILE REDSOX DEFINE FIELD F WITH LENGTH-LE 255 LENGTH-GE 0 IN FILE REDSOX DEFINE FIELD COLOR WITH EXACTLY-ONE

Note: The length constraints apply to the string representation of non-string types as they would be stored in the database. This means that the length of a FLOAT field with value 34.000 would be 2 because after being stored in the database it will return 34 on a retrieval.

LENGTH-EQ (LEQ) attribute

The LENGTH-EQ attribute is followed by a number from 0 to 255 that indicates the required length of a field.

The LENGTH-EQ attribute is not allowed for automatic fields.

LENGTH-GE (LGE) attribute

The LENGTH-GE attribute is followed by a number from 0 to 255 that indicates the minimum length of a field.

The LENGTH-GE attribute is not allowed for automatic fields. You can pair the LGE attribute with the LLE attribute, creating a length range. However, if the range is not valid or overlaps, an error message is invoked.

LENGTH-LE (LLE) attribute

The LENGTH-LE attribute is followed by a number from 0 to 255 that indicates the maximum length of a field.

The LENGTH-LE attribute is not allowed for automatic fields. You can pair the LLE attribute with the LGE attribute, creating a length range. However, if the range is not valid or overlaps, an error message is invoked.

Setting an integer value constraint

For Integer values you can define a field with:

Integer attribute	Specifies that the integer value must be...
INTEGER-GE	Greater than or equal to the value that follows
INTEGER-GT	Greater than the value that follows
INTEGER-LE	Less than or equal to the value that follows
INTEGER-LT	Less than the value that follows

The value stored for a field must be an integer with a value that satisfies the constraint. For example:

DEFINE FIELD NCHILDREN WITH INTEGER-LE 100

The NCHILDREN field can have an integer value of less than or equal to 100.

Note: An integer constraint will not accept a value that would be stored with a decimal place. If you have an integer constraint INTEGER-GE 10 and try to store value 10.0, it will fail because 10.0 is not an integer.

INTEGER-GE (INTGE), INTEGER-GT (INTGT), INTEGER-LE (INTLE) and INTEGER-LT (INTLT) attributes

The integer range attributes-INTGE, INTGT, INTLE, INTLT-can be defined singly or as a pair on a field. The integer range attributes establish a range of acceptable integer values for the field. If a record is added with an integer value that is not within the range, the record is rejected.

If you specify another type of range attribute on the same field, such as DATETIME-LE or FLOAT-GE, an error message is invoked.

Setting a float value constraint

For float values you can define a field with:

Float attribute	Specifies that the float value must be...
FLOAT-GE	Greater than or equal to the value that follows
FLOAT-GT	Greater than the value that follows
FLOAT-LE	Less than or equal to the value that follows
FLOAT-LT	Less than the value that follows

The value stored for a field must be a floating point value that satisfies the constraint. For example:

DEFINE FIELD WEIGHT WITH FLOAT-GT 10.5

The WEIGHT field must have a float value greater than 10.5.

FLOAT-GE (FLTGE), FLOAT-GT (FLTGT), FLOAT-LE (FLTLE) and FLOAT-LT (FLTLT) attributes

The float range attributes-FLTGE, FLTGT, FLTLE, FLTLT-can be defined singly or as a pair on a field. The float range attributes establish a range of acceptable float values for the field. If a record is added with a float value that is not within the range, the record is rejected.

If you specify another type of range attribute on the same field, such as DATETIME-LE or INTEGER-GE, an error message is invoked.

Setting a date/time value constraint

For date/time values you can define a field with:

Date/time attribute	Specifies that the date/time value must be...
DATETIME-GE	Later than or the same as the date/time value that follows
DATETIME-GT	Later than the date/time value that follows
DATETIME-LE	Earlier or equal to the date/time value that follows
DATETIME-LT	Earlier than the value that follows

The value stored for field must be a date/time value that satisfies the constraint. For example:

DEFINE FIELD BIRTHDATE WITH DATETIME-GE 18900101 - DATETIME-LT 20100101

The BIRTHEDATE field is required to have a date/time value greater than or equal to January 1, 1890 and less than January 1, 2010. As with any other date/time values, the values can be extended with HH, HHMM, HHMMSS, HHMMSSX, HHMMSSXX, and so on.

DATETIME-GE (DTGE), DATETIME-GT (DTGT), DATETIME-LE (DTLE), and DATETIME-LT (DTLT) attributes

The date/time range attributes-DTGE, DTGT, DTLE, DTLT-establish a range of acceptable dates for the value of the field. If a record is added with a date that is not within the range, the record is rejected. You can specify a single date/time range attribute or a pair of them on a field.

If you specify another type of range attribute on the same field, such as FLOAT-LE or INTEGER-GE, an error message is invoked.

The value stored for a field must be a date/time with a value that has the proper relationship to the following value. For example:

DEFINE FIELD BIRTHDATE WITH DATETIME-GE 18900101 - DATETIME-LT 20100101

The BIRTHDATE field is required to have a date/time value greater than or equal to January 1, 1890 and less than January 1, 2010. As with any other date/time values, the values can be extended with HH, HHMM, HHMMSS, HHMMSSX, HHMMSSXX, and so on.

Concatinated Fields

Fields that make up concatenated field values must be either AT-MOST-ONE, EXACTLY-ONE, or OCCURS 1 and must all be in the same field group context (including no field group). FIELDGROUP * are not allowed.

Fields defined with range attributes cannot be used in concatenation processing. For more information on this, see "Range Constraints" on [[|]].

The following syntax is used to define a concatenated field with a separator and an escape character:

Syntax

DEFINE FIELD fieldname WITH CONCATENATION-OF - field1 WITH field2 [WITH field3 [WITH field4 - [AND . . .]]] - [SEPARATOR (char | X'hex' | NONE)] - [ESCAPE (char | X'hex' | CANCEL)] [OtherAttributes . . .]

The SEPARATOR character appears between the field values that comprise the concatenated field. Up to eight fields may be defined for a concatenated field.

Concatenated fields could be longer than 255 bytes after adding separator characters and escaping results in request cancellation.

Concatenated fields have the following field attribute support.

Concatenated field attributes	Purpose
CONCATENATION-OF (CAT)	List the fields that make up that concatenated field
ESCAPE (ESC)	Specifies a character that is used to escape separator characters that occur in a field that requires a terminating separator character
SEPARATOR (SEP)	Specifies the character that terminates fields that do not have a fixed length and are not the last field in the concatenation

If a required attribute is not assigned a value, the default value is used. For example, if you define a CONCATENATEION-OF field, which requires the SEPARATOR attribute, and you do not enter the SEPARATOR attribute, the default value of X'00' is used as the SEPARATOR value.

CONCATENATION-OF (CAT) attribute

The CONCATENATION-OF (CAT) attribute is followed by a list of fields that make up that concatenated field. At least two are required and up to eight are allowed. The field names must be separated by the word WITH and subsequent field attributes must be separated from the list by the word AND. Each field specified in the list of concatenated fields must have the EXACTLY-ONE attribute specified.

ESCAPE (ESC) attribute

The ESC attribute is followed by one of the following:

• A single character in C'<char>' or X'<hex>' format that specifies the character that is used to escape separator characters that occur in a field that requires a terminating separator character. In addition to escaping the separator character, the escape character can also escape itself.
• CANCEL, which means that an attempt to store a separator character in a field that requires a terminating separator results in request cancellation.

The default ESCAPE character is X'01'.

Once you define an ESCAPE value for a field, you cannot redefine the ESCAPE value.

The ESCAPE character cannot match the SEPARATOR character. The ESCAPE character is always displayed on DISPLAY FIELD output.

The ESCAPE attribute is not allowed on a field definition, if the SEPARATOR NONE attribute is specified.

Using the ESCAPE attribute

The ESC character is inserted into a concatenated field when the real data being added to the database contains the SEPARATOR or the ESCAPE character itself. When reading in stored values, programs use the ESC character to differentiate between the special characters and normal data that happens to include the special characters. When the processing encounters the ESCAPE character in a concatenated field, it knows that the next character is real data.

Note: The ESCAPE character applies to all fields except the last field in the concatenation. The programmer is expected to know how many components comprise the concatenation and therefore, special characters occurring in the last component must be real data.

Consider the following field definitions:

DEFINE FIELD A_CONCAT_TEST1 WITH AT-MOST-ONE DEFINE FIELD A_CONCAT_TEST2 WITH AT-MOST-ONE DEFINE FIELD A_CONCAT_TEST3 WITH CONCATENATION-OF - A_CONCAT_TEST1 WITH - A_CONCAT_TEST2 AND - SEPARATOR C'+' - ESCAPE C'/'

If we then do:

BEGIN IN POLICIES STORE RECORD A_CONCAT_TEST1 = 1ST+VALUE+ A_CONCAT_TEST2 = 2ND+VALUE+ END STORE FRN $CURREC NP PAI END FOR END

We get:

A_CONCAT_TEST1 = 1ST+VALUE+ A_CONCAT_TEST2 = 2ND+VALUE+ A_CONCAT_TEST3 = 1ST/+VALUE/++2ND+VALUE+

The plus signs in the stored records are intentionally part of the data. For the concatenated field Model 204 stores the escape character preceding the plus signs in the data for all but the last field in the concatenation. This assumes that the programmer knows how many fields there are in the concatenation and therefore knows that any separator characters in the last field are data fields.

In storing the escape character immediately before any plus signs that are part of the data proper, Model 204 tells the programmer that they are to be read as data. If there were any escape characters in the data proper, Model 204 puts an escape character before the data proper version as in the following example:

BEGIN IN POLICIES STORE RECORD A_CONCAT_TEST1 = 1ST/VALUE+ A_CONCAT_TEST2 = 2ND+VALUE+ END STORE FRN $CURREC NP PAI END FOR END

... giving the output ...

A_CONCAT_TEST1 = 1ST/VALUE+ A_CONCAT_TEST2 = 2ND+VALUE+ A_CONCAT_TEST3 = 1ST//VALUE/++2ND+VALUE+

If you do not want either the separator or the escape character in the concatenated field, you can enforce this by assigning the value 'CANCEL' as the escape character as follows:

DEFINE FIELD A_CONCAT_TEST1 WITH CONCATENATION-OF - A_CONCAT_TEST1 WITH - A_CONCAT_TEST2 AND - SEPARATOR C'+' - ESCAPE CANCEL

In which case, the concatenation will be ignored and our STORE record results in the following:

A_CONCAT_TEST1 = 1ST/VALUE+ A_CONCAT_TEST2 = 2ND+VALUE+

Note: There are no error messages associated with this condition.

SEPARATOR (SEP) attribute

The SEP attribute is followed by one of the following options:

• A single character in C'&lt;char>' or an X'&lt;hex>' format that specifies the character that terminates fields that do not have a fixed length and are not the last field in the concatenation. The separator character is always used between field values, regardless of any length constraints on the component fields.
• The NONE option that means the fields in the concatenation are concatenated end to end with no separator character and regardless of their length.

If you define a field with the SEP attribute, you must supply a value. Otherwise, the following error is issued:

DEFINE MAKE_MODEL WITH CONCATENATION-OF MAKE WITH MODEL - AND ORD SEP ESC CANCEL *** 1 M204.0405: INVALID DEFINE OPTION: SEP ESC

If you define a field without the SEP attribute, it defaults to X:00:, as shown in the following code:

DEFINE MAKE_MODEL WITH CONCATENATION-OF MAKE WITH MODEL - AND ORD ESC CANCEL D FIELD (ABBREV) MAKE_MODEL MAKE_MODEL (DEF NFRV NKEY NCOD STR NNR VIS UP ORD CHAR LRES 15 - NRES 15 SPLT 50 IMM 1 NUNIQ ONE CAT MAKE WITH MODEL AND - SEP X'00' EXC CANCEL)

Once you define a SEPARATOR value for a field, you cannot redefine the SEPARATOR value.

The SEPARATOR character cannot match the ESCAPE character. The SEPARATOR character is always displayed on DISPLAY FIELD output.

If the SEPARATOR NONE attribute is specified on a field definition, the ESCAPE attribute is not allowed.

Defining concatenated fields

Using the fields defined in [[|]] and the following fields, you can define concatenated fields.

IN FILE REDSOX DEFINE FIELD COLMOD WITH CONCATENATION-OF COLOR - WITH MODEL AND SEP C'-' ESC C'+' IN FILE REDSOX DEFINE FIELD MODCOL WITH CONCATENATION-OF MODEL - WITH COLOR AND SEP C'$' ESC C'*' IN FILE REDSOX DEFINE FIELD MODCOLI WITH CONCATENATION-OF MODEL - WITH COLOR AND SEP X'40' ESC C'*' INVISIBLE ORDERED IN FILE REDSOX DEFINE FIELD MCY WITH CONCATENATION-OF MODEL - WITH COLOR WITH YEAR AND SEP X'40' ESC C'*' INVISIBLE ORDERED IN FILE REDSOX DEFINE FIELD COLCNT WITH CONCATENATION-OF COLOR - WITH COUNTRY AND SEP X'40' ESC C'*' INVISIBLE ORDERED IN FILE REDSOX DEFINE FIELD CNTYR WITH CONCATENATION-OF YEAR - WITH COUNTRY AND SEP X'40' ESC C'*' INVISIBLE ORDERED

Defining concatenated fields in a field group context

IN FILE REDSOX DEFINE FIELD BATTERS IN FILE REDSOX DEFINE FIELDGROUP STARTERS IN FILE REDSOX DEFINE FIELD NTRIP WITH CTO STARTERS AND EXACTLY-ONE IN FILE REDSOX DEFINE FIELD A WITH AT-MOST-ONE FIELDGROUP STARTERS AND- ORDERED CHARACTER IN FILE REDSOX DEFINE FIELD B WITH AT-MOST-ONE FIELDGROUP STARTERS AND - KEY IN FILE REDSOX DEFINE FIELD C WITH AT-MOST-ONE FIELDGROUP STARTERS AND ORDERED NUMERIC IN FILE REDSOX DEFINE FIELD AB WITH FIELDGROUP STARTERS AND - CONCATENATION-OF A WITH B AND SEP C'/' IN FILE REDSOX DEFINE FIELD ABO WITH FIELDGROUP STARTERS AND - CONCATENATION-OF B WITH A AND SEP C'+' ORDERED IN FILE REDSOX DEFINE FIELD ABI WITH CONCATENATION-OF B WITH A AND - SEP C'+' ORDERED INVISIBLE

Note: A field in a concatenated field value does not have a terminating separator set only if it is:

Last field in the concatenation, or

1. Fixed length (LENGTH-EQ set) and the default value has the same length as LENGTH-EQ.

If you specify the ESCAPE CANCEL attribute for a field and the SEPARATOR character for that field is in one of the field values for the concatenated string, the update is cancelled.

Automatic Fields

Model 204 now lets you define a field whose value is automatically maintained. For example, a field may count occurrences of another field so that every store or delete of the field occurrence changes the count in the automatic field. The automatic fields are fields defined with the following attributes:

• COUNT-OCCURRENCES-OF
• CREATE-TIME
• CREATE-TIMEUTC
• CREATE-USER
• UPDATE-TIME
• UPDATE-TIMEUTC
• UPDATE-USER

The value of an automatic field is updated at the start of a transaction by Model 204 and you cannot set it explicitly by a program. Any valid update causes the appropriate time and user stamps to be updated. For example, DELETE FOO(8). when there are no occurrences of FOO in the record.

Once you define an automatic value for a field, you cannot redefine the automatic value.

COUNT-OCCURRENCES-OF (CTO) attribute

The COUNT-OCCURRENCES-OF (CTO) attribute is not allowed for fields defined as REPEATABLE.

Because one of the major advantages of CTO fields is to avoid unnecessary record scans, it is highly recommended that the field be preallocated. Otherwise the field is stored and updated in Table B in the normal way.

CREATE-TIME (CRTM) and CREATE-TIMEUTC (CRTMU) attribute

The CREATE-TIME and CREATE-TIMEUTC attributes record the time of the start of the transaction. Both or either of these create time attributes can be added to non-empty fields. The value is maintained by Model 204 processing, so you cannot change the value.

The CRTM and CRTMU attributes are not allowed for fields defined as REPEATABLE.

CREATE-USER (CRUS) attribute

The CREATE-USER attribute can be added to non-empty fields. The value is maintained by Model 204 and cannot be edited.

The CRUS attribute is not allowed for fields defined as REPEATABLE.

UPDATE-TIME (UPTM) and UPDATE-TIMEUTC (UPTMU) attributes

The UPDATE-TIME and UPDATE-TIMEUTC attributes record the time of the start of the transaction. Both or either of these update time attributes can be added to non-empty fields. The value is maintained by Model 204 processing, so you cannot change the value.

Once you define an UPDATE-TIME or UPDATE-TIMEUTC value for a field, you cannot redefine the update time value.

The UPTM and UPTMU attributes are not allowed for fields defined as REPEATABLE.

UPDATE-USER (UPUS) attribute

The UPDATE-USER attribute can be added to non-empty fields.

Once you define an UPDATE-USER value for a field, you cannot redefine the UPDATE-USER value.

The UPUS attribute is not allowed for fields defined as REPEATABLE.

Displaying automatic fields

You can specify the AT-MOST-ONE or EXACTLY-ONE attribute for an automatic field. These attributes affect the order in which the fields are displayed on a PAI statement. EXACTLY-ONE fields are displayed in the order they were defined, whereas AT-MOST-ONE fields are displayed in the order they are stored in Table B.

Currently Table B stores automatic fields in the following order:

UPDATE-TIME

1. UPDATE-USER
2. CREATE-TIME
3. CREATE-USER

Counting occurrences of a field

To create such a field you use the COUNT-OCCURRENCES-OF (CTO) keyword on a field definition followed by the name of the field that the field or field group for which the count is automatically maintained. For example, if you have field group DRUG you could define an automatic count field as follows:

DEFINE FIELDGROUP DRUG DEFINE FIELD DRUGCT WITH COUNT-OCCURRENCES-OF DRUG

You cannot define a CTO field in a file that has had records already added to it.

You could also make the automatic count field a binary field:

DEFINE FIELD DRUGCT WITH CTO DRUG AND BINARY OCCURS 1 DEFINE FIELD DRUGCT WITH COUNT-OCCURRENCES-OF DRUG AND - BINARY

Note: AND is a separator between a field or field group name and other field attributes.

An automatic count field can also be an OCCURS 1 field:

DEFINE FIELD DRUGCT WITH COUNT-OCCURRENCES-OF DRUG AND BINARY OCCURS 1

An automatic counter field could even be ORDERED, KEY or NUMERIC RANGE:

DEFINE FIELD DRUGCT WITH CTO DRUG AND BINARY OCCURS 1 ORDERED NUMERIC

You can find all records with N occurrences of field group DRUG. Or, you can find those that have more or less than some number of occurrences.

Limitations on COUNT-OCCURRENCES-OF (CTO) fields

Fields defined with the CTO attribute cannot also have the UPDATE AT END attribute. You cannot define a CTO field that counts occurrences of UPDATE AT END fields.

You cannot use a CTO field to count occurrences of OCCURS fields, even though the OCCURS count is greater than 1.

Also, you cannot define a CTO field in a file that has had records already added to it.

There is a maximum of one CTO field per field or field group.

Retrieving the value of a CTO field

You can retrieve values of CTO fields via the field name (as with any VISIBLE field), as in:

PRINT DRUGCT

or

%DRUGCT = DRUGCT

Automatic optimization with COUNT OCCURRENCES OF fields

The use of CTO fields automatically optimizes many of the record scan processes:

• FOR EACH OCCURRENCE and FOR ALL OCCURRENCES statements

  If a CTO field exists for the field or field group that is being looped over via an FEO or FAO statement, the value of that field limits the number of times the loop is processed, which reduces the need to scan to the end of record for every FEO or FAO statement. Note that when a record is updated or might be updated mid-loop, Model 204 normal concerns about subscript handling in FEO and FOR ALL OCCURRENCES statements need to be considered.

• FOR FIELDGROUP clause

  Before processing a FOR FIELDGROUP loop, Model 204 checks whether the requested occurrence is in the record based on the CTO field. If the User Language request is for occurrence 3, but the CTO field indicates only two occurrences in the record, Model 204 stops processing the FOR loop.

• Field extraction

  Before extracting a field Model 204 checks whether the requested occurrence is in the record based on the CTO field. If the User Language request is occurrence 3, but the CTO field indicates only two occurrences in the record, Model 204 sets the result to null (or NOT PRESENT).

  Note the in the case of the subscripted field optimization:

FOR %I FROM 1 TO %N %FOO = FOOFIELD(%N) END FOR

Model 204 checks only the number of occurrences on the first iteration of the loop.

In all cases, the greatest benefit from the CTO field optimization is when a record contains no occurrences of the requested field or field group. Model 204 immediately determines that no occurrence is on the record and stops scanning the record.

Defining an efficient CTO field

The counter fields are always physically present in the record. Using a DEFAULT-VALUE of zero for such a field defeats its purpose as you need to scan the entire record to determine whether to use the default value. When you issue a STORE RECORD statement, all CTO fields are stored as 0 in the record. For non-OCCURS, fields are always the first variable length fields in the record.

Model 204 customer support recommends that you define a CTO field as BINARY OCCURS 1, as these require only four bytes per record, whereas the minimum length of a variable length field is four bytes (for a compressed 0) and will be at least five for any non-zero value. A CTO field inside a field group cannot be an OCCURS field.

The absolute maximum value for CTO fields is currently X'3FFFFFFF' or 1,073,741,823 since BINARY fields use the high order two bits to distinguish values from coded values.

You cannot update CTO fields; attempts are caught at compile-time for explicit file-context references and at run-time for group-context and field-name variables.

CTO fields can even be in field groups and zeros are stored for CTO fields when the ADD FIELDGROUP or INSERT FIELDGROUP statement is processed.

Displaying CTO fields

By default, the PRINT ALL INFORMATION (PAI), AUDIT ALL INFORMATION (AAI), PRINT ALL FIELD GROUP INFORMATION (PAFGI), and AUDIT ALL FIELDGROUP INFORMATION (AAFGI) statements do not display CTO (counter fields). When you are using PAI [INTO] to reorganize your files or to copy records, the automatic fields are likely to cause problems.

Under other circumstances you can use the argument to indicate that you do want automatic fields to be printed by these statements.

Tracking updates using automatic fields

Model 204 can now track the date/time when a record was created or updated and what user ID created or updated it. This can be accomplished by using one of the following field tracking attributes:

Tracking attribute	Tracks the...
CREATE-TIME	Moment the record was created using machine time
CREATE-TIMEUTC	Moment the record was created using Coordinated Universal Time
CREATE-USER	User ID that created the record
UPDATE-TIME	Moment the record was updated using machine time
UPDATE-TIMEUTC	Moment the record was updated using Coordinated Universal Time
UPDATE-USER	User ID that updated the record

All of the automatic fields that contain date/time information also have the DATETIME attribute.

You cannot update tracking fields with User Language.

A record or field group can contain only a single field of each update tracing type (CREAT-USER, UPDATE-USER, CREATE-TIME, UPDATE-TIME). An attempt to create a second invokes an error message.

You can define update tracking fields (CREATE-TIME, UPDATE-TIME, CREATE-USER, UPDATE-USER) in an already populated file, even an update tracking field for the main records or a field group that already has occurrences. You can also define a DEFAULT-VALUE field, which defaults to a null string. This lets you define such fields with or without a reorganization.

Tracking fields for records

The file itself may have a single field defined for each of the tracking fields.

An attempt to define a second field of the same type on the file results in an error message, for example:

DEFINE FIELD RECORD.CREATE.TIME WITH CREATE-TIME DEFINE FIELD RECORD.CREATE-TIME2 WITH CREATE-TIME *** M204.2866: FILE ALREADY HAS A CREATE-TIME FIELD

The UPDATE tracking fields for the records are updated when any field in the record changes and the create tracking fields are added to the record when it is initially stored.

Tracking fields for field groups

Automatic tracking fields can also provide a date/time and user stamp of the creator and last update of each occurrence of a field group.

If an UPDATE-TIME tracking field is defined as a part of the field group as shown below:

DEFINE FIELD MY_UPDATE_TIME WITH FIELDGROUP X AND - ORD NUM UPDATE-TIME

then each occurrence of field group X will contain the field.

Setting a date/time field

The DATETIME attribute indicates the format of the data stored in Table B.

You can now define a field with a DATETIME attribute:

DEFINE FIELD WHEN WITH DATETIME

DATETIME fields use seven bytes to store the value (plus four bytes overhead, if the field is not preallocated). A binary representation is used to store the 20-character full date format so that it can be held in seven bytes. the DATETIME attribute may not be combined with KEY. You assign a string to it in YYYYMMDDHHMMSSXXXXXX format:

IN FILE FOO DEFINE FIELD WHEN WITH DATETIME ... BEGIN IN FILE FOO STORE RECORD WHEN = '2010070413470643516378' END STORE END

If you attempt to store the field with invalid data such as, WHEN = '72', the request will be cancelled and you will receive an error:

*** 1 CANCELLING REQUEST: M204.2865: ATTEMPT TO ADD INVALID DATETIME VALUE: WHEN = 72

As most timestamps do not require microseconds, you can specify 10ths, 100ths, 1,000ths, 10,000ths and 100,000ths of a second. The value is simply padded on the right with zeros. For date/time field WHEN, the following are all equivalent:

ADD WHEN = '20090704134706000000' ADD WHEN = '2009070413470600000' ADD WHEN = '200907041347060000' ADD WHEN = '20090704134706000' ADD WHEN = '2009070413470600' ADD WHEN = '200907041347060' ADD WHEN = '20090704134706'

In addition, you can leave off seconds, minutes or hours so the following are all equivalent:

ADD WHEN = '20090704000000' ADD WHEN = '200907040000' ADD WHEN = '2009070400' ADD WHEN = '20090704'

which are also, of course, equivalent to

ADD WHEN = '20090704000000000000'

Storing dates or date/times efficiently

The shortest valid format for a field with the DATETIME attribute contains YYYYMMDD. If the values you are storing are simple dates, they can be stored in four bytes as binary values, so requires three fewer bytes per value than a date/time field. However, for anything with a time in it (even just hours), a DATETIME field takes less space than the alternative field types (FLOAT or STRING).

Automatic validation of date/times

In addition to the compact storage of date/time stamps, DATETIME fields also provide automatic validation of the values stored into them. That is, you cannot store numbers into them that are not valid dates. For example, you cannot store '9999111' into a DATETIME field, nor can you store '20070931' (because September does not have 31 days), nor can you store '20070229' (because 2007 is not a leap year, so February only has 28 days).

DATETIME fields also provide for field content validation for DATETIME fields.

Note: It is possible to set a DATETIME DEFAULT-VALUE to an invalid date/time value, such as '_', and to set a DATETIME field to that value. That value will not be stored in the database and the value returned for an EXACTLY-ONE field value that had not been set would be the default value in such a case.

The value returned for DATETIME fields is always in:

YYYYMMDDHHMMSSXXXXXX

If you are interested in only part of the time stamp, you can either use the $SUBSTR function to get the part you want or make the %variable that will hold the time stamp length you are interested in. So, if you want %WHEN to hold a date/time in YYYYMMDDHHMISS format, simply declare %WHEN as:

%WHEN IS STRING LEN 14 ... %WHEN = WHEN

DATETIME values can be indexed either as ORDERED CHARACTER or ORDERED NUMERIC. When indexed as ORDERED NUMERIC, only the first 15 digits (YYYYMMDDHHMISSX) are indexed. When a DATETIME field is indexed as ORDERED CHARACTER, it is indexed as the string representation of the date/time value in the full YYYYMMDDHHMISSXXXXXX format.

Note: Making DATETIME fields KEY fields is not allowed.

Update processing

Date/time values are the date/time of the start of the update processing, so all records, fields and field groups updated by a single update get the same time stamp. Furthermore, the code that starts an update unit ensures that the date/time stamp of each update unit is unique.

The M204.0173 and M204.0172 messages were expanded to include the date/time stamp.

M204.0173: START OF UPDATE 12 AT 10:11:00.51 (20090705101100511734)

and

M204.0172: END OF UPDATE 12 AT 10:11:00.56 (20090705101100511734)

Defining various UPDATE-USER fields

To indicate whether an update tracking field applies to a record or field group, specify or do not specify the containing field group on the field definition. For example:

DEFINE FIELD WHO WITH UPDATE-USER

The WHO field contains the user ID of the last user that updated anything in the record. The following example illustrates defining an UPDATE-USER field in a field group:

DEFINE FIELD DR.WHO WITH UPDATE-USER FIELDGROUP DR

The DR.WHO field contains the user ID of the last user that updated an occurrence of field group DR.

Defining various UPDATE-TIME fields

You can specify an update tracking field as FIELDGROUP *, which means that all field groups and the primary record all use the same update tracking field. For example:

DEFINE FIELD WHEN WITH UPDATE-TIME FIELDGROUP *

The WHEN field is placed in the primary record and each field group to track the last update time. If a file has a FIELDGROUP * update tracking field and a field group or record has a different comparable updating tracking field, the field group or the record specific tracking field applies to the field. For example:

DEFINE FIELD WHEN WITH UPDATE-TIME FIELDGROUP * DEFINE FIELD MAIN.WHEN WITH UPDATE-TIME OCCURS 1 DEFINE FIELD FOO.WHEN WITH UPDATE-TIME FIELDGROUP FOO

• The field WHEN would be updated for changes to any field group occurrences in field groups that do not have their own UPDATE-TIME field defined.
• The Field MAIN.WHEN would be set for any update to any field in the record.
• The field FOO.WHEN would be set in field group FOO for any update to a field group occurrence.

The UPDATE-TIME field in a field group that has a nested field group will be updated when the nested field group is updated, even though no fields in the containing field group were updated.

An alternate approach to adding a date/time stamp field to your files

The update date and time described above

There is a more general way you can include a date/time stamp field in your files. The date/time stamp feature lets you put a date/time stamp (DTS) on each record that was updated in a transaction. You can then use the data in the date/time stamp field in an end-user written application to find and process all the rows of data that were changed.

An application that processes these records can track the date/time stamp field values that have been processed to date, or it may take another action, including updating the date/time stamp field.

To install and use this functionality, you must obtain the proper decrypt keys and install this release. Once installed, the date/time stamp feature works only in files created in V6R1.0 or later.

More detail about the system wide implications about this kind of Date Time Stamp field are discussed in Adding a Date-Time Stamp field

Note: There is no support in PQO/204 for the data/time stamp feature.

Combining Attributes

When deciding on combinations of field attributes, use the chart in Combining attributes to see if the combination you want to use is valid, See Key to table for combining attributes.

Field attribute combinations

Attribute	KEY	OCH	ONM	FRV	UNQ	NR	DEF	LVL	ONE	INV	STR	FLT	BIN	COD	FV	OCC	LEN	PAD	UPD	BLOB	CLOB
KEY
ORD CHAR (OCH)			N	N								N
ORD NUM (ONM)		N		N		N
FRV	R	N	N		N
UNIQUE (UNQ)		@	@	N			N
NUM RANGE (NR)			N									N				=1
DEFERRABLE (DEF)	@	@	@		N	@
LEVEL (LVL)
AT-MOST-ONE (ONE)										N
INVISIBLE (INV)	@	@	@			@			N			N				N	N	N	N
STRING (STR)												N	N
FLOAT (FLT)		N				N				N	N		N	F			R	N
BINARY (BIN)											N	N					N	N
CODED (COD)												F					F	N
FEW VALUED (FV)					@									@
OCCURS (OCC)						=1			=1	N			@	@			@
LENGTH (LEN)										N		@	N	F		@
PAD										N		N	N	N			R
UPDATE (UPD)										N
BINARY_LARGE_OBJECT (BLOB)	N	N	N	N	N	N	N	N	N	N		N	N	N	N		N	N			N
CHARACTER_LARGE_OBJECT (CLOB)	N	N	N	N	N	N	N	N	N	N		N	N	N	N		N	N		N

Key to table for combining attributes

Code	Meaning
N	This attribute combination is not legal.
R	This attribute combination is required.
@	One of these attributes is required. You can choose any one of the attributes marked, but you must choose at least one.
=1	The OCCURS clause is valid with this attribute only if defined as OCCURS=1. You cannot enter multiple field values for fields with these attributes.
F	The FLOAT attribute requires the LEN attribute. Since the FLOAT attribute can also be used with the CODED attribute, this is the only circumstance that the LEN attribute is legal with CODED attribute.

Some of the illegal combinations are direct conflicts, such as UNIQUE and DEFERRABLE; others describe meaningless situations or unusable fields, such as ORDERED CHARACTER and ORDERED NUMERIC.

In addition, some attributes require other attributes. FLOAT, for example, requires a LENGTH specification.

Field descriptions that contain invalid combinations are rejected.

Combining attributes is for Entry Order files. More stringent restrictions are placed on fields that are used as sort keys, hash keys, or record security fields. These restrictions are summarized in the respective Commands and Parameters pages for each.

Field Naming

Every field in a Model 204 file has a field name by which it is referenced. While you have a fair amount of latitude in choosing field names, there are certain restrictions:

• Field names must begin with a letter.
• When more than one consecutive space appears in a field name, the extra spaces are ignored.
• Field names can contain as many as 255 characters. Field names should be short enough to enter easily, but long enough to have meaning.
• Certain restricted words and special characters either cannot be used at all or can be used only with special syntax.

Reserved characters allowed in field names with special syntax

If any of the reserved characters listed in Reserved characters allowed in field names with special syntax is embedded in a field name, the character must be part of a quoted string. When forming field names, avoid the following characters.

Reserved characters allowed with special syntax

$	>	+
(	<	- (minus sign/hyphen)
)	*	:
=	/	,
...	:	%

Reserved characters not allowed in field names

The special characters listed in Reserved characters not allowed in field names cannot be used in field names.

Reserved characters not allowed in field names

??	@ (as delete character)
?$	# (as flush character)
?	;
&

Note

The delete (@) and flush (#) characters used at an installation are controlled by the ERASE and FLUSH parameters, described in the Model 204 Parameter and Command Reference. If different symbols are chosen, the restriction on using these characters in field names applies to the new symbols.

Examples

Some examples of legal field names are:

A534 A' = B' ANNUAL.%INTEREST YEAR.TO.DATE JULY.3.95

Some examples of illegal field names are:

%INTEREST YEAR TO DATE USE COUNT NAME?? 3JULY95

Reserved words requiring special attention in field names

The reserved words or operators listed in Reserved words requiring special attention in field names can be part of an unquoted string as long as they are not surrounded by spaces (NORTHERN SALES is acceptable while NOR SLS is not). They can be part of a quoted string as long as they do not stand alone (A 'OR' B is acceptable while 'OR' is not). Although you can define field names using reserved words, programs that reference those fields might not compile and you might receive errors or other unexpected results.

Reserved words requiring special attention

AFTER	EACH	NOR	RECORDS	WITH
ALL	EDIT	NOT	TAB	EQ
AND	END	OCC	THEN	GE
AT	FROM	OCCURRENCE	TO	GT
BEFORE	IN	ON	VALUE	LE
BY	IS	OR	VALUES	LT
COUNT	LIKE	RECORD	WHERE	NE

Using reserved words or characters in field names

If you do use reserved words or characters as part of a field name, you can reference the field names by enclosing the field name with the reserved word or character in single quotation marks. The following example uses a field name called NOR SLS:

FIND ALL RECORDS WITH 'NOR SLS' = 100000...

To delete a procedure with a reserved character:

DELETE PROC '%SAVINGS'