Stored procedure

Stored procedures are a way to extend database functionality. Wikipedia calls a stored procedure a subroutine, which is as good a definition as any. Stored procedures (SP from here on) can be used to:

• Encapsulate complex processing
• Update a table. Sure, UPDATE does that, but then the end user (web developer) needs to be familiar with your database.
• Return data to SQL such as a result set. Again, isolate the database from the UI. A result set can consolidate columns from several tables, in essence hiding the JOINS from the ultimate end user of the data.
• Provide a service to SQL like RTVSYSVAL.

IBM have a 2006 Redbook entitled Stored Procedures, Triggers, and User-Defined Functions on DB2 Universal Database for iSeries. It's old but it provides a foundation for understanding stored procedures.

SPs are an inherent part of DB2; they are part of the database. On other platforms, the logic section of SPs is written using SQL. That's a choice on DB2 for i as well, and this is called an SQL (or MAIN) Stored Procedure. We have another choice as well, and that is to write the logic using an HLL like RPG. This is called an External stored procedure. In either case, you register a SP with DB2 via the SQL CREATE PROCEDURE statement. To use a stored procedure in your SQL, use the CALL statement.

While all programs on the IBM i can be called as stored procedures, note that CL commands are not programs. One way around this is to use QSYS.QCMDEXEC() or QSYS.QCMDEXEC2() stored procedures. You can also use the CL: prefix to use CL commands directly in your own SQL stored procedure. For example:

CL: CHGCURLIB CURLIB(MYLIB);

An external SP interfaces with DB2 via the parameter list. There are several parameter styles, depending on whether you will need to handle NULL values or not.

TODO:

• Example tables
• Example SQL SP
  • One return variable
  • Result set
• Example RPGLE SP
  • One return variable
  • Result set
• Multiple parameter list usage
  • GENERAL
  • WITH NULLS
• Canonical documentation for parameter lists
• Consume a result set. Java?