Server job running Longtime

[vemisr]

Hi experts,

I have Server job (7.5 AIX) Prod. extracting data from DB2 then loading into Sequential file. job started still showing runing more than 40 Mins.
expected volume of records 60,000 records only.

when ran the same Db2 Query on Control centre executed in 1 min.

ran the same job in Dev region with 200 records completed in 00:00:05 sec.

let me know what are the possible issues

thanks
Vemi

[chulett]

Could be many reasons.

So... is it returning records very slowly or no rows were returned for a 'long period of time' and then they all flew out? Something else?

[ray.wurlod]

200 records in 5 sec. Do the arithmetic. 60,000 records in X sec.
X = ?

What are your expectations, and why?

Is there an ORDER BY clause in the query? What else is the job doing while the clock is running? Do you include the time it takes for DB2CC to connect to the database in your timings? Do you count the time DB2CC takes to return the first row, or to return all rows?

[Abhijeet1980]

Friend,

Use DS job monitor to see the progress and the speed at which records are being fetched.

Replace your query projection clause (between SELECT and FROM) by count(1) and give it to your DBA.

Rest follows automatically.

Regards,
Abhijit

[Akumar1]

Stop the job, Try re-running the same job with less data, and see if you are getting the same issue.

[vemisr]

in development i have less records(500) so it's ran very quickly.

but in production it's nearly 60 K records. taking more than 2 hrs. that's the problem.


how to tune the Database . because it's running (completed) 1 min

[chulett]

You tune the query not the database, and that's a conversation you'd need to have with your DBA or someone else onsite with you.

[Akumar1]

As you are saying that same job is running fine in dev but not in prod. I wud suggest you to run the same job in production with less number of records e.g. 100 records , this is just to identify that where is the problem.

[Sainath.Srinivasan]

I wud suggest you to run the same job in production with less number of records e.g. 100 records.

How do you intend to do this without changing the job and what if the target is a load into some table which cannot be backed out easily ?

Check whether all DB2 jobs are having problem or only this specific one ?
Also check whether there is any join condition which is doing a full scan.

[ray.wurlod]

I wud suggest you to run the same job in production with less number of records...

Please strive for a professional standard of written English on DSXchange. The correct spellng of the word is "would", not "wud".

More pedantically, perhaps, a better formulation is "fewer records" rather than "less number of records".