SQL Wizard (Java 2-enabled browsers)
The SQL Wizard allows you to build an SQL statement, send it to a remote database server, retrieve the resulting data, if any, save the resulting data, and save the SQL statement for re-use.
The remote database server must be running on an iSeries or AS/400, and an appropriate Java Database Connectivity (JDBC) driver must be present on the client workstation. The Z and I Emulator for Web client and the Database On-Demand client already include a JDBC driver from the AS/400 Toolbox for Java, which allows the Z and I Emulator for Web client or Database On-Demand client to access DB2/400 data on a properly configured iSeries or AS/400 (see the Driver identifier field on the Logon tab).
Database servers running on other host platforms are supported only if the appropriate JDBC driver is installed on the Z and I Emulator for Web or Database On-Demand client workstation (see the Driver identifier field on the Logon tab).
The SQL Wizard's user interface helps you to select the tables to work on, to select the columns from which data is to be returned, to specify conditions for selecting the rows to be processed, to select the columns on which the returned data is to be sorted, and to build a valid SQL statement. A new Expression Builder lets you use operators, predefined functions, constants, and programming constructs to define complex conditions for selecting the rows to be processed. The same Expression Builder lets you define column expressions that automatically perform and display the results of complex computations based on returned values. Returned data (including the results of column expressions) can be either displayed or written into a file. Various file formats are supported, including XML. SQL statements can be labeled and saved for re-use.
You can set default values for some of the fields in this wizard, if your administrator has configured your client or your user id to do so:
- If you are running a Z and I Emulator for Web client see Data Transfer Defaults.
- If you are running a Database On-Demand client see Database On-Demand User Options.
A sequence of tabs guides you through the process of building and running an SQL statement. These tabs are:
- Start
- Logon
- Tables
- Columns
- Joins
- Conditions
- Groups
- Order
- Output (SQL Wizard)
- Review
- Results (SQL Wizard)
- Insert
- Update
Start
- Select
- Click Select to select rows from a table or tables.
- Insert
- Click Insert to insert a row into a table.
- Update
- Click Update to update one or more rows in a table.
- Delete
- Click Delete to delete one or more rows from a table.
Logon
- Database URL
- See Specifying a database URL.
If the SQL Wizard is launched from a 5250 Display session, then the SQL Wizard initially displays in the Database URL field a default database URL containing the name of the current iSeries host.
- User ID
- If a user ID is required to log on to the remote database server, type the user ID into the User ID field. Otherwise leave the field blank.
- Password
- If a password is required to log on to the remote database server, type the password into the Password field. Otherwise leave the field blank.
- Driver identifier
- The listbox includes the names of all the JDBC drivers that have been registered
with Z and I Emulator for Web. Expand the listbox and click the name of the
registered JDBC driver that you want to use, or click Other to specify another
driver.
For general information on using a JDBC driver with the Z and I Emulator for Web client or the Database On-Demand client, see Registering a JDBC driver.
- Other
- This field must contain the fully qualified Java class name of the JDBC driver
that you specify in the Driver identifier field.
- If, in the Driver identifier field, you select the name of a registered driver, then the SQL Wizard displays the fully qualified Java class name for you in this field and does not allow you to modify it.
- But if, in the Driver identifier field, you selected Other, then you must
type the fully qualified class name of the driver into this field. Case is
significant (
COM
is different fromcom
). If you do not know the fully qualified class name, contact the provider of the driver.
The driver must be installed on the client workstation and must be accessible to the Z and I Emulator for Web client or Database On-Demand client (see Deploying a JDBC driver).
- Table Filter (SQL Wizard)
- This field does not appear unless enabled by the Z and I Emulator for Web
administrator.
Type a comma-separated list of schema names, schema filters, table names, and table filters. For assistance, see Examples. For a detailed description of schema names, schema filters, table names, and table filters, see Specifying a list of available schemas and tables.
The comma-separated list that you type into this field specifies the schemas and tables that you want to appear in the List of available tables on the Tables tab of the SQL Wizard after you log on to the database.
To use the default value, leave this field blank.
This Table Filter field is one of several levels of filtering for the list of available tables (see Level 4. Value for the current SQL or File Upload statement). - Connect and Disconnect
- Click Connect to connect to the remote database server.
If the connection fails and you see a Java error message or are running Netscape 4.7x, see Problems logging on or connecting.
In certain circumstances you may encounter the error message
SQL Assist Exception
. See SQL Assist Exception.Click Disconnect to disconnect from the remote database server.
Tables
- Available tables
- This is a list of the schemas and tables that are available to be used in the
current SQL statement (see List of available
tables).
The contents of this list are determined by several levels of filtering (see Levels of filtering for schema names and table names).
You will notice that:
- For Insert, Update, and Delete operations, you can select only one table.
- For a Select operation, you can select multiple tables.
To move one or more tables to the list of Selected tables:
- Select the table name or names.
- Click >.
To expand or collapse the contents of a schema in the list, click the
+
or-
symbol to the left of the schema name. - Selected tables
- This list contains the table or tables to which the SQL statement applies.
If the type of the SQL statement is Select, the SQL Wizard adds the names of the selected tables to the FROM clause of the SQL statement on the Review tab.
A correlation name is an alternate name for a table. When you select the same table (for example, AA01) more than once, the SQL Wizard automatically creates a correlation name (for example, AA01_1). To create a correlation name manually:
- Click a table name.
- Type the correlation name that you want to use.
To move one or more table names back to the list of Available tables:
- Select the table name or names.
- Click <.
To move all tables back to the list of Available tables, click
<<
. - Filter Schemas
- Click Filter Schemas to select the names of schemas that you want to appear in the list of Available tables.
- Filter Tables
- Click Filter tables to specify the names or types of tables that you want to appear in Available tables.
- Refresh
- Click Refresh to refresh the list of tables in Available tables.
Columns
Use the Columns tab to select the columns that you want included in the data returned by the SQL statement. The Columns tab is available only when the type of the SQL statement is Select.
When you select a column on the Columns tab, the SQL Wizard adds the column name to the SELECT clause of the SQL statement on the Review tab.
If you do not select any columns on the Columns tab, then by default the SQL statement
selects all the columns in the table or tables that you specify. The reason is
that the default SQL statement for a Select operation is SELECT * from
tablename
, where *
means "all columns".
To select one or more columns:
- Click one or more columns in the Available columns list. The list contains
all the columns of all the tables selected on the Tables tab. (To expand or collapse
the contents of a table in the list, click the
+
or-
symbol to the left of the table name.) - Click > to add the selected column or columns to the list of Selected columns.
Click >> to add all the columns in the list of
Available columnsto the list of
Selected columns.
To remove one or more columns from the list of Selected columns
- Click one or more columns in the Selected columns list.
- Click < to remove the selected column or columns from the list of Selected columns.
Click << to remove all the selected columns from the list of
Selected columns.
- Move Up, Move Down
- Use these buttons to change the order in which the selected columns are
displayed.
When you run the SQL statement, Z and I Emulator for Web displays the columns left to right on the Results tab in the same order as you specify on the Columns tab.
- Add
- Use this button to launch the Expression Builder and build a column expression.
A column expression is an expression whose evaluated value you want to appear as a column in the result.
Column expressions are similar to operations in a software spreadsheet, because you can combine the values in the columns of a table with:
- Operators
- Functions
- Constants
- Programming constructs
Column expressions provide an extremely powerful tool for automatically performing and displaying the results of computations based on table values.
As a very simple example, if table HODTEST.AA01 contains a column named SALES, and you want to compute the value of a 6% sales commission that is based on the value stored in the SALES column of each row of the table, then you can use the Expression Builder to build or type the expression
HODTEST.AA01.SALES * .06
. When you run the SQL statement, a column of output is generated for this column expression showing the sales commission for each entry in the table.You can also use multiple columns in a column expression. For example, to add the values in the columns SALARY, BONUS, and COMM, you could build or type the column expression
SALARY + BONUS + COMM
.For more information see Building expressions.
- Edit
- Use this button to edit an already existing column expression. Follow these
steps:
- Click the column expression that you want to edit.
- Click Edit.
- Delete
- Use this button to delete a column expression. Follow these steps:
- Click the column expression that you want to delete.
- Click Delete.
Joins
A join is a mechanism for selecting which rows from two related tables are included in an SQL statement, based on equality (or some other relationship, such as inequality, greater than, and so on) between the contents of selected columns.
The Joins tab is available only when the type of the SQL statement is Select.
The inner area of the Joins tab displays a small window for each of the tables that you have included in the list of selected tables on the Tables tab. In the small window for each table the names of the columns of that table are listed.
In the outer area of the Joins tab, use the buttons on the right side of the tab to create and manipulate joins.
The colored connecting lines have the following meanings:
- Gray - A possible join (click Join to create a join).
- Red - An impossible join. The connected columns have incompatible data types.
- Blue - An existing join.
- Green - The currently selected join.
- Join
- Click Join to create a new join. Follow these steps:
- Select a column name in the first table that you want to join.
- Select a column name in the second table that you want to join.
- If a join is possible between the two selected columns, then the SQL Wizard displays a gray line between the column names.
- If a join is not possible between the two selected columns, because the two selected columns have different data types, then the SQL Wizard displays a red line between the column names.
- Use the Join Type key to select a join type (see Join Type below). The default type is an inner join.
- Click Join to create the join. The SQL Wizard displays a blue line between the column names to indicate a completed join.
- <, >
- Use these buttons to select a Join to operate on. The SQL Wizard indicates the currently selected join by displaying a green line between the column names.
- Unjoin
- Use this button to dissolve an existing join. Follow these steps:
- Select an existing join using the > or < button.
- Click Unjoin.
- Join Type
- Use this button to select the join operator and the join type. The join operator
is one of the following:
- =
- <>
- <
- >
- <=
- >=
- Inner join. The following rows are selected for processing:
- The rows of the left table and the right table in which the contents of the joined columns are equal.
- Left outer join. The following rows are selected for processing:
- All the rows of the left table.
- The rows from the right table in which the contents of the joined columns are equal.
- Right outer join. The following rows are selected for processing:
- The rows from the left table in which the contents of the joined columns are equal.
- All the rows of the right table.
- Full outer join. The following rows are selected for processing:
- All the rows of the left table.
- All the rows of the right table.
Conditions
Use the Conditions tab for the following two purposes:
- To exclude duplicate rows from the results (SELECT DISTINCT).
- To specify one or more conditions for selecting rows. Rows that meet the conditions that you specify are displayed on the Results tab (Select type), updated (Update type), or deleted (Delete type).
The Conditions tab is available only when the type of the SQL statement is Select, Update, or Delete.
To exclude duplicate rows from the results of a Select statement, select the checkbox Exclude duplicate rows (SELECT DISTINCT). When you select this checkbox, the SQL Wizard adds the DISTINCT modifier to the SELECT verb of the SQL statement on the Review tab.
A condition is a criterion that you define for the remote database server to use in selecting rows from the tables that you have included in the list of selected tables on the Tables tab. You can specify one or more conditions. If a row meets all the conditions that you specify, then the remote database server includes that row in the operation (Select, Update, or Delete).
Use this tab to specify all the conditions for a single SQL statement.
You can use the controls to build a condition, or you can type a condition directly into the Conditions field.
Follow these steps to build a condition using the controls:
-
Select the column for the search from the Available columns list.
-
Select an operator from the Operators list.
-
Type one or more values in the Values list. Enter one value per line. Click Clear to remove all of the values from the Values list.
If you selected the Between operator in the Operators list, you must enter two values per line in the Values list. The values are separated by an ampersand (&).
You can search for appropriate values by clicking Find.
You can specify host variables in the Values list. To do this, click on a line in the Values list, then click Add Variable. The Add Variable window is displayed. Type the variable name, then click OK. The Add Variables window closes, and the variable is added to the Values list on the Conditions tab.
You can specify parameters in the Values list. If a parameter is specified, its value is used in the search condition. A parameter is specified in the format :parm, where parm is the parameter name. For example, :empid is a valid specification for a parameter named empid.
-
Click Add to add the condition to the Conditions field.
-
To specify additional search conditions for your statement, click And or Or. Then, build the second search condition as described in the previous steps. When you click Add, the subsequent conditions are appended to those that already are displayed in the Conditions field.
To type a condition into the Conditions field, move the input focus to the Conditions field and type the condition.
You can also use the Conditions field to modify or delete a condition that you built using the controls. To modify a condition, use the insert, delete, backspace, cursor movement, and letter keys to add or remove text. To delete a condition, use the delete key or backspace key, or follow these steps:
- Use the mouse to highlight the condition.
- Press the Delete key.
You can also copy and paste text between the system clipboard and the Conditions field.
To build complex conditions, click Advanced Expression to open the Expression Builder - Conditions window (see Building expressions).
Groups
Use the Groups page to specify which columns to use for grouping results. When you group results, the data in the result set is divided into groups by the values in the specified columns. One row of data is returned for each group.
The Groups tab is available only when the type of the SQL statement is a Select statement.
You can group more than one column. However, the groups will not be sorted; use the Order page to sort the result set.
To include groups in your result set:
-
Select the Include grouping columns checkbox. The columns that are specified in the Selected columns list on the Columns page are displayed in the Grouping columns list. These are mandatory grouping columns.
If this checkbox is selected and no columns are specified in the Selected columns list on the Columns page, then all of the available columns are displayed in the Grouping columns list.
-
To add more columns to the Grouping columns list:
-
Select one or more columns in the Available columns list. The list displays all of the columns in a particular table. You can expand or collapse a table to display or hide the columns that are included in that table.
-
Click > to add the columns to the Grouping columns list. Click >> to add all of the columns from all of the tables in the Available columns list to the Grouping columns list .
-
-
To remove one or more columns from the Selected columns list, click on the columns, then click <. To remove all of the columns from the Selected columns list, click <<.
To remove mandatory grouping columns from the Selected columns list on the Groups page, you must first remove those columns from the Selected columns list on the Columns page.
Filtering grouping results
You can eliminate unwanted grouped data by filtering the results that are returned by the grouping columns. To do this, either type the conditions in the Group conditions field, or click Advanced Expression to build one or more conditions.
Order
Use the Order tab to sort the rows that appear on the Output tab. The rows on the Output tab are the rows returned by the remote database server when you run an SQL Select statement.
The Order tab is available only when the type of the SQL statement is Select.
Normally (that is, without the Order tab), the rows on the Output tab are displayed in whatever order the rows happen to occur in the table or tables to which they belong.
With the Order tab, you can select one or more columns that you want to be used for sorting the returned rows. For example, if you select a column named OBJECTID and specify ascending order, and if the contents of OBJECTID are positive numbers, then the returned rows are sorted starting with the row or rows that have the smallest value in OBJECTID (such as 00001), followed by the row or rows containing the next smallest value in OBJECTID (such as 00004), and so on.
In addition:
- You can select more than one column for sorting the returned rows.
- The rows are first sorted according to the contents of the first column that you specify (such as OBJECTID).
- Then, for each group of rows that have the same value in the first column (for example, for all rows that have a value of 00007 in the column OBJECTID), the rows are sorted according to the values in the second column that you specify (such as SEVRITY).
- This process is continued for each additional column that you specify.
- For each column, you can specify sorting in ascending order or descending order.
To select a column for sorting:
- Select one or more columns in the list of Available columns.
- Click > to move the column or columns to the list of Selected columns.
- Use the Move Up and Move Down buttons to change the order of the columns.
- Use the Order column to specify sorting in ascending or descending order.
The radio button Display output columns only is always selected. This means that the Output tab displays only columns that are included in the list of Selected columns on the Columns tab (including the results of any column expressions).
Review
The primary use of this tab is to allow you to run the generated SQL statement. You can also do the following operations (unless the administrator has disabled the operation):
- Review or edit the generated SQL statement.
- Copy the generated SQL statement to the clipboard. Once copied, the contents of the clipboard can be pasted into any other application that accepts text data from the clipboard. This is useful if you have another application that will execute a SQL query, but does not provide for easy generation or testing of a SQL query.
- Save the SQL statement for reuse at a later time. You can use this to save common SQL statements that you run multiple times. Queries for getting monthly reports of sales or generating lists of customers who made purchases in the last six months are examples of queries that are good candidates for saving.
Insert
Use the Insert tab to specify the values that you want to add to the specified table. When you insert values, a row is created in the table specified on the Tables tab, and the values that you specify on the Insert tab are placed into that row.
To specify the values to insert,
type the value that will be inserted for that column in the Value field.You must type a value for every non-null colu mn. Non-null columns are indicated by a plus sign (+).
You do not have to provide values for null columns.
Update
Use the Update tab to specify the values that you want to change in the specified table. When you update values, the values that you specify on the Update tab are placed into the table specified on the Tables tab.
To specify the values to change,
in the Value field, type the value that will be u pdated for that column.You do not have to update all the values in a row ; you only need to enter values for the columns that you want to update.
You must enter a value for at least one column.