学步园

Differences between external stored procedures and SQL stored procedures

There are two types of stored procedures that DB2 supports. External stored procedures and SQL procedures.

External stored procedures have their logic defined in a programming language application that exists outside of the database. The executable or library for this type of stored procedure exists in the file system in which the database server is installed.
The external stored procedure, like the SQL procedure, is registered with the database, but during the registration process, the location of the stored procedure executable or library needs to be specified.

DB2 supports external stored procedures in a variety of programming languages including C, C++, COBOL, Java and .NET (also referred to as a CLR stored procedure).

The following features are unique to external stored procedures:

• External stored procedures allows access to non-database interfaces such as the file system, or applications. They can use these resources even if they are not part of the database system specifically. As an example, an external stored procedure can execute
  a shell script on a UNIX database server to run a specific task.

• External stored procedures use parameter styles to determine how a certain input, output, or input/output parameter is to be used by the programming language used for that stored procedure. Some parameter styles allow the use of passing meta-data information
  such as database and stored procedure property information in a structure known asdbinfo that might be useful to the stored procedure.

• External stored procedures can be defined as FENCED or NOT FENCED. This determines if the stored procedure should run in the same address space as the database manager (NOT FENCED), or if it should run in its own process (FENCED). A stored procedure defined
  as NOT FENCED performs slightly faster since it does not need to communicate using shared memory segments; however, they can be riskier. An unfenced stored procedure can cause the database server to crash if there is a problem with the stored procedure since
  it will be using the same address space as DB2. Java stored procedures must be defined as FENCED; however, they can be defined as THREADSAFE or NOT THREADSAFE.

The following features are unique to SQL procedures:

• SQL procedures are only written in SQL using a language called SQL Programming Language (SQL PL). More information about this language can be found in the previous tutorial of this series (seeResources).
  So, the main difference between external stored procedures and SQL procedures is that external procedures are coded using a specific programming language, whereas SQL procedures are coded using only SQL statements.

• SQL procedures reside in the actual database. Unlike external stored procedures which have a dependency on an external library or executable that exists in the file system, an SQL procedure is part of the database.

• Building an SQL procedure does not require a compiler or a deep understanding of a specific programming language. So the development of an SQL procedure might be quicker.

• SQL procedures are always defined as NOT FENCED. There is less risk involved with these types of stored procedures since only SQL operations can be performed by the stored procedure limiting the risk involved with the database server.

• SQL procedures are more portable. Since they do not rely on a specific programming language whose compiler or interpreter would be needed on each database server, it would be easier to re-create these stored procedures on each server that needs them.

SQL procedure structure

The SQL procedure structure consists of the CREATE PROCEDURE statement, parameters, and compound statement. The following pseudo-diagram shows the structure of an SQL procedure:

Listing 1. Structure of an SQL procedure

CREATE PROCEDURE proc_name
   IN, OUT, INOUT parameters
   optional clauses
   SQL procedure body - compound statement

The CREATE PROCEDURE statement defines the characteristics and logic of the stored procedure which is stored in the DB2 system catalogs (for example, SYSCAT.PROCEDURES).

Listing 2. CREATE PROCEDURE command syntax

CREATE PROCEDURE--procedure-name----------------------------->

>--+----------------------------------------------------+--?---->
   '-(--+------------------------------------------+--)-'      
        | .-,------------------------------------. |           
        | V .-IN----.                            | |           
        '---+-------+--parameter-name--data-type-+-'           
            +-OUT---+                                          
            '-INOUT-'                                          

>--+-------------------------+--?------------------------------->
   '-SPECIFIC--specific-name-'      

   .-DYNAMIC RESULT SETS 0--------.     .-MODIFIES SQL DATA-.   
>--+------------------------------+--?--+-------------------+--->
   '-DYNAMIC RESULT SETS--integer-'     +-CONTAINS SQL------+   
                                        '-READS SQL DATA----'   

      .-NOT DETERMINISTIC-.     .-CALLED ON NULL INPUT-.      
>--?--+-------------------+--?--+----------------------+--?----->
      '-DETERMINISTIC-----'                                   

   .-INHERIT SPECIAL REGISTERS-.     .-OLD SAVEPOINT LEVEL-.   
>--+---------------------------+--?--+---------------------+---->
                                     '-NEW SAVEPOINT LEVEL-'   

      .-LANGUAGE SQL-.     .-EXTERNAL ACTION----.      
>--?--+--------------+--?--+--------------------+--?------------>
                           '-NO EXTERNAL ACTION-'      

>--+------------------------------+--?-------------------------->
   '-PARAMETER CCSID--+-ASCII---+-'      
                      '-UNICODE-'        

>--| SQL-procedure-body |--------------------------------------><

The most commonly used components of this diagram are the procedure_name and the parameters (IN/OUT/INOUT).

The procedure_name is an SQL identifier that can be qualified with a SCHEMA NAME whose maximum limit is 128 characters.

The parameters (IN/OUT/INOUT) are used to provide a mechanism for specific data to be sent into the stored procedure, or for data to be returned from the stored procedure. Each stored procedure defined in the system catalogs is distinguished
by name and the number of parameters (regardless of their datatatypes). No two identically-named procedures within a schema are permitted to have exactly the same number of parameters. IN is the default and is not required to be specified. For example, "CREATE
PROCEDURE test1 (p1 INT, OUT p2 INT)" defines a stored procedure with one input and one output parameter. Please note that a stored procedure can be created without paramertes as well, for example, "CREATE PROCEDURE test2."

SPECIFIC--specific-name assigns the stored procedure a specific name, rather than having DB2 assign a system-generated unique name for it. This is useful if you use overloaded stored procedures with the same name and different number of parameters.
This specific name can be used when dropping the stored procedure. It can never be used to invoke the stored procedure. The qualified form is a schema name followed by a period and an SQL identifier (the limit is 18 characters). If the specific name is not
specified, a unique name is generated by the database manager. The unique name is 'SQL' followed by a character timestamp: 'SQLyymmddhhmmssxxx'.

CONTAINS SQL, READS SQL DATA, MODIFIES SQL DATA indicates the level of data access from stored procedure. If any data manipulation is performed within the stored procedure, for example the use of GLOBAL TEMPORARY TABLES, then the stored procedure
need to be specified with the MODIFIES SQL DATA option. If the BEGIN ATOMIC clause is used in a compound SQL procedure, the stored procedure can only be created if it is defined with MODIFIES SQL DATA.

SQL procedure body is the main body of the stored procedure. At its core is a compound statement. Compound statements are bounded by the keywords BEGIN and END. These statements can be ATOMIC or NOT ATOMIC. By default they are NOT ATOMIC. SQL
Procedures requires particular order of declarations and executable statements within compound statement.

Figure 2 illustrates the structured format of a compound statement within SQL procedures:

Figure 2. Structured format of a compound statement

ATOMIC and NOT ATOMIC compound SQL

There are two types of compound statements: NOT ATOMIC (default) and ATOMIC.

NOT ATOMIC

If an unhandled error condition occurs, no SQL statements are rolled back. Listing 3 demonstrates how it works.

Listing 3. NOT ATOMIC compound statement

CREATE TABLE t1 (c1 INT, c2 CHAR(5))!
CREATE PROCEDURE my_proc1 ()
SPECIFIC not_atomic_example         
P1: BEGIN NOT ATOMIC
   INSERT INTO t1 VALUES(1, 'FIRST');    --(1)
   -- SIGNAL SQLSTATE TO INFORCE ERROR
   SIGNAL SQLSTATE '70001' SET MESSAGE_TEXT = 'INFORCE ERROR';
   INSERT INTO t1 VALUES (2,'SECND');    --(2)
END P1!

If INSERT (1) is executed successfully, then an error is enforced and the SQL procedure is terminated without ever executing INSERT (2). Since this SQL procedure was created with the NOT ATOMIC statement, the first INSERT statement is
not rolled back. If you query table T1 after the stored procedure execution, it will return:

ATOMIC

During execution of an ATOMIC compound statement, if any unhandled error conditions arise within it, then all statements that have been executed up to that point are rolled back. ATOMIC statement cannot be nested inside other ATOMIC compound statements.

Listing 4 shows procedure with an ATOMIC compound statement:

Listing 4. Procedure with ATOMIC compoundstatement

CREATE PROCEDURE my_proc2 ()
SPECIFIC atomic_example         
P1: BEGIN ATOMIC
   INSERT INTO t1 VALUES(3, 'THIRD');    --(1)
   -- SIGNAL SQLSTATE TO INFORCE ERROR
   SIGNAL SQLSTATE '70001' SET MESSAGE_TEXT = 'INFORCE ERROR';                            
   INSERT INTO t1 VALUES (4,'FOUR');      --(2)
END P1

If INSERT (1) is executed successfully, then an error is enforced and the SQL procedure is terminated without ever executing INSERT (2). Since this stored procedure was created with the ATOMIC statement, the first INSERT is rolled back. If you query table
T1 after the SQL procedure executes, it still returns the only one row that had been inserted by the previous example:

Compound SQL and scope of variables

You can have one or more compound statements within SQL procedures. Those compound statements could be nested or one can follow another. Each compound statement introduces a new scope for a local variable where those variables can be used. That is why we
recommend using labels when you have more than one compound statement within a store procedure.

Look at Listing 5 as an example:

Listing 5. Nested Compound blocks

CREATE PROCEDURE VAR_SCOPE (  )              
L1:BEGIN
    DECLARE v_outer1 INT;
    DECLARE v_outer2 INT;
    L2:BEGIN
        DECLARE v_inner1 INT;
        DECLARE v_inner2 INT;
        SET v_outer1 = 100;   --(1) -- success
        SET v_inner1 = 200;
    END L2;
    SET v_outer2 = 300;
    SET v_inner2 = 400;    --(2)  -- fail
END L1

Attempting to build this SQL procedure gives you the following error message:
DB2ADMIN.VAR_SCOPE: 12: "V_INNER2" is not valid in the context where it is used.. SQLCODE=-206, SQLSTATE=42703, DRIVER=3.50.152

Note that statement (1) passes successfully as v_outer1 and is declared outside of the compound statement, but statement (2) fails because
v_inner2 is not declared outside of the compound statement.

Back to top

Nested stored procedures

Invoking nested procedures

DB2 supports the CALL statement to invoke one procedure from another (referred to as a nesting store procedures).

The following diagram illustrates the CALL statement syntax:

Listing 6. CALL statement syntax

>>-CALL--procedure-name--+--------------------------+----------><
                         |    .-,--------------.    |
                         |    V                |    |
                         '-(----+-expression-+-+--)-'
                                '-NULL-------'

Note that DB2 is strongly typed and parameters types in the CALL statement need to be compatible with parameters in the CREATE PROCEDURE statement of the calling stored procedure. The nth argument of the CALL statement corresponds to the nth parameter defined
in the CREATE PROCEDURE statement for the procedure.

Listing 7 demonstrates this relation:

Listing 7. Correlation of CALL statement to CREATE PROCEDUREstatement

CREATE PROCEDURE NESTA (p1 int, p2 char(10), OUT p3 INT)
BEGIN
 SQL Statements
END

Now, call this procedure from another one:

Listing 8. Calling procedure

DECLARE v_v1 varchar(10);
DECLARE v_res INT default 0;

--- SQL statements and variable assignments

CALL nesta(10, v_v1, v_res);

In summary, here are the rules for passing parameters to stored procedure:

• Variables and parameters are strongly typed (they must match)
• Local variables are matched to the stored procedure by their position
• All parameters must have a value
• Overloaded procedures are determined by the number of parameters

Retrieving return codes

In nested stored procedures, the RETURN statement is used to immediately terminate an SQL procedure by returning the flow of control to the caller of the stored procedure. The RETURN statement can also pass an INTEGER value back to the calling stored procedure.
If no value has been provided, the default value is 0.

DB2 supports the GET DIAGNOSTIC statement to obtain information about a previously executed CALL statement as the listing below illustrates.

GET DIAGNOSTICS ret_code = DB2_RETURN_STATUS;

The following examples illustrate how to use RETURN and the GET DIAGNOSTICS statements.

For example, suppose you have two different stored procedures:

Listing 9. Different stored procedures

CREATE PROCEDURE TEST1(out v1 int)
begin
  set v1 = 10;
  return;  
end

CREATE PROCEDURE TEST2(out v1 int)
begin
  set v1 = 5;
  return 2;  
end

Now, call those stored procedures and check the respective DB2_RETURN_STATUS:

Listing 10. Calling stored procedures andchecking DB2_RETURN_STATUS

CREATE PROCEDURE NEST_DIAGN (out ret_code1 int, out ret_code2 int  )
P1: BEGIN
	DECLARE val1 INT default 0;
	call test2(val1);
	GET DIAGNOSTICS ret_code1 = DB2_RETURN_STATUS;
	call test1(val1);
	GET DIAGNOSTICS ret_code2 = DB2_RETURN_STATUS;	
END P1

If you execute the NEST_DIAGN stored procedure from the DB2 command line, you get the following results:

Listing 11. Result of executing the NEST_DIAGNstored procedure from the DB2 command line

C:\Program Files\IBM\SQLLIB\BIN>db2 call nest_diagn(?,?)

  Value of output parameters
  --------------------------
  Parameter Name  : RET_CODE1
  Parameter Value : 2

  Parameter Name  : RET_CODE2
  Parameter Value : 0

  Return Status = 0

Please note that you need to DECLARE a variable that will be accepting the value from DB2_RETURN_STATUS.

Sharing data between stored procedures

The previous examples show how stored procedures can share data using parameters and the RETURN statement. Now, let's examine how 2 (or more) stored procedures can share the same result set from a cursor.

Procedure result_from_cursor gets a name, job description, commission and location for each worker from the STAFF and ORG tables for a particular department:

Listing 12. Example of procedure to return result set

CREATE PROCEDURE result_from_cursor (deptin int)
	DYNAMIC RESULT SETS 1
P1: BEGIN
	-- Declare cursor
	DECLARE cursor1 CURSOR WITH RETURN FOR
		SELECT a.name, a.job, COALESCE(a.comm,0), b.location
		 FROM staff a, org b
		 where a.dept = b.deptnumb
		  AND  a.dept = deptin; 

	OPEN cursor1;
END P1

For example, for department 51, you get the following result set:

Listing 13. Result set for department 51

  NAME      JOB   COMMISSION      LOCATION      
  --------- ----- --------------- ------------- 
  Fraye     Mgr              0.00 Dallas       
  Williams  Sales          637.65 Dallas         
  Smith     Sales          992.80 Dallas         
  Lundquist Clerk          189.65 Dallas         
  Wheeler   Clerk          513.30 Dallas

Now you want to use the result set from this stored procedure (without storing it in temporary or permanent table) in another stored procedure.

DB2 permits an outer stored procedure to use a result set from an inner one. Here is what you need to do:

• Declare a result set locator using the following syntax:
  • DECLARE rs_locator_var1 RESULT_SET_LOCATOR VARYING;
• Associate this result set locator with the calling procedure:
  • ASSOCIATE RESULT SET LOCATOR( rs_locator_var1) WITH PROCEDURE proc_called;
• Allocate the cursor that points to the result set from the calling procedure:
  • ALLOCATE cursor1 CURSOR FOR RESULT SET rs_locator_var1;

The following example demonstrates all those methods:

Listing 14. Using result set from nested procedure

CREATE PROCEDURE Use_nested_cursor (deptin int, OUT tot_dept_comm DEC(12,2))
  BEGIN
   
    DECLARE sqlcode int default 0;
    DECLARE v_comm DECIMAL(12,2) DEFAULT 0.0;
    DECLARE v_name, v_location varchar(20);
    DECLARE v_job char(6);
    
    DECLARE LOC1 RESULT_SET_LOCATOR VARYING;  
    
    SET tot_dept_comm = 0;  
    CALL result_from_cursor(deptin);

    ASSOCIATE RESULT SET LOCATOR( LOC1) WITH PROCEDURE result_from_cursor; 
    ALLOCATE C1 CURSOR FOR RESULT SET LOC1;

    FETCH FROM C1 INTO v_name,v_job,v_comm,v_location;
    WHILE sqlcode = 0 DO
            SET tot_dept_comm = tot_dept_comm + v_comm;            
      FETCH FROM C1 INTO v_name,v_job,v_comm,v_location;
    END WHILE;
 END

Now, if you execute this stored procedure with department "51" as the input parameter, you get the total department commission:

Listing 15. Results when department 51 is aninput parameter

> call use_nested_cursor (51,?)

Value of output parameters 
-------------------------- 
  Parameter Name: TOT_DEPT_COMM 
  Parameter Value: 2333.40

Sharing data with global variables

DB2 supports session global variables. A session global variable is associated with a specific session, is global for each stored procedure in that session, and contains a value that is unique to that session.

The following diagram shows the syntax for the session global variable:

Listing 16. CREATE VARIABLE syntax

CREATE VARIABLE var_name DATATYPE [DEAFULT value];

Please note that the session global variable is declared outside of a stored procedure, just like any other database object.

The following script demonstrates the use of global variables:

Listing 17. Use of global variables

CREATE VARIABLE global_var_count INTEGER default 0;

CREATE PROCEDURE project_count (IN var_respemp CHAR(6))   
BEGIN 
  SELECT COUNT(*)
    INTO global_var_count
    FROM   project
    WHERE  respemp = var_respemp;
END

CREATE PROCEDURE PROJECT_STATUS (IN p_respemp CHAR(6),OUT p_new_status CHAR(20))
BEGIN
 CALL project_count(p_respemp);
 IF  global_var_count > 2
 THEN
      SET p_new_status = 'Maximum projects' ;    
 ELSE
      SET  p_new_status  = 'Available';
 END IF;
END

Back to top

Testing and deploying stored procedures

DB2 supports the command line processor (CLP), which is an interface to deploy (build) and test (execute) stored procedures.

The user building the stored procedure should satisfy the following requirements:

• Must have the privileges required to execute the CREATE PROCEDURE statement
• Must have the privileges to execute all SQL statements in the stored procedure
• All database objects (example, tables, views, functions, other procedures) that are referenced in this SQL procedure should exist in the database
• A successful database connection must be established from the CLP (this can be achieved by using the following db2 command :
  db2 connect to sample user userid using password )

Each statement in an SQL procedure requires a statement terminator. The default is semicolon (;). You need to select an alternative termination character to be able to create an SQL procedure in a script for the CLP to know where your SQL procedure is terminated.
Most commonly used termination characters are the "at" (@) and exclamation (!) characters.

So now write a script that contains a simple SQL procedure and put the "@" termination character at the end:

Listing 18. Simple SQL procedure with @termintor at end

CREATE PROCEDURE NUMBER_OF_ORDERS ( in_status varchar(10), in_date DATE,
                                   out num_of_order int)
	
------------------------------------------------------------------------
-- SQL Procedure 
------------------------------------------------------------------------
P1: BEGIN
	
	declare v_number INTEGER DEFAULT 0;
	
		SELECT count(poid)
		INTO v_number
		  FROM PURCHASEORDER
		  where ucase(status) = ucase(in_status)
		    and orderdate < in_date;
		  
    SET  num_of_order = v_number;		  

END P1 @

This script is saved as file myscript.db2. To build the stored procedure number_of_orders from the DB2 CLP, you need to execute the following command:

db2 -td@ -vf myscript.db2

The general syntax for this CLP command is as follows:

db2 -td <terminating-character> -vf <CLP-script-name>

Note that the option -td indicates that the CLP terminator default is to be reset with the corresponding terminating character. The
-vf option indicates that the CLP's optional verbose (-v) option is to be used, which causes each SQL statement or command in the script to be displayed on the screen as it is executed, along with any output that results from
its execution. The -f option indicates that the target of the command is a file.

Now it's time to execute (or test) this stored procedure from the CLP. To do that you need to run the following command:

db2 call number_of_orders('Shipped',current date, ?)

Figure 3. Testing procedure from CLP

Please note that you need to put a value for each input parameter and place a "?" (question mark) for each output parameter.

But how would you know that the return value of 5 is correct? As this stored procedure is simple and only has one SQL statement, you can execute this statement from the CLP by putting input parameters directly into the WHERE clause as Listing 19 illustrates:

Listing 19. SQL statement from procedure

SELECT count(poid)
 FROM PURCHASEORDER
  WHERE ucase(status) = 'SHIPPED'
   AND orderdate < CURRENT DATE;

Figure 4. Running this SQL Query from CLP

As you can see, you get the same result. With more complex stored procedure testing it could be a more time consuming task. You can use the IBM Data Studio tool to help you debug stored procedures.

Back to top

Securing SQL procedures

Authorizations

There are basically two types of authorization that needs to be considered for SQL procedures:

• The stored procedure definer is the user who actually creates the stored procedure.
• The stored procedure invoker is the user who invokes or calls the stored procedure.

In order to create an SQL procedure, the userid performing the task needs to have BINDADD authority on the database and either IMPLICIT_SCHEMA on the database (if the schema for the stored procedure does not already exist), or CREATE_IN on the schema (if
the schema for the stored procedure does already exist). They would also need to have all the necessary privileges to perform the SQL that is defined in the stored procedure body.

In order to invoke or call an SQL procedure, the userid performing the task needs to have EXECUTE privilege on the stored procedure.

The userid who created the SQL procedure implicitly gets EXECUTE privilege and the GRANT EXECUTE privilege. Either DBADM or SYSADM authority also allows a user to create or invoke an SQL procedure. It is generally recommended that a database administrator
(DBA) is the one who creates the stored procedure for an application developer who may need to be able to invoke it.

SQL access levels in SQL procedures

There are four SQL access levels used for SQL statements that can control what type of SQL statements the SQL procedure can define. They are used to provide information to the database manager so that the statement can be executed safely by the database
manager.

The SQL access levels that can be used are:

• NO SQL: no SQL statement can exist in the stored procedure
• CONTAINS SQL: no SQL statement can modify or read data in the stored procedure
• READS SQL: no SQL statement can modify data in the stored procedure
• MODIFIES SQL: SQL statements can both modify or read data in the stored procedure

The default setting for SQL procedures is MODIFIES SQL.

The SQL access level can also determine what kind of stored procedure can be invoked from within that stored procedure. A stored procedure cannot call another stored procedure that is set with a higher SQL data access level. As an example, a stored procedure
defined with CONTAINS SQL can invoke a stored procedure that is defined with either CONTAINS SQL or NO SQL. That same stored procedure cannot invoke another stored procedure that is defined with READS SQL DATA or MODIFIES SQL.

The SQL access level can be specified during the CREATE PROCEDURE statement.

Encrypting SQL procedures

Moving SQL procedures from one server to another server is a common task. For example, a vendor may want to package up their SQL procedure and send that package to their client. If the vendor wants to hide or encrypt the stored procedure contents from their
client, they can do that via the PUT ROUTINE and GET ROUTINE commands.

GET ROUTINE is a DB2 command that extracts an SQL procedure from the database and converts it into a SAR (SQL Archive) file, which can then be sent to the client.

Listing 20. GET ROUTINE command syntax

>>-GET ROUTINE--INTO--file_name--FROM--+----------+------------->
                                       '-SPECIFIC-'   

>----PROCEDURE----routine_name--+-----------+------------------><
                                '-HIDE BODY-'

The HIDE BODY clause on the GET ROUTINE command ensures that the body of the SQL procedure is not extracted, thus encrypting the stored procedure.

PUT ROUTINE is a DB2 command that creates the SQL procedure in the database, given the SAR file that was extracted via GET ROUTINE.

Listing 21. PUT ROUTINE command syntax

>>-PUT ROUTINE--FROM--file-name--------------------------------->

>--+-------------------------------------+---------------------><
   '-OWNER--new-owner--+---------------+-'   
                       '-USE REGISTERS-'

Back to top

Conclusion

This tutorial has introduced you to a number of ideas that you will see on the DB2 9.5 Database Developer Certification Exam (735). The material in this tutorial primarily covers the objectives in Section 2 of the test, which is entitled "SQL Procedures".

In this tutorial, you've learned about the DB2 stored procedure and how your application can benefit from using the SQL procedure. You've seen how to design your procedures using SQL PL and how to build and test them. This tutorial introduced the idea of
nested procedures and showd you how to invoke them and share data between the procedures. Learning how to use these stored procedures allows you to integrate complex business logic into your overall database application.