DB2 Universal Database: Productivity Features of the SQL Builder, Part 6 - Page 2
June 13, 2006
Dynamic SQL Source Pane
In case you haven't noticed, the SQL text that Rational AD generated in the SQL Source pane is dynamic. For me personally, this is one way to master my SQL skills.
As you make changes to the tables you are working with, the SQL in the SQL Source pane is automatically updated for you. If you didn't notice it after manually adding the join using the SQL Assist feature in the previous step, delete the SQL join you added, re-enter it using SQL Assist, and take note of the Tables pane, as shown below:
As you enter SQL text, the Rational AD IDE automatically updates all components of the SQL Builder.
For example, manually change the SQL such that you perform an SQL projection by only including the ORDERAMOUNT column from the RADCUSTOMERORDERS table and the ID and NAME columns from the RADCUSTOMERS table by replacing the * with ORDERAMOUNT, ID, NAME in your SQL statement.
Clicking in a pane other than the SQL Source pane indicates to the Rational AD IDE that you are finished with the change, and the Tables pane is updated with the changes, as shown below:
Notice that the check boxes representing the various columns in the tables you have selected are automatically checked in relation to the columns you manually entered. You will find a number of different auto update features as you work with the Rational AD SQL Builder.
Note: The fastest way to select all the columns in a table for your SQL statement is to right-click in the Tables pane and select Select All Columns from the pop-up menu, as shown below. (It is also the fastest way to de-select all columns.)
Another useful feature of the SQL Builder is called Content Tip, which provides syntax assistance for the task at hand just as the Content Assist feature provides completion assistance.
You can invoke the Content Tip feature by switching focus to the SQL Source pane (by clicking in this pane) and pressing the Shift+Alt+Space keys in sequence. Rational AD 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 creating, as shown below:
Note: You can also right-click in the SQL Source pane and select the Content Tip option from the pop-up menu.
Colorized SQL Builder
As you build your SQL statements, you may have noticed that the text you type at times turns to different colors. This is not just to make things more interesting. The SQL Builder in Rational AD understands the DB2 SQL syntax so it changes the text color when you type in an operational SQL keyword.
For example, append to the end of the SQL statement that you are generating in this article the letters ORDE (that isn't a typo), as shown below:
You can see that since the SQL Builder does not recognize ORDE as a SQL keyword, it is not colorized. If you complete this keyword by adding R BY (ORDER BY), you will see that Rational AD recognizes this keyword and subsequently highlights it for syntax identification, as shown below:
Now save your SQL statement by pressing Ctrl+S.
Rational AD has a real-time SQL parser that is 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). If the error were to occur at build time, not only would the resource to build the project be wasted, but also identification of the error would be problematic because the build error log has to be examined, and more.
For example, working from the query as it is in the previous figure, change the SELECT PAULZ.RADCUSTOMERORDERS.ORDERAMOUNT phrase to SELECT PAULZ.RADCUSTOMER1ORDERS.ORDERAMOUNT and press Ctrl+S to save the statement. Upon the save request, Rational AD parses the statement and surfaces an error because the PAULZ.RADCUSTOMER1ORDERS.ORDERAMOUNT table does not exist.
Note: When a statement fails the automated parser in Rational AD, you can either dismiss the warning by clicking OK and save the SQL statement with the schema or syntax error, or you can correct the problem. You may want to save an SQL statement with an error because you spent a lot of time building it and need to consult your local expert to help identify a problem.
In addition to the specialized SQL statement editing capabilities already mentioned, Rational AD has many of the basic text editing features, such as Cut, Copy, and Paste, that users of word-processing software such as Microsoft Word are accustomed to. As well, there are other features like undo, revert, format, indent, and so on.
In addition to this, the SQL Builder also supports the ability to revert to the last correct source. This feature comes in very handy when your changes to an SQL statement introduce errors.
For example, continuing from the previous example, after dismissing the Validation Failed window, right-click in the SQL Source pane, and select Revert to Last Correct Source, as shown below:
If you ignore a validation failed warning and save the SQL statement, the error will be persisted. However, if you reopen the SQL statement that contains the error and then perform the Revert to Last Correct Source operation, Rational AD will not revert to the last properly saved version of the statement since that was overwritten when you chose to save the SQL statement that failed validation. In this case, Rational AD will reset the SQL statement to the skeleton it uses for base statements.
If you select the Clear to Skeleton option from the pop-up menu in the SQL Builder, Rational AD will erase the SQL statement and revert to the skeleton syntax it uses when creating a new SQL statement from scratch, as shown below:
Wrapping it all up
In this article, I showed you some of the powerful features that are part of the Rational AD SQL Builder that you can use to really boost your productivity and shorten the time it takes to build database applications.
So far in this series, you've learned how to work with database connections, create database schema objects, create Java and SQL-based stored procedures, generate UDFs, UDFs that call Web services, generate SQL statements, and use the productivity features built into the Rational AD SQL Builder.
Part 7 and 8 of this series will show you how to build a JavaServer Pages (JSP) application that operates on DB2 UDB tables and supports the use of various data operations on their data.
About the Author
Paul C. Zikopoulos, BA, MBA, is an award-winning writer and speaker with the IBM Database Competitive Technology team. He has more than ten years of experience with DB2 UDB and has written over one-hundred magazine articles and several books about it. Paul has co-authored the books: DB2 Version 8: The Official Guide, DB2: The Complete Reference, DB2 Fundamentals Certification for Dummies, DB2 for Dummies, and A DBA's Guide to Databases on Linux. Paul is a DB2 Certified Advanced Technical Expert (DRDA and Cluster/EEE) and a DB2 Certified Solutions Expert (Business Intelligence and Database Administration). In his spare time, he enjoys all sorts of sporting activities, running with his dog Chachi, and trying to figure out the world according to Chloë his new daughter. You can reach him at: firstname.lastname@example.org.
IBM, DB2, DB2 Universal Database, Rational, and WebSphere are trademarks or registered trademarks of International Business Machines Corporation in the United States, other countries, or both.
Java is a trademark of Sun Microsystems, Inc. in the United States, other countries, or both.
Windows is a trademark of Microsoft Corporation in the United States, other countries, or both.
UNIX is a registered trademark of The Open Group in the United States and other countries.
Linux is a trademark of Linus Torvalds in the United States, other countries, or both.
Other company, product, and service names may be trademarks or service marks of others.
Copyright International Business Machines Corporation, 2006. All rights reserved.
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.