Relationship File Contents
The relationship file (relationship repository) is a VSAM-KSDS file in which File-AID stores the following types of information:
Db2 Referential Integrity (RI) Relationships
This relationship is one in which the dependent table rows contain foreign keys, the values of which are the primary index of the parent table. The rules that define RI relationships are enforced by Db2.
Use Option 5, LOAD RI from the DB2 Relationship Facilitator menu (Option 1, RELATIONSHIPS) for an online load or update of the RI information in the Relationship File. For more information, see DB2 RI Update Online Options .
To load or refresh RI information from the Db2 catalog with a batch job, use Option L, Batch RI Load, from the DB2 Relationship Facilitator menu (Option 1, RELATIONSHIPS) For more information, see Batch-RI-Load .
Application Relationships (AR)
This relationship is one in which values from dependent MVS file fields, IMS database root segments, or Db2 table or view columns match the values in corresponding parent MVS file fields or Db2 table or view columns. The rules that define application relationships are enforced by user applications, not by Db2 or other databases.
To define an application relationship File-AID for Db2 needs to know the parent and dependent object names along with the names of the related column(s) or field(s). File-AID for Db2 can assist you with the application relationship definition through its SUGGEST feature: File-AID for Db2 will suggest relationships when column or field name, type, and length in the parent and dependent objects are identical.
When defining an application relationship for an MVS object, you must always identify the specific record layout or XREF information that defines the related field within the file.
A relationship can also be established from a Db2 or MVS parent to an Output Key file which contains a record layout, an IMS segment or both. The layout file or IMS segment is considered the dependent object. You define relationships for each field that is to be included in the extract Output Key file. The layout will determine the location and format of each field within the Output Key file. When the extract request is executed the Output Key file will be populated based upon the key values for the KEY relationship in the related extract.
The Output Key File can then be used by File-AID/RDX as an input (external) keys file against a driving object in another extract or as input into another process of your choosing. When the relationship is to an IMS segment, use the Output Key File with File-AID for IMS to extract IMS data.
In addition, you can define relationships in File-AID/RDX and store them in the same relationship file you use with File-AID for Db2. You can view and modify these relationships using File-AID for Db2 Option 6. File-AID for Db2, however, does not support application relationships involving KEY (IMS) or MVS objects in its Related Edit/Browse function.
Considerations for MVS Objects
File-AID for Db2 supports defining application relationships for MVS objects as either parent and/or dependent objects. Before defining a relationship keep in mind:
file type must be SEQ, including GDG and GDS, KSDS, ESDS, or IAM.
- MVS object must be defined with a COBOL or PL/I record layout; COBOL and unaligned PL/I record layouts are compiled as interpreted by File-AID.
- only one record layout per object in a relationship file (layout usage S); if more than one is required, use XREF (layout usage X)
- only one XREF file per object in a relationship file
- the LRCL must be less than or equal to 32,756 in order for the data records to be extracted
GDG Considerations
File-AID for Db2 supports relative GDG numbers in addition to the G0000V00 (i.e. .G0001V00) hard coded number for physical sequential files. File-AID allows you to define application relationships (AR) to or from a GDG or a GDS. Relationships are allowed between generations of the same GDG. If, for example, your current quarterly report is to extract from the current and the two previous months, you define these relationships:
- GDG.PAYMENTS.QUARTER(0) to GDG.PAYMENTS.MONTH(0)
- GDG.PAYMENTS.QUARTER(0) to GDG.PAYMENTS.MONTH(-1)
- GDG.PAYMENTS.QUARTER(0) to GDG.PAYMENTS.MONTH(-2)
When specifying the GDG base in a relationship, for example GDG.PAYMENTS.MONTH, File-AID extracts from all existing generations.
Use of Db2 Views
File-AID for Db2 supports defining application relationships for views in the suboptions of the Relationships Options function (Primary Menu Option 6). Views are treated just like tables and can be entered in any Table field. For example, you can define an application relationship for a view to any other dependent object (MVS, DB2 or KEY).
When you specify a view in either Relationships suboption, File-AID for Db2 will display all relationships defined for the view. If no relationships are defined for the view, File-AID for Db2 will not display any relationships. To see the relationships for the view’s base table you must enter the base table name.