Performance tuning..

Ask question on - DataBase Concepts, IBM Mainframe DB2, DB2/UDB, DB2 Tools, SQL , DB2 programming with languages like COBOL, PL1.

Moderators: Kalicharan, Moderator Group

Post Reply
ambilileela
Member
Posts: 11
Joined: Tue Jun 06, 2006 2:12 pm

Performance tuning..

Post by ambilileela » Tue Jun 27, 2006 12:18 pm

Hi all,

I have a db2 query in the cobol program. Because of this query the job is taking around 75cpu min to run. I need to reduce the cpu time to 35 cpu mins minimum. I am attaching the query in the post. Could anyone please suggest me a method to improve the performance.

SELECT DISTINCT
J.SLS_ORGN_OWN_ID
, E.CLS_OF_TRD_DE
, D.CLS_OF_TRD_DE
, D.CLS_OF_TRD_DE
FROM WKLY_CUST_SLS_SUM B
, COT_HIERARCHY D
, COT_HIERARCHY E
, SLS_PROD_RPTG_PR H
, SALES_ORGN_PAIRS J
WHERE B.CUST_NO = :WS-CSR-CUST-NO
AND B.END_DT > '1999-11-01'
AND B.CLS_OF_TRD_ID = D.CLS_OF_TRD_ID
AND D.CLS_OF_TRD_OWN_ID = E.CLS_OF_TRD_ID
AND B.PROD_GRP_ID = H.PROD_GRP_ID
AND B.HIER_ID = H.HIER_ID
AND H.PROD_GRP_OWN_ID = '12'
AND H.PROD_OWN_HIER_ID = 'USGP'
AND (H.END_DT IS NULL
OR H.END_DT > CURRENT DATE)
AND B.SLS_ORGN_ID = J.SLS_ORGN_ID
AND J.SLS_ORGN_OWN_ID IN ('17','228','30','50')
AND (J.END_DT IS NULL
OR J.END_DT > CURRENT DATE)


The customer no is fetched using the query

SELECT DISTINCT CUST_NO
FROM CUST_SLS_CHNL_CV
WHERE SLS_ORGN_ID IN ('17','30','50')

UNION

SELECT DISTINCT A.CUST_NO
FROM CUST_SLS_CHNL_CV A
, SLS_CREDIT_CV B
, SALES_ORG_PAIRS_CV C
WHERE A.SLS_ORGN_ID = '21'
AND A.CUST_NO = B.CUST_NO
AND B.REP_SLS_ORGN_ID = C.SLS_ORGN_ID
AND C.SLS_ORGN_OWN_ID = '228'

ORDER BY CUST_NO


Thanks in advance.

Regards,
Ambili.

User avatar
Krishna
Site Admin
Posts: 1052
Joined: Fri Jan 27, 2006 7:50 am

db2 performance tunning

Post by Krishna » Wed Jun 28, 2006 4:32 pm

If you used working storage variables in the query.
---------------------------------------------------------

Make sure that variable used in the query has declared with proper
length/size.

If these variable sizes not compatible with DCL variable
length/size query does table scan and it takes more time to complete.



Thanks,
Krishna.

ambilileela
Member
Posts: 11
Joined: Tue Jun 06, 2006 2:12 pm

Post by ambilileela » Thu Jun 29, 2006 9:59 am

Hi,

Both are having the same size only.

Thanks,
Ambili.

User avatar
Kalicharan
Moderator
Posts: 31
Joined: Wed Feb 08, 2006 1:51 pm

Post by Kalicharan » Thu Jul 06, 2006 6:48 pm

Hi Ambili,

Check the indexes for WKLY_CUST_SLS_SUM.CUST_NO

Thinking that CUST_NO column is the most filtering one in the query.
If the column is not indexed then query might take more CPU time.
So verify the indexes of WKLY_CUST_SLS_SUM.CUST_NO.

Also the second query which is fetching the customer number, no need to use DISTINCT keyword in this case.
Union (in DB2 and Oracle) itself returns distinct result set.So no need to use the DISTINCT keyword in customer number selection query.
However distinct will not impact performance much in this case

Also check indexes for CUST_SLS_CHNL_CV. SLS_ORGN_ID as well.

Also you need to confirm whether the whole process (including the first and second queries) is taking time or first one alone taking such a long time.

Thanks
Kali.

ambilileela
Member
Posts: 11
Joined: Tue Jun 06, 2006 2:12 pm

Post by ambilileela » Tue Oct 03, 2006 3:20 pm

Hi

Sorry for the late reply. The outer query alone takes much time. Could some one please provide any method to tune this query.

Thanks in advance
Ambili

User avatar
popeye
Member
Posts: 1
Joined: Fri Nov 03, 2006 1:15 am

Post by popeye » Fri Nov 03, 2006 1:30 am

ambilileela wrote:Hi

Sorry for the late reply. The outer query alone takes much time. Could some one please provide any method to tune this query.

Thanks in advance
Ambili
Hi Ambili

Have you checked all the indexes, as well as which ones are clustered and when last stats were gathered on all the tables?

You would need to check the indexes and stats first. Also check that the indexes have the relevant columns and in the relevant orders.

Run an explain and see what the query is doing, then you will be able to see where exactly in the query you are encountering problems. It also depends on how much data is on each of the tables.

Then you might try re-writing your query, using the JOIN clauses.

Unfortunately there is no "correct" answer to your question, one has to take into account quite a lot of the surrounding circumstances, including the installation PARMS, Bufferpool sizes, Work Pool sizes, etc.

The above should help you to identify if you have any problem with the query, then you can see where you need to concentrate your efforts.

Cheers.

Post Reply

FREE TUTORIALS

Tutorials
Free tutorials from mainframegurukul
  • JCL Tutorial
    Covers all important JCL concepts.
  • Cobol Tutorial
    This tutorials covers all Cobol Topics from STRING to COMP-3.
  • DB2 Tutorial
    DB2 Tutorial focuses on DB2 COBOL Programming.
  • SORT Tutorial
    This Tutorial covers all important aspects of DFSORT with examples
  • CICS Tutorial
    This CICS tutorial covers CICS concepts and CICS Basics, CICS COBOL Programming.
Interview
Mainframe Interview questions



Other References
Mainframe Tools and others