Joined: 13 Feb 2009 Posts: 20 Location: United States of america
Actually i am trying to convert one focus program to cobol.
After retrieving ,data is written to o/p files and one report is generated
Query is
Code:
SELECT
T1.WK_ORD_NBR
, T1.SEQ_NBR AS EQP_SEQ_NBR
, T2.MSA_PART_NBR AS PRT_NBR
, T2.SEQ_NBR AS PRT_SEQ_NBR
-*-
, MAX(T1.SERV_CD) AS SERV_CD
, MAX(T1.MISS_DSC_IND) AS MISS_DSC_IND
, MAX(T1.INVL_DSC_IND) AS INVL_DSC_IND
, MAX(T1.AVDBL_DSC_IND) AS AVDBL_DSC_IND
, MAX(T1.EQP_SER_NBR) AS EQP_SER_NBR
, MAX(T1.VLD_SER_NBR_IND) AS VLD_SER_NBR_IND
, MAX(T1.NO_SERV_W_PRTS_IND) AS NO_SERV_W_PRTS_IND
, MAX(T2.LINE_PART_QTY) AS LINE_PART_QTY
, MAX(T2.UNT_COST_AMT) AS UNT_COST_AMT
, MAX(T2.LI_TOTAL_COST) AS LI_TOTAL_COST
FROM ISDBA.VSR_OPS_TECN_DTL T1
LEFT OUTER JOIN
ISDBA.VSV_SO_LINE_PART T2
ON T2.WK_ORD_NBR = T1.WK_ORD_NBR
AND T2.SNL_SEQ_NBR = T1.SEQ_NBR
GROUP BY
T1.WK_ORD_NBR
, T1.SEQ_NBR
, T2.MSA_PART_NBR
, T2.SEQ_NBR
ORDER BY
T1.WK_ORD_NBR
, T1.SEQ_NBR
, T2.MSA_PART_NBR
, T2.SEQ_NBR
FOR FETCH ONLY.
Index for VSR_OPS_TECN_DTL (view)
WK_ORD_NBR
TECN_ID
SEQ_NBR and
for VSV_SO_LINE_PART ( view)
WK_ORD_NBR
SEQ_NBR
SNL_SEQ_NBR
Code'd
I used coalesce just to replace null value with zeros.
Even if i run query as it is(without using coalesce) there is hardly any difference in time. My objective is to reduce the time.
Please let me know if any other info is required
Joined: 13 Feb 2004 Posts: 551 Location: Bangalore
Changes in your select clause is not going to change the timings as these are done only after the rows satisfying the where clause are bought into temp space ... views usually perform pretty well stand-alone and they can insulate the developer from requiring any knowledge about the data . But when multiple views are joined the access path can be less than optimum .. try joining the base tables directly for your query ..
you might even consider removing the order by .. group by and order by has the same sequence of columns.
Joined: 13 Feb 2009 Posts: 20 Location: United States of america
I tried by joining tables not views and removed order by clause but it didnt make any difference.
Answer to " why i feel this query is taking much time?'
Normally focus takes more time than cobol so i was converting this program to cobol but somehow it is not working.Infact batch query is taking more time.
Joined: 13 Feb 2004 Posts: 551 Location: Bangalore
How do you compare cobol and focus ?
You new query is upon DB2 and this is should be the actual time it takes in DB2. Now what you need to focus ( look upon ) is on reducing the DB2 time by improving your query or indexes.
Check with your DBA and request them to check where all the time is spending. It is in DB2 or in application.
If it is in DB2, please do explain see the access path. Check if there is any tablespace scan or non-matching index scan for any one of the table.
Also need to look on the index structure and explain output to provide further recommendation / suggestions.
Can you also mention where you are running this query. If it is test environment, please check RUNSTATS in test environment is same like production for these tables. Please check with DBA, do REORG, RUNSTATS for these tables if required.