Extended Column Relationship Definition

The Extended Column Relationship Definition pop-up window shown in the following figure is displayed when you attempt to map two columns/fields containing character (CHAR or VCHAR) or graphic (GRPH or VGRPH) data, or one column/field containing DECIMAL and its parent or dependent CHAR data.

Extended Column Relationship Definition Window

Use this window to create partial-column/field relationships.

If the columns you want to map are the same length, the fields in this window are prefilled and the following message is displayed:

Complete this window and press Enter to proceed.

The left side of the body of this window contains fields that pertain to the
parent column/field:

Number

Displays the number of the parent column/field.

Name

Displays the name of the parent column/field.

Type(Length)

Displays the type of data contained in the parent column/field. The length, in parentheses, follows the data type. If an MVS field includes an occurrence of subscripts, the ARRAY SIZE and MAX LENGTH values are also displayed, see Extended Field Relationship Definition with Field Subscripts Defined for an example.

Start Position

Specify the starting position of the string in the parent column/field that will comprise the column/field relationship.

For DECIMAL columns/fields the Start Position value cannot be changed.

Length

Specify the length of the string contained in the parent column/field on which the column/field relationship will be based.

For DECIMAL data types the Length value represents its internal byte length and cannot be changed.

The right side of the body of this window contains fields that pertain to the dependent column/field:

Number

Displays the number of the dependent column/field.

Name

Displays the name of the dependent column/field.

Type(Length)

Displays the type of data contained in the dependent column/field. The length, in parentheses, follows the data type. If an MVS field includes an occurrence of subscripts, the ARRAY SIZE and MAX LENGTH values are also displayed, see Extended Field Relationship Definition with Field Subscripts Defined for an example.

Start Position

Specify the starting position of the string in the dependent column/field that will comprise the column/field relationship. If the right side does not display a Length field, the length of the dependent column/field is determined by the length of the string in the corresponding parent column/field.

For DECIMAL columns the Start Position value cannot be changed.

Length

(displays only for DECIMAL to CHAR relationships) Specify the length of the converted decimal string in the dependent character column/field. The maximum length of a DECIMAL to CHAR conversion is “precision + sign + decimal delimiter”. For example, maximum CHAR length for DEC(9,2) is 11 (9 + 1 + 1).

Rules for DECIMAL/FIXPIC and CHARACTER Columns/Fields

File-AID handles DECIMAL and FIXPIC fields similarly when mapping or converting to or from CHARACTER fields. Exceptions are noted in the following rules:

Mapping a DECIMAL/FIXPIC to a character column/field:

1. The DECIMAL/FIXPIC column/field is always mapped in its entirety. Start position and length cannot be modified.
2. The CHARACTER column/field start position and length can be changed but cannot exceed the maximum displayable length of the DECIMAL/FIXPIC column/field. For example, if decimal parent format is DEC(9,2), the maximum map length of the dependent character column/field is 11 (9 plus 1 for the decimal point plus 1 for a possible minus sign).

Mapping a CHARACTER to a DECIMAL/FIXPIC column/field:

1. The DECIMAL/FIXPIC column/field is always mapped in its entirety. Start position and length cannot be modified.
2. The CHARACTER column/field start position and length can be changed but cannot exceed the maximum displayable length of the DECIMAL/FIXPIC column/field. For example, if DECIMAL/FIXPIC parent format is DEC(9,2), the maximum map length of the dependent CHARACTER column/field is 11 (9 plus 1 for the decimal point plus 1 for a possible minus sign).
3. The default parent CHARACTER column/field start position is 1.

Converting a DECIMAL/FIXPIC to a CHARACTER column/field:

1. The string values derived from DECIMAL/FIXPIC column values have these characteristics:
   • Leading zeroes are suppressed

   • Values less than 1:

     DECIMAL: For values less than 1, a leading zero is inserted before the decimal point (or comma), for example 0.25.

     FIXPIC: For values less than 1, the leading zero is dropped before the decimal point (or comma), for example .25.

   • For values less than 0, a minus sign is the left most character in the returned string

   • Values greater than 0:

     DECIMAL: For values greater than 0, a plus sign is not returned

     FIXPIC: For values greater than 0, if signed, a plus sign is returned; if not signed, a plus sign is not returned

   • Special characters, such as monetary symbols, are not returned
   • Where the scale of the DECIMAL/FIXPIC column/field is zero, for example DEC(5,0), no decimal point is in the returned string
2. DECIMAL only: Where the dependent CHARACTER column/field map length is less than the returned character string, truncation occurs on the left of the returned string.

Converting a CHARACTER to a DECIMAL/FIXPIC column/field:

The string values to be converted to decimal values must comply with these constraints:

• The minus or plus sign must be the left most character of the string
• The placement of the decimal point in the string is interpreted, but the scale of the decimal value is derived from the DECIMAL column/field definition. If, for example, the decimal definition is DEC(9,2) and the string to be converted is 99.999, the last decimal position is truncated. Only applies to FIXPIC if last digit is zero (0).
• If the string does not contain a decimal point and the scale is greater than zero, zeroes will be put into the decimal positions of the decimal value. For example, character string 1234 would be converted to 1234.000 for a DEC(7,3) column.
• The character string cannot contain values other than numeric values, sign or decimal delimiter (point or comma)

Extended Column Relationships Examples

The following describes several situations in which the Extended Column Relationship Definition window is displayed.

Example 1

Assume you have entered a column-number line command (as illustrated in the following figure) to create a DB2 to DB2 column relationship between two columns with data types of CHAR and lengths of 6.

Relationship Screen with column-number command

The Extended Column Relationship Definition pop-up window would be displayed as shown in the following figure with default values for starting position and length prefilled.

Extended Column Relationship Definition Window

Example 2

Assume you have entered a column-number line command (as illustrated in the following figure) to create a DB2 to KEY relationship between columns with data types of CHAR and different lengths.

Column/Segment Relationships Screen with Segment-Number command

The Extended Column Relationship Definition pop-up window would be displayed as shown in the following figure with default values for starting position and length prefilled.

Extended Column Relationship Definition Window

Change the Dependent Root Segment’s Start Position to 5 and the Parent Column Length to 2. The following figure indicates that the first two characters in the parent column and the last two characters in the dependent root segment will make up the column/segment relationship.

Changed Dependent Start Position and Length

When you press Enter the Column/Segment Relationship screen reflects the desired relationship (see the following figure).

Partial Column/Segment Relation Established

Now the Mapped Fields value includes the extended column relationship information in parentheses (1,2,5) where 1 is the parent starting position, 2 is the length, and 5 is the dependent starting position.

Example 3

Assume you have entered a Field-Number line command (as illustrated in the following figure) to create a DB2 to MVS relationship between a column with data type of DECIMAL and a field with data type CHAR.

Column/Field Relationships Screen Mapping DECIMAL to CHAR

The Extended Column Relationship Definition pop-up window would be displayed as shown in the following figure with default values for starting position and length prefilled:

Extended Column Relationship Definition Window with DECIMAL to CHAR

The values entered in the window in the following figure indicate that the complete parent decimal column and the last eleven characters in the dependent column (starting position 10, length 11) will make up the column/field relationship.

Extended Column Relationship Definition Window after Modification

Press ENTER to complete the extended relationship definition.

Specify MVS Field Subscripts

The Specify MVS Field Subscripts pop-up window shown in the following figure is displayed when you attempt to map two fields where one or both fields contain an occurrence of subscript. You then specify the bounds of the array for each subscript. File-AID for Db2 supports a maximum occurrence of a three dimensional array.

Specify MVS Field Subscripts for Parent Field

This window appears only if the selected parent field contains subscripts. Use it to specify the desired value for up to three subscripts, where applicable.

Parent

Displays the name of the parent file.

Field Name

Displays the name of the parent field to be mapped.

Data Type

Displays the type of data contained in the field. The length, in parentheses, follows the data type.

Subscript 1

Specify the starting position of substring 1 in the parent field that will comprise the column/field relationship.

Subscript 2

Specify the starting position of substring 2 in the parent field that will comprise the column/field relationship.

Subscript 3

Specify the starting position of substring 3 in the parent field that will comprise the column/field relationship.

After you press Enter, you continue either with the Dependent Field Subscripts window (see the following figure), if the dependent field contains subscripts, or the Extended Field Relationship Definition window (Extended Field Relationship Definition with Field Subscripts Defined), if the parent and dependent field types qualify for Extended Field Relationship Definition, or return directly to the Field/Column Relationship screen (see Extended Field Relationship with Defined Subscripted Relationship) when the relationship definition is completed.

Specify MVS Field Subscripts for Dependent Field

Use this window to specify the desired value for up to three subscripts, if applicable, for the dependent field.

Dependent

Displays the name of the dependent file.

Field Name

Displays the name of the dependent field to be mapped.

Data Type

Displays the type of data contained in the field. The length, in parentheses, follows the data type.

Subscript 1

Specify the starting position of substring 1 in the parent or dependent field that will comprise the column/field relationship.

Subscript 2

Specify the starting position of substring 2 in the parent or dependent field that will comprise the column/field relationship.

Subscript 3

Specify the starting position of substring 3 in the parent or dependent field that will comprise the column/field relationship.

After you press Enter, you continue either with the Extended Field Relationship Definition window (see the following figure), if the parent and dependent field types qualify for Extended Field Relationship Definition, or return directly to the Field/Column Relationship screen (see Extended Field Relationship with Defined Subscripted Relationship) when the relationship definition is completed.

Extended Field Relationship Definition with Field Subscripts Defined

See Extended Column Relationship Definition vBase for a description of the fields. Note that the Type(Length) field now also includes the array size and maximum length of the related elements.

Upon completion, File-AID for Db2 displays the defined relationship in the Field Relationships screen as shown in the following figure.

Extended Field Relationship with Defined Subscripted Relationship

See Relationships for a description of the fields. Note that the element notation for the parent is enclosed in parentheses following the parent field name, while the element notation for the dependent is enclosed in parentheses following the dependent name in the Mapped Fields area.