DB2: How to Call External Stored procedure

An external stored procedure is a procedure that is written in a host language and can contain SQL statements. The source code for external procedures is separate from the definition. Just a try SQL Server with Stored procedures-Tables, you can learn how to apply for DB2.

Example:

The definition for external procedure. The “mymodule” stored procedure contains source code.

CREATE PROCEDURE SYSPROC.MYPROC(IN INT, OUT INT, OUT DECIMAL(7,2))
         LANGUAGE COBOL
         EXTERNAL NAME MYMODULE
         PARAMETER STYLE GENERAL
         WLM ENVIRONMENT PARTSA
         DYNAMIC RESULT SETS 1;

An external stored procedure is much like any other SQL application. It can include static or dynamic SQL statements, IFI calls, and DB2® commands that are issued through IFI.

How to create external stored procedures

You prepare external stored procedures as you would normally prepare application programs. You precompile, compile, and link-edit them. Then, you bind the DBRM into a package.

LANGUAGE -This is mandatory clause used to specify the language interface convention to which the procedure body is written.

All programs must be designed to run in the server’s environment. Assembler, C, COBOL, and PL/I programs must be designed to run in IBM®’s Language Environment®.

ASSEMBLE: The stored procedure is written in Assembler.
C: The stored procedure is written in C or C++.
COBOL: The stored procedure is written in COBOL, including the OO-COBOL language extensions.
JAVA: The stored procedure is written in Java and is executed in the Java Virtual Machine. When LANGUAGE JAVA is specified, the EXTERNAL NAME clause must be specified with a valid external-java-routine-nameand PARAMETER STYLE must be specified with JAVA. The procedure must be a public static method of the specified Java class.Do not specify LANGUAGE JAVA when DBINFO, PROGRAM TYPE MAIN, or RUN OPTIONS is specified.
PLI: The stored procedure is written in PL/I.
REXX: The stored procedure is written in REXX. Do not specify LANGUAGE REXX when PARAMETER STYLE SQL is in effect. When REXX is specified, the procedure must use PARAMETER STYLE GENERAL or GENERAL WITH NULLS.