In an article called “Rational Data Architect and DB2 9: Building an SQL Statement”, I introduced you to the IBM Rational Database Architect (Rational DA) product and its SQL builder feature. Specifically, I showed you how easy it is to build an SQL statement using the Rational DA toolset.
If you’ve read that article, you may recall that the SQL builder contains the SQL editor and that you can create SQL statements using the SQL builder and its embedded SQL editor, or by starting an SQL editor session on its own. For some reason, the availability of the many useful SQL editing features in Rational DA Version 220.127.116.11 depends on whether you launch the SQL editor by itself or through the SQL builder. For example, the SQL Assist feature (covered later in this article) is available in the SQL editor on its own, but not when you launch the SQL editor from the SQL builder. In the future, I expect these features to merge and be available from either editing method since it’s the same SQL editor.
To work around this availability issue, you can build an SQL statement in the SQL builder. If a particular SQL editing feature isn’t supported in this mode, close and reopen the same statement directly using the SQL editor (or vice-versa).
I’ll use this article to point out what’s available where in Rational DA because these nuances are a source of confusion for many data architects. After reading this article, you’ll have a good understanding of all the capabilities available for SQL generation and editing in Rational DA, and know where to find them. (You can see my previous article for other highlights of the SQL editor such as the real-time parser.)
Where we left off…
If you followed the steps in my previous article, your Rational DA integrated development environment (IDE) should now look similar to the example below. (Note the FemaleEmployees SQL statement created in the previous article in the SQL Scripts folder within the Data Project Explorer view.)
Features of the SQL editor
The best way to learn about the SQL editing and assistance features available in Rational DA is to actually edit or create SQL statements using the SQL editor, either on its own or as part of the SQL builder. In this section, I’ll detail the most useful SQL editing and creation features available within Rational DA and tell you where they can be used.
To work through the examples in this article, open the FemaleEmployees SQL statement located in the MyFirstRDAProject project that you created in the previous article, as follows:
After you open an SQL statement using either the SQL builder or the SQL editor, Rational DA will make that the default editing method whenever you subsequently double-click an SQL statement for editing. For example, in the previous figure, I selected SQL Builder, so if I double-click the FemaleEmployees SQL script in the future, it will automatically open within the SQL builder. If I were to subsequently open this statement using the SQL editor, the next time that I double-clicked that script, it would open directly within the SQL editor.
When we built the FemaleEmployees SQL statement in the previous article, we didn’t use the SQL editor portion of the SQL builder because the statement’s contents were built by actions performed in the SQL builder – though the SQL editor was always available for manual fine-tuning of the SQL statement. The SQL editor within the SQL builder is where you see the SQL statement in the figure below. After you open the FemaleEmployees SQL statement using the method illustrated in the previous figure, your Rational DA IDE should look like this:
SQL change identification
The SQL editor (whether opened as part of the SQL builder or on its own) will visually flag changes to any SQL statements that are reopened for editing or altered after a save command. By default, Rational DA uses a purple left margin identifier to show any changes made to an SQL statement that are not yet saved. For example, if you changed the sort of the FIRSTNME column in the FemaleEmployees SQL statement to Ascending (ASC), the SQL editor would look like this:
You can see in the previous figure that a shaded bar is placed in the left margin beside the row of the SQL statement that was changed (in this case, from DESC to ASC). Once you save this SQL statement again, this identifier will disappear since the new sort order is now considered part of the base SQL statement.
Perhaps one of the most useful and time-saving SQL development features in Rational DA is SQL Assist. If you’re familiar with the IntelliSense feature in Microsoft Visual Studio, then you know about the SQL statement completion assistance this feature offers only with Rational DA, it’s extended to the database schema (courtesy of the schema cache), which is something Visual Studio can’t do.
SQL Assist is available when the SQL editor is opened on its own but not from within the SQL builder. Perhaps one of the reasons why SQL Assist doesn’t work from within the SQL builder is because the schemas and their respective objects are shown within the Database Explorer view and you can drag them to the SQL designer canvas. If you’ve built an SQL statement using the SQL builder, and you need to use SQL Assist, close the SQL builder and re-open the statement directly using the SQL editor. (Remember that this will change the default way an SQL statement is opened for editing.)
Quite simply, SQL Assist pops up a list of all the tables and views within a schema once you specify the schema name followed by a period (.) in an SQL statement. For example, if I type SELECT * FROM PAULZ. in the SQL editor, the Rational DA IDE responds as follows:
You can see in the previous figure that Rational DA automatically popped up a list of tables and views within the PAULZ schema. All I need to do is select the table I want and press Enter.
In addition, you can quickly navigate the SQL Assist list using the slider control on the right and Rational DA helps out a lot because it will jump to the list of objects that begin with the first letter you type. For example, I entered a P right after the PAULZ. qualifier, and Rational DA automatically took me to the first object that begins with a P (PURCHASEORDER) within the PAULZ schema; at this point I just move the cursor to the PZTESTXML table and press Enter, and the SQL statement is updated with a reference to this object:
Remember, if you need to use SQL Assist to work with an SQL statement that you built using the SQL builder, you need to open the statement directly using the SQL editor.
SQL Assist really enhances productivity because it moves the focus away from knowledge of the database schema and places automated discovery of it in the hands of the architects and developers for a truly rapid application development experience. Errors are reduced at design time, which eliminates the expense of discovering them at build time and the effort of tracing perhaps hundreds of lines of SQL to find the error conditions.