ZOS DBA : DSN_FILTER TABLE

Nanthakumar Yoganathan

ZOS DBA : DSN_FILTER TABLE


Hi,

Is there anyway i can find the difference of STAGE1 or STAGE1 Indexable from DSN_FILTER_TABLE as STAGE column of DSN_FILTER_TABLE provides only STAGE 1 or STAGE 2 ?

 

Please advise.

Regards,

Nanthakumar Yoganathan

+91-9942512341

Walter Janißen

AW: ZOS DBA : DSN_FILTER TABLE
(in response to Nanthakumar Yoganathan)
Hi

I would say, if STAGE1 is indexable it's either matching or screening and both is reported in that table. So if it is STAGE1, it's a predicate on a column, which is not contained in the index used to access the table.


Mit freundlichen Grüßen
Walter Janißen

ITERGO Informationstechnologie GmbH
Spezialisten
Technische Anwendungsarchitektur
Victoriaplatz 2
40477 Düsseldorf
[login to unmask email]

Vorsitzender des Aufsichtsrats: Christian Diedrich
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Lothar Engelke, Ina Kirchhof, Dr. Christian Nymphius, Dr. Michael Regauer
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf, HRB 37996



________________________________
Von: Nanthakumar Yoganathan [mailto:[login to unmask email]
Gesendet: Montag, 14. Oktober 2013 17:59
An: [login to unmask email]
Betreff: [DB2-L] - ZOS DBA : DSN_FILTER TABLE


Hi,

Is there anyway i can find the difference of STAGE1 or STAGE1 Indexable from DSN_FILTER_TABLE as STAGE column of DSN_FILTER_TABLE provides only STAGE 1 or STAGE 2 ?



Please advise.

Regards,

Nanthakumar Yoganathan

+91-9942512341

-----End Original Message-----

Venkata Sai Arjun Pettam

RE: AW: ZOS DBA : DSN_FILTER TABLE
(in response to Walter Janißen)

Hi,

I have a doubt regarding how the DSN_FILTER_TABLE is populated. For the following SQL Query instead of having two predicate entries it has only one entry for the whole condition as single compound predicate in DSN_FILTER_TABLE.

 

SQL Query:

 

SELECT * FROM DSN_PREDICAT_TABLE WHERE PREDNO = QUERYNO OR LEFT_HAND_SIDE = RIGHT_HAND_SIDE;

 

This is just a sample query. 

Both the predicates are stage 2 predicates.

Please let me know is it the expected entry to the table.

Roy Boxwell

AW: ZOS DBA : DSN_FILTER TABLE
(in response to Venkata Sai Arjun Pettam)
In the filter table I get one line keyed to the DSN_PREDICAT_TABLE PREDNO = 1 and in that table I get three lines:



PREDNO TYPE LEFT_HAND_SIDE

------+---------+---------+---------+

1 OR

2 EQUAL PREDNO

3 EQUAL LEFT_HAND_SIDE





Looks fine to me!



Roy Boxwell

SOFTWARE ENGINEERING GmbH and SEGUS Inc.
-Product Development-

Heinrichstrasse 83-85
40239 Duesseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: <mailto:[login to unmask email]> [login to unmask email]
Web http://www.seg.de http://www.seg.de

https://www.seg.de/corporate/rechtliche-hinweise/datenschutz Link zur Datenschutzerklärung


Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert, Ulf Heinrich



From: Venkata Sai Arjun Pettam [mailto:[login to unmask email]
Sent: Tuesday, July 23, 2019 8:04 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: AW: ZOS DBA : DSN_FILTER TABLE



Hi,

I have a doubt regarding how the DSN_FILTER_TABLE is populated. For the following SQL Query instead of having two predicate entries it has only one entry for the whole condition as single compound predicate in DSN_FILTER_TABLE.



SQL Query:



SELECT * FROM DSN_PREDICAT_TABLE WHERE PREDNO = QUERYNO OR LEFT_HAND_SIDE = RIGHT_HAND_SIDE;



This is just a sample query.

Both the predicates are stage 2 predicates.

Please let me know is it the expected entry to the table.



-----End Original Message-----

Attachments

  • smime.p7s (5.1k)

Joe Geller

RE: AW: ZOS DBA : DSN_FILTER TABLE
(in response to Roy Boxwell)

Yes, generally speaking that is what you get with ORs.  ANDs are independent predicates - each one must be satisfied.  ORs with different columns are dependent - only one has to be satisfied (also known as Inclusive OR).  (This is a simplification).

Joe

In Reply to Roy Boxwell:

In the filter table I get one line keyed to the DSN_PREDICAT_TABLE PREDNO = 1 and in that table I get three lines:



PREDNO TYPE LEFT_HAND_SIDE

------+---------+---------+---------+

1 OR

2 EQUAL PREDNO

3 EQUAL LEFT_HAND_SIDE





Looks fine to me!



Roy Boxwell

SOFTWARE ENGINEERING GmbH and SEGUS Inc.
-Product Development-

Heinrichstrasse 83-85
40239 Duesseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: <mailto:[login to unmask email]> [login to unmask email]
Web http://www.seg.de http://www.seg.de

https://www.seg.de/corporate/rechtliche-hinweise/datenschutz Link zur Datenschutzerklärung


Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert, Ulf Heinrich



From: Venkata Sai Arjun Pettam [mailto:[login to unmask email]
Sent: Tuesday, July 23, 2019 8:04 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: AW: ZOS DBA : DSN_FILTER TABLE



Hi,

I have a doubt regarding how the DSN_FILTER_TABLE is populated. For the following SQL Query instead of having two predicate entries it has only one entry for the whole condition as single compound predicate in DSN_FILTER_TABLE.



SQL Query:



SELECT * FROM DSN_PREDICAT_TABLE WHERE PREDNO = QUERYNO OR LEFT_HAND_SIDE = RIGHT_HAND_SIDE;



This is just a sample query.

Both the predicates are stage 2 predicates.

Please let me know is it the expected entry to the table.



-----End Original Message-----

Phil Grainger

AW: ZOS DBA : DSN_FILTER TABLE
(in response to Joe Geller)
Have you transposed “dependent” and “independent” Joe?

In my head, I imagine ANDs as being DEPENDENT (as you say, they ALL have to be true – they depend on each other)

And ORs are INDEPENDENT (any one can be true, independent of the others)

Phil Grainger
Principal Enablement Manager

[BMC Exchange 2019 - Global Event Series - REGISTER] https://www.bmc.com/ami

Direct

+44 1189 218 000

Mobile

+44 7808 643 479

Email

[login to unmask email]

E2, Eskdale Road
Winnersh
Berkshire
United Kingdom
RG41 5TS
[image001 (002)] [cid:[login to unmask email] [https://acclaim-production-app.s3.amazonaws.com/images/2429c3cd-a1de-44fc-b4f3-bc762bb2f963/IBM%2BChampion%2B-%2BAnalytics%2B2018.png]



From: Joe Geller <[login to unmask email]>
Sent: 23 July 2019 12:36
To: [login to unmask email]
Subject: [EXTERNAL] [DB2-L] - RE: AW: ZOS DBA : DSN_FILTER TABLE


Yes, generally speaking that is what you get with ORs. ANDs are independent predicates - each one must be satisfied. ORs with different columns are dependent - only one has to be satisfied (also known as Inclusive OR). (This is a simplification).

Joe

In Reply to Roy Boxwell:
In the filter table I get one line keyed to the DSN_PREDICAT_TABLE PREDNO = 1 and in that table I get three lines:



PREDNO TYPE LEFT_HAND_SIDE

------+---------+---------+---------+

1 OR

2 EQUAL PREDNO

3 EQUAL LEFT_HAND_SIDE





Looks fine to me!



Roy Boxwell

SOFTWARE ENGINEERING GmbH and SEGUS Inc.
-Product Development-

Heinrichstrasse 83-85
40239 Duesseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: <mailto:[login to unmask email]><mailto:[login to unmask email]%3e> [login to unmask email]<mailto:[login to unmask email]>
Web http://www.seg.de https://urldefense.proofpoint.com/v2/url?u=http-3A__www.seg.de&d=DwMFaQ&c=UrUhmHsiTVT5qkaA4d_oSzcamb9hmamiCDMzBAEwC7E&r=EAGrd_qzLADPfI8dgytr8sbCG7_U9QfXwQMLgK1Zo30&m=BIkAZ8SOam_AnN0UmL9kObDfFKTg0vpaF_yN7zDG0Kw&s=ghcCgcnaJAe5OxtWzzdykF-aWsHXkvmF8hPoXLoDR0w&e= http://www.seg.de https://urldefense.proofpoint.com/v2/url?u=http-3A__www.seg.de&d=DwMFaQ&c=UrUhmHsiTVT5qkaA4d_oSzcamb9hmamiCDMzBAEwC7E&r=EAGrd_qzLADPfI8dgytr8sbCG7_U9QfXwQMLgK1Zo30&m=BIkAZ8SOam_AnN0UmL9kObDfFKTg0vpaF_yN7zDG0Kw&s=ghcCgcnaJAe5OxtWzzdykF-aWsHXkvmF8hPoXLoDR0w&e=

https://www.seg.de/corporate/rechtliche-hinweise/datenschutz https://urldefense.proofpoint.com/v2/url?u=https-3A__www.seg.de_corporate_rechtliche-2Dhinweise_datenschutz&d=DwMFaQ&c=UrUhmHsiTVT5qkaA4d_oSzcamb9hmamiCDMzBAEwC7E&r=EAGrd_qzLADPfI8dgytr8sbCG7_U9QfXwQMLgK1Zo30&m=BIkAZ8SOam_AnN0UmL9kObDfFKTg0vpaF_yN7zDG0Kw&s=BCa5MQWnH5Cv_ZThU0Pc5_1YW6gRHw739DaBMUQ2gSo&e= Link zur Datenschutzerklärung


Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert, Ulf Heinrich



From: Venkata Sai Arjun Pettam [mailto:[login to unmask email]
Sent: Tuesday, July 23, 2019 8:04 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: AW: ZOS DBA : DSN_FILTER TABLE



Hi,

I have a doubt regarding how the DSN_FILTER_TABLE is populated. For the following SQL Query instead of having two predicate entries it has only one entry for the whole condition as single compound predicate in DSN_FILTER_TABLE.



SQL Query:



SELECT * FROM DSN_PREDICAT_TABLE WHERE PREDNO = QUERYNO OR LEFT_HAND_SIDE = RIGHT_HAND_SIDE;



This is just a sample query.

Both the predicates are stage 2 predicates.

Please let me know is it the expected entry to the table.



-----End Original Message-----

-----End Original Message-----
BMC Software Limited Registered Office: Building E2, Eskdale Road, Winnersh, Wokingham, Berkshire, United Kingdom, RG41 5TS Registered in England No. 1927903 The content of this email is confidential. If you are not the addressee, you may not distribute, copy or disclose any part of it. If you receive this message in error, please delete this from your system and notify the sender immediately.
Attachments

  • image001.jpg (49.7k)
  • image002.png (6.7k)
  • image003.jpg (1.6k)
  • image004.png (<1k)

Joe Geller

RE: AW: ZOS DBA : DSN_FILTER TABLE
(in response to Phil Grainger)

Hi Phil,

I over-simplified too much and lost clarity.  What I meant was that the filter factors of predicates that are ANDed are calculated independently of each other.  These filter factors are then multiplied to get the filter factor of both being satisfied (e.g. SEX='M' AND EYECOLOR='BROWN'.  If sex='M' has a FF of .5 and 'BROWN' is .8, then .4 of the people will be brown eyed males.)

There are two types of OR - inclusive and exclusive.  COLA=x OR COLA=y is exclusive (COLA cannot be both x and y).  The filter factors are calculated separately and then added (e.g. if .2 are x and .15 are y then the combined FF is .35).    COLA=x OR COLB=z are inclusive.  The OR will be satisfied if either or both are true).  To get the combined FF you have to add the two and then subtract the FF of both being satisfied.  That is what I meant by dependent. 

I certainly did not make that clear, nor is it necessarily the correct usage of the words dependent and independent.  I'd better shut up now before I make things even more confusing.

Joe

In Reply to Phil Grainger:

Have you transposed “dependent” and “independent” Joe?

In my head, I imagine ANDs as being DEPENDENT (as you say, they ALL have to be true – they depend on each other)

And ORs are INDEPENDENT (any one can be true, independent of the others)

Phil Grainger
Principal Enablement Manager

[BMC Exchange 2019 - Global Event Series - REGISTER] https://www.bmc.com/ami

Direct

+44 1189 218 000

Mobile

+44 7808 643 479

Email

[login to unmask email]

E2, Eskdale Road
Winnersh
Berkshire
United Kingdom
RG41 5TS
[image001 (002)] [cid:[login to unmask email] [https://acclaim-production-app.s3.amazonaws.com/images/2429c3cd-a1de-44fc-b4f3-bc762bb2f963/IBM%2BChampion%2B-%2BAnalytics%2B2018.png]



From: Joe Geller <[login to unmask email]>
Sent: 23 July 2019 12:36
To: [login to unmask email]
Subject: [EXTERNAL] [DB2-L] - RE: AW: ZOS DBA : DSN_FILTER TABLE


Yes, generally speaking that is what you get with ORs. ANDs are independent predicates - each one must be satisfied. ORs with different columns are dependent - only one has to be satisfied (also known as Inclusive OR). (This is a simplification).

Joe

In Reply to Roy Boxwell:
In the filter table I get one line keyed to the DSN_PREDICAT_TABLE PREDNO = 1 and in that table I get three lines:



PREDNO TYPE LEFT_HAND_SIDE

------+---------+---------+---------+

1 OR

2 EQUAL PREDNO

3 EQUAL LEFT_HAND_SIDE





Looks fine to me!



Roy Boxwell

SOFTWARE ENGINEERING GmbH and SEGUS Inc.
-Product Development-

Heinrichstrasse 83-85
40239 Duesseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: <mailto:[login to unmask email]><mailto:[login to unmask email]%3e> [login to unmask email]<mailto:[login to unmask email]>
Web http://www.seg.de https://urldefense.proofpoint.com/v2/url?u=http-3A__www.seg.de&d=DwMFaQ&c=UrUhmHsiTVT5qkaA4d_oSzcamb9hmamiCDMzBAEwC7E&r=EAGrd_qzLADPfI8dgytr8sbCG7_U9QfXwQMLgK1Zo30&m=BIkAZ8SOam_AnN0UmL9kObDfFKTg0vpaF_yN7zDG0Kw&s=ghcCgcnaJAe5OxtWzzdykF-aWsHXkvmF8hPoXLoDR0w&e= http://www.seg.de https://urldefense.proofpoint.com/v2/url?u=http-3A__www.seg.de&d=DwMFaQ&c=UrUhmHsiTVT5qkaA4d_oSzcamb9hmamiCDMzBAEwC7E&r=EAGrd_qzLADPfI8dgytr8sbCG7_U9QfXwQMLgK1Zo30&m=BIkAZ8SOam_AnN0UmL9kObDfFKTg0vpaF_yN7zDG0Kw&s=ghcCgcnaJAe5OxtWzzdykF-aWsHXkvmF8hPoXLoDR0w&e=

https://www.seg.de/corporate/rechtliche-hinweise/datenschutz https://urldefense.proofpoint.com/v2/url?u=https-3A__www.seg.de_corporate_rechtliche-2Dhinweise_datenschutz&d=DwMFaQ&c=UrUhmHsiTVT5qkaA4d_oSzcamb9hmamiCDMzBAEwC7E&r=EAGrd_qzLADPfI8dgytr8sbCG7_U9QfXwQMLgK1Zo30&m=BIkAZ8SOam_AnN0UmL9kObDfFKTg0vpaF_yN7zDG0Kw&s=BCa5MQWnH5Cv_ZThU0Pc5_1YW6gRHw739DaBMUQ2gSo&e= Link zur Datenschutzerklärung


Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert, Ulf Heinrich



From: Venkata Sai Arjun Pettam [mailto:[login to unmask email]
Sent: Tuesday, July 23, 2019 8:04 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: AW: ZOS DBA : DSN_FILTER TABLE



Hi,

I have a doubt regarding how the DSN_FILTER_TABLE is populated. For the following SQL Query instead of having two predicate entries it has only one entry for the whole condition as single compound predicate in DSN_FILTER_TABLE.



SQL Query:



SELECT * FROM DSN_PREDICAT_TABLE WHERE PREDNO = QUERYNO OR LEFT_HAND_SIDE = RIGHT_HAND_SIDE;



This is just a sample query.

Both the predicates are stage 2 predicates.

Please let me know is it the expected entry to the table.



-----End Original Message-----

-----End Original Message-----
BMC Software Limited Registered Office: Building E2, Eskdale Road, Winnersh, Wokingham, Berkshire, United Kingdom, RG41 5TS Registered in England No. 1927903 The content of this email is confidential. If you are not the addressee, you may not distribute, copy or disclose any part of it. If you receive this message in error, please delete this from your system and notify the sender immediately.

Venkata Sai Arjun Pettam

RE: AW: ZOS DBA : DSN_FILTER TABLE
(in response to Joe Geller)

Hi Joe,

Firstly, thanks for your reply.

For OR as you said FF is calculated  by adding the two and then subtracting the FF of both being satisfied, my question is why the segregated predicates with 'OR' (1. 'PREDNO = QUERYNO' and 2. 'LEFT_HAND_SIDE = RIGHT_HAND_SIDE' in the sample SQL provided in initial comment) are not being listed in DSN_FILTER_TABLE and why the information for compound predicate is listed.

Thanks & regards,

Arjun

Roy Boxwell

AW: ZOS DBA : DSN_FILTER TABLE
(in response to Venkata Sai Arjun Pettam)
Because it is one filter factor - simple as that!

Roy Boxwell
SOFTWARE ENGINEERING GmbH and SEGUS Inc.
-Product Development-
Heinrichstrasse 83-85
40239 Düsseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]
http://www.seg.de
Link zur Datenschutzerklärung

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert, Ulf Heinrich

> On 24 Jul 2019, at 14:29, Venkata Sai Arjun Pettam <[login to unmask email]> wrote:
>
> Hi Joe,
>
> Firstly, thanks for your reply.
>
> For OR as you said FF is calculated by adding the two and then subtracting the FF of both being satisfied, my question is why the segregated predicates with 'OR' (1. 'PREDNO = QUERYNO' and 2. 'LEFT_HAND_SIDE = RIGHT_HAND_SIDE' in the sample SQL provided in initial comment) are not being listed in DSN_FILTER_TABLE and why the information for compound predicate is listed.
>
> Thanks & regards,
>
> Arjun
>
>
> Site Links: View post online View mailing list online Start new thread via email Unsubscribe from this mailing list Manage your subscription
>
> This email has been sent to: [login to unmask email]
> Discover the best cloning tool on the market. Try BCV5 & the new BCV5 Masking Tool.
> ESAi also has powerful solutions for Buffer Pool Tuning, Log Analysis, TDM, & more.
> http://www.ESAIGroup.com/idug
>
>
> Use of this email content is governed by the terms of service at:
> http://www.idug.org/p/cm/ld/fid=2
>
Attachments

  • smime.p7s (3.9k)

Venkata Sai Arjun Pettam

RE: AW: ZOS DBA : DSN_FILTER TABLE
(in response to Roy Boxwell)

Understood, thank you so much!

Arjun

Michael Hannan

RE: AW: ZOS DBA : DSN_FILTER TABLE
(in response to Roy Boxwell)

In Reply to Roy Boxwell:

Because it is one filter factor - simple as that!

Yes sometimes simple. Ha ha. Not always.  Mostly the FF applies to column on the LEFT_HAND_SIDE but occasionally to RHS for some of the joins depending on direction.

This predicate confuses me:
AND NAME LIKE '%ANNE%

It does good filtering right? However it does not limit the index scan at all, does screening though to limit the rows returned. If this value was in a Hostvar at Bind time DB2 has no clue what the filtering will be.

AND NAME LIKE :hostvar can be a match column, but may scan the entire index. So if hostvar is '%', FF=1. If hostvar is '%ANNE%', what is the true FF? It still scans the entire index but less result rows. Seems to me there should be more than one FF per predicate. Having just one works for most cases.

Filter Factor for  PRED1 OR PRED2 is typically:

FF(PRED1) + FF(PRED2) - FF(PRED1)*FF(PRED2)
so not a simple addition. e.g 0.6 + 0.8 - 0.6*0.8 = 1.4 - 0.48 = 0.92

From memory DSN_FILTER_TABLE does show Stage detail for the given access path:

Matching, Index Screening, Stage 1, Stage 2. It does not exactly show "indexable" predictes (as that is hypothetical). However generally any Stage 1 predicate is hypothetically "Indexable" provided it is not a NOT predicate, e.g. "NOT=" is Stage 1 but not indexable. Similarly NOT LIKE. NOT NULL is indexable (sort of) because it can limit the index scan, and can be a matching column. Indexable basically means can be a matching column if the column is provided in the right position in an index. The "Not" predicate indicator column is in DSN_PREDICATE_TABLE. forgot its name for now.

A set of Stage 1 predicates ANDed and ORed, even including NOTs, remains stage 1 if the predicate group is on one table. As soon as 2 tables are referenced, it becomes a join or after join predicate. Any Stage 2 predicate makes a whole ORed group Stage 2.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd