Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Index scan with proper values & TS scan with host variab

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
kanha

New User


Joined: 05 Nov 2005
Posts: 28

PostPosted: Sat Sep 17, 2011 2:54 pm    Post subject: Index scan with proper values & TS scan with host variab
Reply with quote

All,

The below query behaves weird. when i explain the query giving a proper value for T1.UPD_DT as below, index scan happens for all the table involved. But when I explain using just :H in stead, this does TS scan for 2 tables.

RUNSTATS has been done but no better result.

Please advise whether REOPT(ALWAYS) bind option would replace the actual value in the host variable so that index scan happens.

The tables are large & one table4 is approx. 40 million rows.

SELECT T2.CSN_ID AS CSN_ID,
COALESCE(T4.VNDR_STYLE,'NO STYLE') AS VNDR_STYLE,
SUM(T2.UPC_SHIP_QTY) AS ASN_UNITS
FROM TABLE1 T1
INNER JOIN
TABLE2 T2
ON T2.ASN_NBR = T1.ASN_NBR AND
T2.PO_NBR = T1.PO_NBR AND
T2.PO_STR_NBR = T1.PO_STR_NBR
INNER JOIN
TABLE T3
ON T2.CSN_ID = T3.CSN_ID
LEFT OUTER JOIN
TABLE4 T4
ON T2.COMP_UPC = T4.UPC
WHERE
T1.UPD_DT <= CURRENT_TIMESTAMP AND
T1.UPD_DT > '2011-05-24-13.00.18.118159'
GROUP BY T2.CSN_ID,
T4.VNDR_STYLE
WITH UR
Back to top
View user's profile Send private message

dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Sat Sep 17, 2011 3:29 pm    Post subject:
Reply with quote

well,
since you can not be bothered to read the manuals
or do a google search on something like:

effect on db2 performance when host variables vs literals

i will quote the manual for you: (link here)
Quote:
Use parameter markers for host variables

If you have host variables in a predicate for an original query in a static application and if you are using DB2 QMF or SPUFI to execute EXPLAIN for the query, in most cases, use parameter markers where you use host variables in the original query. If you use a literal value instead, you might see different access paths for your static and dynamic queries.
Back to top
View user's profile Send private message
kanha

New User


Joined: 05 Nov 2005
Posts: 28

PostPosted: Sun Sep 18, 2011 11:31 am    Post subject:
Reply with quote

Dick,

Thanks for your response.

This static query as part of a cursor is in an application program rnning in production.

As the manual says, with values in the host variable, we might see a diff. access path. In this case, with values access path is better. So, can we force the optimizer to choose access path using values with REOPT(ALWAYS) option.

Or is any better way is der to force optimizer in this case???

TS scan is killing a lot of CPU.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Sun Sep 18, 2011 3:29 pm    Post subject:
Reply with quote

REOPT(ALWAYS) adds extra processing time. Manual states the host values are substituted.

OPTHINT is another way, but I would not suggest it.
(you will have to do your own research to see how to implement OPTHINT)

you could use OPTIMIZE FOR N ROWS,
which would speed up your return time.

you could use ROWSET retrieval to decrease the number of db2 CALLs for rows.

this part of the predicate:
T1.UPD_DT > '2011-05-24-13.00.18.118159'

instead of a host variable,
is there not a basic range that you could supply that would always be valid?
e.g. CURRENT_TIMESTAMP - 6 MONTHS
what does this date represent?
are your totals based on accounting time periods,
it appears you want totals shipped within a time period.
granted, you would be forced to add UPD_DT to your SELECT and GROUP BY
and then filter out the results in your code

Why do you need:
T1.UPD_DT <= CURRENT_TIMESTAMP
do you have UPD_DT values that are in the future?

you have a GROUP BY because you want DB2 to SUM the T2.UPC_SHIP_QTY.
in fact, this particular SQL is designed so that you can avoid accumulating the T2.UPC_SHIP_QTY
in your code.

the GROUP BY causes a SORT.
Why not get rid of the SUM,
add UPD_DT to the SELECT
use UPD_DT > CURRENT_TIMESTAMP - 6 MONTHS (or whatever number of months, days,minutes,hours,seconds) to filter out the obviously too old
use a simple ORDER BY
use ROWSET retrieval
discard rows retrieved in your module based on UPD_DT appropriate starting date
accumulate in you code?
Back to top
View user's profile Send private message
kanha

New User


Joined: 05 Nov 2005
Posts: 28

PostPosted: Mon Sep 19, 2011 11:27 am    Post subject:
Reply with quote

Thanks Dick for your detailed response.

Will look for ways to tune the query further but as of now, I think REOPT(ALWAYS) can readily be implemented because it will save significant CPU by eliminating TS scan on 40 million row table.

Although it will add a little extra processing time, it will still perform much better.

Let me know your thoughts.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Mon Sep 19, 2011 8:04 pm    Post subject:
Reply with quote

Hello,

Suggest you try it and post what you observe. . .
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Check Binary Values for symbols gandikk CA Products 3 Tue Mar 21, 2017 5:11 am
No new posts To display values of multiple rows in... vinu78 DB2 2 Thu Mar 09, 2017 1:20 pm
No new posts Replacing same string with different ... vickey_dw DFSORT/ICETOOL 6 Wed Feb 22, 2017 10:44 pm
No new posts setting new limit Key values for inde... srilata83 DB2 1 Fri Feb 10, 2017 9:24 pm
No new posts Remove junk values in a file and rite... KP1125 DFSORT/ICETOOL 2 Wed Jan 25, 2017 9:58 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us