Creating a Multi-table Structure

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.

Multi-table Master Files

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.

Top of page

Syntax: How to Define an Equijoin in the Master File

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:

mtname

Is the one- to eight-character name of the multi-table Master File.

sqlsuffix

Is one of the following values: SQLDS, SQLIDMS, SQLDBC, SQLORA.

table1

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).

name

Is any field name.

table2

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.

relationship

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.

crfile1

References a remote segment description. Indicates the name of the Master File that contains the full definition of the columns in table1.

crfile2

References a remote segment description. Indicates the name of the Master File that contains the full definition of the columns in table2.

Top of page

Syntax: How to Define a Conditional Join in the Master File

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:

mtname

Is the one- to eight-character name of the multi-table Master File.

sqlsuffix

Is one of the following values: SQLDS, SQLIDMS, SQLDBC, SQLORA.

table1

name

Is any field name.

seg

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.

styp

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.

parseg: Is the parent segment name.
xmfd: Is the cross-referenced Master File.

xseg: Is the cross-referenced segment, if seg is not the same name as the SEGNAME in the cross-referenced Master File.
expression: Is any expression valid in a DEFINE FILE command. All of the fields referenced the expression must lie on a single path.

Top of page

Reference: SEGNAME

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.

Top of page

Reference: SEGTYPE

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:

SEGTYPE=S0 (S-zero) 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. (For every row of the parent table there may be more than one matching row in the related table.)
SEGTYPE=U is used for an embedded equijoin and 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. (For every row of the parent table, there is at most one matching row in the related table. For a one-to-one relationship to exist, both tables must share the same primary key. For a many-to-one relationship to exist, the primary key of the related table must be a subset of the primary key of the parent table.)
SEGTYPE=KL references a remote segment description. It 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. (For every row of the parent table there may be more than one matching row in the related table.)
SEGTYPE=KLU references a remote segment description. It 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. (For every row of the parent table, there is at most one matching row in the related table. For a one-to-one relationship to exist, both tables must share the same primary key. For a many-to-one relationship to exist, the primary key of the related table must be a subset of the primary key of the parent table.)
SEGTYPE DKU or KU indicates a dynamic unique conditional join.
SEGTYPE DKM or KM indicates a dynamic non-unique conditional join.

Top of page

Reference: PARENT

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.

Top of page

Reference: CRFILE

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.

Top of page

Reference: FIELD

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:

A name can consist of letters, digits, and underscore characters. Special characters and embedded blanks are not advised.
The name must contain at least one letter.

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.

Top of page

Reference: ALIAS

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.

Top of page

Example: Specifying an Embedded Equijoin in a Master File

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 join is based on matching values between a field in the parent segment and a field in the child segment. The join fields are identified in a multi-table Access File, which is described in Multi-table Access Files.
Multi-table Master Files used in MODIFY and Maintain procedures invoke FOCUS referential integrity. Refer to Maintaining Tables With FOCUS for a discussion of referential integrity before deciding whether to use the same file descriptions for both reporting and maintenance procedures.

Top of page

Example: Specifying a Conditional Join in a Master File

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

Multi-table Access Files

How to:

Create a Multi-table Access File

Reference:

KEYFLD and IXFLD

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:

A conditional join does not use a multi-table Access File.
The KEYFLD and IXFLD attributes that specify the matching fields for an equijoin should not be used when doing a conditional join with JOIN_WHERE.

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.

Top of page

Syntax: How to Create a Multi-table Access File

SEGNAME=table1, TABLENAME=tname1,...,$
SEGNAME=table2, TABLENAME=tname2,...,
  KEYFLD=pkfield, IXFLD=fkfield,$

where:

table1

Is the SEGNAME of the parent table from the multi-table Master File.

table2

Is the SEGNAME of the related table from the multi-table Master File.

tname1, tname2

Are the names of the parent (tname1) and related (tname2) tables:

For DB2, tablename or creator.tablename. For the DB2 Distributed Data Facility, include a subsystem location identifier (see Additional Topics).
For Teradata, tablename or databasename.tablename.
For IDMS/SQL, tablename or schema.tablename.
For Oracle, userid.tablename.

pkfield

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).

fkfield

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).

Top of page

Reference: KEYFLD and IXFLD

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,$