We are using the ADO.NET implementation from IBM to access a DB2 database on an AS400. There is a Db2Connection object taking a connection string - everything is pretty standard (lots of examples out there) and all is working fine.
Our problem is that we have to do a large migration and the migration job needs to do quite a bit of querying on the database. Our migration can be split in several tasks running in parallel - unfortunately the performance gain is not linear (double amount of threads < double speed). Our tests show that the bottleneck is the Db2Connection - we assume it has to do something with the pooling. We are only reading from the DB2 database and saving the data in a SQL database and there we do not encounter this issue (reducing the db2 reads but keeping the sql writes increases the speed significantly). We tried to play around with the MinSize/MaxSize values of the pools and the amounts of the threads but the effect seems to be minimal - seems like the queries from our parallel tasks are being executed in serial.
So my question: Is there some upper limit regarding the amount of active connections (open readers) for a Db2Connection pool? Setting the MaxSize in the connection string has no impact, so does the server has any limit? Or what else could be throttling our queries?
We were not able to really solve the problem. However, our solution was to first copy the entire data from the db2 database to a temporary sql database (using BulkCopy) and then migrate the data from there to the "real" new sql database. This way migration was speeded up from around 12h to 30min (BulkCopy takes about 5min).
As it is basically the same code and concept doing the migration from db2 to sql and now from sql to sql I have no idea what is causing the performance loss with db2. I assume it is some flaw in the implementation logic of the IBM tools.