The Dynamic JOIN Command

With the FOCUS dynamic JOIN command, you can reference two or more related data sources in a single FOCUS report request. The data sources 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:

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 adapters installed, you can join an IMS database to any other joinable data source such as a DB2 table, a VSAM file, or a FOCUS data source.

Two types of dynamic JOINs are available. The difference between the two depends on the cross-referenced file and its key values:

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.

WITHrfield

Is any real field in the same segment as the DEFINE field. This 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.

TAGtag

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.

INcrfile

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.

For information on the number of concurrent joins supported, see the Creating Reports manual.

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 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 Creating Reports 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 fields in the host file (fielda1, fielda2) has, at most, one possible corresponding value of the join field in the cross-referenced file (fieldb1, fieldb2).

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.

See the Creating Reports manual for information about the number of concurrent joins supported.

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 Creating Reports manual for a complete discussion of short path behavior.

Join Optimization

To determine the best access path into the database, the adapter examines the record selection criteria in each report request (see Auto Index Selection). When the request references a join, the adapter 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 adapter 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 adapter 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 fieldof 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 trace output illustrates that while the adapter 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 adapter 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 adapter 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 adapter 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 adapter 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                        *      )         *