Reducing the amount of overhead on the network.
We also considered the case of relocating a series of SQL statements to decrease network message flows (see Case 1 in Part 1). Now let's consider another case.
Case 2: Implementing unsupported functions on the server decreases data flows
Because of SQL's language limitations, a client application must retrieve the entire result table of a remote SELECT statement request to determine the actual data required. This is inefficient because all the data must travel across the network even if only a single result row satisfies the client application's conditions.
For example, suppose a client application allows the user to search for neighborhoods whose median price range for a house is greater than a user-specified value. The client program transmits the following Select statement to the server:
Select * From ucg/homesales Order By nbrhood
The server must then transmit each record from the homesales file to the client application which, in turn, must calculate the median value for each neighborhood (nbrhood field) and compare it against the user-specified value.
A much more efficient way of handling this is to embed the above Select statement in a program (i.e. stored procedure) on the server which will accept the user-specified minimum median value as a parameter. The program will then read each record resulting from the Select statement as the client program previously did. The client program code that calculates the medians and compares them to the user-specified value is likewise relocated to the server program. The server program writes a record to a temporary file for each neighborhood meeting the median price criteria. Finally, the server program returns the name of the temporary file to the client program.
To run the stored procedure, the client program can execute the following SQL statement:
Call ServerPgm (:minval, :tmpfil)
where ServerPgm is the name of the program on the server, minval is a host variable containing the user-specified minimum median value (and will be passed as parameter 1), and tmpfil is a host variable that will be passed as parameter 2 and return with the name of the temporary file created by the stored procedure.
After the Call statement, the client program can build and execute a Select statement over the temporary file. Each record retrieved from this file will meet the user's criteria for median home price.
Note that it is also possible to combine these two steps into one large (and rather complicated) compound SQL statement on the server.
New feature makes stored procedures even more convenient
As of V5R3, it is possible to have dynamic result sets returned by stored procedures as open cursors. The calling program can then issue fetches (and other functions) against the open cursor. Here is an example:
CREATE PROCEDURE ront/custmast2(IN inlastname CHAR(15)) LANGUAGE SQL
DYNAMIC RESULT SETS 1
BEGIN
DECLARE C1 CURSOR FOR SELECT * FROM ront/custmast
WHERE upper(cstlnm) like (upper(TRIM(inlastname)) || '%');
OPEN C1;
RETURN;
END
The Declare Procedure statement
The Declare Procedure SQL statement is used when it is necessary to override the basic assumptions the Call statement makes about the stored procedure's name, the number and type of parameters, and whether or not the parameters are input, output, or both. Simple procedures, especially ones that do not have parameters, generally will not require the use of a Declare Procedure statement. However, using it will increase the readability of your code.
The Declare Procedure statement is used to explicitly specify the program name (as it is known on the server), the name you will use on the CALL statement (if different), and the attributes of each parameter. You also specify the language in which the program was written.
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.
