In this section: |
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.
How to: Reference: |
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 1024 segments. An RDBMS view counts as one segment toward the total, even if the view represents a join of two or more tables.
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:
Is the one- to eight-character name of the multi-table Master File.
Is one of the following values: SQLDS, SQLIDMS, SQLDBC, SQLORA.
Is the SEGNAME value for the parent table. If this segment references a remote segment description, table1 must be identical to the SEGNAME from the Master File that contains the full definition of the columns in the RDBMS table (see Additional Topics).
Is any field name.
Is the SEGNAME value for the related table. If this segment references a remote segment description, table2 must be identical to the SEGNAME from the Master File that contains the full definition of the columns in the RDBMS table.
Indicates the type of relationship between the table and its parent. Valid values are:
S0 indicates that the related table is in a one-to-many or many-to-many (non-unique) relationship with the table named as its parent.
U indicates that the related table is in a one-to-one or a many-to-one (unique) relationship with the table named as its parent.
KL references a remote segment description. Indicates that the related table is in a one-to-many or many-to-many (non-unique) relationship with the table named as its parent.
KLU references a remote segment description. Indicates that the related table is in a one-to-one or a many-to-one (unique) relationship with the table named as its parent.
References a remote segment description. Indicates the name of the Master File that contains the full definition of the columns in table1.
References a remote segment description. Indicates the name of the Master File that contains the full definition of the columns in table2.
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; ,$
where:
Is the one- to eight-character name of the multi-table Master File.
Is one of the following values: SQLDS, SQLIDMS, SQLDBC, SQLORA.
Is the SEGNAME value for the parent table. If this segment references a remote segment description, table1 must be identical to the SEGNAME from the Master File that contains the full definition of the columns in the RDBMS table (see Additional Topics).
Is any field name.
Is the segment name for the joined segment. Only this segment participates in the join, even if the cross-referenced Master File describes multiple segments.
Is the segment type for the joined segment. Can be DKU, DKM, KU, or KM as with traditional cross-references in the Master File.
Note: If you specify a unique join when the relationship between the host and cross-referenced files is one-to-many, the results will be unpredictable.
Is the parent segment name.
Is the cross-referenced Master File.
Is the cross-referenced segment, if seg is not the same name as the SEGNAME in the cross-referenced Master File.
Is any expression valid in a DEFINE FILE command. All of the fields referenced the expression must lie on a single path.
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 columns in the RDBMS table.
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 columns in the table 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 columns in the RDBMS table. For a complete discussion of remote segment descriptions, see Additional Topics.
Field names can consist of up to 66 alphanumeric characters. Within the Master File, field names 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 qualifier when referencing them in requests.
The ALIAS value for each field must be the full SQL column name (the adapter uses it to generate SQL statements). The ALIAS name must be unique within the segment. DB2 permits 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 attribute identifies EMPINFO as the parent and notifies the 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 a 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
How to: Reference: |
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.
Note:
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:
Is the SEGNAME of the parent table from the multi-table Master File.
Is the SEGNAME of the related table from the multi-table Master File.
Are the names of the parent (tname1) and related (tname2) tables:
Is the fieldname of the primary key column in the parent (table1) table.
Note: This attribute is not supported for a conditional join in the Master File (JOIN_WHERE).
Is the fieldname of the foreign key column in the related (table2) table.
Note: This attribute is not supported for a conditional join in the Master File (JOIN_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 |