Stored Procedures

Stored procedures are another server-side feature of DB2 that you should consider using to consolidate database programming code and increase performance. They allow you to download your database operations into libraries that are called locally by a DB2 server instance (see Figure 8.4). Thus, there are two pieces to application code that uses a stored procedure. You must write the stored procedure itself in a supported language (C or Java) or as an SQL Stored Procedure. In addition, you must write client-side code that will call the stored procedure. Writing a database application that issues a CALL statement to run the stored procedure performs this task. There are several features in DB2 stored procedures that you can benefit from.

• Network transmissions are not required to execute the SQL in a stored procedure because the stored procedure runs locally on the DB2 server.

• Database processing will occur on the system that houses your DB2 server and alleviates processing load from client systems.

• Zero or more input and output parameters can be passed into and returned by the stored procedure.

• Zero or more result sets can be returned by a stored procedure to the calling application.

• A calling application from any platform, written in any supported language or interface supported by DB2, can call the stored procedure.

• Database-specific code maintenance is simplified by consolidating it on the DB2 server.

• External stored procedures written in languages such as Java, C, or COBOL can perform other processing (such as reading a file) that you would perform in non-database application code.

These are only some of the benefits that you can gain by using stored procedures. DB2 allows you to issue a COMMIT or ROLLBACK from within a stored procedure, which was not previously possible. In addition, as with earlier versions, each DB2 instance has a stored procedure cache. Updating the database manager configuration with the setting KEEPDARI ON enables the cache.

As a rule, you should keep this setting off in your development environment when you are making a lot of changes to your code. After your procedure is stable and you are ready to benchmark it, you should enable KEEPDARI. The reason for this is that once a stored procedure is cached, changes to the stored procedure will not be reflected unless you restart your DB2 instance.

Stored procedures also have certain limitations that you should be aware of.

• Stored procedures cannot create any database connections; they use the connection of the calling program.

• Embedded SQL calling programs cannot retrieve result sets from stored procedures.

• A DUOW cannot be committed or rolled back from within a stored procedure.

• Stored procedures cannot call other stored procedures, except in the case of SQL Stored Procedures, which can be nested up to 16 levels.

Just as with UDFs, stored procedures can be FENCED or NOT FENCED. A fenced stored procedure runs within a separate db2dari process. In the case of stored procedures, this is a db2dari process, whereas unfenced procedures run within a DB2 agent memory space. Also like fenced UDFs, stored procedures are normally placed in the sqllib/function directory if they are fenced, and sqllib/function/unfenced if they are unfenced.