IBM i modernization – Extended database : “SQL Procedures, Triggers, User defined functions, Constraints”

Modernización de IBM i – Base de datos ampliada: “SQL Procedures, Triggers, User defined functions, Constraints”

Modernização “IBM i” Base de dados estendida: “SQL Procedures, Triggers, User defined functions, Constraints”



In the previous posts I discussed about the need to move to DDL and SQL technology from DDS:

and how to do it, by re-engineering the database:

In this study I want to elaborate on the possibilities that SQL and the data-centric programming offer to modernize your application.

For more detail about this analysis please consult the following RedBooks available on the site:

“SQL Procedures, Triggers, and Functions on IBM DB2 for i”

“External Procedures, Triggers, and User-Defined Functions on IBM DB2 for i”




The PSM Persistent Stored Module

As I discussed previously, Data centric programming is the model and the methodology for design and develop logic and processes that reside near the database: this let become your application more efficient and faster within the database server then the data processing in application server or web server. SQL is the standard language of the database, so it is used to communicate with the database management system and facilitate operations on data. An extension of SQL, initially published in 1996, is the SQL/PSM (Persistent Stored Modules): SQL/PSM standardizes syntax and semantics for control flow, exception handling, variables, assignment of expressions to variables and use of cursors for stored procedures. SQL/PSM is one language in which methods can be defined.

SQL/PSM offers the advantage to be a common language, portable, powerful and with continuous enhancements. How SQL/PSM objects are invoked depends on how they are used. Mainly SQL/PSM could be used in the following ways:




I will analyze all these types after a few general considerations about SQL/PSM language.

SQL script could be run by using RUNSQL or RUNSQLSTM into CL or using IBM i SQL interfaces IBM i Access Client Solutions and Navigator for i. As alternative you could use the product 5770-ST1 for run STRSQL and STRQMQRY or buy one of the third-part solutions.

Procedures can be written in two different ways on DB2 for I: the first approach is to describe them as SQL procedures. This is based on the extensions to the SQL language. The other approach is based on high-level languages (HLLs) such us C, CL, RPG, COBOL and Java too. External procedures are coded in one of the high-level languages that are available on the IBM I server: if you want to reuse existing code or if you must write sophisticated processes, external procedures are the best solution.


The structure of an SQL PSM program

An SQL/PSM program consists in the following parts:

  • the CREATE statement: this indicates the beginning of the routine or trigger. Possible values are CREATE PROCEDURE, CREATE FUNCTION and CREATE TRIGGER.
  • the routine or trigger name
  • parameter declarations for procedures and functions (no parameters for triggers)
  • properties
  • options: this options control the way the PSM object is created
  • This is the main of the routine, a set of SQL statements.


The following is an example of procedure to insert records into an employee table.


SQL Control statement

The logic that control the flow through the code is the “control statement”. It could be defined by the constructs:

  • assignment (SET statement, INTO clause)
  • conditional control (IF-THEN, IF THEN-ELSE, IF-THEN ELSEIF, CASE)
  • iterative control (LOOP, WHILE, REPEAT, FOR, LEAVE and ITERATE to directly affect iterative statements)
  • calling (CALL)
  • compound (it consists in a BEGIN and END block of any number of SQL statements)

For the sake of brevity I will not discuss the constructs listed, referring to the consultation of the indicated RedBook.


Dynamic SQL in PSM/error handling/ transaction management

SQL statements could be used in two ways: static SQL and dynamic SQL. The last one is a statement that is known only at run time, when it is built up from the program logic and executed. Dynamic SQL offers a high level of flexibility: SQL statement could be assembled including parameters values that are received from the interface. The main SQL statements that can be processed as dynamic statements are:

  • SELECT: selects are used for cursors and for INTO statements
  • BEGIN/END compound statements: an entire block of statements could be generate dynamically.


The following statements are used in PSM to process dynamic SQL statements:


I remind you to the indicated RedBook for all the details about how realize dynamic SQL, handle errors and execute transactions.

After this short summarize of base SQL constructs, we could examine the main SQL/PSM objects:



As I wrote before there are two way to implement procedure: SQL procedures or external procedure (by high-level language programs). In this case you should use imbedded SQL in a host language program. Usually the recommended implementation is the first but there are cases where it is better to use external procedures.

Procedures are called by SQL CALL statement and parameters can be passed. The application waits for the end of the procedure before to go on. Procedures can be called locally, in the same server where the application runs, or remotely.

The procedure has the following structure:

  • a procedure name
  • the declared parameters
  • the procedure properties
  • the options to control the creation
  • the routine body

For more details about the syntax please consult the website:

The following figure shows the syntax to create a procedure:

Every procedure starts with “CREATE PROCEDURE” and after comes the procedure name.

The parameters declarations are enclosed within parentheses, immediately after the name.

The WRAPPED clause is supported for procedures, triggers, and functions to allow the use of

an obfuscated version of the routine body. Support for triggers was added in IBM i 7.2.

A parameter can be declared as one of three types: IN, OUT, or INOUT where In is the default type. Each parameter must have a valid data type and can have a default value.

The data type for a procedure parameter can be the built-in types, a distinct type (which is also known as a user-defined type), or an array type.

For more details about the built-in and distinct data types, see the DB2 for i SQL reference,

which is at this website:


Calling a procedure

The SQL procedure must be called by the following statement:

On the call statement the name of the stored procedure must be the same of the name specified in the CREATE PROCEDURE statement. Arguments can be constant, registers, host variables or expressions. SQL descriptors can be used instead of specifying individual variables. The descriptor must be allocated large enough to contain all of the parameters that are defined.




The trigger is a set of actions that are executed automatically by the database when a delete, insert or update operation occurs on a specified table or view. When the operation on the table is executed, the trigger is activated or invoked. Triggers represent a powerful extension to conventional programming because they are called directly by the database rather than directly by the application program. They have the following properties:

  • Triggers are activated regardless of the database interface that is used. They include SQL operations, such us DELETE, INSERT and UPDATE
  • Triggers are activated regardless of the user interface

End users are not aware that the trigger exists, because no changes are required to the application program.

Important benefits are provided by triggers:

  • Implementation of business rules
  • Improved database consistency across tables
  • Improved auditing and controls: it’s more difficult for a user to make an unauthorized change in the database
  • Improved integration of existing applications and non-database functionality: triggers could a send confirmation text message, a message to the system operator or send an email.
  • Improved integration with internal and third-party applications: trigger can provide a way to take actions when table changes without any changes to the application.
  • Trigger can be cascaded

Triggers integrate the functionality that is provided by referential integrity or check constraints. It is important to use them for business rules that only can’t be implemented by constraints.

Constraints are used to maintain the integrity and atomicity of database for preventing invalid data especially when tables are created. The main 5 constrains are NOT NULL, PRIMARY KEY, FOREIGN KEY, UNIQUE KEY and CHECK. Triggers are stored procedures “event oriented” that are activated when any insert, update or delete is issued on the table.

There are two different type of triggers:

  • SQL triggers

In this case the program that perform the tests and actions is written by using the SQL programming language. An SQL trigger can call stored procedures or user-defined functions to perform additional processing when the trigger is activated.

SQL trigger offers all the significant advantages of the SQL use in term of standard, syntax and portability.

  • External triggers

For an external trigger the program that contains the actions is defined in any of the common HLL languages. SQL can be embedded into the trigger program. They are managed by using control language (CL) commands, such as Add Physical File Trigger (ADDPFTRG).

The figure shows the syntax for SQL CREATE TRIGGER and ALTER TRIGGER statements. For additional information please consult the link:

Each trigger must have a unique name within the schema. The trigger is created in the schema that is specified by the implicit or explicit qualifier, if the schema is not qualified the trigger is created in the same schema as the subject table or view.

The following figures show the clauses that you can indicate in trigger creation:


Here a sample of trigger to manage the employee count:




User Defined Functions are a possibility for the user to customize tasks in applications: they allow the programmers to modularize a database application and they can be used in SQL.

DB2 for i comes with a rich set of built-in functions, but users and programmers might have different requirements that are not covered by the built-in functions. UDFs play an important role by allowing users and programmers to enrich the database manager by providing their own functions.

UDFs offer the following advantages:

  • Customization: you can create functions that do not exist in the set of DB2 built-in functions
  • Flexibility: you can use functions with the same name in the same library by using different sets of parameters
  • Standardization: you can reuse the UDFs in your other applications by using SQL
  • Performance: a UDF can run in the database engine and this is useful for performing calculations in the database manager server.
  • Portability: you could use UDFs to create built-in functions not defined in DB2 for I, when you migrate your data


UDFs can be classified, depending on the nature of the return value or values, into two groups:

  • User-defined scalar functions

These UDFs return a single scalar value. They are used in a SELECT list or in a WHERE clause. They can be useful for encapsulating complex operations or calculations.

  • User-defined table functions

User-defined table functions can return a set of output values in a row and columnar format. This set of output values is known as a table or “result set”. UDFs are invoked from an SQL FROM clause and they can be referred to in a view for ease of use.

One useful use of the table function is the ability to access data in non-ralational object with an SQL statement. A table function can be written to extract data out of a stream file in the integrated file system (IFS).

User-defined functions can be listed into three categories:

  • Sourced UDFs

These are functions that are registered to the database and make a reference to another function. It means no code is involved but they map to the sourced function. The only thing required for an UDF like this is to use the CREATE OR REPLACE FUNCTION statement.

  • SQL UDFs

These functions are written entirely by using the SQL programming language, so the SQL statements are embedded within the CREATE FUNCTION statement.

  • External UDFs


In the following figures I show the complete syntax for all the parameters about the CREATE FUNCTION statement.

Here samples of scalar and table functions:



This little panoramic on the possibilities of the SQL use in a data-centric approach is surely not exhaustive, but it is only an introduction to have a general idea about the meaning of procedures, triggers and UDF and their use.

You should surely deeply analyze these arguments when you will use them in your job.

The important thing is to have clear the idea of the potentiality of this methodology.

Leave a Reply

Your email address will not be published. Required fields are marked *