Linkage conventions for external stored procedures

The linkage convention for a stored procedure can be either GENERAL, GENERAL WITH NULLS, or SQL. These linkage conventions apply to only external stored procedures.

GENERAL
Specify the GENERAL linkage convention when you do not want the calling program to pass null values for input parameters (IN or INOUT) to the stored procedure. If you specify GENERAL, ensure that the stored procedure contains a variable declaration for each parameter that is passed in the CALL statement.

The following figure shows the structure of the parameter list for PARAMETER STYLE GENERAL.

Figure 1. Parameter convention GENERAL for a stored procedure
Begin figure summary.This figure depicts the relationship between register 1 and the parameter addresses and data. Detailed description available.
GENERAL WITH NULLS
Specify the GENERAL WITH NULLS linkage convention when you want to allow the calling program to supply a null value for any parameter that is passed to the stored procedure. If you specify GENERAL WITH NULLS, ensure that the stored procedure performs the following tasks:
  • Declares a variable for each parameter that is passed in the CALL statement.
  • Declares a null indicator structure that contains an indicator variable for each parameter.
  • On entry, examines all indicator variables that are associated with input parameters to determine which parameters contain null values.
  • On exit, assigns values to all indicator variables that are associated with output variables. If the output variable returns a null value to the caller, assign the associated indicator variable a negative number. Otherwise, assign a value of 0 to the indicator variable.

In the CALL statement in the calling application, follow each parameter with its indicator variable. Use one of the following forms:

  • host-variable :indicator-variable
  • host-variable INDICATOR :indicator-variable

The following figure shows the structure of the parameter list for PARAMETER STYLE GENERAL WITH NULLS.

Figure 2. Parameter convention GENERAL WITH NULLS for a stored procedure
Begin figure summary.The figure shows the relationship between register 1, parameters, an indicator array, and data.Detailed description available.
SQL
Specify the SQL linkage convention when you want both of the following conditions:
  • The calling program to be able to supply a null value for any parameter that is passed to the stored procedure.
  • Db2 to pass input and output parameters to the stored procedure that contain the following information:
    • The SQLSTATE that is to be returned to Db2. This value is a CHAR(5) parameter that represents the SQLSTATE that is passed into the program from the database manager. The initial value is set to ‘00000'. Although the SQLSTATE is usually not set by the program, it can be set as the result SQLSTATE that is used to return an error or a warning. Returned values that start with anything other than ‘00', ‘01', or ‘02' are error conditions.
    • The qualified name of the stored procedure. This is a VARCHAR(128) value.
    • The specific name of the stored procedure. The specific name is a VARCHAR(128) value that is the same as the unqualified name.
    • The SQL diagnostic string that is to be returned to Db2. This is a VARCHAR(1000) value. Use this area to pass descriptive information about an error or warning to the caller.
Restriction: You cannot use the SQL linkage convention for a REXX language stored procedure.

The following figure shows the structure of the parameter list for PARAMETER STYLE SQL.

Figure 3. Parameter convention SQL for a stored procedure
Begin figure summary.Several boxes depict the relationship between register 1, addresses of various objects and data. Detailed description available.