Speed of records processing is decreasing with the time

singhald · Post by **singhald** » Mon Jun 25, 2007 2:18 am

Hi DS Gurus,

I am running so many DS job which having same type of
structure. all the job processing incomming flate files pipe
delimited data and looking into vary larger table's primary key column
and number of records in table are very huge(aroung 500000000). when i started to run all the Datastag jobs which are refering different different table , the speed of the record processing was 500 records per second but when i saw all the job after 2 days the speed was only 50 records per second,
all the jobs are still running .

could any body explain me why it is happening .

Now i want to spped the records processing but dont want to restart
datastge jobs.

Please give me some useful advice to achive good result.

ArndW · Post by **ArndW** » Mon Jun 25, 2007 2:29 am

Talk to your DBA; this symptom is common when you have indices on the table. Have the DBA monitor what is going on inside the database. If you make a copy of your job and write to a sequential file instead of to the table(s) {make the file /dev/null for real speed} you will see that the job will most likely run much faster, but also that it will perform at a relatively constant speed; this shows that the cause is not in the DS job itself but in the database.

singhald · Post by **singhald** » Mon Jun 25, 2007 2:32 am

Hello Arndw

I am not writing records into table , I am just creating the flate file only.

Thanks
Sid

ArndW · Post by **ArndW** » Mon Jun 25, 2007 3:20 am

Is the database on the same machine as the DataStage server? Is the machine paging or maximizing I/O or at 100% CPU?

singhald · Post by **singhald** » Mon Jun 25, 2007 3:36 am

Hi Arndw

Database is on same machine. but i am not aware about the machine paging ?

Regards,
Sid

rameshrr3 · Post by **rameshrr3** » Mon Jun 25, 2007 4:51 am

Are you looking up to the database directly? I mean , do you have a refernce link connecting to database directly? If so load the database lookup data to a hashed file and use the hashed file as a source for your refernce links and see if you have the same problem.

singhald · Post by **singhald** » Mon Jun 25, 2007 5:39 am

hi Ramesh,

I am looking into Database(Informix) directly only based on Primary key field.
I have tried this wiht Hash lookup also. but creating hash lookup is not easy (becaose i am having huge amount of records in my each table (around 500000000 records ).
will it be feasible sol to create hash lookup instead of looking into table direcltly

Regards,
Sid