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: