Benefits of using routines
Routines are a powerful way of encapsulating logic, which improves the application logic structure, the code maintenance, and potentially improve your application performance.
The following benefits can be gained by using routines:
- Encapsulates application logic that can be called from the SQL interface
- In an environment that contains many different client applications with common requirements, an effective use of routines can simplify code reuse, standardize the code, and simplify the code maintenance. If a particular aspect of common application behavior is implemented as a routine and changes to the common application behavior is required, only the affected routine that encapsulates the behavior requires modification. If routine is not used to encapsulate the common application logic, all affected application must be modified to address the required change to a common application behavior.
- Enable controlled access to other database objects
- Routines can be used to control access to database objects. A user might not have permission to generally issue a particular SQL statement, such as the CREATE TABLE statement. However, a user can be given required permission to call routines that contain one or more specific implementations of the CREATE TABLE statement, thus simplifying privilege management through encapsulation.
- Improve application performance by reducing network traffic
- When applications are installed on a remote client, each SQL statement is sent separately from the client computer to the database server computer and each result set is returned separately. Sending the query and receiving result set can result in high levels of network traffic. If an application task that requires extensive database interaction can be isolated, you can implement such task as a routine on the server to minimize the network traffic and improve the application performance.
- Allow for faster, more efficient SQL execution
- Because routines are database objects, they are more efficient at transmitting SQL requests and data than client applications. Therefore, SQL statements within routines can provide better performance than if SQL statements are issued from the client applications. If routines are created with the NOT FENCED clause, the NOT FENCED routines are run under the database manager process and use the shared memory for communication, which can result in improved application performance.
- Allow the interoperability of logic that is implemented in different programming languages
- Routines provide high degree of interoperability as it can be
called from various applications that are implemented in different
programming languages. Client applications in one programming language
can call routines that are implemented in a different programming
language. For example, C client applications can call .NET common
language runtime routines.
Routines can call other routines regardless of the routine type or routine implementation. For example, a Java™ procedure can call an embedded SQL scalar function.
Routines that are created in a database server on one operating system can be called from a database client on a different operating system.
Use of the routines can benefit database administrators, database architects, and database application developers.
The choice of routine type and implementation can affect the degree of the benefits that can be achieved.