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,
PAULZ.EMPLOYEE.FIRSTNME, PAULZ.EMPLOYEE.LASTNAME,
PAULZ.EMPLOYEE.PHONENO
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.
