Application Development Guide

Issuing CREATE PROCEDURE Statements

To issue a CREATE PROCEDURE statement as a DB2 Command Line Processor (DB2 CLP) script, you must use an alternate terminating character for SQL statements in the script. The semicolon (';') character, the default for DB2 CLP scripts, terminates SQL statements within the SQL procedure body.

To use an alternate terminating character in DB2 CLP scripts, select a character that is not used in standard SQL statements. In the following example, the at sign ('@') is used as the terminating character for a DB2 CLP script named script.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
@

To process the DB2 CLP script from the command line, use the following syntax:

   db2 -tdterm-char -vf script-name

where term-char represents the terminating character, and script-name represents the name of the DB2 CLP script to process. To process the preceding script, for example, issue the following command from the CLP:

   db2 -td@ -vf script.db2