Stored procedures

Stored procedures can help improve application performance and reduce

database access traffic. All database access must go across the network, which,
in some cases, can result in poor performance. For each SQL statement, a
database manager application must initiate a separate communication with DB2.
To improve application performance, you can create stored procedures that run
on your database server. A client application can then simply call the stored
procedures to obtain results of the SQL statements that are contained in the
procedure. Because the stored procedure runs the SQL statement on the server
for you, database performance is improved. In addition, stored procedures can
help to centralize business logic. If you make changes to a stored procedure, the
changes are immediately available to all client applications that use it.

Stored procedures are programs that have the following characteristics:

• Contain procedural constructs with SQL statements
• Are stored in databases and run on DB2 servers
• Can be called by name by an application that is using SQL
• Allow an application program to run in two parts: the application on the
client and the stored procedure on the server
The following figures show how two client applications access a database
located on a database server. A client application that does not use stored
procedures to access a database can require more network traffic. A client
application that takes advantage of a stored procedure can help to reduce
network traffic and the number of times the database is accessed. A typical
stored procedure contains one or more SQL statements and some manipulative
or logical processing in a programming language. The client application program
uses the SQL CALL statement to start the stored procedure. When the stored
procedure runs, it locally accesses the database on the database server and
returns requested information to the client application.
Figure 1. A client application that does not use a stored procedure
Figure 2. A client application that uses a stored procedure

To use a stored procedure, you need to write two programs: the stored
procedure, which runs on a database server, and a client application, which runs
on a client workstation or a middleware server (such as a Web server). The client
application calls the stored procedure by using one of the available API methods.
A stored procedure follows certain conventions for exchanging data with a client
application.

A stored procedure does not connect to the database, but relies on the database
connection already established by the client. The call across the network
includes parameters that are required by the stored procedure. The stored
procedure uses the parameters to complete its logic when it runs. It can return a
set of values, rows, or modified parameters to the calling client application.
The Development Center is designed to give you a full development environment
for working with stored procedures, from creating to deploying.

Related tasks
Building routines
Exporting routines
Importing routines
Editing source code
Filtering in the Server View
Dropping routines or objects from a database
Removing routines or objects from a project

Related information
Creating stored procedures
Running routines
Debugging
Deploying routines

Benefits of using stored procedures

Applications that use stored procedures have the following advantages:
Reduced network usage between clients and servers
A client application passes control to a stored procedure on the database
server. The stored procedure performs intermediate processing on the
database server, without transmitting unnecessary data across the
network. Only the records that are actually required by the client
application are transmitted. Using a stored procedure can result in
reduced network usage and better overall performance.

Applications that execute SQL statements one at a time typically cross the
network twice for each SQL statement. A stored procedure can group SQL
statements together, making it necessary to only cross the network twice
for each group of SQL statements. The more SQL statements that you
group together in a stored procedure, the more you reduce network usage
and the time that database locks are held. Reducing network usage 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

present only a subset of the data to the user, can generate excessive
network usage 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 have access to increased
memory and disk space on the server computer. These applications also
have access to software that is installed only on the database server. You
 can distribute the executable business logic across machines 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 DBA or
developer who builds 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.
This can reduce the number of users who require 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. By reusing one common
procedure, a stored procedure can provide a highly efficient way to
address these recurrent situations.
Centralized security, administration, and maintenance for common
routines
By managing shared logic in one place at the server, you can simplify
security, administration, and maintenance . Client applications can call
stored procedures that run SQL queries with little or no additional
processing.

Parent topic: Stored procedures

Related concepts
Languages for stored procedures
SQL stored procedures
Java stored procedures

Languages for stored procedures

Using the Development Center, you can create stored procedures in Java or SQL. Both languages
create stored procedures that are highly portable from platform to platform. The language that you
choose depends on your development environment.

SQL stored procedures

SQL provides procedural constructs for writing stored procedures. Writing stored procedures with the
SQL procedure language has the following advantages:
• You can support database modules that are written completely in an SQL language.
• You can quickly learn to write stored procedures with the SQL procedure language. This ease
of learning is especially true if you have experience with other database languages.
• SQL stored procedures are fast because the SQL routines run as compiled routines.
• You can debug SQL stored procedures on Windows, AIX, Sun, and Linux on DB2 Universal
Database servers and on DB2 for z/OS Version 8 server.
• With SQL stored procedures, you can call other SQL procedures, nesting calls up to 16 layers.
SQL stored procedures have size and parameter limitations, depending on the version of DB2 that you
are running:
• For DB2 for OS/390 and z/OS versions 6 and 7, the maximum size for SQL stored procedures
is 32 KB.
• For DB2 for z/OS version 8, the maximum size for SQL stored procedures is 2MB
• For DB2 for Windows and UNIX versions 7 and 8.1, the maximum size for SQL stored
procedures in is 64 KB.
• For DB2 version 8.2, the maximum size for SQL stored procedures is 2 MB

Java stored procedures

Writing stored procedures with Java has the following advantages:

• You can establish a common development environment and common language. This
commonality can be shared by the stored procedures on the database server and the client
application that runs on a client workstation or a middleware server (such as a Web server).
• You can reuse code from Java methods that you have already written.
• The input/output of files is possible with Java stored procedures, within the security limits of
Java. SQL stored procedures do not support the input/output of files.
Java stored procedures have size and parameter limitations, depending on the version of DB2 that you
are running:
• For DB2 for z/OS version 7, the maximum size for Java stored procedures is 32 KB.
• For DB2 for z/OS version 8, the maximum size for Java stored procedures is 10MB
Restriction: For iSeries version 5 release 3, the following are not supported for Java stored
procedures:
• PARAMETER STYLE DB2GENERAL
• BINARY and VARBINARY built-in data types

Parent topic: Stored procedures

Related concepts
Benefits of using stored procedures
SQL stored procedures
Java stored procedures

Related reference
Name lengths and restrictions for identifiers and parameters

SQL stored procedures

An SQL stored procedure is a stored procedure in which the source code is part
of the CREATE PROCEDURE statement. The part of the CREATE
PROCEDURE statement that contains the code is called the stored procedure
body.

SQL stored procedure definitions provide the following information:

• The stored procedure name
• Parameter attributes
• The language in which the stored procedure is written. For an SQL stored
procedure, the language is SQL
 • Information about the SQL stored procedure that is used when the stored
procedure is called. This information can include run-time options and
whether the stored procedure returns result sets

Unlike a CREATE PROCEDURE statement for an external stored procedure, the

CREATE PROCEDURE statement for an SQL stored procedure does not specify
the EXTERNAL clause. Instead, an SQL stored procedure has a stored
procedure body, which contains the source statements for the stored procedure.

The following figures show example CREATE PROCEDURE statements for

simple SQL stored procedures. The second example shows a simple SQL stored
procedure for z/OS. The stored procedure name, the list of parameters that are
passed to or from the stored procedure, and the LANGUAGE parameter are
common to all stored procedures. The LANGUAGE value of SQL is particular to
an SQL stored procedure. Line numbers are included here for convenience.

Figure 1. CREATE PROCEDURE for a simple SQL stored procedure

1 CREATE PROCEDURE SCHEMA.Procedure6 ( INOUT var0 varchar(9) )
2 LANGUAGE SQL
--------------------------------------------------------------
3 -- SQL stored procedure SCHEMA.Procedure6
--------------------------------------------------------------
4 P1: BEGIN
-- Declare cursor
DECLARE cursor1 CURSOR WITH RETURN FOR
SELECT * FROM STAFF;

END P1

Line Description

1 The SQL stored procedure name is SCHEMA.Procedure6. The InOut parameter has data type varchar(9).

2 LANGUAGE SQL indicates that this is an SQL stored procedure.

3 Shows a comment for the SQL stored procedure.

4 Begins the body of the SQL stored procedure. All SQL stored procedure bodies consist of one or more statements nested within
a BEGIN and an END keyword.

Figure 2. CREATE PROCEDURE for an SQL stored procedure for z/OS

1 CREATE PROCEDURE SCHEMA.Proc1111 ( )
RESULT SETS 1
2 LANGUAGE SQL
MODIFIES SQL DATA
3 COLLID TEST
4 WLM ENVIRONMENT WLMENV1
5 ASUTIME NO LIMIT
RUN OPTIONS 'NOTEST(NONE,*,*,*)'
----------------------------------------------------------
6 -- SQL Stored Procedure
 ----------------------------------------------------------
7 P1: BEGIN
-- Declare cursor
DECLARE cursor1 CURSOR WITH RETURN FOR
SELECT SCHEMA, NAME FROM SYSIBM.SYSROUTINES;
-- Cursor left open for client application
OPEN cursor1;
END P1

Line Description

1 The SQL stored procedure name is SCHEMA.Proc1111.

2 LANGUAGE SQL indicates that this is an SQL stored procedure.

3 Specifies a collection ID of TEST.

4 Specifies a Workload Manager (WLM) environment.

5 Specifies no processor time limit for running the routine.

6 Shows a comment for the SQL stored procedure.

7 Begins the body of the SQL stored procedure. All SQL stored procedure bodies consist of one or more statements nested within
a BEGIN and an END keyword.

Parent topic: Stored procedures

Related concepts
Benefits of using stored procedures
Languages for stored procedures
Java stored procedures

Related reference
SQL stored procedure

Java stored procedures

Java stored procedures built by the Development Center conform to the SQLJ
(SQL in Java) Routines specification. Java stored procedures must follow these
guidelines:
• The method that is mapped to the stored procedure must be defined as a
public static void method.
• Output and InOut parameters must be set up as single element arrays.

Java stored procedures can access DB2 data using either Java Database
Connectivity (JDBC) API calls or SQLJ statements. The Development Center can
create Java stored procedures that use either format.
The Development Center defines Java stored procedures in the catalog table
with LANGUAGE JAVA and PARAMETER STYLE JAVA.
Restriction: For iSeries version 5 release 3, the following are not supported for
Java stored procedures:
• PARAMETER STYLE DB2GENERAL
• BINARY and VARBINARY built-in data types

Parent topic: Stored procedures

Related concepts
Benefits of using stored procedures
Languages for stored procedures
SQL stored procedures

Related reference
Java stored procedure with dynamic SQL using JDBC database access
Java stored procedure with static SQL using SQLJ database access