Solved

# db2 z/os V10 access path

Posted on 2014-08-16
312 Views
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.
0
Question by:stj11

LVL 37

Assisted Solution

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?
0

Author Comment

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

LVL 37

Assisted Solution

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

Author Comment

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

LVL 24

Accepted Solution

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

## Featured Post

### Suggested Solutions

In today’s complex data management environments, it is not unusual for UNIX servers to be dedicated to a particular department, purpose, or database.  As a result, a SAS® data analyst often works with multiple servers, each with its own data storage…
I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
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…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…