DB2 stored procedures

Stored procedures contain procedural constructs with SQL statements. Stored procedures are stored in DB2 databases and run on DB2 servers. An application can call a stored procedure by name and run the SQL statements that are included in the stored procedure. The application can be on the client, while the stored procedure executes its logic on the server. Stored procedures and user-defined functions are sometimes referred to collectively as routines.

Applications that use stored procedures have the following advantages:

Reduced network traffic

Using a stored procedure reduces network traffic and improves overall application performance because a stored procedure can perform intermediate processing on the database server, without transmitting unnecessary data across the network. The stored procedure transmits only the records that are required by the client application.

SQL statements issued one at a time by an application typically cross the network twice. A stored procedure can group SQL statements together, so that each group of SQL statements crosses the network once. The more SQL statements that you group in a stored procedure, the more you can reduce network traffic and the length of time that database locks are held. Reducing network traffic and the length of database locks improves overall network performance and reduces lock contention problems.

Applications that process large amounts of SQL-generated data, but show only a subset of the data to the user, can cause excessive network traffic because all of the data is returned to the client before final processing. A stored procedure can do the processing on the server, and transmit only the required data to the client, which reduces network usage.

Enhanced hardware and software capabilities

Applications that use stored procedures can access increased memory and disk space on the server system. These applications also can access software that is installed only on the database server. You can distribute the executable business logic across systems that have sufficient memory and processors.

Improved security

By including database privileges with stored procedures that use static SQL, the database administrator (DBA) can improve security. The person who creates the stored procedure must have the database privileges that the stored procedure requires. Users of the client applications that call the stored procedure do not need such privileges, which can reduce the number of users who require the privileges.

Reduced development cost and increased reliability

In a database application environment, many tasks are repeated. Repeated tasks might include returning a fixed set of data or performing the same set of multiple requests to a database. A stored procedure is an efficient way to perform such recurring tasks.

Centralized security, administration, and maintenance for common routines

Managing shared logic in one place on the server simplifies security, administration, and maintenance. Client applications can call stored procedures that run SQL queries with little or no additional processing. Changes to a stored procedure are immediately available to all client applications that use it.

Overview of DB2 stored procedures tasks
The workbench provides numerous features to help you develop DB2 stored procedures.

Tips for developing DB2 stored procedures

Creating DB2 stored procedures
You can use the New Stored Procedure wizard to create DB2 stored procedures in SQL or Java.

Preparing to debug a DB2 SQL stored procedure
After you build an SQL stored procedure in debug mode, you can start the debugger and step through the source code. This topic describes how to prepare an SQL stored procedure for debugging.

Stored procedure task restrictions
Some restrictions apply to tasks that are available for DB2 UDB stored procedures in the workbench.

Stored procedure and user-defined function support by DB2 Universal Database server type
The following table summarizes the types of stored procedures and user-defined functions (UDFs) that are supported on various DB2 UDB servers.

Related reference
Authorities for DB2 UDB stored procedures and user-defined functions