DB2 9.5 and IBM Data Studio: Part 7: The SQL Builder's Development Accelerators - Page 2
February 19, 2008
Content Assist (a.k.a. Content Tip)
Another useful feature of the SQL Builder is called Content Assist (though better known by its name Content Tip). Content Tip provides syntax assistance for the task at hand in the same manner that the Content Assist feature provides completion assistance for an SQL statement.
You can invoke the Content Tip feature by switching focus to the pane where your SQL statement resides and pressing Shift+Alt+Space, or selecting it from the pop-up window when you right-click in this pane. IBM Data Studio then displays a tip for the SQL statement that you are generating, as shown below:
The displayed tip corresponds to the type of SQL statement you are building. For example, if you were working with a DELETE template, it would give a tip that relates to a DELETE statement:
Content Tip is a very useful feature because it saves you from having to go into reference documentation if youve forgotten an element of a statement, and so on. It is especially valuable for personnel who are moving from a different data server platform and havent yet mastered the DB2 SQL syntax.
While building your SQL statements, you may have noticed that the text you type at times turns to different colors. This isnt just to make things more interesting. The SQL Builder in IBM Data Studio understands the DB2 SQL syntax, so it changes your text color when you type an operational SQL keyword.
Continuing with our current example, lets append DES (that isnt a typo) to the end of the ORDER BY clause in your SQL statement such that the SQL statement looks like this:
Notice DES is in the same color as the rest of the non-SQL-specific text? Notice also that the values you specify with string delimiters ( s) turn green? Also note that SQL keywords by default show up in maroon. In the previous example, Ive mistyped the keyword DESC (which would instruct DB2 to perform the ORDER BY operation in a descending manner). Now I want you to add a C such that youve specified a valid keyword and see what happens (dont forget to save your query at this point):
I find colorization a great way for the tool to quickly let me know if Ive entered something wrong while typing my SQL statement (which is getting rarer and rarer these days, thanks to the features Im covering in this article); it also serves to organize sections of the SQL statement with visual breakpoints.
You can customize the colorization properties of the SQL Builder using the Window>Preferences>General>Appearance>Colors and Fonts. For example:
You can use Restore Defaults at any time to restore the default color used for keywords.
IBM Data Studio has a real-time SQL parser built into the SQL Builder. This parser greatly helps the design-time experience for developers because it alerts them before build time to syntax issues (which could arise from hand-coding part or all of the SQL statement). Of course, the colorization feature in IBM Data Studio is one such visual queue; however, this parser is a before save check. This can greatly help developer productivity because if the error were to occur at application build time, not only would the resources to build the project be wasted, but identification of the error would be problematic because the build error log has to be examined, and more.
Continuing along in our current example, lets append a 1 to the end of the tables in the SELECT statement and save the statement (press Ctrl+S). For example, working from the query as it is in the previous figure, change the SELECT * FROM DEPARTMENT and EMPLOYEE... to SELECT * FROM DEPARTMENT1 and EMPLOYEE1... (Ive highlighted the changes in red.)
When you try to save such a statement (assuming these tables dont exist), you will see an error message similar to this one:
Before you even ran this SQL statement, IBM Data Studio let you know that the tables you specified don't exist. Of course, if you solely relied on Content Assist to add tables to your SQL statements, then you couldnt make an error since it only presents a list of valid table names. As I mentioned, most developers use both methods to build their SQL statements: Content Assist and manual entry. The parser thats built into IBM Data Studio is another great way in which IBM Data Studio makes you more productive.
You can also see from the Validation Failed message that the validation is only against those tables that are in the database connection object after any filtering has been applied to the connection. This is important to keep in mind when working with a specific database connection object that may have filtering applied to it. For example, if you referenced a table in a different schema that was excluded from the filter, it wouldnt pass the parsing check, but would be a valid table.
The design time parser also works on SQL syntax, not just validating references to the underlying schema. For example, if you introduced the error I showed you in the Colorization section (specifying a descending property for the ORDER BY clause using DES as opposed to the correct DESC), you would get an error when you tried to save that SQL statement:
The key point to keep in mind here is that all this is happening before you build and test your application. Youre able to pre-diagnose errors before they happen at run time and thats what development productivity is all about.
Wrapping it up...
In this article, I introduced you to my favorite four features available in the IBM Data Studio SQL Builder: SQL Assist, Content Tip, colorization, and the design-time parser. It should be evident at this point just how much IBM Data Studio can help you develop SQL statements. The features I covered in this article should be enough incentive by themselves to use this toolset in your day-to-day work, but SQL Builder offers you even more assistance for rapid application development, and those remaining features will be the focus of my next article.
IBM and DB2 are trademarks or registered trademarks of International Business Machines Corporation in the United States, other countries, or both.
Microsoft and Windows are trademarks of Microsoft Corporation in the United States, other countries, or both.
Other company, product, or service names may be trademarks or service marks of others.
© Copyright International Business Machines Corporation, 2008. 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.