SQL and External procedures

Procedures are, in general, any program that can be executed using an SQL CALL statement. They are commonly used in client/server applications, especially in the area of online transaction processing (OLTP), since they can provide performance, transaction-integrity and security benefits. In DB2® for i , procedures can be written in SQL procedure language or in a number of external programming languages, such as ILE RPG or ILE COBOL. For information regarding specific SQL statements that are used in the examples of these procedures, see the DB2 for i SQL Reference topic collection in the IBM i Information Center.

The illustration below shows an application where one transaction consists of four separate I/O operations, each that requires an SQL statement to be processed. In the client/server environment, this requires a minimum of eight messages between the server and the client, as shown. This can represent significant overhead, especially where the communication speed is slow (for example over a dial-up line), or where the turnaround speed for the connection is slow (for example over a satellite link).

The following illustration shows the same transaction by a stored procedure on the server. As illustrated, the communications traffic has been reduced to a single message pair. There are additional benefits. For example, the procedure can arrange to send back only the data that is absolutely required (for example, just a few characters from a long column). A DB2 for i stored procedure can be any IBM i program, and does not have to use SQL for data access.