On several projects I’ve been involved in, have had some kind of interface to the mainframe universe. Therefore also typically some kind of access to the IBM DB2 database. Some customers have made their own layers, abstracting the access away from .NET e.g. by using web services, while others do direct access to DB2 from .NET.
For the later part, I had the opportunity of writing the datalayer for such in a solution that required a lot of insert statements. In a SQL Server environment, you would typically do a BULK insert operation within a transaction and you could also do this with IBM DB2. However only to a certain extend, as far as I know it is quite possible to do BULK inserts with DB2 – but when it comes to BULK operations within transactions, there’s no way.
So inserting a lot of rows, has to be done in the old fashion way – and that’s definetly not very fast!!!
During some optimizations of my code and researching the wonderfull IBM documentation, I came across something called “chaining”. This isn’t very well documented, but it is a feature you can turn on, on the DB2 connection (just like a transaction), and when running in a chain the DB2 driver will package the calls to blocks of calls, that’ll be send to DB2. Again: this means a significant speed improvement and this even works wihtin transactions!
DB2Connection conn; // This holds the open DB2 connection conn.BeginChain(); // ... Do your SQL magic here ... // If you need it in a transaction, just put this handling around the // chaining parts. conn.EndChain();
