The art of simplicity

On a project where we are using DB2, we have to do a lot of bulk inserts and updates. We investigated the bulkcopy option, but in the end we decided to switch to an alternative option supported by DB2; “chaining”.  Chaining will bundle a set of calls and send them in one package to DB2. This has a major speed improvement compared to inserting/updating a lot of rows. Note: The main reason we switched to a different approach  is that the Bulkcopy option in DB2 has limited support for transactions. Here is how to use it: Remark: When using BeginChain/EndChain you cannot combine this with SELECT operations. You end up with exceptions similar to the following one: {"ERROR [HY010] [IBM] CLI0125E  Function sequence error. SQLSTATE=HY010\r\nERROR [HY010] [IBM] CLI0125E  Function sequence error. SQLSTATE=HY010"}

With every new releases of the Microsoft OS, I’ll have to go through the same pain to get the DB2 providers working. IBM seems not able to release a driver that works out-of-the-box. So what are the hacks this time to get it working? Start with a normal installation of the DB2 client on your system(don’t forget to run as an administrator). The installation will end successfully but  when you try to connect to a DB2 database, you’ll probably get an error similar to this one: “sql1159 initialization error with db2 .net data provider reason code 7”   Go to Start -> Programs -> Visual Studio -> Developer Command Prompt. Open the prompt as an administrator Add the following assemblies to the GAC using gacutil: gacutil /if "C:\Program Files\IBM\SQLLIB\BIN\netf40\IBM.Data.DB2.dll" gacutil /if "C:\Program Files\IBM\SQLLIB\BIN\netf40\IBM.Data.DB2.entity.dll" gacutil /if "C:\Program Files\IBM\SQLLIB\BI...

I have to admit that I’m not a big fan of DB2 although there is not much wrong with the database itself. I have more problems with the far-from-perfect tooling. One of the things that keep annoying me are the cryptic error messages including an even more cryptic SQL error code that DB2 returns. Normally I look up the error code in the DB2 Information Center , but if you need a shorter and faster way to get at least a short explanation of what an error code means, you can use the following SQL query: VALUES SQLERRM(-161) Result: 1 ------------------------------------------------------------------------------------------------------ SQL0161N The resulting row of the insert or update operation does not conform to the view definition.

To solve the growing need for more memory, we decided to upgrade all our development machines to a 64bit OS.  Although this allows us to install and use more memory on our machines, it also introduces a whole list of new problems. One of them was regarding the usage of the .NET transactionscope in combination with DB2. In DB2 managing your transactions through the transactionscope will immediatelly involve the DTC coordinator. However the moment the DTC tries to open a transaction, it fails with the following error message: The XA Transaction Manager attempted to load the XA resource manager DLL. The call to LOADLIBRARY for the XA resource manager DLL failed: DLL=C:\Program Files\IBM\SQLLIB\BIN\DB2APP.DLL, HR=0x800700c1, File=d:\w7rtm\com\complus\dtc\dtc\xatm\src\xarmconn.cpp Line=2446. After trying almost every possible solution, we finally found a solution that worked for us. When installing the DB2 client, it doesn’t correctly register the 64bit dll’s in the register. ...

After a really long waiting period, it’s finally there, a beta version of IBM’s .NET provider for .NET Framework 4.0 as well as Addins for Visual Studio 2010. This beta level package requires a V9.7 FP3a client install(available here: http://www-01.ibm.com/support/docview.wss?uid=swg24028317 ) , and has the following features: DB2 .NET provider for connectivity to DB2 LUW, IDS, DB2 for z/OS and DB2 for IBM i DB2 Connect license required for z/OS and IBM i Common IDS .NET provider for IDS Entity Framework provider Entity Framework support for database first scenarios Entity Framework canonical function support Full filtering of Add Connection properties when use with Entity Framework Designer Visual Studio 2010 Addins Full Server Explorer filtering support Windows, Web and WPF application development scenarios with full drag and drop Designer support for SQL Procedures with syntax highlighting Full end to end debugging for SQL procedures ...

This Friday, I had the most scariest exception ever: "System.Security.VerificationException: Operation could destabilize the runtime.” Sounds like all hell could break loose. How did I got this exception? I was trying the Entity Framework integration for IBM DB2 and I was trying to load an entity which had a timestamp column in the database. The issue was caused by the Entity Framework query translator which couldn’t understand how to map this database type could be mapped to a datetime in code. A more meaningful exception message would have been nice. It took me a lot of time to figure out the reason for this error.

One of the things that make working with IBM DB2 hard, are the very useful(read: useless) errorcodes you get back. No other information is returned so you don’t have a clue what’s going on. To find the corresponding error message, you have two options: Browse to the IBM site and search for the error. For people who have already tried finding their way in this site, it’s very easy to get lost. Search for the error message using the DB2 tools. Let’s have a look at the second option: Open a DB2 command window Execute "db2 ? [errorcode]" One sample. When the Error Code was SQL0668N than execute: 1: db2 ? SQL0668N This will return a list of reason codes and their explanation.

When running a very expensive query, I always got timeouts when running this query on Friday's ;-) (Yes, I’m loving the predictability of our DB2 database) The first thing I did to prevent the query from timing out was increasing the DbCommand timeout property: 1: var command= new DB2Command(); 2: //Increase timeout to 300 seconds 3: command.CommandTimeout=300; But after 60 seconds the query still timed out. So as I was using this query inside a TransactionScope , I also increased the transaction timeout: 1: //Initialize the transactionscope with a 300 seconds timeout interval 2: using (var transactionScope= new TransactionScope(TransactionScopeOption.Required, new TimeSpan(0,5,0)) 3: { 4: ... 5: } And even that was not enough, so I finally also increased the timeout interval of the DTC: The Transactions Timeout value is located in the My Computer Properties dialog, which can be available in "Administrative Tools -> Component Service ...

At one of my clients, I have the ‘luck’ to work with a DB2 database environment. After upgrading my system to Vista, I saw this heuristic processing error below: [IBM][CLI Driver][DB2] SQL0998N Error occurred during transaction or heuristic processing. Reason Code = "16". Subcode = "3-8004D00E". SQLSTATE=58005 From previous experiences, I learned it is caused by the distributed transaction coordinator that is always used when you’re opening a DB2 transaction inside a transactionscope.  By default in Vista, MSDTC settings are all locked down.  A blog post here describes how to use the dcomcnfg command to enable Inbound, Outbound and enable XA Transactions.  Enabling XA transactions and both inbound and outbound connections solved the problem.