Part 5 of this series, focuses on the built-in SQL Builder in Rational AD, examining how to use it to build SQL statements that you can use in your Java programs, Web services, routines, or just stand-alone statements.
by Paul C. Zikopoulos
In Part 1 of this series, I discussed how to use the Database Explorer view in the IBM Rational Application Developer for WebSphereSoftware (Rational AD) product to create and work with IBM DB2 Universal Database (DB2 UDB) for Linux, UNIX, and Windows database connections. Part 2 introduced you to some of the many capabilities provided by the Data Definition view in the Rational AD integrated development environment (IDE). Part 3 taught you how to use Rational AD to build a stored procedure, deploy it, and test it. Part 4 taught you how to use Rational AD to build a simple user-defined function (UDF), as well as a complex one that invokes a Web service.
Part 5 and 6 of this series focus on the built-in SQL Builder and SQL Editor (here-in referred to as the SQL Builder) in Rational AD. Here in Part 5 I will show you how to use it to build SQL statements that you can use in your Java programs, Web services, routines, or just stand-alone statements. In Part 6, which will follow, I will detail some of its other features that can accelerate the developer's time to value when building database logic.
Note: Although you don't have to have read the first 4 parts of this series, the steps in this article assume you have and examples are based on the schema objects built in those parts.
Building an SQL Statement
Rational AD comes with a built-in tool for SQL creating SQL statements called the SQL Builder. This tool can be invoked from different parts of the IDE; for example, it is available in various wizards for building routines and it can be started on its own to build standalone SQL statements.
The steps that follow outline how to build the following query using the SQL Builder:
SELECT PAULZ.DEPARTMENT.LOCATION, PAULZ.EMPLOYEE.EMPNO,
FROM PAULZ.DEPARTMENT, PAULZ.EMPLOYEE
WHERE PAULZ.DEPARTMENT.DEPTNO = PAULZ.EMPLOYEE.WORKDEPT
AND PAULZ.EMPLOYEE.SEX = 'F'
ORDER BY LASTNAME DESC, FIRSTNME DESC
This query returns a predefined number of attributes from the EMPLOYEE and DEPARTMENT tables for all female employees registered for a fictitious company.
The easiest way to access the SQL Builder, is to build an SQL statement.
1. Select the Statements folder in the database tree where you want to create your SQL statement and select New>Select Statement, as shown below:
As you can see in the previous figure, you can create many kinds of SQL statements from the Statements folder.
Note: The With Statement option is available with DB2 UDB for Linux, UNIX, or Windows server Version 8.1.4 or later.
2. The New Select Statement window opens, as shown below. Enter a name for your SQL statement and click OK.
Note: The examples outlined in this part of this series assume that you have the SAMPLE database that comes with DB2 UDB created on your system. If you don't, you can create it now by entering the db2sampl command on your operating system's command prompt.
The SQL Builder opens with a sample statement that corresponds to the type of SQL statement you selected from the pop-up menu in the previous step.
For example, when you click the Select option in Step 1, the Rational AD SQL Builder starts with a sample SELECT statement, as shown below:
If you chose to create any of the other supported types of SQL statements, Rational AD would provide starter templates for those statements as well:
You can see in the previous figure that depending on the type of SQL statement you want to create, the stemming options for the different types of SQL statements are presented in various panes within this wizard's pages.
3. Right-click in the Tables pane and select the Add Table option. The Add Table dialog box opens, as shown below:
For this example, add the EMPLOYEE and DEPARTMENT tables located in the DB2 UDB SAMPLE database.
You can optionally create an alias for each of the tables using the Table alias field (although you do not need these for this example). If you use the Table alias field to associate an alias with the table (or view) used in your SQL statement, Rational AD will adjust the SQL syntax accordingly, as shown below:
Once you have added both tables to the SQL Builder workspace, the Rational AD IDE should look similar to this:
Rational AD 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.)
The 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:
4. Right-click in the Tables window and select the Create Join option to create a join between the two tables, as shown below:
For this example, join the DEPARTMENT and EMPLOYEE tables using the DEPARTMENT.DEPTNO and EMPLOYEE.WORKDEPT columns, as shown in the previous figure.
by Paul C. Zikopoulos
If you select two join columns that are not compatible, the SQL Builder tells you this and 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 window by hovering over the desired join column on one table and releasing the mouse button on the join column on the target table, as shown below:
You can see in the previous figure that when you hover over a column, its underlying data type is revealed in the 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 AD changes the mouse icon to an error indicator and stops the operation.
5. 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, as shown below:
For this example, select the following columns:
Notice how the SQL statement that you are building in the SQL Source pane continues to evolve as you perform different operations such as adding or removing tables, projecting or restricting columns, identifying join predicates, and so on.
You can also use the Column tab at the bottom of the SQL Builder to add columns to your SQL statement simply click the empty Column column in the Columns tab 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.)
6. 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 hierarchal order to the different sorting definitions for your SQL statement.
7. Apply a condition for your SQL statement (a.k.a. a WHERE predicate) 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 is case-sensitive and must be delimited with single quotes.
8. 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 is good to know it is there if the SQL statement you are building requires it.
9. Press Ctrl+S to save your query. You query should now look similar to this:
10. To run the query, select it from the Statements folder, right-click it, and select Execute. The output of this action and the associated result set are shown in the bottom of the Rational AD IDE, as shown below:
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, with the click of a button you can use the SQL statement to automatically generate a Java- or SQL-based stored procedure, as shown below:
At this point, you can use all the features provided in Rational AD to work with stored procedures. For example, the following figure shows how to test the stored procedure:
You can also use Rational AD to automatically generate a Java bean from your SQL statement in order to present Java developers with the appropriate methods and a more natural programming experience when interacting with your database.
The following figure shows the results of selecting the Generate Java Bean option from the pop-up menu that appears when you right-click an SQL statement and change to the J2EE perspective:
Using a Java bean, you can automatically create a Web service with mere clicks of a button, further extending the rapid application development features that the Rational AD IDE provides developers.
Wrapping it all up
In this article, I showed you how to use the built-in SQL Builder in Rational AD to build SQL statements that can be used as stand-alone statements in your development projects, converted into SQLJ artifacts, used in your routines, and more.
So far in this series, you have learned how to work with database connections, create database schema objects and stored procedures, how to generate UDFs, and how to use the SQL Builder.
In Part 6 of this series, I will 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.
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 ten years of experience with DB2 UDB and has written over 100 magazine articles and several books about it. Paul has co-authored the books: 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, running with his dog Chachi, and trying to figure out the world according to Chloë his new daughter. You can reach him at: firstname.lastname@example.org.
IBM, DB2, DB2 Universal Database, Rational, and WebSphere are trademarks or registered trademarks of International Business Machines Corporation in the United States, other countries, or both.
Java is a trademark of Sun Microsystems, Inc. in the United States, other countries, or both.
Windows is a trademark of Microsoft Corporation in the United States, other countries, or both.
UNIX is a registered trademark of The Open Group in the United States and other countries.
Linux is a 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, 2006. All rights reserved.
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.