Creating SQL Queries the Easy Way with SQL Assist for DB2 UDB Version 8.1

This article explains the basics of getting started with SQL Assist, a tool for easily creating SQL queries for DB2 UDB on Windows, UNIX and Linux platforms.

Alex Jarzebowicz (jarzebow@us.ibm.com), Software Engineer, IBM Silicon Valley Lab

Alex Jarzebowicz is a software engineer in the Business Intelligence - User Technology Group at the IBM Silicon Valley Lab in San Jose, CA. He specializes in improving the user experience for IBM Data Management products through information development and user-centered design.



Sheila Sholars, Advisory Software Engineer, IBM Silicon Valley Lab

Sheila Sholars is an advisory software engineer at the IBM Silicon Valley Lab in San Jose in the Database Application Development tooling group. A significant portion of her 14 years with IBM has focused on user-centered application development solutions with a specialty in implementing efficient, intuitive user interfaces. She earned her Bachelor's degree in Computer Science and Engineering from the University of California at Los Angeles.



Brian Payton, Advisory Software Engineer, IBM Silicon Valley Lab

Brian Payton is an Advisory Software Engineer at the IBM Silicon Valley Lab in San Jose, CA. He has spent almost his entire career at IBM working on interactive database query tools, beginning with QMF, later Visualizer Query, and now SQL Assist. Brian has a B.S. in Computer Science from California Polytechnic University, San Luis Obispo.



06 November 2002

© 2002 International Business Machines Corporation. All rights reserved.

Introduction

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
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.)

Command Center

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.

Control Center

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.

Development 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.

Replication Center

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
basic start 01

Outline view

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.

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
BasicFrom02

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
BasicWhere05

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.


JOIN assistance

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
AdvJoin01

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
AdvJoin06

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.


Condition building

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
BasicWhere01

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.


Additional information

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:

Your product experiences and feedback are greatly appreciated. Contact jarzebow@us.ibm.com to offer feedback on this article or the SQL Assist product.


Acknowledgements

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.

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Select information in your profile (name, country/region, and company) is displayed to the public and will accompany any content you post. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=14289
ArticleTitle=Creating SQL Queries the Easy Way with SQL Assist for DB2 UDB Version 8.1
publish-date=11062002