MAINFRAME - TIP OF THE DAY :
Q. If there is a situation, where we need to code more than 255 steps in a JOB?
A. We need to split jcl into two jcls , at the end of the first jcl check the condition code and initiate the second jcl.
Programmers Voted for below topics. Please Vote for good Posts.
Thank You! for your feedback. Connecting to the server. Please Wait...
Posted: Tue Jun 27, 2006 12:18 pm Post subject: Performance tuning..
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.
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
WHERE SLS_ORGN_ID IN ('17','30','50')
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'
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.
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
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.
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum