We seem to have a situation brewing that is causing jobs to run critically slow. By that I mean I can see database activity related to inserts running around 15 per minute. I would expect tens of thousand's of inserts per minute. This has only recently started and is somewhat rare. We are on 7.5.1a
As the symptoms go, a job runs fine for several executions and then suddenly it has a runtime of 6+ hours when it used to be maybe 15 minutes. The job is cancelled and rerun and bang - it finishes in 15 minutes like it is supposed to.
My best guess at the moment is that DataStage takes a snapshot of the resources at the time of initial execution and somehow limits the job to only those resources even if addition resources become available. We are running on a partitioned AIX server along with SAS (I don't get to make decisions on what software goes in what partition, so I am stuck running along side of SAS). There are always ETL jobs running and SAS users "SASing" and I don't have a system level view of what is going on. I do have the cooperation of the AIX Systems Staff the next time this occurs.
I have watched the jobs run extremely slow from my database monitor and have also seen these long running jobs cancelled and then run very fast. My position is a database architect/admin with responsibilities to administer the suite of tools and am not (in any sense of the word) a DataStage Developer. I am looking for some insight as to what would cause the behavior I have described. Has anyone come across this or have any idea of where I should be looking?
