DSXchange

Hi,
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?

Hi

You need to find out if the problem is really due to your database. First thing to do would be to swap out the database stage that performs the writes to writing out to a dataset (or even a sequential file).
If the job performs well writing to files or datasets then you have a database performance issue.

JS

John Smith wrote:Hi

You need to find out if the problem is really due to your database. First thing to do would be to swap out the database stage that performs the writes to writing out to a dataset (or even a sequential file).
If the job performs well writing to files or datasets then you have a database performance issue.

JS

It's definitely not the database. It is our main transactional database that has and average of 1200 connections and runs the company. If it were the database I would not have a job right now (I manage the databases in the company as my primary position) :D