Rational Data Architect and DB2 9: Using the SQL Editor

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 6.1.0.1 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.


Note:



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:




Note:



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.


SQL Assist


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.

Paul Zikopoulos
Paul Zikopoulos
Paul C. Zikopoulos, BA, MBA is the Program Director for the DB2 Evangelist team at IBM. He is an award-winning writer and speaker with more than 14 years of experience with DB2. Paul has written more than 230 magazine articles and 11 books on DB2 including, Information on Demand: Introduction to DB2 9.5 New Features, DB2 9 Database Administration Certification Guide and Reference (6th Edition), DB2 9: New Features, Information on Demand: Introduction to 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 Clusters) and a DB2 Certified Solutions Expert (BI and DBA). In his spare time, he enjoys all sorts of sporting activities, including running with his dog Chachi, avoiding punches in his MMA training, and trying to figure out the world according to Chloë - his daughter.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles