Chapter13.Stored Procedures

Chapter 13. Stored Procedures

• Benefits of using stored procedures

• Writing stored procedures

• Defining stored procedures

• Execution environments

• SQL Procedure Language

• Developing SQL stored procedures

• DB2 Development Center

A stored procedure is a program that is stored and executed within the control of the DBMS. Some DBMS products limit stored procedures to a subset of SQL functionality. DB2 implements stored procedures as externally compiled programs that run within specialized DB2 address spaces.

All DB2 stored procedures are compiled programs stored in a load library available at the DB2 server. The DB2 server can be either the local DB2 subsystem or a remote DB2 subsystem. These programs can execute SQL statements, connect to other DB2 servers, and retrieve data from nondatabase sources. A stored procedure typically contains two or more SQL statements and some manipulative or logical processing in a host language. A client application program uses the SQL CALL statement to invoke a stored procedure:

 EXEC SQL CALL MYSP (:parm1, :parm2, :parm3) END-EXEC.