JOB run taking more time not throwing any error msg

[arasan]

We are involved in test run for 2007.one of the job normally in production it takes 1 hr time to complete the job.Now it is taking more time almost 24 hrs this job is in running state and it is not throwing any error message.


Job details:

the store procedure is called in the datastage designer to load the ENGR_JOB table.

triggers are placed on the engr_job table to perform an insert and update to the other table(ENGR_JOB_HIST).

the steps we have checked:
whether any locks r there on the table with the DBA

is there any way to perform an insert and update in a single SQL query.and anyone can you suggest what may be the cause for the above mentioned problem.

[satadru.kar]

which database u r loading? which stage u r using? r u trying to load same database at a time with more than one job?

[arasan]

database used is SQL server,

stage used is ODBC,

sorce and the target tdata base is same.I am not using more then one job.

[narasimha]

Run your Stored Procedure outside Datastage and check how long it takes.
Then you can find out as to where your problem is occuring.

[ray.wurlod]

Search the forum. Is the job really running, or are you relying upon what you see in Director (which is an entry in an RT_STATUSnnn table, and which may not have been able to be updated if the job aborted). Check to see whether the process ID still exists.

[arasan]

Hi ray,

yes i replied upon what i see in the director the job is still in running state.
for this issue isolation level may be the cause just geusing?

[arasan]

hi narasimha,

as you said i executed the store procedure.It is running succesfully and executed with in 10 min.

regards
arasan

[arasan]

hi ray,narasimha

l the same job if i run in the testing environment it is succesful when i try to run in the other environment IA(integration acceptance) it is taking such a klong time.

currently in ST (system test) for the job run it is taking 15 min to complete

[chulett]

Hi ray,

yes i replied upon what i see in the director the job is still in running state.

That wasn't the question. Specifically, the Director status (like 'Running') is just a flag. If the job in question dies in such a manner that it cannot update its status, the Director will show 'Running' forever. Same as if your server crashed with running jobs, on bringing it back up they would all still say running.

Did you check at the O/S and DB levels? Does this process still exist in both places?