To create a multi-table structure, describe the tables, and the relationships between them, in a single Master File. An embedded equijoin also requires that the join fields be specified in a single Access File. |
In this section: |
All segment declarations in a multi-table Master File must describe RDBMS tables or views. Each segment represents one table or view, up to a total of 64 segments. An RDBMS view counts as one segment toward the total, even if the view represents a join of two or more tables. |
How to: Define an Equijoin in the Master File Define a Conditional Join in the Master File Example: Specifying an Embedded Equijoin in a Master File Specifying a Conditional Join in a Master File Reference: |
If several Master Files (used only with TABLE requests) include the same table, you can avoid repeating the same description multiple times. Describe the table in one of the Master Files, and use the CRFILE attribute in the other Master Files to access the existing description. For a full explanation of remote segment descriptions, see Additional Topics.
An embedded equijoin uses the PARENT segment attribute to describe the relationships between tables
FILENAME=mtname, SUFFIX=sqlsuffix [,$]
SEGNAME=table1, SEGTYPE= {S0|KL} [,CRFILE=crfile1] [,$]
FIELD=name,...,$
.
.
.
SEGNAME=table2, SEGTYPE=relationship, PARENT=table1 [,CRFILE=crfile2][,$]
FIELD=name,...,$
.
.
.
where:
Conditional joins in the Master File are supported between relational data sources only. The conditions are considered virtual fields in the Master File.
FILENAME=mtname, SUFFIX=sqlsuffix [,$]
SEGNAME=table1, SEGTYPE= {S0|KL} [,CRFILE=crfile1] [,$]
FIELD=name,...,$
.
.
.
SEGNAME=seg, SEGTYPE=styp, PARENT=parseg,
CRFILE=xmfd, [CRSEG=xseg, ]
JOIN_WHERE=expression; [JOIN_WHERE=expression; ...] ,$
where:
Note:
xseg
The SEGNAME attribute names the segment. SEGNAME values must be unique within the Master File. If the segment references a remote Master File, its SEGNAME value must be identical to the SEGNAME from the Master File that contains the full definition of the RDBMS table's columns.
The SEGTYPE attribute indicates how a table participates in a relationship. The SEGTYPE of the first segment in a multi-table Master File is always S0 (or KL for a remote segment description). Thereafter, related tables (additional segments) are described as follows:
All segment declarations other than the first require the PARENT attribute. The PARENT value for a segment is the SEGNAME of the table to which it will be related at run time.
Specify the CRFILE attribute only if the actual description of the table's columns is stored in another (remote) Master File or to create a conditional join. For a remote segment description, the CRFILE value must be the name of the Master File that contains the full definition of the RDBMS table's columns. For a complete discussion of remote segment descriptions, see Additional Topics.
Field names can consist of up to 66 alphanumeric characters (including any filename and segname qualifiers and qualification characters you may later prefix to them in requests). Within the Master File, fieldnames cannot include qualifiers. Column names are acceptable values if they meet the following naming conventions:
Since the field name displays as the default column title for reports, select a name that is representative of the data. In TABLE, GRAPH, and MODIFY requests, you can specify field names, aliases, or a unique truncation of either. MAINTAIN does not support alias names or truncated names. In all requests, you can qualify a field name with its file name and/or segment name.
Field names must be unique within a single segment. If field names are duplicated across segments, use the segment name as a high level qualifier when referencing them in requests.
The ALIAS value for each field must be the full SQL column name (the data adapter uses it to generate SQL statements). The ALIAS name must be unique within the segment. DB2 and DB2 for VM permit a maximum of 18 alphanumeric characters, Teradata and Oracle permit 30, and IDMS SQL permits 32. The ALIAS name must comply with the same naming conventions described for field names.
ALIAS names may be duplicated within the Master File if they are defined for different tables.
The following Master File relates the DB2 tables EMPINFO and COURSE. The EMPINFO table is described first; therefore, its segment declaration does not include the PARENT attribute. In the COURSE segment declaration, the PARENT keyword identifies EMPINFO as the parent and notifies the data adapter that the two tables may be joined at run time for reporting purposes. The FILENAME ECOURSE identifies this relationship. (For an example of a multi-table Master File with a remote segment, refer to Additional Topics):
FILENAME=ECOURSE ,SUFFIX=SQLDS, $
SEGNAME=EMPINFO ,SEGTYPE=S0, $
FIELD=EMP_ID ,ALIAS=EID ,USAGE=A9 ,ACTUAL=A9,$
FIELD=LAST_NAME ,ALIAS=LN ,USAGE=A15 ,ACTUAL=A15,$
FIELD=FIRST_NAME ,ALIAS=FN ,USAGE=A10 ,ACTUAL=A10,$
FIELD=HIRE_DATE ,ALIAS=HDT ,USAGE=YMD ,ACTUAL=DATE,$
FIELD=DEPARTMENT ,ALIAS=DPT ,USAGE=A10 ,ACTUAL=A10,
MISSING=ON,$
FIELD=CURRENT_SALARY,ALIAS=CSAL ,USAGE=P9.2 ,ACTUAL=P4,$
FIELD=CURR_JOBCODE ,ALIAS=CJC ,USAGE=A3 ,ACTUAL=A3,$
FIELD=ED_HRS ,ALIAS=OJT ,USAGE=F6.2 ,ACTUAL=F4,
MISSING=ON,$
FIELD=BONUS_PLAN ,ALIAS=BONUS_PLAN ,USAGE=I4 ,ACTUAL=I4,$
SEGNAME=COURSE ,SEGTYPE=S0 ,PARENT=EMPINFO,$
FIELD=CNAME ,ALIAS=COURSE_NAME ,USAGE=A15, ACTUAL=A15,$
FIELD=WHO ,ALIAS=EMP_NO ,USAGE=A9, ACTUAL=A9,$
FIELD=GRADE ,ALIAS=GRADE ,USAGE=A1, ACTUAL=A1, MISSING=ON,$
FIELD=YR_TAKEN,ALIAS=YR_TAKEN ,USAGE=A2, ACTUAL=A2,$
FIELD=QTR ,ALIAS=QUARTER ,USAGE=A1, ACTUAL=A1,$
Note:
The following Master File for the EMPINFO table (defined solely for the purpose of this example and not included in File Descriptions and Tables) contains a conditional join to the PAYINFO table. The conditions create a join that includes all employees who received a salary increase within six months of being hired:
FILENAME=EMPINFO ,SUFFIX=SQLDS,$
SEGNAME=EMPINFO ,SEGTYPE=S0,$
FIELD=EMP_ID ,ALIAS=EID ,USAGE=A9 ,ACTUAL=A9 ,$
FIELD=LAST_NAME ,ALIAS=LN ,USAGE=A15 ,ACTUAL=A15 ,$
FIELD=FIRST_NAME ,ALIAS=FN ,USAGE=A10 ,ACTUAL=A10 ,$
FIELD=HIRE_DATE ,ALIAS=HDT ,USAGE=YMD ,ACTUAL=DATE ,$
FIELD=DEPARTMENT ,ALIAS=DPT ,USAGE=A10 ,ACTUAL=A10,
MISSING=ON,$
FIELD=CURRENT_SALARY,ALIAS=CSAL ,USAGE=P9.2 ,ACTUAL=P4 ,$
FIELD=CURR_JOBCODE ,ALIAS=CJC ,USAGE=A3 ,ACTUAL=A3 ,$
FIELD=ED_HRS ,ALIAS=OJT ,USAGE=F6.2 ,ACTUAL=F4,
MISSING=ON,$
FIELD=BONUS_PLAN ,ALIAS=BONUS_PLAN ,USAGE=I4 ,ACTUAL=I4 ,$
FIELD=HIRE_DATE_TIME ,ALIAS=HDTT ,USAGE=HYYMDm ,ACTUAL=HYYMDm ,$
FIELD=HIRE_TIME ,ALIAS=HT ,USAGE=HHIS ,ACTUAL=HHIS ,$
SEGNAME=PAYINFO, SEGTYPE=KM, PARENT = EMPINFO,
CRFILE = PAYINFO,
JOIN_WHERE = DAT_INC GT HIRE_DATE AND DAT_INC LT ( HIRE_DATE + 182);
JOIN_WHERE = EMP_ID EQ PAYEID; ,$
Each table has single segment Access File. No other Access File is needed.
The following report shows the employees included in the join:
TABLE FILE EMPINFO
PRINT SALARY HIRE_DATE DAT_INC
BY LAST_NAME BY FIRST_NAME
END
The output is:
LAST_NAME FIRST_NAME SALARY HIRE_DATE DAT_INC
--------- ---------- ------ --------- -------
CROSS BARBARA $27,062.00 81/11/02 82/04/09
GREENSPAN MARY $9,000.00 82/04/01 82/06/11
IRVING JOAN $26,862.00 82/01/04 82/05/14
JONES DIANE $18,480.00 82/05/01 82/06/01
MCKNIGHT ROGER $16,100.00 82/02/02 82/05/14
SMITH RICHARD $9,500.00 82/01/04 82/05/14
A multi-table Master File indicates that tables are related. However, to implement an equijoin (TABLE) or FOCUS referential integrity (MODIFY and MAINTAIN), you must identify their common fields to FOCUS in the corresponding Access File. |
How to: Create a Multi-table Access File Reference: |
Note: A conditional join does not use a multi-table Access File.
The multi-table Access File includes a segment declaration for each table described in the Master File, even if the segment was referenced remotely in the Master File. The order of segment declarations in the Access File does not have to match the order in the Master File, but maintaining the same order enhances readability.
Each segment declaration in the Access File must contain the required keywords described in Describing Tables to FOCUS.
In addition, each segment except the first must identify the fields that it shares with its parent segment. In each Access File segment declaration other than the first, the KEYFLD and IXFLD attributes supply the names of the primary key and foreign key fields that implement the relationships established by the multi-table Master File.
SEGNAME=table1, TABLENAME=tname1,...,$
SEGNAME=table2, TABLENAME=tname2,...,
KEYFLD=pkfield, IXFLD=fkfield,$
where:
The KEYFLD and IXFLD attributes identify the field shared by a related table pair. KEYFLD is the FIELDNAME of the common column from the parent table. IXFLD is the FIELDNAME of the common column from the related table. KEYFLD and IXFLD must have the same data type. It is recommended, but not required, that their lengths also be the same.
Note: An RDBMS index on both the KEYFLD and IXFLD columns provides the RDBMS with a greater opportunity to produce efficient joins. The columns must have the same data type. If their length is the same, the RDBMS handles the join more efficiently.
In the ECOURSE example from Specifying an Embedded Equijoin in a Master File, the fields EMP_ID in EMPINFO and WHO in COURSE both contain employee identification numbers; they represent the common field. Since the COURSE table is the related table, its segment declaration in the ECOURSE Access File identifies these common columns from EMPINFO and COURSE:
SEGNAME = EMPINFO, TABLENAME = "USER1"."EMPINFO", KEYS = 1,
WRITE = YES, DBSPACE = PUBLIC.SPACE0,$
SEGNAME = COURSE, TABLENAME = "USER1"."COURSE", KEYS = 2,
WRITE = YES, DBSPACE = PUBLIC.SPACE0,
KEYFLD = EMP_ID, IXFLD = WHO,$
| Information Builders |