Describing Multi-File Structures for DATACOM

In this section:

You can describe many different DATACOM data sources in one Master File and Access File pair. However, in the adapter, there must be at least one field in common between any parent and descendant pair of data sources. Each set of related fields must also have comparable USAGE and ACTUAL formats.

Each time you add a descendant segment, you must specify the PARENT attribute in the segment record. This will identify hierarchical relationships between the data sources.

In addition, you must specify the relationship between the fields in the Access File with the KEYFLD and IXFLD attributes:

There are advantages to defining multiple data sources in a single structure:

To illustrate this concept, we will use the DATACOM data sources PERSON and PAYROLL.

The PAYROLL data source contains information about the wages, commissions, and taxes for each person in the PERSON data source.

Master File PAYROLL

FILENAME=PAYROLL, SUFFIX=DATACOM , $
  SEGMENT=PAYROLL, SEGTYPE=S0, $
    FIELDNAME=NUMBER,          ALIAS=PAYRC.0000, USAGE=P5,   ACTUAL=Z5, $
    FIELDNAME=ACTIVITY_CODE,   ALIAS=PAYRC.0001, USAGE=A1,   ACTUAL=A1, $
    FIELDNAME=ACTIVITY_STATUS, ALIAS=PAYRC.0002, USAGE=A1,   ACTUAL=A1, $
    FIELDNAME=CURRENT_RATE,    ALIAS=PAYRC.0003, USAGE=P9.2, ACTUAL=Z8, $
    FIELDNAME=YTD_WAGES,       ALIAS=PAYRC.0004, USAGE=P9.2, ACTUAL=Z8, $
    FIELDNAME=YTD_COMMISSION,  ALIAS=PAYRC.0005, USAGE=P9.2, ACTUAL=Z8, $
    FIELDNAME=YTD_TAX,         ALIAS=PAYRC.0006, USAGE=P9.2, ACTUAL=Z8, $

Access File PAYROLL

USERTABLE=SAMPURT, TRACE=NO, $
 SEGNAME=PAYROLL, TABLENAME=PAY, DBID=1, KEYNAME=EMPNO, $
  FIELD=NUMBER,         SIGN=N, TYPE=C, $
  FIELD=CURRENT_RATE,   SIGN=N, TYPE=C, $
  FIELD=YTD_WAGES,      SIGN=N, TYPE=C, $
  FIELD=YTD_COMMISSION, SIGN=N, TYPE=C, $
  FIELD=YTD_TAX,        SIGN=N, TYPE=C, $

Top of page

x
Multi-File Master File

Consider an application that contains both the PERSON and PAYROLL data sources. It is reasonable to generate reports that identify the current salary rate for all employees, or a list of all the employees who have a particular salary rate.

For demonstration purposes, we will define both these DATACOM logical files in one Master File. In all but the top (or root) segment, you must specify the PARENT attribute. PARENT establishes the relationship between two segments

PARENT=segname

where:

segname

Is the name of the parent of the child segment. If you do not specify a PARENT attribute, it will default to the immediate predecessor of the child segment as the parent.



Example: Multi-File Master File

The following example shows a static Join in a manually combined multi-file Master File with the PARENT attribute added to the descendent segment. The file name PERSPAY identifies the entire Master File.

Master File PERSPAY

FILENAME=PERSPAY, SUFFIX=DATACOM , $
 SEGMENT=PERSON, SEGTYPE=S0, $
  FIELDNAME=NUMBER_PERS,     ALIAS=EMDTA.0000, USAGE=P5,   ACTUAL=Z5,  $
  FIELDNAME=NAME,            ALIAS=EMDTA.0001, USAGE=A24,  ACTUAL=A24, $
  FIELDNAME=STREET_ADDRESS,  ALIAS=EMDTA.0002, USAGE=A24,  ACTUAL=A24, $
  FIELDNAME=CITY_ADDRESS,    ALIAS=EMDTA.0003, USAGE=A15,  ACTUAL=A15, $
  FIELDNAME=STATE_ADDRESS,   ALIAS=EMDTA.0004, USAGE=A2,   ACTUAL=A2,  $
  FIELDNAME=ZIP_CODE_LOC,    ALIAS=EMDTA.0005, USAGE=A5,   ACTUAL=A5,  $
  FIELDNAME=SOCIAL_SECURITY, ALIAS=EMDTA.0006, USAGE=P10,  ACTUAL=P5,  $
 SEGMENT=PAYROLL, SEGTYPE=S0, PARENT=PERSON, $
  FIELDNAME=NUMBER_PAY,      ALIAS=PAYRC.0000, USAGE=P5,   ACTUAL=Z5,  $
  FIELDNAME=ACTIVITY_CODE,   ALIAS=PAYRC.0001, USAGE=A1,   ACTUAL=A1,  $
  FIELDNAME=ACTIVITY_STATUS, ALIAS=PAYRC.0002, USAGE=A1,   ACTUAL=A1,  $
  FIELDNAME=CURRENT_RATE,    ALIAS=PAYRC.0003, USAGE=P9.2, ACTUAL=Z8,  $
  FIELDNAME=YTD_WAGES,       ALIAS=PAYRC.0004, USAGE=P9.2, ACTUAL=Z8,  $
  FIELDNAME=YTD_COMMISSION,  ALIAS=PAYRC.0005, USAGE=P9.2, ACTUAL=Z8,  $
  FIELDNAME=YTD_TAX,         ALIAS=PAYRC.0006, USAGE=P9.2, ACTUAL=Z8,  $

Top of page

x
Multi-File Access File

The Access File for a multi-file structure must have a segment record for each logical segment you define in the Master File.

Each segment record, other than the root, must contain the KEYFLD and IXFLD attributes in addition to the Access File attributes described earlier. These new attributes identify the field(s) in an embedded cross-reference. The common field(s) serve as the cross-referenced link between the two data sources.

The PAYROLL data source is the child, or cross-referenced, segment in the following example. This is important because you identify cross-referencing fields by specifying KEYFLD and IXFLD in the cross-referenced segment record.

KEYFLD

The field name in the parent or host segment, or a list of up to five fields separated by a forward slash symbol ( / ).

IXFLD

The field name in the child or cross-referenced segment, or a list of up to five fields separated by a forward slash symbol ( / ).



Example: Multi-File Access File

In the example, the field NUMBER_PERS in the PERSON data source is related to the field NUMBER_PAY in the PAYROLL data source. Both have the same USAGE and ACTUAL data format and length (P5 and Z5); this is a server requirement.

Add the segment record for the PAYROLL data source to the Access File, including the KEYFLD and IXFLD attributes:

Access File PERSPAY

USERTABLE=SAMPURT, TRACE=NO, $
 SEGNAME=PERSON, TABLENAME=PMF, DBID=1, KEYNAME=EMPNO, $
  FIELD=NUMBER, SIGN=N, TYPE=C, $
  FIELD=SOCIAL_SECURITY, SIGN=Y, TYPE=C, $
 SEGNAME=PAYROLL, TABLENAME=PAY, DBID=1, KEYNAME=EMPNO,
         KEYFLD=NUMBER_PERS, IXFLD=NUMBER_PAY, $
  FIELD=NUMBER,         SIGN=N, TYPE=C, $
  FIELD=CURRENT_RATE,   SIGN=N, TYPE=C, $
  FIELD=YTD_WAGES,      SIGN=N, TYPE=C, $
  FIELD=YTD_COMMISSION, SIGN=N, TYPE=C, $
  FIELD=YTD_TAX,        SIGN=N, TYPE=C, $

Top of page

x
Using Multiple Fields to Cross-Reference Data Sources

With the Adapter for DATACOM, you can use up to five fields to establish a relationship or cross-reference between data sources.

For example, matching permutations of values of Z/Y/X/W/V in the KEYFLD and IXFLD attributes can be used to create new cross-referenced fields in the Access File. This is sometimes referred to as using concatenated keys.



Example: Using Multiple Fields to Cross-Reference Data Sources

To illustrate how to use concatenated keys, we will alter the PERSPAY multi-file Master and Access Files as shown in the following example:

  1. Replace the field NUMBER_PERS in the PERSON segment with the LAST_NAME and FIRST_NAME fields.
  2. In the PAYROLL segment, replace the field NUMBER_PAY with LN and FN.
  3. Separate each field that is part of the cross-reference with a forward slash symbol (/).
FILENAME=PERPAY, SUFFIX=DATACOM , $
 SEGMENT=PERSON, SEGTYPE=S0, $
  FIELDNAME=LAST_NAME,       ALIAS=EMDTA.0000, USAGE=A15,  ACTUAL=A15, $
  FIELDNAME=FIRST_NAME,      ALIAS=EMDTA.0001, USAGE=A10,  ACTUAL=A10, $
  FIELDNAME=NAME,            ALIAS=EMDTA.0002, USAGE=A24,  ACTUAL=A24, $
  FIELDNAME=STREET_ADDRESS,  ALIAS=EMDTA.0003, USAGE=A24,  ACTUAL=A24, $
  FIELDNAME=CITY_ADDRESS,    ALIAS=EMDTA.0004, USAGE=A15,  ACTUAL=A15, $
  FIELDNAME=STATE_ADDRESS,   ALIAS=EMDTA.0005, USAGE=A2,   ACTUAL=A2,  $
  FIELDNAME=ZIP_CODE_LOC,    ALIAS=EMDTA.0006, USAGE=A5,   ACTUAL=A5,  $
  FIELDNAME=SOCIAL_SECURITY, ALIAS=EMDTA.0007, USAGE=P10,  ACTUAL=P5,  $
 SEGMENT=PAYROLL, SEGTYPE=S0, PARENT=PERSON, $
  FIELDNAME=LN,              ALIAS=EMDTA.0000, USAGE=A15,  ACTUAL=A15, $
  FIELDNAME=FN,              ALIAS=EMDTA.0001, USAGE=A10,  ACTUAL=A10, $
  FIELDNAME=ACTIVITY_CODE,   ALIAS=PAYRC.0002, USAGE=A1,   ACTUAL=A1,  $
  FIELDNAME=ACTIVITY_STATUS, ALIAS=PAYRC.0003, USAGE=A1,   ACTUAL=A1,  $
  FIELDNAME=CURRENT_RATE,    ALIAS=PAYRC.0004, USAGE=P9.2, ACTUAL=Z8,  $
  FIELDNAME=YTD_WAGES,       ALIAS=PAYRC.0005, USAGE=P9.2, ACTUAL=Z8,  $
  FIELDNAME=YTD_COMMISSION,  ALIAS=PAYRC.0006, USAGE=P9.2, ACTUAL=Z8,  $
  FIELDNAME=YTD_TAX,         ALIAS=PAYRC.0007, USAGE=P9.2, ACTUAL=Z8,  $

These multiple fields will now become the KEYFLD and IXFLD attributes in the Access File.

Separate each field from the next with the forward slash symbol (/), and add these attributes to the PAYROLL segment record in the Access File:

Access File PERPAY

USERTABLE=SAMPURT, TRACE=NO, $
 SEGNAME=PERSON, TABLENAME=PMF, DBID=1, KEYNAME=EMPNO, $
  FIELD=SOCIAL_SECURITY, SIGN=Y, TYPE=C, $
 SEGNAME=PAYROLL, TABLENAME=PAY, DBID=1, KEYNAME=EMPNO,
         KEYFLD=LAST_NAME/FIRST_NAME, IXFLD=LN/FN, $
  FIELD=CURRENT_RATE,   SIGN=N, TYPE=C, $
  FIELD=YTD_WAGES,      SIGN=N, TYPE=C, $
  FIELD=YTD_COMMISSION, SIGN=N, TYPE=C, $
  FIELD=YTD_TAX,        SIGN=N, TYPE=C, $

Top of page

x
Reviewing a DATACOM Detail Report

Although the synonym creation facility generates metadata for a DATACOM data source, you may find it useful to review internal information, as it is maintained by DATACOM, in a detail report like the sample shown in this section.



Example: Detailed DATACOM Field Report

This is a sample DATACOM field report. (Note that non-essential information has been removed from this multi-page report, as indicted by ellipses, to focus your attention on the basic content.)

... ...

...


iWay Software