Relationships screen
The Relationships screen shown in the following figure is displayed after you
- Specified the related objects in the Add Relationship screen.
- Used the S (Select) line command on the Application Relationship Detail screen.
The screen title varies depending on the object types involved.
The following table shows the possible title combinations.
Relationship Titles
Object Type Parent | Object Type Dependent | Title |
|---|---|---|
DB2 | DB2 | Column Relationships |
DB2 | MVS | Column/Field Relationships |
DB2 | KEY | Column/Segment Relationships |
MVS | MVS | Field Relationships |
MVS | DB2 | Field/Column Relationships |
MVS | KEY | Field/Segment Relationships |
Relationships Screen - Column Relationships not yet defined
Command ===> SCROLL ===> PAGE
Primary Command: CONDitional relationships, SUGgest Relationships
Line Commands: Column Number to Define Relationship D = Delete Relationship
Parent: DB2-LOCATION.TSOID01.EMP
Dependent: DB2-LOCATION.TSOID01.ORDKFL
CMD Parent Columns Type(Length) Mapped Fields
---- ------------------------------- ------------ -----------------------------
____ EMPNO CHAR(6)
____ FIRSTNME VCHAR(12)
____ MIDINIT CHAR(1)
____ LASTNAME VCHAR(15)
____ WORKDEPT CHAR(3)
____ PHONENO CHAR(4)
____ HIREDATE DATE
NMBR Dependent Columns Type(Length)
---- ------------------------------- ------------
1 ORDER_NUMBER CHAR(6)
2 CUST_NUM CHAR(6)
3 ORD_TYPE CHAR(8)
4 ORD_DATE DATE
5 ORD_STAT CHAR(8)
6 ORD_AMOUNT DEC(9,2)
The above figure shows the Relationships screen as it appears when adding a DB2 to DB2 application relationship, before any column relationships have been defined or suggested.
The Relationships screen shown in the following figure is displayed when maintaining an MVS to MVS application relationship, with field relationships already defined.
Relationships Screen — Field Relationships already defined
Command ===> SCROLL ===> PAGE
Primary Command: CONDitional relationships, SUGgest Relationships, CANcel
Line Commands: Field Number to Define Relationship D = Delete Relationship
Parent: MVS-TSOID01.FARDX2.SAMP.SALEHIST
Dependent: MVS-TSOID01.FARDX2.SAMP.ZIPCODE
CMD Parent fields Type(Length) Mapped fields
---- ------------------------------- ------------ -----------------------------
____ FILLER CHAR(15) 6(1,11,1)
____ SH-DATE-SHIP CHAR(8) 3(1,4,1)
- - - - - - - - - End of Related Columns - - - - - - - - -
____ SH-CUST-NO CHAR(6)
____ SH-ORD-NO CHAR(6)
____ SH-ORD_TYPE CHAR(8)
____ SH-DATE-SHIP CHAR(8)
____ SH-SHIP-CUST-NO CHAR(6)
NMBR Dependent fields Type(Length)
---- ------------------------------- ------------
1 ZM-BASE-ZIP CHAR(5)
2 ZM-ZIP-SUF CHAR(4)
3 ZM-TIME-DIFF DEC(2,0)
The figure above shows the Relationships screen as it appears when modifying an MVS to MVS application relationship. Notice that the titles now include the MVS naming convention where Parent Fields and Dependent Fields are used in place of Db2 columns. Also, the Parent Portion of the screen includes another scrollable area — Relationships — that lists the defined relationships.
The Relationships screen shown in the following figure is displayed when maintaining an MVS to DB2 application relationship, with Unicode data types and field/column relationships already defined.
Relationships Screen — with Unicode data
-
Command ===> SCROLL ===> PAGE
Primary Command: CONDitional relationships, SUGgest Relationships, CANcel
Line Commands: Column Number to Define Relationship D = Delete Relationship
Parent: MVS-TSOID01.FASAMP.EMPLOYU2
Dependent: DB2-LOCATION.TSOID01.UNI_CUSTOMER_TABLE
CMD Parent fields Type(Length) Mapped fields
---- ------------------------------- ------------ -----------------------------
____ 24/OUT-POSTAL-CODE N(5) U16 15(1,5,1)/CONTACT_ZIP
- - - - - - - - - End of Related Columns - - - - - - - - -
____ OUT-STREET-ADDRESS N(25) U16
____ FILLER N(1) U16
____ OUT-CITY N(15) U16
____ OUT-STATE N(2) U16
____ FILLER N(2) U16
NMBR Dependent Columns Type(Length)
---- ------------------------------- ------------
14 CONTACT_STATE CH(2) U-8
15 CONTACT_ZIP CH(9) U-8
16 CONTACT_COUNTRY CH(10) U-8
17 CONTACT_AREA_CD CH(3) U-8
18 CONTACT_TELEPHONE CH(7) U-8
Use the relationship screen to create or update the column or field level relationship detail for the related objects in the relationship file. For more information about how to use the Relationships screen, see Building Column/Field Relationships.
The body of the Relationships screen consists of two portions: a parent portion and a dependent portion. The parent portion includes two scrollable areas, Defined Relationships and Parent Columns/Fields. When the parent and dependent portions contain more information than can be displayed at one time, use the scroll commands to scroll through the data. Each area can also be scrolled individually with scroll mode CSR: move the cursor into the area you want to scroll and use the UP or DOWN scroll commands.
Object Names
The top portion of this screen displays the object names for the relationship:
Parent
Displays the full name of the parent object preceded by the object type. For example, Db2 tables are identified in the form location.creator.table.
Dependent
Displays the full name of the dependent object preceded by the object type. For example,
- Db2 tables are identified in the form
Db2-location.creator.table.
- Db2 tables are identified in the form
- MVS files are identified in the form
MVS-hlq.dataset(member).
- MVS files are identified in the form
- IMS root segments are identified in the form
KEY-hlq.DBD library dataset(member).
- IMS root segments are identified in the form
- IMS segment layouts are identified in the form
KEY-hlq.segment layout dataset(member).
- IMS segment layouts are identified in the form
- Output Key File layouts are identified in the form
KEY-hlq.record layout dataset(member).
- Output Key File layouts are identified in the form
Parent Portion
The Parent portion of this screen represents the parent information for the relationship.
If relationships have already been defined, the Parent portion is divided into two scrollable areas by a dashed line, for example
The part above the dashed line contains the names of the columns/fields participating in the application relationship with the Mapped Fields values filled in. The part below the dashed line contains the names of all of the columns/fields for the parent.
CMD
Enter line commands in this column. For information about valid commands, see Line Commands. This column displays the ERR> flag when you select an existing relationship from the Application Relationship Detail screen in the following situations:
- When File-AID/RDX cannot find a column or field that is defined as part of an application relationship. You should delete such a relationship before you exit from this screen.
- When there is a conflict between column information stored in the relationship file and column information stored in the Db2 catalog table, SYSIBM.SYSCOLS.
Parent Columns or Parent Fields
Displays the names of the columns in the Db2 parent table, or, if the parent is an MVS file, displays the names of the fields. If the field contains an occurrence of subscripts, the element notation follows in parentheses.
Type(Length)
Displays the parent column’s or parent field’s data type. The length of the data is displayed in parentheses immediately following the data type for CHAR, CH, VCHAR, VC, GRPH, VGRPH, DEC, FIXPIC, National data types only.
Unicode Db2 character data types are identified as CH(x), VC(x) or LV(x) with a suffix of U16 or U-8. Unicode character data from an MVS file is identified as N(x) U-16. See Relationships Screen — with Unicode data for a sample screen.
See the following table for more information about data types displayed in this column.
Data Type Values
Data Type | Value |
|---|---|
CHAR or CH | Fixed-length character strings less than or equal to 254 characters. |
VCHAR or VC | Variable-length character strings less than or equal to the field length specified when the column is created. Maximum allowable field length is 4056 characters for 4k pages and 32,714 characters for 32k pages. |
LVCHAR or LV | Variable-length character strings less than or equal to 4056 characters for 4k pages and 32,714 characters for 32k pages. |
GRPH | Fixed-length graphic strings less than or equal to 127 characters. |
VGRPH | Variable-length graphic strings less than or equal to the field length specified when the column is created. Maximum allowable field length is 2028 characters for 4k pages and 16,357 characters for 32k pages. |
LVGRPH | Variable-length graphic strings less than or equal to 2028 characters for 4k pages and 16,357 characters for 32k pages. |
SMALLINT | Fixed-length, binary integers in the range of -32,768 to +32,767, requiring two bytes of storage. |
INTEGER | Fixed-length, binary integers in the range of -2,146,483,648 to +2,147,483,467, requiring four bytes of storage. |
BIGINT | Fixed-length, binary integers in the range of -9223372036854775808 to 9223372036854775807, requiring eight bytes of storage. |
DEC | Fixed-length, decimal numbers in the range of -10E15 to +10E15. |
DECFLOAT | Decimal Floating Point can be either DECFLOAT(16) or DECFLOAT(34) representing either 16 or 34 significant digits. The range of a DECFLOAT(16) number is 10-383 to 10+384 and the range of a DECFLOAT(34) number is 10-6143 to 10+6144. A DECFLOAT(16) field occupies 8 bytes and a DECFLOAT(34) occupies 16 bytes. |
REAL | Fixed-length, single-precision, floating-point numbers in the approximate range of 5.4E-79 to 7.2E+75. |
DOUBLE | Fixed-length, double-precision, floating-point numbers in the approximate range of 5.4E-79 to 7.2E+75. |
DATE | A three-part value (year, month, day) whose format is determined by DB2. |
TIME | A three-part value (hour, minute, second) whose format is determined by DB2. |
TMSTMP(n) | TIMESTAMP: A seven-part value (year, month, day, hour, minute, second, and precision) whose format is YYYY-MM-DD-HH.MM.SS.TTTTTTTTTTTT. The precision value (TTTTTTTTTTTT) is variable length and has 0 to 12 digits (up to picosecond) with a default of 6 (microsecond). TMSTMP(n) specifies the scale (length) with n = 0 through 12. For DB2 Versions prior to Version 10, the length is always 6. |
TMESTZ(n) | TIMESTAMP WITH TIME ZONE: An eight-part value (year, month, day, hour, minute, second, precision, time zone) whose format is YYYY-MM-DD-HH.MM.SS.TTTTTTTTTTTT±HH:MM. The precision value (TTTTTTTTTTTT) is variable length and has 0 to 12 digits (up to picosecond) with a default of 6 (microsecond). The time zone is the difference in hours and minutes between local time and UTC. The range of the hour offset is -12 to +14, and the minute offset is 00 to 59. The time zone is specified in the format ±th.tm, with values ranging from -12.59 to +14.00. TMESTZ(n) specifies the scale (length) with n = 0 through 12. DB2 Version 10 and higher only. |
CLOB | A character large object (CLOB) is a varying-length string with a maximum length of 2 147 483 647 bytes (2 gigabytes minus 1 byte). |
DBCLOB | A double-byte character large object (DBCLOB) is a varying-length string with a maximum length of 1 073 741 823 double-byte characters. |
XML | A data type for storage of well formed XML documents. |
BLOB | A binary large object (BLOB) is a varying-length string with a maximum length of 2 147 483 647 bytes (2 gigabytes minus 1 byte). |
BINARY | BINARY is a fixed length binary string which extends the support for binary strings beyond BLOBs. Length attribute must between 1 and 255 inclusive. |
VARBINARY | VARBINARY is a variable length binary string which extends the support for binary strings beyond BLOBs. Length attribute must between 1 and 32704. |
ROWID | Row identifier. A value that uniquely identifies a row. If a table contains a large object it must also have a ROWID column. |
HEXADEC | Fixed length string, supported as a string only. |
FLTPIC | Fixed length character string representing a floating point value. |
FIXPIC | Fixed length character string representing a decimal or integer value. |
N (National) | Unicode data from an MVS file in COBOL is identified as PIC N or USAGE NATIONAL. This is UTF-16, CCSID=1200. Identified as N(x) U-16. |
The following table identifies the allowable column/field type mapping when building a column relationship.
Column/Field Types Mapping
TO → | CHAR or CH | VCHAR or VC | LVCHARor LV | GRPH | VGRPH | LVGRPH | SMALLINT | INTEGER | BIGINT | DEC | REAL | DOUBLE | DECFLOAT | DATE | TIME | TMSTMP | TIMESTZ | BINARY | HEXADEC | FLTPIC | FIXPIC | National |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
CHAR or CH | X | X | X | X | X | X | X | X | X | X | X | X | X | X | X | X | X | |||||
VCHAR or VC | X | X | X | X | X | X | X | X | X | X | X | X | X | X | X | |||||||
LVCHAR or LV | X | X | X | X | X | X | X | X | X | X | X | X | X | X | X | |||||||
GRPH | X | |||||||||||||||||||||
VGRPH | X | |||||||||||||||||||||
LVGRPH | X | |||||||||||||||||||||
SMALLINT | X | X | X | X | X | X | X | X | X | |||||||||||||
INTEGER | X | X | X | X | X | X | X | X | X | |||||||||||||
BIGINT | X | X | X | X | X | X | X | |||||||||||||||
DEC | X | X | X | X | X | X | X | X | X | |||||||||||||
REAL | X | X | X | X | X | X | ||||||||||||||||
DOUBLE | X | X | X | X | X | X | ||||||||||||||||
DECFLOAT | X | X | X | X | ||||||||||||||||||
DATE | X | X | X | X | ||||||||||||||||||
TIME | X | X | X | X | ||||||||||||||||||
TMSTMP | X | X | X | X | X | |||||||||||||||||
TMESTZ | X | X | X | X | X | |||||||||||||||||
BINARY | X | |||||||||||||||||||||
HEXADEC | X | X | X | X | ||||||||||||||||||
FLTPIC | X | |||||||||||||||||||||
FIXPIC | X | X | X | X | X | X | X | X | X | |||||||||||||
N (National) | X | X | X | X | X | X | X | X | X | X | X | X |
Mapped Fields
Displays a value only for already defined relationships in the Parent portion. It lists
- the column/field number of the dependent column/field,
- the extended column information in parentheses (parent start position, length, dependent start position, length) where applicable
- the dependent column/field name.
* AR Conditional *
The Mapped Fields heading is replaced with the * AR Conditional * heading after you have defined a conditional application relationship with the COND command. The column displays a value only for already defined relationships in the Parent portion. It lists the same information as the Mapped Fields column:
- the column/field number of the dependent column/field,
- the extended column information in parentheses (parent start position, length, dependent start position, length) where applicable
- the dependent column/field name.
Dependent Portion:
The Dependent portion of the Relationships screen contains the following information:
NMBR
Displays File-AID/RDX-generated sequence numbers that indicate the dependent column/field/segment numbers to be used as input in the above parent CMD field to establish a new relationship. This number also correlates to the dependent column/field/segment number in the Mapped Fields values for defined relationships.
Dependent Columns/Fields/Root Segment
Displays the names of the Db2 columns, MVS layout fields, IMS root segment, or KEY File layout fields in the dependent object. If an MVS field contains an occurrence of subscripts, the element notations follows in parenthesis.
Type(Length)
Displays the dependent column’s or field’s data type. The length of the data is displayed in parenthesis immediately following the data type for CHAR, CH, VCHAR, VC, GRPH, VGRPH, DEC, FIXPIC, National data types only.
Unicode Db2 character data types are identified as CH(x), VC(x) or LV(x) with a suffix of U16 or U-8. Unicode character data from an MVS file is identified as N(x) U-16. See Relationships Screen — with Unicode data for a sample screen.
For more information about data types displayed in this column, see Data Type Values. Column/Field Types Mapping identifies the allowable column/field type mapping when building a column relationship.
Line Commands
The following File-AID/RDX-specific line commands are valid on the Relationships screen:
number
Establishes an application relationship (AR) between a parent column/field and a dependent column/field/root segment.
The panel names the number line command according to the object type of the dependent:
Column-Number | when the dependent is a Db2 table. |
Field-Number | when the dependent is an MVS file or IMS segment layout. |
Root Segment-Number | when the dependent is an IMS database. |
Enter a File-AID/RDX-generated column/field/segment number (listed in the dependent NMBR column) in the parent CMD column next to the parent column/field for which you want to establish a relationship.
For example, Relationships Screen — Creating a DB2 to MVS Relationship a Field-Number line command (1) has been entered to relate the dependent field, SH-CUST-NUM to the parent column, CUSTOMER_NUMBER. When you press Enter, the new relationship is established and the Column/Field Relationships screen is redisplayed with the new relationship above the dashed line as shown in Relationships Screen — New Db2 Column to MVS Field Relationship Established.
D (Delete)
Enter D in the CMD column to remove an existing column/field relationship from the application relationship.
Primary Commands
The following File-AID/RDX-specific primary commands are valid on the Relationships screen:
CANCEL (CAN)
Quits the Relationship Definition without saving any changes.
COND
Displays the Relationship Condition Definition panel where you can define a conditional test for the current application relationship (AR). You must have already established a relationship (mapped columns/fields) before you can issue the COND command.
SUGGEST (SUG)
Enter this command and File-AID/RDX will suggest a relationship when the parent and dependent column or field name, type and length are identical. Same as if you had entered the column or field number(s) manually for all identical column or field matches. You only need to enter the SUGGEST command when you didn’t enable it in the previous “Add Relationships Screen” or when you want File-AID/RDX to suggest matching pairs again.
Use the D line command to delete any suggested relationships you don’t want included. Use the CANCEL primary command to abort the relationship definition.
This section provides information about the following topics: