db2 z/os V10 access path

Posted on 2014-08-16
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
    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?
    can you add more indexes?

    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)

    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

    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

    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 24

    Accepted Solution


    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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    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…
    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…
    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…

    759 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

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now