Parameters in .NET CLR routines

Parameter declaration in .NET CLR routines must conform to the requirements of one of the supported parameter styles, and must respect the parameter keyword requirements of the particular .NET language used for the routine.

Attention: With the release of Db2® 11.5.9, support for Microsoft .Net common language runtime (CLR) routines is deprecated and might be removed in the future. If you are currently running routines that have a dependency on .NET CLR, rewrite the routine logic in a supported language and then recreate the routines.

If the routine is to use a scratchpad, the dbinfo structure, or to have a PROGRAM TYPE MAIN parameter interface, there are additional details to consider. This topic addresses all CLR parameter considerations.

Supported parameter styles for CLR routines

The parameter style of the routine must be specified at routine creation time in the EXTERNAL clause of the CREATE statement for the routine. The parameter style must be accurately reflected in the implementation of the external CLR routine code. The following parameter styles are supported for CLR routines:
  • SQL (Supported for procedures and functions)
  • GENERAL (Supported for procedures only)
  • GENERAL WITH NULLS (Supported for procedures only)
  • DB2SQL (Supported for procedures and functions)
For more information about these parameter styles see:

CLR routine parameter null indicators

If the parameter style chosen for a CLR routine requires that null indicators be specified for the parameters, the null indicators are to be passed into the CLR routine as System.Int16 type values, or in a System.Int16[] value when the parameter style calls for a vector of null indicators.

When the parameter style dictates that the null indicators be passed into the routine as distinct parameters, as is required for parameter style SQL, one System.Int16 null indicator is required for each parameter.

In .NET languages distinct parameters must be prefaced with a keyword to indicate if the parameter is passed by value or by reference. The same keyword that is used for a routine parameter must be used for the associated null indicator parameter. The keywords used to indicate whether an argument is passed by value or by reference are discussed in more detail in the following section.

For more information about parameter style SQL and other supported parameter styles, see:

Passing CLR routine parameters by value or by reference

.NET language routines that compile into intermediate language (IL) byte-code require that parameters be prefaced with keywords that indicate the particular properties of the parameter such as whether the parameter is passed by value, by reference, is an input only, or an output only parameter.

Parameter keywords are .NET language specific. For example to pass a parameter by reference in C#, the parameter keyword is ref, whereas in Visual Basic, a by reference parameter is indicated by the byRef keyword. The keywords must be used to indicate the SQL parameter usage (IN, OUT, INOUT) that was specified in the CREATE statement for the routine.

The following rules apply when applying parameter keywords to .NET language routine parameters:
  • IN type parameters must be declared without a parameter keyword in C#, and must be declared with the byVal keyword in Visual Basic.
  • INOUT type parameters must be declared with the language specific keyword that indicates that the parameter is passed by reference. In C# the appropriate keyword is ref. In Visual Basic, the appropriate keyword is byRef.
  • OUT type parameters must be declared with the language specific keyword that indicates that the parameter is an output only parameter. In C#, use the out keyword. In Visual Basic, the parameter must be declared with the byRef keyword. Output only parameters must always be assigned a value before the routine returns to the caller. If the routine does not assign a value to an output only parameter, an error will be raised when the .NET routine is compiled.

Here is what a C#, parameter style SQL procedure prototype looks like for a routine that returns a single output parameter language.

   public static void Counter  (out String language,
                                out Int16  languageNullInd,
                                ref String sqlState,
                                    String funcName,
                                    String funcSpecName,
                                ref String sqlMsgString,
                                    Byte[] scratchPad,
                                    Int32  callType);
It is clear that the parameter style SQL is implemented because of the extra null indicator parameter, languageNullInd associated with the output parameter language, the parameters for passing the SQLSTATE, the routine name, the routine specific name, and optional user-defined SQL error message. Parameter keywords have been specified for the parameters as follows:
  • In C# no parameter keyword is required for input only parameters.
  • In C# the 'out' keyword indicates that the variable is an output parameter only, and that its value has not been initialized by the caller.
  • In C# the 'ref' keyword indicates that the parameter was initialized by the caller, and that the routine can optionally modify this value.

See the .NET language specific documentation regarding parameter passing to learn about the parameter keywords in that language.

Note: The database manager controls allocation of memory for all parameters and maintains CLR references to all parameters passed into or out of a routine.

No parameter marker is required for procedure result sets

No parameter markers is required in the procedure declaration of a procedure for a result set that will be returned to the caller. Any cursor statement that is not closed from inside of a CLR stored procedure will be passed back to its caller as a result set.

For more on result sets in CLR routines, see:

Dbinfo structure as CLR parameter

The dbinfo structure used for passing additional database information parameters to and from a routine is supported for CLR routines through the use of an IL dbinfo class. This class contains all of the elements found in the C language sqludf_dbinfo structure except for the length fields associated with the strings. The length of each string can be found using the .NET language Length property of the particular string.

To access the dbinfo class, simply include the IBM.Data.DB2 assembly in the file that contains your routine, and add a parameter of type sqludf_dbinfo to your routine's signature, in the position specified by the parameter style used.

UDF scratchpad as CLR parameter

If a scratchpad is requested for a user defined function, it is passed into the routine as a System.Byte[] parameter of the specified size.

CLR UDF call type or final call parameter

For user-defined functions that have requested a final call parameter or for table functions, the call type parameter is passed into the routine as a System.Int32 data type.

PROGRAM TYPE MAIN supported for CLR procedures

Program type MAIN is supported for .NET CLR procedures. Procedures defined as using Program Type MAIN must have the following signature:

	void functionname(Int32 NumParams, Object[] Params)