Creating SQL procedures from the command line

Using the command line to create SQL procedures can be faster than using graphical developmental environment tools.

Before you begin

The user must have the privileges required to execute the CREATE PROCEDURE statement for an SQL procedure.
Privileges to execute all of the SQL statements included within the SQL-procedure-body of the procedure.
Any database objects referenced in the CREATE PROCEDURE statement for the SQL procedure must exist prior to the execution of the statement.

Procedure

Select an alternate terminating character for the Command Line Processor (Db2® CLP) other than the default terminating character, which is a semicolon (';'), to use in the script that you will prepare in the next step.
This is required so that the CLP can distinguish the end of SQL statements that appear within the body of a routine's CREATE statement from the end of the CREATE PROCEDURE statement itself. The semicolon character must be used to terminate SQL statements within the SQL routine body and the chosen alternate terminating character should be used to terminate the CREATE statement and any other SQL statements that you might contain within your CLP script.

For example, in the following CREATE PROCEDURE statement, the 'at;' sign ('@') is used as the terminating character for a Db2 CLP script named myCLPscript.db2:
```
    CREATE PROCEDURE UPDATE_SALARY_IF
    (IN employee_number CHAR(6), IN rating SMALLINT)
    LANGUAGE SQL
    BEGIN
      DECLARE not_found CONDITION FOR SQLSTATE '02000';
      DECLARE EXIT HANDLER FOR not_found
         SIGNAL SQLSTATE '20000' SET MESSAGE_TEXT = 'Employee not found';

      IF (rating = 1)
        THEN UPDATE employee
          SET salary = salary * 1.10, bonus = 1000
          WHERE empno = employee_number;
      ELSEIF (rating = 2)
        THEN UPDATE employee
          SET salary = salary * 1.05, bonus = 500
          WHERE empno = employee_number;
      ELSE UPDATE employee
          SET salary = salary * 1.03, bonus = 0
          WHERE empno = employee_number;
      END IF;
    END
@
```
Run the Db2 CLP script containing the CREATE PROCEDURE statement for the procedure from the command line, using the following CLP command:
```
   db2 -td terminating-character -vf CLP-script-name
```
where terminating-character is the terminating character used in the CLP script file CLP-script-name that is to be run.

The Db2 CLP option -td indicates that the CLP terminator default is to be reset with terminating-character. The -vf indicates that the CLP's optional verbose (-v) option is to be used, which will cause each SQL statement or command in the script to be displayed to the screen as it is run, along with any output that results from its execution. The -f option indicates that the target of the command is a file.

To run the specific script shown in the first step, issue the following command from the system command prompt:
```
   db2 -td@ -vf myCLPscript.db2
```