DB2 9.5 and IBM Data Studio - Part 10: Building Stored Procedures
By Paul Zikopoulos
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:
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.
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 FEMALEPERSONNELstatement,
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.
» 
Comment and Contribute