DB2 Universal Database: Productivity Features of the SQL Builder, Part 6

Tuesday Jun 13th 2006 by Paul Zikopoulos
Share:

Part 6 of this series focuses on some of the SQL Builder features that accelerate application development.

In Part 1 of this series, I discussed how to use the Database Explorer view in the IBM Rational Application Developer for WebSphere Software (Rational AD) product to create and work with IBM DB2 Universal Database (DB2 UDB) for Linux, UNIX, and Windows database connections. Part 2 introduced you to some of the many capabilities provided by the Data Definition view in the Rational AD integrated development environment (IDE). Part 3 taught you how to use Rational AD to build a stored procedure, deploy it, and test it. Part 4 taught you how to use Rational AD to build a simple user-defined function (UDF), as well as a complex UDF that invokes a Web service. Part 5 of this series introduced you to how you to build SQL statements using the SQL Builder that is part of the Rational AD IDE.

In Part 6 of this series, I focus on some of the SQL Builder features that accelerate application development. What I will show you is very different from creating SQL statements as you did in Part 5.

You are not required to understand how to use these features to build an SQL statement (since I didn't introduce you to any of them in Part 5), but understanding them will greatly enhance your productivity.

Note: The steps in this article assume that you have read the first three parts in this series, and have built the associated schema objects outlined in each of those parts.

Features of the SQL Builder

This section introduces you to various design-time features of the SQL Builder that accelerate the development of SQL statements. To experience some of these features outlined in this article, run the following data definition language (DDL) statements to create the RADCUSTOMERS and RADCUSTOMERORDERS tables in the NEWSMPL database you created earlier in this series:

CREATE TABLE RADCUSTOMERS 
(ID INT NOT NULL PRIMARY KEY, NAME VARCHAR(30))
CREATE TABLE RADCUSTOMERORDERS
(ORDERID INT NOT NULL PRIMARY KEY, ORDERAMOUNT DEC(8,2),
 CUSTOMERID INT, FOREIGN KEY CUSTOMERIDTOID (CUSTOMERID)
  REFERENCES RADCUSTOMERS ON DELETE NO ACTION)
INSERT INTO RADCUSTOMER VALUES(1,'BAKLARZ'), (2,'JAMES'),(3,'ZIKOPOULOS')
INSERT INTO RADCUSTOMERORDERS VALUES (1, 34544.22, 1),
(2,2500.00,1),(3,7677.66,2),(4,565.00,3)

After you have created these tables and inserted the data, the Data Definition view in your Rational AD IDE should look similar to this:

Using the steps you learned in "Part 5 – Generating SQL Statements Using the SQL Builder", create a new SQL statement (call it SELECTallCustomerOrdersAndCustomers), leaving the editor blank, as shown below:

You can use these tables to follow the steps in this article.

Table Attributes

One of the things I like most about the SQL Builder is that it can give visual clues about a table.

For example, in the SELECTallCustomerOrdersAndCustomers SQL statement, drag the RADCUSTOMERS and RADCUSTOMERORDERS tables into the Tables pane as you did in "Part 5 – Generating SQL Statements Using the SQL Builder". Hover over the different columns in these tables, and observe the metadata in the hover help, as shown below:

Additionally, look at the icons beside the RADCUSTOMERORDERS.ORDERID, RADCUSTOMERORDERS.CUSTOMERID, and RADCUSTOMERS.ID columns, as highlighted in the following figure:

You can see that the SQL Builder automatically flags referential integrity relationships such as primary keys (using the icon) and foreign keys (using the icon) for any tables that you want to work with in the Tables pane.

SQL Assist

Perhaps one of the most rapid application development features in the Rational AD IDE that I know of is SQL Assist, which is similar to IntelliSense in Microsoft Visual Studio. However, when you use the Rational AD IDE to work with a DB2 database, the capabilities extend beyond what IntelliSense offers Visual Studio developers. The assistance is hooked into the underlying database's schema – not just the respective programming language semantics. In fact, since the Rational AD IDE maintains a cache of the DB2 UDB database you are working with, you can even work in a disconnected mode (offline) and still leverage this feature.

For example, consider the SQL that is automatically generated for you after dragging the RADCUSTOMERORDERS and RADCUSTOMERS tables into the Table area of the SQL Builder:

While you can use the graphical controls at the bottom of the SQL Builder to add segments to your SQL statement, most developers are likely to use a combination of manual entry and assisted entry (like the Add Join and projection options I covered in "Part 5 – Generating SQL Statements Using the SQL Builder".

In the SQL Source area of the previous figure, try to manually append the following condition (shown in bold text below) to the SQL statement that was generated for you:

SELECT * FROM PAULZ.RADCUSTOMERORDERS, PAULZ.RADCUSTOMERS
 WHERE PAULZ.RADCUSTOMERORDERS.CUSTOMERID =  
  PAULZ.RADCUSTOMERS.ID 

Note: Do not forget to substitute the schema qualifier PAULZ used in this article for the schema you used to create the database. (On a Windows platform, it is whatever user account you were logged in with when you ran the db2 create database command.)

Did anything happen? Well, it depends how fast you entered in the join condition for this SQL statement. If nothing happened, manually delete the text and just enter:

SELECT * FROM PAULZ.RADCUSTOMERORDERS, PAULZ.RADCUSTOMERS
 WHERE PAULZ.

In Rational AD, when a period (.) follows a schema name that DB2 UDB recognizes, the SQL Assist feature is automatically engaged and all the applicable objects in the schema are automatically presented for you, as shown below:

Your job is simply to scroll to the column or table you want to include in the SQL statement, press the Enter key, and continue building your SQL statement.

Note: If the list of tables returned is lengthy, you can simply enter the first letter of the table or column you are looking for and Rational AD will automatically go to the first occurrence in that ordered list.

You can also invoke the SQL Assist feature: to do this, right-click in the SQL Source pane and select Content Assist from the pop-up menu, or press the Ctrl+Space key combination after specifying a schema or table name in your SQL statement.

Note: Since views are presented to applications like tables, Rational AD will link the SQL Assist features to enumerate all the qualifying views, along with tables, in the list returned to the developer.

This feature really enhances productivity because it moves the focus away from knowledge of the database schema for productivity and places automated discovery of it in the hands of the application developers for a rapid application development experience.

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.)


Content Tip

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.

Design-Time Parser

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.

Editing Functions

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: paulz_ibm@msn.com.


Trademarks

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.

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.

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved