Encapsulate multiple SQL statements

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

Simplify 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.

Simplify changes

UDRs are especially helpful in a client or 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.

Improve 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 about performance considerations for SPL routines, refer to Improve UDR performance.