DB2 9.5 and IBM Data Studio - Part 10: Building Stored Procedures

Monday May 19th 2008 by Paul Zikopoulos

Learn how to take an SQL statement that you built using either the SQL Editor or the SQL Builder and quickly turn it into a deployed SQL/PL stored procedure. In addition, this article explains how to move a data development project between servers.

In the first nine parts of this series, I’ve introduced you to some of the many features available within the IBM Data Studio integrated development environment (IDE) that’s available for use with the IBM data servers. Specifically, I’ve shown you how to set up database connection objects, the features available when working with these objects, how to generate an overview diagram of your database architecture, how to build OLE DB functions that can be used to easily integrate data from external data sources that have an OLE DB provider, and the various ways you can create an SQL statement using either the IBM Data Studio SQL Builder or SQL Editor.

In Part 10, we’re going take what we’ve learned so far and build some business logic that will serve as the basis for examples I’ll show you in subsequent articles in this series.

Note: Some of the screen shots in this article might look a little different from those in previous articles because I’m using a different version of the IBM Data Studio IDE on a different workstation. Nevertheless, you should be able to follow along easily with the examples in this article. If you ever need to move or copy data development projects to other installations of IBM Data Studio, see “Addendum: Moving data development projects between different workstations” at the end of this article.

Things you have to do to follow the examples in this article…

If you’re just joining this series, the good news is that there’s not a lot you have to do to catch up. To follow along, you need to know how to create database connection objects, work with those connections, create database development projects, and create SQL statements. If you need a refresher, just skim through the last few parts of this series.

From a sample perspective, I assume you created (or have full access to) the SAMPLE database and that it was created using the db2sampl –xml –sql command. In addition, I assume that you have a database development project called DatabaseJournalProject. You also need to have the FEMALEPERSONNEL SQL statement created. I showed you how to do this in Part 6 using the following DML:


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.

Building an SQL/PL procedure from an existing SQL statement

In our working example, we have an SQL statement that’s ready for production: FEMALEPERSONNEL. Perhaps in your environment, a separate team builds and tunes SQL statements, while application DBAs are responsible for exposing that logic through stored procedures, user-defined functions (UDFs), Web services, and so on. (The term routine is often used to refer to stored procedures, UDFs, or both: consider it a synonym for these objects.) This is yet another example where the IBM Data Studio IDE really shines because it allows multiple constituents within the application development framework to participate and collaborate on the work they are doing.

To build an SQL/PL stored procedure using the FEMALEPERSONNEL statement, perform the following steps:

1.  Select the Stored Procedures folder, right-click, and select New>Store Procedure.

2.  The New Stored Procedure wizard opens. On the Name and Language page, select the project where you want to create this stored procedure in the Project drop-down list. You can use the New button if you want to create a new project. For our example, select the DatabaseJournalProject (as shown in Step 4).

3.   Type a name in the Name field; for this example, use SP_FEMALEPERSONNEL (as shown in the next step).

4.   Leave the Language field set to SQL (since we are using an SQL statement to build this stored procedure). When you are finished, the New Stored Procedure wizard should look like the following; click Next:

5.  On the SQL Statements page, select the default SQL statement, Statement1, and click Remove.

As you can see, IBM Data Studio provides you with a sample SQL statement. You can create a new SQL statement in this wizard (more on that in a bit) or import one (or more).

6.   Click Import.

7.   The Import SQL Statements window opens. Select the FEMALEPERSONNEL SQL statement. You can see this SQL statement’s DML in the right pane (and the name of the query is shown as [FEMALEPERSONNEL]). Click Import.

The SQL Statements page should now look like this:

8.   Click Finish.

There are many other options available in the New Stored Procedure wizard. I cover them in the next article in this series when I show you how to build stored procedures from scratch when an SQL statement doesn’t exist.

As you can see, the SP_FEMALEPERSONNEL stored procedure has been added to the Stored Procedures folder. The code for this SQL/PL procedure is in the SQL Editor that I covered in previous parts of this series.

Now try to locate this stored procedure in the Database Explorer view – I’m willing to bet that you will have difficulties doing so (for good reason):

Notice that you can’t find it? That’s because it hasn’t been built yet! You have to explicitly build the business logic you created (or specify that it be built using an option in the New Stored Procedure wizard that I didn’t cover). Before we build it, let’s look at some other options.

9.  Select SP_FEMALEPERSONNEL, right-click, and select Deploy to build this stored procedure on the target SAMPLE database:

10.  The Deploy Routines wizard opens. On the Select Connection page, ensure that the Use current database radio button is selected in the Target database frame.

If you want to deploy this routine on a different database from the one your database development project was bound to when it was created, select the Use different database radio button and use the Database drop-down list to select a database where this stored procedure should be built:

In the previous figure, you can see that I could optionally deploy this SQL/PL procedure on a database called STLEC1. STLEC1 happens to be a DB2 for z/OS data server, and since the SQL API (and SQL/PL language) is more than ninety percent common across the DB2 data server family, I could definitely deploy this procedure on that data server if I wanted to. You can obviously use this feature to deploy business logic built in a data development project that’s bound to a different data server (such as a development database) compared to the production database.

If you didn’t have a database connection object defined for the target database, you could define it in this window using the Connection button. This launches you into the Database Connection wizard that I introduced you to in Part 1 of this series.

11.   You can use the Schema field in the Target schema for deploying an unqualified routine box to specify the schema for the stored procedure. For this example you can ignore this field.

12.   Select the Treat duplicates as errors radio button in the Duplicate handling box.

You can tell IBM Data Studio what to do when you try to deploy a stored procedure on a database where a stored procedure with the same name within the same schema already exists. If you select the Drop duplicates radio button, IBM Data Studio will drop the existing stored procedure and create a new one with the same name using the SQL/PL code in your project.

You can deploy multiple stored procedures in a single deployment using IBM Data Studio as well. If you select the Ignore duplicates and continue to the next routine radio button, IBM Data Studio will simply skip any stored procedures with the same name and build those that are unique on the target database.

13.   Select the Deploy by building the source radio button to do a full build of the stored procedure and deploy the SQL/PL source and the binaries to the database. Now click Next.

The Deploy by building the source option instructs IBM Data Studio to fully build the routine and deploy both the source and the binaries to the database.

The Deploy using binaries if available in the database radio button deploys binaries that you’ve built on the database instead of doing a full build. If the routine’s binaries are not available, IBM Data Studio will do a full build of the routine before deploying it. To simply deploy the routine’s source to a database, select Deploy source to the database.

14.  The Deploy Routines wizard opens. On the Routines Options page, select the Enable debugging check box so you can leverage the distributed debugging capabilities that are part of IBM Data Studio. Click Next.

You can use the Precompile options field to provide any options for the compilation of your routine.

15.  On the Summary page, click Finish.

You can use the Data Output view to see the results of the requested build operation. If your routine built successfully, you will see messages similar to the following ones:

After we used the wizard to create our stored procedure in step 8, the routine didn’t appear in the database because it wasn’t yet built and deployed on it. If you look in the Database Explorer view now, you should see that the routine is indeed deployed on the server. (If you don’t see it, right-click the Stored Procedures folder and click Refresh):

At this point, any options that are available in the pop-up menu for objects in the Database Explorer view are applicable to your routine. For example, to run it, right-click the routine and select Run:

You can also generate a script file that would create and deploy this procedure by selecting the Generate DDL option:

Wrapping it all up

In this article, I showed you how to take an SQL statement that you built using either the SQL Editor or the SQL Builder and quickly turn it into a deployed SQL/PL stored procedure. In addition, I also showed you how to move a data development project between servers. In my next article, I’ll show you how to build a stored procedure from scratch and dive deeper into some of the options I didn’t cover in this article.

Addendum: Moving data development projects between different workstations

It’s easy to move projects to different workstations or installations if you ever install a different copy of the IBM Data Studio IDE on your workstation or engage the services of other personnel to work with you on your projects. To export a project from a workstation, perform the following steps:

1.   Select the project you want to export (for our example, DatabaseJournalProject), right-click, and select Export. For example:

2.   Select File System and click Next:


If you don’t see the File System folder in the default expanded tree view, you might have to expand the Other folder to find this option.

3.   Select the data development project that you want to export from the left side of the File system page of the Export wizard, and in the right pane optionally select the components of the project that you want to export. For example, on the following page I’ve only chosen to export the FEMALEPERSONNEL SQL statement along with the project’s metadata:

4.   Type a target directory for the project in the To directory field.

5.   Select any options that you want to apply to this export operation in the Options box. For this example, the defaults are fine; click Finish.

To import your exported project into a new data development project, perform the following steps:

1.   Select the destination project, right-click, and select the Import option. (You can see in the following figure that I’ve created a new data development project with the same name as the previous one.)

2.   Select File System and click Next:

3.   The Import wizard opens. Select the project in the left pane of the File system page and the components of this project that you want to import into the new data development project. For this example, select everything in the import file as shown below, and then click Finish.

As you can see, the elements have been imported into the Data Project Explorer view in the data development project that you specified:

Note that if you imported a stored procedure or a user-defined function into your new data development project, these objects aren’t created in the database that the new project is linked to (assuming it’s a different database). You’d have to deploy these objects. In this example, all my data development projects connect to the SAMPLE database, so I wouldn’t have to rebuild any routines if I had them in this imported project.

» See All Articles by Columnist Paul C. Zikopoulos


IBM, DB2, and z/OS are trademarks or registered trademarks of International Business Machines Corporation 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, or service names may be trademarks or service marks of others.

Copyright International Business Machines Corporation, 2008.



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.

Mobile Site | Full Site