[Last Call] Learn how to a build a cloud-first strategyRegister Now


db2 z/os V10 access path

Posted on 2014-08-16
Medium Priority
Last Modified: 2014-12-20
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
 T2.C2=T1.C2 AND
 T2.C5=T1.C5 AND
 T1.C6='Z'      AND
 T2.C6='Y'      AND
 T2.C7='X'      AND


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.
Question by:stj11
  • 2
  • 2
LVL 37

Assisted Solution

momi_sabag earned 1328 total points
ID: 40265652
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?

Author Comment

ID: 40266285
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)

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 ?
LVL 37

Assisted Solution

momi_sabag earned 1328 total points
ID: 40278564
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

Author Comment

ID: 40279344
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.
LVL 26

Accepted Solution

Tomas Helgi Johannsson earned 672 total points
ID: 40406453

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.

      Tomas Helgi

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In this article, we’ll look at how to deploy ProxySQL.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question