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 youve 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 its 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 isnt supported in this mode, close and reopen the same statement directly using the SQL editor (or vice-versa).
Ill use this article to point out whats available where in Rational DA because these nuances are a source of confusion for many data architects. After reading this article, youll 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, Ill 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 didnt use the SQL editor portion of the SQL builder because the statements 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 youre 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, its extended to the database schema (courtesy of the schema cache), which is something Visual Studio cant 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 doesnt 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 youve 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.
Content Assist helps in the building of SQL statements by providing a context-aware quick access list of operators and functions that you can use within an SQL statement. Content Assist is available within Rational DA no matter what method you use to work with your SQL statements.
You can invoke Content Assist when editing an SQL statement by placing your cursor beside a highlighted syntax in the SQL statement, right-clicking, and selecting Content Assist. (A faster way of invoking Content Assist is to position your cursor and simply press Ctrl+Space):
The following figure shows what a Content Tip looks like when I place my cursor beside the SELECT keyword in the FemaleEmployees SQL statement, which I opened in the SQL builder:
When using the SQL editor by itself or through the SQL builder, you can take advantage of a feature called Content Tip. Content Tip provides syntax assistance for the statement that you are creating in the same manner that the Content Assist feature, covered in the previous section, provides statement completion assistance.
You can invoke Content Tip when editing an SQL statement by placing your cursor beside a highlighted syntax in the SQL statement, right-clicking, and selecting Content Tip. (A faster way of invoking Content Tip is to position the cursor and simply press Ctrl+Shift+Space):
The following figure shows a Content Tip after I placed my cursor beside the SELECT keyword in the FemaleEmployees SQL statement, which I opened with the SQL builder:
You can see that the basic skeleton for this data manipulation statement (DML) is displayed in accompanying hover help.
The tip displayed always corresponds to the type of SQL statement you are creating. For example, if you were creating a DELETE statement, it would look like this:
Dynamic SQL Source Pane
The SQL builder includes dynamic linkage to the SQL statement that is generated in the top of its window pane. For example, each time you select a column from a table, set a projection or restriction, or perform a similar task, the generated SQL statement is automatically updated.
To see this feature in action, create a new SQL statement using the SQL builder and experiment by adding new tables, including and excluding specific columns, setting predicates, and more, and watch the SQL statement at the top of the SQL builder each time.
Obviously, since this feature links drag-and-drop actions to the actual SQL statement generated, it is only available in the SQL builder since the SQL editor already provides a manual method of working with SQL statements.
Colorized SQL Builder
As you build your SQL statements using either the SQL builder or the SQL editor, you may have noticed that the text you type sometimes turns to different colors. This isnt just to make things more interesting. Rational DA understands the DB2 SQL syntax so it parses the SQL with color whenever you type SQL keywords.
For example, remove the R from the last line of the FemaleEmployees SQL statement and watch it change to black (the default color used for non-SQL text). ORDE means nothing to a DB2 SQL parser, but ORDER is a keyword and therefore it shows up in a different color. In the following figure you can also see that the string within ...s is highlighted in green. (You can control the default colors used to highlight components of an SQL statement in Rational DA.)
In addition to the specialized SQL statement editing capabilities already described in this article, Rational DA has many of the basic text-editing features (such as cut, copy, paste, and so on) that you would expect from any text-based editor.
In addition, there are a host of other helpers such as undo, revert, format, indent, and so on. Not all of these features are available when working with SQL statements using the SQL builder and the SQL editor so you may need to switch your method of SQL editing depending on what function you need.
For example, only the SQL builder provides a Revert to Last Correct Source option when you right-click within an SQL statement. This feature is activated whenever you introduce an error into your SQL statement and save it despite the error warning that Rational DA surfaces.
In the following example, the SQL statement was made invalid by removing the R from the ORDER clause and subsequently saved despite warnings from Rational DA. To correct this problem, I right-clicked in the statement window and selected the Revert to Last Correct Source option:
Another feature thats only available in the SQL builder is the Clear to Template option, which effectively removes all the SQL youve generated through the graphical builder and replaces it with the original SQL template that you started with; think of this as the undo of all undo features. Dont use this indiscriminately because you could lose a lot of work (you cant undo a clear to template operation)!
The SQL editor also has some unique features available from its interface that arent available from the SQL builder. If you right-click in any white space in the SQL editor and compare it with the pop-up menu from the SQL builder, you can see the different features that are available within each method:
For example, using the SQL editor, you can set a termination character from within the editor itself, which is something you cant do from the SQL builder. (You have to change the preference settings for Rational DA.)
You can also shift blocks of the SQL text right or left for formatting, and theres even a Format SQL option that will format your SQL block. In addition, you can change the database connection for the SQL statement from within the SQL statement itself, instead of having to alter it from the Data Project Explorer view.
Wrapping it all up
In this article, I took you on a detailed tour of the different functions and features available within Rational DA when working with SQL statements. Rational DA provides two tools for SQL editing, the SQL builder and the SQL editor. Some SQL Rational DA features are only available when the SQL statement is opened using one or the other of these tools. I explained how to access these features, as well as how to work around any access limitations. In the next article, Ill continue a tour of the features available from the Database Explorer view after youve connected to a live database.
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 and has written more than one hundred magazine articles and is currently working on his tenth book. Paul has co-authored the books DB2 9: New Features, Information on Demand: Introduction 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 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: mailto:firstname.lastname@example.org.
IBM, DB2, Rational, and WebSphere are trademarks or registered trademarks of International Business Machines Corporation in the United States, other countries, or both.
Microsoft is a trademark of Microsoft Corporation in the United States, other countries, or both.
Linux is a registered 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, 2007. All rights reserved.
The opinions, solutions, and advice in this article are from the authors 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 authors knowledge at the time of writing.
» See All Articles by Columnist Paul C. Zikopoulos