Road Map to DB2 Programming

Stored Procedures

	Embedded SQL Programming Guide	Explains how to develop applications that access DB2 databases using embedded SQL. Chapter 5 contains details about stored procedures.
	CLI Guide and Reference	Explains how to develop applications that access DB2 databases using the DB2 Call Level Interface. Chapter 3 contains details about stored procedures.
	Building Applications for UNIX Environments	Provides instructions to compile, link, and run stored procedures on a UNIX system.
	Building Applications for Windows and OS/2 Environments	Provides instructions to compile, link, and run stored procedures on a Windows or OS/2 system.

		Embedded SQL	DB2 CLI
	inpcli	X		Demonstrates stored procedures using either the SQLDA structure or host variables. This is the client program of a client/server example. The server program is inpsrv. inpcli fills the SQLDA with information and passes it to the server program for further processing.
	inpsrv	X		Creates a table in the SAMPLE database with the information received in the SQLDA. Returns the SQLCA status to the client program inpcli.
	outcli	X		Demonstrates stored procedures using the SQLDA. This is the client program of a client/server example. The server program is outsrv. outcli allocates and initialized a one variable SQLDA, and passes it to the server program for further processing.
	outsrv	X		Finds the median salary, fills the SQLDA with the median, and returns the SQLDA and SQLCA status to the client program outcli.

		Embedded SQL	DB2 CLI
	inpcli.c		X	Calls embedded SQL stored procedure inpsrv.
	inpcli2.c		X	Calls DB2 CLI stored procedure inpsrv2.c.
	inpsrv2.c		X	Stored procedure (rewrite of embedded SQL sample inpsrv).

Typically, applications access the database across the network. This can result in a lot of data being transmitted across the network, and poor performance.

Figure 13. Application accessing a database across the network

A stored procedure is a part of your application that runs on the database server. Your client application passes control to the stored procedure allowing it to perform intermediate processing on the server without transmitting unnecessary data across the network. Only the records your application needs are transmitted by the stored procedure.

Figure 14. Application using a stored procedure

You gain several benefits using stored procedures:

Reduced network traffic

Grouping SQL statements together can save on network traffic. A typical application requires two trips across the network for each SQL statement. Grouping SQL statements results in two trips across the network for each group of statements, resulting in better performance for applicatons.

Access to features that exist only on the server

Stored procedures can have access to commands that run only on the server, such as LIST DATABASE DIRECTORY and LIST NODE DIRECTORY; they might have the advantages of increased memory and disk space on server machines; and they can access any additional software installed on the server.

Enforcement of business rules

You can use stored procedures to define business rules that are common to several applications. This is another way to define business rules, in addition to using constraints and triggers.

When an application calls the stored procedure, it will process data in a consistent way according to the rules defined in the stored procedure. If you need to change the rules, you only need to make the change once in the stored procedure, not in every application that calls the stored procedure.

[ DB2 List of Books | Search the DB2 Books ]