A stored procedure is a compiled program that can execute SQL statements. Stored procedures are stored at a local or remote XDB Server. Local XDB Server applications or remote DRDA applications can use the SQL statement CALL to invoke a stored procedure.
Design the application to use stored procedures if the application program operates in a client/server environment, and if either of the following two problems apply:
- The application accesses host variables for which you want to guarantee security and integrity.
- The application executes a series of SQL statements, creating many network send and receive operations, which significantly increase CPU and elapsed time costs.
Use of stored procedures removes SQL applications from the workstation, which prevents workstation users from manipulating the contents of sensitive SQL statements and host variables. The use of stored procedures also allows you to combine many of your application's SQL statements into a single message to the DB2 subsystem or XDB Server, reducing network traffic to a single send and receive operation for a series of SQL statements.
Note: If you are running your stored procedure on DB2 MVS, through the XDB Link product, then see your IBM DB2 documentation for other steps necessary to run the stored procedure.
To get a stored procedure up and running:
- Code and prepare a stored procedure. See
Writing and Preparing Stored Procedures for instructions.
- Code and prepare an application that calls the stored procedure. An SQL statement, CALL, in that application must use the same parameter list and linkage convention as the stored procedure that it invokes. See
Writing and Preparing Applications to Use Stored Procedures for instructions.
- Define your stored procedure to the XDB Server by issuing a CREATE PROCEDURE command, which will place a row in the appropriate SYSIBM table(s). See
Defining Stored Procedures under SQL Option for additional details.
- Compile and define a LOADLIB entry for your stored procedure. The LOADLIB entry tells the XDB Server where your stored procedure is. See
Compiling Stored Procedures under SQL Option for additional details.
- Access mainframe tables using stored procedures under SQL Option. The steps necessary to do this are in
Accessing Mainframe Tables With Stored Procedures.
- Check the current
Limitations with Stored Procedures under SQL Option if you experience problems.