The Dynamic JOIN Command
With the FOCUS dynamic JOIN command, you can reference two or more related
databases or external files in a single FOCUS report request. The databases remain
physically separate, but FOCUS treats them as a single logical structure.
The JOIN command joins a host file (the FROM file) to a cross-referenced file (the TO file)
at execution time by matching values in one or more fields common to both files. IMS
databases can participate as host or cross-referenced files in joins.
To issue a JOIN, you do not have to restructure the IMS database or rerun either the
PSBGEN or the DBDGEN. However, when you join TO an IMS database, you must join to
one of the following:
- A key field or secondary index in the root segment (ALIAS= IMSname.KEY, IMSname.HKY, or IMSname.SKY).
- The high-order portion of a key field or secondary index in the root segment.
- HDAM cannot use the high-order portion of a key field. It must be the full key.
Note: If a key consists of multiple fields, you must make it a GROUP in the Master File.
This restriction is necessary because FOCUS connects the databases at execution time
using GET UNIQUE calls with a qualified SSA on the key field of the TO file.
You can join an IMS database to other IMS databases or, with the appropriate FOCUS
Interfaces installed, you can join an IMS database to any other joinable database such as
a DB2 table, a VSAM file, or a FOCUS database.
Two types of dynamic JOINs are available. The difference between the two depends on the
cross-referenced file and its key values:
- Multiple or non-unique join. This defines a one-to-many or many-to-many correlation between the records of the host file and the records of the cross-referenced file. For each record in the host file, FOCUS may retrieve multiple matching records from the cross-referenced file.
- Unique join. This defines a one-to-one correlation between a record in the host file and one record in the cross-referenced file. For each record in the host file, FOCUS will retrieve at most one matching record from the cross-referenced file.
To create a non-unique join, include the ALL keyword in the JOIN command; for a unique
join, omit the ALL keyword.
Single-Field Dynamic JOIN
To create a join based on relating one field in the host file to one field in the
cross-referenced file, the syntax is
JOIN fielda1 [WITH rfield] IN hostfile [TAG tag]
TO [ALL] fieldbl IN crfile [TAG tag] [AS joinname]
[END]
where:
- fielda1
- Is a field in the host file or a DEFINE field that contains values shared with fieldb1 in
the cross-referenced file.
- WITH rfield
- Is any real field in the same segment as the DEFINE field; associates the DEFINE field
with a segment location in the host file. Use only if fielda1 is a DEFINE field.
- IN hostfile
- Indicates the name of the host file. Use this name in subsequent report requests on
the joined structure.
- TAG tag
- Indicates a one- to eight-character tag name (usually the Master File name). The tag
name serves as a unique qualifier for fields and aliases in host or cross-referenced
files.
- TO ALL
- Indicates that the host and cross-referenced files participate in a one-to-many or
many-to-many relationship. In other words, for any value of the join field in the host file
(fielda1), there may be more than one corresponding instance of that value for the join
field in the cross-referenced file (fieldb1). This is known as a non-unique or multiple
join.
- Omitting the ALL parameter indicates a unique join. Use only when each value of the
join field in the host file (fielda1) has, at most, one possible corresponding value of
the join field in the cross-referenced file (fieldb1).
- fieldb1
- Is a key, a secondary index, or the high-order portion of a key or secondary index in
the root segment of the cross-referenced file; fieldb1 shares values with fielda1. The
ACTUAL format of fieldb1 does not have to match the ACTUAL format of fielda1.
However, the contents of fielda1 must be convertible to a value of the same type as
fieldb1.
- IN crfile
- Indicates the name of the cross-referenced file.
- AS joinname
- Assigns an optional one- to eight-character name to the join structure. Naming the join
prevents subsequent JOIN commands from overwriting it and enables you to clear it
selectively with the JOIN CLEAR command.
- END
- Required when the JOIN command is longer than one line; it terminates the
statement.
Up to 16 JOIN commands can be active at one time. Joins operate in all environments.
Databases accessed by CMS through a Cross-Machine Interface can be joined together, to
local CMS files, and to other databases accessed by another XMI.
Note:
- All participating files in a join must use the same PSB. Any attempt to join files that require different PSBs generates the following error message:
(FOC4295) ACCESS POINTS TO DIFFERENT PSBS IN JOIN
If you join an IMS database to itself (a recursive join), the PSB must contain two PCBs for the database.
HSAM databases cannot participate as the cross-referenced (TO) file in a join because they lack an index or randomizing scheme for locating records.
Except for HDAM, if you join a field in the host file to a longer field in the cross-referenced file, FOCUS implements the join as a partial key search and retrieves the records as in a range test (see Section Partial Key and Multi-Segment Requests, Partial Key and Multi-Segment Requests). Short to long joins are not supported against the primary key of an HDAM database since they result in incomplete record selection. However, short to long joins are supported if the join fields constitute the high-order portion of a secondary index.
If you join a field in the host file to a shorter field in the cross-referenced file, FOCUS truncates the host field.
When a record in the host file lacks a matching record in the cross-referenced file, the retrieval path is called a short path. Retrieval of Unique Segments, illustrates an example. Refer to the FOCUS for IBM Mainframe Users Manual for a complete discussion of short path behavior.
The following examples illustrate the JOIN command
JOIN V1 IN VSAM1 TAG FILE1 TO I1 IN IMS1 TAG FILE2 AS JOIN1
JOIN I2 IN IMS2 TO ALL I3 IN IMS3 AS JOIN2
JOIN I1 IN IMS1 TO F1 IN FOCUS1 AS JOIN3
where:
- VSAM1
- Is a VSAM file.
- IMS1,IMS2,IMS3
- Are IMS databases.
- FOCUS1
- Is a FOCUS database.
- V1
- Is any field in VSAM1.
- I1
- Is a key to the root segment of IMS1.
- I2
- Is any field in IMS2.
- I3
- Is a key to the root segment of IMS3.
- F1
- Is a FOCUS indexed field.
Multi-Field Dynamic JOIN
To create a join based on relating multiple fields in the host and cross-referenced files, the
syntax is
JOIN fielda1 AND fielda2 IN hostfile [TAG tag] TO [ALL]
fieldbl AND fieldb2 IN crfile [TAG tag] [AS joinname]
[END]
where:
- fielda1
- Is a field in the host file that shares values with fieldb1 in the cross-referenced file.
- AND fielda2
- Indicates a field in the host file that shares values with fieldb2 in the cross-referenced
file.
- IN hostfile
- Indicates the name of the host file. Use this name in subsequent report requests on
the joined structure.
- TAG tag
- Indicates a one- to eight-character tag name (usually the Master File name). The tag
name serves as a unique qualifier for fields and aliases in host or cross-referenced
files.
- TO ALL
- Indicates that the host and cross-referenced files participate in a one-to-many or
many-to-many relationship. In other words, for any value of the join fields in the host
file (fielda1, fielda2), there may be more than one corresponding instance of those
values for the join fields in the cross-referenced file (fieldb1, fieldb2). This is known as
a non-unique or multiple join.
- Omitting the ALL parameter indicates a unique join. Use only when each value of the
join field in the host file (fielda1) has, at most, one possible corresponding value of
the join field in the cross-referenced file (fieldb1).
- fieldb1
- Is a key, a secondary index, or the high-order portion of a key or secondary index in
the root segment of the cross-referenced file; fieldb1 shares values with fielda1. The
ACTUAL format of fieldb1 does not have to match the ACTUAL format of fielda1.
However, the contents of fielda1 must be convertible to a value of the same type as
fieldb1.
- fieldb2
- Is a key, a secondary index, or the high-order portion of a key or secondary index in
the root segment of the cross-referenced file; fieldb2 shares values with fielda2. The
ACTUAL format of fieldb2 does not have to match the ACTUAL format of fielda2.
However, the contents of fielda2 must be convertible to a value of the same type as
fieldb2.
- IN crfile
- Indicates the name of the cross-referenced file.
- AS joinname
- Assigns an optional one- to eight-character name to the join structure. Naming the join
prevents subsequent JOIN commands from overwriting it and enables you to
selectively clear it with the JOIN CLEAR command.
- END
- Required when the JOIN command is longer than one line; it terminates the
statement.
Up to 16 JOIN commands can be active at one time. Joins operate in all environments.
Databases accessed by CMS through a Cross-Machine Interface can be joined together, to
local CMS files, and to other databases accessed by another XMI.
Note:
- If you join TO multiple components of a key or secondary index (a partial key join), you must specify the ALL parameter to retrieve all matching records.
- All participating files in a join must use the same PSB. Any attempt to join files that require different PSBs generates the following error message:
(FOC4295) ACCESS POINTS TO DIFFERENT PSBS IN JOIN
If you join an IMS database to itself (a recursive join), the PSB must contain two PCBs for the database.
HSAM databases cannot participate as the cross-referenced file in a join because they lack an index or randomizing scheme for locating records.
Except for HDAM, if you join a field in the host file to a longer field in the cross-referenced file, FOCUS implements the join as a partial key search and retrieves the records as in a range test (see Partial Key and Multi-Segment Requests). Short to long joins are not supported against the primary key of an HDAM database since they result in incomplete record selection. However, short to long joins are supported if the join fields constitute the high-order portion of a secondary index.
If you join a field in the host file to a shorter field in the cross-referenced file, FOCUS truncates the host field.
When a record in the host file lacks a matching record in the cross-referenced file, the retrieval path is called a short path. Retrieval of Unique Segments, illustrates an example. Refer to the FOCUS for IBM Mainframe Users Manual for a complete discussion of short path behavior.
Join Optimization
To determine the best access path into the database, the Interface examines the record
selection criteria in each report request (see Auto Index Selection). When the request
references a join, the Interface may use the results of this examination to retrieve the
cross-referenced segment using a group key or secondary index.
Consider a single-field JOIN command in which the cross-referenced field is the high order
portion of a group key or secondary index. Under certain conditions, the Interface uses the
group key or secondary index to retrieve the cross-referenced segment, effectively
converting the single-field join into a multi-field join. This conversion process is called join
optimization.
The Interface can optimize a join when a TABLE or SQL Translator request satisfies the
following two conditions:
- The report references a join on the high-order portion of a key or secondary index in the cross-referenced file.
For example, the EMPDB01 database contains fields SSNALPHA, EMPID, and LAST_NAME. In addition, the PATDB01 database has a secondary index, IXCOMP, that consists of fields SSNALPHA, EMPLOYEEID, and LAST_NAME (see Sample File Descriptions). The following request references a join on the high-order field of the IXCOMP secondary index:
JOIN SSNALPHA IN EMPDB01 TAG E1 TO SSNALPHA IN PATINFO TAG P1 AS J1
TABLE FILE EMPDB01
PRINT E1.SSNALPHA P1.SSNALPHA E1.EMPID P1.EMPLOYEEID
- The request includes optimizable record selection tests on one or more of the remaining high-order fields in the key or secondary index. For example:
WHERE E1.EMPID EQ P1.EMPLOYEEID
WHERE E1.LAST_NAME EQ P1.LAST_NAME
END
Auto Index Selection examines the request and determines that SSNALPHA, EMPLOYEEID,
and LAST_NAME participate in a secondary index in the cross-referenced file. The
following FSTRACE4 output illustrates that while the Interface generates an unqualified
SSA to retrieve the EMPINFO segment, it retrieves the PATINFO segment using a qualified
SSA on the IXCOMP index:
set up SSA-Q:
C5D4D7C9 D5C6D640 5C604040 *EMPINFO *- *
set up SSA-Q:
D7C1E3C9 D5C6D640 5C604DC9 E7C3D6D4 *PATINFO *-(IXCOM*
D740407E 40404040 40404040 40404040 *P = *
40404040 40404040 40404040 40404040 * *
40404040 40405D40 * ) *
If the request contains no record selection criteria against the low-order portion of the
index, or it includes a range test on the low order portion of the index, the Interface
constructs a qualified SSA with a range test. For example, the following request contains a
greater-than test on the low-order field in the IXCOMP index:
JOIN SSNALPHA IN EMPDB01 TAG E1 TO SSNALPHA IN PATINFO TAG P1 AS J1
TABLE FILE EMPDB01
PRINT E1.SSNALPHA P1.SSNALPHA E1.EMPID P1.EMPLOYEEID
WHERE E1.EMPID EQ P1.EMPLOYEEID
WHERE E1.LAST_NAME GT P1.LAST_NAME
END
The qualified SSA that the Interface generates to retrieve the PATINFO segment
incorporates a range test on the IXCOMP index:
set up SSA-Q:
C5D4D7C9 D5C6D640 5C604040 *EMPINFO *- *
set up SSA-Q:
D7C1E3C9 D5C6D640 5C604DC9 E7C3D6D4 *PATINFO *-(IXCOM*
D740406E 7E000000 00000000 00000000 *P >= *
00000000 00000000 00000000 00000000 * *
00000000 00005CC9 E7C3D6D4 D740404C * *IXCOMP <*
7EFFFFFF FFFFFFFF FFFFFFFF FFFFFFFF *= *
FFFFFFFF FFFFFFFF FFFFFFFF FFFFFFFF * *
FFFF5D40 * ) *
If an SQL Translator request contains an SQL SELECT statement that includes a join on
the high-order portion of a key or secondary index, the Interface creates a qualified SSA
using the appropriate key or secondary index.
For example, the following request joins the EMPDB01 database to the SSNALPHA,
EMPLOYEEID, and LAST_NAME fields in the PATDB01 database:
SQL
SELECT E1.SSNALPHA, P1.SSNALPHA, E1.EMPID, P1.LAST_NAME, E1.LAST_NAME
FROM EMPDB01 E1, PATINFO P1
WHERE(E1.SSNALPHA = P1.SSNALPHA AND
E1.EMPID = P1.EMPLOYEEID AND
E1.LAST_NAME = P1.LAST_NAME);
END
The Interface retrieves the PATINFO segment using a qualified SSA on the IXCOMP index:
set up SSA-Q:
C5D4D7C9 D5C6D640 5C604040 *EMPINFO *- *
set up SSA-Q:
D7C1E3C9 D5C6D640 5C604DC9 E7C3D6D4 *PATINFO *-(IXCOM*
D740407E 40404040 40404040 40404040 *P = *
40404040 40404040 40404040 40404040 * *
40404040 40405D40 * ) *