Refreshing relationship file from an outside source

This section describes batch jobs that load or refresh a Relationship file with current information from an outside source. The Relationship file was specified during the Db2 Environment definition (see RELATIONSHIP_DATASET). The relationship file is a VSAM KSDS file which can be can be shared between File-AID/RDX and File-AID for Db2.

Loading or Refreshing CA Gen Application Relationships

If you have CA Gen1 for mainframe systems, you can load Db2 application relationship (AR) information from the CA Gen Public Interface tables.

Important

1: CA Gen is a Computer Aided Software Engineering (CASE) application development environment marketed by CA Technologies. Gen was previously known as IEF (Information Engineering Facility), Composer by IEF, Composer, COOL:Gen, Advantage:Gen and AllFusion Gen.

Db2 Application Relationships from Other Sources

File-AID includes a sample COBOL program that you must modify to load Db2 application relationship information from other relationship sources such as code generators.

These jobs can be executed initially by the person responsible for installing File-AID. However, they should be executed periodically to relationships in the relationship file. This is necessary after structure changes, such as new or changed foreign keys, new or changed primary keys, and new tables.

Loading or Refreshing CA Gen Application Relationships

The following procedure describes how to load or refresh AR information from the CA Gen Public Interface tables. The following CA Gen tables are required to load information.

  • FIELD
  • FLD_LINK_USE
  • LINKAGE
  • MODEL
  • RECORD
  • REL_PART_IMPL

Procedure

Perform the following tasks. All JCL members can be found in the File-AID sample library, hlq.SXVJSAMP.

Important

When refreshing application relationships, you only need to complete Task 19.2 and Task 19.3.

Task 19.1 Bind the Plan

Select member XFR2EFBD. This JCL binds the plan XFRIEFCA.

  1. Modify sample JCL (in hlq.SXVJSAMP) to your site's requirements by following the instructions in the member.
  2. Submit the job.

Task 19.2 Create a Sequential File

Select member XFR2EFRT. This JCL extracts the relationship information from the CA Gen tables and loads it into a sequential file ??.IEF.FLATFILE.

  1. Modify sample JCL (in hlq.SXVJSAMP) to your site's requirements by following the instructions in the member.
  2. Submit the job.

Important

To load or refresh the relationships for a single model only, change the last parameter of the FRONTEND task from '*’ to the desired model ID, 'model ID’.

Task 19.3 Load the Relationship File

Select member XFR2EFBK or XFR3ARBK (for a Version 3 Relationship file with long object name support). This JCL loads the information from the flat file created in Task 19.2 into the relationship file.

  1. Modify sample JCL (in hlq.SXVJSAMP) to your site's requirements by following the instructions in the member.
  2. Submit the job.

This task also checks for:

  • duplicates in the relationship file
  • existence of related tables and columns in the specified subsystem

Return Codes and Messages

The above jobs can return the following return codes.

Return Code

Description

0

Job ran to completion.

4

Job ran to completion with warning messages issued.

8

Severe error, job aborted.

Front End Messages

(Task 19.2) As the extracted relationship(s) from the CA Gen Public Interface tables are loaded in the relationship file, error messages appear in the output listing. Each important error message also includes a recommended action.

Please include the '*’ or Model Id in the Parm. The program terminates.
Return code: 08

User Response: Supply either '*’ or ModelId in the PARM keyword of the RUN command in the front end JCL for all the Model IDs or a particular Model ID to be extracted respectively.

More than 12 fields encountered in the relationship. RDX restriction. The program terminates.
Return code: 08

User Response: The relationship cannot be extracted. RDX does not allow more than 12 fields in the relationship.

Important

For the File Open errors, check the file’s DCB information in the JCL.

Back End Messages

(Task 19.3) In case of any error message(s) in the Back End Job, first check whether you are using the correct File-AID Plan Name and Load Library, Subsystem ID, and Db2 Load Libraries.

The Parent table <Creator Id>/<Table Name> was not found.
Return code: 04

User Response: Check whether the table exists in the specified subsystem.

The Dependent table <Creator Id>/<Table Name> was not found
Return code: 04

User Response: Check whether the table exist in the specified subsystem.

There is already a relationship record with the same key on the RDX VSAM file
Parent Owner = <Owner ID>
Parent Table = <Table Name>
Target Owner = <Owner ID>
Target Table = <Table Name>
Return code: 04

User Response: The specified VSAM file already contains the relationship. That is why it cannot be loaded.

No match found for IEF Column Name, Type, and Length in DB2 Table.
DB2 table Name is <Owner ID>.<TableName>
IEF Column Name is <Column Name>
IEF Column Type is <Column Type>
IEF Column Length is <Column Length>
Return code: 04

User Response: The CA Gen column Name, Type, or Length of the table involved in the relationship extracted from CA Gen Public Interface tables do not match that of the Db2 table. The CA Gen column information is displayed as above. Check the same in Db2.

Db2 Application Relationships from Other Sources

The following information describes how Db2 application relationship information from other relationship sources, such as code generators, could be loaded into the File-AID relationship file.

COBOL Sample Program (XFRMRTUP)

The File-AID sample file (hlq.SXVJSAMP) includes a sample COBOL program (XFRMRTUP) which you can modify to load Db2 application relationship (AR) information from other Db2 relationship sources. The provided sample program extracts relationship data from CA Gen (IEF) model tables and populates a flat file that can then be loaded with XFR2EFBK for a Version 2 Relationship file or XFR3ARBK for a Version 3 Relationship file with long object name support.

Use this sample as a starting point so it can extract the relationship information from your source and populate the flat file needed to load the File-AID relationship file.

Flat File Layout

The layout for the flat file is member XFRRLFMT or XFR3RLFM (for a Version 3 Relationship file with long object name support) in the File-AID sample file (hlq.SXVJSAMP). The file format is VB. The minimum record length is 138 (for a single pair of parent/dependent columns). The maximum record length is 3672, but File-AID only allows a maximum of 20 related column pairs.

Important

When using XFRRLFMT (short name support):

  • ALL fields must be filled in
  • C-NUM-COLS-P must be equal to C-NUM-COLS-D
  • COL-INFO-P.C-PAR-IND should have an "S" (stands for source or parent)
  • COL-INFO-D.C-PAR-IND should have a "T" (stands for target or child)

Important

When using XFR3RLFM (long name support):

  • ALL fields must be filled in
  • NUM-COL must be equal to NUM-COLS-DEP
  • COL-INFO-PAR.PAR-DEP-IND should have an "S" (stands for source or parent)
  • COL-INFO-DEP.PAR-DEP-IND should have a "T" (stands for target or child)
  • PAR-OBJ-TYP must be "D" for a Db2 table
  • DEP-OBJ-TYP must be "D" for a Db2 table

Procedure

Complete the following tasks. All referenced members can be found in the File-AID sample file (hlq.SXVJSAMP).

Important

When refreshing application relationships, you only need to complete Task 19.6 and Task 19.7.

Task 19.4 Modify the Sample COBOL Program

Modify member XFRMRTUP. This program is meant to be modified so that any Db2 relationship data available from other sources such as code generators, 4GL packages, or ERP systems can be used to populate the flat file.

Compile and link the COBOL program. (JCL is not provided due to the varying environmental factors in various computing environments.)

Task 19.5 Bind the Modified COBOL Program

Important

Only needed if the relationship data is stored in Db2 tables. If, for example, the database for your application code generator stores its relationship data in a VSAM file, skip this task.

  1. Modify sample JCL (member XFRMRTBD in hlq.SXVJSAMP) to your site's requirements by following the instructions in the member.
  2. Submit the job.

Task 19.6 Run the Modified COBOL Program

Execute the modified COBOL program.

If, for example, the database for your application code generator stores its relationship data in a VSAM file, XFRMRTJB is not appropriate.

If the relationship data is stored in Db2 tables:

  1. Modify sample JCL (member XFRMRTJB in hlq.SXVJSAMP) to your site's requirements by following the instructions in the member.
  2. Submit the job.

This program extracts the relationship information from the specified source and loads it into a sequential file ??.IEF.FLATFILE.

Task 19.7 Load the Relationship File

Select member XFR2EFBK or XFR3ARBK (for a Version 3 Relationship file with long object name support). This JCL writes the information in the sequential file created in Task 19.6 to the relationship file.

  1. Modify sample JCL (in hlq.SXVJSAMP) to your site's requirements by following the instructions in the member.
  2. Submit the job.

This task also checks for

  • duplicates in the relationship file
  • existence of related tables and columns in the specified subsystem

Back End Messages

(Task 19.7) Refer to Back End Messages for information.

Related topics

BMC AMI DevX File-AID/RDX
BMC AMI DevX File-AID for Db2

 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*