IBM Data Studio supports drag-and-drop operations from the Data Definition view
to the SQL Builder. (Simply right-click and drag the table you want to add to
the Tables window, and release the mouse button to move the table to
this region.)
Drag-and-drop support allows you to
circumvent using the Add Table option outlined in this step. This gives
you a table selection process that provides a more natural query-building
experience (although when you use this method you can’t directly define a table
alias):
6. Right-click a blank area where the tables are shown in the SQL
Builder, and select Create Join to create a join between the two tables
using the join conditions shown below:
You can also use the drag-and-drop
method to create a join directly from this part of the Query Builder too.
Simply hover over the desired join column on one table and release the mouse
button on the join column on the target table, as shown below:
7.
Click the check boxes that correspond to the columns that you want to
include in the result set for this SQL statement from both tables. For this
example, select the following columns: DEPARTMENT.DEPTNAME,
DEPARTMENT.LOCATION, EMPLOYEE.EMPNO, EMPLOYEE.FIRSTNME, EMPLOYEE.LASTNAME, and EMPLOYEE.PHONENO, as shown below:
Notice how the SQL statement that you are building in the SQL Builder continues
to evolve as you perform different operations such as adding or removing
tables, projecting or restricting columns, specifying join predicates, and so
on.
You can also use the Columns tab at the bottom of the SQL Builder
to add columns to your SQL statement – from there, simply click an empty field
in the Column column, and select the column that you want to add, as
shown below. (Repeat this process for all the columns that you want to add to
your SQL statement if this is the manner in which you want to specify including
columns for your query.)
8.
Using the Columns tab, apply any additional sorting order using
the Sort Type and Sort Order columns by clicking them and
selecting the corresponding options, as shown below:
In the previous
figure, you can see that the output of this SQL statement will be sorted first
by LASTNAME in descending order from Z->A
(a personal bias of mine), and second by FIRSTNAME (in the same descending
order, as indicated in the Sort Type column). The sort order gives a hierarchical
order to the different sorting definitions for your SQL statement.
9.
Apply a WHERE predicate
to your SQL statement by selecting the Conditions tab and building the
condition by clicking each respective column, as shown below:
For this
example, use the condition shown in the previous figure. To enter a value in
the Value field, simply click the field and type the value instead of
building the expression.
Note:
Don’t forget that for the EMPLOYEE table, the SEX column is a CHAR-based
column; therefore, the value you specify in the Value column should be
delimited with single quotes and case matters.
10.
Apply any grouping options that you want to add to your SQL statement
using the Groups and Group Conditions tabs at the bottom of the
SQL Builder.
For this
example, you can skip this step, but it’s good to know it’s there if the SQL
statement you are building requires it.
11.
Press Ctrl+S to save your query. Your query should now look
similar to this:
12.
To run the query, select it from the SQL Scripts folder,
right-click, and select Run SQL. The output of this action and the
associated result set are shown in the Data Output tab at the bottom of
the IBM Data Studio IDE:
Once you have your SQL statement, you can
right-click to see a number of other actions that you can perform on it (aside
from just running it to see if it returns the results you are expecting).
For example, through
Visual Explain you can quickly generate an access plan graph of the SQL
statement you just created:
I’ll actually take you
through many of these options in future installments of this series.
Wrapping it up...
In this article, I showed you how
to use the IBM Data Studio SQL Builder to build an SQL statement that can be subsequently
used for many purposes. In the next part of this series, I’m going to show you
some really cool features that are part of the SQL Builder such as formatting,
syntax colorization, content assist, content tips, and a whole lot more! Having
a sound understanding of these capabilities can really boost a developer’s
productivity.
»
See All Articles by Columnist Paul C. Zikopoulos
Trademarks
IBM and DB2 are trademarks or registered
trademarks of International Business Machines Corporation in the United States,
other countries, or both.
Other company, product, or
service names may be trademarks or service marks of others.
Copyright International
Business Machines Corporation, 2008. All rights reserved.
Disclaimer
The opinions, solutions, and
advice in this article are from the author’s experiences and are not intended
to represent official communication from IBM or an endorsement of any products
listed within. Neither the author nor IBM is liable for any of the contents in
this article. The accuracy of the information in this article is based on the
author’s knowledge at the time of writing.
» 
Comment and Contribute
