A DB2® Common Language Runtime (CLR) procedure is a DB2 procedure that runs a method, written in a .NET-managed language, from an SQL statement. With the IBM® Database Add-Ins for Visual Studio support for CLR procedures, you can write methods in either C# or Visual Basic, or map existing CLR methods, to work with a DB2 data source. You can develop CLR methods in a .NET managed language and use the DB2 CLR Procedure wizard to create DB2 CLR procedures to run the CLR methods.
Note: You can create DB2 CLR procedures only on DB2 for Linux®, UNIX®, and Windows®, Version 9 or later data servers.
The following code samples show a CLR method that is written in C# and a corresponding DB2 CLR procedure:
1 public static void GetEmployeeName(
2 String inEmpID,out String outEmpName)
{
3 DB2Command myCommand = DB2Context.GetCommand();
4 outEmpName = "No Data!";
5 try
{
6 myCommand.CommandText = "SELECT FIRSTNME, MIDINIT, LASTNAME FROM EMPLOYEE " +
7 "WHERE EMPNO = '" + inEmpID + "'";
8 DB2DataReader reader = myCommand.ExecuteReader();
9 if (reader.Read())
{
10 outEmpName = reader.GetString(0) + " " + reader.GetString(1) + ". " + reader.GetString(2);
}
11 reader.Close();
}
12 catch( DB2Exception db2e )
{
13 outEmpName = "DB2Exception: " + db2e.Message;
}
14 catch( Exception syse )
{
15 outEmpName = "Exception: " + syse.ToString();
}
}
|
Line |
Description |
|---|---|
| 1 | Declares a method named GetEmployeeName. |
| 2 | This method takes two String parameters. The first parameter is an input parameter called EmpID. The second parameter is an output parameter called EmpName. |
| 3 | Specifies an instance of DB2Command to establish DB2 connection information required to run SQL statements in a procedure. The instance of DB2Command uses the ConnectionString property for the connection associated with the procedure to identify a database connection to run the SQL statements. |
| 4 | If EmpID is null, then "No Data" gets returned. |
| 5 | Starts a try block to handle exceptions. |
| 6 - 7 | myCommand is an instance of DB2Command, which is used to run an SQL statement. |
| 8 | Retrieves the result set of myCommand that is run by the DB2Command instance. |
| 9 | Reader examines the returned row from the SELECT statement. |
| 10 | If a row is returned, the outEmpName parameter is set to the first name, middle initial, and last name of the employee. |
| 11 | Closes the Reader. |
| 12 - 13 | If the code in the try block throws a DB2 exception, then the outEmpName is set to the DB2 exception error message. |
| 14 - 15 | If the code in the try block throws any other exception, then the outEmpName is set to the error message that corresponds to that exception. |
1 CREATE PROCEDURE SCHEMA1.GETEMPLOYEENAME (
2 IN INEMPID VARCHAR( 254 ),
3 OUT OUTEMPNAME VARCHAR( 254 ))
4 SPECIFIC SCHEMA1.GETEMPLOYEENAME
5 LANGUAGE JAVA
6 PARAMETER STYLE JAVA
7 NO DBINFO
8 FENCED
9 MODIFIES SQL DATA
10 DYNAMIC RESULT SETS 0
11 PROGRAM TYPE SUB
12 EXTERNAL NAME '"[!DB2CLRAssembly WindowsApplication6.dll]":WindowsApplication6.Form1!GetEmployeeName'
@
13 GRANT EXECUTE ON SPECIFIC PROCEDURE SCHEMA1.GETEMPLOYEENAME TO PUBLIC
@
|
Line |
Description |
|---|---|
| 1 | Declares a procedure named GetEmployeeName. |
| 2 - 3 | This method takes two String parameters. The first parameter is an input parameter called EmpID. The second parameter is an output parameter called EmpName. |
| 4 - 12 | Shows the CREATE PROCEDURE statements, including where the original C# method resides. See the IBM DB2 SQL Reference for more information about these keywords. |
| 13 | Shows the GRANT EXECUTE ON SPECIFIC PROCEDURE statement. This statement authorizes any user to run this procedure. |
Creating DB2 CLR Procedures | Changing CLR Assembly Properties | Managing Procedures, Functions, and Objects