mainframegurukul.com

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.

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.

Hi,

Both are having the same size only.

Thanks,
Ambili.

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.

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

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.