Rational Data Architect and DB2 9: Building an SQL Statement - Page 3
May 8, 2007
Minimizing design-time errors with Rational DA
You may recall in the Rational Data Architect and DB2 9: The Database Explorer article that I showed you how to set up a database connection such that you can work in offline mode. I noted that this had the effect of creating a schema cache for your database objects. While this option allows you to create models, SQL statements, and more without a database connection, it also has the added benefit of exposing that schema cache to design time helpers within Rational DA such as the SQL parser and code assistants. This feature can greatly help reduce coding errors that you usually dont find out about until deployment time.
For example, when saving an SQL statement, Rational DA will alert you to an SQL statement that will fail even if the syntax is correct. For example, in the working example, change the PAULZ.DEPARTMENT.DEPTNO text to PAULZ.DEPARTMENT.DEPTNUMB and press Ctrl+S to save the query.
Rational DA instantly presents you with an error message similar to this:
Again, you can see how this schema cache can save you valuable time when designing SQL statements because its used to alert you to problematic statements during design time, rather than at test or run time.
In the following figure, you can also see that Rational DA gives you visual clues when an SQL statement contains an error. On the left side of this figure you can see that the bottom portions of the SQL builder are gray, indicating an error. (I put the r in this portion of the figure to show this query is incorrect.) The right side of this figure (with the a) is placed on an SQL statement that was written correctly. Notice that portions of this SQL builder are not shaded.
Rational DA provides other tools that help to minimize design time errors and Ill cover those features in full detail in a follow-on article to this one.
Houston, we have a problem I cant save my SQL
You may have noticed that when you save the SQL statement you just generated, there is no place to save it to. This is one of the drawbacks when building an SQL statement from the Database Explorer view. If you want to save an SQL statement for future reuse, you need to create it as part of a project. (Of course, you could always cut and paste the generated SQL statement into a Rational DA project, or into a separate file.)
If you look closely at the SQL statement figures in this article, you can see an asterisk (*) beside the name of the query, which implies that its not a persisted query
If you want to create a query that you directly save within Rational DA, you need to create a Data Design or Data Development project.
Perform the following steps to create a Data Development project and use the steps already detailed in this article to create the same SQL statement, as follows:
1. Click File->New->Project, select Data Development Project from the New Project window, and click Next. (You might need to select Show All Wizards to see this project.)
2. Type a name for the Data Development project (for example, MyFirstRDAProject), and click Next:
You can also use this window to set other options such as the default schema for this project and the option to include schema identification names in generated SQL statements. For this article, you can ignore these options.
3. Specify the default database connection that you want to use for this project (in this case, DB2SAMPLE), and click Next:
You can see in the previous figure that I chose to use the DB2SAMPLE database connection created in the Rational Data Architect and DB2 9: The Database Explorer article. If you dont have a database connection to your chosen target database, select Create a new connection and click Next. (The panels from the Add Database Connection wizard will appear in this wizard; so just follow the steps in my previous article.)
4. Specify the default path for the JDK, and click Finish. By default, this should be pre-filled for you with the Java path used by DB2 9:
You may have noticed in Step 3 that the Finish button became active after you selected a target database connection object. This happened because the JDK home field was filled in by default by Rational DA. If this field wasnt filled in, that button would not have been active in Step 3. In the future, you can just click Finish in Step 3.
A new Data Development project is created in the Data Project Explorer view, which usually sits above the Database Explorer view in the Rational DA workspace (though you can customize its location at any time):
5. Expand your new project, select SQL Scripts->New->SQL Statement.
6. In the New SQL Statement window, select the project in which you want to create the new SQL statement, and click Next.
By default, the project from which this wizard was launched is used to store the new SQL statement. You can select from other projects that exist in Rational DA by clicking Project, or create a new project by clicking New.
7. Follow the steps outlined earlier in this article to re-create the FEMALEEMPLOYEES SQL statement. Once you have saved and tested this SQL statement, the Rational DA Data Project Explorer view should look like this:
You can see that the FemaleEmployees SQL statement can now be saved (and retrieved) from within your newly created Data Development project.
Wrapping it all up
In this article, I showed you how to leverage a database connection to create a new SQL statement and all the development assistance Rational DA gives you for creating SQL statements. I also showed you a way to ensure that you can retrieve your SQL statement later using a Data Development project. (Note: You can also use a Data Design project for this purpose more on that in a future article.) In the next article, Ill build on this example and show you all the things you can do in Rational DA with a saved SQL statement, as well as some other SQL editor features that I did not cover in this article.
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 eleven years of experience with DB2 and has written more than one hundred magazine articles and is currently working on his tenth book. Paul has authored the books DB2 9: New Features, Information on Demand: Introduction DB2 9 New Features, Off to the Races with Apache Derby, 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, including running with his dog Chachi, avoiding punches in his MMA class, and trying to figure out the world according to Chloë his daughter. You can reach him at: mailto:firstname.lastname@example.org.
IBM, DB2, 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.
Linux is a registered 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, 2007. All rights reserved.
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.