Inconsistent run times

Post questions here relative to DataStage Server Edition for such areas as Server job design, DS Basic, Routines, Job Sequences, etc.

Moderators: chulett, rschirm, roy

Post Reply
justlrng
Participant
Posts: 30
Joined: Thu Oct 16, 2003 1:17 pm

Inconsistent run times

Post by justlrng »

Hi,

I have a datastage job that uses an OLEDB connection to a database, runs a script in the BEFORE SQL and then splits the data into one of three text files located on the network. The transform for this job is simple as it only checks the value of a column to separate the data into the files. I am testing so have only 2 to 4 rows that are being processed.

However, when I go to run this job, it takes way too long - on average about 45 minutes to an hour. The weird thing is, occassionally, the job will run in about 3 seconds - the way I expect it to.

I've tested the SQL Script used for running the job and it is fine. I've recreated the job. The files are okay. I just don't know what is going on. It really stumps me when it has run fine on occassion. That and my other DataStage jobs run just fine, even the more complicated ones.

Has anyone run into a problem like this and how would you go about to fix it?

Thanks
Michelle
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

Umm... only run it on the occasions when it runs in three seconds? :lol:

Check the log file and compare the job start and finish times with the active stage start and finish times. This will isolate the problem better.

When you run the job from Director, switch to the Tracing tab. Select the Transformer stage, and enable at least Subroutine Calls and Performance Statistics. This will give you a better handle on where the hot spot in your job is.

I really have no idea without access to such information. It may simply be inconsistent performance of OLEDB in the Windows environment.

Is the database local, or on a remote machine? If remote, how busy the network is will definitely have an impact, as your work is competing for bandwidth with all those emails with photos and other attachments that are flying around. Use a dedicated (and isolated) network segment if you want consistently good network performance.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Only other advice I can think of would be to work with a DBA during one of the 45 minute runs and see if you are hitting any locks or other problems in the database. :?

I had a job yesterday that literally takes 5 seconds sit for 15 minutes waiting for access to a table it needed to truncate and rebuild. Once I hunted down who was in it and had them quit out of TOAD for a moment - boom, job done. Perhaps something like that is going on?
-craig

"You can never have too many knives" -- Logan Nine Fingers
wnogalski
Charter Member
Charter Member
Posts: 54
Joined: Thu Jan 06, 2005 10:49 am
Location: Warsaw

Post by wnogalski »

See if Your process isn't blocked by some other process - run Query Analyzer and execute sp_who2 stored procedure to see all processes and locks.
Regards,
Wojciech Nogalski
justlrng
Participant
Posts: 30
Joined: Thu Oct 16, 2003 1:17 pm

Post by justlrng »

Thanks for all your suggestions. I will try them out and see if I can isolate the problem. I do know that the database tables being locked is not the problem as I am the only one accessing the tables and I have checked with the DBA a couple of times and the process is not being blocked so it has to be something else. I'll let you know when I figure it out.

Thanks.
Post Reply