Introduction to DB2 Stored Procedures

When it comes to stored procedures, there seem to be three kinds of developers: those who love them, those who hate them, and those who don't really know what the heck they are. DBAs usually fall into the first category, coders in the second, scripters in the third (as a former scripter, I can say that). But in honor of all three, I'll go through a brief overview of stored procedures in general, including when and when not to use them. Then I'll introduce DB2's version. If you don't love them already, you will by the time we're done.
What is a Stored Procedure?
If a procedure is encapsulated logic that can be invoked from within your application, then a stored procedure is simply a procedure that is stored on the database server. Usually written in SQL, the stored procedure benefits from the power and proximity of the database from which it is managed.
If you've used a database at all in your applications, then you've most likely coded in SQL at some point. To turn it into a stored procedure, you can include it within a CREATE PROCEDURE statement. Technically, it would be a stored procedure and could thus be invoked from external applications. But the true power of the stored procedure comes from its control syntax and ability to process multiple statements. In other words, while you would rarely use a stored procedure for a single SELECT statement, you could add INSERT, UPDATE, and DELETE statements, all controlled with IF/ELSE logic, for example. Just about any database manipulation you're doing with your app code can be done within a stored procedure instead, with all of its inherent benefits.
When Do I Use Stored Procedures?
Coders might argue that you never really need a stored procedure. All of your database calls can be done programmatically. That's true, but somewhat limiting. Stored procedures can do a lot of things for you that even the most elegant code cannot.
Chief among these, stored procedures save processor time by allowing you to offload database calls to the database server itself. Most databases load the compiled procedures into memory the first time they're called, or even before, dramatically reducing overhead when you're doing the same essential process multiple times (such looping operations are very common in database manipulation).
Stored procedures offer you many other benefits, as well.
 They allow you to encapsulate code. In other words, the database operation appears once, in the stored procedure, not multiple times throughout your application source. This improves debugging as well as maintainability.
 Changes to the database schema affect your source code in only one place, the stored procedure. Any schema changes then become a DBA task rather than a wholesale code revision.
 Since the stored procedures reside on the server, you can set tighter security restrictions on the client space, saving more trusted database permissions for the well-protected stored procedures themselves.
 Since stored procedures are compiled and stored outside the application, they can use more sensitive variables within the SQL syntax, such as passwords or personal data, that you would avoid using in scripts or remote calls.
 Using stored procedures greatly reduces network traffic.
As a further illustration of this last point, suppose you want to update a customer record but you're not sure if the record even exists. One way is (a) to SELECT the record to see if the customer exists, (b) to UPDATE the record if it does, and (c) to INSERT a new record if it does not.
If you just put a series of SQL statements in your client code, each line is executed by sending a message over the network to the server, usually getting a response in return. But a stored procedure resides on the server. When called from the client application, it executes on the server and only has to respond when returning the final result set to the client, saving lots of back-and-forth traffic.
One other benefit you'll find with DB2 stored procedures in particular, the distinction of which will be explained in but a moment, is the Development Tooling support. The Enterprise edition of DB2 UDB (Universal Database) ships with a set of developer tools that allow for fast, reliable creation of stored procedures in both SQL and Java. Using the Wizards and Query Builders, you can create complex procedures without typing a word of code.

DB2 Universal Database for Linux, UNIX and Windows- Download Now!
DB2 is the fastest growing database on UNIX and Windows. Judge for yourself -- get DB2 Version 8 via free download.


When Would I Not Use Stored Procedures?
Whole discussion boards have been devoted to the pro/con debate on stored procedures. But when it comes down to it, you have only a few occasions when it might not be possible or prudent to use them.
 You don't have the database permissions to create them. Or a cooperative DBA.
 You're only using a few SELECT statements in your application and can't really benefit from the performance and organizational boosts offered by stored procedures (though you might still profit from the other gifts of the stored procedure).
 Your organization is undergoing a major code migration and wants to avoid encapsulating your source in external procedures. However, this is debatable as well since you may actually benefit from the stability of SQL syntax.
What's Special About DB2's Stored Procedures?
DB2 expands on the definition of a stored procedure by allowing you to code them in just about any language you may need. DB2 gives you two different kinds of stored procedures: a SQL procedure, and an external procedure. In DB2, a stored procedure is a database object that encapsulates control-flow logic, a mini application really, that runs in a "stored procedure address space." Going back to the idea that a stored procedure is simply a chunk of compiled code, DB2 stored procedures can be written in your language of choice, compiled, then referenced with SQL syntax.
The major difference between a SQL procedure and an external procedure is that SQL procedures are, obviously, written in SQL. This means that the logic for the procedure is defined within the SQL procedure body itself. By contrast, external procedures identify, load, and run a unit of code written in an entirely different language. Early stored procedures were all essentially external, generally written in C. Nowadays, when we talk about stored procedures, most people think of SQL procedures which are written in easy to implement high-level SQL syntax. But DB2's external procedures provide a handy option for the codernauts and those wanting to implement more complex logic than SQL can support, using languages such as Visual Basic .NET, C, C++, C#, COBOL, Java, or REXX.
Huh?
How about an example?
The following external procedure demonstrates how Java can be used to control the logic of a database operation. If you haven't used stored procedures before, then this type of code might look familiar. It is essentially two SQL statements, one of which will be executed depending on a control-flow condition. The difference here, though, is that the "application" in this case is the stored procedure-body - the body in which the stored procedure functionality is defined.
/**
* JDBC Stored Procedure ADMINISTRATOR.UPDATE_SAL
*/
package proc.useraccount;

import java.sql.*; // JDBC classes

public class MYJAVACLASS
{
public static void UpdateSal (String [] empNum, Double rating )
throws SQLException
{
int errorCode = 0;
boolean bFlag;
String sql;
PreparedStatement stmt = null;

try
{
Connection con = DriverManager.getConnection("jdbc:default:connection");

if ( rating = 1 ) {
sql = "UPDATE employee "
+ "SET salary = salary * 1.10, "
+ "bonus = 1500 "
+ "WHERE empno = '" + empNum + "'";
}
else {
sql = "UPDATE employee "
+ "SET salary = salary * 1.05, "
+ "bonus = 1000 "
+ "WHERE empno = '" + empNum + "'";
}
stmt = con.prepareStatement( sql );
bFlag = stmt.execute();
}
catch ( SQLException sqle)
{
errorCode = sqle.getErrorCode();
throw new SQLException ( errorCode + " : " + errorLabel + " FAILED" );
}
}
If you're using the DB2 Development Center tooling, then once the procedure is created, the following chunk of code is automatically generated by DB2. It creates a handle for the above procedure in the database. When you call this procedure from your application, the above code is then looked up, identified, found, and run by DB2.
CREATE PROCEDURE ADMINISTRATOR.UPDATE_SAL
( IN empNum CHAR(6), IN rating SmallInt )
SPECIFIC ADMINISTRATOR.UPDATE_SAL
DYNAMIC RESULT SETS 0
NOT DETERMINISTIC
LANGUAGE JAVA
PARAMETER STYLE JAVA
EXTERNAL NAME 'MyJavaClass.UpdateSal'
FENCED
THREADSAFE
For comparison, here is the same basic operation written as a SQL procedure. You'll see that the UPDATE statement remains the same, but control flow operations are now managed by SQL syntax included in the CREATE PROCEDURE statement itself.
CREATE PROCEDURE ADMINISTRATOR.UPDATE_SAL
(IN empNum CHAR(6), IN rating SMALLINT)
LANGUAGE SQL
BEGIN
IF rating = 1 THEN
UPDATE employee
SET salary = salary * 1.10,
bonus = 1500
WHERE empno = empNum;
ELSE
UPDATE employee
SET salary = salary * 1.05,
bonus = 1000
WHERE empno = empNum;
END IF;
END
Once your stored procedure has been successfully created and you have the privileges to invoke it (you will have these by default if you are the creator) then the procedure can be called.
As you can see, SQL procedures give you an easy-to-use syntax for handling multiple statements and control flow within the procedure itself. External procedures give you the power and flexibility of using your language of choice, opening up all new possibilities for straight coders and DBAs alike.
How Do I Use Stored Procedures in my Application?
Just as your favorite language has a way to talk to your database, it also has a way to invoke your database's stored procedures. Below is some C# which invokes the above example. In fact, because of the uniform interface of DB2 stored procedures, this code could be used with either example since they have the same parameter list.
For this snippet, imagine a form with an "Employee Number" textbox that supplies the IN parameter for the procedure.
conn = new DB2eConnection(connString);
conn.Open();
DB2eParameter p1 =
new DB2eParameter("@p1", DB2eType.Char,6);
p1.Value = txtEmpNum.Text;
DB2eParameter p2 =
new DB2eParameter("@p2", DB2eType.SmallInt);
p2.Value = 1;
DB2eCommand cmd =
new DB2eCommand("ADMINISTRATOR.UPDATE_SAL", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(p1);
cmd.Parameters.Add(p2);
DB2eDataReader reader = cmd.ExecuteReader();
How Do I Get Started?
As mentioned earlier, DB2 makes it easy to create stored procedures by giving you not one but two different wizards, one for SQL procedures and one for Java procedures. These can both be found in the DB2 Development Center. In creating your stored procedures, you can either code them directly or use a Query Builder to help you assemble them without coding a single command.
In addition to this portal, you have several resources available for learning more. One excellent tutorial shows you the basics of creating a stored procedure, then using it with Visual Studio .NET: "Build and access DB2 stored procedures using mobile clients".
If you run into trouble, you can find extensive help in the DB2 Information Center: Stored Procedures.
Also be sure to check out "IBM DB2 Development Add-Ins for Visual Studio .Net" for more detailed information on DB2's integration with VS.NET which allows you to make DB2 stored procedures right within Visual Studio.
And of course, you'll want to download DB2 itself, if you haven't already. Look for DB2 Universal Database, Enterprise Edition v8.2. Or perhaps you'd prefer to tinker with the beta to the 64-bit version, DB2 UDB V8.2.2 Beta, which supports the tech preview of Windows Server 2003 x64 Edition.