Routines
Routines are pieces of code or programs that you can call to perform operations.
- Stored procedures
A procedure (often called a stored procedure) is a program that can be called to perform operations. A procedure can include both host language statements and SQL statements. Procedures in SQL provide the same benefits as procedures in a host language. - Using user-defined functions
In writing SQL applications, you can implement some actions or operations as a user-defined function (UDF) or as a subroutine in your application. Although it might appear easier to implement new operations as subroutines, you might want to consider the advantages of using a UDF instead. - Triggers
A trigger is a set of actions that runs automatically when a specified change operation is performed on a specified table or view. - Array support in SQL procedures
SQL procedures support parameters and variables of array types. Arrays are a convenient way of passing transient collections of data between an application and a stored procedure or between two stored procedures. - Debugging an SQL routine
By specifying SET OPTION DBGVIEW = *SOURCE in the CREATE PROCEDURE, CREATE FUNCTION, or CREATE TRIGGER statement, you can debug the generated program or service program at the SQL statement level. - Obfuscating an SQL routine
SQL functions and procedures can be obfuscated so that their routine body logic and statements are not visible on a system. - Managing SQL and external routine objects
SQL and external functions and procedures are implemented using system programs and service programs. When managed correctly, these objects can regenerate the routine registration in the QSYS2/SYSROUTINES, QSYS2/SYSPARMS, and QSYS2/SYSROUTINEDEP system catalogs. - Improving performance of procedures and functions
When creating a stored procedure or a user-defined function (UDF), the SQL procedural language processor does not always generate the most efficient code. However, you can reduce the number of database engine calls and improve performance.
Parent topic: SQL programming