Building SQL Procedures

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.

Table C.1. CLP Options

Option Flag	Description
-a	Display SQLCA data.
-c	Automatically commit SQL statements.
-e{c|s}	Display SQLCODE or SQLSTATE. These options are mutually exclusive.
-ffilename	Read command input from a file instead of from standard input.
-lfilename	Log commands in a history file.
-n	Removes the newline character within a single delimited token. If this option is not specified, the newline character is replaced with a space. This option must be used with the -t option.
-o	Display output data and messages to standard output.
-p	Display a CLP prompt when in interactive input mode.
-rfilename	Write the report generated by a command to a file.
-s	Stop execution if errors occur while executing commands in a batch file or in interactive mode.
-t	Specifies to use a semi colon (;) as the statement termination character.
-tdx	Specifies to use x as the statement termination character.
-v	Echo command text to standard output.
-w	Display SQL statement warning messages.
-x	Return data without any headers, including column names.
-zfilename	Redirect all output to a file (similar to the -r option, but includes any messages or error codes with the output).