Defining Stored Procedures under DB2 LUW

A stored procedure is unusable until it is defined. Use the CREATE PROCEDURE command to do this. You can either use the DB2 command prompt or place the command in a program and compile and run it. If you use the DB2 command prompt, you first connect to the DB2 LUW Server where the stored procedure will be executed.

For example:

C:> db2 connect to sample

You can type in the command at the DB2 command prompt making sure you include continuation characters and command delimiters, or you can place the CREATE PROCEDURE in an ANSI text file. For example, if we placed the previous command in text file creproc.sql, the command that you would enter would be:

Windows:

C:> db2 -td; -vf creproc.sql

UNIX:

C:> db2 -td\; -vf creproc.sql

where:

• the -td option indicates the next character is the delimiter to end the command. In our example, it is a semicolon (;).
• the -vf option indicates that the next token is the file to process that contains the SQL command script.
• The backslash (UNIX only) is required so that the shell does not interpret the semi-colon.

The create procedure statement must uniquely identify a stored procedure. If you want to change the stored procedure to either add or drop parameters or change functionality, you must use the DROP PROCEDURE command and then re-add it with the CREATE PROCEDURE command.

This example includes a CREATE PROCEDURE command identifying a unique stored procedure in a text file (which would be named creproc.sql).

DROP PROCEDURE GETPRML;
CREATE PROCEDURE GETPRML
  (IN PROCNM CHAR(18) 
  ,IN SCHEMA CHAR(8)  
  ,OUT OUTCODE INTEGER 
  ,OUT PARMLST VARCHAR(30) 
  )   
    DYNAMIC RESULT SETS 0
    LANGUAGE COBOL 
    PARAMETER STYLE GENERAL
    NO DBINFO
    FENCED
    NOT THREADSAFE
    READS SQL DATA
    PROGRAM TYPE SUB
    EXTERNAL NAME "GETPRML!GETPRML"  ;

When DB2 was originally developed, it did not support the CREATE PROCEDURE function, and it is possible to write COBOL stored procedures without doing a CREATE PROCEDURE. Examples of this method and the parmlist that is required are included in the DB2 LUW Application Development Client.