Developing Routines

Development of routines is often done when there is no built-in routine available that provides the functionality that is required.

Before you begin

  • Read and understand basic routine concepts:
    • See the following topic to learn about types of routines, useful applications of routines, tools for developing routines, routine best practices:
  • Learn about the available routine development tools that make it faster and easier to develop routines:

About this task

There are different functional types of routines and routine implementations, however the basic steps for developing routines are common for all routines. You must determine what type of routine to create, what implementation to use, define the interface for the routine, develop the routine logic, execute SQL to create the routine, test your routine, and then deploy it for general use.

The following procedure provides steps for getting started with the routine development.

Procedure

  1. Determine whether an existing built-in routine already meets your routine needs.
    • If a built-in routine meets your needs, you might want to refer to Invoking routines .
  2. Determine what functional type of routine to develop.
  3. Determine what routine implementation to use.
    • If a SQL routine is required, refer to the information about SQL routines.
    • If an external routine is required, refer to the information about External routines.

What to do next

The development of SQL and external routines is similar, but there are differences. For both types of routines, you must first design your logic, and then to create the routine in the database but the CREATE statement is specific to the routine type. These routine creation statements include CREATE PROCEDURE, CREATE FUNCTION, and CREATE METHOD. The clauses specific to each of the CREATE statements define characteristics of the routine, including the routine name, the number and type of routine parameters, and details about the routine logic. The database manager uses the information that is provided by the clauses to identify and run the routine when it is called. Upon successful execution of the CREATE statement for a routine, the routine is created in the database. The characteristics of the routine are stored in the database system catalog tables that users can query. Executing the CREATE statement to create a routine is also referred to as defining a routine or registering a routine.

Because external routines have their logic implemented in user-created libraries or classes that are located in the database file system, additional steps are required to program the logic, build it, and properly locate the resulting library or class file.

Once you have developed routines, you can perform the following steps:
  • Debug routines
  • Deploy routines to production environments
  • Grant execute privileges to users
  • Call routines
  • Tune the performance of routines