When IBM announced V3R1 in May 1994, the database enhancements were touted as a major reason AS/400 shops should move quickly to the new release. Now that V3R1 is a reality, you'll want to take a closer look at the new functions. This article examines one of the new functions in detail?stored procedures.

Stored procedures make it possible to use a single SQL statement to execute an entire program?the SQL equivalent of an RPG CALL. There's been a lot of confusion about stored procedures, probably because RPG programmers take the convenience of CALLs for granted. It comes as a shock to realize that, until V3R1, OS/400 did not support SQL CALLs.

Stored procedures are most often discussed in the context of a client/server or Distributed Relational Database Architecture (DRDA)application. The importance of V3R1 support for stored procedures is that it delivers performance, security, and modularity benefits to every application accessing OS/400 using SQL.

Stored procedures in SQL serve the same purpose as procedures in other languages. A common piece of code need only be written and maintained once?the premise of modular programming. Host languages and SQL can call procedures on the local system. SQL, however, can also call procedures on remote systems. This fact creates one of the benefits of stored procedure calls in SQL?improved performance of distributed applications. For example, an SQL client application may need to perform several database operations on a remote server. You can execute each database operation separately from the client application, or you can call a stored procedure that executes on the remote server. The call to the stored procedure is the better approach for three reasons: it requires less traffic across the communication line, it allows some of the logic of the application to execute on the server, and it allows better control over server function security.

In a client/server environment, stored procedures allow you to place entire functions on the server; these functions can also provide services not normally available through SQL. Their main value is in distributing application logic and processing.

Stored procedures also provide improved security. For example, in a client/server application using ODBC with no stored procedure calls, you would need to connect to the remote database with update access to allow the client application to update the remote database. This can give the entire client application update access to the database?an obvious security concern.

By placing the update function in a stored procedure that adopts the authority of the owner, the user only needs read authority to the AS/400 database. When an update is required, the client application calls the stored procedure to perform the update; the client application has no ability to update on its own.

AS/400 Implementation

With V3R1, SQL/400 supports stored procedures. Perhaps more importantly, the underlying SQL engine in OS/400 supports stored procedures. This support allows any product that uses standard SQL CALL syntax to run a program on the AS/400. Prior to V3R1, SQL calls that targeted the AS/400 were implemented by each vendor. For example, a PC-based SQL product would issue an SQL CALL that would be interpreted as a CL CALL command. Obviously, this defeats much of the reason for using a standard language such as SQL.

A stored procedure can be written in any high-level language (HLL) except S/36 languages (e.g., OCL). A stored procedure can call another stored procedure if the invoking stored procedure is local. A remote stored procedure can call local stored procedures, but it cannot call remote stored procedures. Stored procedures that contain only SQL statements are referred to as SQL procedures. One of the strengths of the AS/400 implementation is that a stored procedure can be a host language program that may or may not contain embedded SQL statements. These are referred to as external procedures.

Cui Bono?

Now that you know what a stored procedure is, let's examine what this new function means in terms of application design. Stored procedures are particularly important in distributed applications because they can be used to reduce the traffic across communications lines. As AS/400s move toward client/server and other distributed implementations, the communications line traffic becomes a critical performance component. Although stored procedures can be implemented locally, they are much more important in a distributed environment.

1 and 2 illustrate the difference between a remote SQL implementation that does not use stored procedures and one that does. In 2, the SQL CALL causes an entire program (PGMB) to execute on the remote system. PGMB contains all the logic that was previously sent across the communications line one statement at a time (as illustrated in 1). A stored procedure can update multiple files, and it can even call other programs.

Figures 1 and 2 illustrate the difference between a remote SQL implementation that does not use stored procedures and one that does. In Figure 2, the SQL CALL causes an entire program (PGMB) to execute on the remote system. PGMB contains all the logic that was previously sent across the communications line one statement at a time (as illustrated in Figure 1). A stored procedure can update multiple files, and it can even call other programs.

The implementation of stored procedures depends on the SQL implementation you are using. Essentially, there are two components. An optional DECLARE PROCEDURE statement defines the called program and the parameters that will be passed to it. DECLARE PROCEDURE applies to static embedded SQL statements and ODBC extended dynamic SQL. It does not apply to any dynamically executed SQL statements. The SQL CALL statement initiates the stored procedure and passes the parameter data to the called program. The calling program is suspended while the called program executes just as it is in the RPG implementation.

Declaring procedures gives you increased control and flexibility when you use stored procedures. For example, the DECLAREPROCEDURE statement can define the length and type of literal parameters and specify the library name for the called program.

Another big advantage of stored procedures is that they can return parameters to the caller. Again, the implementation is similar to the RPG CALL implementation that you are familiar with. You can improve performance even further by reviewing the parameters passed back and forth to the stored procedure. For example, don't define parameters as input/output (INOUT) unless you really need to send data in both directions. In DRDA applications, the overhead on the communications line to send long parameters back and forth may have a noticeable effect on remote stored procedure performance.

If you are designing client/server applications that utilize SQL, stored procedures could make the difference between acceptable performance and disgruntled users. Despite all the mystique, they are extremely simple to use. In the following section, we've provided a simple AS/400-to-AS/400 example to illustrate how stored procedures are implemented using RPG and embedded SQL.

Easy as...

For a simple example, we've created an SQL application that runs a payroll application. 3 illustrates how an SQL application might be coded. As you can see, the application connects to the remote database, runs an UPDATE statement to calculate and update the current pay, runs an INSERT statement to update the payroll history file (PAYHST), drops the CURPAY table, and then creates a new one for the next pay period. Although this application gets the job done, numerous requests and replies are sent to and received from the system where the data resides (NEWYORK).

For a simple example, we've created an SQL application that runs a payroll application. Figure 3 illustrates how an SQL application might be coded. As you can see, the application connects to the remote database, runs an UPDATE statement to calculate and update the current pay, runs an INSERT statement to update the payroll history file (PAYHST), drops the CURPAY table, and then creates a new one for the next pay period. Although this application gets the job done, numerous requests and replies are sent to and received from the system where the data resides (NEWYORK).

A more efficient method would be to call a stored procedure, as illustrated in 4. With this modified version of the SQL application, an SQL CALL statement is used to call a stored procedure (RPG program CLCPAY). The RPG program performs all the work that needs to be performed on the remote data.

A more efficient method would be to call a stored procedure, as illustrated in Figure 4. With this modified version of the SQL application, an SQL CALL statement is used to call a stored procedure (RPG program CLCPAY). The RPG program performs all the work that needs to be performed on the remote data.

Using the stored procedure causes the work to be done faster since less interaction occurs between the two systems. The stored procedure can also be secured on the server, eliminating the need to give client applications more than minimal database access. Here, for simplicity, we chose to not use the DECLARE PROCEDURE statement to define the stored procedure and its parameter attributes.

5 illustrates partial code for the RPG program used to run the payroll application. As you can see, no embedded SQL statements were included in this program. The important thing to see here is that a program on the remote system carries out all the work with minimal interaction with the client application.

Figure 5 illustrates partial code for the RPG program used to run the payroll application. As you can see, no embedded SQL statements were included in this program. The important thing to see here is that a program on the remote system carries out all the work with minimal interaction with the client application.

This example was tested using two AS/400s, but the principles apply to any distributed application. The most likely scenario for taking advantage of V3R1 stored procedure support is a client/server application. For example, a Power Builder program that accesses the AS/400 to retrieve information using the Client Access ODBC driver.

Seeing Is Believing

The biggest advantage in using stored procedures is with distributed applications, where delegating and isolating application functions to the approprate system can reduce the amount of interaction between the two systems. One SQL CALL to a stored procedure from a client system can perform any amount of work on the server system. Stored procedures allow you to place the application function where it will have least impact on network performance.

IBM is continually enhancing the database and client/server functionality of OS/400. In the process of reviewing this article, we talked with John Broich at IBM in Rochester, Minnesota. John shared information about several PTFs that deliver additional stored procedure support including three new SQL statements: CREATE PROCEDURE, DROP PROCEDURE, and SET RESULT SETS. The PTFs should be available by the time you read this. You should request PTFs for stored procedure Result Sets from IBM service. John has agreed to work with us on an upcoming article that will illustrate the real power of stored procedures in an AS/400 client/server environment.

Richard Shaler is a senior technical editor and Sharon Hoffman is the editor for Midrange Computing.

REFERENCES

Database 2/400 Advanced Database Functions (GG224-4249, CD-ROM GG244249).

DB2/400 SQL Programming V3R1 (SC41-3611, CD-ROM QBKAQ800).

DB2/400 SQL Reference V3R1 (SC41-3612, CD-ROM QBKAQ900).

DB2/400 Stored Procedure Implementation

Figure 1: Remote Processing without Stored Procedures

DB2/400 Stored Procedure Implementation

Figure 2: Remote Processing with Stored Procedures

DB2/400 Stored Procedure Implementation

Figure 3: Run Payroll Application on Remote System with SQL Statements

 connect to NEWYORK user SMITH using '' UPDATE curpay set CURPAY = (CURHRS * EMRATE) INSERT into PAYHST SELECT * from CURPAY DROP table CURPAY CREATE table CURPAY 
 
DB2/400 Stored Procedure Implementation
 
Figure 4: Run Payroll Application on Remote System with Stored Procedure
 
 connect to NEWYORK user SMITH using '' CALL PAYROLL/CLCPAY 
 
DB2/400 Stored Procedure Implementation
 
Figure 5: Stored Procedure CLCPAY
 
 *. 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 ...+... 7 FCURPAY IF E DISK FPAYHST O E DISK A * C *IN99 DOWEQ*OFF C READ CURPAY 99 C *IN99 IFEQ *OFF C CURHRS MULT EMRATE CURPAY C UPDATCPREC * Add to PAYHST file C WRITEPAYHST C ENDIF C ENDDO * C EXSR CLRCP * C MOVE *ON *INLR * Clear CURPAY file C CLRCP BEGSR * . * . * . C ENDSR *. 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 ...+... 7