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