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.
Performance tuning..
Moderators: Kalicharan, Moderator Group
db2 performance tunning
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.
---------------------------------------------------------
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.
-
- Member
- Posts: 11
- Joined: Tue Jun 06, 2006 2:12 pm
- Kalicharan
- Moderator
- Posts: 31
- Joined: Wed Feb 08, 2006 1:51 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.
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.
-
- Member
- Posts: 11
- Joined: Tue Jun 06, 2006 2:12 pm
Hi Ambiliambilileela 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
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.
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
- Cobol Interview Questions
50+ Interview Questions - JCL Interview Questions
50+ Interview Questions - DB2 Interview Questions
100+ Interview Questions - CICS Interview Questions
70+ Interview Questions - VSAM Interview Questions
27 Interview Questions
Other References
Mainframe Tools and others
- XPEDITER Reference
Explains how we can debug a program - FILEAID Reference
Explains how to browse , edit and delete datasets - Change Man Reference
Quick Start tutorial on Changeman - Abend Reference
Important Abend codes explained - FaceBook Page
MainframeGurukul FaceBook Page - LinkedIn Page
MainframeGurkul Linkedin Page