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.
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:
C:> db2 -td; -vf creproc.sql
C:> db2 -td\; -vf creproc.sql
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.