Array size in DB2UDB stage

kaps · Post by **kaps** » Fri Sep 08, 2006 2:07 pm

I am specifying Array Size as 1 in my DB2UDB Input stage while I am writing to the table. I have done this to enable the dynamic lookup conecpt meaning I am using the Target table as my lookup and I want to capture multiple state of same enitity. i.e.If a record is updated more than once a day I want to capture all of them.

Can anyone tell me does the array size value being 1 affects the performance if we are loading million fat records ?

Thanks

Mike · Post by **Mike** » Fri Sep 08, 2006 4:44 pm

It's likely that an array size of 1 won't be the optimal size. It determines the number of rows held in a memory buffer before they are written out to the DBMS. Only you can determine the most optimal value. It depends on whether the DBMS is local or remote, how fast the network connection is between the ETL server and the DBMS server, how heavily loaded the network is, how heavily loaded the DBMS server is, how heavily loaded the ETL server is, etc. Given the design where you are doing a lookup on the target table, you really have no option other than using an array size of 1. I would be a lot more concerned about the 1 million SELECT statements that your lookup is doing.

Mike