Creating .NET CLR routines from the Db2 command window

Procedures and functions that reference an intermediate language assembly are created in the same way as any external routine is created.

Before you begin

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.

Creating .NET CLR routines from the command line requires you to have some knowledge of CLR routine implementation. To learn about CLR routines in general and about CLR features, see .NET common language runtime (CLR) routines.

Ensure that the following systems and authorities exist before you begin:

  • A database server running a Windows operating system that supports the Microsoft .NET Framework.
  • A supported version of the Microsoft .NET Framework software installed on the server. The .NET Framework is independently available or as part of the Microsoft .NET Framework Software Development Kit.
  • A supported database product or IBM® Data Server Client installed. See the installation requirements for database products.
  • Authority to execute the CREATE statement for the external routine. For the privileges required to execute the CREATE PROCEDURE statement or CREATE FUNCTION statement, see the details of the appropriate statement.


Restrictions

For a list of restrictions associated with CLR routines see:

About this task

You would choose to implement an external routine in a .NET language if:
  • You want to encapsulate complex logic into a routine that accesses the database or that performs an action outside of the database.
  • You require the encapsulated logic to be invoked from any of: multiple applications, the CLP, another routine (procedure, function (UDF), or method), or a trigger.
  • You are most comfortable coding this logic in a .NET language.

Procedure

  1. Code the routine logic in any CLR supported language.
    • For general information about .NET CLR routines and .NET CLR routine features see the topics referenced in the Before you begin section
    • Use or import the IBM.Data.DB2 assembly if your routine will execute SQL.
    • Declare host variables and parameters correctly using data types that map to SQL data types. For a data type mapping between the database and .NET data types, see the following topic:
    • Parameters and parameter null indicators must be declared with one of the supported parameter styles and according to the parameter requirements for .NET CLR routines. Also, scratchpads for UDFs, and the DBINFO class are passed into CLR routines as parameters. For more on parameters and prototype declarations, see the following topic:
    • If the routine is a procedure and you want to return a result set to the caller of the routine, you do not require any parameters for the result set. For more on returning result sets from CLR routines:
    • Set a routine return value if required. CLR scalar functions require that a return value is set before returning. CLR table functions require that a return code is specified as an output parameter for each invocation of the table function. CLR procedures do not return with a return value.
  2. Build your code into an intermediate language (IL) assembly to be executed by the CLR.
    For information on how to build CLR .NET routines that access the databases, see the related links.
  3. Copy the assembly into the database function directory on the database server. You can store assemblies or libraries that are associated with database routines in the function directory. For more information about the function directory, see the EXTERNAL clause of the CREATE PROCEDURE or CREATE FUNCTION statement.

    You can copy the assembly to another directory on the server if you want, but to successfully invoke the routine you must note the fully qualified path name of your assembly as you will require it for the next step.

  4. Execute either dynamically or statically the appropriate SQL language CREATE statement for the routine type: CREATE PROCEDURE or CREATE FUNCTION.
    • Specify the LANGUAGE clause with value: CLR.
    • Specify the PARAMETER STYLE clause with the name of the supported parameter style that was implemented in the routine code.
    • Specify the EXTERNAL clause with the name of the assembly to be associated with the routine using one of the following values:
      • the fully qualified path name of the routine assembly.
      • the relative path name of the routine assembly relative to the function directory.

      By default the database manager will look for the assembly by name in the function directory unless a fully qualified or relative path name for the library is specified in the EXTERNAL clause.

      When the CREATE statement is executed, if the assembly specified in the EXTERNAL clause is not found by the database manager, you will receive an error (SQLCODE -20282) with reason code 1.

    • Specify the DYNAMIC RESULT SETS clause with an integer value equivalent to the maximum number of result sets that might be returned by the routine.
    • You can not specify the NOT FENCED clause for CLR procedures. By default CLR procedures are executed as FENCED procedures.