Performance Enhancement

DWH-M · Post by **DWH-M** » Thu Dec 13, 2007 5:54 am

Hi,

I have a small question on performance enhancement. Currently we are having design where source is text file , has transformer,lookup/joiner and then target as DB2.

But when we test our jobs with small amount of data (10000 rows) it works fine, when we do volume test with 10 Million record the job is giving aborting because of tmout 68 code.

DBA suggested us to split job into 2 and in first job have target as sequential file and in second job use db2 load to load from sequential file to target table.

I was just wondering will performance improve if we load sequential file and then use db2load to load data in db2 ??

Thanks in advance.

AmeyJoshi14 · Post by **AmeyJoshi14** » Thu Dec 13, 2007 6:29 am

Yes it will surely improve the performance.

We were also facing the same problem.Our job structure was :

Sequnctial file-->transfomer--->lookup--->transfomer-->DB2

The main problem was we were using 2 transformers and a lookup stage.
When have done some performance tuning...we removed one transfomer prior ti lookup stage ...and we also removed the lookup and replaced it with join stage....

Try to search in job where you can eliminate lookup stage...it will be quiet helpful..

Maveric · Post by **Maveric** » Thu Dec 13, 2007 6:31 am

That will not increase the performance. But surely will solve the Timeout issue. Use a DataSet instead of sequential file for staging the data. This will be comparatively faster than sequential file.

ray.wurlod · Post by **ray.wurlod** » Thu Dec 13, 2007 5:55 pm

Two jobs with a Data Set (rather than a sequential file) will be even better in a parallel job.