Errors related to .NET CLR routines

Although external routines share a generally common implementation, there are some database errors, which might arise that are specific to common language runtime (CLR) routines.

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.
The following list contains of most commonly encountered SQLCODEs by .NET CLR routines. Database errors that are related to routines can be classified as follows:
Routine creation time errors
Errors that arise when the CREATE statement for the routine is executed.
Routine runtime errors
Errors that arise during the routine invocation or execution.

The error message details the cause of the error and the action that the user can take to resolve the error. Additional routine error scenario information can be found in the database diagnostic log file (db2diag.log).

CLR routine creation time errors

SQLCODE -451, SQLSTATE 42815
The -451 error is raised when the CREATE TYPE statement. which includes an external method that specifies the LANGUAGE clause with the CLR value is issued. You cannot create external methods for structured types that reference a CLR assembly at this time. Change the LANGUAGE clause so that it specifies a supported language for the method and implement the method in that alternate language.
SQLCODE -449, SQLSTATE 42878
The CREATE statement for the CLR routine contains an invalidly formatted library or function identification in the EXTERNAL NAME clause. For language CLR, the EXTERNAL clause value must specifically take the form: '<a>:<b>!<c>' as follows:
  • <a> is the CLR assembly file in which the class is located.
  • <b> is the class in which the method to invoke resides.
  • <c> is the method to invoke.
No leading or trailing blank characters are permitted between the single quotation marks, object identifiers, and the separating characters (for example, ' <a> ! <b> ' is invalid). Path and file names, however, can contain blanks if the platform permits. For all file names, the file can be specified using either the short form of the name (example: math.dll) or the fully qualified path name (example: d:\udfs\math.dll). If you use the short form file name for CLR routines on Linux® or UNIX operating systems, the file must reside in the database function directory. If you use the short form file name for CLR routines on Windows operating systems, the file must reside in the system PATH environment variable. File extensions (examples: .a (on Linux and UNIX), .dll (on Windows)) must always be included in the file name.

CLR routine runtime errors

SQLCODE -20282, SQLSTATE 42724, reason code 1
The external assembly specified by the EXTERNAL clause in the CREATE statement for the routine was not found.
  • Check that the EXTERNAL clause specifies the correct routine assembly name and that the assembly is located in the specified location. If the EXTERNAL clause does not specify a fully qualified path name to the desired assembly, the database manager presumes that the path name provided is a relative path name to the assembly, relative to the database function directory.
SQLCODE -20282, SQLSTATE 42724, reason code 2
An assembly was found in the location specified by the EXTERNAL clause in the CREATE statement for the routine, but no class was found within the assembly to match the class specified in the EXTERNAL clause.
  • Check that the assembly name specified in the EXTERNAL clause is the correct assembly for the routine and that it exists in the specified location.
  • Check that the class name specified in the EXTERNAL clause is the correct class name and that it exists in the specified assembly.
SQLCODE -20282, SQLSTATE 42724, reason code 3
An assembly was found in the location specified by the EXTERNAL clause in the CREATE statement for the routine, that had a correctly matching class definition, but the routine method signature does not match the routine signature specified in the CREATE statement for the routine.
  • Check that the assembly name specified in the EXTERNAL clause is the correct assembly for the routine and that it exists in the specified location.
  • Check that the class name specified in the EXTERNAL clause is the correct class name and that it exists in the specified assembly.
  • Check that the parameter style implementation matches the parameter style specified in the CREATE statement for the routine.
  • Check that the order of the parameter implementation matches the parameter declaration order in the CREATE statement for the routine and that it respects the extra parameter requirements for the parameter style.
  • Check that the SQL parameter data types are correctly mapped to CLR .NET supported data types.
SQLCODE -4301, SQLSTATE 58004, reason code 5 or 6
An error occurred while attempting to start or communicate with a .NET interpreter. The database manager was unable to load a dependent .NET library [reason code 5] or a call to the .NET interpreter failed [reason code 6].
  • Ensure that the database instance is configured correctly to run a .NET procedure or function (mscoree.dll must be present in the system PATH). Ensure that db2clr.dll is present in the sqllib/bin directory, and that IBM.Data.DB2 is installed in the global assembly cache. If these are not present, ensure that the supported .NET Framework version is installed on the database server.
SQLCODE -4302, SQLSTATE 38501

An unhandled exception occurred while executing, preparing to execute, or subsequent to executing the routine. This could be the result of a routine logic programming error that was unhandled or could be the result of an internal processing error. For errors of this type, the .NET stack traceback that indicates where the unhandled exception occurred will be written to the database diagnostic log file (db2diag.log).

This error can also occur if the routine attempted an action that is beyond the scope of allowed actions for the specified execution mode for the routine. In this case, an entry is made in the database diagnostic log file to indicate that the exception occurred due to an execution control violation. The exception stack traceback that indicates where the violation occurred is also logged.

Determine if the assembly of the routine has been compromised or recently modified. If the routine has been validly modified, this problem can be occurring because the EXECUTION CONTROL mode for the routine is no longer set to a mode that is appropriate for the changed logic. If you are certain that the assembly has not been wrongfully tampered with, you can modify the routine's execution mode with the ALTER PROCEDURE or ALTER FUNCTION statement as appropriate. Refer to the following topic for more information: