To access database (DB2) tables, the Arbutus zSeries Server must be configured with the optional DB2 interface. Choose the DB2 radio button in the Select Server Data Source panel and click [Next].
The Select Data Source or Subsystem panel is displayed. To define relational (DB2) tables, you must specify the data source or database subsystem. A list of data sources and subsystems that were defined for the Arbutus Server will be displayed.
Note: The subsystem panel only appears if you have access to more than one database subsystem, otherwise it is automatically bypassed.
Note: No authorization checking is performed as subsystem access is controlled by the relational database.
By default, you can choose up to five tables at one time.
Note: To increase the number of tables that can be joined at one time, edit the Table Option Maximum Relational Joined Tables.
Where applicable, available tables displayed may be restricted to Tables, Queries, and/or System Tables (or Aliases). Additionally, where applicable, tables may be selected from different available schemas.
Note: As each table is selected, the Selected Tables list displays the selected table names and their internal Analyzer alias (For example: “dbo.customer (A)”, “dbo.employee (B)”). When multiple tables are selected and joined, the Join Criteria list will display the internal Analyzer alias followed by the field name (For example: “A.CustomerID = B.EmployeeID”)
Check the Edit Query checkbox to advance through to the Select Columns panel and the Where and Order Clause panel, otherwise clicking [Next] will bypass these panels and advance you directly to the Final panel.
The Wizard prompts you to specify the key field relationships between tables being joined when more than one table is selected.
Note: Analyzer performs a many-to-one join between multiple tables. Columns that can be used as key fields are identified in your table layout using the KEY and NATIVE field modifiers discussed in Native and Key Fields.
You are required to select at least one column from each table selected.
Use the Table Data pull-down menu to select columns from each table if more than one table was selected. Click [Next] to proceed.
Note: You will only be prompted to select columns if the “Edit Query” checkbox is selected on the Select Tables panel. Otherwise this panel is bypassed and all fields are selected.
You can specify SQL Select or Where and Order clauses to select or filter and order the data.
Note: You will only be prompted to edit the SQL Select statement or specify Where and Order clause if the “Edit Query” checkbox is selected on the Select Tables panel. Otherwise this panel is bypassed.
Note: For the Arbutus zSeries Server, if the DB2 Version 9.1 for z/OS ODBC driver is installed at your site, the “Edit SQL Select” and “Validate” options will be available, otherwise they are not.
Note: To subsequently view or edit the SQL Select or Condition, Where and Order clauses, open the table layout, click Edit and select Table Layout and then choose the Table Options tab. See Table Options
Note: To subsequently override the SQL Select or Condition, Where and Order clauses using system variables on the command line or procedures see Overriding the SQL Select Statement and Modifying the Database Condition, Where and Order Clauses.
The underlying SQL Select statement can edited in the Wizard by checking the Edit SQL Select checkbox on the Where and Order clause panel in the Wizard. clicking [Next] allows you to edit the underlying SQL Select statement for the selected table(s).
Note: Edit the SQL Select statement with extreme caution!
Analyzer supports standard ANSI SQL. The Select statement is permanently stored within the table layout (see notes below). Click Validate to verify the edited SQL Select clause.
It is useful to ensure that all required fields were selected from all tables prior to editing the SQL Select statement. If not, simply click [Back] to get to the Select Columns dialog and then select all necessary fields.
In the Where and Order clause panel in the Wizard, you can specify the DB2 (database) Where and Order clause information using standard ANSI SQL. This information is then permanently stored in the table layout.
Note: To add the full column name to the Where or Order clause, place the cursor in the desired clause text box and double click on the desired column name.
Note: The DB Where and Order variables should only reference native key fields, and all DB variables should be used on live data sources only. For more information on native key fields, see Native and Key Fields.
| • | In the Order Clause text box, enter a Structured Query Language (SQL) “Order” statement if you want to order the records imported. This SQL Order Clause only applies to the selected table. |
| • | To check the validity of the entered Where and Order clause, click the “Validate” button. Errors are reported in a text box. |
Click [Next]. The Wizard proceeds to the Final panel. See Final Panel.