Reduce execution time of a job (currently it is taking 5 min

ruchi.jain · New User Joined: 17 Sep 2007 Posts: 17 Location: Pune

Hi,

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.

PeD · Posted: Fri Oct 09, 2009 2:47 pm

Make a code review, and/or use Strobe or APA...

Ketan Varhade · Posted: Fri Oct 09, 2009 2:47 pm

HI Ruchi,
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.

Robert Sample · Posted: Fri Oct 09, 2009 4:57 pm

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.

ruchi.jain · New User Joined: 17 Sep 2007 Posts: 17 Location: Pune

5 min is CPU time. We are using 2 NVS file.
- 1 file contains ~18000 users
- 2 file contains ~ 3000 users.

Please find the queries attached in the text file. We are doing simply select of the queries. The output file is also an NVS file.

expat · Posted: Fri Oct 09, 2009 5:02 pm

Please DO NOT post attachments as not everyone can open them, thus limiting the number of people able to help you.

ruchi.jain · New User Joined: 17 Sep 2007 Posts: 17 Location: Pune

ok...
so here are the 3 queries:

Ketan Varhade · Posted: Fri Oct 09, 2009 5:12 pm

you remove the count * with select 1 if there any check for the duplicate in the first query as I am asuming that its ind you need to check for the action after SQL code is equal to zero

You can remove the select of the first colum as the value is known to you.
same in the query no three.

there is not much according to me to tune in these query,

dick scherrer · Posted: Fri Oct 09, 2009 10:18 pm

Hello,

How much time do these queries take if they are run in spufi?

How many times are these queries executed in a single transaction?

ruchi.jain · New User Joined: 17 Sep 2007 Posts: 17 Location: Pune

Thanks all.

These queries are not taking more than 3 sec per query while executing in SPUFI.

After we select the data we are writing it to output file. Is the time because of that?
Can we do some other approach to reduce the time as queries are not taking much time?

Ketan Varhade · Posted: Mon Oct 12, 2009 12:44 pm

Do you have a strobe report of the program ? what is the frequecy of the queires?

ruchi.jain · New User Joined: 17 Sep 2007 Posts: 17 Location: Pune

Query runs on daily basis. The output file was used on daily basis.

what to do mean by strobe report?

Ketan Varhade · Posted: Mon Oct 12, 2009 12:48 pm

U have a cobol prgram which run the SQL query, what is the frequecy of the quiries to be run in e single run of the job ?
Strobe report if a performance analysis tool.

ruchi.jain · New User Joined: 17 Sep 2007 Posts: 17 Location: Pune

Queries execute once in the job but that job runs daily.
Can you let me know how to use that performance tool and what's the process.

So that i can do the performance analysis of the program.

Ketan Varhade · Posted: Mon Oct 12, 2009 1:42 pm

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!!

Terry Heinze · Posted: Mon Oct 12, 2009 10:16 pm

Have you run EXPLAINs on the SQLs?

dick scherrer · Posted: Tue Oct 13, 2009 1:13 am

Hello,

dbzTHEdinosauer · Posted: Tue Oct 13, 2009 3:42 am

Ruchi Jain,

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.

devd · New User Joined: 19 Mar 2009 Posts: 11 Location: Chennai

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????