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 you’ve 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 haven’t yet mastered the DB2 SQL syntax.
Colorization
While building your SQL statements, you may have noticed
that the text you type at times turns to different colors. This isn’t 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, let’s append DES (that isn’t 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, I’ve
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 you’ve specified a valid
keyword and see what happens (don’t forget to save your query at this point):
I find colorization a great way for the tool to quickly let
me know if I’ve entered something wrong while typing my SQL statement (which is
getting rarer and rarer these days, thanks to the features I’m 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.
Design-Time Parser
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, let’s 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... (I’ve
highlighted the changes in red.)
When you try to save such a statement (assuming these tables
don’t 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
couldn’t 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 that’s 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 wouldn’t 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. You’re able to
pre-diagnose errors before they happen at run time and that’s 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.
»
See All Articles by Columnist Paul C. Zikopoulos
Trademarks
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.
Disclaimer
The opinions, solutions, and
advice in this article are from the author’s 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
author’s knowledge at the time of writing.
» 
Comment and Contribute
