DB2 Basics: Stored Procedures, Functions, Modules, Oh My!

DB2 has a number of objects where that use procedural SQL. IBM calls this SQL PL, not to be confused with PL/SQL or TSQL, both similar concepts. Much PL/SQL also works in DB2, if you have the Oracle compatibility vector enabled. The various types of objects have different places and purposes, and it can be useful to understand the use of each.

Functions

Functions are essentially pieces of SQL code that can be easily called to apply to data in a query or other location. They are categorized in terms of whether they are system defined or user defined (UDF), and also catgorized by whether they return a scalar value, a row, or a table. If you want to understand more about user defined functions, I suggest Dan Lukestitich’s excellent article.

Functions take zero or more input parameters and return output as specified by their type (scalar, row, table). Functions do not create, alter, or drop database objects. Nor do they change data in the database – they act only in the context of the data provided and with the ability to read database data.

Functions have the main advantage of storing the logic to perform certain types of calculations or parsing data in a repetitive way in a central place where that logic can easily be reused by many applications.

Functions are used within SQL. The following are examples of function use:
Use of a system scalar function:

select upper(last_name) 
from db2.user
where user_id=1302;

Use of a system table function:

select  workload_name
    , case 
        when rows_returned > 0 
            then decimal(float(rows_read)/float(rows_returned),10,5) 
        else -1 
    end as read_eff
from table(mon_get_workload('',-2))
with ur;

Nearly every SQL I write uses a function in some way, whether is a scalar or a table function. I tend to be fond of SUBSTR.

Stored Procedures

Stored procedures can do what functions can do and much more. Stored procedures allow procedural logic to be stored in the database. Stored procedures may have several advantages over performing the same work in an application:

1. The logic is stored in a central location that must only be updated once if the logic needs to change.
2. Permissions can be granted on the stored procedure, and the executing user in many cases does not also need permissions on the objects being manipulated. Stored procedures can be an excellent way of limiting table-level permissions even further.
3. Stored procedures usually mean the DBA is aware of the SQL being used and may have more involvement in when it is changed. I have one client who has a policy that most new repetitive SQL executed against their OLTP database must be in a stored procedure, and this contributes to the stability of their environment.
4. The procedure is executed on the database server, which may have more resources to apply to the logic than the application or client computers. Processing at the database server may significantly limit the number of trips over the network and the amount of data transferred, contributing to lower overall execution time.
5. The SQL is often pre-compiled, reducing compile time.

One of the disadvantages of stored procedures is that they are often not portable from one RDBMS to another (MS SQL Server to DB2 for example).

Stored procedures are called using this type of syntax:

db2 "call get_dbsize_info(?,?,?,-1)"

Stored procedures can return result sets in a number of formats, and can also raise errors that are user defined.

The variables in both stored procedures and functions are strictly typed, meaning that the variable must be declared with its data type and size/precision/scale prior to being used.

Writing stored procedures is a book in itself and beyond the scope of this introductory article.

Triggers

Triggers are procedural code that is called by DB2 only when some other action occurs. They are run before, after, or instead of a update, insert, or delete on a table. Triggers should be kept as simple as possible as they can bog down performance if they are overly big or overly used. When triggers are created, the triggering action is defined – you cannot explicitly call them like the last two examples.

Modules

Modules are relatively new to DB2, having been introduced in 9.7. A Module is a grouping of other database objects, including functions, stored procedures, types, and variables. The system one that I’ve seen the most is the MONREPORT module that has useful stored procedures such as DBSUMMARY for monitoring database performance. Modules make it easier to deploy interdependent routines and functions and for stored procedures and other objects to be used in different schemas. However, permissions can only be granted on the module as a whole, and not on individual objects in the module. This can be an advantage or a disadvantage, depending on what you’re trying to accomplish.