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:
To define derived fields, select the field on the Field List tab and choose the 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.
The following command buttons are available (in addition to the standard command buttons):
| Command Button | Description |
|---|---|
| New | Create a new source field. The source field can be selected from a list of all fields contained in the file. |
| Delete | Delete the selected source field. |
| Up | Move the selected source field one position up in the table. |
| Down | Move the selected source field one position down in the table. |
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 Position | 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 Position | 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. |
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.
The following topics are covered below:
The window for the definition of superfields/descriptors for files of type Adabas, Conceptual and Standard looks as follows.
See also General Rules for Defining Derived Fields.
Subfields/Descriptors for files of type Adabas, Conceptual and Standard are defined in the window below.
With subfields/descriptors, only one source field can be entered in the window.
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).
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.
Note:
See also
General
Rules for Defining Derived Fields.
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:
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/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.
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.
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.
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.
Changes to sub/superfield/descriptors and fields used in sub/superfield/descriptors (source fields) are rippled as described in the sections below.
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 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).
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.
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 or button.
The following topics are covered below:
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.
Note:
See also
General
Rules for Defining Derived Fields.
The window for defining hyperdescriptors looks as follows:
| Attributes | |
|---|---|
| User Exit No. | A number between 1 and 31 identifying the user exit that defines the hyperdescriptor. See the Adabas User Exits documentation. |
The window for defining collation descriptors looks as follows:
| 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. |
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.
| 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 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: |
||||||||
| Ascending |
|
||||||||
| Expression | Only applies when an index and if files of type D or MT. Select the desired field and click on the button to display an editor for the expression. Up to 64 expressions can be maintained. An asterisk indicates that an expression exists. | ||||||||
VSAM superdescriptors (Field type SP) in a file of type V (physical VSAM) and L (logical VSAM) are defined in the following window.
| Attributes | |
|---|---|
| Start Position | 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 from the context menu of a field: upgrade flag, sort flag, null flag and DD name (see below).
| Additional Descriptor Attributes | |||||
|---|---|---|---|---|---|
| Upgrade flag |
|
||||
| Sort flag |
|
||||
| Null flag |
|
||||
| DD name | The DD name associated with this alternate index file. In CICS, the FCT name of the VSAM file. | ||||