In Part 1 of this series, I discussed how to use the Database Explorer view in the IBM Rational Application Developer for WebSphere Software (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 UDF that invokes a Web service. Part 5 of this series introduced you to how you to build SQL statements using the SQL Builder that is part of the Rational AD IDE.
In Part 6 of this series, I focus on some of the SQL Builder features that accelerate application development. What I will show you is very different from creating SQL statements as you did in Part 5.
You are not required to understand how to use these features to build an SQL statement (since I didn’t introduce you to any of them in Part 5), but understanding them will greatly enhance your productivity.
Note: The steps in this article assume that you have read the first three parts in this series, and have built the associated schema objects outlined in each of those parts.
Features of the SQL Builder
This section introduces you to various design-time features of the SQL Builder that accelerate the development of SQL statements. To experience some of these features outlined in this article, run the following data definition language (DDL) statements to create the RADCUSTOMERS and RADCUSTOMERORDERS tables in the NEWSMPL database you created earlier in this series:
CREATE TABLE RADCUSTOMERS
(ID INT NOT NULL PRIMARY KEY, NAME VARCHAR(30))
CREATE TABLE RADCUSTOMERORDERS
(ORDERID INT NOT NULL PRIMARY KEY, ORDERAMOUNT DEC(8,2),
CUSTOMERID INT, FOREIGN KEY CUSTOMERIDTOID (CUSTOMERID)
REFERENCES RADCUSTOMERS ON DELETE NO ACTION)
INSERT INTO RADCUSTOMER VALUES(1,’BAKLARZ’), (2,’JAMES’),(3,’ZIKOPOULOS’)
INSERT INTO RADCUSTOMERORDERS VALUES (1, 34544.22, 1),
(2,2500.00,1),(3,7677.66,2),(4,565.00,3)
After you have created these tables and inserted the data, the Data Definition view in your Rational AD IDE should look similar to this:
Using the steps you learned in “Part 5 – Generating SQL Statements Using the SQL Builder”, create a new SQL statement (call it SELECTallCustomerOrdersAndCustomers), leaving the editor blank, as shown below:
You can use these tables to follow the steps in this article.
Table Attributes
One of the things I like most about the SQL Builder is that it can give visual clues about a table.
For example, in the SELECTallCustomerOrdersAndCustomers SQL statement, drag the RADCUSTOMERS and RADCUSTOMERORDERS tables into the Tables pane as you did in “Part 5 – Generating SQL Statements Using the SQL Builder”. Hover over the different columns in these tables, and observe the metadata in the hover help, as shown below:
Additionally, look at the icons beside the RADCUSTOMERORDERS.ORDERID, RADCUSTOMERORDERS.CUSTOMERID, and RADCUSTOMERS.ID columns, as highlighted in the following figure:
You can see that the SQL Builder automatically flags referential integrity relationships such as primary keys (using the 
icon) and foreign keys (using the 
icon) for any tables that you want to work with in the Tables pane.
SQL Assist
Perhaps one of the most rapid application development features in the Rational AD IDE that I know of is SQL Assist, which is similar to IntelliSense in Microsoft Visual Studio. However, when you use the Rational AD IDE to work with a DB2 database, the capabilities extend beyond what IntelliSense offers Visual Studio developers. The assistance is hooked into the underlying database’s schema – not just the respective programming language semantics. In fact, since the Rational AD IDE maintains a cache of the DB2 UDB database you are working with, you can even work in a disconnected mode (offline) and still leverage this feature.
For example, consider the SQL that is automatically generated for you after dragging the RADCUSTOMERORDERS and RADCUSTOMERS tables into the Table area of the SQL Builder:
While you can use the graphical controls at the bottom of the SQL Builder to add segments to your SQL statement, most developers are likely to use a combination of manual entry and assisted entry (like the Add Join and projection options I covered in “Part 5 – Generating SQL Statements Using the SQL Builder”.
In the SQL Source area of the previous figure, try to manually append the following condition (shown in bold text below) to the SQL statement that was generated for you:
SELECT * FROM PAULZ.RADCUSTOMERORDERS, PAULZ.RADCUSTOMERS
WHERE PAULZ.RADCUSTOMERORDERS.CUSTOMERID =
PAULZ.RADCUSTOMERS.ID
Note: Do not forget to substitute the schema qualifier PAULZ used in this article for the schema you used to create the database. (On a Windows platform, it is whatever user account you were logged in with when you ran the db2 create database command.)
Did anything happen? Well, it depends how fast you entered in the join condition for this SQL statement. If nothing happened, manually delete the text and just enter:
SELECT * FROM PAULZ.RADCUSTOMERORDERS, PAULZ.RADCUSTOMERS
WHERE PAULZ.
In Rational AD, when a period (.) follows a schema name that DB2 UDB recognizes, the SQL Assist feature is automatically engaged and all the applicable objects in the schema are automatically presented for you, as shown below:
Your job is simply to scroll to the column or table you want to include in the SQL statement, press the Enter key, and continue building your SQL statement.
Note: If the list of tables returned is lengthy, you can simply enter the first letter of the table or column you are looking for and Rational AD will automatically go to the first occurrence in that ordered list.
You can also invoke the SQL Assist feature: to do this, right-click in the SQL Source pane and select Content Assist from the pop-up menu, or press the Ctrl+Space key combination after specifying a schema or table name in your SQL statement.
Note: Since views are presented to applications like tables, Rational AD will link the SQL Assist features to enumerate all the qualifying views, along with tables, in the list returned to the developer.
This feature really enhances productivity because it moves the focus away from knowledge of the database schema for productivity and places automated discovery of it in the hands of the application developers for a rapid application development experience.
