Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

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

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> JCL & VSAM
View previous topic :: :: View next topic  
Author Message
ruchi.jain

New User


Joined: 17 Sep 2007
Posts: 17
Location: Pune

PostPosted: Fri Oct 09, 2009 2:31 pm    Post subject: Reduce execution time of a job (currently it is taking 5 min
Reply with quote

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.
Back to top
View user's profile Send private message

PeD

Active User


Joined: 26 Nov 2005
Posts: 456
Location: Belgium

PostPosted: Fri Oct 09, 2009 2:47 pm    Post subject:
Reply with quote

Make a code review, and/or use Strobe or APA...
Back to top
View user's profile Send private message
Ketan Varhade

Active User


Joined: 29 Jun 2009
Posts: 197
Location: Mumbai

PostPosted: Fri Oct 09, 2009 2:47 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
Robert Sample

Global Moderator


Joined: 06 Jun 2008
Posts: 7904
Location: Bellevue, IA

PostPosted: Fri Oct 09, 2009 4:57 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
ruchi.jain

New User


Joined: 17 Sep 2007
Posts: 17
Location: Pune

PostPosted: Fri Oct 09, 2009 4:59 pm    Post subject:
Reply with quote

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.
Code:
Query1:
 EXEC SQL
   SELECT COUNT(*) AS DUPS
          INTO :WS-DUPL-IND
   FROM  VLDAP_DWR_EMPLOYEE
   WHERE CDE_MSA_EMP_NUM =
        :DCLVLDAP-DWR-EMPLOYEE.CDE-MSA-EMP-NUM
         AND CDE_TRM_RSN = ' '
 END-EXEC.
 
Query2:
EXEC SQL
 SELECT C.CDE_MSA_EMP_NUM
       ,C.NME_1ST
       ,C.NME_MID
       ,C.NME_LST
       ,C.NME_JOB_TLE
       ,C.CDE_OFC_RNK
       ,C.NME_OFC_RNK
       ,C.CDE_LVL_3
       ,C.DT2_TRM
       ,C.DT2_HIR
       ,C.CDE_OFF_ICE
       ,C.CDE_LOC
       ,C.CDE_RGN
   INTO
        :DCLVLDAP-DWR-EMPLOYEE.CDE-MSA-EMP-NUM
       ,:DCLVLDAP-DWR-EMPLOYEE.NME-1ST
       ,:DCLVLDAP-DWR-EMPLOYEE.NME-MID
       ,:DCLVLDAP-DWR-EMPLOYEE.NME-LST
       ,:DCLVLDAP-DWR-EMPLOYEE.NME-JOB-TLE
       ,:DCLVLDAP-DWR-EMPLOYEE.CDE-OFC-RNK
       ,:DCLVLDAP-DWR-EMPLOYEE.NME-OFC-RNK
       ,:DCLVLDAP-DWR-EMPLOYEE.CDE-LVL-3
       ,:DCLVLDAP-DWR-EMPLOYEE.DT2-TRM
        :IV-DT2-TRM
       ,:DCLVLDAP-DWR-EMPLOYEE.DT2-HIR
        :IV-DT2-HIR
       ,:DCLVLDAP-DWR-EMPLOYEE.CDE-OFF-ICE :NULL-CDE-OFF-ICE
       ,:DCLVLDAP-DWR-EMPLOYEE.CDE-LOC :NULL-CDE-LOC
       ,:DCLVLDAP-DWR-EMPLOYEE.CDE-RGN
   FROM VLDAP_DWR_EMPLOYEE C
  WHERE C.CDE_MSA_EMP_NUM =
        :DCLVLDAP-DWR-EMPLOYEE.CDE-MSA-EMP-NUM
         AND CDE_TRM_RSN = ' '
 END-EXEC
 
Query3:
EXEC SQL
 SELECT D.CDE_OFF
       ,D.NME_OFF_NME
       ,D.NME_LOC
       ,D.ADR_ST
       ,D.ADR_ST_2
       ,D.ADR_CTY
       ,D.ADR_STE
       ,D.CDE_ZIP
       ,D.CDE_BLD
       ,D.CDE_CTY
       ,D.ORG_STE
   INTO
        :DCLVLDAP-DWR-ADDRESS.CDE-OFF
       ,:DCLVLDAP-DWR-ADDRESS.NME-OFF-NME
       ,:DCLVLDAP-DWR-ADDRESS.NME-LOC
       ,:DCLVLDAP-DWR-ADDRESS.ADR-ST
        :IV-ADR-ST
       ,:DCLVLDAP-DWR-ADDRESS.ADR-ST-2
        :IV-ADR-ST-2
       ,:DCLVLDAP-DWR-ADDRESS.ADR-CTY
        :IV-ADR-CTY
       ,:DCLVLDAP-DWR-ADDRESS.ADR-STE
        :IV-ADR-STE
       ,:DCLVLDAP-DWR-ADDRESS.CDE-ZIP
        :IV-CDE-ZIP
       ,:DCLVLDAP-DWR-ADDRESS.CDE-BLD
       ,:DCLVLDAP-DWR-ADDRESS.CDE-CTY
        :IV-CDE-CTY
       ,:DCLVLDAP-DWR-ADDRESS.ORG-STE
        :IV-ORG-STE
   FROM VLDAP_DWR_ADDRESS D
  WHERE D.CDE_OFF = :DCLVLDAP-DWR-ADDRESS.CDE-OFF
END-EXEC.
Back to top
View user's profile Send private message
expat

Global Moderator


Joined: 14 Mar 2007
Posts: 8593
Location: Back in jolly old England

PostPosted: Fri Oct 09, 2009 5:02 pm    Post subject:
Reply with quote

Please DO NOT post attachments as not everyone can open them, thus limiting the number of people able to help you.
Back to top
View user's profile Send private message
ruchi.jain

New User


Joined: 17 Sep 2007
Posts: 17
Location: Pune

PostPosted: Fri Oct 09, 2009 5:04 pm    Post subject:
Reply with quote

ok...
so here are the 3 queries:


Code:

Query1:
 EXEC SQL
   SELECT COUNT(*) AS DUPS
          INTO :WS-DUPL-IND
   FROM  VLDAP_DWR_EMPLOYEE
   WHERE CDE_MSA_EMP_NUM =
        :DCLVLDAP-DWR-EMPLOYEE.CDE-MSA-EMP-NUM
         AND CDE_TRM_RSN = ' '
 END-EXEC.
 
Query2:
EXEC SQL
 SELECT C.CDE_MSA_EMP_NUM
       ,C.NME_1ST
       ,C.NME_MID
       ,C.NME_LST
       ,C.NME_JOB_TLE
       ,C.CDE_OFC_RNK
       ,C.NME_OFC_RNK
       ,C.CDE_LVL_3
       ,C.DT2_TRM
       ,C.DT2_HIR
       ,C.CDE_OFF_ICE
       ,C.CDE_LOC
       ,C.CDE_RGN
   INTO
        :DCLVLDAP-DWR-EMPLOYEE.CDE-MSA-EMP-NUM
       ,:DCLVLDAP-DWR-EMPLOYEE.NME-1ST
       ,:DCLVLDAP-DWR-EMPLOYEE.NME-MID
       ,:DCLVLDAP-DWR-EMPLOYEE.NME-LST
       ,:DCLVLDAP-DWR-EMPLOYEE.NME-JOB-TLE
       ,:DCLVLDAP-DWR-EMPLOYEE.CDE-OFC-RNK
       ,:DCLVLDAP-DWR-EMPLOYEE.NME-OFC-RNK
       ,:DCLVLDAP-DWR-EMPLOYEE.CDE-LVL-3
       ,:DCLVLDAP-DWR-EMPLOYEE.DT2-TRM
        :IV-DT2-TRM
       ,:DCLVLDAP-DWR-EMPLOYEE.DT2-HIR
        :IV-DT2-HIR
       ,:DCLVLDAP-DWR-EMPLOYEE.CDE-OFF-ICE :NULL-CDE-OFF-ICE
       ,:DCLVLDAP-DWR-EMPLOYEE.CDE-LOC :NULL-CDE-LOC
       ,:DCLVLDAP-DWR-EMPLOYEE.CDE-RGN
   FROM VLDAP_DWR_EMPLOYEE C
  WHERE C.CDE_MSA_EMP_NUM =
        :DCLVLDAP-DWR-EMPLOYEE.CDE-MSA-EMP-NUM
         AND CDE_TRM_RSN = ' '
 END-EXEC
 
Query3:
EXEC SQL
 SELECT D.CDE_OFF
       ,D.NME_OFF_NME
       ,D.NME_LOC
       ,D.ADR_ST
       ,D.ADR_ST_2
       ,D.ADR_CTY
       ,D.ADR_STE
       ,D.CDE_ZIP
       ,D.CDE_BLD
       ,D.CDE_CTY
       ,D.ORG_STE
   INTO
        :DCLVLDAP-DWR-ADDRESS.CDE-OFF
       ,:DCLVLDAP-DWR-ADDRESS.NME-OFF-NME
       ,:DCLVLDAP-DWR-ADDRESS.NME-LOC
       ,:DCLVLDAP-DWR-ADDRESS.ADR-ST
        :IV-ADR-ST
       ,:DCLVLDAP-DWR-ADDRESS.ADR-ST-2
        :IV-ADR-ST-2
       ,:DCLVLDAP-DWR-ADDRESS.ADR-CTY
        :IV-ADR-CTY
       ,:DCLVLDAP-DWR-ADDRESS.ADR-STE
        :IV-ADR-STE
       ,:DCLVLDAP-DWR-ADDRESS.CDE-ZIP
        :IV-CDE-ZIP
       ,:DCLVLDAP-DWR-ADDRESS.CDE-BLD
       ,:DCLVLDAP-DWR-ADDRESS.CDE-CTY
        :IV-CDE-CTY
       ,:DCLVLDAP-DWR-ADDRESS.ORG-STE
        :IV-ORG-STE
   FROM VLDAP_DWR_ADDRESS D
  WHERE D.CDE_OFF = :DCLVLDAP-DWR-ADDRESS.CDE-OFF
END-EXEC.
Back to top
View user's profile Send private message
Ketan Varhade

Active User


Joined: 29 Jun 2009
Posts: 197
Location: Mumbai

PostPosted: Fri Oct 09, 2009 5:12 pm    Post subject:
Reply with quote

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,
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Fri Oct 09, 2009 10:18 pm    Post subject:
Reply with quote

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?
Back to top
View user's profile Send private message
ruchi.jain

New User


Joined: 17 Sep 2007
Posts: 17
Location: Pune

PostPosted: Mon Oct 12, 2009 12:42 pm    Post subject:
Reply with quote

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?
Back to top
View user's profile Send private message
Ketan Varhade

Active User


Joined: 29 Jun 2009
Posts: 197
Location: Mumbai

PostPosted: Mon Oct 12, 2009 12:44 pm    Post subject:
Reply with quote

Do you have a strobe report of the program ? what is the frequecy of the queires?
Back to top
View user's profile Send private message
ruchi.jain

New User


Joined: 17 Sep 2007
Posts: 17
Location: Pune

PostPosted: Mon Oct 12, 2009 12:46 pm    Post subject:
Reply with quote

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

what to do mean by strobe report?
Back to top
View user's profile Send private message
Ketan Varhade

Active User


Joined: 29 Jun 2009
Posts: 197
Location: Mumbai

PostPosted: Mon Oct 12, 2009 12:48 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
ruchi.jain

New User


Joined: 17 Sep 2007
Posts: 17
Location: Pune

PostPosted: Mon Oct 12, 2009 12:52 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
Ketan Varhade

Active User


Joined: 29 Jun 2009
Posts: 197
Location: Mumbai

PostPosted: Mon Oct 12, 2009 1:42 pm    Post subject:
Reply with quote

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!!
Back to top
View user's profile Send private message
Terry Heinze

JCL Moderator


Joined: 14 Jul 2008
Posts: 1238
Location: Richfield, MN, USA

PostPosted: Mon Oct 12, 2009 10:16 pm    Post subject:
Reply with quote

Have you run EXPLAINs on the SQLs?
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Tue Oct 13, 2009 1:13 am    Post subject:
Reply with quote

Hello,

Quote:
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.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Tue Oct 13, 2009 3:42 am    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
devd

New User


Joined: 19 Mar 2009
Posts: 11
Location: Chennai

PostPosted: Wed Oct 14, 2009 8:27 pm    Post subject:
Reply with quote

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????
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> JCL & VSAM All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
This topic is locked: you cannot edit posts or make replies. Get a job submitted itself every time... arunsoods JCL & VSAM 3 Tue Nov 22, 2016 4:05 pm
No new posts Check System time(Minute) Using TIME1... balaji81_k DFSORT/ICETOOL 5 Fri Nov 11, 2016 10:53 am
This topic is locked: you cannot edit posts or make replies. Get correct date and time when curren... balaji81_k DB2 24 Fri Oct 14, 2016 10:40 pm
No new posts cics & real time monitoring icehat CICS 5 Wed Sep 07, 2016 2:29 am
No new posts Get system time in micro second or cl... lind sh CICS 11 Mon Sep 05, 2016 11:14 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us