Stored procedures basics

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