Rational Data Architect and DB2 9: Building an SQL Statement

In an article called “Rational
Data Architect and DB2 9: The Database Explorer,”
I introduced you to the
IBM Rational Database Architect (Rational DA) Database Explorer view and showed
you how to add database connections to it. If you’re not familiar with that process,
or you didn’t read that article, it would be helpful to review it now. In this
article, I want to show you one of the features available from the Database Explorer
view, namely, the ability to build SQL statements to run against your database.

Building an SQL Statement

Rational DA comes with a built-in tool for creating SQL
statements. You can access this tool from different parts of Rational DA,
including the Database Explorer view.

Let’s look at how to build the following query using
Rational DA:


SELECT DEPARTMENT.LOCATION, EMPLOYEE.EMPNO,
EMPLOYEE.FIRSTNME, EMPLOYEE.LASTNAME,
EMPLOYEE.PHONENO FROM DEPARTMENT, EMPLOYEE
WHERE DEPARTMENT.DEPTNO = EMPLOYEE.WORKDEPT
AND EMPLOYEE.SEX = ‘F’
ORDER BY LASTNAME DESC, FIRSTNME DESC

This query returns all the female employees in a company.

To build an SQL statement from the Database Explorer view,
perform the following steps:

1.  Right-click
a database connection object and select New SQL Statement.

You can select this option from a
connected or disconnected database connection object. If you select New SQL
Statement
from a disconnected database connection object, it will
automatically create a connection to the database. You can alternatively
right-click the database object and select this option.

2.  In
the New SQL Statement window, type a name for this statement in the Statement
name
field, and select the type of query you want to create in the Statement
template
list. For this example, specify FemaleEmployees as the Statement
name
, SELECT as the Statement template, and SQL builder
in the Edit using box, and then click OK. The SQL builder opens
with a SELECT template statement.

In the Statement template
list, Rational DA provides source templates for all kinds of queries, such as: INSERT, UPDATE,
DELETE, FULLSELECT,
and WITH (for Common Table Expression
definitions).

The SQL builder option
invokes a graphical-assisted SQL creation tool that enables you to build SQL
statements with simple drag-and-drop operations, point-and-click attribute
changes, and more (shown in the remaining steps of this article).

The SQL builder looks like this:

The SQL editor option only
opens the SQL editor (which is part of the SQL builder – it’s where the actual
SQL statement resides in the SQL builder). The SQL editor provides code generation
assistance such as syntax colorization and automated schema lookup, but doesn’t
provide the drag-and-drop features associated with the SQL builder to build SQL
statements. I cover the SQL editor in the next article in this series.

3.  Right-click
in the Tables pane and select the Add Table option. The Add Table
window opens. For this example, add the EMPLOYEE and DEPARTMENT tables located
in the SAMPLE database for which you created a connection in the previous
article.

You can see in the previous figure
that only the HR and PAULZ schemas show up in the Add Table window. This is
because the database connection object you created in the last article applied
a filter.

You can also see in the previous
figure that you have the option to create an alias for each table you add to a
query. A table alias makes it easier to refer to tables in an SQL statement. If
you use a table alias to represent a table (or view) used in your SQL statement,
Rational DA will adjust the SQL syntax and its interface accordingly, as shown
below:

Once you’ve added both tables to
the SQL builder workspace, the Rational DA workspace should look similar to this:

Paul Zikopoulos
Paul Zikopoulos
Paul C. Zikopoulos, BA, MBA is the Program Director for the DB2 Evangelist team at IBM. He is an award-winning writer and speaker with more than 14 years of experience with DB2. Paul has written more than 230 magazine articles and 11 books on DB2 including, Information on Demand: Introduction to DB2 9.5 New Features, DB2 9 Database Administration Certification Guide and Reference (6th Edition), DB2 9: New Features, Information on Demand: Introduction to DB2 9 New Features, Off to the Races with Apache Derby, DB2 Version 8: The Official Guide, DB2: The Complete Reference, DB2 Fundamentals Certification for Dummies, DB2 for Dummies, and A DBA's Guide to Databases on Linux. Paul is a DB2 Certified Advanced Technical Expert (DRDA and Clusters) and a DB2 Certified Solutions Expert (BI and DBA). In his spare time, he enjoys all sorts of sporting activities, including running with his dog Chachi, avoiding punches in his MMA training, and trying to figure out the world according to Chloë - his daughter.

Latest Articles