Creating SQL Queries the Easy Way with SQL Assist for DB2 UDB Version 8.1
© 2002 International Business Machines Corporation. All rights reserved.
Do you have trouble remembering complex SQL syntax? Do you spend too much time dealing with SQL query development and maintenance? Now you can spend less time manually developing your SQL queries and more time using them to harness the full potential of your IBM® DB2® RDBMS by using DB2 UDB Version 8.1 SQL Assist.
SQL Assist is a powerful utility provided with DB2 that can help you generate, modify, and maintain SQL. This newly redesigned SQL utility is easy to use and offers a variety of functionality to support your SQL needs:
- Re-designed and easy to use multiple view user interface
- Round-trip query editing for working with pre-existing SQL
- Join assistance functionality to highlight potential table joins
- Condition builder for creating complex parenthesized WHERE clauses
- Syntax checking for SQL validation
- Column data type information for inserting or updating values into tables
- Quick and easy point and click functionality
SQL Assist product overview
Designed to meet the functionality needs of an expert, while easy enough for beginners to use and learn with, SQL Assist offers more than the average SQL query builder and is seamlessly integrated with much of DB2. You can conveniently launch SQL Assist from many DB2 centers that require SQL query input. SQL Assist helps you generate SQL and then plug it directly back into the DB2 center you need to use it in.
Figure 1. DB2 Centers
SQL Assist is available from within the following DB2 centers. (For additional information on how to launch SQL Assist from a DB2 center, see the step-by-step tutorial.)
Use SQL Assist in the Command Center to run SQL statements. SQL Assist can be used in the Interactive tab to help create the SQL that will be returned to the text area.
Use SQL Assist in the Control Center to help you build statements to use in common Control Center tasks, such as: CREATE VIEW, CREATE TRIGGER, EXPORT, and LOAD. You can launch SQL Assist from four different points within this center.
Use SQL Assist in the Development Center when you are working with stored procedures that contain SQL statements. SQL Assist helps you build a variety of supported stored procedure statement types.
Use SQL Assist in the Replication Center to work with SQL scripts that the Replication Center generates. You can use SQL Assist to modify and rerun the scripts so that defining new registrations and subscriptions is even easier.
The SQL Assist user interface consists of three views which allow you to see an outline of available database elements, choose from options to build your query, and view or edit the SQL code.
Figure 2. SQL Assist views
The Outline view contains a high-level tree structure representation of the current SQL statement. Use the Outline view to visually examine an outline of the SQL statement and navigate through the steps of building the SQL statement. Select a node in the Outline view to display details for that element in the Details area.
The Details view displays the detailed info about the node you have selected from the Outline view. Use the Details area to add or change elements in a selected portion of your SQL statement. The Details area changes based on what node you select in the Outline view. When you select a node in the Outline view and make changes in the Details area, the SQL code is generated in the SQL code view.
SQL code view
The SQL code view contains the SQL statement that is generated based on the contents of the Outline view and the changes that you made in the Details area. The statement is syntax-highlighted. When you type in the SQL code view, the rest of the interface becomes disabled until you commit your changes by clicking the "Check" button. Your changes are then parsed and the interface is updated to match the SQL. Or, if you decide you don't like your changes, you can click the "Undo Edit" button to return the SQL to the state it was before you started editing it.
By using the Outline view and Details area, you can focus on one part of your SQL statement at a time. Because the Outline view lists the subparts of the SQL statement, you can easily navigate to each subpart of the statement by selecting an item in the Outline view. When you select an item from the Outline view, the Details area displays the options that are available for working with that particular piece of the statement. For example, if you select the "FROM (Source tables)" item in the Outline view, the Details area will display the list of available source tables that you can choose from.
Figure 3. Details area
If you've worked with previous versions of SQL Assist, you'll notice some of the same easy-to-use, sloshbucket interfaces on the Details area panels. By selecting the top item in the Outline view, SQL Statement Properties, you can change the type of SQL statement you are working with and view the database connection information. (This feature may not be available depending on the context from which SQL Assist is launched.) By selecting other items in the Outline view, you can work with the subparts of the current SQL statement, such as the result columns and the WHERE clause in a SELECT statement.
SQL Assist also allows you to work with your SQL statement as a whole. With the new layout, you can see your entire SQL statement at all times while the new round-tripping capability lets you type your statement directly into the SQL code view and see it reflected back into the Details area option selections. You can also paste text from the clipboard into the SQL code area. Pasting text in the SQL code view can be helpful, for example, if you have an existing SQL statement that you want to modify in SQL Assist instead of starting from scratch. Whenever you type in the SQL code view, the panels from the Details area are disabled until you validate the statement by using the Check feature. If the statement is determined to be valid and supported, the Details area will be enabled and reflect the changes made in the SQL code view. At this point, you can resume working with the panels in the Details area or continue typing in the SQL code area.
After completing the SQL query you can run it from the SQL code view within SQL Assist. After you finish working on your query, press the OK button and the statement you created is returned to the text area in the DB2 center from which you launched so you can put it to use.
Round-trip query editing
Many query builder tools work well if you are creating a new SQL query from scratch. But what if you need to modify an existing query? What if the query was generated by a different tool or was hand-written directly in SQL? SQL Assist can handle these situations because it supports round-trip query editing. This means that you can create or modify your query using the SQL Assist user interface, by typing SQL directly, or by any combination of the two. SQL Assist allows you to work on an existing SQL query, regardless of the tool or editor used to create it.
Round-trip query editing also enables you to tweak your query directly in the SQL code view while you are creating it using the interface. Sometimes it's quicker and easier to edit small pieces of the code than to work with an interface.
For example, if you added the following predicate to your WHERE clause:
WORKDEPT IN ('B01', 'E01', 'E21')
but you realize that 'E01' should be 'E12'. You can edit the value directly in the SQL code view and avoid having to use the interface to make the changes.
Figure 4. Editing SQL directly
The SQL code view contains a syntax-aware editor that colorizes SQL keywords, identifiers, and literals. It supports the usual text editing commands such as Control-C (copy), Control-X (cut), and Control-V (paste), plus line-oriented editing commands such as Alt-L (mark line), Alt-C (copy lines), Alt-M (move lines), and Alt-U (unmark lines).
Note that there are restrictions on the SQL that you can enter in SQL code view. The subset of SQL that is supported is the same as that which you can generate using the GUI. In the current version of SQL Assist, this means that you can enter almost any SQL as long as it is a single "sub select" (the query contains one SELECT keyword), or a single INSERT, UPDATE, or DELETE statement.
SQL Assist aids you in creating table joins (when your query involves columns from more than one source table). By joining tables, you indicate how rows in one table correspond to rows in another table. While the concept of a JOIN is relatively simple, building a query with a JOIN can be cumbersome if you do not have the ability to see what columns are available in your tables. If the tables are not joined, your query is likely to perform poorly and return more rows than you want. The JOIN assistance feature in SQL Assist can help you ensure you get the expected results from you joins
The SQL Assist interface provides a detailed listing of all the available JOIN options for the particular query you are working with. In addition to the JOIN interface options, SQL Assist will provide suggestions regarding valid ways in which your tables can be joined by highlighting potential joins. To use JOIN assistance, select the tables you want to work with and click the Join Tables button on the "FROM (Source Tables)" details area.
Figure 4. Using SQL Assist to join tables
In your database, some or all of the tables may have been defined with built-in relationship information, using primary and foreign key columns (referential constraints). If the tables you are using have been defined with primary and foreign key columns, JOIN assistance suggests how to join the tables.
The Join Tables window lists the tables that are in your FROM clause. You can select a single table to view the corresponding column details, or you can select two tables, join them, and define the JOIN expressions. The joined tables are displayed in a tree structure in the Join Tables window.
Figure 5. Join Tables window
SQL Assist also helps you choose the JOIN type. The JOIN type indicates how your query deals with rows in the tables where a row in one table does not have a corresponding row in another table. It is possible to explicitly specify an inner, left outer, right outer, or full JOIN in the interface. But, if you are not sure what to use, you can select the type of JOIN based on descriptive text explaining precisely what logic is used to determine if rows of the joined tables are included.
When you finish joining your tables, click the OK button and the joins will be added to your query. If all of the joins are inner joins, then the joins will be added to the WHERE clause. If you defined at least one outer JOIN, the joins will be added to the FROM clause.
Creating WHERE and HAVING clauses to specify criteria to filter rows of data is a critical step in the query building process. The condition builder in SQL Assist provides you with an easy way to deal with this common SQL task. Most condition builders in query generation tools have a straightforward, grid-style interface that is most suited to building simple conditions. SQL Assist, however, can handle more complex SQL criteria that require parenthesizing or negating of single predicates or groups of predicates.
Figure 6. Inserting a WHERE clause
The newly designed SQL Assist condition builder packs a lot of capability into a compact, user-friendly layout. With this utility, you can build your complex search condition one predicate at a time. When you select the predicate that you want to work with, SQL Assist takes the syntax guesswork out of the equation by tailoring the interface to the specific predicate you requested. For example, if you need a predicate that specifies a range of values, select the BETWEEN operator in the condition builder. The SQL Assist interface will then adapt to provide two entry fields for the upper and lower bounds of the range. If you need to specify a set of possible values, select the IN operator and the interface will adapt to allow you to enter the values in a list.
When working with predicates, it is likely that you will need to refer to values contained in columns in your database. For example, you might have a predicate that selects rows with a value in a certain column that is before or after a specific date. The problem is that dates are stored in inconsistent formats (MM/DD/YY or YY-MM-DD). SQL Assist provides a "Find values" feature in the condition builder, which lists the unique values of the column you are working with, so you don't have to remember the format or exact spelling of values stored in that column.
WHERE clause conditions can involve many predicates combined with the logical operators AND and OR. In cases like these, it may not be obvious how the database will evaluate the condition. Operator precedence rules for AND and OR might cause the database to evaluate the condition in a way you were not intending. For example:
WHERE EDLEVEL > 16 AND WORKDEPT = 'D11' OR WORKDEPT = 'D21'
Will this query return any rows where EDLEVEL is less than 16? The answer is yes, because AND has a higher precedence than OR, and the query will return all rows where WORKDEPT = 'D21'. You can make this condition behave differently by grouping the two WORKDEPT predicates within parentheses:
WHERE EDLEVEL > 16 AND (WORKDEPT = 'D11' OR WORKDEPT = 'D21')
In addition, SQL Assist provides you with a way to select multiple predicates and to group or ungroup them. You can also indicate that you want a predicate or group of predicates to be negated with NOT. SQL Assist displays grouped predicates in a structured fashion, which makes it easier to visualize and understand grouping and how the complete condition will behave when you run the query.
Modifying your search condition is just as easy as building it. You can update your condition using the condition builder interface, or edit it directly in the SQL code view and use the round-trip editing feature to validate your changes and display them in the interface.
Step-by-step SQL Assist query building tutorial
Learn more about how to use SQL Assist by following this detailed tutorial with sample code, product screen shots, and easy to follow step-by-step instructions: Using SQL Assist to Generate SQL Queries in DB2.
Whether you're a novice or an expert, SQL Assist can help you develop SQL queries so you can devote more of your valuable resources to the vital database administration and development tasks that power your business. The powerful functionality and easy to use interface make SQL Assist a valuable utility for creating, modifying, and maintaining SQL to plug directly back into DB2. Look for additional functionality enhancements in future versions of DB2.
IBM offers a range of products, services, and information, to help meet your Data Management and database development needs. For additional information regarding this topic or similar issues please use the following information:
- To create SQL statements using SQL Assist, you should have a basic knowledge of relational databases and SQL. For more detailed information about SQL in a DB2 environment, see the SQL Reference for your DB2 platform: http://www.ibm.com/software/data/db2/library/
- Learn more about IBM's data management and database development products and services at: http://www.ibm.com/software/data/
- Get information about SQL development for DB2: http://www.ibm.com/software/data/db2/udb/ad/v7/sql.html
- Provide feedback to the SQL Assist development team at the IBM DB2 newsgroup at news://news.software.ibm.com/ibm.software.db2.udb. Please prefix all responses with "SQL Assist" in the heading.
- Learn about what's new for DB2 UDB Version 8.1: http://www.ibm.com/software/data/db2/udb/pdfs/db2q0.pdf
- Learn abou the DB2 Development Center in the article DB2 Development Center-Next generation AD-Tooling for DB2.
Your product experiences and feedback are greatly appreciated. Contact firstname.lastname@example.org to offer feedback on this article or the SQL Assist product.
The authors would like to recognize Polly Hughes and Teresa Stoll for their graphic contributions and Gary Faircloth, Kristin Vincent, Kathy Zeidenstein, and Melinda Pfeiffer for helping to make the article and tutorial possible.