Creating SQL procedures from the command line

document origin URL: http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=%2Fcom.ibm.db2.luw.sql.rtn.doc%2Fdoc%2Fr0012547.html

Prerequisites

• 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 theSQL-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.execution

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