Rational Data Architect and DB2 9: Using the SQL Editor - Page 2
May 15, 2007
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.