A detailed look at DB2 Stinger .NET CLR Routines

Learn when and how to implement a .NET CLR routine

This article is ideal for DB2® UDB™ users new to either DB2 external routines or programming DB2 database application logic in a .NET common language runtime (CLR) programming language. You will learn what a .NET CLR routine is, what is required to create one, how to create them, and when implementing one is a good choice. In addition the author includes tips regarding the implementation details that will get you up and developing .NET CLR routines quickly and easily.

Share:

Gwyneth Evans (grevans@ca.ibm.com), Software Developer, IBM Canada Inc.

Gwyneth Evans is a software and information developer in the DB2 Solutions Development team for DB2 UDB for Linux, UNIX, and Windows at the IBM Software Lab in Toronto. She has previously worked in product development for the DB2 UDB for Linux, UNIX, and Windows product and as an application developer.



18 June 2004

Also available in Japanese

DB2 .NET CLR routine terminology

In DB2, a .NET CLR routine is an external routine that encapsulates database and application logic that is implemented in a supported .NET CLR programming language. The following terminology will help you as you learn about DB2's support for .NET CLR routines:

  • Routines
    DB2 database objects that contain application logic and database operations that generally relate to a specific task. DB2 provides support for creating 3 types of routines: procedures, functions, and methods. Each type differs in its purpose, implementation, and method of invocation; however they share a consistent DDL syntax, run on the database server, and generally perform better than if the logic contained in them was instead implemented in a client application.

    • Procedures can be invoked directly from a client application, trigger, another routine, or CLP.
    • User-defined functions extend SQL statements and can be referenced in an SQL statement column-list or where expressions are supported.
    • Methods provide class behaviors to structured types .NET CLR methods are not supported.
  • External routines
    DB2 routines that users can create, and that have their routine body written in a host programming language (C, C++, Java™, OLE, COBOL, or a .NET CLR language ) that is compiled into a library. External routines are created by executing an appropriate CREATE statement for the routine type that references the EXTERNAL NAME clause which identifies the library containing the functionality of the routine. These libraries reside in the file system of the database server and not in DB2 itself; hence they are external. All external routines can contain SQL. When external routines are invoked by name, DB2 loads and runs the external library associated with the routine name.

  • .NET Framework
    A Microsoft® application development environment comprised of the CLR and .NET Framework class library designed to provide a consistent programming environment for developing and integrating pieces of code.

  • Common Language Runtime (CLR)
    The runtime interpreter for all .NET Framework applications.

  • Intermediate Language (IL)
    A type of byte-code that is interpreted by the .NET Framework CLR. Source code from all .NET compatible languages compiles to IL byte-code.

  • Assemblies
    Files that contain IL byte-code. They can either be a library or an executable.

  • DB2 .NET Data Provider
    The DB2(R) .NET Data Provider is an extension of the ADO.NET interface that allows .NET applications to access a DB2 database through a secure connection, execute commands, and retrieve results.

  • Microsoft Visual Studio Add-ins
    A set of DB2 Development Center tooling made available within the Visual Studio development environments. Tooling includes support for working with DB2 routines and other database object definitionss. The Add-in must first be registered with Visual Studio before it is available for use.

  • CLR routines can be implemented in any language that can be compiled into an IL assembly. These languages include, but are not limited to: Managed C++, C#, Visual Basic, and J#.


Benefits of implementing CLR routines

Procedures and functions that reference an IL assembly are created in the same way as any external routine is created. 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.

The reasons for implementing a routine at all are pretty much the same regardless of the implementation language:

Encapsulate application logic
In an environment with numerous client computers, each running a variety of database applications, the effective use of routines can simplify code reuse, code standardization, and code maintenance.

Enable controlled access to database objects
You can use routines to control access to database objects. A user might not have permission to generally issue a particular SQL statement, however the user can be given permission to invoke routines that contain specific implementations of these statements.

Reduce network traffic
When an application is running on a client computer, each SQL statement is sent separately from the client computer to the server computer and each result is returned separately. This can result in a high degree of network traffic. If a piece of work can be identified that involves heavy database activity and little user interaction, it makes sense to install this piece of work on the server. With this work running on the server, the quantity of network traffic between the client computer and the server computer is reduced. DB2 routines run on the database server in this manner. Using routines is an effective way of reducing network traffic and improving overall client application performance.

Alleviate the processing load on the client
In environments where the performance of a client computer is a concern, routines are a practical means of reducing the dependence on the client computer. After an application invokes a routine, the processing of the routine is done on the database server, thus allowing the application to exploit the power of the database server while relieving the client computer of the processing load.

Allow faster, more efficient execution
Routines are database objects and therefore have a closer relationship with the database manager than client applications do. For some types of routines the performance of SQL statements can be much better than the performance of SQL statements that are executed from a client application. For example, NOT FENCED routines run in the same process as the database manager using shared memory for communication. This makes the routines more proficient in transmitting SQL requests and data, than a client application could ever be that communicates using TCP/IP protocols.

Interoperability of logic implementations
Because code modules are often implemented by different programmers, each with programming expertise in different programming languages, and because it is generally desirable to reuse code wherever possible to save on development time and costs, DB2 routines are highly interoperable.

  • A client application in one programming language can invoke routines that are implemented in a different programming language. For example, C client applications can invoke .NET common language runtime routines.
  • A routine can invoke another routine regardless of the routine type or the implementation language of the routine. For example a Java procedure (one type of routine) can invoke an SQL scalar function (another type of routine with a different implementation language).
  • A routine created in a database server on one operating system can be invoked from a DB2 client running on a different operating system.

High level .NET CLR languages and Data Provider methods easy to implement
Many of the .NET languages, including C# and Visual Basic, provide strong data typing and easy method-based programming language syntax. Combine that with the ease of using a .NET Data Provider that consists of classes for easily creating DB2DataReaders, and command objects that make interacting with DB2 a cinch, and the ease of use argument is pretty clear.

With the increasing popularity of Microsoft's Visual Studio .NET languages as languages of choice for small and medium businesses implementing Web applications or applications with graphical user-interfaces, it makes sense for you to capitalize on your existing .NET language skills, build on them, and apply them to the development of your server-side logic. The interoperability of DB2 routines that are implemented in different languages and on different platforms is DB2's way of giving you the flexibility to keep on building new application code without throwing away previously existing code written in other languages.


Prerequisites

In order to create a DB2 .NET CLR routine you must first satisfy the following prerequisites:

  • The database server must be running a Windows® operating system that supports the Microsoft .NET Framework
  • The .NET Framework, Version 1.1, must be installed on the server. The .NET Framework is independently available or as part of the Microsoft .NET Framework 1.1 Software Development Kit (SDK).
  • The following versions of DB2 must be installed:
    • On the server: DB2 Stinger or later
    • On the client: DB2 Version 7.2 or later
  • Authority to execute the CREATE statement for the external routine.

Development environments

Active Visual Studio .NET developers need not stray far from the Visual Studio .NET development environment. The DB2 Development Center includes a DB2 Development Add-In for each of your Microsoft Visual Studio application development environments, including Visual Basic version 6, Visual InterDev version 6, and Visual C++ version 6. The add-ins give you easy access to the Development Center wizards and functionality, making it easy for you to create, build, and test DB2 stored procedures and UDFs for use with your Visual Studio applications. With the add-ins, you can also create ADO-based code and objects for the DB2 routines to use in the applications that you create.

For a more detailed description of the Visual Studio .NET DB2 Add-in support, please refer to: http://publib.boulder.ibm.com/infocenter/db2help/topic/com.ibm.db2.udb.doc/core/c0010836.htm.

The alternative to using the Add-in tooling support for developing routines, is to use your favourite editor and the command line. The examples in this article, which focuses on the contents and features of .NET CLR routines, show the Command Line approach to building and running .NET CLR routines.


DB2 .NET Data Provider support

DB2 .NET Data Provider support reference documentation is included with the DB2 .NET Data Provider. It presents detailed information about all the DB2 .NET Data Provider objects and their members. During the DB2 installation process, this documentation is registered with Microsoft(R) Visual Studio .NET. To view the DB2 .NET Data Provider documentation from Microsoft Visual Studio .NET, select the Help menu option, and Contents. Once the help viewer opens, filter by IBM(R) DB2 .NET Data Provider Help .

For more information about and documentation of the DB2 .NET Data Provider objects, please refer to the resources.


CLR routine structure

.NET CLR routines, like all external routines consist of two main pieces:

  • The DDL, in the form of a CREATE statement, that defines the routine in DB2's system catalog tables.
  • The source code that implements the logic and database operations of the routine.

The CREATE statement of the routine gives your routine a name, a parameter signature, and defines certain key characteristics about the routine, including the location of the assembly that contains the method that implements the routine body. At routine invocation time, DB2 resolves the routine name from amongst the set of DB2's routine definitions, locates the appropriate assembly, loads it, and executes the corresponding method.


Developing CLR routines

You can put any programming logic in your routine that is supported by the programming language you choose and you can include any of the SQL statements that are supported in the routine type. The following are the high-level steps to take to create a DB2 .NET CLR from the command line.

Details of the steps follow below along with examples that show you various routine implementations.

  1. Code the routine logic in any CLR supported language
    1. In your source code file, use or import IBM.Data.DB2 if the routine will execute SQL.
    2. Declare host variables and parameters correctly using data types that map to DB2 SQL data types.
    3. Parameters must be declared using one of DB2's supported parameter styles and according to the parameter requirements for .NET CLR routines. Scratchpads for UDFs and the DBINFO class are passed into CLR routines as parameters.
    4. Procedures can return result sets to the caller of a procedure. This is specified, not with a parameter, but by leaving a cursor open (DB2DataReader object in an open state).
    5. Set a routine return value if required.
      • CLR scalar user-defined functions must return a value before returning.
      • CLR table user-defined functions require that a return code is specified as an output parameter for each invocation of the table function.
      • CLR procedures do not require any special return code or value.
  2. Build the code to create an IL assembly
  3. Deploy the assembly
    • Copy the assembly into the DB2 function directory on the database server, the recommended location for storing assemblies or libraries associated with DB2 routines. The function directory is %DB2INSTANCE%\SQLLIB\function where %DB2INSTANCE% is the directory associated with the currently active database manager. To find out more about the function directory, refer to the SQL Reference documentation EXTERNAL clause of either of the following statements: CREATE PROCEDURE or CREATE FUNCTION.

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

  4. Execute the CREATE statement for the routine
    • Dynamically, or statically, execute the CREATE PROCEDURE or CREATE FUNCTION, to define a routine in DB2 by adding details about the routine's parameter signature, dependencies, run-time restrictions, and privileges related to the routine to DB2's system catalog tables. Specify these important non-default clauses as required:
      • Specify the routine name and parameter signature.
      • 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 (for example, GENERAL).
      • 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 DB2 looks 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.

      • Specify DYNAMIC RESULT SETS with value 1 if your routine is a procedure and it will return a result set to the caller.
      • You can not specify the NOT FENCED clause for CLR procedures. By default CLR procedures are executed as FENCED procedures.
      • Specify the EXECUTION CONTROL clause to set limits on the runtime actions of the routine.
  5. Grant the EXECUTE privilege to run the routine to identified routine invokers if they don't already have it, SYSADM, OR DBADM privileges. Any user with SYSADM or DBADM privileges privileges can grant the EXECUTE privilege.
  6. Invoke the DB2 .NET CLR routine. For a procedure, use the CALL statement and specify any necessary parameter values. For a UDF, execute a dynamic or static SQL statement that contains a reference to the UDF. Refer to Routine invocation below for more.

DB2 SQL data types mapped to DB2 .NET Data Provider data types

If your routine passes parameters or contains SQL statements with SQL parameters you must use the appropriate DB2 .NET Data Provider data type to represent those values in order to preserve length and precision, without truncation, of the intended values. The following is a mapping of the DB2 SQL data types to the .NET CLR supported data types.

Mapping of DB2 SQL Data Types to DB2 .NET Data Provider Data Types
DB2Type EnumDB2 Data Type.NET Data Type
SmallInt SMALLINT Int16
Integer INTEGER Int32
BigInt BIGINT Int64
Real REAL Single
Double DOUBLE PRECISION Double
Float FLOAT Double
Decimal DECIMAL Decimal
Numeric DECIMAL Decimal
Date DATE DateTime
Time TIME TimeSpan
Timestamp TIMESTAMP DateTime
Char CHAR String
VarChar VARCHAR String
LongVarChar(1) LONG VARCHAR String
Binary CHAR FOR BIT DATA Byte[]
VarBinary VARCHAR FOR BIT DATA Byte[]
LongVarBinary(1) LONG VARCHAR FOR BIT DATA Byte[]
Graphic GRAPHIC String
VarGraphic VARGRAPHIC String
LongVarGraphic(1) LONG GRAPHIC String
Clob CLOB String
Blob BLOB Byte[]
DbClob DBCLOB(N) String

CLR routine parameters

Parameter declaration in CLR routines must conform to one of DB2's supported parameter styles, and must respect the parameter keyword requirements of the particular .NET language used for the routine. 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. These are addressed below.

CLR routine supported parameter styles

Each routine must conform to a particular convention for the exchange of parameters known as a parameter style which must be specified at routine creation time in the PARAMETER STYLE 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 DB2(R) 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)

Although each of these parameter styles is supported, right now, parameter style GENERAL is the preferred parameter style and also the fastest and easiest to implement.

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.

Passing CLR routine parameters by Value and by reference

.NET CLR supported languages require that method parameters be prefaced by keywords that indicate the particular properties of a 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 DB2 routines:

  • 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# this keyword is ref. In Visual Basic, the 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#, the keyword is out. In Visual Basic, the keyword is byRef. Output only parameters must always be assigned a value before the routine returns to the caller; otherwise a .NET error will be raised at compile time.

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

C# parameter style SQL routine signature
public static void Counter  (out String language,
                             out Int16  languageNullInd,
                             ref String sqlState,
	                             String funcName,
                               	 String funcSpecName,
                             ref String sqlMsgString,
                             ref 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.

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

Parameter memory allocation

DB2 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 marker is required in the procedure declaration of a procedure for a result set that will be returned to the caller. Any cursor statement (implemented as a DB2DataReader object that is in the open state) that is not closed within a CLR stored procedure is passed back to its caller as a result set.

Dbinfo structure is passed to routines as a CLR parameter

The dbinfo structure used for passing additional database property 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)

Building the .NET CLR routine source code

Source code for a .NET CLR routine is essentially the same as a .NET application, however to build your .NET CLR routine into an IL assembly to be executed by the CLR, your compile command must include a reference to the IBM.Data.DB2.dll which contains DB2 .NET Data Provider and routine support. For example, to compile a C# source file containing methods for your external routine with .NET Framework Version 1.1, you require the following command:

csc /out:%1.dll /target:library /debug /reference:%DB2PATH%\bin\netf11\IBM.Data.DB2.dll %1.cs

where:

DB2PATH is an environment variable set by DB2, to specify where DB2 is installed. The compile command is set by default for you if you develop within the Visual Studio .NET IDE. If you work from the command line, you can with a simple batch file compile your code, and copy the compiled assembly over to the function directory as the following compile

Batch file for building CLR routines
@echo off
rem Builds C# routines (stored procedures and UDFs)
rem Usage: bldrtn prog_name
rem Note: To compile and run the stored procedure samples, you must have 
rem           Version 1.1 or later of the .NET Framework installed.
rem           When using the .NET Framework Version 1.1 point to netf11
set VERSION=netf11

rem Compile the program.
csc /out:%1.dll /target:library /debug /reference:%DB2PATH%\bin\%VERSION%\IBM.Data.DB2.dll %1.cs

if exist "%DB2PATH%\function\%1.dll" goto delete else goto copydll

:delete
del "%DB2PATH%\function\%1.dll"
goto copydll

:copydll
rem Copy the routine assembly data link library to the 'function' directory
copy "%1.dll" "%DB2PATH%\function"

@echo onroutine

For Visual Basic, simply change the compile command to:

vbc /target:library /debug /libpath:%DB2PATH%\bin\%VERSION% 
  /reference:%DB2PATH%\bin\%VERSION%\IBM.Data.DB2.dll 
    /reference:System.dll /reference:System.Data.dll %1.vb

Executing the routine CREATE statement

To create an external routine you must formulate and execute a well-formed SQL statement that defines the routine, such as the CREATE PROCEDURE or CREATE FUNCTION statements. These statements create a DB2 database object in DB2's system catalog tables with a name, parameter style and specification, details about the characteristics of the routine that allows DB2 to uniquely identify the routine, check that it hasn't been tampered with, properly locate and load the external library associated with the routine when it is invoked, and run it according to the characteristics provided.

The following is the syntax of a CREATE PROCEDURE statement for external routines. For the CREATE FUNCTION statement syntax and the authorities required to execute the statements, refer to the SQL Reference (refer to Resources).

CREATE PROCEDURE statement
>>-CREATE PROCEDURE--procedure-name----------------------------->

>--+--------------------------------------------------------+--->
   '-(--+----------------------------------------------+--)-'
        | .-,----------------------------------------. |
        | V .-IN----.                                | |
        '---+-------+--+----------------+--data-type-+-'
            +-OUT---+  '-parameter-name-'
            '-INOUT-'

>--*--+-------------------------+--*---------------------------->
      '-SPECIFIC--specific-name-'

   .-DYNAMIC RESULT SETS 0--------.     .-MODIFIES SQL DATA-.
>--+------------------------------+--*--+-------------------+--->
   '-DYNAMIC RESULT SETS--integer-'     +-NO SQL------------+
                                        +-CONTAINS SQL------+
                                        '-READS SQL DATA----'

      .-NOT DETERMINISTIC-.     .-CALLED ON NULL INPUT-.
>--*--+-------------------+--*--+----------------------+--*----->
      '-DETERMINISTIC-----'

   .-OLD SAVEPOINT LEVEL-.
>--+---------------------+--*--LANGUAGE--+-C-----+--*----------->
   '-NEW SAVEPOINT LEVEL-'               +-JAVA--+
                                         +-COBOL-+
                                         +-CLR---+
                                         '-OLE---'

>--EXTERNAL--+----------------------+--*------------------------>
             '-NAME--+-'string'---+-'
                     '-identifier-'

   .-FENCED------------------------.
>--+-------------------------------+--*------------------------->
   +-FENCED--*--+-THREADSAFE-----+-+
   |            '-NOT THREADSAFE-' |
   |                .-THREADSAFE-. |
   '-NOT FENCED--*--+------------+-'

   .-EXTERNAL ACTION----.  .-INHERIT SPECIAL REGISTERS-.
>--+--------------------+--+---------------------------+--*----->
   '-NO EXTERNAL ACTION-'

>--PARAMETER STYLE--+-DB2GENERAL---------+--*------------------->
                    +-DB2SQL-------------+
                    +-GENERAL------------+
                    +-GENERAL WITH NULLS-+
                    +-JAVA---------------+
                    '-SQL----------------'

>--+------------------------------+--*-------------------------->
   '-PARAMETER CCSID--+-ASCII---+-'
                      '-UNICODE-'

                                  .-NO DBINFO-.
>--+------------------------+--*--+-----------+--*-------------->
   '-PROGRAM TYPE--+-SUB--+-'     '-DBINFO----'
                   '-MAIN-'
>--+---------------------------------------------------+-------><
   +-EXECUTION CONTROL-+----SAFE----+------------------+
                       +---FILEREAD-+
                       +--FILEWRITE-+
                       +---UNSAFE---+

Now that looks quite long and complex, but it isn't really. The statement provides default and non-default clauses. The default clauses are listed above the line. You only need to explicitly include a clause in your CREATE PROCEDURE statement if you wish to use the non-default value.

A few noteworthy clauses in this statement, of particular interest for .NET CLR routines:

  • LANGUAGE
    This clause is used to tell DB2 what language the external library is implemented in. For any .NET CLR supported language, including managed C, you must use: CLR.
  • DYNAMIC RESULT SETS
    This indicates whether your procedure will return a result set. .NET CLR routines can only return a single result set at this time.
  • MODIFIES SQL DATA
    DB2 uses this clause to determine the intended level of SQL access the routine is allowed to have. You should set this to the lowest level of access appropriate for your routine's logic so that DB2 can check and warn you when a library's SQL access changes in the external library. The default is the least restrictive setting.
  • EXTERNAL NAME
    This is the clause that specifies the path, namespace, class, and method name that contains the implementation of your .NET CLR routine. It is best if you specify a fully-qualified path name and store the assemblies associated with your .NET CLR routines in SQLLIB\function. This is the default directory that DB2 will look in for routine assemblies. You can however, also store the assembly in any directory that is specified within your PATH. The string that designates the external method to use must be formatted as follows:
    >>-'--assembly--:--class_id--!--method_id--'-------------------><

    where:

    • assembly:Identifies the full name of the DLL or assembly file in which the class resides
    • class_id:Specifies the name of the class in which the method that is to be invoked resides. If a namespace is used for this class, it must prefix the class name
    • method_id:Identifies the method within the class to be invoked

    No leading or trailing blank characters are permitted between the single quotes, object identifiers, and the separating characters (for example, ' <a> ! <b> ' is invalid). Path and file names, however, may 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 the short form of the file name is used, if the platform is UNIX or if the routine is a LANGUAGE CLR routine, then the file must reside in the function directory. If the platform is Windows and the routine is not a LANGUAGE CLR routine then the file must reside in the system PATH. File extensions (examples: .a (on UNIX), .dll (on Windows)) should always be included in the file name.

  • PARAMETER STYLE
    DB2 supports a set of parameter styles that serve slightly different purposes. At this time, parameter style GENERAL is the preferred parameter style for .NET CLR procedure, although you can use any of: GENERAL, GENERAL WITH NULLS, and SQL. All user-defined functions must be implemented with parameter style SQL, because they are invoked from within SQL statements.
  • EXECUTION CONTROL
    Identifies what types of actions a routine will be allowed to perform at run time. At run time, DB2 can detect if the routine attempts to perform actions beyond the scope of its specified execution control mode, which can be helpful when determining whether a library has been compromised.

    These modes represent a hierarchy of allowable actions, and a higher-level mode includes the actions that are allowed below it in the hierarchy. For example, execution control mode NETWORK allows a routine to access files on the network, files on the local file system, and resources that are controlled by the database manager.


CLR procedure result sets

You can develop CLR procedures that return result sets to a calling routine or application. Result sets cannot be returned from CLR functions (UDFs).

The DB2 .NET Data Provider representation of a result set is a DB2DataReader object which can be returned from one of the various execute calls of a DB2Command object. Any DB2DataReader object whose Close()method has not explicitly been called prior to the return of the procedure, can be returned as a result set. No additional parameters are required in the function definition in order to return a result set.

To return a result set from a CLR procedure:

  1. In the CREATE PROCEDURE statement for the CLR routine specify the DYNAMIC RESULT SETS clause with a value equal to the number of result sets that are to be returned by the procedure. No parameter marker is required in the procedure declaration for a result set that is to be returned to the caller.
  2. In the .NET language implementation of your CLR routine, create a DB2Connection object, a DB2Command object, and a DB2Transaction object. A DB2Transaction object is responsible for rolling back and committing database transactions.
  3. Initialize the Transaction property of the DB2Command object to the DB2Transaction object.
  4. Assign a string query to the DB2Command object's CommandText property that defines the result set that you want to return.
  5. Instantiate a DB2DataReader, and assign to it, the result of the invocation of the DB2Command object method ExecuteReader. The result set of the query will be contained in the DB2DataReader object.
  6. Do not execute the Close() method of the DB2DataReader object at any point prior to the procedure's return to the caller. The still open DB2DataReader object will be returned as a result set to the caller.

After compiling and deploying your routine's code, and executing the CREATE PROCEDURE statement has successfully been executed, you can invoke the procedure with the CALL statement to see the result sets return to the caller.

Note:
At the time of publishing this article you can't specify DYNAMIC RESULT SETS with a value greater than 1.


Troubleshooting tips

All external routines share a generally common implementation, but there are some DB2 errors that may arise that are specific to CLR routines. Regardless of when a DB2 routine related error is raised by DB2, the error message text details the cause of the error and the action that the user should take to resolve the problem. Additional routine error scenario information can be found in the db2diag.log diagnostic log file.

To avoid CLR routine creation time or runtime errors, verify the following:

  • .NET CLR methods are currently not supported
  • Make sure that you specify the EXTERNAL NAME clause correctly using either the short form of the name (example: math.dll) or the fully qualified path name (example: d:\udfs\math.dll).
  • If the short form of the file name is used make sure that the file resides in the function directory. If the platform is Windows and the routine is not a LANGUAGE CLR routine then the file must reside in the system PATH.
  • Make sure that the assembly resides in the directory specified by the EXTERNAL NAME clause.
  • Ensure that the DB2 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, please ensure that the .NET Framework version 1.1, or a later version, is installed on the database server, and that the database server is running DB2 version 8.2 or a later release.
  • 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.
  • It is a good idea to implement error handling that catches errors after each SQL statement so that you can debug errors with some degree of granularity.

Routine invocation

DB2 stored procedures are invoked by executing the CALL statement. The CALL statement provides parameter checking and parameter passing support back and forth between the caller and the stored procedure. UDFs are invoked by referencing a UDF within a SELECT query, subselect query, a VALUES clause, or anywhere that an expression is supported within an SQL statement, including, in the case of table-functions, the FROM clause.

To successfully invoke a stored procedure or UDF, the invoker must have EXECUTE privileges on the routine.


Examples of CLR procedures (C#)

The following examples below make use of a table named EMPLOYEE that is contained in the SAMPLE database.

C# external code file structure

The examples show a variety of C# procedure implementations. Each example consists of two parts: the CREATE PROCEDURE statement and the external C# code implementation of the procedure from which the associated assembly can be built.

The C# source file that contains the procedure implementations of the following examples is named gwenProc.cs and has the following format:

C# external code file structure
 using System;
 using System.IO;
 using IBM.Data.DB2;
  
 namespace bizLogic
 {
    class empOps
    {          ... 
      // C# procedures 
               ...
    }
 }

The IBM.Data.DB2 file inclusion is required if any of the procedures in the file contain SQL. There is a namespace declaration in this file and a class empOps that contains the procedures. The use of namespaces is optional. If a namespace is used, the namespace must appear in the assembly path name provided in the EXTERNAL clause of the CREATE PROCEDURE statement.

Example 1: C# parameter style GENERAL procedure

This example shows the following:

  • CREATE PROCEDURE statement for a parameter style GENERAL procedure
  • C# code for a parameter style GENERAL procedure

This procedure takes an employee ID and a current bonus amount as input. It retrieves the employee's name and salary. If the current bonus amount is zero, a new bonus is calculated, based on the employee's salary, and returned along with the employee's full name. If the employee is not found, an empty string is returned.

Code to create a C# parameter style GENERAL procedure
   CREATE PROCEDURE setEmpBonusGEN(IN empID CHAR(6), INOUT bonus Decimal(9,2), 
                                   OUT empName VARCHAR(60))
   SPECIFIC SetEmpBonusGEN
   LANGUAGE CLR
   PARAMETER STYLE GENERAL
   DYNAMIC RESULT SETS 0
   PROGRAM TYPE SUB
   EXTERNAL NAME 'gwenProc.dll:bizLogic.empOps!SetEmpBonusGEN' 
   EXECUTION CONTROL SAFE;
    public static void SetEmpBonusGEN(    String empID, 
                                      ref Decimal bonus, 
                                      out String empName)
    {
       // Declare local variables
       Decimal salary = 0;
 
       DB2Command myCommand = DB2Context.GetCommand();
       myCommand.CommandText = 
                        "SELECT FIRSTNME, MIDINIT, LASTNAME, SALARY "
                      + "FROM EMPLOYEE "
                      + "WHERE EMPNO = '" + empID + '";
 
       DB2DataReader reader = myCommand.ExecuteReader();
 
       if (reader.Read())  // If employee record is found
       {
          // Get the employee's full name and salary
          empName = reader.GetString(0) + " " +
                    reader.GetString(1) + ". " +
                    reader.GetString(2);
 
          salary = reader.GetDecimal(3);
 
          if (bonus == 0)
          {
             if (salary > 75000)
             {
                bonus = salary * (Decimal)0.025;
             }
             else
             {
                bonus = salary * (Decimal)0.05;
             }
          }
       }
       else  // Employee not found
       {
          empName = "";  // Set output parameter
       }
       
       reader.Close();
    }

Example 2: C# parameter style GENERAL WITH NULLS procedure

This example shows the following:

  • CREATE PROCEDURE statement for a parameter style GENERAL WITH NULLS procedure
  • C# code for a parameter style GENERAL WITH NULLS procedure.

This procedure takes an employee ID and a current bonus amount as input. If the input parameter is not null, it retrieves the employee's name and salary. If the current bonus amount is zero, a new bonus based on salary is calculated and returned along with the employee's full name. If the employee data is not found, a NULL string and integer is returned.

Code to create a C# parameter style GENERAL WITH NULLS procedure
   CREATE PROCEDURE SetEmpbonusGENNULL(IN empID CHAR(6), 
                                     INOUT bonus Decimal(9,2), 
                                     OUT empName VARCHAR(60))
   SPECIFIC SetEmpbonusGENNULL
   LANGUAGE CLR
   PARAMETER STYLE GENERAL WITH NULLS
   DYNAMIC RESULT SETS 0
   FENCED
   PROGRAM TYPE SUB
   EXTERNAL NAME 'gwenProc.dll:bizLogic.empOps!SetEmpBonusGENNULL'
   EXECUTION CONTROL SAFE;
   public static void SetEmpBonusGENNULL(    String empID, 
                                         ref Decimal bonus, 
                                         out String empName, 
                                             Int16[] NullInds)
    {
       Decimal salary = 0;
       if (NullInds[0] == -1) // Check if the input is null
       {
         NullInds[1] = -1;    // Return a NULL bonus value
         empName = "";        // Set output value
         NullInds[2] = -1;    // Return a NULL empName value
       }
       else
       {
          DB2Command myCommand = DB2Context.GetCommand();
     	   myCommand.CommandText = 
                          "SELECT FIRSTNME, MIDINIT, LASTNAME, SALARY "
                        + "FROM EMPLOYEE "
                        + "WHERE EMPNO = '" + empID + "'";
          DB2DataReader reader = myCommand.ExecuteReader();
  
          if (reader.Read())  // If employee record is found
          {
             // Get the employee's full name and salary
             empName = reader.GetString(0) + " "
             +
                       reader.GetString(1) + ". " +
                       reader.GetString(2);
             salary = reader.GetDecimal(3);
 
             if (bonus == 0)
             {
               if (salary > 5000)
               {
                  bonus = salary * (Decimal)0.025;
                  NullInds[1] = 0; // Return a non-NULL value
               }
               else
               {
                  bonus = salary * (Decimal)0.05;
                  NullInds[1] = 0; // Return a non-NULL value
               }
             }
          }
          else  // Employee not found
          {
             empName = "*sdq;;     // Set output parameter
             NullInds[2] = -1;     // Return a NULL value
          }
 	       
          reader.Close();
       }
    }

Example 3: C# parameter style GENERAL procedure returning a result set

This example shows the following:

  • CREATE PROCEDURE statement for an external C# procedure returning a result set
  • C# code for a parameter style GENERAL procedure that returns a result set

This procedure accepts the name of a table as a parameter. It returns a result set containing all the rows of the table specified by the input parameter. This is done by leaving a DB2DataReader for a given query result set open when the procedure returns. Specifically, if reader.Close() is not executed, the result set will be returned.

Code to create a C# procedure that returns a result set
   CREATE PROCEDURE ReturnResultSet(IN tableName
   VARCHAR(20))
   SPECIFIC ReturnResultSet
   DYNAMIC RESULT SETS 1
   LANGUAGE CLR
   PARAMETER STYLE GENERAL
   FENCED
   PROGRAM TYPE SUB
   EXTERNAL NAME
   'gwenProc.dll:bizLogic.empOps!ReturnResultSet' 
   EXECUTION CONTROL SAFE  ;
   public static void ReturnResultSet(string tableName)
    {
       DB2Command myCommand = DB2Context.GetCommand();
          
       // Set the SQL statement to be executed and execute it.
       myCommand.CommandText = "SELECT * FROM " + tableName;
       DB2DataReader reader = myCommand.ExecuteReader();
 
       // The DB2DataReader contains the result of the query.
       // This result set can be returned with the procedure,
       // by simply NOT closing the DB2DataReader.
       // Specifically, do NOT execute reader.Close();
    }

Examples of CLR UDFs (C#)

The examples below make use of a table named EMPLOYEE that is contained in the SAMPLE database. The SAMPLE database is a database definition that can be used to create a sample. Use the db2sampl command to create this database. Use the examples as references when making your own C# CLR UDFs:

The C# external code file

The following examples show a variety of C# UDF implementations. The CREATE FUNCTION statement is provided for each UDF with the corresponding C# source code from which the associated assembly can be built. The C# source file that contains the functions declarations used in the following examples is named gwenUDF.cs and has the following format:

C# external code file format
using System;
using System.IO;
using IBM.Data.DB2;
 
namespace bizLogic
{
   ...
   // Class definitions that contain UDF declarations
   // and any supporting class definitions
   ...
}

The function declarations must be contained in a class within a C# file. The use of namespaces is optional. If a namespace is used, the namespace must appear in the assembly path name provided in the EXTERNAL clause of the CREATE PROCEDURE statement. The IBM.Data.DB2. inclusion is required if the function contains SQL.

Example 1: C# parameter style SQL table function

This example shows the following:

  • CREATE FUNCTION statement for a parameter style SQL table function
  • C# code for a parameter style SQL table function

This table function returns a table containing rows of employee data that was created from a data array. There are two classes associated with this example. Class person represents the employees, and the class empOps contains the routine table UDF that uses class person. The employee salary information is updated based on the value of an input parameter. The data array in this example is created within the table function itself on the first call of the table function. Such an array could have also been created by reading in data from a text file on the filesystem. The array data values are written to a scratchpad so that the data can be accessed in subsequent calls of the table function.

On each call of the table function, one record is read from the array and one row is generated in the table that is returned by the function. The row is generated in the table, by setting the output parameters of the table function to the desired row values. After the final call of the table function occurs, the table of generated rows is returned.

Code to create a C# parameter style SQL table function
  CREATE FUNCTION tableUDF(double)
  RETURNS TABLE (name varchar(20), 
                 job varchar(20), 
                 salary double)
  EXTERNAL NAME 'gwenUDF.dll:bizLogic.empOps!tableUDF'
  LANGUAGE CLR
  PARAMETER STYLE SQL
  NOT DETERMINISTIC
  FENCED
  SCRATCHPAD 10
  FINAL CALL
  DISALLOW PARALLEL
  NO DBINFO 
  EXECUTION CONTROL SAFE  ;
  // The class Person is a supporting class for  
  // the table function UDF, tableUDF, below.
  class Person
  {
       private String name;
       private String position;
       private Int32 salary;

       public Person(String newName, String newPosition, Int32
       newSalary)
       {
          this.name = newName;
          this.position = newPosition;
          this.salary = newSalary;
       }

       public String getName()
       {
          return this.name;
       }

       public String getPosition()
       {
          return this.position;
       }

       public Int32 getSalary()
       {
          return this.salary;
       }
   }
  class empOps
  {
    {
     public static void TableUDF( Double factor, out String name,
                       out String position, out Double salary,
                       Int16 factorNullInd, out Int16 nameNullInd,
                       out Int16 positionNullInd, out Int16 salaryNullInd,
                       ref String sqlState, String funcName,
                       String specName, ref String sqlMessageText,
                       Byte[] scratchPad, Int32 callType)
     {

        Int16 intRow = 0;

        // Create an array of Person type information
        Person[] Staff = new
        Person[3];
        Staff[0] = new Person("Gwen", "Developer", 10000);
        Staff[1] = new Person("Andrew", "Developer", 20000);
        Staff[2] = new Person("Liu", "Team Leader", 30000); 

        salary = 0;
        name = position = "";       
        nameNullInd = positionNullInd = salaryNullInd = -1;

        switch(callType)
        {
           case (-2):  // Case SQLUDF_TF_FIRST:
             break;

           case (-1):  // Case SQLUDF_TF_OPEN:
             intRow = 1;
             scratchPad[0] = (Byte)intRow;  // Write to scratchpad
             break;
           case (0):   // Case SQLUDF_TF_FETCH:
             intRow = (Int16)scratchPad[0];
             if (intRow > Staff.Length)
             {
                sqlState = "02000";  // Return an error SQLSTATE
             }
             else
             {
                // Generate a row in the output table 
                // based on the Staff array data.
                name =
                Staff[intRow-1].getName();
                position = Staff[intRow-1].getPosition();
                salary = (Staff[intRow-1].getSalary[]] * factor;
                nameNullInd = 0;
                positionNullInd = 0;
                salaryNullInd = 0;
             }
             intRow++;
             scratchPad[0] = (Byte)intRow;  // Write scratchpad
             break;

           case (1):   // Case SQLUDF_TF_CLOSE:
             break;
     
           case (2):   // Case SQLUDF_TF_FINAL:
             break;
        }
     }
  }

Example 2: C# parameter style SQL scalar function

This example shows the following:

  • CREATE FUNCTION statement for a parameter style SQL scalar function
  • C# code for a parameter style SQL scalar function

This scalar function returns a single count value for each input value that it operates on. For an input value in the nth position of the set of input values, the output scalar value is the value n. On each call of the scalar function, where one call is associated with each row or value in the input set of rows or values, the count is increased by one and the current value of the count is returned. The count is then saved in the scratchpad memory buffer to maintain the count value between each call of the scalar function.

This scalar function can be easily invoked if for example we have a table defined as follows:

  CREATE TABLE T (i1 INTEGER);
  INSERT INTO T VALUES 12, 45, 16, 99;

A simple query such as the following can be used to invoke the scalar function:

  SELECT countUp(i1) as count, i1 FROM T;

The output of such a query would be:

  COUNT           I1
  -----------     ----------
  1               12
  2               45
  3               16
  4               99

This scalar UDF is quite simple. Instead of returning just the count of the rows, you could use a scalar function to format data in an existing column. For example you might append a string to each value in an address column or you might build up a complex string from a series of input strings or you might do a complex mathematical evaluation over a set of data where you must store an intermediate result.

Code to create a C# parameter style SQL scalar function
  CREATE FUNCTION countUp(INTEGER) 
  RETURNS INTEGER
  LANGUAGE CLR
  PARAMETER STYLE SQL
  FENCED
  SCRATCHPAD 10
  FINAL CALL
  VARIANT
  NO SQL
  EXTERNAL NAME 'gwenUDF.dll:bizLogic.empOps!CountUp' 
   EXECUTION CONTROL SAFE ;
  class empOps
  {
     public static void CountUp(     Int32 input,     
                                 out Int32 outCounter,
                                     Int16 inputNullInd, 
                                 out Int16 outCounterNullInd,
                                 ref String sqlState,
                                     String funcName,
                                     String specName,
                                 ref String sqlMessageText,
                                     Byte[] scratchPad,
                                     Int32 callType)
     {
        Int32 counter = 1;        switch(callType)
        {
           case -1: // case SQLUDF_FIRST_CALL
             scratchPad[0] = (Byte)counter;
             outCounter = counter;
             outCounterNullInd = 0;
             break;
           case 0:  // case SQLUDF_NORMAL_CALL:
             counter = (Int32)scratchPad[0];
             counter = counter + 1;
             outCounter = counter;
             outCounterNullInd = 0;
             scratchPad[0] =
             (Byte)counter;
             break;
           case 1:  // case SQLUDF_FINAL_CALL:
             counter =
             (Int32)scratchPad[0];
             outCounter = counter;
             outCounterNullInd = 0;
             break;
           default: // Should never enter here
                    // * Required so that at compile time 
                    //   out parameter outCounter is always set *
             outCounter = (Int32)(0);
             outCounterNullInd = -1;
             sqlState="ABCDE";
             sqlMessageText = "Should not get here: Default
             case!";
             break;
        }            
     } 
  }

Examples of CLR procedures (VB)

Use the following examples as references when making your own Visual Basic CLR procedures:

The Visual Basic external code file

The examples show a variety of Visual Basic procedure implementations. Each example consists of two parts: the CREATE PROCEDURE statement and the external Visual Basic code implementation of the procedure from which the associated assembly can be built.

The Visual Basic source file that contains the procedure implementations of the following examples is named gwenVbProc.vb and has the following format:

Visual Basic external code file format
using System;
using System.IO;
using IBM.Data.DB2;
 
Namespace bizLogic
  
  Class empOps
                ... 
     ' Visual Basic procedures 
                ...
   End Class
End Namespace

The file inclusions are indicated at the top of the file. The IBM.Data.DB2 inclusion is required if any of the procedures in the file contain SQL. There is a namespace declaration in this file and a class empOps that contains the procedures. The use of namespaces is optional. If a namespace is used, the namespace must appear in the assembly path name provided in the EXTERNAL clause of the CREATE PROCEDURE statement.

It is important to note the name of the file, the namespace, and the name of the class, that contains a given procedure implementation. These names are important, because the EXTERNAL clause of the CREATE PROCEDURE statement for each procedure must specify this information so that DB2 can locate the assembly and class of the CLR procedure.

Example 1: Visual Basic parameter style GENERAL procedure

This example shows the following:

  • CREATE PROCEDURE statement for a parameter style GENERAL procedure
  • Visual Basic code for a parameter style GENERAL procedure

This procedure takes an employee ID and a current bonus amount as input. It retrieves the employee's name and salary. If the current bonus amount is zero, a new bonus is calculated, based on the employee salary, and returned along with the employee's full name. If the employee is not found, an empty string is returned.

Code to create a Visual Basic parameter style GENERAL procedure
  CREATE PROCEDURE SetEmpBonusGEN(IN empId CHAR(6),
                                  INOUT bonus Decimal(9,2),
                                  OUT empName VARCHAR(60))
  SPECIFIC setEmpBonusGEN
  LANGUAGE CLR
  PARAMETER STYLE GENERAL
  DYNAMIC RESULT SETS 0
  FENCED
  PROGRAM TYPE SUB
  EXTERNAL NAME 'gwenVbProc.dll:bizLogic.empOps!SetEmpBonusGEN'
  EXECUTION CONTROL SAFE ;
    Public Shared Sub SetEmpBonusGEN(ByVal empId As String, _
                                     ByRef bonus As Decimal, _
                                     ByRef empName As String)

       Dim salary As Decimal
       Dim myCommand As DB2Command
       Dim myReader As DB2DataReader

       salary = 0

       myCommand = DB2Context.GetCommand()
       myCommand.CommandText = _
                  "SELECT FIRSTNME, MIDINIT, LASTNAME, SALARY " _
                + "FROM EMPLOYEE " _
                + "WHERE EMPNO = '" + empId + "'"
       myReader = myCommand.ExecuteReader()

       If myReader.Read()  ' If employee record is found
          ' Get the employee's full name and salary
          empName = myReader.GetString(0) + " " _
                  + myReader.GetString(1) + ". " _
                  + myReader.GetString(2)

          salary = myReader.GetDecimal(3)

          If bonus = 0
             If salary > 5000
                bonus = salary * 0.025
             Else
                bonus = salary * 0.05
             End If
          End If
       Else  ' Employee not found
          empName = ""  ' Set output parameter
       End If

       myReader.Close()

    End Sub

Example 2: Visual Basic parameter style SQL procedure

This example shows the following:

  • CREATE PROCEDURE statement for a parameter style SQL procedure
  • Visual Basic code for a parameter style SQL procedure

This procedure takes an employee ID and a current bonus amount as input. It retrieves the employee's name and salary. If the current bonus amount is zero, a new bonus based on salary is calculated and returned along with the employee's full name. If the employee is not found, an empty string is returned.

Code to create a Visual Basic procedure in parameter style SQL with parameters
  CREATE PROCEDURE SetEmpBonusSQL(IN empId CHAR(6),
                                  INOUT bonus Decimal(9,2),
                                  OUT empName VARCHAR(60))
  SPECIFIC SetEmpBonusSQL
  LANGUAGE CLR
  PARAMETER STYLE SQL
  DYNAMIC RESULT SETS 0
  FENCED
  PROGRAM TYPE SUB
  EXTERNAL NAME 'gwenVbProc.dll:bizLogic.empOps!SetEmpBonusSQL'
  EXECUTION CONTROL SAFE ;
    Public Shared Sub SetEmpBonusSQL(byVal empId As String, _
                                     byRef bonus As Decimal, _
                                     byRef empName As String, _
                                     byVal empIdNullInd As Int16, _
                                     byRef bonusNullInd As Int16, _
                                     byRef empNameNullInd As Int16, _
                                     byRef sqlState As String, _
                                     byVal funcName As String, _
                                     byVal specName As String, _
                                     byRef sqlMessageText As String)

       ' Declare local host variables
       Dim salary As Decimal
       Dim myCommand As DB2Command
       Dim myReader As DB2DataReader

       salary = 0

       If empIdNullInd = -1   ' Check if the input is null
          bonusNullInd = -1   ' Return a NULL Bonus value
          empName = ""
          empNameNullInd = -1 ' Return a NULL empName value
       Else
         myCommand = DB2Context.GetCommand()
         myCommand.CommandText = _
                    "SELECT FIRSTNME, MIDINIT, LASTNAME, SALARY " _
                  + "FROM EMPLOYEE " _
                  + " WHERE EMPNO = '" + empId + "'"

          myReader = myCommand.ExecuteReader()

          If myReader.Read()  ' If employee record is found
             '  Get the employee's full name and salary
             empName = myReader.GetString(0) + " " 
                     + myReader.GetString(1) _
                     + ". " +  myReader.GetString(2)
             empNameNullInd = 0
             salary = myReader.GetDecimal(3)

             If bonus = 0
                If salary > 5000
                   bonus = salary * 0.025
                   bonusNullInd = 0  ' Return a non-NULL value
                Else
                   bonus = salary * 0.05
                   bonusNullInd = 0  ' Return a non-NULL value
                End If
             End If
          Else  ' Employee not found
             empName = ""            ' Set output parameter
             empNameNullInd = -1     ' Return a NULL value
          End If
          
          myReader.Close()
       End If
       
    End Sub

Example 3: Visual Basic procedure with PROGRAM TYPE MAIN style

This example shows the following:

  • CREATE PROCEDURE statement for a procedure using a main program style
  • Visual Basic parameter style GENERAL WITH NULLS code in using a MAIN program style

To implement a routine in a main program style, the PROGRAM TYPE clause must be specified in the CREATE PROCEDURE statement with the value MAIN. Parameters are specified in the CREATE PROCEDURE statement however in the code implementation, parameters are passed into the routine in an argc integer parameter and an argv array of parameters.

Code to create a Visual Basic procedure in program type MAIN style
  CREATE PROCEDURE MainStyle(IN empId CHAR(6),
                             INOUT bonus Decimal(9,2),
                             OUT empName VARCHAR(60))
  SPECIFIC mainStyle
  DYNAMIC RESULT SETS 0
  LANGUAGE CLR
  PARAMETER STYLE GENERAL WITH NULLS
  FENCED
  PROGRAM TYPE MAIN
  EXTERNAL NAME 'gwenVbProc.dll:bizLogic.empOps!Main'
  EXECUTION CONTROL SAFE ;
    Public Shared Sub Main( byVal argc As Int32, _
                            byVal argv As Object())

       Dim myCommand As DB2Command
       Dim myReader As DB2DataReader
       Dim empId As String
       Dim bonus As Decimal
       Dim salary As Decimal
       Dim nullInds As Int16()

       empId = argv(0)  ' argv[0] (IN)    nullInd = argv[3]
       bonus = argv(1)  ' argv[1] (INOUT) nullInd = argv[4]
                        ' argv[2] (OUT)   nullInd = argv[5]
       salary = 0
       nullInds = argv(3)

       If nullInds(0) = -1     ' Check if the empId input is null
          nullInds(1) = -1     ' Return a NULL Bonus value
          argv(1) = ""         ' Set output parameter empName
          nullInds(2) = -1     ' Return a NULL empName value
          Return
       Else
          ' If the employee exists and the current bonus is 0,
          ' calculate a new employee bonus based on the employee's
          ' salary.  Return the employee name and the new bonus
          myCommand = DB2Context.GetCommand()
          myCommand.CommandText = _
                     "SELECT FIRSTNME, MIDINIT, LASTNAME, SALARY " _
                   + " FROM EMPLOYEE " _
                   + " WHERE EMPNO = '" + empId + "'"

          myReader = myCommand.ExecuteReader()


          If myReader.Read()  ' If employee record is found
             ' Get the employee's full name and salary
             argv(2) = myReader.GetString(0) + " "  _
                     + myReader.GetString(1) + ". " _
                     + myReader.GetString(2)
             nullInds(2) = 0
             salary = myReader.GetDecimal(3)

             If bonus = 0
                If salary > 5000
                   argv(1) = salary * 0.025
                   nullInds(1) = 0  ' Return a non-NULL value
                Else
                   argv(1) = Salary * 0.05
                   nullInds(1) = 0  ' Return a non-NULL value
                End If
             End If
          Else  ' Employee not found
             argv(2) = ""      ' Set output parameter
             nullInds(2) = -1  ' Return a NULL value
          End If

          myReader.Close()
       End If

    End Sub

Examples of CLR UDFs (VB)

Use the following examples as references when making your own Visual Basic CLR UDFs:

The Visual Basic external code file

The following examples show a variety of Visual Basic UDF implementations. The CREATE FUNCTION statement is provided for each UDF with the corresponding Visual Basic source code from which the associated assembly can be built. The Visual Basic source file that contains the functions declarations used in the following examples is named gwenVbUDF.cs and has the following format:

Visual Basic external code file format
  using System;
  using System.IO;
  using IBM.Data.DB2;
 
  Namespace bizLogic
  
     ...
     ' Class definitions that contain UDF declarations
     ' and any supporting class definitions
     ...   
End Namespace

The function declarations must be contained in a class within a Visual Basic file. The use of namespaces is optional. If a namespace is used, the namespace must appear in the assembly path name provided in the EXTERNAL clause of the CREATE PROCEDURE statement. The IBM.Data.DB2. inclusion is required if the function contains SQL.

Example 1: Visual Basic parameter style SQL table function

This example shows the following:

  • CREATE FUNCTION statement for a parameter style SQL table function
  • Visual Basic code for a parameter style SQL table function

This table function returns a table containing rows of employee data that was created from a data array. There are two classes associated with this example. Class person represents the employees, and the class empOps contains the routine table UDF that uses class person. The employee salary information is updated based on the value of an input parameter. The data array in this example is created within the table function itself on the first call of the table function. Such an array could have also been created by reading in data from a text file on the filesystem. The array data values are written to a scratchpad so that the data can be accessed in subsequent calls of the table function.

On each call of the table function, one record is read from the array and one row is generated in the table that is returned by the function. The row is generated in the table, by setting the output parameters of the table function to the desired row values. After the final call of the table function occurs, the table of generated rows is returned.

Code to create a Visual Basic parameter style SQL table function
 CREATE FUNCTION TableUDF(double)
  RETURNS TABLE (name varchar(20), 
                 job varchar(20), 
                 salary double)
  EXTERNAL NAME 'gwenVbUDF.dll:bizLogic.empOps!TableUDF'
  LANGUAGE CLR
  PARAMETER STYLE SQL
  NOT DETERMINISTIC
  FENCED
  SCRATCHPAD 10
  FINAL CALL
  DISALLOW PARALLEL
  NO DBINFO
  EXECUTION CONTROL SAFE
  Class Person
  ' The class Person is a supporting class for  
  ' the table function UDF, tableUDF, below.

    Private name As String 
    Private position As String
    Private salary As Int32

    Public Sub New(ByVal newName As String, _
                   ByVal newPosition As String, _
                   ByVal newSalary As Int32)

      name = newName
      position = newPosition
      salary = newSalary
    End Sub
    
    Public Property GetName() As String
      Get
        Return name
      End Get
  
      Set (ByVal value As String)
        name = value
      End Set
    End Property

    Public Property GetPosition() As String
      Get
        Return position
      End Get
    
      Set (ByVal value As String)
        position = value
      End Set
    End Property

    Public Property GetSalary() As Int32
      Get
        Return salary
      End Get
 
      Set (ByVal value As Int32)
        salary = value
      End Set
    End Property

  End Class
   Class empOps

     Public Shared Sub TableUDF(byVal factor as Double, _
                                byRef name As String, _
                                byRef position As String, _
                                byRef salary As Double, _
                                byVal factorNullInd As Int16, _
                                byRef nameNullInd As Int16, _
                                byRef positionNullInd As Int16, _
                                byRef salaryNullInd As Int16, _
                                byRef sqlState As String, _
                                byVal funcName As String, _
                                byVal specName As String, _
                                byRef sqlMessageText As String, _
                                byVal scratchPad As Byte(), _
                                byVal callType As Int32)

       Dim intRow As Int16

       intRow = 0

       ' Create an array of Person type information
       Dim staff(2) As Person
       staff(0) = New Person("Gwen", "Developer", 10000)
       staff(1) = New Person("Andrew", "Developer", 20000)
       staff(2) = New Person("Liu", "Team Leader", 30000) 

       ' Initialize output parameter values and NULL indicators
       salary = 0
       name = position = ""
       nameNullInd = positionNullInd = salaryNullInd = -1

       Select callType              
          Case -2   ' Case SQLUDF_TF_FIRST:
          Case -1   ' Case SQLUDF_TF_OPEN:
            intRow = 1
            scratchPad(0) = intRow  ' Write to scratchpad
          Case 0    ' Case SQLUDF_TF_FETCH:
            intRow = scratchPad(0)
            If intRow > staff.Length
               sqlState = "02000"  ' Return an error SQLSTATE
            Else
               ' Generate a row in the output table 
               ' based on the staff array data.
               name = staff(intRow).GetName()
               position = staff(intRow).GetPosition()
               salary = (staff(intRow).GetSalary()) * factor
               nameNullInd = 0
               positionNullInd = 0
               salaryNullInd = 0
            End If
            intRow = intRow + 1
            scratchPad(0) = intRow  ' Write scratchpad
            
          Case 1    ' Case SQLUDF_TF_CLOSE:
            
          Case 2    ' Case SQLUDF_TF_FINAL:
       End Select

     End Sub           
   
   End Class

Example 2: Visual Basic parameter style SQL scalar function

This example shows the following:

  • CREATE FUNCTION statement for a parameter style SQL scalar function
  • Visual Basic code for a parameter style SQL scalar function

This scalar function returns a single count value for each input value that it operates on. For an input value in the nth position of the set of input values, the output scalar value is the value n. On each call of the scalar function, where one call is associated with each row or value in the input set of rows or values, the count is increased by one and the current value of the count is returned. The count is then saved in the scratchpad memory buffer to maintain the count value between each call of the scalar function.

This scalar function can be easily invoked if for example we have a table defined as follows:

  CREATE TABLE T (i1 INTEGER);
  INSERT INTO T VALUES 12, 45, 16, 99;

A simple query such as the following can be used to invoke the scalar function:

  SELECT my_count(i1) as count, i1 FROM T;

The output of such a query would be:

  COUNT           I1
  -----------     ----------
  1               12
  2               45
  3               16
  4               99

This scalar UDF is quite simple. Instead of returning just the count of the rows, you could use a scalar function to format data in an existing column. For example you might append a string to each value in an address column or you might build up a complex string from a series of input strings or you might do a complex mathematical evaluation over a set of data where you must store an intermediate result.

Code to create a Visual Basic parameter style SQL scalar function
  CREATE FUNCTION mycount(INTEGER) 
  RETURNS INTEGER
  LANGUAGE CLR
  PARAMETER STYLE SQL
  FENCED
  SCRATCHPAD 10
  FINAL CALL
  VARIANT
  NO SQL
  EXTERNAL NAME 'gwenUDF.dll:bizLogic.empOps!CountUp'
  EXECUTION CONTROL SAFE;
  Class empOps
    Public Shared Sub CountUp(byVal input As Int32, _
                              byRef outCounter As Int32, _
                              byVal nullIndInput As Int16, _
                              byRef nullIndOutCounter As Int16, _
                              byRef sqlState As String, _
                              byVal qualName As String, _
                              byVal specName As String, _
                              byRef sqlMessageText As String, _
                              byVal scratchPad As Byte(), _
                              byVal callType As Int32)

       Dim counter As Int32
       counter = 1

       Select callType
          case -1           ' case SQLUDF_TF_OPEN_CALL
             scratchPad(0) = counter
             outCounter = counter
             nullIndOutCounter = 0
          case 0              'case SQLUDF_TF_FETCH_CALL:
             counter = scratchPad(0)
             counter = counter + 1
             outCounter = counter
             nullIndOutCounter = 0
             scratchPad(0) = counter
          case 1             'case SQLUDF_CLOSE_CALL:
             counter = scratchPad(0)
             outCounter = counter
             nullIndOutCounter = 0
          case Else          ' Should never enter here
             ' These cases won't occur for the following reasons:
             ' Case -2  (SQLUDF_TF_FIRST)     ->No FINAL CALL in CREATE stmt
             ' Case 2   (SQLUDF_TF_FINAL)     ->No FINAL CALL in CREATE stmt
             ' Case 255 (SQLUDF_TF_FINAL_CRA) ->No SQL used in the function
             '
             ' * Note!*
             ' ---------
             ' The Else is required so that at compile time
             ' out parameter outCounter is always set *
             outCounter = 0
             nullIndOutCounter = -1
       End Select  
    End Sub        

  End Class

Conclusion

Hopefully this article has helped you on your way towards implementing DB2 .NET CLR routines. Look for lots of examples and complete samples applications coming in DB2 Stinger along with more exciting DB2 .NET Data Provider information.

Resources

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=13673
ArticleTitle=A detailed look at DB2 Stinger .NET CLR Routines
publish-date=06182004