Use database connection
The Use Database Connection option is useful
because it allows you to run the SQL statement you just built against a
different database from the one your Data Development Project was configured to
use. This is great for testing against a production database those SQL
statements that you’ve been running against your development environment. All
in all, this feature makes it easy to run your SQL statements on development,
test, quality assurance (Q/A), and production databases.
If you recall, when we created the DATABASEJOURNALPROJECT
Data Development Project, we configured it such that it would connect to the
SAMPLE database. If you wanted to run this same query against a Q/A database,
you could do this using the following option:
Set statement terminator
By default, DB2 uses a semi-colon (;) as the
statement terminator. You can change this default behavior using the Set
Statement Terminator option:
Validate table references
The Validate table references option performs the
same checking that the Validate Statement Syntax option does, but this
option validates references to tables in your SQL statement rather than SQL
syntax. The notion here is that the SQL Editor can provide design-time
assistance for not only validating references to existing tables but helping
you select them too.
You enable and disable this option in the same manner as you
do the Validate Statement Syntax:
I strongly recommend that you enable this option as well
when you’re building SQL statements using the SQL Editor.
To see how this option works, open up the FEMALEPERSONNEL
SQL statement, enable this option, and add the following errors to your SQL
statement; watch how the SQL Editor highlights the fact that the new table
names are not valid for the database that this Database Development Project is
configured to use:
You can see that the SQL Editor uses the same visual cues as
the Validate Statement Syntax option outlined earlier in this article.
For example, if you hover over the left margin column indicator (
) on the
line that references the EMPLOYEEu table, you see that this table
doesn’t exist (
).
Visual Explain
This option launches the Visual Explain component for a DB2
data server. This component enables the SQL designer to quickly view the access
plan that the DB2 optimizer will choose for the SQL statement without having to
contact a DBA or resort to other tooling (such as the command line processor
utility or the Control Center).
The output for the FEMALEPERSONNEL query is as follows:
You can see in the previous access plan that this query involves
a table scan of each table, followed by a hash join, then a sort, and then a
scan of the intermediate table. Since these tables aren’t very large, it might
be best to leave the query and the database schema as is. Of course, you can always
use the Design Advisor to see whether a number of different data server objects
could help increase the performance of this query. For example, running this
query through the Design Advisor yields the following suggestions:
In this case, you can see that the Design Advisor verifies
our assumptions. Since the table is very small, the DB2 optimizer will simply choose
to perform a table scan even if you created an index. (If an index would have
helped this query, the Design Advisor would have recommended that you create
one).
Many options are available from the menu of the Visual Explain
utility. You can explore them. My favorite feature (aside from seeing my query
visually) is the ability to see the SQL statement that I wrote and what the DB2
optimizer did to rewrite it and make it more efficient without my doing
anything. (Yes, you read that right. DB2 automatically turns bad SQL into good
SQL when it determines that a query would be more efficient if it were written
in a different manner):
Enhanced Design-Time Assistance
I showed you some of the design-time assistance options in
the SQL Builder in previous articles. The SQL Editor offers more design-time
assistance beyond what the SQL Builder can do. If you recall, the SQL Builder
provides you with Content Assist and SQL Assist, as well as a parser that will
alert you if you try to save your SQL statement while it contains an error.
The SQL Editor offers a lot more design-time assistance,
which is why I say that as you get more and more comfortable building SQL
statements, you’ll likely gravitate towards this editor.
Options not supported in the SQL Editor
The options shown in the following figure aren’t used by the
SQL Editor.
Perhaps one of the drawbacks to all the extensibility of an
open platform such as the Eclipse framework is that application developers
don’t always have complete control over what shows up in context menus. For
example, if you wanted to run an SQL statement in the SQL Editor, you would
select the Run SQL option not the Run As option.
Wrapping it up...
In this article, I showed you
some features that are unique to the SQL Editor. Personally, I like the SQL
Editor the most, but the SQL Builder has some features that are very useful.
I’m hoping these two editors merge in future editions of IBM Data Studio.
Nevertheless, now that you know how to write SQL statements, and know about the
different features of IBM Data Studio that you can use to make it easy, it’s
time for us to write some business logic. In the next article, I will show you
how to create a stored procedure using the IBM Data Studio from scratch using an
SQL statement that you developed in this series.
»
See All Articles by Columnist Paul C. Zikopoulos
Trademarks
IBM and DB2 are registered
trademarks of International Business Machines 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.
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
