Defining Derived Fields

Note:
Derived field is a generic term in Predict for fields and descriptors defined on the basis of one or more source fields. This term should not be confused with field type DV applicable to SQL views (see Field Type).

Defining derived fields and keeping the definitions consistent is a complex task. Predict offers a variety of functions to help with it.

General rules applying to the definition of derived fields are described in the following sections:


General Rules for Defining Derived Fields

To define derived fields, select the field on the Field List tab and choose the Source button. A new window appears, displaying a table in which you can define derived fields. The size and format of this table varies with the type of the derived field.

The window below only applies to files of type D or MT with format SP (superfield). Other SQL file types lack the Random option and have no Expression option either. Refer to Key or Index Fields in SQL Files - Superfields for details on these options.

Superfield

The following commands are available (in addition to the standard commands):

Command Description
Add Create a new source field. The source field can be selected from a list of all fields contained in the file.
Remove Delete the selected source field.

General Attributes of Definitions of Derived Fields

The following attributes are contained in most definitions of derived fields. Attributes specific to certain types of derived fields are described in the respective sections.

Attributes
Source field Name of the fields used by derived fields. Each cell in this column contains a drop-down list box. This drop-down list box provides for selection all fields of the field list which are valid in the current context.
F, Length Format and length of the source field. These columns are read-only. Section Rules Applying to Format Changes describes how the format of the derived field is determined by Predict.
Start The relative byte position where the part of the source field to be used by the derived field starts (not applicable to phonetic descriptors). See also Specifying the Start and End Position below.
End The relative byte position where the part of the source field to be used by the derived field ends (not applicable to phonetic descriptors and VSAM Primary Superdescriptors or Alternate Indices). See also Specifying the Start and End Position below.
DB Field short name of the source field. This column is read-only.

Specifying the Start and End Position

The start and end values given in the definition of a derived field are always byte positions within the source fields (beginning with 1 and counting from left to right for alphanumeric fields and binary fields and from right to left for numeric fields).

The full length is used if no start and end values are specified. In Adabas it is possible to address byte positions outside of the length of field. If this feature is used and a start byte outside of the source field specified, an end byte must be specified.

Note:
Special rules apply when specifying the length of subfields/descriptors. See Specifying the Length of Subfields.

The following rules apply:

  • Superfields/descriptor definitions can be based on up to twenty source fields.

  • Only formats A, B and N are possible for superfields/descriptors.

  • Format N can be useful for Natural, but is not recommended because an alphanumeric or binary value cannot be converted to a numeric field.

Defining Derived Fields of Special Types

The following topics are covered below:

Superfields/Descriptors for Files of Type A, C and Z

The window for the definition of superfields/descriptors for files of type Adabas, Conceptual and Standard looks as follows.

Superfield

See also General Rules for Defining Derived Fields.

Subfields/Descriptors for Files of Type A, C and Z

Subfields/Descriptors for files of type Adabas, Conceptual and Standard are defined in the window below.

Subfield/descriptor

With subfields/descriptors, only one source field can be entered in the window.

Specifying the Length of Subfields

If the source field of a subfield/descriptor has format P and the start byte is greater than 1, the length of the subfield/descriptor is normal length+1, because the sign of the source field is always included in the subfield/descriptor field (see Adabas Utilities documentation).

Example:

Given that

  • the source field has format P and length 5,

  • the subfield/descriptor definition is source field from 2 to 3,

  • the length of the subfield is 3 bytes (2 bytes + 1 byte for sign),

  • the 3 bytes packed are 5 digits,

then the subfield/descriptor has format P and length 5.

Rules Applying to Format Changes

The format of derived fields is determined by Predict or can be defined manually.

Note:
To understand the following, some knowledge of the hierarchical data structures of Predict and the process of rippling is required. See Rippling in the section File for more information.

The following topics are covered below:

Determining the Format of Sub/Superfields/Descriptors

The format of sub/superfields/descriptors in files of type Adabas, Conceptual and Standard (codes A, C, Z) is generated automatically by Predict. A format of a derived field that has been determined by Predict can, however, be overwritten manually. The following sections describe the rules applying.

Subfield/Descriptor

Subfield/descriptors always have the same format as the source fields they are derived from. If the format of a source field is changed, the format of the subfield/descriptor is changed accordingly.

Superfield/Descriptor without Format

If a superfield/descriptor is defined without a format, Predict assigns the format as follows:

  • Format=A
    if at least one source field of the SP field is defined with format A, or if one of the source fields specified in the definition does not yet exist in the file.

  • Format=B
    if no source field is defined with format A.

Superfield/Descriptor with Format

If the format of source fields has been changed, Predict checks if the new and the old format of the source field are compatible. If they are compatible, the change does not have any impact on the format of the superfield/descriptor.

The formats NS, US, N and U and the formats P and PS are compatible. So, if the format is changed from N to US, for example, the format of the superfield/descriptor will not change.

If the new and the old format of the source field are not compatible, a window appears in which a format change proposed by Predict can be confirmed or a new format can explicitly be assigned to the superfield/descriptor.

Changing the Format of Superfield/Descriptors Manually

The format of a superfield/descriptor can be changed manually. If a source field of the superfield/descriptor is then changed again, Predict checks if the change affects the format of the superfield/descriptor.

Impact of Changes to Standard Fields - Rippling

Changes to sub/superfield/descriptors and fields used in sub/superfield/descriptors (source fields) are rippled as described in the sections below.

Changes to Sub/Superfield/Descriptors

It is not recommended to define sub/superfield/descriptors in standard files and to use these in real files. It is however possible to do it. The following rule then applies:

Changes to the format and length and changes to the definition of derived fields in standard files are not rippled from standard files to real files and userviews. This is because the definition of derived fields is not coupled, and rippling format and length alone could lead to inconsistent data definitions in real files and userviews.

Changes to Source fields

Changes to the format of a standard field are rippled as normal to all fields in a file connected to this standard field.

If a field in an Adabas file is used in the definition of a sub/superfield/descriptor, the format of the sub/superfield/descriptor is also changed if one of the following conditions is met:

  • the resulting format is A, or

  • the resulting format is B and the old format was A.

Note:
In the case of superdescriptors, if the format in the Adabas file is set (manually) to N and the correct format were B, no change is made (unless the field length is greater than 29).

Rules Applying to Suppression/Length Changes

The suppression and length of derived fields is determined by Predict.

The suppression and length of sub/superfields/descriptors in files of type Adabas, Conceptual and Standard (codes A, C, Z) is generated automatically by Predict. A derived field gets:

  • null suppression if at least one source field has null suppression.

  • the total length of the source field(s).

    Note:
    The full source field length is used if no start and end values are specified.

If the suppression or length of a source field is changed, suppression and length of the sub/superfields/descriptors are changed accordingly.

Validation of Derived Field Definitions

If the format of derived fields is changed manually, Predict performs validation checks. These checks are described in this section.

Predict performs the following validations for derived fields:

  • A superfield/descriptor can have only one source field which is a multiple-value field.

  • Source fields with format D, T, or L must not have a start or end character.

The following rule applies for all file types except Conceptual and Standard: All source fields must exist in the file. This check is performed when you choose the OK or Apply button.

The following topics are covered below:

Phonetic Descriptors for Files of Type A, C and Z

The window for defining phonetic descriptors for files of type Adabas, Conceptual and Standard is identical to that for subfields/descriptors. See Subfields/Descriptors for Files of Type A, C and Z.

With phonetic descriptors, only one source field can be entered in the window.

The Start and End attributes do not apply to phonetic descriptors: Adabas always uses the first 20 bytes of this field to build a phonetic descriptor.

Hyperdescriptors for Files of Type A, C and Z

The window for defining hyperdescriptors looks as follows:

Hyperdescriptor

Attributes
User exit nr A number between 1 and 31 identifying the user exit that defines the hyperdescriptor. See the Adabas User Exits documentation.

Collation Descriptors for Files of Type A, C and Z

The window for defining collation descriptors looks as follows:

Collation descriptor

Attributes
User exit nr

A number between 1 and 8 identifying the user exit that defines the collation descriptor.

See the Administration section of the Adabas on Open Systems documentation for further information.

HE option

If you specify this option, you must specify the corresponding parent field value in the value buffer for search operations, rather than the internal collation key.

See the Administration section of the Adabas on Open Systems documentation for further information.

Locale

One of the locales supported by ICU.

See the Administration section of the Adabas on Open Systems documentation for further information.

Strength

You can specify one of the following values: P (Primary), S (Secondary), T (Tertiary), Q (Quarternary), I (Identical) or blank (none). The value specified represents the comparison levels.

See the Administration section of the Adabas on Open Systems documentation for further information.

Case first

Specifies whether uppercase letters will be sorted before lowercase letters or vice versa.

Valid values: U (Upper), L (Lower) or blank (none).

See the Administration section of the Adabas on Open Systems documentation for further information.

Alternate

Specifies the sorting sequence for punctuation characters such as space or hyphen.

Valid values: S (Shifted), N (Non ignorable) or blank (none).

See the Administration section of the Adabas on Open Systems documentation for further information.

Case level

If specified, an additional case level is formed between secondary and tertiary.

Valid values: C (Caselevel), N (No caselevel) or blank (none).

See the Administration section of the Adabas on Open Systems documentation for further information.

French

Specifies whether or not diacritics will be sorted as in French.

Valid values: F (French), N (No french) or blank (none).

See the Administration section of the Adabas on Open Systems documentation for further information.

Normalization

Specifies whether or not Unicode canonical equivalence is to be taken into account.

Valid values: O (Normalization), N (No Normalization) or blank (none).

See the Administration section of the Adabas on Open Systems documentation for further information.

Key or Index Fields in SQL Files - Superfields

The window below is used for defining Keys or Indexes in fields of the following file types:

A Adabas file (with parameter Adabas SQL usage set to Y)
BT Adabas D table
D DB2 table
MT DB2 query table
JT Ingres table
OT Oracle table
X General SQL
XT Informix table
YT Sybase table

The following rules apply:

  • If the field type is blank (normal field), the key or index is based on one field.

  • If the field type is SP (superfield), the key or index includes more than one field.

  • If the field type is SP (superfield), the index includes one or more fields if the file is D or MT.

Key or index fields

Attributes
Index name The name of the key or index. Must be entered in qualified form: creator/schema name followed by key or index name, separated by a hyphen. The creator/schema and key or index name are subject to SQL naming conventions. Creator name and field name are concatenated and proposed as index name.
No Overlaps

Only applies when documenting DB2 temporal tables that record the application period (BUSINESS_TIME). Specifies whether (Y) or not (N) the BUSINESS_TIME WITHOUT OVERLAPS clause applies.

If BUSINESS_TIME WITHOUT OVERLAPS is specified, the BUSINESS_TIME period will not overlap in time periods for the same column-name values.

Source field The name of a column (source field) from which the key or index is derived. If the key or index is based on one field (field type blank), the name of that field is displayed and cannot be changed. If the key or index includes more than one field (Field type SP), up to 64 column names can be entered. Each must name a column of the table.

Note:
For fields in files of type X (General SQL), you can enter up to 16 column names.

Sort seq
  Puts key or index entries in ascending order by source fields (column). Default.
  Puts key or index entries in descending order by source fields (column).
  Puts key or index entries in random order by source fields (column). Only applies to files of type D or MT.
  Specifies additional columns to append to the set of index key columns of a unique index. Only applies to files of type D or MT.
Expression Only applies when an index and if files of type D or MT and when in Expression mode. Select the desired field and click on the Expression button to display an editor for the expression. Up to 64 expressions can be maintained. An asterisk indicates that an expression exists.

VSAM Primary Superindex or Alternate Superindex

VSAM superdescriptors (Field type SP) in a file of type V (physical VSAM) and L (logical VSAM) are defined in the following window.

Superdescriptor

Attributes
Start The starting position (offset plus one) of the superdescriptor within the source field. An end position cannot be specified.

If a VSAM field on an alternate index (descriptor A) in a file of type V (physical VSAM), L (logical VSAM), or C (conceptual) is defined (the descriptor type is A), the following additional attributes can be defined when you choose Open from the context menu of a field: upgrade flag, sort flag, null flag and DD name (see below).

Additional Attributes for VSAM Alternate Fields

Additional attributes for VSAM alternate fields

Additional Descriptor Attributes
Upgrade flag
Y Alternate index is updated by Natural.
N Alternate index is updated by VSAM.
Sort flag
Y If the upgrade flag is also Y, the alternate index is read in ascending order. Otherwise, the alternate index is read in the order in which the values were entered during field update.
Null flag
Y Records with a null value in this index field are suppressed.
DD name The DD name associated with this alternate index file. In CICS, the FCT name of the VSAM file.