The Microsoft^® .NET vision is all about interoperability of disparate code bases. It allows code written in various supported languages to be easily integrated into a single application. .NET makes a compiler into a syntax-checking device that, in turn, generates bytecode rather than machine-specific instructions.

This is the same idea as the Java™ language, of course. The big difference is that .NET allows any supported programming language to be compiled to Microsoft’s version of bytecode (called intermediate language, or IL for short). Microsoft has compilers for C#, C++, Visual Basic.NET, and J#, which can generate IL code. Other compiler vendors are also implementing this capability for their Windows^®-based products, and many clients are interested in moving to the use of .NET in their application development and database environments.

Essentially, the Java approach is a write-one-language, run-anywhere paradigm, while the .NET approach is a write-any- (supported) language, run-in-one-place (Windows) paradigm. (Although there are projects trying to move the Common Language Runtime (CLR) to operating systems such as Linux^®, they are not officially endorsed by Microsoft.)

The IBM^® DB2^® Universal Database™ product (DB2 UDB) has supported CLR routines in the database since the Version 8.2 release, which became generally available in September 2004. Since then, DB2 UDB was (and, at the time of writing, still is) the only database in the world to supports CLR routines. Other database vendors are bringing this feature to their databases before year’s end.

DB2 UDB and CLR Routines

In DB2 UDB V8.2 (or later), you can code C# and VB.NET methods in class assemblies and then deploy them as DB2 UDB routines. DB2 UDB CLR routines enable you to code your procedures using your favorite .NET-supported language. This support for CLR in DB2 UDB isn’t just for stored procedures; it includes user-defined functions (UDFs) as well — hence, the reference to routines.

Although SQL content in the body of a CLR procedure is optional, it can be coded using the DB2Context object to create new ADO.NET commands that have the context connection object preset. Apart from IN, OUT, IN/OUT parameters, CLR procedures can also return zero or more result sets to the client.

The CLR is essentially the .NET equivalent of a Java virtual machine (JVM), so the DB2 UDB implementation looks quite similar to the one used for its Java routines. In fact, this similarity is a big reason why DB2 UDB was able to quickly implement this feature. The DB2 UDB database has supported external routines for quite some time.

Another item that should be clarified is the term “managed” and “unmanaged” code. Managed code is code that the CLR can run. This is called bytecode, which is an executable library that has been compiled to Microsoft’s intermediate language. In contrast, “unmanaged” or “native” code is an executable library that has been prepared by a conventional compiler in a conventional machine native assembly.

One of the primary goals in the DB2 UDB implementation of the CLR was to minimize the number of native-to-managed (and vice-versa) crossings. Like JNI calls, these crossings are very expensive because they involve lots of processing to prepare data structures, and so on. Unlike JNI, the .NET infrastructure provides many tools for running managed code exclusively. The goal of the DB2 UDB implementation is to cross from native to managed once, and once only. DB2 UDB sets up all the parameters, invokes the routine, and gathers result data, all from within the managed layer, before passing control back to the unmanaged DB2 UDB side.

.NET routines in DB2 UDB can only be created in FENCED mode. While there has been some debate about this implementation (which is beyond the scope of this article), the basis of this decision was ultimate safety. If an application developer writes some messy code, it can’t take down the database engine.

The following figure illustrates the implementation of the CLR in DB2 UDB:

DB2 UDB isolates all .NET routines to run inside a particular set of processes. .NET routines can be defined to run multithreaded, or in a process of their own. The code to load the CLR exists in a new library called db2clr.dll. Once the CLR is started, any thread in the DB2 UDB process can access it. There’s no need to “attach” to the CLR.

Building Your First DB2 UDB CLR-based Stored Procedure

It’s easy to build CLR routines in DB2 UDB. In this section, I’ll take you through a brief (but effective) example of how to build a simple CLR-based stored procedure in DB2 UDB. This example will show you how the rich tooling in DB2 UDB for .NET developers can help accelerate application development.

Step 1. Start with a DB2 UDB database project

To build a CLR-based stored procedure in DB2 UDB, you need to start out with a database project that will allow you to automate the process of cataloging the DB2 UDB CLR stored procedure and deploying the CLR assembly. When you build your DB2 UDB database project as part of a solution that defines a CLR stored procedure, Visual Studio .NET can create the assemblies first, deploy them to the server, and catalog your stored procedure on the DB2 UDB database.

The following figure shows the creation of a DB2 UDB database project:

Once you’ve created a DB2 UDB database project, your Visual Studio .NET integrated development environment (IDE) looks similar to the following figure:

View larger image

Step 2. Create the assembly

Any CLR-based stored procedure has its “guts” in the assembly — in fact, the code you write in Visual Basic.NET or C# is compiled to an assembly, and this is where the logic behind the stored procedure resides.

DB2 UDB comes with a DB2 Class Library template that you can add to your projects as a starter for your own assembly. This makes the development process very simple.

You can add this template to your solution in Visual Studio .NET’s Solution Explorer window, shown in the following figure. (Of course, you must ensure you select the template that matches the .NET language you want to write your CLR-based stored procedure in. Templates are provided for both C# and Visual Basic.NET projects.)

This template provided by the DB2 UDB add-in includes an automatic project reference to the IBM.Data.DB2 managed provider assembly and a simple CLR method to get you started.

When you create a new class library project using the DB2 Class Library project template, a DB2Class1.cs file is also created. It contains a default CLR method that has the DB2 UDB required signature for a CLR procedure: namely, a public class with a public static void method.

The following figure shows a C# template starter class:

View larger image

For the example in this article, you can replace the sample code in the public class DB2Class1 braces with the following code:

The preceding code will take an input parameter as a qualifier for the DEPARTMENT column in the STAFF table in the SAMPLE database that comes with DB2 UDB. If you don’t have the SAMPLE database created, simply enter the db2sampl command at a command prompt.

Step 3. Set the build order of your solution and compile the class

At this point, I like to set the build order of my solution such that the class is compiled into an assembly before the stored procedure is cataloged on the database server (which we’ll do in the next step). Programmers often forget to do this, so I believe it’s a best practice at this point to set the build order of your solution. (You’re going to have to do it anyway, so you might as well do it now.)

The following figure shows how to create a dependency of the DB2 UDB project on the class library you just created. This will automatically adjust the build order accordingly.

As you can see, setting up this dependency alters the build order:

As the Project Dependencies window shows, whenever the YourFirstCLRStoredProcedure project is built, the DB2ClassLibrary1 code will be compiled into an assembly before any logic is built in your project (namely, the stored procedure you are creating).

You also need to compile the class into the assembly that you just built in case it contains any errors, but also to take full advantage of the tooling provided for CLR procedure deployment by the DB2 UDB add-in (more on that in a bit). The following figure shows how to do that:

If all is successful (as shown in the following figure), you can proceed to the next step. Note that in this step, you are only compiling the class into a dynamic link library (.dll) file, and that is why the build action is selected from the pop-up menu on the DB2ClassLibrary1 object.

Step 4. Catalog the build order of your solution

At this point, you’re ready to tell DB2 UDB all about the CLR code you just compiled into an assembly. DB2 UDB comes with a wizard to help you catalog your CLR-based stored procedures: Simply select the Procedures folder in your DB2 UDB database project, and select Add  Add New Item  CLR Procedure Wizard, as shown in the following:

This opens the DB2 CLR Procedure wizard. Click Next.

You can use this wizard to automatically detect the list of CLR projects in your solution and the class (and its associated methods) that you created. (The wizard looks for the ones that match the signature requirement for a DB2 UDB stored procedure.)

The following figure shows this wizard’s usefulness in accelerating the deployment of your CLR stored procedures in DB2 UDB:

Note that the wizard detected the method in the class that was built in the previous step. (If the class had more than one method, they would all be shown here.)

For the example in this article, accept the default values and click Next, which opens the following window:

In Step 2 of this wizard, you can set various properties for the stored procedure. For example, you can map parameters, alter their types, and so on. For the example in this article, select Reads SQL data for the SQL Usage drop-down box, and set Number of results set(s) to 1.

There are a number of other steps in this wizard that you can explore, but for the example in this article, simply skip to the end and click Finish.

At this point, you can see the DB2 UDB CLR stored procedure in your solution and the code that was automatically generated for you from the wizard in the middle pane of the IDE:

View larger image

Step 5. Installing the assembly

The final step in building a .NET stored procedure in DB2 UDB is to ensure that the assembly compiled from the class library is installed on your DB2 UDB server. Again, the tooling provided by DB2 UDB for .NET developers helps here too. The DB2 UDB Database Project supports the management of assemblies, and they are automatically deployed to the DB2 UDB server at build time.

To install the assembly for the stored procedure in this article, right-click your project and select Assemblies, as shown in the following:

Click the … button to locate the assembly (it’s a .dll file). In this example, since I built my solution in debug mode, I selected the assembly in the appropriate folder.

Step 6. Build the solution — you’re done!

Now you’re ready to build your solution.

If you look at the IBM DB2 UDB Output Message Pane, you can see that the stored procedure was cataloged successfully:

Look also at the Build information, and you can see that the assembly was installed and compiled successfully too:

Wrapping It Up

As you can see, building and deploying CLR stored procedures in DB2 UDB is simple and it’s easy to test your business logic as well.

To test the stored procedure you just created, right-click the Procedures folder in the IBM Explorer window and select Refresh. This will refresh the cached object schema in the IBM Explorer. Now, expand the Procedures folder, and select the stored procedure you just built (in this case, the GETSTAFFEMPLOYEENAME procedure), and select Run Procedure.

The Parameter Values window opens. Use this window to alter the input parameter for your stored procedure (the default is null). For example, clear the Null check box, and enter 20 in the Value field, as shown in the following, and click OK.

Follow the Result Set link to see the data that your stored procedure returned based on your input, shown follows:

--

Paul C. Zikopoulos, BA, MBA, is an award-winning writer and speaker with the IBM Database Competitive Technology team. He has more than ten years of experience with DB2 UDB and has written over sixty magazine articles and several books about it. Paul has co-authored the books: DB2 Version 8: The Official Guide, DB2: The Complete Reference, DB2 Fundamentals Certification for Dummies, DB2 for Dummies, and A DBA’s Guide to Databases on Linux. Paul is a DB2 Certified Advanced Technical Expert (DRDA and Cluster/EEE) and a DB2 Certified Solutions Expert (Business Intelligence and Database Administration). In his spare time, he enjoys all sorts of sporting activities, running with his dog Chachi, and trying to figure out the world according to Chloe — his new daughter. You can reach him at: paulz_ibm@msn.com.

Trademarks

IBM, DB2, and DB2 Universal Database, are trademarks or registered trademarks of International Business Machines Corporation in the United States, other countries, or both.

Java and all Java-based trademarks are trademarks of Sun Microsystems, Inc. in the United States, other countries, or both.

Microsoft and Windows are trademarks of Microsoft Corporation in the United States, other countries, or both.

Linux is a trademark of Linus Torvalds in the United States, other countries, or both.

Other company, product, and service names may be trademarks or service marks of others.

Disclaimer

The opinions, solutions, and advice in this article are from the author’s experiences and are not intended to represent official communication from IBM or an endorsement of any products listed within. Neither the author nor IBM is liable for any of the contents in this article. The accuracy of the information in this article is based on the author’s knowledge at the time of writing.