DB2 10.5 for Linux, UNIX, and Windows

Referencing objects defined in modules

Referencing objects defined in modules can be done from within a module or from outside of the module. The ability to reference objects defined in modules also depends on whether the objects in the module have been published or not.

You can reference module objects by 3-part, 2-part, or sometimes 1-part names. Resolution of name references is done deterministically and depends on the name reference and the context (within the module or outside the module) in which it is used.

From within a module:
- Objects defined within the module, both published and unpublished module objects, can be referenced using 3-part, 2-part, and 1-part name references.
From outside of the module:
- Only published module objects can be referenced using 3-part and 2-part name references.

Object resolution of 3-part name references

3-part name references are the most straightforward; they are of the form <SCHEMA-NAME>.<MODULE-NAME>.<OBJECT-NAME>. References of this type will always resolve to a unique module object.

The following is the definition of a module named mod in schema named schema:

CREATE MODULE schema.mod@

ALTER MODULE schema.mod 
  PUBLISH PROCEDURE proc1 (a integer) 
            BEGIN
            END@

The following is an example of a CALL statement that references the procedure using a 3-part name reference:

CALL schema.mod.proc1@

A 3-part name can contain a package name if the connected server supports the DB2_COMPATIBILITY_VECTOR registry variable and the variable is set to ORA. The following embedded example shows use of a 3-part name to call a stored procedure:

// Assume that the DB2 server has following package and procedure defined
CREATE OR REPLACE PACKAGE MyPackage IS
  PROCEDURE MyProcedure(INOUT a INTEGER);
END;

CREATE OR REPLACE PACKAGE BODY MyPackage IS
  PROCEDURE MyProcedure(INOUT a INTEGER) IS
    a := a + 100;
  END;
END;

// Embedded SQL C program snippet
EXEC SQL BEGIN DECLARE SECTION;
  sqlint32 inout_val;
EXEC SQL END DECLARE SECTION;

EXEC SQL CALL MySchema.MyPackage.MyProcedure(:inout_val);

Object resolution of 2-part name references

2-part name references are of the form <MODULE-NAME>.<OBJECT-NAME>.

The 2-part name reference format can be ambiguous since non-module objects can be referenced by 2-part names of the form <SCHEMA-NAME>.<OBJECT-NAME>. The first qualifier could refer to either a schema or a module. A deterministic method for resolving 2-part name references will resolve the object reference as follows:

If the context of the reference is within a module and the first part of the qualifier matches that module name, the database manager searches the module for the object, applying the appropriate resolution for the type of object to find a match among published and unpublished module objects. If no match is found, the search continues as per the next bullet.
It is then presumed that the first part of the 2-part name reference is a schema name. If a schema with that name exists, object resolution within that schema.
If the first part of the 2-part name reference is not an existing schema, or if the object is not found in the schema that matches the qualifier and the qualifier did not match the context module name , search for the first module that matches the qualifier in the schemas on the SQL path. If authorized to the matching module, resolve to the object in that module, considering only published module objects.
If the qualifier is not found as a module on the SQL path and the qualifier did not match the context module name, check for a module public synonym that matches the qualifier. If found, resolve the object in the module identified by the module public synonym, considering only published module objects.

The following SQL statements define multiple module objects and references to module objects. Comments indicate to what module object each 2-part object name reference will resolve to:

CREATE PROCEDURE S.proc1 (a integer) 
        BEGIN END@

CREATE MODULE schema.S@

ALTER MODULE schema.S 
  PUBLISH PROCEDURE proc1 (a integer) 
            BEGIN END@

CALL S.proc1(5)@ 								-- This reference resolves to procedure S.proc1()

ALTER MODULE schema.S 
  PUBLISH PROCEDURE proc2 (a integer) 
            BEGIN
              CALL S.proc1(5)		-- This reference resolves to procedure schema.S.proc1()
            END@

CREATE MODULE schema.M@

ALTER MODULE schema.M 
  PUBLISH PROCEDURE proc1 (a integer) 
            BEGIN END@

CALL M.proc1(5)@ 				         -- This reference resolves to procedure schema.M.proc1()

Object resolution of 1-part name references

1-part name references can be used for module objects only within the context of that module. Only an object within a module can reference another module object using a 1-part name reference.

The following defines the deterministic resolution of 1-part object name references:

If the context of the reference is within a module, a search is performed within the module for the object subject to the object being of a matching type. If no match is found for the 1-part name reference, the object is assumed to not be a module object, but a schema object. The SQL path is sought for the first schema in the SQL path such that the object of the appropriate type exists in the schema. If not such object is found, resolution of the name reference fails.
The following SQL statements define objects, modules, and module objects - the comments indicate which

CREATE PROCEDURE S.proc1 (a integer) 
         BEGIN
         END@

CREATE MODULE schema.S@

ALTER MODULE schema.S 
  PUBLISH PROCEDURE proc1 (a integer) 
    BEGIN
    END@

CALL proc1(5)@ 				-- will resolve to S.proc1()

ALTER MODULE schema.S 
  PUBLISH PROCEDURE proc2 (a integer) 
            BEGIN
              CALL proc1(5)			-- will resolve to schema.S.proc1()
            END@

CREATE MODULE schema.M@

ALTER MODULE schema.M 
  PUBLISH PROCEDURE proc1 (a integer) 
            BEGIN
              CALL proc1(5)			-- will resolve to S.proc1()
                                  -- (since no proc1() in module M)
            END@