Datastage 8.0.1 optimnization

friend.kak@gmail.com · Tue Jul 07, 2009 4:34 am

Hi,
We have a requirement from the client to fine tune the jobs that are running for long time in production.

Environment details in brief:

Version : IBM WebSphere Datastage 8.0.1 (Sun OS 5.10)
Sources : Teradata,Oracle,Flat files Target : Teradata
Transformation rules : Complex
Nodes : 1 nodes in production ( config file)

From the observations, ETL tool, Datastage is only used when the business rules / functionality cannot be implemented using Teradata's SQL or via BTEQ.Loading is implemented through Datastage only with TD Multiload , TD Enterprise.

Please post your high points on the same which can help to fine tune the jobs.

Thanks, friend.kak@gmail.com

ArndW · Post by **ArndW** » Tue Jul 07, 2009 4:41 am

When you run your job is the CPU use at over 90% for long periods of time, or is I/O overloaded or does the machine swap? Without knowing where your bottleneck is one cannot recommend tuning.
Although a 1-node configuration doesn't give PX a chance to take advantage of any parallelism whatsoever.

vmcburney · Post by **vmcburney** » Tue Jul 07, 2009 7:41 pm

A one node DataStage configuration for a Teradata environment is insane. Like feeding an elephant with a teaspoon. I am guessing you have performance problems in your ETL and in the ELT Teradata steps. You might be using the wrong Teradata load options - it often depends on incoming volume as to the best load method. You will find it hard to tune those BTEQ scripts. You need to get yourself a *DataStage* expert who has solution architecture knowledge, not a *Teradata* expert but someone who understands how to balance load across ETL and ELT. Turn your DataStage steps into a parallel architecture with a lot of grunt, rewrite the worst BTEQ steps into DataStage steps, move the heavy lifting off Teradata and onto a massively parallel DataStage cluster. Review how data is pushed into Teradata - have a look at the blog post from Joshy George IBM Information Server / Datastage Enterprise Edition with Teradata.

ArndW · Post by **ArndW** » Tue Jul 07, 2009 11:19 pm

Vincent - you were so much more direct than I - do you think I was being too subtle regarding a 1-node configuration

Compare my

1-node configuration doesn't give PX a chance to take advantage

with your somewhat more forceful

A one node DataStage configuration for a Teradata environment is insane

ray.wurlod · Post by **ray.wurlod** » Wed Jul 08, 2009 12:40 am

I did like "feeding an elephant with a teaspoon"!