Link Inner Join (Magic xpa 3.x)
The Link Inner Join definition uses the RDBMS Join capabilities for the reading and joining of different database tables, in conjunction with Magic xpa mechanisms, in order to create a many-to-one relationship. When the user updates a record or when a Magic xpa recompute is taking place, the Link Inner Join definition will behave as a Link Query operation.
The Link Inner Join definition is valid only for SQL databases. Only tables from the same database can be joined.
Note: A Link Join operation cannot be used on a Web Service database.
The Link Inner Join definition will generate an SQL inner join statement among all the participating tables.
In a regular Link definition (Query), for each row being read from the Main Source, Magic xpa adds a second SELECT statement to retrieve the linked row. The rows for the Main Source are read by a loop that fetches all the rows as described above. To perform the link, every time the FETCH operation of a row in the Main Source is performed, another cursor is opened to retrieve the rows of the linked table. If there are two links on a Main Source, Magic xpa opens a cursor for the Main Source and in a loop opens a cursor per each linked row. For example, a task with 10 records in the Main Source and 2 link tables will open 21 cursors: one cursor to fetch all the 100 records in the Main Source, 10 cursors to fetch one record at a time from the first linked table and another 10 cursors to fetch one record at a time from the second linked table.
The differences between a Link operation and a Link Inner Join operation are:
A Join of several tables makes them into one entity, and only one cursor will be opened and retrieved.
If the row in the table does not exist in the joined table, the row will not be retrieved.
A regular link in Magic xpa results in slower response time than a Link Inner Join because the regular link communicates and requests more from the SQL Database than a Link Inner Join.
It is recommended that you:
-
Use a view that joins tables in reports and read-only queries.
-
Cache the linked tables, especially if the linked tables contain only a few rows, or if the linked tables result in repeatable identical rows.
Link Inner Join differs from Link Query in the following ways:
-
In Link Query, a sub-select is done to fetch each linked row. If a linked row is not found, the corresponding row from the Main Source is still available.
-
In Link Inner Join, an inner join SELECT statement is used for the main and joined table. For this reason, integrity constraints are important.
When a data view is created for the task, Magic xpa generates a Join statement for all of the Joined tables. Magic xpa creates a WHERE clause that consists of the constants and the matching clauses with the names of the columns from the Main source and the Joined table. Then Magic xpa builds the rest of the data view.
When a Link Inner Join generates a Join Statement, it will always return a True variable. When the Link Inner Join behaves as Link Query, it will attempt to establish a link where data from the linked record's selected columns are included in the record data view.
All the index segments of the joined table must be selected and each of the index segments should have a Locate expression.
For the joined tables, an exact range is needed. Therefore, the Minimum Locate expression must be equal to the Maximum Locate expression.
When the Locate expression is a single variable (from either the Main Source or another Join Link), the variable reference will be used in the generated SQL statement. Therefore, the Link will be done according to the variable value per record. Otherwise, the expression will be computed when the task is initialized (before the Task Prefix) and the result value will be used in the generated SQL statement. Therefore, the Link will be done according to a constant value.
Because DB2 requires separate locks for each table, Link Inner Join in Batch tasks with immediate locking strategies will be implemented as a Link Inner Join operation without a lock for each table joined, and as a Link Query operation for each row fetched.
Note that when Magic xpa generates a SELECT statement with multiple tables, Magic xpa uses aliases for the table names, such as in A for a Main Source, B for the first join and so on.
The cache that the Link Inner Join definition utilizes is cleared in the following cases:
-
When changing the index
-
When a column from a joined table is updated
-
When a locate/range/sort is performed
-
When changing the task mode to Create
Link Inner Join - Locking
