Part 1 of this series introduced the IBM Rational Database Architect (Rational DA)
Database Explorer view. Part 2 discusses one of the features available from the Database Explorer
view, namely, the ability to build SQL statements to run against your database.
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
SELECT DEPARTMENT.LOCATION, EMPLOYEE.EMPNO,
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:
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.
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,
and WITH (for Common Table Expression
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.
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
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
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
Once youve added both tables to
the SQL builder workspace, the Rational DA workspace should look similar to this:
Rational DA supports drag-and-drop
operations from the Database Explorer view to the SQL builder. Simply
right-click and drag the table you want to add to the Tables pane, and
release the mouse button to move the table to this region.
This drag-and-drop support replaces
the Add Table option outlined earlier in this step; this table selection
process provides a more natural query-building experience:
4. Right-click in the Tables pane and select the Create
Join option to create the join illustrated by the following figure:
For this example, join the DEPARTMENT
and EMPLOYEE tables using the DEPARTMENT.DEPTNO and EMPLOYEE.WORKDEPT columns,
as shown in the previous figure.
If you select two join columns that
are not compatible, the SQL builder prevents the join because it would result
in an error at run time. An example of such an error message is shown below:
You can also use the drag-and-drop
method to create a join directly from the Tables pane by hovering over
the desired join column on one table and releasing the mouse button on the join
column on the target table:
You can see in the previous figure
that when you hover over a column, the underlying data type is revealed in its hover
help this enriches the design time experience and minimizes potential join
errors. If you try to create a join between two data types that are
incompatible, Rational DA changes the mouse icon to an error indicator and stops
the check boxes that correspond to the columns that you want to include in the
result set for this SQL statement from both tables. Ensure that your SQL
builder looks similar to this by selecting the following columns: DEPARTMENT.DEPTNAME,
DEPARTMENT.LOCATION, EMPLOYEE.EMPNO, EMPLOYEE.FIRSTNME, EMPLOYEE.LASTNAME,
Notice how the
SQL statement that you are building in the SQL Source pane dynamically
changes as you perform different operations such as adding or removing tables,
projecting or restricting columns, identifying join predicates, and so on.
Also, note that
Rational DA gives you visual cues as to business relationships encoded within
the schema as they relate to each tables columns. For example, in the previous
figure, you can see that the DEPTNO and EMPNO columns are primary keys as
indicated by their accompanying icon:.
The DEPARTMENT tables MGRNO and ADMRDEPT columns, as well as the EMPLOYEE tables
WORKDEPT column, are foreign keys linked to other tables, as indicated by their
accompanying icon: .
You can also
use the Columns tab at the bottom of the SQL builder to add columns to your
SQL statement simply click the Column list to select the column or
columns that you want to add.
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. For this example, ensure that your Columns
tab looks similar to this:
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 (as indicated by the Sort Order column)
by FIRSTNME (in the same descending order, as indicated in the Sort Type column).
Apply a conditional predicate to your SQL statement (for example, a WHERE predicate) by selecting the Conditions
tab and building the condition by clicking each respective column:
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.
Dont forget that the EMPLOYEE tables SEX column is a CHARACTER-based
column; therefore, the value you specify in the Value column is case-sensitive
and must be delimited with single quotes; that why I used F in
the previous figure.
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
example, you can skip this step, but its good to know its there if the SQL
statement you are building requires it.
Press Ctrl+S to save your query. You query should now look
similar to this:
10. Test the
query you just built by clicking Run->Run
SQL or clicking the corresponding icon, as shown below:
The output of
this action and the associated result set are shown in the Data Output tab
at the bottom of the Rational DA workspace:
Minimizing design-time errors with Rational DA
You may recall in the Rational Data Architect and DB2 9: The Database Explorer article that I showed you how to set up a database connection such that you can work in offline mode. I noted that this had the effect of creating a schema cache for your database objects. While this option allows you to create models, SQL statements, and more without a database connection, it also has the added benefit of exposing that schema cache to design time helpers within Rational DA such as the SQL parser and code assistants. This feature can greatly help reduce coding errors that you usually dont find out about until deployment time.
For example, when saving an SQL statement, Rational DA will alert you to an SQL statement that will fail even if the syntax is correct. For example, in the working example, change the PAULZ.DEPARTMENT.DEPTNO text to PAULZ.DEPARTMENT.DEPTNUMB and press Ctrl+S to save the query.
Rational DA instantly presents you with an error message similar to this:
Again, you can see how this schema cache can save you valuable time when designing SQL statements because its used to alert you to problematic statements during design time, rather than at test or run time.
In the following figure, you can also see that Rational DA gives you visual clues when an SQL statement contains an error. On the left side of this figure you can see that the bottom portions of the SQL builder are gray, indicating an error. (I put the r in this portion of the figure to show this query is incorrect.) The right side of this figure (with the a) is placed on an SQL statement that was written correctly. Notice that portions of this SQL builder are not shaded.
Rational DA provides other tools that help to minimize design time errors and Ill cover those features in full detail in a follow-on article to this one.
Houston, we have a problem
I cant save my SQL
You may have noticed that when you save the SQL statement you just generated, there is no place to save it to. This is one of the drawbacks when building an SQL statement from the Database Explorer view. If you want to save an SQL statement for future reuse, you need to create it as part of a project. (Of course, you could always cut and paste the generated SQL statement into a Rational DA project, or into a separate file.)
If you look closely at the SQL statement figures in this article, you can see an asterisk (*) beside the name of the query, which implies that its not a persisted query
If you want to create a query that you directly save within Rational DA, you need to create a Data Design or Data Development project.
Perform the following steps to create a Data Development project and use the steps already detailed in this article to create the same SQL statement, as follows:
1. Click File->New->Project, select Data Development Project from the New Project window, and click Next. (You might need to select Show All Wizards to see this project.)
2. Type a name for the Data Development project (for example, MyFirstRDAProject), and click Next:
You can also use this window to set other options such as the default schema for this project and the option to include schema identification names in generated SQL statements. For this article, you can ignore these options.
3. Specify the default database connection that you want to use for this project (in this case, DB2SAMPLE), and click Next:
You can see in the previous figure that I chose to use the DB2SAMPLE database connection created in the Rational Data Architect and DB2 9: The Database Explorer article. If you dont have a database connection to your chosen target database, select Create a new connection and click Next. (The panels from the Add Database Connection wizard will appear in this wizard; so just follow the steps in my previous article.)
4. Specify the default path for the JDK, and click Finish. By default, this should be pre-filled for you with the Java path used by DB2 9:
You may have noticed in Step 3 that the Finish button became active after you selected a target database connection object. This happened because the JDK home field was filled in by default by Rational DA. If this field wasnt filled in, that button would not have been active in Step 3. In the future, you can just click Finish in Step 3.
A new Data Development project is created in the Data Project Explorer view, which usually sits above the Database Explorer view in the Rational DA workspace (though you can customize its location at any time):
5. Expand your new project, select SQL Scripts->New->SQL Statement.
6. In the New SQL Statement window, select the project in which you want to create the new SQL statement, and click Next.
By default, the project from which this wizard was launched is used to store the new SQL statement. You can select from other projects that exist in Rational DA by clicking Project, or create a new project by clicking New.
7. Follow the steps outlined earlier in this article to re-create the FEMALEEMPLOYEES SQL statement. Once you have saved and tested this SQL statement, the Rational DA Data Project Explorer view should look like this:
You can see that the FemaleEmployees SQL statement can now be saved (and retrieved) from within your newly created Data Development project.
Wrapping it all up
In this article, I showed you how to leverage a database connection to create a new SQL statement and all the development assistance Rational DA gives you for creating SQL statements. I also showed you a way to ensure that you can retrieve your SQL statement later using a Data Development project. (Note: You can also use a Data Design project for this purpose more on that in a future article.) In the next article, Ill build on this example and show you all the things you can do in Rational DA with a saved SQL statement, as well as some other SQL editor features that I did not cover in this article.
» See All Articles by Columnist Paul C. Zikopoulos
About the Author
Paul C. Zikopoulos, BA, MBA, is an award-winning writer and speaker with the IBM Database Competitive Technology team. He has more than eleven years of experience with DB2 and has written more than one hundred magazine articles and is currently working on his tenth book. Paul has authored the books DB2 9: New Features, Information on Demand: Introduction 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 Cluster/EEE) and a DB2 Certified Solutions Expert (Business Intelligence and Database Administration). In his spare time, he enjoys all sorts of sporting activities, including running with his dog Chachi, avoiding punches in his MMA class, and trying to figure out the world according to Chloë his daughter. You can reach him at: mailto:firstname.lastname@example.org.
IBM, DB2, and Rational are trademarks or registered trademarks of International Business Machines Corporation in the United States, other countries, or both.
Java and all Java-based trademarks are trademarks of Sun Microsystems, Inc. in the United States, other countries, or both.
Linux is a registered trademark of Linus Torvalds in the United States, other countries, or both.
Other company, product, and service names may be trademarks or service marks of others.
Copyright International Business Machines Corporation, 2007. All rights reserved.
The opinions, solutions, and advice in this article are from the authors 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 authors knowledge at the time of writing.