Improve Performance of Batch Jobs
I have batch jobs running for even 9hrs to generate reports depending on the input data. Can anyone let me know what parameters i can monitor so that i can actually find out the performance and improve the same. my database server is DB2 on Mainframe.
thanx in advance
Re: Improve Performance of Batch Jobs
<BLOCKQUOTE><font size="1" face="Verdana, Arial, Helvetica">quote:</font><HR>Originally posted by deepikalakshmi:
I have batch jobs running for even 9hrs to generate reports depending on the input data. Can anyone let me know what parameters i can monitor so that i can actually find out the performance and improve the same. my database server is DB2 on Mainframe.<HR></BLOCKQUOTE>
Ouch, in this shop that would not even be tolerated.
With reports straight from DB2 there are a few areas you could take a look at.
I have included a documents (from our performance test manual) that contains some counters that could followed during the execution of the batch job. Mind you, these counters will typically not help you identify the offending (bottleneck) areas in your batch programs, but they could be a great indicator for excessive resource usage.
In my experience the main problem with lengthy batch jobs against DB2 is the SQL and how the SQL is used.
I'll include another file as well that contains our internal guidelines for the use of DB2. Maybe it is useful.
We found in the past that faulty logic (or lazy programming) is quite often the culprit.
In one situation we found that detailed info was queried within a loop in a program.
This caused certain SQL statements be fired millions of times. By expanding the main SQL (the one that drives the main loop) we were able to cut significant time from the run.
Also every SQL statement used in a batch program should be prototyped and evaluated before it is implemented. All dynamic SQL should be avoided like the plague. We have found that SQL against DB2 can have many forms and some are significantly more efficient than others. Sadly the more efficient ones are not the ones that are the most logical or intuitive. ANother factor is also that they are differences between the different DB2 versions. In one version the SQL runs just fine and in the next version it is a dog. We had quite a bit of experience with that.
Another thing that I like to suggest is:
Forget about DB2: Just extract all the data needed and do you regular batch cobol (balance line) type operation.
Particularly for a report this is often very do-able. Break up your batch job in a few smaller programs.
We found tremendous gains here (from 15 hours to 30 minutes type of gains).
I assume you have eliminated any possibility of locks happening.
I hope this helps.