So far in this series about the IBM Data Studio 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. Part 7 introduced you to a set of rapid application development features within IBM Data Studio that takes your SQL development capabilities to a whole new level. Specifically, I introduced you to my favorite SQL Builder features: namely, SQL Assist, Content Tip, colorization, and the design-time parser. In this installment, I finish the tour of the SQL Builder features that help you get to an extreme development paradigm. Things you have to do to follow the examples in this article...

I recommend for that all the multi-part articles I write 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 read through Part 7 and your Database Explorer view looks like this:



More Development Assistance from the SQL Builder

In this section, I’ll detail the rest of the SQL Builder features that help boost productivity when you’re designing SQL statements using the FEMALEPERSONNEL2 query, which we built in Part 7 of this series.



Standard Editing Functions

IBM Data Studio comes with a number of basic text editing capabilities such as cut, copy, and paste, that users of word-processing software, spreadsheets, and more are accustomed to. As well, there are more advanced features such as undo, revert, format, indent, and so on. These features really come in handy when you are designing SQL statements.

To access the basic editing features (I’ll cover some of the more advanced ones in a bit), simply right-click within the design canvas of the SQL Builder:



Revert to Last Correct Source

Quite often, you’ll find yourself editing your SQL and making a mistake (or perhaps it’s just me). The Revert to Last Correct Source option comes in very handy. For example, add a D to the DEPARTMENT table name such that it looks like:



Now save this SQL statement (press Ctrl+S). You should receive an error message similar to this:



The SQL Builder is smart enough to see that this table doesn’t exist, and alerts you to this error when you try to save the statement. Click OK and then press Ctrl+S to override the real time parser in IBM Data Studio and save the query with the error.

Note: Make sure you save your SQL statement to enable this option. You can tell that your statement hasn’t been saved when an asterisk (*) prefixes the name of the SQL statement. When a statement is successfully saved (even with an error) the asterisk disappears, as shown below:



To have IBM Data Studio return the SQL statement to the last known version of it that worked, right-click in the SQL Builder and select Revert to Last Correct Source. IBM Data Studio will open a window that contains that last SQL statement that it successfully parsed. You can probably figure out what happens if you click OK at this point:



As you can see in the previous figure, IBM Data Studio changed the SQL statement back to the last time the SQL you wrote was valid. I like this feature because I often work off an SQL template, a stub file, or some sample SQL statement that I copied from the DB2 documentation or some article. If I mess it up, I know that I’ve got IBM Data Studio watching my back. What’s more, it seems that IBM Data Studio can remember that last version of your SQL statement that worked even after you close and subsequently reopen your SQL statement; that means the ‘state’ of the help engine persists across editing sessions! Clear to Template

This option takes your SQL statement, as well as any changes you’ve made, and replaces it with the original template you worked with. For example, create the same error in the SQL statement that you just did in the previous section, but this time select the Clear to Template option.

If you recall from Part 5, we created an SQL statement using the SELECT template, and this is why the SQL statement reverted back to what’s shown below:

Full article...