Creating an End-User Routine

You can write end-user routines to accomplish the following tasks:

• Encapsulate multiple SQL statements
• Create triggered actions for multiple applications
• Restrict who can read data, change data, or create objects
• Create iterators

Routines also can accomplish tasks that address new technologies, including the following ones:

• Manipulate large objects
• Manage new data domains, such as images, web publishing, and spatial

Encapsulating Multiple SQL Statements

You create a routine to simplify writing programs or to improve performance of SQL-intensive tasks.

Simplifying Programs

A UDR can consolidate frequently performed tasks that require several SQL statements. Both SPL and external languages offer program control statements that extend what SQL can accomplish alone. You can test database values in a UDR and perform the appropriate actions for the values that the routine finds.

By encapsulating several statements in a single routine that the database server can call by name, you reduce program complexity. Different programs that use the same code can execute the same routine, so that you need not include the same code in each program. The code is stored in only one place, eliminating duplicate code.

Simplifying Changes

UDRs are especially helpful in a client/server environment. If a change is made to application code, it must be distributed to every client computer. A UDR resides in the database server, so only database servers need to be changed.

Instead of centralizing database code in client applications, you create UDRs routines to move this code to the database server. This separation allows applications to concentrate on user-interface interaction, which is especially important if multiple types of user interfaces are required.

Improving Performance Using SPL

Because an SPL routine contains native database language that the database server parses and optimizes as far as possible when you create the routine, rather than at runtime, SPL routines can improve performance for some tasks. SPL routines can also reduce the amount of data transferred between a client application and the database server.

For more information on performance considerations for SPL routines, refer to Improving UDR Performance.

Creating Triggered Actions

An SQL trigger is a database mechanism that executes an action automatically when a certain event occurs. The event that can trigger an action can be an INSERT, DELETE, or UPDATE statement on a specific table. The table on which the triggered event operates is called the triggering table.

An SQL trigger is available to any user who has permission to use it. When the trigger event occurs, the database server executes the trigger action. The actions can be any combination of one or more INSERT, DELETE, UPDATE, EXECUTE PROCEDURE, or EXECUTE FUNCTION statements.

Because a trigger resides in the database and anyone who has the required privilege can use it, a trigger lets you write a set of SQL statements that multiple applications can use. It lets you avoid redundant code when multiple programs need to perform the same database operation. By invoking triggers from the database, a DBA can ensure that data is treated consistently across application tools and programs.

You can use triggers to perform the following actions as well as others that are not found in this list:

• Create an audit trail of activity in the database

  For example, you can track updates to the orders table by updating corroborating information in an audit table.

• Implement a business rule

  For example, you can determine when an order exceeds a customer's credit limit and display a message to that effect.

• Derive additional data that is not available within a table or within the database

  For example, when an update occurs to the quantity column of the items table, you can calculate the corresponding adjustment to the total_price column.

For more information on triggers, refer to the IBM Informix: Guide to SQL Tutorial.

Restricting Access to a Table (SPL)

SPL routines offer the ability to restrict access to a table. For example, if a database administrator grants insert permissions to a user, that user can use ESQL/C, DB–Access, or an application program to insert a row. This situation could create a problem if an administrator wants to enforce any business rules.

Using the extra level of security that SPL routines provide, you can enforce business rules. For example, you might have a business rule that a row must be archived before it is deleted. You can write an SPL routine that accomplishes both tasks and prohibits users from directly accessing the table.

Rather than granting insert privileges, an administrator can force users to execute a routine to perform the insert.

Creating Iterators

An iterator function returns an active set of items. Each iteration of the function returns one item of the active set. To execute an iterator function, you must associate the function with a database cursor.

The database server does not provide any built-in iterator functions. However, you can write iterator functions and register them with the ITERATOR routine modifier. For more information, see Using an Iterator Function.