• 1 reply
  • Latest Post - ‏2013-06-21T21:14:30Z by hclewelad
130 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
    27 Posts

    Re: Updating table


    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,