Rational Data Architect and DB2 9: Building an SQL Statement
May 8, 2007
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 youre not familiar with that process, or you didnt 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.
Lets 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 its 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 doesnt 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 youve added both tables to the SQL builder workspace, the Rational DA workspace should look similar to this: