ISTEK Consulting - DB2 Stored Procedures and the Mainframe

Stored Procedures are a very powerful database feature that can often be employed to great benefit. However, in the context of an application running entirely on the mainframe, using stored procedures can bring you no benefit and sometimes can even be detrimental to your application. Lets analyze why this can be...

First, let's review some of the benefits of stored procedures.

Reducing network traffic and client code complexity in a client/server environment - if the application logic requires processing large result sets, and that logic can be encapsulated in a stored procedure, we can reduce the amount of data going back and forth between the client and the server.
Centralizing common business rules for accessing and manipulating the data.
Providing a standard API(SQL) and an application server type environment (transactionality, work load managed address spaces) for all clients on and off the host.

Now lets review some details about how stored procedures are implemented in DB2.

In DB2-land, stored procedures can be of two types, fenced and non-fenced. Non-fenced stored procedures run in a DB2 address space. Fenced stored procedures run in separate address spaces from DB2. If a non-fenced stored procedure crashes badly, it can possibly bring down DB2. For this reason, the mainframe version of DB2 allows only fenced stored procedures.

A fenced stored procedure is essentially a standalone program that is invoked through SQL. There are various restrictions on what this program can do, but overall, the program can pretty much do anything it wants: read files, send and receive MQ messages, and of course execute SQL.

Here is an interaction diagram of the address spaces involved and the flow of information through them.

Stored Procedure Interaction

Notice that the application program interacts with the stored procedure by first going through the DB2 Master address space. It's a bit of a ping-pong game, trying to follow the flow of information. Let's compare this to the stadard way that a local program interacts with DB2.

Local application going to DB2

As you can see, in this case the interactions are much simpler, avoiding the cross address space memory transfers.

	Stored Procedures are a very powerful database feature that can often be employed to great benefit. However, in the context of an application running entirely on the mainframe, using stored procedures can bring you no benefit and sometimes can even be detrimental to your application. Lets analyze why this can be...
	First, let's review some of the benefits of stored procedures. Reducing network traffic and client code complexity in a client/server environment - if the application logic requires processing large result sets, and that logic can be encapsulated in a stored procedure, we can reduce the amount of data going back and forth between the client and the server. Centralizing common business rules for accessing and manipulating the data. Providing a standard API(SQL) and an application server type environment (transactionality, work load managed address spaces) for all clients on and off the host.
	Now lets review some details about how stored procedures are implemented in DB2. In DB2-land, stored procedures can be of two types, fenced and non-fenced. Non-fenced stored procedures run in a DB2 address space. Fenced stored procedures run in separate address spaces from DB2. If a non-fenced stored procedure crashes badly, it can possibly bring down DB2. For this reason, the mainframe version of DB2 allows only fenced stored procedures. A fenced stored procedure is essentially a standalone program that is invoked through SQL. There are various restrictions on what this program can do, but overall, the program can pretty much do anything it wants: read files, send and receive MQ messages, and of course execute SQL. Here is an interaction diagram of the address spaces involved and the flow of information through them.

	Notice that the application program interacts with the stored procedure by first going through the DB2 Master address space. It's a bit of a ping-pong game, trying to follow the flow of information. Let's compare this to the stadard way that a local program interacts with DB2.

	As you can see, in this case the interactions are much simpler, avoiding the cross address space memory transfers.