The source data count 600000 records. the job is running 15 hours to complete in sit environment.
the same job completed in the development environment is 3 hours.
As we are taking the help of datasage admin and DBA to moniter the process why it is taking lot of time. they said it is not occupying not that many resources. they are unable to figure out the root cause.
i have searched the fourms realted but dod not find the similar issue.
Don't answer "nothing" because that's clearly not the case.
For example, are the tables indexed on the key in dev and not in sit? Or is the database local to dev and remote from sit? There are several other possibilities.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
First check we have done indexes on the tables. both are in sync.
second data stage sctrach space. we have enough space.
third check compared two jobs any change in the jobs(different environments). jobs are same.
Any other check which we dont have access to the admin side to monitor the job.
What other differences may there be between the two systems? Hardware? Physical location? Are the network connections different on SIT and DEV? Perform a traceroute and ping from each DS server to each database server (sources and targets).
Also look at the DB2 client configuration on each DS server.
What about how you're running the job? Degree of parallelism on SIT vs DEV?
What kind of throughput do you get from the source table on dev and sit? Create a job which just reads the table and dumps to a peek or copy and see what the difference in runtime is on the two environments. If you can do so with the targets, run a job that is a row generator going into the DB2 stage and write to a temporary table on each of the target database servers.
This above is just a list of things to check in the process of elimination.
Regards,
- james wiles
All generalizations are false, including this one - Mark Twain.
A lot of factors can play into database deletes. The tables primary key may be foreign keys in several different tables. It has to go check for the existence of child before actually deleting the record. Then depending upon the constraint definition, it may be delete cascade or do nothing.
If this is zos DB2 then it also depends upon how much load is on the cpu, if zip processors are involved etc.
So depending upon everything that goes around under the covers the normal time may be 5 mins or may be 5 hours.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.