How to:
Reference: |
If you select more than one data source for your data flow, you must use a join object to connect them.
You need to specify the common column or columns that will link the tables, as well as the type of join.
You can perform an inner, left outer, right outer, full outer, or cross join.
Note: You can create a recursive join by selecting the same data source twice.
When you link data sources, DataMigrator identifies each table with Tn, where n is the order in which the table was chosen. For example, the first source chosen would be identified as T1, the second as T2, and so on.
DataMigrator creates join syntax in ANSI 92 SQL. For example, when selecting the tables Product and Vendor, and columns VENDORNUM and VENDOR_NUM, DataMigrator generates the SQL syntax:
SELECT...FROM Product t1 INNER JOIN VENDORS T2 on t1.vendornum=T2.vendor_num
iWay lets you create joins across similar data sources, different data sources, and across servers (also known as cross-platform joins).
In general, cross-platform joins and joins without key fields can create many intermediate files, depending on the number of tables joined. This issue may be less significant for some iWay platforms. For example, the Server for z/OS can use hyperspaces and alternate sort utilities. If performance is a concern, you should carefully evaluate the impact of your joins.
To improve performance, try any of the following:
The default settings of the DMC automatically create join objects. If join objects are not being created, you can change your settings so that join objects are created automatically:
Join objects are created automatically when you drag additional data sources into the data flow workspace if the DMC is using the default settings.
You can also add a join by right-clicking in the data flow workspace, and selecting Add Join. In that case, you must connect the data source to it by right-clicking it and dragging it to the join object.
The Join Editor window opens, listing the columns from the two data sources in the Left and Right Source Columns lists. For more information, see Join Editor Window.
If the two data sources have a column with the same name and format and that column is a key column in one of the sources, an inner join will automatically be created for those columns. This default join can be changed, if necessary.
The equality condition will appear in the Expression box under Join Conditions.
DataMigrator supports both equi and non-equi (conditional) joins. In an equi-join, the condition is one of equality, while a non-equi join uses any other condition, such as greater than, less than, and so on.
Note: You can delete the condition from the Expression box by selecting it and clicking the Delete Condition button.
You can edit the condition by selecting it in the Expression box and clicking the Edit condition button.
The type of join you create will be graphically displayed in the workspace.
The equality condition will appear in the Expression box under Join Conditions.
A data flow with three joined sources looks like this:
The Join Calculator opens.
The Join Calculator opens.
The Join Editor window is available from the workspace by double-clicking a join object or by right-clicking a join object and selecting Join Editor.
The Join Editor window contains the following fields and options:
Displays the available columns from the first data source.
Next to each column name there is a symbol that indicates regular columns, key columns, index columns, or virtual columns.
Note: Clicking a column heading will sort the column grid on that heading.
Displays the available columns from the second data source.
The columns symbols are:
Symbol |
Description |
---|---|
|
Key column |
|
Regular column |
|
Index column |
|
Virtual column |
Establishes the relationship between the selected columns as equal.
Allows you to search for a column.
Opens the Join Calculator to create a condition.
Deletes the selected condition.
Opens the Join Calculator to edit a condition.
Opens the Join Analysis window where the join condition is analyzed to show the number of rows and distinct rows that would result from a left outer, inner, and right outer join.
Tests the join conditions.
Note: If you have not selected columns, test join displays the keys columns from the tables in the join. If you have selected columns, it displays the columns selected. If neither table has keys and no columns were selected, test join displays an empty answer set.
Lists the conditions by which the two data sources are connected.
Enables you to select the join type from the drop-down menu or by clicking in the join diagram, which graphically represents the type of join being used.
The Join Calculator is available from the Join Editor window by selecting a row and clicking the Calculator button.
The Join Calculator contains the following fields and options:
Displays the expression.
Displays available columns and variables.
Displays a list of SQL functions that are available for your transformations.
Allows you to specify parameters for the function through a dialog box when creating or editing a transformation.
Insert numbers and operators.
The following operators are available in both the Join and Filter calculators:
Specifies data will be returned if the value of the field falls between the values entered on either side of the word AND. For example:
RETAIL BETWEEN 20 AND 30
For alphanumeric data, specifies data will be returned if the field value is a substring of the right operand.
Specifies that records with a null value for the specified column are returned.
Returns data if the value of the field matches the character string on the right operand. In the character string, use the underscore character (_) to represent any single character, and the percent character (%) to represent any sequence of characters. For example, the following string returns the value TRUE if LAST_NAME contains the string SMITH anywhere inside it (for example, SMITH, SMITHSON, SILVERSMITH):
LAST_NAME LIKE '%SMITH%'
The following string returns the value TRUE if LAST_NAME is exactly five characters long and the first letter is S.
LAST_NAME LIKE 'S_ _ _ _'
Returns data if the value of the field is less than the right operand. For example, the following string restricts the query to those employees who earn less than $30,000:
SALARY < 30000
Returns if the value of the field is less than or equal to the right operand.
Specifies data if the field value equals the right operand.
Is used in combination with the BETWEEN operator.
Is used in combination with other relational operators to negate them. For example, the following string returns all records where the value of LAST_NAME is not null:
LAST_NAME IS NOT NULL
Specifies data will be returned if the value of the field is greater than the right operand.
Specifies data will be returned if the value of the field is greater than or equal to the right operand.
Specifies data will be returned if the left operand is not equal to the right operand.
Inserts the operator OR.
Adds parentheses.
Inserts two single quotation marks. Enter alphanumeric test values between these.
Converts selected text to uppercase.
Converts selected text to lowercase.
Opens the Date Editor window which lets you use the current date, or specify a date from the a calendar.
Opens the Date Editor window which lets you use the current date and time, or specify a date and time from the calendar.
The Join Analysis window is available from the Join Editor window by clicking on the Join Analysis button.
The Middle column shows the number of rows (Count) and unique rows (Distinct Count) that the entered join criteria would return for an Inner join.
The Left and Right columns show the number of additional values and rows that would be returned for their respective join type.
From the example shown in the above image, a Left outer join would return 60 additional rows with 7 unique values, in addition to the rows returned by the inner join.
Note that all of the numbers in each row/column are also hotlinks. For example, if you double-click 7, a Test Results window opens, as shown in the image below. This window shows the 7 values for EMPID that were in the left source, but not found in the right source.
After a join has been added to the workspace, the following options are available in the shortcut menu:
Opens a window that allows you to view the join expression and sample data.
Opens the Join Editor which allows you to create or edit the join conditions.
Opens a submenu with the following options. Note that these options are only available when the Join object does not already have two inputs.
Deletes the selected Join object.
Opens the properties panel for the selected Join object.
This option is only available if there is an error. Displays a dialog box with an error message. For more information, click Details.
The Test Individual Join window is available from the Join Editor window by selecting a join and clicking the Test Join button. It shows sample data from the join.
iWay Software |