IC4NOTICE: developerWorks Community will be offline May 29-30, 2015 while we upgrade to the latest version of IBM Connections. For more information, read our upgrade FAQ.
1 reply Latest Post - ‏2013-06-21T21:14:30Z by hclewelad
75 Posts

Pinned topic Updating table

‏2013-06-21T11:40:21Z |

Hi All,


Currently we are using a for-each loop & dp:sql-execute() to update records in the sql-server table. We are fetching these records from a "DB2" database. 

In order to update around 5k records in each run, it's taking close to 2 hours.


Is there any other alternative approach (without using the for-each loop) with which we could reduce the processing time?



  • hclewelad
    21 Posts

    Re: Updating table

    ‏2013-06-21T21:14:30Z  in response to Developer11

    I've had great results with just using big sql statements that manipulate multiple rows since this minimizes the connection overhead.

    Here's a good stackoverflow link:

    This approach works better for inserts, but since there doesn't seem to be anyway to turn autocommit off, which I suspect in db2 is your nemesis on doing all those individual updates. We use DP and DB2 where I work as well and I have seen this kind of performance issue. 

    I hope this helps, and I hope you find a setting in the datapower Control Panel->Objects->Network Settings->SQL Data Sources that helps. The only one I see that might help is maximum connections, but it looks like you are doing things sequentially.

    Take care,