DB2 10.5 for Linux, UNIX, and Windows

Calling procedures from the command line processor (CLP)

You can call stored procedures by using the CALL statement from the command line processor interface. The stored procedures must be defined in the database system catalog tables.

Procedure

To call a stored procedure, first connect to the database:

   db2 connect to sample user userid using password

where userid and password are the user ID and password of the instance where the sample database is located.

To use the CALL statement, enter the stored procedure name plus any IN or INOUT parameter values and a place-holder ('?') for each OUT parameter values.

The parameters for a stored procedure are defined in the CREATE PROCEDURE statement for the stored procedure.

Example

SQL procedure examples

Example 1.

In the whiles.db2 file, the CREATE PROCEDURE statement for the DEPT_MEDIAN procedure signature is as follows:

   CREATE PROCEDURE DEPT_MEDIAN 
   (IN deptNumber SMALLINT, OUT medianSalary DOUBLE)

The DEPT_MEDIAN procedure selects the STAFF table for the specified deptNumber value. You can call the DEPT_MEDIAN procedure with the following CALL statement:

   db2 call dept_median (51, ?)

On Linux and UNIX operating systems, the parentheses have special meaning to the command shell, so they must be preceded with a "\" character or surrounded with quotation marks, as follows:

   db2 "call dept_median (51, ?)"

You do not use quotation marks if you are using the interactive mode of the command line processor. The following results are returned from the DEPT_MEDIAN procedure:

   Value of output parameters
   --------------------------
   Parameter Name  : MEDIANSALARY
   Parameter Value : +1.76545000000000E+004 

   Return Status = 0

Example 2.

The example 2 illustrates how to call a procedure with array parameters. The user-defined data type phonenumbers is defined as follows:

CREATE TYPE phonenumbers AS VARCHAR(12) ARRAY[1000]

In the following example, the find_customers procedure contains parameters of type phonenumbers. The find_customers procedure searches for the area_code value in the numbers_in parameter and reports them in the numbers_out parameter.

CREATE PROCEDURE find_customers(
IN numbers_in phonenumbers,
IN area_code CHAR(3),
OUT numbers_out phonenumbers)
BEGIN
DECLARE i, j, max INTEGER;

SET i = 1;
SET j = 1;
SET numbers_out = NULL;
SET max = CARDINALITY(numbers_in);

WHILE i <= max DO
IF substr(numbers_in[i], 1, 3) = area_code THEN
SET numbers_out[j] = numbers_in[i];
SET j = j + 1;
END IF;
SET i = i + 1;
END WHILE;
END

To call the find_customers procedure, you can use the following CALL statement:

db2 CALL find_customers(ARRAY['416-305-3745',
                              '905-414-4565',     
                              '416-305-3746'],
                              '416',
                               ?)

As shown in the CALL statement example, when a procedure has an input parameter of an array data type, the input argument can be specified with an array constructor that contains a list of literal values.

The following results are returned from the find_customers procedure:

Value of output parameters
  --------------------------
  Parameter Name  : OUT_PHONENUMBERS
  Parameter Value : ['416-305-3745',
                     '416-305-3746']

  Return Status = 0

C stored procedure example

You can also call stored procedures that are created from supported host languages with the Command Line Processor. In the c subdirectory under the sample directory contains files that contain sample stored procedures. The spserver shared library contains a number of stored procedures that can be created from the spserver.sqc file. The spcreate.db2 file registers the stored procedures.

In the spcreate.db2 file, the following CREATE PROCEDURE statement for the MAIN_EXAMPLE procedure can be found:

CREATE PROCEDURE MAIN_EXAMPLE (IN job CHAR(8), 
                               OUT salary DOUBLE, 
                               OUT errorcode INTEGER)

The MAIN_EXAMPLE procedure selects the job value from the EMPLOYEE table. The C sample program, spclient, that calls the stored procedure, uses 'DESIGNER' for the JOB value. :

   db2 "call MAIN_EXAMPLE ('DESIGNER', ?, ?)"

The following results are returned from the MAIN_EXAMPLE procedure:

   Value of output parameters
   --------------------------
   Parameter Name  : SALARY
   Parameter Value : +2.37312500000000E+004 

   Parameter Name  : ERRORCODE
   Parameter Value : 0

   Return Status = 0

An ERRORCODE of zero indicates a successful completion.