SQL (Structured Query Language) stored procedures are a great feature. They provide an excellent tool to help optimize client/server applications. By shifting key portions of the processing onto the server, network data flows can be reduced substantially. All this without bogging down the server, which is the reason you went to client/server in the first place.
Procedures are called from the client (not necessarily a PC) using the SQL CALL statement.
Because SQL is used to implement stored procedures, the reader should have a basic understanding of it. If the server is an iSeries, it is not required to have the DB2 Query Manager and SQL Developer Kit licensed program – but it does help.
The client may or may not be required to have some kind of SQL product depending on the client. Typically, a PC client will use an ODBC driver or the Client Access SQL APIs to establish the SQL link to the server's database.
Stored procedures come in two flavors
There are two types of stored procedures, external stored procedures and SQL stored procedures. External stored procedures are created using any of the standard iSeries programming languages (e.g., C, COBOL, RPG, etc.). You simply create a program that does what you need. It does not have to contain embedded SQL statements, but it certainly can. Once the program is created, you make a stored procedure from it using the Create Procedure SQL statement.
SQL stored procedures are created using just SQL. Again, you use the Create Procedure SQL statement, but instead of referring to an external program as you would with an external stored procedure, you enter the SQL statement(s) that constitute the procedure as part of the Create Procedure SQL statement. (SQL stored procedures are created using only SQL, so you obviously have to use SQL.)
Why use stored procedures
Stored procedures are used primarily to increase efficiency in two ways: One, eliminating the downloading of temporary data and tables; and two, reducing the amount of overhead on the network. Consider the following case.
Case 1: Relocating a series of SQL statements to decrease network message flows. A client sends a fixed series of SQL statements to the server, repeatedly. Every instruction must be transmitted, received, executed, and returned independently. For example, as part of its initialization, a client application creates a group of temporary files on the server. A series of Create Table statements is executed from the client application each time the program is called. Each statement is transmitted independently and, thus, produces inefficiencies.
This situation is easily improved by simply removing the series of Create Table statements from the client application and embedding them in a program on the server. The client then uses the SQL Call statement like the following to execute the stored procedure program.
Call SQLInit ()
-- where SQLInit is the name of the program on the server. The empty set of parentheses indicate that there are no parameters.
In the next installment, we'll consider a different situation where stored SQL procedures can help. We'll also explore some more examples and some other aspects of using stored procedures.
-----------------------------------
About the author: Ron Turull is editor of Inside Version 5. He has more than 20 years' experience programming for and managing AS/400-iSeries systems.
