I come to bury UniOLEDB; not to praise it.
Which won’t be a big surprise if you’ve seen by previous whining and kvetching on the subject.
At the time we adopted UniOLEDB, it was only ADO.Net-compatible driver for our IBM U2 UniVerse database, other than some third party products with expensive runtime licenses. UniOLEDB was never widely used in the U2 developer community, with preference given instead to the venerable but decidedly non-relational UniObjects.Net API.
UniOLEDB does work — our app has been running live on it for 9 months with decent reliability and performance. However, in my experience UniOLEDB had a few significant drawbacks, in descending order of priority:
1) It is not reliable when updating the database: INSERT and UPDATE commands would often complete without any error message but leave some fields untouched. We never could find a pattern to this problem (it was more common with dates and numeric fields, but would also occur with plain vanilla strings), and IBM support wasn’t able to recreate it. I reluctantly decided to use UniObjects for all database updates, thus giving up some of the advantages of ADO.Net.
2) It has an alarming tendency to raise a "protected memory" error — alarming both because of the panic-inducing but (as far as I can tell) totally misleading wording of the error message, and because it is quite frequent. Most users of our system will encounter this error a few times each day (though, needless to say, we replaced the error message text with something less worrisome). I never found a workaround for this.
3) It doesn’t play nicely with Visual Studio’s design-time database tools. If you use the Add Data Connection wizard to add a UniOLEDB database to Visual Studio you’ll promptly be greeted with the aforementioned "protected memory" error, and be unable to use the Data Connections tool to graphically view the database’s tables and fields. (You can, however, type in SQL statements and view their results).
4) For us, at least, it doesn’t work well with the latest version of UniVerse, 10.2. Our app would frequently encounter "Native error: 930065" exceptions — the error code indicates that the maximum number of database licenses has been reached, but it would occur randomly and with no apparent connection to license usage. IBM’s tech support was not able to recreate this problem, either, and though they tried adding patches in various point upgrades to 10.2, none of the patches worked for us.
5) In order to make the non-relational UniVerse database accessible to a relational database driver, a large amount of effort must be spent tinkering with the UniVerse data dictionary trying to map out the data. Even then, you’ll almost certainly end up with some parts of the database that UniOLEDB can’t read without resorting to the native UniVerse delimited string format. I list this as the least of the problems only because it’s one that I never really expected UniOLEDB to solve. As Barack Obama might say, "you can put lipstick on a multivalue database, but it’s still a multivalue database". (No, not you, Sarah — I’m talking about UniVerse).
Late in 2007 IBM finally released a native ADO.Net driver for U2 which, for lack of a better name, I’ll refer to as "the ADO.Net driver". (Not very ingenious, I admit, but inarguably a better name than IBM’s. Depending on where you look, IBM refers to it as the "IBM Data Server Provider for .NET", or the "IBM Data Server Client" or the "DB2 9.5 Client".
This driver, when combined with a separate piece of software called the "IBM Database Add-Ins for Visual Studio", was primarily targeted at addressing the third of the above problems. However, given that U2 is a very small fish in IBM’s large database pond, I found it particularly reassuring to see that this is part of a unified driver which also covers their flagship DB2 product along with the database business they bought from Informix. Since this driver was, on the client side, a complete rewrite when compared to UniOLEDB, I was pretty confident that it would also fix the fourth problem, and somewhat optimistic about finally solving the first two.
About a month ago we finally found time in the project timetable to begin porting our UniOLEDB code to the ADO.Net driver. Since the new driver is fully supported only in UniVerse 10.2, and all of our user sites are still on 10.1, we have to support both UniOLEDB and ADO.Net for the time being. So, I was hoping that the conversion would require relatively few changes to our code.
I’m pleased to report that it was a pretty simple conversion. On the server side, ADO.Net requires the same dictionary entries as UniOLEDB (and ODBC before it), so there was virtually nothing to be done within the database itself. On the client side, a new namespace is required, IBM.Data.DB2. In this namespace are "DB2"-specific replacements on a one-for-one basis for the OLEDB objects, such as connections, data adapters and parameters. After making these 1-for-1 substitutions, almost all of our code "just worked". We retested every SQL statement in the application and found only two compatibility problems between UniOLEDB and the ADO.Net driver, as described below.
Here’s a blow-by-blow description of what we needed to do to port UniOLEDB to ADO.Net:
1. Download and install the ADO.Net client software. This step is actually incredibly difficult, thanks to IBM’s byzantine web site. Currently the right page to use is http://www-01.ibm.com/software/data/db2/support/db2_9/download.html, and the file you are looking for is the "IBM Data Server Driver for
ODBC, CLI, and .NET" (as opposed to the "IBM Data Server Client" or the "IBM Data Server Runtime Client"). The file is named v9.5fp2_nt32_dsdriver_EN.exe (where fp2 refers to Fix Pack 2). The Visual Studio Add-In is somewhat easier to spot, and is named v9.5fp2_nt32_vsai.exe. Once you have the files, I’d strongly recommend that you use IBM’s excellent series of tutorials (Parts one, two and three) to walk you through installation and configuration, and to familiarize yourself with what the new driver can do.
2. OK, back to your code. As the tutorial explains, you’ll need to add a reference to your project to IBM.Data.DB2.dll in order to get the IBM.Data.DB2 namespace. Naturally, you’ll need to declare this namespace (e.g. "using IBM.Data.DB2") at the top of your source code, along with System.Data.Common. You should be able to remove the "System.Data.OleDB’ declaration.
3. Replace the OleDBConnection object with a DbConnection object.
4. Replace the OLEDB Connection string parameters with those used by the DbConnection object. You’ll need to add a new "ServerType" parameter and set to to "Universe" (since IBM’s unified ADO.Net driver wouldn’t otherwise have any idea that you want to use a UniVerse database). Since connection pooling is turned on by default, unless you have a UniVerse license that supports pooling you’ll also have to add a parameter to turn it off: set "Pooling" to "false". A couple of other connection string parameters, for the server and database, now have new names. When all is said and done, you should have a connection string that looks like this:
string strConn = @"User ID=JoeUser;Password=HisPassword;Database=c:accountpath;Server=ourserver;ServerType=universe;pooling=false";
Incidentally, you can now do away with the cryptic "uci.config" initialization file used by UniOLEDB. The ADO.Net driver does not require any configuration beyond what is in the code. Welcome to the 21st century!
5. Certain OleDB database objects need to be replaced with their ADO equivalents. Note that the syntax for DataReaders, Commands and Parameters is quite similar, with the main difference being that the ADO objects don’t have their own constructors. The DataAdapter, on the other hand, requires considerably more code to create using the ADO.Net driver:
- OleDbCommand is now DbCommand
// UniOLEDB
public static OleDbCommand DB_GetCommand(string strSQL)
{
OleDbCommand cmd = new OleDbCommand(strSQL, conn);
return cmd;
}
// ADO.Net
public static DbCommand DB_GetCommand(string strSQL)
{
DbCommand cmd = conn.CreateCommand();
cmd.CommandText = strSQL;
cmd.CommandType = CommandType.Text;
return cmd;
}
- OleDbDataReader is now DbDataReader
// UniOLEDB:
public static DbDataReader DB_GetReader(string strSQL)
{
OleDbCommand cmd = new OleDbCommand(strSQL, conn);
DbDataReader rdr = cmd.ExecuteReader();
return rdr;
}
// ADO.Net:
public static DbDataReader DB_GetReader(string strSQL)
{
DbCommand cmdSQL = conn.CreateCommand();
cmdSQL.CommandText = strSQL;
cmdSQL.CommandType = CommandType.Text;
DbDataReader rdr = cmdSQL.ExecuteReader();
return rdr;
}
- OleDbDataAdapter is now DbDataAdapter
// UniOLEDB:
public static OleDbDataAdapter DB_GetAdapter()
{
return new OleDbDataAdapter();
}
// ADO.Net:
public static DbDataAdapter DB_GetAdapter()
{
DbProviderFactory factory = DbProviderFactories.GetFactory("IBM.Data.DB2");
DbDataAdapter adapter = factory.CreateDataAdapter();
return adapter;
}
- OleDbType is now Db2Type (since we needed to support both UniOLEDB and ADO.Net, I changed our code to use the generic DbType enum and added conversion methods for the types that we use):
// UniOLEDB:
public static OleDbType DB_GetOleDbType(DbType type)
{
switch (type)
{
case DbType.Date:
return OleDbType.DBDate;
case DbType.Decimal:
return OleDbType.Decimal;
case DbType.String:
return OleDbType.VarChar;
case DbType.Int32:
return OleDbType.Integer;
}
}
// ADO.Net:
private static DB2Type DB_GetDB2Type(DbType type)
{
switch (type)
{
case DbType.Date:
return DB2Type.Date;
case DbType.Decimal:
return DB2Type.Decimal;
case DbType.String:
return DB2Type.VarChar;
case DbType.Int32:
return DB2Type.Integer;
}
}
- OleDbParameter is now DbParameter
// UniOLEDB:
public static void DB_AddParameter(OleDbCommand cmd, string strName, DbType type, int intSize, string strSourceColumn)
{
OleDbType oleDbType = DB_GetOleDbType(type);
cmd.Parameters.Add(strName, oleDbType, intSize, strSourceColumn);
}
// ADO.Net:
public static void DB_AddParameter(DbCommand cmd, string strName, DbType type, int intSize, string strSourceColumn)
{
DB2Type db2Type = DB_GetDB2Type(type);
DbParameter param = new DB2Parameter(strName, db2Type, intSize, strSourceColumn);
cmd.Parameters.Add(param);
}
6. Once all of the tedious but straight-forward "a Quarter Pounder with Cheese is now a Royale with Cheese" stuff is out of the way, you should find that the two drivers speak pretty much the same language and not many other code changes are required. When we fired up the app and tried out our SQL statements, we only found two cases where the same statement didn’t return the same data:
a. Subvalue markers (ASCII 252) are returned by the ADO.Net driver as carriage-return/line-feed ("/r/n"). Subvalues are a method used by UniVerse to stuff 3 levels of data into a single field: if a multivalued field can be thought of as representing a parent-child table relationship, then subvalues can be thought of as parent-child-grandchild. We don’t use them much, and it seems that most other developers don’t either, since IBM support wasn’t aware of this when I reported it to them. However, IBM intends to resolve this problem in Fix Pack 3.
b. The ADO.Net driver doesn’t support use of the virtual "@ASSOC_ROW" field in SQL statements. The UniOLEDB driver stored a row number in this field when converting a multivalued field into multiple virtual rows. SQL statements that rely on row numbers are almost certainly not a good idea in any database — you should be able to rewrite the SQL to avoid using them.
And there you have it — with a relatively small and straightforward set of code changes, you’re now accessing your UniVerse data with a true-blue ADO.Net driver.
As for the set of problems listed at the top of this article, I can report that the new driver solves problems #2 and #4. I haven’t tried doing database updates with ADO.Net yet, so I can’t say if problem #1 is solved. I noticed that IBM’s series of tutorials for the new driver recommends that database subroutines (written in UniVerse Basic) be used to handle updates, so I plan to stick to using UniObjects for the time being. 
We haven’t completely cracked problem #3 yet, partly because of the unavoidable "multivalued database with lipstick" issue. The Visual Studio add-in works great with the sample HS.SALES account, and every crusty old UniVerse Basic programmer that I’ve showed it too is quite surprised to see a graphical database design tool that works with UniVerse. Unfortunately, when we tried accessing our real world database we ran into a serious performance problem. HS.SALES has 749 entries in its master dictionary — our database has over 5 thousand. As a result, it took Visual Studio a few minutes to enumerate all of our schema objects. A few minutes might not seem so bad, but this enumeration is done not only when the database’s object tree is first displayed, but each and every time a new query is created from the Data Connection. It’s really cramping our style!
However, there is hope that Visual Studio and UniVerse can now get along. IBM provides a few different ways to filter the number of schema objects that are available to Visual Studio, and we’ll be trying out those methods in the future. I’ll write about that in a future article.