DB2 9.5 and IBM Data Studio: Part 7: The SQL Builder's Development Accelerators

Tuesday Feb 19th 2008 by Paul Zikopoulos
Share:

Paul Zikopoulos introduces his four favorite features available in the IBM Data Studio SQL Builder: SQL Assist, Content Tip, colorization, and the design-time parser.

So far in this series about the IBM Data Studio Developer integrated development environment (IDE) that’s available with DB2 Version 9.5 (DB2 9.5), I’ve shown you how to set up database connection objects and the actions that you can perform on them. In addition, I introduced you (in Part 2) to some of the features available in the Database Explorer view, and Part 3 and Part 4 gave you a full tour of overview diagrams. In Part 5, you learned how to point-and-click your way to OLE DB functions that can integrate data from external data sources that have an OLE DB provider. In Part 6, I showed you how easy it is to create an SQL statement using the IBM Data Studio SQL Builder. In this installment, I’m going to show you my four favorite features in the SQL Builder that make this tool even more helpful than just point and clicking your way to an SQL statement like I showed you in the last article.

Things you have to do to follow the examples in this article...

I recommend that you start with Part 1 because I tend to build on the concepts and objects created in these series sequentially. For this article, I assume that you’ve at least created the SAMPLE database (use the db2sampl –xml –sql command) and that the FEMALEPERSONNEL SQL statement built in Part 6 resides in the SQL Scripts folder in a database development project. Your Data Project Explorer and Database Explorer views should look similar to these:

IBM Data Studio Query Builder productivity boosters

In this section, I’ll talk about my favorite features that really help boost productivity when you’re designing SQL statements using the Query Builder. I may have mentioned some of the features in past articles in this series, but this section will serve as a place where they can all come together.

SQL Assist

Perhaps one of the most rapid application development features that I know of in IBM Data Studio is SQL Assist. If you’ve ever worked with Microsoft Visual Studio and are familiar with IntelliSense, then you already have a good idea what I’m talking about. However, when you use the SQL Builder in IBM Data Studio, the assistance is hooked into the underlying database’s schema – not just the respective programming language semantics. In fact, since IBM Data Studio can maintain a cache of the data servers you are working with, you can even work in a disconnected mode (offline) and still leverage this feature. SQL Assist is a key part of the pureQuery initiative: it places the same assistance feature that Java programmers expect from their tools into an integrated editor that understands both Java and SQL.

For example, create a new query (call it FEMALEPERSONNEL2) and drag the DEPARTMENT and EMPLOYEE tables to your SQL Builder design canvas in the same manner you did in Part 6. IBM Data Studio should now look like this:

In the last article, you added columns to the query by simply selecting them from the middle pane of the SQL Builder. While you can use the graphical controls at the bottom of the SQL Builder to add clauses to your SQL statement, most developers are likely to use a combination of manual entry and assisted entry (like the Add Join and projection options I covered in Part 6). While the check box column definition method is more useful for those not as adept in writing SQL, even SQL maestros will want to use contextual assistance while they are writing the SQL statement; the SQL Builder offers this with SQL Assist.

In the area of the SQL Builder that displays the actual text of your SQL statement, try to manually append the following condition (shown in bold text below) to the SQL statement that was automatically generated for you:

 SELECT * FROM DEPARTMENT, EMPLOYEE 
 
 WHERE PAULZ.EMPLOYEE.WORKDEPT=’A00’ AND SEX=’F’ 
 
 ORDERBY PAULZ.EMLOYEE.LASTNAME

Note: Don’t forget to substitute the schema qualifier PAULZ used in this article for the schema you used to create the SAMPLE database. (On a Windows platform, it’s whatever user account you were logged in with when you ran the DB2 CREATE DATABASE command or db2sampl.)

Now I want you to erase the newly typed text and retype it pausing ever so slightly after you specify PAULZ - of course, replace PAULZ with your schema name. (Stop at the period.) In IBM Data Studio, when a period (.) follows a schema name that DB2 recognizes, the SQL Assist feature is automatically engaged and all the applicable objects in context (in this case, the schema objects) are automatically presented in a pop-up list for you, as shown below:

Simply select the object you are looking for from the pop-up list and press Enter. In this example, select the EMPLOYEE table and briefly pause after typing the qualifying period after the EMPLOYEE table name. You can see that SQL Assist is smart enough to recognize the context of the SQL statement: notice below that the pop-up list only shows the columns that are part of the EMPLOYEE table:

If you’re following along in this example, you’ll want to select the WORKDEPT column. Sometimes you may notice that the column you want isn’t on the pop up list. Obviously, you can use the scroll bar or your cursor movement keys to scroll down the list of returned columns - but there’s a better way! SQL Assist has filter and stemming options that quickly move focus in the pop-up list to the location based on the first key you press. In this example, if you pressed W, then the SQL Assist pop-up list would only display those columns that begin with this letter:

Note: You can also invoke SQL Assist by right-clicking in your SQL statement window and selecting SQL Assist from the pop-up menu, or by pressing Ctrl+Space after specifying an object name in your SQL statement.

Continue building this SQL statement, but now I want you to manually enter the AND SEX=‘F’ restriction. (Notice that it changed color - we’ll talk about that in a bit.) You can see that sometimes it’s better just to type something quickly than to leverage SQL Assist. As you get more accustomed to this great feature, you’ll decide for yourself when to use it and when to use your old fashioned fast-typing fingers. In this case, I know I don’t actually have to specify the schema here, and I know there is a column called SEX, and I know the value and its corresponding data type, so it was just faster to enter it myself.

Enter a space after SEX=‘F’. SQL Assist can help further, but it’s only going to show up if you ask for it using one of the methods detailed in the previous note (because there is no period triggering the action). For example, to add the ORDER BY clause, invoke SQL Assist and manually scroll the entire list of options to find this operator. (Of course, pressing a letter key will automatically move focus in the list to the first occurrence for that group):

You can see this list is very long. The point is that there is always some help available for you, and depending on the context of the SQL statement, it changes dynamically. Finish building this SQL statement and save your work.

Of course, since views and nicknames are presented like tables to applications, IBM Data Studio will link the SQL Assist features to enumerate all the qualifying views and nicknames, along with tables, in the list returned to the developer.

Another reason why this feature is so important is that if you were to create an SQL statement using the SQL Editor, you wouldn’t have the option to drag (or add) tables to the middle pane of the editor. You would just have the SQL text.

You can also customize the way SQL Assist behaves in IBM Data Studio. For example, you could choose whether to display SQL proposals (keywords, templates, and names of database objects) when you use content assistance while writing or editing an SQL statement. As previously mentioned, Ctrl+Space is the default key combination for this content assistance. If you want to change this combination, select Window>Preferences>General>Keys and change the configuration for Content Assist.

SQL Assist really enhances productivity because it moves the requirement away from knowledge of the database schema for productivity and places automated discovery of the schema in the hands of the application developers for a rapid application development experience: this is true extreme application development.



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.

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved