DB2 Version 10.1 for Linux, UNIX, and Windows

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:
- To learn about types of routines, useful applications of routines, tools for developing routines, routine best practices and more, see the topic:
  - Overview of routines
Learn about the available routine development tools that make it faster and easier to develop routines:
- To learn about the available tools for routine development, see the topic:
  - Tools for developing routines

About this task

There are different functional types of routines and routine implementations, however the basic steps for developing routines are generally 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.

Depending on what type of routine you choose to develop there are some specific procedures you must follow. This topic will direct you to the appropriate topics for getting started with routine development.

Procedure

Determine if 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 .
Determine what functional type of routine to develop.
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 you must execute a routine functional type specific CREATE statement. 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. DB2® uses the information provided by the clauses to identify and run the routine when it is invoked. 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 DB2's 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 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 might want to:

Debug routines
Deploy routines to production environments
Grant privileges to execute routines to users
Invoke routines
Tune the performance of routines