Performance degraded after migration from Datstage 7.5 to 8.

swarnkar · Post by **swarnkar** » Thu Dec 01, 2011 9:52 am

Hi All,

We have recently migrated Datastage Project from 7.5 to 8.5, But we are facing performance issues, The old project and new project, both are on same UNIX box. There are some jobs in new project taking as much as double the time of what they were taking in old project.

We have taken a job, having performance issue . The Design of the job is as given below.

DRS_SATGE --> Transformer + Lookup --> DRS_STAGE

The Source and target tables are in Oracle database.

and in order to find out bottleneck, we devided the Job into three jobs

Job1
------------------------------------------------
DRS Stage --> Sequential_File_1

Job2
-------------------------------------------
Sequential_File_1 --> Transformer + Lookup --> Sequential_File_2

Job 3
---------------------------------------------------
Sequential_File_2 --> DRS_STage

We run these three jobs in Datastage 7.5 and 8.5

The Performance of Job2 is almost same in both the version of Datastage,
But the performance of Job_1 and Job_3 in 8.5 side is not comparable with 7.5 side.

We replace DRS stage with OCI stage but it didn't help much.

Are there any settings or parameters which need to fix in 8.5 .

Please suggest.

Thanks,
Nitin Swarnkar

kamtammystuff · Post by **kamtammystuff** » Thu Dec 01, 2011 11:19 pm

What does your Datastage Admin has to say about this?

ray.wurlod · Post by **ray.wurlod** » Fri Dec 02, 2011 12:40 am

There is always some performance hit in server jobs on version 8 over version 7.5 because of the new environment in which things are operating. I've posted about this in the past. In short, though, everything executes in a service-oriented architecture running on a parallel execution engine. To communicate with this, server jobs have to undergo a "translation" process.

swarnkar · Post by **swarnkar** » Fri Dec 02, 2011 11:46 am

Thanks Ray, Thanks kamtammystuff

The Admins are working with Parameters like MFILES,TFILES,ULIMIT. Array Size and Transaction Size. And also opened a ticket with IBM.

Thanks,
Nitin Swarnkar

ray.wurlod · Post by **ray.wurlod** » Fri Dec 02, 2011 3:37 pm

Let us know if any of that will make a difference. I predict that it won't make much difference at all.

roy · Post by **roy** » Sun Dec 04, 2011 6:26 am

In my humble opinion, the fact that the only jobs that are connecting to the database are significantly slower, implies that the problem is your database connection.
I can't say if the actual connection or perhaps some database optimezer issue or your network configuration, is the one rsponsible but those seems to be the the ones to check.
IHTH(I Hope This Helps),

tonystark622 · Post by **tonystark622** » Wed Dec 07, 2011 8:20 pm

Something to lookup... On the DRS stage make sure that Array Size is 1000 or greater

swarnkar · Post by **swarnkar** » Thu Dec 08, 2011 11:51 am

Hi All,

The IBM suggested to disable the NLS settings and that does improve the performance.

In Datastage 7.5, job was processing 11000 rows/second. In Datastage 8.5, with NLS enabled it was processing 4500 rows/sec and after desabling NLS, it become 8500 row/sec.

Admins are still in touch with IBM. I will keep you posted.

Thanks,
Nitin Swarnkar

ray.wurlod · Post by **ray.wurlod** » Thu Dec 08, 2011 3:03 pm

Was NLS enabled or disabled in 7.5 ?

swarnkar · Post by **swarnkar** » Mon Dec 12, 2011 2:48 pm

Ray,

It was not enabled in 7.5

Thanks,
Nitin Swarnkar