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:
If a derived field is modified, a table containing the source fields appears when ENTER is pressed in the Modify Field screen. The size and format of this table varies with the type of the derived field.
The table in the screen below only applies to files of type D or MT with format SP (superfield). Editor functions and a selection mechanism help when defining derived fields. Other SQL file types lack the Random option as shown in the A/D/R column and have no Expression option either. Refer to Key or Index Fields in SQL Files - Superfields for details on these options.
13:24:29 ***** P R E D I C T ***** 2009-07-31
- Modify Field -
Field ID ........ ARH_SP Modified 2007-05-31 at 13:14
File ID ......... ARH-D1
Ty L Field name F Length Occ D U DB N NAT-l
-- - -------------------------------- -- -------- ----- - - -- - -----
SP 1 ARH_SP D U AB
Index Name .... ARH-ARH_SP
Source field name * A/D/R Expression
1 ARH1 A _
2 ARH3 D _
3 ARH2 A _
4 ARH4 A _
5 ARH5 A _
6 ARH6 A _
7 ARH7 A _
8 ARH8 A _
9 ARH9 A _
10 ARH10 A _
Additional attributes ..* N Associations ..* N Scroll to: __
|
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 | Name of the fields used by derived fields. |
| 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. |
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.
Source fields can be deleted from the definition by overwriting their name with blanks. The remaining lines will be reorganized automatically.
Source fields can be moved with the .m command. Enter .m at the beginning of the line to be moved, position the cursor in the line where the moved line is to appear, and press ENTER. The table of source fields is automatically reorganized.
If a definition of a derived field contains more source fields than can be displayed in one screen, the source field to be displayed on top of the list can be specified in the field Scroll to. See General Rules for Defining Derived Fields.
New source fields can be added to the definition of a field by selecting them from a list of all fields contained in the file. This list is displayed in a Source field window if a name with asterisk notation (*) is entered as selection criterion in the list of the current definitions (as shown in the screen below).
13:09:01 ***** P R E D I C T ***** 2007-05-31
- Modify Field -
Field ID ........ ARH_SP +Top---------Source field------------+
File ID ......... ARH-D1 ! _ ARH1 !
! _ ARH2 !
! _ ARH3 !
Ty L Field name F ! _ ARH4 !
-- - -------------------------------- - ! _ ARH5 !
SP 1 ARH_SP ! _ ARH6 !
! _ ARH7 !
Index Name .... ARH-ARH_SP ! _ ARH8 !
Source field name * ! _ ARH9 !
1 ARH1 ! _ ARH10 !
2 ARH3 ! _ ARH11 !
3 ARH2 ! _ ARH12 !
4 ARH4 ! _ ARH13 !
5 ARH5 ! _ ARH14 !
6 *RH6 ! _ ARH15 !
7 ARH7 ! _ ARH16 !
8 ARH8 ! _ ARH17 !
9 ARH9 ! _ ARH18 !
10 ARH10 !Command ==> +______ !
Additional attributes ..* N +More--------------------------------+
|
A source field is selected by marking it in the left column or by positioning the cursor in the respective line and pressing ENTER. One field can be selected at a time.
The following topics are covered below:
The screen for the definition of superfields/descriptors for files of type Adabas, Conceptual and Standard looks as follows.
19:13:06 ***** P R E D I C T ***** 2007-05-31
- Modify Field -
Field ID ........ SUPER-1 Added 2007-05-31 at 13:39
File ID ......... PD-A2 Modified 2007-05-31 at 14:53
Ty L Field name F Cs Length Occ D U DB S NAT-l
-- - -------------------------------- *- * -------- ----- * * -- * -----
SP 1 SUPER-1 A 168.0 AJ N
Source field name * F Length Start End DB
1 STD-EL1 A 30.0 1 10 AA
2 STD-EL2 N 23.0 2 2 AB
3 STD-EL2 N 23.0 2 3 AB
4 LOGICAL B 10.0 AC
5 LOGICAL B 10.0 AC
6 MAIG A 50.0 BA
7 TIME T BB
8
Start/End: Relative byte position in source field. Default
. is first and last byte of source field
Additional attributes ..* N Associations ..* N Scroll to: __
|
See also General Rules for Defining Derived Fields.
Subfields/Descriptors for files of type Adabas, Conceptual and Standard are defined in the screen below.
09:13:28 ***** P R E D I C T ***** 2007-05-31
- Add a Field -
Field ID ........ PHON-4 Added 2007-05-31 at 09:11
File ID ......... HEB-FI by HNO
Ty L Field name F Cs Length Occ D U DB N NAT-l
-- - -------------------------------- -- - -------- ----- - - -- - -----
SB 1 PHON-4 SS N
Source field name * F Length Start End DB
1 HEB-TEST A 1.0 AF
Start/End: relative byte position in source field. Default
is first and last byte of source field.
Additional attributes ..* N Associations ..* N Scroll to: __
|
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 (see screen below).
13:51:16 ***** P R E D I C T ***** 2007-05-31
- Modify field -
Field ID ........ FELD5 Added 2007-05-31 at 13:15
File ID ......... HNO-STA1 Modified 2007-05-31 at 13:15
Ty L Field-name F Cs Length Occ D U DB N NAT-l
-- - -------------------------------- -- - -------- ----- - - -- - -----
SP FELD5 B 25.0
+-----------------------------------------------+
! Superdescriptor definition changed !
! to the correct format. !
! !
! old format .. B new format .. A !
! !
! Hit 'ENTER' to continue or change format. !
+-----------------------------------------------+
|
The format of a superfield/descriptor can be changed manually (with the Modify Field function). 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).
If changes to standard fields are rippled to derived fields in real files and userviews, two screens are displayed indicating this process of rippling.
In the first screen the changes of source fields are indicated.
13:51:35 ***** P R E D I C T ***** 2007-05-31
- Modify file - Page: 1
list of field updates
---------------------
FELD2 *** upd ***
FELD2 HNO-ADA1 *** upd ***
FELD5 HNO-ADA1 *** upd ***
FELD5 *** upd ***
|
In the second screen the changes of the format and/or length of derived fields are indicated.
SUB/SUPER/PHON/COLLATION- fields, -descriptors length are changed
-----------------------------------------------------------------
Ty Field name File name
-- -------------------------------- --------------------------------
SP FELD5 HNO-STA1 updated
|
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 a CHECK or
CAT command is entered in the field list editor or
when the Add/Copy/Modify field function is executed from the Field
Maintenance menu.
These validations can be executed differently:
If a source field is changed with the list editor (function Link
children in the Modify File menu with Related type set to EL), the validation
can be executed explicitly with the CHECK command.
The CAT command will also perform the
validation.
If a source field is changed with the function Modify Field, the validations are performed directly.
The following topics are covered below:
The screen 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.
Further information on sub/superfields/descriptors and phonetic descriptors can be found in the section ADACMP (COMPRESS-DECOMPRESS) in the Adabas Utilities documentation.
Note:
See also
General
Rules for Defining Derived Fields.
The screen for defining hyperdescriptors looks as follows:
13:00:05 ***** P R E D I C T ***** 2007-05-31
- Add a Field -
Field ID ........ FIELD3 Added 2007-05-31 at 12:59
File ID ......... DEMO by HNO
Ty L Field name F Length Occ D U DB N NAT-l
-- - -------------------------------- -- -------- ----- - - -- - -----
HY 1 FIELD3 A 20.0 D XZ N
User exit nr ...
Source field name * Source field name *
1 2
3 4
5 6
7 8
9 10
11 12
13 14
15 16
17 18
19 20
Additional attributes ..* N Associations ..* N
|
| Attributes | |
|---|---|
| User exit nr | A number between 1 and 31 identifying the user exit that defines the hyperdescriptor. See the Adabas User Exits documentation. |
The screen for defining collation descriptors looks as follows:
18:13:37 ***** P R E D I C T ***** 2006-04-20
- Add a Field -
Field ID ........ HNO_COLLATION Added 2006-04-20 at 18:13
File ID ......... HNO-ADA by HNO
Ty L Field name F Length Occ D U DB N NAT-l
-- - -------------------------------- -- -------- ----- - - -- - -----
OD 1 HNO_COLLATION D AB N
Source field ........*
User exit nr .........
HE option ............ (Y,N)
Locale string ........
Collation strength ..* (none)
Case first ..........* (none)
Alternate ...........* (none)
Case level ..........* (none)
French ..............* (none)
Normalization .......* (none)
X Additional attributes ..* N Associations ..* N |
| 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 string |
One of the locales supported by ICU. See the Administration section of the Adabas on Open Systems documentation for further information. |
| Collation 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 screen 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.
18:21:07 ***** P R E D I C T ***** 2011-11-24
- Modify Field -
Field ID ........ POLICY Modified 2011-11-21 at 17:13
File ID ......... SMR-POLICY by SMR
Ty L Field name F Length Occ D U DB N NAT-l
-- - -------------------------------- -- -------- ----- - - -- - -----
SP 1 POLICY D U AJ
Index Name .... SMR-POLICY
No Overlaps ... (Y/N)
Source field name * A/D/R/I Expression
1 COVERAGE A _
2 BUS_END A _
3 BUS_START A _
4 _
5 _
6 _
7 _
8 _
9 _
10 _
* Additional attributes ..* S Associations ..* N Scroll to ... __ |
| 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 name | 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: |
||||||||
| A/D/R/I |
|
||||||||
| Expression | Only applies when an index and if files of type D or MT. Mark the column Expression to display an editor for the expression. Up to 64 expressions can be maintained. An asterisk indicates that an expression exists. | ||||||||
If you mark the Expression field, the following editor is shown.
An index is composed of source field name(s) and/or expression(s). If the index contains at least one expression, the source field name(s) are saved as an expression also. If an index consists only of expressions that are reduced to source field names, then these source field names will be shown in the column labeled Source field name when the field is edited again.
16:16:04 ***** P R E D I C T ***** 2009-07-31
- Modify Field -
Field ID ........ EL_1_SP Added 2009-02-05 at 15:31
File ID ......... HEB-D Modified 2009-07-11 at 07:59
Ty L Field name F Length Occ D U DB N NAT-l
-- - -------------------------------- -- -------- ----- - - -- - -----
SP 1 EL_1_SP D AI
**** ************************** Expression 1 *********************************
0001 SUBSTR (EL_INDEX,1,2)
0002
0003
0004
0005
0006
0007
0008
0009
0010
Command ===>
**** ************************************************************************** |
Enter Help in the command line or
press PF1 to display the editor help:
16:16:04 ***** P R E D I C T ***** 2009-07-31
- Modify Field -
Field ID ........ EL_1_SP Added 2009-02-05 at 15:31
File ID ......... HEB-D Modified 2009-07-11 at 07:59
Ty L Field name F Length Occ D U DB N NAT-l
-- - -------------------------------- -- -------- ----- - - -- - -----
SP 1 EL_1_SP D AI
+--------------------------------- Commands ----------------------------------+
! Top or T or -- Position to top PF1 Help !
! Bottom or B or ++ Position to bottom PF3 Exit !
! +P or + Position forward one page PF5 Sel !
! -P or - Position backward one page PF6 -- !
! +nn Position forward 'nn' lines PF7 - !
! -nn Position backward 'nn' lines PF8 + !
! nn Position to line 'nn' PF9 ++ !
! !
! Save or Cat Save Changes Exit or E or . Exit from editor !
! Check or C Check Change Sel Select field !
! !
+-----------------------------------------------------------------------------+ |
VSAM superdescriptors (Field type SP) in a file of type V (physical VSAM) and L (logical VSAM) are defined in the following screen.
13:45:57 ***** P R E D I C T ***** 2007-05-31
- Modify Field -
Field ID ........ KEY Added 2002-07-05 at 13:01
File ID ......... PD-V1 Modified 2007-05-31 at 13:42
Ty L Field name F Cs Length Occ D U DB N NAT-l
-- - -------------------------------- -- - -------- ----- - - -- - -----
SP 1 KEY 30.0 A AE N
Source field name * F Length Start End DB
1
Start/End: relative byte position in source field. Default
is first and last byte of source field.
Additional attributes ..* N Associations ..* N Scroll to: __
|
| 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), a window is displayed for defining additional attributes: upgrade flag, sort flag, null flag and DD name (see below).
13:44:37 ***** P R E D I C T ***** 2007-05-31
- Modify Field -
Field ID ........ KEY Added 2002-07-05 at 13:01
File ID ......... PD-V1 Modified 2007-05-31 at 13:42
Keys .. Zoom: N
Ty L Field name F Length Occ D U DB N NAT-l
*- - -------------------------------- *- -------- ----- * * -- * -----
1 KEY A 30.0 A AE N
+- VSAM descriptor attributes -+
Natural attributes ! !
Header1 .... ! Upgrade flag ... Y (Y,N) !
Header2 .... ! Sort flag ...... N (Y,N) !
Header3 .... ! Null flag ...... N (Y,N) !
Edit mask .. ! DD name ........ DDNAME !
Comments Zoom: N ! !
+------------------------------+
Additional attributes ..* N Associations ..* N
|
| 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. | ||||