So far in this series about the IBM Data Studio integrated development environment (IDE) that’s available with DB2 Version 9.5 (DB2 9.5), I’ve shown you how to set up database connection objects and the actions that you can perform on them. In addition, I introduced you (in Part 2) to some of the features available in the Database Explorer view, and Part 3 and Part 4 gave you a full tour of overview diagrams. In Part 5, I showed you how to point-and-click your way to OLE DB functions that can integrate data from external data sources that have an OLE DB provider. In this part, I’m going to show you how easy it is to leverage the SQL editor in IBM Data Studio to quickly and easily build an SQL statement.
Things you have to do to follow the examples in this article…
I recommend that you start with Part 1 because I tend to build on the concepts and objects created in this series sequentially. For this article, I assume that you’ve at least created the SAMPLE database (using the db2sampl –xml –sql command). Of course, I’m assuming that the SAMPLE database appears in the Database Explorer view and that you have a data development project created. You can use the DatabaseJournalProject that I quickly showed you how to create in Part 5, or create a new one, as shown below:
You can see in the previous figure the OLE DB function that we created in Part 5 of this series.
Building an SQL Statement using the IBM Data Studio
IBM Data Studio comes with a built-in tool called the SQL Builder for creating SQL statements. This tool can be invoked from different parts of the integrated development environment (IDE); for example, it’s available in various wizards for building routines, and it can be started on its own to build standalone SQL statements.
The steps that follow outline how to build the following query using the SQL Builder:
SELECT PAULZ.DEPARTMENT.LOCATION, PAULZ.EMPLOYEE.EMPNO, PAULZ.EMPLOYEE.FIRSTNME, PAULZ.EMPLOYEE.LASTNAME, PAULZ.EMPLOYEE.PHONENO FROM PAULZ.DEPARTMENT, PAULZ.EMPLOYEE WHERE PAULZ.DEPARTMENT.DEPTNO = PAULZ.EMPLOYEE.WORKDEPT AND PAULZ.EMPLOYEE.SEX = 'F' ORDER BY LASTNAME DESC, FIRSTNME DESC
This query returns a predefined number of attributes that are joined from the EMPLOYEE and DEPARTMENT tables for all female employees registered for a fictitious company.
To build an SQL statement using the IBM Data Studio SQL Builder, perform the following steps:
1. Select the SQL Scripts folder from your project, right-click, and select New->SQL Statement.
2. The New SQL Statement window opens. Select a project from the Project name drop-down list that you want to associate this SQL statement with; for our example, use the DatabaseJournalProject project. In addition, enter FEMALEPERSONNEL as the name for this SQL statement in the Statement name field, and select the SQL builder radio button. The default SELECT option in the Statement template field is fine since we are building a SELECT SQL statement. Now click Finish.
In the previous figure, you can see that you can use the Project name drop-down list to select any existing data development project that you’ve created in IBM Data Studio. If you want to create a new one, or don’t have one, click New and this will launch the New Data Development Project wizard.
IBM Data Studio provides templates for multiple SQL statements. The default template creates a SELECT statement, but as you can see in the previous figure, you can use the Statement template drop-down list to select one of several templates provided.
Also, note in the previous figure that you have the option to create your new SQL statement using the SQL Editor as opposed to the SQL Builder. While the SQL Editor lets you build your SQL statement, this option doesn’t give you all of the assistance features associated with the SQL Builder. For this article, we’ll stick with the SQL Builder. As you get more experienced with IBM Data Studio, you can use SQL Editor to build other SQL statements. In the end, you’ll decide just how much ‘hand holding’ you want; either facility will give you a productivity boost.
3. Depending on the way you configured your database connection object, you may be prompted to enter a valid set of credentials to connect to the database for which the database connection object was created. If this is the case, enter in the credentials for a user account with access to the data server, and click OK.
If the connection is successful, IBM Data Studio will create a template for the type of SQL statement you are trying to create, as shown below:
4. Right-click in the To add a table… pane and select Add Table.
5. The Add Table window pop-up appears. Select the EMPLOYEE and DEPEARTMENT tables, and then click OK. (Use the CTRL key to select multiple tables from the Add Table window – or add them one at a time.)
You may have noticed that the Add Table window has a Table Alias field. As its name implies, you can use this field to give a table an alternative name; however, when you add multiple tables to the SQL Builder at the same time, this field becomes inactive.
If you are dealing with complex table names, or simply want to refer to your tables using an alias name, you need to select them one at a time and assign each an alias name.
For this example, you can optionally remove the DEPARTMENT table that you’ve already added (simply click to select it and press Delete), and add the DEPARTMENT table again using an alias of DEPARTMENTALPERSONNEL.
The SQL Builder should now look like this:
You can see that the SQL Builder provides visual cues to certain relationships in each column. For example, a primary key is represented by 
and a foreign key relationship by 
. You can also hover over any column in the SQL Builder and hover help will let you know the underlying data types (especially useful when creating SQL statements that contain a join):
