Intelligent Enterprises of the 21st Century

When SQL procedures are created outside of the Development Center, their definitions are typically stored in an ASCII flat file that will be sent to DB2 to process. When using this technique, you will have to change the default statement termination character by using one of the options in the DB2 command window. First, you will have to change the default statement termination by using a few options in the DB2 command window.

Options that you need to be familiar with are discussed in the following sections. All other supported options are covered briefly.

Creating a DB2 Command-Line Processor Script

The file extension does not affect how DB2 processes the file. However, the scripts used in this book end with an extension of .sql.

Note

It is important to note that the SQL procedures listed in the CLP script must be specified in an order that satisfies all dependencies. When the SQL procedure is created, the compiler will check for the existence of procedures being called at compilation time. If a calling procedure does not exist, DB2 will raise an error.

Type the complete CREATE PROCEDURE statement into the script file as you normally would. At the end of the statement, postfix it with a @ (or any other character not used in the code). This is the new statement termination character. Without it, DB2 would use the default character semicolon (;) as the statement terminator. However, because each statement inside the SQL procedure body already ends with a semicolon (;), this would cause each statement in the SQL procedure body to be executed separately as opposed to being part of the same statement. To tell DB2 to use a different statement terminator other than the default, use one of the DB2 CLP options (such as -td), which is demonstrated later in the chapter.

Before executing the script, connect to the database:

db2 connect to sample

Consider the script in Figure C.1.

Figure C.1. Sample DB2 CLP script containing SQL Procedure definitions.

CREATE PROCEDURE DB2ADMIN.CALC_EMP_RAISE ( IN p_EmpNum CHARACTER(6) , IN p_PerfReview INTEGER , OUT p_SugSalary DECIMAL(9,2) ) DYNAMIC RESULT SETS 1 LANGUAGE SQL SPECIFIC CALC_EMP_RAISE P1: BEGIN -- Declare variables DECLARE v_SugSalary DECIMAL(9,2) DEFAULT 0.0; DECLARE v_prep_stmt VARCHAR(128) DEFAULT 'SELECT Rating, SuggestedSalary FROM SESSION.SalaryRange'; -- Declare cursor DECLARE cursor1 CURSOR WITH RETURN FOR prep_stmt; -- Declare temporary table DECLARE GLOBAL TEMPORARY TABLE SESSION.SalaryRange (Rating INT, SuggestedSalary DECIMAL(9,2)); SELECT Salary INTO v_SugSalary FROM employee WHERE EMPNO = p_EmpNum; INSERT INTO SESSION.SalaryRange VALUES (1, v_SugSalary * 1.10); INSERT INTO SESSION.SalaryRange VALUES (2, v_SugSalary * 1.05); INSERT INTO SESSION.SalaryRange VALUES (3, v_SugSalary * 1.02); SELECT SuggestedSalary INTO p_SugSalary FROM SESSION.SalaryRange WHERE Rating = p_PerfReview; PREPARE prep_stmt FROM v_prep_stmt; -- Cursor left open for client application OPEN cursor1; RETURN 0; END P1 @ CREATE PROCEDURE DB2ADMIN.EMP_LIST () DYNAMIC RESULT SETS 1 LANGUAGE SQL SPECIFIC EMP_LIST BEGIN -- Declare cursor DECLARE cursor1 CURSOR WITH RETURN FOR SELECT Salary FROM employee; -- Cursor left open for client application OPEN cursor1; END @ CREATE PROCEDURE DB2ADMIN.PROC3 RETURN 3 @ CREATE PROCEDURE DB2ADMIN.PROC4 RETURN 4 @

To create the SQL procedures, issue the following command:

db2 -td@ -vf test_sprocs.sql

Now the SQL Procedure should be built and ready to use. Other options are available on the command line, see Table C.1 for their explanations.