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:
In this case, don’t save the SQL statement; just close it by
clicking 
. By now
you’re likely wondering why you would want to leverage this option. After all,
you just spent time writing a full SQL statement, adding tables, specifying an
ordering for the result set, and more.
In this example, we built the SQL statement from a simple
SELECT statement. As you likely noticed, the built-in templates in the SQL Builder are very
simple. The good news is that you can create customized templates that you can
leverage within the SQL Editor (they are not available within the SQL Builder at this present time, but I expect that to change)
and revert back to the template you defined. (I’ll introduce you to templates
in the next section and discuss the SQL Editor in the next article.) If you
recall, I’ve discussed how you would use a combination of the SQL Builder and
the SQL Editor. For example, despite the fact that we created the
FEMALEPERSONNEL SQL statement using the SQL Builder, you can subsequently open
it using the SQL Editor and get all of the functionality that’s only available
within the SQL Editor (such as templates) for your SQL statement:
In the future, these two editors may be merged into a single
editor. One thing to note, however: there may come a point at which your SQL
statement gets too complex for the SQL Builder and can only be opened in the
SQL Editor. Those of us who have to write SQL statements daily as part of our
jobs will likely feel the SQL Editor is the right trade-off between personal
knowledge and design assistance.
Run your SQL
It’s good to know that you can run your SQL statement directly from
the SQL Builder. This handy option enables you to quickly look at the result
set for your SQL statements with a click of a button. Now think about extending
such a benefit when you are in the Java perspective and writing a Java
application. Forget about the fact that IBM Data Studio gives you SQL
assistance when in the Java perspective, but the fact that you don’t have to write
Java code or create a JUnit program to run your SQL is the big benefit here.
These topics are outside the scope of this article, but this simple feature
will prove to be very beneficial through the data lifecycle management process
that I outlined in “DB2 9.5
and IBM Data Studio: Things I Couldn’t Tell You before DB2 9.5 Was Announced”.
SQL Templates
SQL templates are actually a favorite feature of mine that
likely should have made it into one of my other articles that highlight the top
features of the SQL Builder; however, I had to leave something of great
interest for this last section too!
IBM Data Studio supports the concept of templates.
Programmers are well familiar with these objects for extreme application
development. Essentially, templates are structured descriptions of coding
patterns that recur in source code. Java editors support the use of templates
to fill in commonly used source patterns. For example, a common coding pattern
is to iterate over the elements of an array using a FOR loop that indexes into the array. By using a template
for this pattern, you can avoid typing in the complete code for the loop.
Invoking Content Assist (Ctrl+Space) after typing a word will present
you with a list of possible templates for a FOR
loop in the Java perspective in IBM Data Studio. You subsequently choose the
appropriate template by name (in this case, Iterate over Array).
Selecting this template will insert the code into the editor and position your
cursor so that you can edit the details.
IBM Data Studio extends the benefits of templates for SQL
development as well, providing an enormous productivity boost for database
administrators (DBAs) and programmers alike. What’s more, since IBM Data Studio
can integrate with source control programs such as Microsoft Visual Source
Safe, IBM Rational ClearCase, Concurrent Versions System (CVS), and more,
you’ve got a great mechanism to ensure that best practices proliferate
throughout the organization and into your business logic.
As of the DB2 9.5 release, SQL Templates are only available
in the SQL Editor, so if you built your SQL statement using the SQL Builder,
open it using the SQL Editor to access this feature. You can access the
template list within the SQL Editor by invoking Content Assist (Ctrl+Space)
from within SQL Editor.
To see how this works, create a new SQL statement and press Ctrl+Space
below the default generated SELECT shell to see a list of all the available
built-in templates in IBM Data Studio. In the following example, you can see
that I selected the first occurrence of the XMLVALIDATE – XMLVALIDATE scalar
function template. (You select templates in the same manner that you select
Content Assist options.)
As you may have noticed, a lot of templates are already shipped
with IBM Data Studio. You can see all of the SQL-based templates that ship with
IBM Data Studio by selecting Windows>Preferences>SQL Editor>Templates.
IBM Data Studio also provides the ability to generate your
own SQL templates and subsequently retrieve them for use within the SQL Editor.
To create a new SQL template, perform the following steps in
the previous window:
1. Click
New. The New Template window opens.
2. Call
this template DescribeATable using the Name field.
3. Select
the type of statement you are creating using the Context drop-down list.
For this example, the default sql is fine. (If you were defining an
XQuery statement, you would select xquery instead.)
4. Leave
the Automatically insert field selected; this will instruct IBM Data
Studio to instantly add the template without prompting the SQL Editor after
you select it from the context pop-up menu.
5. Enter
a description of the template in the Description field.
6. Use
the Pattern field to enter the SQL statement. For our example, use the
following statement:
CALL ADMIN_CMD('describe select * from employee');
For this example, we want to create
a template SQL statement that will describe a table without having to use an
API or the command line processor (CLP). DB2 includes the ADMIN_CMD administrative routine that
effectively enables you to flow SQL to the database to subsequently perform management operations and so on. This make the management
of your data server via the SQL API much easier and broader scoped. Your New
Template window should now look like this:
You can use the Insert Variable button
to add special register variables into your SQL template as well. For example,
you could augment the previous template to include information such as the
current time when the statement was run, and so on:
7. Press
OK.
8. The
new SQL Template is added to the Templates window, as shown below. Click OK.
Once you’ve defined your custom template, you can refer to
it in the SQL Editor: simply type in the name of the SQL template and press Ctrl+Space.
IBM Data Studio will insert the template into the designer palette:
Note: You can also
type in the first few letters of the SQL template name and press Ctrl+Space,
and IBM Data Studio will insert the first SQL template it finds that
matches that combination. For our example, you could type DES, and then Ctrl+Space,
and IBM Data Studio would insert the DescribeATable SQL template you
created in the previous step.
From here, you use your editor as you normally would. For
example, run this SQL statement. In this example, you can see that the SQL
statement describes the EMPLOYEE table that resides in the SAMPLE database.
Now change employee to staff to describe that
table, and run the SQL statement again and you will see the structure of the
STAFF table.
Wrapping it up...
In this article, I rounded out
the features of the SQL Builder and finished off with a great feature that is
part of the SQL Editor -- SQL templates. The SQL Builder is good for single
data manipulation language (DML) statements, or to get you started. As you
noticed, in order to leverage SQL templates, you have to open an SQL statement
using the SQL Editor. (If you created your SQL statement using the SQL Builder,
you can still open it in the SQL Editor.) This seems like a great segue into
the next part of this series, where I will detail all the features of the SQL
Editor. By the time you are finished reading the next article, you’ll have an
excellent grasp of the SQL development features provided by IBM Data Studio,
and know which facility to use for the task at hand.
»
See All Articles by Columnist Paul C. Zikopoulos
Trademarks
IBM, DB2, ClearCase, and Rational
are trademarks or registered trademarks of International Business Machines
Corporation in the United States, other countries, or both.
Java and all Java-based
trademarks are trademarks of Sun Microsystems, Inc. in the United States, other
countries, or both.
Microsoft is a trademark 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.
About
