db2 z/os V10 access path

Hi , I would like to calculate the Filter Factor for the below query to compare with the access path after the rebind.

SELECT T1.C1, T1,C2, T1.C3,T2.C4,T2.C10
 FROM T1, T2
 WHERE T2.C1=T1.C1 AND
 T2.C2=T1.C2 AND
 T2.C5=T1.C5 AND
 T1.C6='Z'      AND
 T2.C6='Y'      AND
 T2.C7='X'      AND
 T2.C8<>T1.C9
FOR FETCH ONLY

T1  CARDINALITY =400052
T2  CARDINALITY=1300006

T1 INDEX  -> T1IX1 ON C1,C2   FULLKEYCARD = 400052
T2 INDEX  -> T2IX2 ON C1,C2,C5  FULL KEY CARDF =400052

 Otimiser selects the following access path before rebind:
1.1 Tablespace scan on T2
1.2  Nested loop join with T1 on index T1X1  MC=2

My questions are :
a)  When it does the Table space scan on T2 will it access all data pages and apply T2 predicates(C1,C2,C5,C6,C7,C8)  and filtered and generate the  result set and use that to do the  Nested loop join ?

b) How to workout the Filter Factor for the Query for the above access Path given the above Indexes/STATS are available ?

I am new to application performance tuning /access paths selection and  thanks in advance for your advice/direction. Would like to understand the basics on access path cost calculation.
stj11Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

momi_sabagCommented:
a) yes. that is exactly the reason it chose to do a tablespace scan (as opposed to index scan)
b) i'm not sure i understand what you mean by figuring out the filter factor. In order to calculate the filter factor you need to know if the value distribution of c6 and c7 is uniform and the cardinality for those values.
Also what is the primary key of each table ? (since both indexes has the same fullkeycard and t2 has more rows, the index on t2 can't be unique

are you trying to improve this query?
can you add more indexes?
0
stj11Author Commented:
Hi Thanks for the help.

a) How does it do the join please,  when it finds the first candidate row will it start the join process or else wait till the TS scan on the entire Tablespace before starting  the join ?

b) Sorry for my terminology. What I meant was how to work out the selectivity estimation. I guess for that one needs to know the filter factors for the predicates ?
There is no stats(-1) on the columns but those have HIGH2KEY and  LOW2KEY valuse as below. How to find out    the distribution is uniform or not pl ?
                           H2KEY     L2KEY
  C6                     x'D5'         x'C2'
  C7                     x'C6'         x'C6'

Primary keys are as below :
T2 (C1,C2,C5,C6,C8)
T1(C1,C2,C5)

Yes , T2IX2 is not unique.

I am not going to improve the query but try to compare with the new access path after the rebind. First I'm trying to make sure that  I understand correctly how to work out the selectivity estimation ,which I believe  optimiser uses to decide on access path selection. Could you please help me to calculate how many rows optimiser estimates qualify from Tables T1 and T2  under the available STATs ?
0
momi_sabagCommented:
a) it does the join as it scans the rows (a nested loop join)
b) the optimizer will choose the same access path after the rebind if no new statistics are introduced. Look into the runstats utility  - it has some options which allows you to collect distribution statistics about the columns of the tables. If distribution statistics are not there, DB2 assumes uniform distribution
if you want to check if the distribution is uniform before running the runstats you can do something like:

select min(cnt) as min_cnt, avg(cnt) as avg(cnt), max(cnt) as max_cnt from (
select count(*) cnt, your_colum_name from your_table group by your_column) a

if you see that max_cnt is very small, the distribution is very close to uniform
0
stj11Author Commented:
Thanks for the explanation.
a) Can you please advise on how to calculate the selectivity (cost) for Table( T1 and T2 ) based on the FF/    
    available STATS  ?
b) Because of the rebind it did change the access path due to Version upgrade.  if  you advise me how to  calculate
    the cost for the above access path then I can work out the cost for the new one.
0
Tomas Helgi JohannssonCommented:
Hi!

Have you tried the IBM Data Studio 4.1  and the Visual Explain against your DB2 on z/OS ?
Data Studio is free and has free single query tuning feature which gives you graphical access path
as well as all CPU(ms) and CPU(su) values and other values that you can look at.

Regards,
      Tomas Helgi
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
DB2

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.