DB2 9.5 and IBM Data Studio Part 9: The SQL Editor Development Accelerators - Page 2
May 12, 2008
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 youve 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 youre 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 doesnt exist ( ).
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 arent 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, youll likely gravitate towards this editor.
Options not supported in the SQL Editor
The options shown in the following figure arent 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 dont 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. Im 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, its 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.
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.
The opinions, solutions, and advice in this article are from the authors 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 authors knowledge at the time of writing.