DB2 10.5 for Linux, UNIX, and Windows

Creating objects in modules

Creating objects in modules is a task that you would perform after you have created a module when you have identified a set of routines, data types, and variables (perhaps in support of a single business case) that you would like to define together within a common namespace for easier management and deployment.

Before you begin

Before proceeding with this task:

Review the ALTER MODULE statement documentation.
Ensure that you have the privileges required to execute the ALTER MODULE statement.

About this task

This task is done by executing the ALTER module statement. The ALTER module statement allows objects to be added as published objects or as unpublished objects. Published objects can be referenced from outside of the module, while unpublished objects can be referenced only by other objects in the same module.

Within modules, a procedure or function can be initially defined without a routine-body. These are called prototypes. Prototypes can only be created for published procedures or functions. The routine-body for a published routine prototype can be added later using the same routine signature.

Restrictions

For restrictions on modules:

See: Restrictions on modules

Procedure

Formulate an ALTER MODULE statement:
- Specify the ADD clause to add an object to the module that can only be referenced from within the module.
- Specify the PUBLISH clause to add an object to the module that can be referenced from within the module and from outside the module.
1. Provide the definition for the object to be created in the module according to the ALTER MODULE statement syntax. For routines, if the PUBLISH clause is specified, you can optionally provide only the routine prototype. The implementation of the routine can be provided later using the ALTER MODULE statement with the ADD clause.
Execute the ALTER MODULE statement from a supported DB2® interface.
Repeat steps 1 - 2 to create additional objects in the database.

Results

If the ALTER MODULE statement executes successfully the object definition is added to the module.

Example

The following is an example of how to use the ALTER MODULE statement to add objects to a module named inventory:

ALTER MODULE inventory
  PUBLISH PROCEDURE p(IN c1 INT, IN c2 INT, IN c3 CHAR) @

ALTER MODULE inventory
  PUBLISH PROCEDURE q(IN c1 INT, IN c2 VARCHAR(8))@

ALTER MODULE inventory
  ADD PROCEDURE p(IN c1 INT, IN c2 INT, IN c3 CHAR) 
           LANGUAGE SQL 
           BEGIN 
              IF c1 > 10 THEN
                CALL q(1, 'hello')
              ELSE IF c2 > 5 THEN
                   SET c3 = CHAR(c2);
              END IF;
           END@

ALTER MODULE inventory
  ADD PROCEDURE r(c1 INT, c2 INT, c3 CHAR) 
           LANGUAGE SQL 
           BEGIN 
             CALL q(1, 'hello');
           END@

What to do next

After creating objects in a module, you can add additional module objects to the module, reference module objects in SQL statements, invoke module routines, or perform other related module or database tasks.