Procedures and functions, whether written in SQL or in traditional application development languages, provide powerful tools for accessing your database.

One of the most difficult tasks when you're designing an application is deciding how to code each portion of the application. We can develop applications in CL, RPGLE, SQL, and a host of other languages. The capabilities of these languages overlap one another to a certain degree, so the decision of which language to use is not always clear-cut.

In development on the IBM i, SQL is one of the new kids on the block. As a collective whole, we developers aren't as comfortable with it and its role as we are with more traditional languages, such as CL and RPGLE. As developers become comfortable with the basics of using SQL within their applications, a natural extension of that is to begin to use stored procedures and functions. These programming tools provide interesting options for developers that allow us to more easily accomplish some of the tasks we have within our applications.

This article focuses on how to use functions and procedures and explains some design considerations, such as static versus dynamic SQL statements, determinism, SQL versus RPGLE coding, as well as testing and performance measuring.

When to Use a Stored Procedure

Stored procedures are simply programs. They can be written in SQL or other high-level languages such as RPGLE. These procedures provide the means to write code one time and then reuse it again later. The same types of activities that warranted being coded into their own programs or modules in traditional applications may also be viable candidates for stored procedures.

For example, you might choose to create a stored procedure that is responsible for adding a PO Header record to the database. The stored procedure could receive all the necessary data as parameters, edit them, update the table, and perform additional tasks such as logging data to a history file. This gives you a single point of control for all the work related to adding a record to that table. You might also choose to create a stored procedure to retrieve data, such as all of the open POs in the PO Header table. Any filters, security tasks, or reformatting options required can be carried out by the stored procedure.

Another reason for using stored procedures is that they can improve performance and provide additional security options, such as adopted authority. Since stored procedures are compiled, some of the work of analyzing their SQL statements is completed at compile time, allowing that step to be skipped at run time, which can lead to improved performance. High-level language programs can be compiled with adopted authority (set the compile option to USER(*OWNER)). This allows the stored procedure to run using the permissions of the programmer who compiled it rather than the user who is executing it. Using this feature, developers may provide user IDs with very limited rights to programmers who need remote connectivity. Rather than accessing the data directly, they use these limited profiles to access stored procedures, which adopt a more trusted profile and perform the necessary work.

One of the issues we often struggle with when using a stored procedure is how the SQL engine locates the stored procedure. First of all, the logic that is used for this depends upon the naming convention in use for this connection to the database. If you are using the traditional system naming convention of library/file, then the SQL engine will use the current user's library list to find stored procedures. If you are using an SQL naming convention of library.file, the library list is the system libraries and a library is named the same as the current user.

Stored Procedure Language

Which language is the right language to code in? That depends on a number of factors. First, is the task being performed something you already have code for? If you already have RPGLE code that accomplishes the necessary task, then it's relatively easy to register that program or a modified copy of that program as a stored procedure. This is great for leveraging existing code and reducing your development costs.

If you don't already have the code written, you should consider the task to be performed. If the task is to generate a report, obviously RPGLE is a better choice than SQL. However, if the task is to either retrieve or write data to a database, perhaps SQL is the better choice. For some tasks, you might even find that CLLE is the best language.

Static vs. Dynamic

Whether you are coding in SQL or RPGLE, you have to decide if your SQL statements should be static or dynamic. Static or "hard-coded" statements may outperform dynamic statements, which require additional analysis at run time. But dynamic statements offer more flexibility.

The following example shows a static SQL statement in a stored procedure. This procedure receives a five-digit decimal customer number and then returns all the matching records from the database.

create procedure GETCUST 1  

(in Custid dec(5 0))     

language sql     

result sets 1                          

begin                                            

RETURN SELECT * FROM CUST WHERE CNUMBER = CustID

End

Figure 1: Static SQL Statement

Figure 2 shows an example of a dynamic SQL statement in a stored procedure. This procedure receives in a customer number as a character string (even though customer number is numeric, passing it a character value makes the concatenation simpler). The procedure is written in SQL and returns one result set.

create procedure your-lib/getcust1                       

(in cust char(10))                                      

 language sql                                          

 result sets 1                                 

 begin                                                 

 declare stmt char(50);                                

 declare c1 cursor for s1;                             

 set stmt = 'SELECT * FROM CUST where cnumber = '  concat cust;

 prepare s1 from stmt;                                 

 open c1;                                               

 return;                                               

 end

Figure 2: Dynamic SQL Statement

Result Sets

As seen in the previous examples, stored procedures can return one or more result sets to their caller. But RPGLE programs cannot receive result sets. Therefore, RPGLE programs cannot call stored procedures that return result sets. Even though RPGLE programs cannot receive result sets, that does not prevent them from returning result sets if they are defined as stored procedures.

Figure 3 shows the code to use when returning a multiple-occurrence data structure as a result set. In this case, the variable X contains a count of how many rows in the APTMNTS data structure to return.

C/EXEC SQL                                                     

C+         SET RESULT SETS ARRAY :Aptmnts  FOR :X Rows        

C/END-EXEC                                                    

Figure 3: RPGLE Embedded SQL to Return a Multiple-Occurrence Data Structure

That works well for traditional legacy programs that are already building a set of data for a report or a subfile program that we now want to return as a result set. But if we are working with a request for new data, we might prefer to follow the example shown in Figure 4 and open a cursor in the RPGLE program and return that result set to the call.

C/EXEC SQL                                     

C+    DECLARE C1 CURSOR FOR SELECT * FROM CUST     

C/END-EXEC                                     

C/EXEC SQL                                     

C+    OPEN C1                                  

C/END-EXEC                                     

C/EXEC SQL                                      

C+         SET RESULT SETS CURSOR C1           

C/END-EXEC

Figure 4: RPGLE Embedded SQL to Return a Result Set

Stored procedures do not need to return result sets. They can simply pass parameters back and forth if needed or, even more simply, just perform a specific task, like initializing all the tables in a database or deleting and recreating all the indexes for a database.

When to Use a Function

Functions are similar to procedures in that they are code routines (written in either SQL or some other high-level language) that perform a specific task. They differ from procedures in that they are called from within an SQL statement, usually taking in one or more arguments and returning a related value.

In general, functions will tend to perform a smaller, more focused task than a procedure. For example, you might use a function to convert a date stored in a legacy format into a real date data type. You might also use a function to perform some sort of translation, giving a value as an argument and receiving back the related value, such as an item price lookup. Figure 5 shows a function that receives an order number as an argument and returns the one-byte character value of its status.

CREATE FUNCTION ORDSTAT(OrdrIn Numeric(6 0))

RETURNS CHAR(1)

LANGUAGE SQL

NOT DETERMINISTIC

BEGIN

Declare FLAG Char(1);

Set FLAG = '*';

Select STATUS  INTO FLAG from ORDMAST WHERE ORDER = OrdIn;

RETURN FLAG;

END

Figure 5: SQL Function to Return a Status Flag

Anytime you have data to convert from the format it's stored in within the database into a more usable format, coding your own function is an option. If the available SQL functions cannot easily perform the conversion, then coding your own is a reasonable solution.

If you already have code written in a language such as RPGLE that performs the necessary work, then registering a subprocedure as a function may be the simplest way to deploy that solution. Otherwise, you might choose to create a new function in SQL.

Deterministic Functions

If you define a function as deterministic, you are authorizing the SQL engine to use cached results of previous calls to the function rather than call it each time. For example, the SQL function UPPER('a') will always translate the lowercase a into an uppercase A. That is deterministic. If that function is used in a result set that returns 1000 rows, all with a lowercase a, then the SQL can call the function one time, and the other 999, it can pull the result from its cache, improving performance.

If you are sure your function is deterministic, then code it that way to gain performance whenever possible. If you are not sure, assume it's not deterministic, as that will prevent the SQL engine from returning incorrect data.

Overloading Functions

Functions may be overloaded, meaning that you can create many functions, all with the same name, as long as each function has a different set of parameters. At run time, the SQL engine will determine which of the functions to invoke, based upon the parameters passed.

Figure 6 shows CREATE statements for two functions that share the same name. The specific name parameter may be used to identify the specific version of the function.

CREATE FUNCTION ORDSTAT(OrdrIn Decimal(6 0))

RETURNS CHAR(1)

LANGUAGE SQL

NOT DETERMINISTIC

BEGIN

Declare FLAG Char(1);

Set FLAG = '*';

Select STATUS  INTO FLAG from ORDMAST WHERE ORDER = OrdIn;

RETURN FLAG;

END

CREATE FUNCTION ORDSTAT(OrdrIn Numeric(6 0))

RETURNS CHAR(1)

LANGUAGE SQL

NOT DETERMINISTIC

BEGIN

Declare FLAG Char(1);

Set FLAG = '*';

Select STATUS  INTO FLAG from ORDMAST WHERE ORDER = OrdIn;

RETURN FLAG;

END

 Figure 6: Overloaded Function

This very useful option allows us to configure a function to receive decimal, numeric, integer, or other numeric data types without forcing the users to know which version of the function to call.

Scratchpad

Typically, functions process only the data passed to them as arguments and do not store any information from previous executions. However, if you need a function to retain data between each row it's called for, you can use a SCRATCHPAD option in the CREATE command. Coding a scratchpad function is too complex to cover in detail here.

Testing

Testing stored procedure calls from the STRSQL command is not feasible as it cannot handle parameters and result sets. So the simplest way to test a stored procedure is to use the RUN SQL SCRIPTS tool, which is distributed for free within iSeries Navigator.

With this tool, you can test the functioning of the stored procedure by calling it with various parameters and evaluating its results. You also have the option to use the visual explain tool to analyze the performance of an SQL command and measure its performance.

The database feature of iSeries Navigator also contains an SQL monitor tool, which allows administrators to watch the execution of SQL statements for virtually any job on the system, including the execution of programs that you do not have source code for.

Feel the Power

Procedures and functions, whether they are written in SQL or use traditional application development languages such as RPGLE, provide powerful tools for accessing your database. One of the greatest benefits of these SQL based tools is that they can be used internally within your own applications, yet also provide a framework for integrating with external tools and applications through common interfaces such as ODBC and JDBC.