Thursday, June 30, 2016

Improving performance of your DB2 INSERT and UPDATE operations

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"}

No comments: