DB2 9.5 and IBM Data Studio: Building an SQL Statement

Tuesday Feb 5th 2008 by Paul Zikopoulos
Share:

Part six of this series discusses how to leverage the SQL editor in IBM Data Studio to quickly and easily build an SQL statement.

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:

Click for larger image

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

IBM Data Studio supports drag-and-drop operations from the Data Definition view to the SQL Builder. (Simply right-click and drag the table you want to add to the Tables window, and release the mouse button to move the table to this region.)

Drag-and-drop support allows you to circumvent using the Add Table option outlined in this step. This gives you a table selection process that provides a more natural query-building experience (although when you use this method you can’t directly define a table alias):

Click for larger image

6.  Right-click a blank area where the tables are shown in the SQL Builder, and select Create Join to create a join between the two tables using the join conditions shown below:

Click for larger image

You can also use the drag-and-drop method to create a join directly from this part of the Query Builder too. Simply hover over the desired join column on one table and release the mouse button on the join column on the target table, as shown below:

7.  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. For this example, select the following columns: DEPARTMENT.DEPTNAME, DEPARTMENT.LOCATION, EMPLOYEE.EMPNO, EMPLOYEE.FIRSTNME, EMPLOYEE.LASTNAME, and EMPLOYEE.PHONENO, as shown below:

Notice how the SQL statement that you are building in the SQL Builder continues to evolve as you perform different operations such as adding or removing tables, projecting or restricting columns, specifying join predicates, and so on.

You can also use the Columns tab at the bottom of the SQL Builder to add columns to your SQL statement – from there, simply click an empty field in the Column column, and select the column that you want to add, as shown below. (Repeat this process for all the columns that you want to add to your SQL statement if this is the manner in which you want to specify including columns for your query.)

8.  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, as shown below:

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 by FIRSTNAME (in the same descending order, as indicated in the Sort Type column). The sort order gives a hierarchical order to the different sorting definitions for your SQL statement.

9.  Apply a WHERE predicate to your SQL statement by selecting the Conditions tab and building the condition by clicking each respective column, as shown below:

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 for the EMPLOYEE table, the SEX column is a CHAR-based column; therefore, the value you specify in the Value column should be delimited with single quotes and case matters.

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

11.   Press Ctrl+S to save your query. Your query should now look similar to this:

12.   To run the query, select it from the SQL Scripts folder, right-click, and select Run SQL. The output of this action and the associated result set are shown in the Data Output tab at the bottom of the IBM Data Studio IDE:

Once you have your SQL statement, you can right-click to see a number of other actions that you can perform on it (aside from just running it to see if it returns the results you are expecting).

For example, through Visual Explain you can quickly generate an access plan graph of the SQL statement you just created:

I’ll actually take you through many of these options in future installments of this series.

Wrapping it up...

In this article, I showed you how to use the IBM Data Studio SQL Builder to build an SQL statement that can be subsequently used for many purposes. In the next part of this series, I’m going to show you some really cool features that are part of the SQL Builder such as formatting, syntax colorization, content assist, content tips, and a whole lot more! Having a sound understanding of these capabilities can really boost a developer’s productivity.

» See All Articles by Columnist Paul C. Zikopoulos

Trademarks

IBM and DB2 are trademarks or registered trademarks of International Business Machines Corporation in the United States, other countries, or both.

Other company, product, or service names may be trademarks or service marks of others.

Copyright International Business Machines Corporation, 2008. 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