Creating a new relationship
You can dynamically create a new application relationship in the following ways:
- Enter REDIT ADD in the Command field of the last opened window.
- Enter ADD in the Command field of the following windows:
- No Relationships Exist window (No Relationships Exist Window)
- Relationship Listing window (Relationship Listing Window)
- Related Table List window (Related Table List Window ).
After specifying that you want to add a new relationship, the Add Application Relationships screen displays (see the following figure).
Add Application Relationships screen Invoked by ADD Command
The creator and table name of the parent table is prefilled with values from the last opened table. Enter the dependent creator and table name for which you want to define a relationship. You can use a wildcard character in either field to display a selection list of tables. See Wildcard-Characters for more information. Enter a description of the relationship in the description field. A maximum of 128 characters is allowed.
Press Enter on this screen to bring up the Column Relationships screen (see the following figure).
Column Relationships screen
Building Column Relationships
To create application relationships, you create column relationships on the Column Relationships screen. An application relationship can consist of more than one column relationship. The fields in the lower portion of the Column Relationships screen comprise two lists. These lists contain names, data types, and lengths of columns from the parent and dependent tables specified in the upper portion of the screen. Use these lists to build column relationships.
The following procedure gives the steps to build column relationships.
- Verify the parent and dependent table creator and table name fields. These fields are prefilled with information from the previous screen.
- Verify or enter a description for the relationship.
- Decide which parent and dependent columns you want to be related. This relationship should reflect the logic in the application program that uses these tables.
- Enter the number from the COLUMN NUMBER field that corresponds to the dependent column in the NMBR column next to the name of the parent column. One of the following must be true of any pair of corresponding parent and dependent columns:
- Parent and dependent columns contain the same data type and are the same length.
- One of the pair is INTEGER or SMALLINT data and the other is CHAR. In this case, the column lengths need not match.
Both parent and dependent columns contain character (CHAR or VCHAR) data or graphic (GRPH or, VGRPH) data. In this case, the columns need not be the same length. When you map a column containing character or graphic data to another column of the same data type, the Extended Column Relationship Definition pop-up window displays. The Extended Column Relationship Definition window enables you to create column relationships using partial columns. See Extended Column Relationship Definition for more information about partial column relationships.
Otherwise, File-AID for Db2 displays an error message and does not establish the relationship.
For example: the following figure shows how to relate the DEPT table to the EMP table. To relate the WORKDEPT column from the EMP table to the DEPTNO column from the DEPT enter a 5 in the CMD field next to the DEPTNO column. Both of these columns have the same column type and length: CHAR(3).
Column Relationships screen—Creating a new column relationship
Press Enter.
As the parent and dependent columns both contain CHAR data, the Extended Column Relationship Definition pop-up window displays (see the following figure). Press Enter to accept the full-column relationship or change the prefilled start positions and length values for a partial-column relationship.
Extended Column Relationship Definition Window
The Column Relationships screen now displays the defined related columns in the upper part of the columns listing. If one column contains INTEGER (or SMALLINT) data and the other contains CHAR data or both columns contain the same data type and are the same length, the column relationship is established. File-AID for Db2 positions the names of the parent and dependent columns in the column relationship you just created to the top of their respective lists and above the dashed line (see the following figure). In addition, the number that appeared in the COLUMN NUMBER column next to the dependent column is replaced by an arrow that points from the parent column to the dependent column and the remaining column numbers are renumbered. The message Column Added displays in the upper left corner.
Column Relationships screen — New Column Relationship Established
Repeat steps 3-5 for all columns that you want to include in the application relationship.
Once the relationship is defined, enter END on this screen to begin the Related Table Edit session (see the following figure). The relationship definition is saved into the relationship file when you exit the Column Relationships screen.
Related Table Edit Display — DEPT to EMP Tables
Extended Column Relationship Definition
The Extended Column Relationship Definition pop-up window (see the following figure) displays when you attempt to map two columns containing character (CHAR or VCHAR) or graphic (GRPH or VGRPH) data.
Extended Column Relationship Definition Window
Use this window to create partial-column relationships.
If the columns you want to map are the same length, the fields in this window are
prefilled and the following message displays:
column relationship definition
Complete this window and press Enter to proceed.
Parent Column
Number | Displays the number of the parent column. |
Name | Displays the name of the parent column. |
Type(Length) | Displays the type of data contained in the parent column. The length of the parent column in parentheses follows the data type. |
Start Position | Specify the starting position of the string in the parent column that will comprise the column relationship. |
Length | Specify the length of the string contained in the parent column on which the column relationship is based. |
Dependent Column
Number | Displays the number of the dependent column. |
Name | Displays the name of the dependent column. |
Type(Length) | Displays the type of data contained in the dependent column. The length of the dependent column in parentheses follows the data type. |
Start Position | Specify the starting position of the string in the dependent column that will comprise the column relationship. Length of the dependent column is determined by the length of the string in the corresponding parent column. |
Extended Column Relationships Examples
The following describes several situations in which the Extended Column Relationship Definition window displays.
Example 1
Assume you have entered a column-number line command (as illustrated in see the following figure) to create a column relationship between two column with data types of CHAR and lengths of 6.
Column Relationships screen with column-number command
The Extended Column Relationship Definition pop-up window displays (see the following figure).
Extended Column Relationship Definition Window
column relationship definition
Example 2
Assume you have entered a column-number line command (see the following figure) to create a column relationship between two column with data types of CHAR and different lengths.
Column Relationships screen with column-number command
The Extended Column Relationship Definition pop-up window displays (see the following figure).
Extended Column Relationship Definition Window
The values entered in the window (Extended Column Relationship Definition Window) indicate that the first two characters in the parent column and the last two characters in the dependent column comprise the column relationship.
Related topics