Using an application program as a stored procedure

A stored procedure is a compiled program, stored at a DB2 local or remote server, that can execute SQL statements. A typical stored procedure contains two or more SQL statements and some manipulative or logical processing in a program. A client application program uses the SQL CALL statement to invoke the stored procedure.

Consider using stored procedures for a client/server application that does at least one of the following things:

• Executes multiple remote SQL statements.

  Remote SQL statements can result in many network send and receive operations, which increases processor costs.

  Stored procedures can encapsulate many of an application's SQL statements into a single message to the DB2 server, reducing network traffic to a single send and receive operation for a series of SQL statements.

  Locks on DB2 tables are not held across network transmissions, thereby reducing contention for resources at the server.

• Accesses tables from a dynamic SQL environment in which table privileges for the application that is running are undesirable.

  Stored procedures allow static SQL authorization from a dynamic environment.

• Accesses host variables for which you want to guarantee security and integrity.

  Stored procedures remove SQL applications from the workstation, preventing workstation users from manipulating the contents of sensitive SQL statements and host variables.

• Creates a result set of rows to return to the client application.

Choosing a language for creating stored procedures

You can write stored procedures in the following programming languages:

Java

If you have more experience writing applications in an object-oriented programming environment, you might want to create stored procedures by using Java. “Using Java to execute static and dynamic SQL” on page 198 has information about the Java programming language.

SQL procedural language

If your application consists entirely of SQL statements, some simple control flow logic, and no complex application logic, you might choose to create your stored procedures by using the SQL procedural language. “Using SQL procedural language to create a stored procedure” on page 206 has information about SQL procedures.

REXX

You can create stored procedures by using REXX programs that can contain dynamic SQL. DBAs and programmers generally use REXX for administrative tasks.

Traditional programming languages: C, C++, COBOL, PL/I, and Assembler

All traditional language programs must be designed to run using Language Environment^®. COBOL and C++ stored procedures can contain object-oriented extensions.

The program that calls the stored procedure can be in any language that supports the SQL CALL statement. ODBC and JDBC applications can use an escape clause to pass a stored procedure call to DB2.

Running stored procedures

This section explains how stored procedure processing works, provides examples of stored procedures, and shows you how to run stored procedures.

Figure 6.2 illustrates processing without stored procedures.

Figure 6.3 illustrates processing with stored procedures.

Figure 6.3. Processing with stored procedures. The same series of SQL statements uses a single send or receive operation.

[View full size image]

Notes to Figure 6.3:

• The workstation application uses the SQL CONNECT statement to create a conversation with DB2.

• DB2 creates a DB2 thread to process SQL requests. A thread is the DB2 structure that describes an application's connection and traces its progress.

• The SQL statement CALL tells the DB2 server that the application is going to run a stored procedure. The calling application provides the necessary arguments.

• DB2 processes information about the request and loads the stored procedure program.

• The stored procedure executes SQL statements.

  One of the SQL statements opens a cursor that has been declared by using WITH RETURN. This causes a result set to be returned to the workstation application.

• The stored procedure assigns values to the output parameters and exits. Control returns to the DB2 stored procedures region and goes from there to the DB2 subsystem.

• Control returns to the calling application, which receives the output parameters and the result set. DB2 then:

  • Closes all cursors that the stored procedure opened, except those that are defined to return result sets.

  • Discards all SQL statements that the stored procedure prepared.

The application can call other stored procedures, or it can execute additional SQL statements. DB2 receives and processes the COMMIT or ROLLBACK request. The commit or rollback operation covers all SQL operations that the application or the stored procedure executes during the unit of work.

If the application involves IMS or CICS, similar processing occurs. This processing is based on the IMS or CICS synchronization model, rather than on an SQL COMMIT or ROLLBACK statement.

Using SQL procedural language to create a stored procedure

With SQL procedural language, you can write stored procedures that consist entirely of SQL statements. An SQL procedure can include declarations of variables, conditions, cursors, and handlers. The SQL procedure can also include flow control, assignment statements, and traditional SQL for defining and manipulating relational data. These extensions provide a procedural language for writing stored procedures, and they are consistent with the Persistent Stored Modules portion of the SQL standard.

Example: This example shows a simple SQL stored procedure:

CREATE PROCEDURE ITERATOR()  LANGUAGE SQL
BEGIN
  ..
  DECLARE not_found CONDITION FOR SQLSTATE '02000';
  DECLARE c1 CURSOR FOR ....;
  DECLARE CONTINUE HANDLER FOR not_found                 (2)
      SET at_end = 1;
  OPEN c1;
  ftch_loop1: LOOP
      FETCH c1 INTO v_dept, v_deptname, v_admdept;       (1)
      IF at_end = 1 THEN
           LEAVE ftch_loop1;                             (3)
      ELSEIF v_dept = 'D01' THEN
      INSERT INTO department (deptno, deptname, admrdept)
            VALUES ( 'NEW', v_deptname, v_admdept);
      END IF;
  END LOOP;
  CLOSE c1;
END

In this example:

• Processing goes through ftch_loop1, assuming that a row is found.

• The first time that the FETCH does not find a row, processing goes to the HANDLER (1).

• The HANDLER sets the at_end flag. Because the procedure uses a CONTINUE HANDLER, processing continues at the next step after the FETCH (2).

• Processing continues with the CLOSE SQL statement (3).

(The syntax for the CREATE PROCEDURE statement in the preceding example shows only a portion of the statement clauses.)

Using the DB2 Development Center

Introduced in Version 8 of DB2 UDB for z/OS, the DB2 Development Center extends the capabilities of its predecessor, the DB2 Stored Procedure Builder. The DB2 Development Center helps application developers create stored procedures in the Java programming language and the SQL procedural language. These stored procedures are portable across the entire family of DB2 servers including DB2 UDB for z/OS, DB2 UDB for iSeries, and DB2 UDB for Linux, UNIX, and Windows.

Without the DB2 Development Center, the process of installing a stored procedure on a server, whether local or remote, requires manual steps that can be error prone. In contrast, the DB2 Development Center generates, with a simple click of the Build icon, the steps for the required operating system.

When a DB2 subsystem is set up for creating SQL and Java stored procedures, application developers can easily create, install, and test stored procedures for DB2 UDB for z/OS by using the DB2 Development Center. The DB2 Development Center also provides similar steps for building and installing DB2 Java stored procedures on distributed operating systems.

Through a fully integrated set of Development Add-Ins for Microsoft Visual Studio 6.0 (for Visual Basic, Visual C ++ , Visual InterDev, and Visual Studio .NET), DB2 Development Center also supports rapid iterative development of server-side stored procedures and client-side ADO code generation and integration with Visual Source Safe.

In addition to stored procedure development, the DB2 Development Center supports read-only access to user-defined functions, triggers, tables, and views.

Setting up the stored procedure environment

Setting up the stored procedure environment requires tasks that include establishing the stored procedure environment and defining your stored procedure to DB2. Typically, a system administrator customizes the environment, and an application programmer defines the stored procedure.

Before a stored procedure can run, you must define it to DB2. Use the SQL CREATE PROCEDURE statement to define a stored procedure to DB2. To alter the definition, use the ALTER PROCEDURE statement.

Preparing a stored procedure

A stored procedure can consist of more than one program, each with its own package. A stored procedure can call other programs, stored procedures, or user-defined functions. Use the facilities of your programming language to call other programs.

If the stored procedure calls other programs that contain SQL statements, each of those called programs must have a DB2 package. The owner of the package or plan that contains the CALL statement must have EXECUTE authority for all packages that the other programs use.

When a stored procedure calls another program, DB2 determines the collection to which the called program's package belongs.

Writing and preparing an application to call stored procedures

Use the SQL statement CALL to call a stored procedure and to pass a list of arguments to the procedure.

An application program can call a stored procedure in the following ways:

• Call more than one stored procedure.
  
  
• Execute the CALL statement locally or send the CALL statement to a server. The application executes a CONNECT statement to connect to the server. The application then executes the CALL statement, or it uses a three-part name to identify and implicitly connect to the server where the stored procedure is located. You can read about the CONNECT statement and three-part names in “Programming techniques for accessing remote servers” on page 377.
  
  
• After connecting to a server, mix CALL statements with other SQL statements. To execute the CALL statement, you can either execute the CALL statement statically or use an escape clause in an ODBC or JDBC application to pass the CALL statement to DB2.
  
  
• Use any of the DB2 attachment facilities.
  
  

Executing a stored procedure involves two types of authorization:

• Authorization to execute the stored procedure
  
  
• Authorization to execute the stored procedure package and any packages that are under the stored procedure package
  
  

If the owner of the stored procedure has authority to execute the packages, the person who executes the packages does not need the authority.

The authorizations that you need depend on whether the name of the stored procedure is explicitly specified on the CALL statement or is contained in a host variable.

If the stored procedure invokes user-defined functions or triggers, you need additional authorizations to execute the user-defined function, the trigger, and the user-defined function packages.