DB2 Universal Database: Building Stored Procedures using the Database Definition View, Part 3

Tuesday Mar 28th 2006 by DatabaseJournal.com Staff

Part 3 of this article discusses how to build stored procedures using the Data Definition view.

by Paul C. Zikopoulos

In Part 1 of this series, I discussed how to use the Database Explorer view in the IBM Rational Application Developer for WebSphereSoftware (Rational AD) product to create and work with IBM DB2 Universal Database (DB2 UDB) for Linux, UNIX, and Windows database connections. Part 2 introduced you to some of the many capabilities provided by the Data Definition view in the Rational AD integrated development environment (IDE). In this part, we will look at how to build stored procedures using the Data Definition view. You will learn how to build stored procedures, use variables within them, and how to deploy and test them.

Note: Ensure that you are in the Data Perspective when performing the steps outlined in this article.

Building a Stored Procedure

In this section, I will show you how to build a stored procedure using the Data Definition view. To complete the steps in this part of the series, you need to import the SAMPLE database (or any database for which you want to create a stored procedure) into your DATABASEJOURNALSERIES project.

Prepping the Database Explorer View

In order to build a stored procedure using Rational AD in your database, the database must be in your project. For this article, we assume you imported the SAMPLE database.

As a quick refresher, you can follow the steps below:

1.  Right-click the SAMPLE database entry in the Database Explorer and select Reconnect, as shown below:

You will know that you have a connection to this database because you will be able to expand the contents of this folder (using the sign) and see the objects in the database, as shown below:

2.  Right-click the connected database and select Copy to Project, as shown below:

3.  Use the Browse button to locate the project you want to import the database into (for our example, select the DATABASEJOURNALSERIES project), and click Finish, as shown below:

When you complete the previous steps, the DATABASEJOURNALSERIES project in the Data Definition view should look similar to this. (The order of the objects within your project depends on when they were added.)

Note: In the previous figure, I have highlighted the PAULZ schema. Whatever user ID was used to create the SAMPLE database (the system user account you used when you entered the db2sampl command in Part 1) will be used for the user tables in this database. Specifically, we will be working with the STAFF table, so I would qualify this table as PAULZ.STAFF. If you are logged on to your system with the same user account that you used to create the SAMPLE database in Part 1, then you may not have to explicitly specify your schema to qualify the STAFF table when writing SQL. However, it is generally considered a ‘best practice’ to fully qualify an object with its schema name.

Building the Stored Procedure

In this section, I will show you how to build an SQL/PL-based stored procedure that takes a numeric input (which represents a department number) and returns the names of employees who work in the corresponding department. For this example, the source of the employee data is the STAFF table, which resides in the SAMPLE database.

To build this stored procedure in the Rational AD IDE, perform the following steps:

1.  Expand the schema where you want to create your stored procedure, right-click the Stored Procedures folder, and select SQL Stored Procedure, as shown below:

The stored procedure language used by DB2 UDB is called SQL/PL – it is an open ANSI-compliant language for writing business logic in databases. You can use Rational AD to create both Java-based and SQL-based stored procedures. In fact, Rational AD can be used to test any kind of stored procedures, including .NET CLR-based stored procedures in DB2 for Windows data servers.

Since the DB2 UDB V8.2 for Linux, UNIX, and Windows release, you no longer require a compiler to build SQL/PL procedures. You can use the Data Definition view in Rational AD to build stored procedures for DB2 UDB for i5/OS  and DB2 UDB for z/OS databases as well (provided you have the DB2 Connect product installed; however, this article assumes you are using an installation of DB2 UDB on the Linux, UNIX, or Windows platform).

2.  The New SQL Stored Procedure wizard opens. Type a name for this stored procedure (for this article, use the name STAFFSELECTION) in the Name field. In addition to this, ensure that the Build and Enable debugging check boxes are selected, and then click Next.

Selecting the Build check box instructs Rational AD to automatically build the stored procedure in the database immediately after you click Finish in this wizard. If you do not select this check box, you will have to explicitly tell Rational AD to build the stored procedure in the database at a later time.

As its name suggests, the Enable debugging check box enables this stored procedure for debugging. You can use the integrated debugger in Rational AD to set breakpoints, view variable values, and other typical things that debuggers do. Click Next.

3.  The SQL Statements page of this wizard opens. You can build the SQL statement for this stored procedure using the SQL Assist wizard by clicking on its associated button (the focus of the remainder of this step). Alternatively, you can enter it directly in the Statement details pane, and click Next.

For reference, the SQL statement used for this stored procedure is:


The remaining instructions in this step show you how to create the previous SQL statement using the SQL Assist feature in Rational AD. As you get more experienced with the Rational AD IDE, you will find there are many ways to build SQL statements like this, and their associated parameters, for your routines. For example, you can specify a parameter directly in the SQL, or use the Parameters page that follows (see Step 4).

The following steps (a-f) detail the method that I find the most direct and easiest to build the previous SQL statement for a stored procedure. (They include specification of the parameter for this routine.)

a.   Click SQL Assist. The Specify SQL Statement Information page opens:

If you wanted to build the SQL statement manually using the SQL editor, you could select the Manually type an SQL statement radio button. (In Part 4 of this series, I will take you through this process and introduce you to the SQL editor that is built into the Rational AD IDE and show how it can be used to build multiple statements.)

b.  Select SELECT from the SQL statement drop-down box (since we want to build a stored procedure that simply selects values from the STAFF table) and the Be guided through creating an SQL statement radio button, and click Next.

c.  The Construct an SQL Statement page for this wizard opens. Ensure that the STAFF (remember to use the appropriate schema) table is moved to the Select Tables window and click the Columns tab:

d.  Use the Columns tab to specify the columns to be retrieved by your SQL stored procedure. For this example, simply move all the columns to the Selected Columns pane (use the >> button), as shown below, and click Conditions:

Note: You can click the Joins tab to specify the join predicates if you are building an SQL statement that references more than one table. Since our example only uses the STAFF table, you can skip this tab entirely.

e.   Use the Conditions tab to specify the conditions for your SQL statement. Ensure that your page looks similar to this, and click Next:

When you click a column on this page (the figure above shows the Column column), you can select a corresponding item from the drop-down list that appears. In the previous figure, you would select STAFF.DEPT to select the column on which you want to build the WHERE condition. (Recall that this stored procedure will select all of the employees in the STAFF table based on the department they work in.) For the Operator column, ensure that you selected the = operator. In the Value column, you can select a value or just enter a parameter (denoted with a colon). For this example, type :INDEPT in the Value column.

In the same manner in which you selected objects and values in this step, you can group the result set of your stored procedure using the Groups tab.

In addition to this, you can use the Order tab to specify the order of the result set. To set the order of your result set, simply move columns to the Selected Columns window and select the order (ascending or descending), as shown below:

You can specify the order of the result set using the columns returned by the stored procedure, or an order based on a column that was projected out of the result set in a previous step.

by Paul C. Zikopoulos

f.  The Work with the SQL Statement page for this wizard opens. At this point, the SQL statement for your stored procedure is finished. (Note how the wizard generated schema and table qualifiers for the table and its columns – this is considered a ‘best practice’.) Click Finish to return to the New SQL Stored Procedure wizard.

Click for larger image

Before inserting the generated SQL statement into the New SQL Stored Procedure wizard, you can parse the generated statement by clicking Parse. If the statement fails to parse, an error will be returned to the Rational AD IDE.

For example, if you manually entered the SQL statement (or altered the generated statement) such that the SQL statement referenced a table that did not exist in the schema, Rational AD would return an error similar to this:

Click for larger image

This feature greatly accelerates the application development design time experience because developers do not have to comb through build errors to find the source of database-related errors in their code.

Another rapid application development feature is the ability to run the generated SQL statement before inserting it into the stored procedure. When you click Execute, you have the opportunity to run the generated SQL statement (including passing the runtime engine any input parameters) and to view the results, as shown in the following figure:

Click for larger image

Note: The Execute button will not be accessible if the generated SQL statement fails the parse operation.

After completing this step, the SQL Statements page for this wizard should look similar to this:

You can use the Add and Remove buttons to build more than one SQL statement for the stored procedure. You can import existing statements using the Import button.

4.  The Parameters page opens. Ensure that your page looks similar to the following figure, and click Next.

You can add, change, and remove parameters using the corresponding buttons on the right side of this window. Because we specified the parameter INDEPT in the generation of the SQL statement, Rational AD automatically placed this parameter in this stored procedure’s parameter list.

You can also use this page to specify the type of error-code handling mechanism to associate with the stored procedure, as shown below:

5.  The Code Fragments page opens.

You can see at this point that your stored procedure is really starting to take shape. Note how Rational AD shows you the data definition language (DDL) required to build this stored procedure throughout the entire process.

You can use this page to insert code fragments and make your stored procedures logic even more powerful. For example, you can enter an exception fragment that will launch other code to execute in the event of a certain error. You could attach a header fragment whereby you could execute some logic each time before the stored procedure is run. For example, many developers like to associate CREATE TABLE and INSERT INTO statements with their header fragments to automatically create and populate a table on which the stored procedure will be run. Typically, the logic will include an ‘after-the-run’ fragment to delete the table and its contents. This type of methodology accelerates the application development cycle and minimizes complexity.

For this example, just click Next on this page.

6.  The Summary page opens. Click Finish

To view the statement that will be used to generate this stored procedure, click Show SQL.

Note: Whenever the Finish button becomes active, you can click it to build the stored procedure. You may have noticed that as you progressed through the wizard, the Finish button became active before reaching the final page of this wizard, which indicates that the final pages in this wizard are optional items.

Since we specified that this stored procedure was to be built automatically by the Rational AD IDE when the wizard completed, Rational AD automatically builds the stored procedure when you click Finish. From the figure below, you can see that this operation was successful:

Since we instructed Rational AD to automatically build the stored procedure, there is no need to deploy it. At this point, you can test the stored procedure from the command line or the Rational AD IDE itself.

From the command line, just pass the argument (the department to which the employees belong) with the CALL statement, as shown below:

Alternatively, you can use the Rational AD IDE to test the stored procedure and pass an argument to it at run time by right-clicking the stored procedure and selecting Run, as shown below. (Simply enter a value for the INDEPT parameter as you did in the wizard in Step 3):

Wrapping it all up

In this article, I showed you how to build an SQL stored procedure that you can use to encapsulate business logic on the server side. One you have a stored procedure, there are all sorts of things you can do with it aside from just calling it from your application. The Rational AD IDE lets you wrap this stored procedure and expose it as a Web service with mere clicks of a button, and more.

In the next article, I’ll focus on the Data Definition view again, but I’ll show you how to create a user-defined function (UDF) that returns values, as well as a UDF that calls a Web service using the Rational AD integration with DB2 UDB. (Note that for Version 8 DB2 servers, you need the XML Extender. You also need to ensure that you have enabled your database for Web services for this section.)

About the Author

Paul C. Zikopoulos, BA, MBA, is an award-winning writer and speaker with the IBM Database Competitive Technologies team. He has more than ten years of experience with DB2 products and has written numerous magazine articles and books about it. Paul has co-authored the books: 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). Currently he is writing a book on the Apache Derby/IBM Derby database. You can reach him at: paulz_ibm@msn.com.


IBM, DB2, DB2 Universal Database, i5/OS, Rational, WebSphere, and z/OS 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.

Windows is a trademark of Microsoft Corporation in the United States, other countries, or both.

UNIX is a registered trademark of The Open Group in the United States and other countries.

Linux is a 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, 2006. All rights reserved.


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