You are currently on IBM Systems Media’s archival website. Click here to view our new website.

MAINFRAME > Administrator > Db2

Creating DB2 Java Stored Procedures on z/OS

Stored procedures are a convenient option for connecting applications to the database. However, the task of manually creating stored procedures is anything but convenient-it can in fact be a labor-intensive and sometimes error-prone process. That's why the recent availability of IBM's DB2* Stored Procedure Builder (SPB) for creating Java* stored procedures on z/OS* is good news for mainframe developers.

This article examines stored procedures and shows you the advantages of using the SPB to create and run interpreted Java stored procedures on DB2 UDB for OS/390* and z/OS V7. For comparison, we'll also cover the process of manually creating stored procedures.

A Look at Stored Procedures

A stored procedure is an application program executed by DB2 in response to a single SQL CALL statement. It can contain SQL statements as well as any application or business logic. Introduced with DB2 V4 and enhanced in subsequent releases, stored procedures can be written in any language supported by the database server, including C, C++, COBOL, PL/I, S/390* Assembler, REXX, procedural SQL and Java. Support for invoking a stored procedure and processing its result sets is built into many client applications, as well as ODBC, JDBC and SQL for Java (SQLJ) standards.

Code reuse, isolation of business rules, ease of maintenance and security are some of the key benefits of using stored procedures for both local and remote server environments.

Stored procedures allow organizations to use a single copy of code, which is easier to maintain and facilitates business logic reuse. Storing stored procedures in DB2 rather than on a client allows the stored procedure owner to control and protect the SQL tables. This capability is designed to enhance security, because end users don't require table privileges-they only need authorization to execute the stored procedure.

Additionally, stored procedures can help improve performance when operating in a client/server environment. When SQL statements are issued from a client application, a network send/receive operation is required for each statement. When SQL statements are encapsulated into a stored procedure, the SQL is executed locally at the server. This may help to reduce network transmission time and limit the contention for DB2 locks (because they aren't held across network send/receive operations) and, ultimately, result in lower processing times executing the SQL statement.

Peggy Rader is a senior software engineer for IBM. Peggy can be reached at



2019 Solutions Edition

A Comprehensive Online Buyer's Guide to Solutions, Services and Education.

An Intuitive Approach to DB2 for z/OS SQL Query Tuning

Real-world samples provide valuable lessons.


A Db2 Utilities Migration Project

IBM Systems Magazine Subscribe Box Read Now Link Subscribe Now Link iPad App Google Play Store
Mainframe News Sign Up Today! Past News Letters