External routine parameter styles

External routine implementations must conform to a particular convention for the exchange of routine parameter values. These conventions are known as parameter styles.

An external routine parameter style is specified when the routine is created by specifying the PARAMETER STYLE clause. Parameter styles characterize the specification and order in which parameter values are passed to the external routine implementation. They also specify what if any additional values are passed to the external routine implementation. For example, some parameter styles require that an additional separate null-indicator value is specified for each routine parameter value to provide information about the nullability. The null-indicator value provides information about the nullability of a parameter that is not easily determined with the native programming language data type.

The following table provides a list of the available parameter styles, the routine implementations that support each parameter style, the functional routine types that support each parameter style, and a description of the parameter style:

Table 1. Parameter styles
Parameter style	Supported language	Supported routine type	Description
SQL ¹	C/C++ OLE COBOL ²	UDFs stored procedures methods	In addition to the parameters passed during invocation, the following arguments are passed to the routine in the following order: A null indicator for each parameter or result that is declared in the CREATE statement. The SQLSTATE to be returned to the database manager. The qualified name of the routine. The specific name of the routine. The SQL diagnostic string to be returned to the database manager. Depending on options that are specified in the CREATE statement and the routine type, the following arguments can be passed to the routine in the following order: A buffer for the scratchpad. The call type of the routine. The `dbinfo` structure (contains information about the database).
DB2SQL ¹	C/C++ OLE COBOL	stored procedures	In addition to the parameters passed during invocation, the following arguments are passed to the stored procedure in the following order: A vector that contains a null indicator for each parameter on the CALL statement. The SQLSTATE to be returned to the database manager. The qualified name of the stored procedure. The specific name of the stored procedure. The SQL diagnostic string to be returned to the database manager. If the DBINFO clause is specified in the CREATE PROCEDURE statement, a `dbinfo` structure (it contains information about the database) is passed to the stored procedure.
JAVA	Java™	UDFs stored procedures	PARAMETER STYLE JAVA routines use a parameter passing convention that conforms to the Java language and SQLJ Routines specification. For stored procedures, INOUT and OUT parameters are passed as single entry arrays to facilitate the returning of values. In addition to the IN, OUT, and INOUT parameters, Java method signatures for stored procedures include a parameter of type ResultSet[] for each result set specified in the DYNAMIC RESULT SETS clause of the CREATE PROCEDURE statement. For PARAMETER STYLE JAVA UDFs and methods, no additional arguments to those specified in the routine invocation are passed. PARAMETER STYLE JAVA routines do not support the DBINFO or PROGRAM TYPE clauses. For UDFs, PARAMETER STYLE JAVA can be specified only when no structured data types are specified as parameters. Also, you cannot specify the following data types for return types of PARAMETER STYLE JAVA UDFs: Structured type CLOB DBCLOB BLOB Also, PARAMETER STYLE JAVA UDFs do not support table functions, call types, or scratchpads.
DB2GENERAL	Java	UDFs stored procedures methods	This type of routine uses a parameter passing convention that is defined for use with Java methods. Unless you are developing table UDFs, UDFs with scratchpads, or need access to the `dbinfo` structure, use PARAMETER STYLE JAVA. For PARAMETER STYLE DB2GENERAL routines, no additional arguments to those specified in the routine invocation are passed.
GENERAL	C/C++ COBOL	stored procedures	A PARAMETER STYLE GENERAL stored procedure receives parameters from the CALL statement in the invoking application or routine. If the DBINFO clause is specified in the CREATE PROCEDURE statement, a `dbinfo` structure (it contains information about the database) is passed to the stored procedure.
GENERAL WITH NULLS	C/C++ COBOL	stored procedures	A PARAMETER STYLE GENERAL WITH NULLS stored procedure receives parameters from the CALL statement in the invoking application or routine. Also included is a vector that contains a null indicator for each parameter on the CALL statement. If the DBINFO clause is specified in the CREATE PROCEDURE statement, a `dbinfo` structure (it contains information about the database) is passed to the stored procedure.
HIVE	JAVA	UDFs	This parameter style uses a parameter passing convention that is defined by Apache Hive. It is the recommended parameter style in Big SQL. PARAMETER STYLE HIVE functions are the only functions that can be pushed down to the Big SQL Input/Output layer. Performance is improved by running functions in the Big SQL I/O layer, because the logic is run as close to the data as possible. For PARAMETER STYLE HIVE, no additional arguments to those specified in the routine invocation are passed. PARAMETER STYLE HIVE routines do not support the DBINFO, PROGRAM TYPE, SCRATCHPAD, FINAL CALL, EXTERNAL ACTION, NOT DETERMINISTIC, DISALLOW PARALLEL, READS SQL DATA, or CONTAINS SQL clauses. Also, PARAMETER STYLE HIVE is not supported for table functions.

Note:

For UDFs and methods, PARAMETER STYLE SQL is equivalent to PARAMETER STYLE DB2SQL.
COBOL can be used only to develop stored procedures.