Rational Data Architect and DB2 9: Building an SQL Statement

Tuesday May 8th 2007 by Paul Zikopoulos
Share:

Part 1 of this series introduced the IBM Rational Database Architect (Rational DA) Database Explorer view. Part 2 discusses one of the features available from the Database Explorer view, namely, the ability to build SQL statements to run against your database.

In an article called “Rational Data Architect and DB2 9: The Database Explorer,” I introduced you to the IBM Rational Database Architect (Rational DA) Database Explorer view and showed you how to add database connections to it. If you’re not familiar with that process, or you didn’t read that article, it would be helpful to review it now. In this article, I want to show you one of the features available from the Database Explorer view, namely, the ability to build SQL statements to run against your database.

Building an SQL Statement

Rational DA comes with a built-in tool for creating SQL statements. You can access this tool from different parts of Rational DA, including the Database Explorer view.

Let’s look at how to build the following query using Rational DA:

SELECT DEPARTMENT.LOCATION, EMPLOYEE.EMPNO, 
EMPLOYEE.FIRSTNME, EMPLOYEE.LASTNAME, 
EMPLOYEE.PHONENO FROM DEPARTMENT, EMPLOYEE
WHERE DEPARTMENT.DEPTNO = EMPLOYEE.WORKDEPT 
AND EMPLOYEE.SEX = 'F'
ORDER BY LASTNAME DESC, FIRSTNME DESC

This query returns all the female employees in a company.

To build an SQL statement from the Database Explorer view, perform the following steps:

1.  Right-click a database connection object and select New SQL Statement.

You can select this option from a connected or disconnected database connection object. If you select New SQL Statement from a disconnected database connection object, it will automatically create a connection to the database. You can alternatively right-click the database object and select this option.

2.  In the New SQL Statement window, type a name for this statement in the Statement name field, and select the type of query you want to create in the Statement template list. For this example, specify FemaleEmployees as the Statement name, SELECT as the Statement template, and SQL builder in the Edit using box, and then click OK. The SQL builder opens with a SELECT template statement.

In the Statement template list, Rational DA provides source templates for all kinds of queries, such as: INSERT, UPDATE, DELETE, FULLSELECT, and WITH (for Common Table Expression definitions).

The SQL builder option invokes a graphical-assisted SQL creation tool that enables you to build SQL statements with simple drag-and-drop operations, point-and-click attribute changes, and more (shown in the remaining steps of this article).

The SQL builder looks like this:

The SQL editor option only opens the SQL editor (which is part of the SQL builder – it’s where the actual SQL statement resides in the SQL builder). The SQL editor provides code generation assistance such as syntax colorization and automated schema lookup, but doesn’t provide the drag-and-drop features associated with the SQL builder to build SQL statements. I cover the SQL editor in the next article in this series.

3.  Right-click in the Tables pane and select the Add Table option. The Add Table window opens. For this example, add the EMPLOYEE and DEPARTMENT tables located in the SAMPLE database for which you created a connection in the previous article.

You can see in the previous figure that only the HR and PAULZ schemas show up in the Add Table window. This is because the database connection object you created in the last article applied a filter.

You can also see in the previous figure that you have the option to create an alias for each table you add to a query. A table alias makes it easier to refer to tables in an SQL statement. If you use a table alias to represent a table (or view) used in your SQL statement, Rational DA will adjust the SQL syntax and its interface accordingly, as shown below:

Once you’ve added both tables to the SQL builder workspace, the Rational DA workspace should look similar to this:

Rational DA supports drag-and-drop operations from the Database Explorer view to the SQL builder. Simply right-click and drag the table you want to add to the Tables pane, and release the mouse button to move the table to this region.

This drag-and-drop support replaces the Add Table option outlined earlier in this step; this table selection process provides a more natural query-building experience:

4.  Right-click in the Tables pane and select the Create Join option to create the join illustrated by the following figure:

For this example, join the DEPARTMENT and EMPLOYEE tables using the DEPARTMENT.DEPTNO and EMPLOYEE.WORKDEPT columns, as shown in the previous figure.

If you select two join columns that are not compatible, the SQL builder prevents the join because it would result in an error at run time. An example of such an error message is shown below:

You can also use the drag-and-drop method to create a join directly from the Tables pane by hovering over the desired join column on one table and releasing the mouse button on the join column on the target table:

You can see in the previous figure that when you hover over a column, the underlying data type is revealed in its hover help – this enriches the design time experience and minimizes potential join errors. If you try to create a join between two data types that are incompatible, Rational DA changes the mouse icon to an error indicator and stops the operation.

5.  Click the check boxes that correspond to the columns that you want to include in the result set for this SQL statement from both tables. Ensure that your SQL builder looks similar to this by selecting the following columns: DEPARTMENT.DEPTNAME, DEPARTMENT.LOCATION, EMPLOYEE.EMPNO, EMPLOYEE.FIRSTNME, EMPLOYEE.LASTNAME, and EMPLOYEE.PHONENO.

Notice how the SQL statement that you are building in the SQL Source pane dynamically changes as you perform different operations such as adding or removing tables, projecting or restricting columns, identifying join predicates, and so on.

Also, note that Rational DA gives you visual cues as to business relationships encoded within the schema as they relate to each table’s columns. For example, in the previous figure, you can see that the DEPTNO and EMPNO columns are primary keys as indicated by their accompanying icon:. The DEPARTMENT table’s MGRNO and ADMRDEPT columns, as well as the EMPLOYEE table’s WORKDEPT column, are foreign keys linked to other tables, as indicated by their accompanying icon: .

You can also use the Columns tab at the bottom of the SQL builder to add columns to your SQL statement – simply click the Column list to select the column or columns that you want to add.

6.  Using the Columns tab, apply any additional sorting order using the Sort Type and Sort Order columns by clicking them and selecting the corresponding options. For this example, ensure that your Columns tab looks similar to this:

In the previous figure, you can see that the output of this SQL statement will be sorted first by LASTNAME in descending order from Z->A (a personal bias of mine), and second (as indicated by the Sort Order column) by FIRSTNME (in the same descending order, as indicated in the Sort Type column).

7.  Apply a conditional predicate to your SQL statement (for example, a WHERE predicate) by selecting the Conditions tab and building the condition by clicking each respective column:

For this example, use the condition shown in the previous figure. To enter a value in the Value field, simply click the field and type the value instead of building the expression.

Note: Don’t forget that the EMPLOYEE table’s SEX column is a CHARACTER-based column; therefore, the value you specify in the Value column is case-sensitive and must be delimited with single quotes; that why I used ‘F’ in the previous figure.

8.  Apply any grouping options that you want to add to your SQL statement using the Groups and Group Conditions tabs at the bottom of the SQL builder.

For this example, you can skip this step, but it’s good to know it’s there if the SQL statement you are building requires it.

9.  Press Ctrl+S to save your query. You query should now look similar to this:

10.  Test the query you just built by clicking Run->Run SQL or clicking the corresponding icon, as shown below:

The output of this action and the associated result set are shown in the Data Output tab at the bottom of the Rational DA workspace:

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 don’t 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 it’s 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.

Click for larger image
 

Rational DA provides other tools that help to minimize design time errors and I’ll cover those features in full detail in a follow-on article to this one.

Houston, we have a problem…I can’t 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 it’s 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 Explorerarticle. If you don’t 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 wasn’t 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, I’ll 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.

» See All Articles by Columnist Paul C. Zikopoulos

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

Trademarks

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.

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