Parameter Conventions

When an application executes the CALL statement, the DB2 LUW Server builds a parameter list for the stored procedure, using the parameters and values provided in the statement. The DB2 LUW Server obtains information about parameters from the system tables. See the section Defining Stored Procedures under DB2 LUW for more information. Parameters are defined as one of these types:


If a stored procedure fails to set one or more of the output-only parameters, the DB2 LUW Server simply returns the output parameters to the calling program, with the values established on entry to the stored procedure. COBOL supports three parameter list conventions. Other languages support other conventions. The parameter list convention is chosen based on the parameter style defined in the CREATE PROCEDURE statement.

Parameter Style Description
SIMPLE Use SIMPLE (or GENERAL) to prevent the calling program passing null values for input parameters (IN or INOUT) to the stored procedure. The stored procedure must declare a variable for each parameter passed in the CALL statement.
SIMPLE WITH NULLS Use SIMPLE WITH NULLS (or GENERAL WITH NULLS) to allow the calling program to supply a null value for any parameter passed to the stored procedure. The following rules apply:
  • An indicator variable must follow each parameter in the calling program's CALL statement, using one of the following two forms:
    • host variable :indicator variable

      or:

    • host variable INDICATOR :indicator variable
  • The stored procedure must declare a variable for each parameter passed in the CALL statement.
  • The stored procedure must declare a null indicator structure containing an indicator variable for each parameter passed in the CALL statement.
  • On entry, the stored procedure must examine all indicator variables associated with input parameters to determine which parameters contain null values.
  • On exit, the stored procedure must assign values to all indicator variables associated with output variables. An indicator variable for an output variable that returns a null value to the caller must be assigned a negative number. Otherwise, the indicator variable must be assigned the value zero (0).
DB2SQL In addition to the parameters on the CALL statement, the following arguments are passed to the stored procedure:
  • a NULL indicator for each input parameter on the CALL statement.
  • the SQLSTATE to be returned to DB2.
  • the qualified name of the stored procedure.
  • the specific name of the stored procedure.
  • the SQL diagnostic string to be returned to DB2.
Parent topic: Writing and Preparing Applications to Use Stored Procedures