8/8/2019 STP01

1/2

Stored Procedure Concept

Stored procedures are user-written structured query language (SQL) programs that are stored at thedata base server and can be invoked by client applications. A stored procedure can contain moststatements that an application program usually contains. Stored procedures can execute SQL

statements at the server as well as application logic for a specific function.

A stored procedure can be written in many different languages, such as COBOL, OO COBOL, C, C++,PL/I, FORTRAN, Assembler, and REXX. The language in which stored procedures are writtendepends on the platform where the data base server is installed.

Local client applications, remote Distributed Relational Database Architecture (DRDA), or remote dataservices (private protocol) can invoke the stored procedure by issuing the SQL CALL statement. TheSQL CALL statement is part of the International Organization for Standardization/American NationalStandards Institute (ISO/ANSI) proposal for SQL3, an open solution for invoking stored proceduresamong database management system vendors that support the SQL ISO/ANSI standard.

The client program can pass parameters to the stored procedure and receive parameters from the

stored procedure.

The DRDA architecture allows SQL CALL statements to use static or dynamic SQL. The version of theproducts used during this project only supports the SQL CALL statement as static SQL. Nevertheless,parameters in the CALL statement, including the stored procedure name, can be supplied at executiontime. Thus, you can use the SQL CALL statement to dynamically invoke any procedure supported bythe data base.

The client program and the stored procedure do not have to be written in the same programminglanguage. For example, a C client program can invoke a COBOL stored procedure.

Why Use Stored Procedures?

In previous releases of DRDA, the client system performed all application logic. The server wasresponsible only for SQL processing on behalf of the client. In such an environment, all databaseaccesses must go across the network, resulting in poor performance in some cases. Figure 1 showsan example of the processing for a client/server application without using stored procedures.

Figure 1. Processing without Stored Procedures

This is a relatively simple model, which makes the application program easy to design and implement.Because all application code resides at the client, a single application programmer can takeresponsibility for the entire application. However, there are some disadvantages to using thisapproach.

8/8/2019 STP01

2/2

Because the application logic runs only on the client workstations, additional network input/output (I/O)operations are required for most SQL requests. These additional operations can result in poorperformance. This approach also requires the client program to have detailed knowledge of theserver's database design. Thus, every change in the database design at the server requires acorresponding change in all client programs accessing the database. Also, because the programs run

at the client workstations, it is often complicated to manage and maintain the copies there.

Stored procedures enable you to encapsulate many of your application's SQL statements into aprogram that is stored at the data base server. The client can invoke the stored procedure by usingonly one SQL statement, thus reducing the network traffic to a single send and receive operation for aseries of SQL statements. It is also easier to manage and maintain programs that run at the serverthan it is to manage and maintain many copies at the client machines.

Stored procedures enable you to split the application logic between the client and the server. You canuse this technique to prevent the client application from manipulating the contents of sensitive serverdata. You can also use it to encapsulate business logic into programs at the server. Figure 2 shows anexample of the processing for a client/server application with stored procedures.

Figure 2. Processing with Stored Procedures

The stored procedure can issue static or dynamic SQL statements. Data definition language (DDL),most data manipulation language (DML), and data control language (DCL) statements can be codedin a stored procedure.

Stored procedures also enable access to features that exist only on the database server. Thesefeatures include commands that run only on the server, software installed only on the server that can

be accessed by the stored procedure, and the computing resources of the server, such as memoryand disk space.

Because stored procedures are defined in DRDA, they also take advantage of DRDA features, suchas data transformation between platforms, database security and accounting, and two-phase commitsupport.