Why do SQL Procedures have a Program Type Setting?

 If your application uses a lot of SQL stored procedures, then you may have noticed there’s a PROGRAM TYPE setting. A stored procedure is really SQL’s version of a program object. There’s nothing in the SQL standards that connects a stored procedure to a program object.  However, many relational databases, Db2 for i included, utilize a program object in their stored procedure implementation to improve performance.

When you execute the CREATE PROCEDURE statement on Db2 for i, you may or may not know that the Db2 engine generates a C program object to implement the procedural logic  that was coded with SQL.  This enables Db2 for i to interpret the SQL-based logic a single time and store the implementation in a program object. The alternative is dynamically interpreting the SQL logic every time that the SQL stored procedure is invoked – it doesn’t take a rocket scientist to understand that performance would be unacceptable if the SQL procedure had to be interpreted on every execution.

 

The fact there’s a program object associated with your SQL stored procedure is hidden from the application. The application just needs to know the name of the SQL procedure to invoke and Db2 is responsible for finding and using the corresponding C program object. 

 

Now that you understand the association between an SQL procedure and IBM i program, the PROGRAM TYPE setting for an SQL stored procedure should make a little more sense. Many moons ago when the SQL procedure  support was first delivered with the V4R2 release in 1998, there was only a single PROGRAM TYPE setting MAIN.  Not sure where the values of the PROGRAM TYPE setting came from, but regardless of the origin MAIN meant that a C program was generated every time that an SQL stored procedure was created. 

 

When I was working on the IBM PartnerWorld team (now known as IBM Parter Plus) in the late 1990’s & early 2000’s, one of my responsibilities was helping software vendors port their applications based on other databases to the IBM i (then known as the iSeries). The Db2 for i SQL stored procedures made it much easier for vendors to port their applications.

 

A couple of years after the SQL stored procedure support was delivered, our porting team helped an Oracle-based vendor port their application to run natively on the IBM i. This software vendor’s application relied almost exclusively on stored procedures – if I recall correctly that application used over 8,000 different stored procedures. Every business function supported by the applications resulted in a significant number of stored procedure calls. As this porting project moved into the performance testing phase, the cost of the high number of stored procedure invocations was consuming a nontrivial amount of  the overall application runtime.

 

That’s when the second PROGRAM TYPE value of SUB entered the picture to give Db2 for i the option of generating a C service program object. With a service program object, IBM i only performs the activation processing once within a job (or database connection). In contrast, activation processing is performed every time that a program object is called within a job. While the performance difference is relatively small – my testing showed about almost a 10% performance improvement on a stored procedure called repeatedly within a job - the multiplicative impact it had on the vendor’s stored procedure heavy solution was significant. 

 

Another bonus is the simplicity of the coding change as shown in the following example. All that you need to do is add the PROGRAM TYPE clause to your CREATE PROCEDURE statement.

CREATE PROCEDURE simpleproc (IN i1 INT, OUT o1 INT)
  LANGUAGE SQL
  PROGRAM TYPE SUB 
BEGIN
  SET o1 = i1;
END

Your application doesn’t need hundreds or thousands of stored procedures to benefit from the performance benefits provided by the PROGRAM TYPE SUB option. If you have a single SQL stored procedure that’s called multiple times within a job or connections which is often the case with connection pools, then your application performance will increase from the multiplicative effect as well.