Performance Issues

[shaimil]

Does anyone know the best way for me to figure out what's strangling my Datastage jobs.

I have several jobs that run in sequence, some parallel some serially. They either read from flat files or hashed files and all write to SQL Server tables. In between they do some very simple transformations involving at least one look up.

The input file in most cases is contains around 1.2 million records. The jobs run at anything between 10 - 35 rows/sec. This seems extremely slow to me.

Ultimately, i'd like to know what is considered a reasonable runtime, and how I can figure out whether I have read or write problems.

Any help is welcome.

Shay

[ogmios]

Be like Sherlock Holmes, kidding... this is what makes DataStage annoying but also fun at times.

Reasonable runtimes in my opinion (for average row lengths).
From Oracle to Oracle between 1000 and 2000 rows per second.
From DB2 to DB2 between 200 and 400 rows per second
I don't have much experience with SQL server.

The main items that affect DataStage:
- Volumes... whatever goes via DataStage has to be pulled in... transformed and then pushed out again. So if you can already constrain the data coming in on the database side please do so. I can assure you that pulling in 1.000.000 rows to DataStage and then constraining the number of rows to be processed to 300.000 rows in DataStage is much slower than only pulling in the 300.000 rows from the database.
- Location: If your input or output database servers are located at the other side of the globe you will get a slow stream of data trickling in.
- Wrong configuration of database server... Don't laugh but in fact I did once hear: "indexes, what are they... why do we need them?'
- Doing too much in DataStage: I know it's a pricey ETL tool but if possible do everything you can in the database: aggregation, calculations, joining, lookups (through joining), ... I would rather copy a table first from one database to another and then join in SQL than do a lot of lookups to get the data. Most of my jobs only have an input stage, transformer, output stage and an error file.

What you can try to do (what I try most of the times):
- How long does in monitor stay the number of your input stage to 0. If it stays on 0 for a few hours and then it starts running up you have a problem with your database input.
- Replace your output stage by a sequential file and rerun... if that goes blazingly fast you have an output problem. E.g. in the beginning we discovered doing updates in Oracle are very very slow. Changing the job design by erasing all rows in scope and then doing insert was much faster (this was a possibility in that one situation).
- Remove any unnecessary lookups (any lookup not used to constrain the rows being processed) and see how fast that goes. If it goes much faster consider co-locating a table and joining in the input SQL to get extra data into DataStage.
- ...

Any job functionally can made fast, but most of it in the end through re-engineering the way of processing; which takes in some cases a lot of time. You can split your input in subsets and that do processing parallel, you can try to change your source systems so that you get only relevant data back, ...

Ogmios

[richdhan]

Hi Shay,

When you run the job you can select the performance statistics checkbox from the Tracing tab in the Job Run option screen. You can come to know whether the problem is due to I/O limitation or CPU limitation.

For more details check chapter2 in servjdev.pdf.

HTH
--Rich

Pride comes before a fall
Humility comes before honour

[ogmios]

Another thing I noticed is that hash files are very fast on a server which is way below maximum capacity (development e.g.) but that speed quickly deteriorates when you've got a few hundred jobs using a lot of hash files: lots of memory uses, more swapping, slower speed.

Ogmios.

[chulett]

Generally, that can be mitigated by tuning some of the parameters in uvconfig like MFILES. There's been several posts on this here, including this recent one on the concept of the Rotating File Pool.

[ogmios]

Generally, that can be mitigated by tuning some of the parameters in uvconfig like MFILES. There's been several posts on this here, including this recent one on the concept of the Rotating File Pool.

It couldn't be tuned in my case ... If all of the physical memory is used, that's the end of the line. A lot of developers were making jobs and they all ran fine in development, the jobs got unit tested properly, .... but upon final system integration testing on the productive server we went from 1000 rows/s to 2 rows/s... just because of memory trashing. We ended up rewriting big pieces of jobs, avoiding hash files when possible.

Ogmios

[shaimil]

This has all been very useful thanks.

I've narrowed the problem down to the target load and the commit size. Can anyone tell me how I now optimise the Parameter Array Size. I'm currently using 250, but when i used a higher value the job gave a compile time error. Should this value be the same as the commit value and what is this value based on.

Thanks again
Shay

[ray.wurlod]

"Add more memory" is a valid response to a "running out of memory" symptom.
Sometimes hardware solutions are the easiest to implement, especially if you've tweaked everything else.