One of the mainframe job is taking total 5 minutes for executing. We analysed and observed that in job there are 8 steps and 1 step is taking 4.52 minute.
The detail of the step which is taking 4.52 minutes is:
- taking 2 files as an input
- executing 3 SQL queries to select the data from table and INTO it to variable
- write the data to output file.
Currently we are having 20K users. We are expecting to increase the count of users to 40K; we are worried as currently it is taking 5 minutes. If we double the number of users it might take 10 minutes.
Please let us know on how to reduce the execution time of the job.
Its very difficult to say why the program is taking that much time, you can post the queries for more details on the same, what is the volume of the two files, what is 5 Mins is the CPU time or elapses time? what are kind of file that u r using? please provide us with the details for more calrification.
Joined: 06 Jun 2008 Posts: 8449 Location: Dubuque, Iowa, USA
Most mainframe shops run hundreds or thousands of jobs per day, each of which take anywhere from a few seconds to many hours to complete. Exactly why are you so concerned about a single short-running job in this mix? Even if it ran 20 or 30 minutes, that's still not much time out of a day to cause such concern.
Programs are either CPU-bound or I/O-bound; generally it requires an analysis tool like STROBE or APA to determine which the program is. If the program is CPU-bound, you might be able to speed it up by changing the way it works by looking at the statements using the most CPU time. If the program is I/O-bound, the usual culprits should be reviewed -- buffers, channel contention, indexes.
Its a ccumpuware tool used for doing the performance analysis, for the team member for the avalibility of the sortware at ur shop?
If the occurance of the query in just once in the pgm then look for some other process check for redundnat process in the code!!
Joined: 23 Nov 2006 Posts: 19270 Location: Inside the Matrix
These queries are not taking more than 3 sec per query while executing in SPUFI.
If the same data is used, the time taken should be similar (unless a query has some optimization problem). Suggest you capture the time before and after the execution of the query in the program to see how long the query runs inside the program.
Joined: 20 Oct 2006 Posts: 6970 Location: porcelain throne
I am confused.
you have two input files - what are they for?
you mention employees, and it seems as if you are extracting employee data,
based on a host variable employee number.
are you executing these queries once for each employee contained
in the input files? (which contain 20k records?)
also, your INTO populates the host variable which you have populated for the WHERE clause.
you can save a little time by not having DB2 populate host variables which you have already populated for the WHERE clause.
Strobe is not available in all shop. So it's also not possible to get that report.
Here even there's not much to fine tune the SQL query ,moreover the queries are not taking much of time.
Here it need some code analysis.Is the job taking that much time everyday or for some particular day????