Performance tuning of Job

sam334 · Post by **sam334** » Fri Feb 13, 2015 2:44 pm

All, Have a question on performance tuning.

We have a job design like,

FTP PLUG IN -- TRANSFORMER--DB2 CONNECTOR.

It is almost 14 million record refresh everyday and taking almost 22 minutes to run. In log, it says, FTP Plug in "Logging delayed metadata" and finishes the job in 22 minutes. partitioning is AUTO and data is not sorted anywhere.

Any tips how to do an performance tuning. Job is running on 4 node configuration file and output table has one column as Primary key.

Source is delimited file which is in unix server.

Thanks,

PaulVL · Post by **PaulVL** » Fri Feb 13, 2015 4:01 pm

Like any performance tuning, you have to identify where your bottleneck is.

Since you are using a Parallel job and not one of those pesky server jobs... You can drop a peek stage into your job. Make two other jobs:

FTP Stage --> Peek

FTP Stage --> Transformer --> Peek

This will help narrow down your speed issue location.

Is the table you are loading into a partitioned table?
Is your data keyed in the stage based upon that column?
Maybe sort the data?

FTP Stage --> Transformer --> Sort --> DB2 Con

Is the DB2 database configured to be local or remote to your ETL box?

chulett · Post by **chulett** » Fri Feb 13, 2015 4:03 pm

PaulVL wrote:Since you are using a Parallel job and not one of those pesky server jobs... You can drop a peek stage into your job.

PaulVL · Post by **PaulVL** » Fri Feb 13, 2015 5:06 pm

Ya... I didn't think that would slip by without a nudge nudge...