Set alert whenever nested loop join is used by SQL - DB2 Z/OS

Solt K

Set alert whenever nested loop join is used by SQL - DB2 Z/OS

Hello All,

We have a requirement to throw alert whenever a SQL statement chooses nested loop join in DB2 Z/OS. Is there any way we can pull out this detail?

As we know explain tables can be queried to list all the plans/packages that are using nested loop join. But what if they were bound using EXPLAIN(NO) and if it was a dynamic query coming from the distributed server.

Please provide your advice on this. 

 

Thank You.

Chris Hoelscher

Set alert whenever nested loop join is used by SQL - DB2 Z/OS
(in response to Solt K)
Need this be a real-time alert? Or can it be end-of-day summary ?

Chris Hoelscher
Technology Architect, Database Infrastructure Services
Technology Solution Services
Humana Inc.
123 East Main Street
Louisville, KY 40202
Humana.com
(502) 476-2538 or 407-7266

From: Solt K [mailto:[login to unmask email]
Sent: Wednesday, May 2, 2018 8:01 PM
To: [login to unmask email]
Subject: [DB2-L] - Set alert whenever nested loop join is used by SQL - DB2 Z/OS


Hello All,

We have a requirement to throw alert whenever a SQL statement chooses nested loop join in DB2 Z/OS. Is there any way we can pull out this detail?

As we know explain tables can be queried to list all the plans/packages that are using nested loop join. But what if they were bound using EXPLAIN(NO) and if it was a dynamic query coming from the distributed server.

Please provide your advice on this.



Thank You.

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

The information transmitted is intended only for the person or entity to which it is addressed
and may contain CONFIDENTIAL material. If you receive this material/information in error,
please contact the sender and delete or destroy the material/information.

Humana Inc. and its subsidiaries comply with applicable Federal civil rights laws and
do not discriminate on the basis of race, color, national origin, age, disability or
sex. Humana Inc. and its subsidiaries do not exclude people or treat them differently
because of race, color, national origin, age, disability or sex.

English: ATTENTION: If you do not speak English, language assistance services, free
of charge, are available to you. Call 1‐877‐320‐1235 (TTY: 711).

Español (Spanish): ATENCIÓN: Si habla español, tiene a su disposición servicios
gratuitos de asistencia lingüística. Llame al 1‐877‐320‐1235 (TTY: 711).

繁體中文(Chinese):注意:如果您使用繁體中文,您可以免費獲得語言援助
服務。請致電 1‐877‐320‐1235 (TTY: 711)。

Kreyòl Ayisyen (Haitian Creole): ATANSION: Si w pale Kreyòl Ayisyen, gen sèvis èd
pou lang ki disponib gratis pou ou. Rele 1‐877‐320‐1235 (TTY: 711).

Polski (Polish): UWAGA: Jeżeli mówisz po polsku, możesz skorzystać z bezpłatnej
pomocy językowej. Zadzwoń pod numer 1‐877‐320‐1235 (TTY: 711).

한국어 (Korean): 주의: 한국어를 사용하시는 경우, 언어 지원 서비스를 무료로
이용하실 수 있습니다. 1‐877‐320‐1235 (TTY: 711)번으로 전화해 주십시오.

James Campbell

Set alert whenever nested loop join is used by SQL - DB2 Z/OS
(in response to Solt K)
IFCID 22 - QW0022OD =4,5,6 or 7.

James Campbell

On 2 May 2018 at 17:01, Solt K wrote:

>
> Hello All,
> We have a requirement to throw alert whenever a SQL statement chooses nested loop join in
> DB2 Z/OS. Is there any way we can pull out this detail?
> As we know explain tables can be queried to list all the plans/packages that are using nested loop
> join. But what if they were bound using EXPLAIN(NO) and if it was a dynamic query coming from
> the distributed server.
> Please provide your advice on this. 
>  
> Thank You.
>

---
This email has been checked for viruses by AVG.
http://www.avg.com

Patrick Bossman

RE: Set alert whenever nested loop join is used by SQL - DB2 Z/OS
(in response to James Campbell)

Wow.  NLJ is the most common join method.  It is more commonly chosen than the others combined, and it is not even close.

 

May I ask ... What is the source of this requirement?

Edited By:
Patrick Bossman[Organization Members] @ May 03, 2018 - 05:41 AM (America/Eastern)

Solt K

RE: Set alert whenever nested loop join is used by SQL - DB2 Z/OS
(in response to Patrick Bossman)

Hi Patrick,

 

We are looking particularly for Cartesian join as they often lead to high cpu usage.

Solt K

RE: Set alert whenever nested loop join is used by SQL - DB2 Z/OS
(in response to James Campbell)

Hi James,

 

Thanks for the information. I will check IFCID 22 - QW0022OD =4,5,6 or 7..

Solt K

RE: Set alert whenever nested loop join is used by SQL - DB2 Z/OS
(in response to Chris Hoelscher)

Hello Chris,

It can be end-of-day summary.

James Campbell

Set alert whenever nested loop join is used by SQL - DB2 Z/OS
(in response to Solt K)
Then , despite it its inaccuracies, perhaps
QW0022OS COST FOR THE SQL STATEMENT (FLOAT)
QW0022AS Processor Cost Estimate (SU)
might be a better targets.

Not to mention, for those pesky dynamic statements, RLF.

James Campbell


On 3 May 2018 at 9:44, Solt K wrote:

>
> Hi Patrick,
>  
> We are looking particularly for Cartesian join as they often lead to high cpu usage.
>
>

---
This email has been checked for viruses by AVG.
http://www.avg.com

Patrick Bossman

RE: Set alert whenever nested loop join is used by SQL - DB2 Z/OS
(in response to Solt K)

The false positive rate for notifications will be large.  I would think you would need the explain data processed via automation also.  

Terry Purcell

RE: Set alert whenever nested loop join is used by SQL - DB2 Z/OS
(in response to Solt K)

I agree with Patrick's surprise here - as typically you are looking for a needle in a haystack (given that NLJ is the predominant join method). But given that you are looking, that implies that you have either been burned badly in the past, or you see many of these (or both).

Cartesian joins to combine filtering from multiple tables before joining to a larger table can be a very good thing. Where it's bad - 1) when the size of the tables participating in the cartesian join are estimated to be smaller than reality, AND/OR 2) the resultant join to the large(r) table does NOT result in increased MATCHCOLS from the additional table(s).

So if you are having this issue - find one example and look at the indexing on the large table to see if you can alter it to support increased matching. But also (and more importantly) - look at any statistics recommendations (either thru tooling or Db2 optimizer's externalized recommendations) on those cartesian join tables to see what would help.

There have been a couple of APARs in 2016/2017 to reduce the optimizer's choice of a cartesian join unless it will increase matching on the larger table.

Regards

Terry Purcell

In Reply to Solt K:

Hi Patrick,

 

We are looking particularly for Cartesian join as they often lead to high cpu usage.

Walter Janißen

AW: Set alert whenever nested loop join is used by SQL - DB2 Z/OS
(in response to Terry Purcell)
Hi

I also see from time to time queries, which are selecting data from very big tables, which are joined without a join condition, e.g.
SELECT DISTINCT ... FROM BIG_T1, BIG_T2 WHERE BIG_T1 predicates
They just forgot to provide the join predicates. I raised an RFE to get at least a warning message, but unfortunately this RFE was declined. In general these queries don’t make any sense, but burn unnecessary a lot of CPU.

Kind regards
Walter Janißen

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Technische Anwendungsarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
Tel.: +49(0)211/477-2928
Fax: +49(0)211/477-6441
[login to unmask email]<mailto:[login to unmask email]>

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

Von: Terry Purcell <[login to unmask email]>
Gesendet: Freitag, 4. Mai 2018 14:45
An: [login to unmask email]
Betreff: [DB2-L] - RE: Set alert whenever nested loop join is used by SQL - DB2 Z/OS


I agree with Patrick's surprise here - as typically you are looking for a needle in a haystack (given that NLJ is the predominant join method). But given that you are looking, that implies that you have either been burned badly in the past, or you see many of these (or both).

Cartesian joins to combine filtering from multiple tables before joining to a larger table can be a very good thing. Where it's bad - 1) when the size of the tables participating in the cartesian join are estimated to be smaller than reality, AND/OR 2) the resultant join to the large(r) table does NOT result in increased MATCHCOLS from the additional table(s).

So if you are having this issue - find one example and look at the indexing on the large table to see if you can alter it to support increased matching. But also (and more importantly) - look at any statistics recommendations (either thru tooling or Db2 optimizer's externalized recommendations) on those cartesian join tables to see what would help.

There have been a couple of APARs in 2016/2017 to reduce the optimizer's choice of a cartesian join unless it will increase matching on the larger table.

Regards

Terry Purcell

In Reply to Solt K:

Hi Patrick,



We are looking particularly for Cartesian join as they often lead to high cpu usage.

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

Terry Purcell

RE: AW: Set alert whenever nested loop join is used by SQL - DB2 Z/OS
(in response to Walter Janißen)

Hi Walter,

Yes, I declined that RFE. In Db2 we will assume that any SQL that we receive is correct. It is very difficult for Db2 to decide what SQLs potentially contain coding errors and which do not. If Db2 would start to provide such recommendations, it is similarly difficult to know how far we should venture into providing query tuning and rewrite advice - it is a large space which already has some vendor tooling that attempts to address this.

Regards

Terry Purcell

In Reply to Walter Janißen:

Hi

I also see from time to time queries, which are selecting data from very big tables, which are joined without a join condition, e.g.
SELECT DISTINCT ... FROM BIG_T1, BIG_T2 WHERE BIG_T1 predicates
They just forgot to provide the join predicates. I raised an RFE to get at least a warning message, but unfortunately this RFE was declined. In general these queries don’t make any sense, but burn unnecessary a lot of CPU.

Kind regards
Walter Janißen

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Technische Anwendungsarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
Tel.: +49(0)211/477-2928
Fax: +49(0)211/477-6441
[login to unmask email]<mailto:[login to unmask email]>

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

Von: Terry Purcell <[login to unmask email]>
Gesendet: Freitag, 4. Mai 2018 14:45
An: [login to unmask email]
Betreff: [DB2-L] - RE: Set alert whenever nested loop join is used by SQL - DB2 Z/OS


I agree with Patrick's surprise here - as typically you are looking for a needle in a haystack (given that NLJ is the predominant join method). But given that you are looking, that implies that you have either been burned badly in the past, or you see many of these (or both).

Cartesian joins to combine filtering from multiple tables before joining to a larger table can be a very good thing. Where it's bad - 1) when the size of the tables participating in the cartesian join are estimated to be smaller than reality, AND/OR 2) the resultant join to the large(r) table does NOT result in increased MATCHCOLS from the additional table(s).

So if you are having this issue - find one example and look at the indexing on the large table to see if you can alter it to support increased matching. But also (and more importantly) - look at any statistics recommendations (either thru tooling or Db2 optimizer's externalized recommendations) on those cartesian join tables to see what would help.

There have been a couple of APARs in 2016/2017 to reduce the optimizer's choice of a cartesian join unless it will increase matching on the larger table.

Regards

Terry Purcell

In Reply to Solt K:

Hi Patrick,



We are looking particularly for Cartesian join as they often lead to high cpu usage.

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

Michael Hannan

RE: Set alert whenever nested loop join is used by SQL - DB2 Z/OS
(in response to Solt K)

In Reply to Solt K:

Hi Patrick, 

We are looking particularly for Cartesian join as they often lead to high cpu usage.

Solt K,

Wrong Cartesian Joins or semi-catesian joins (theta joins missing a join predicate) do exist in Production systems often masked by SELECT DISTINCT eliminating all the duplicate results. I have seen them. This makes it a good topic.
 
Cartesian join can be detected using DSN_PREDICAT_TABLE table if you have the Explain output. Any table accessed in the explain with a join that has no join predicates, only local predicates, or no predicates at all. 

For Dynamic SQL you might need to use Predictive Resource limiting to prevent horrific SQLs from happening. It is possible to build an Explain facility for use by users before executing their Ad Hoc Dynamic queries, and include a warning for very high cost, and also for joined tables seeeming to lack adequate join predicates.

The whole thing becomes a lot more complicated for Theta Joins, where instead of all join predicates missing, have some join predicates but just one has been forgotten. Yes, seen quite often in real life.

Another variation is an OR in the predicates with missing brackets that causes cartesian like processing.

I would like to build a query to detect some of these situations in Explain, but I don't have it right now.

If the shop uses Referential Integrity, then the relationships between tables can help suggest the likely join predicates, but it is still complicated by indirect joins, and unusual joins not according to the RI relationships.

Cartesian join can be a valid access path, in a multi-table join, assuming one of the tables is quite small, especially if first table of the join has just one row. Also Data Warehouse queries often use cartesian join of small tables.

Detecting Nested Loop Join, is definitely not the the answer to your true problem, and hardly relevant, even though Hybrid and Merge Scan might not support a join with no predicates, Hybrid could possibly occur for a table in join with local predicates only (I am not sure). NLJ is the normal join process.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Edited By:
Michael Hannan[Organization Members] @ May 06, 2018 - 12:57 PM (Europe/Berlin)
Michael Hannan[Organization Members] @ May 06, 2018 - 12:59 PM (Europe/Berlin)
Michael Hannan[Organization Members] @ May 06, 2018 - 12:59 PM (Europe/Berlin)

Suresh Sane

AW: Set alert whenever nested loop join is used by SQL - DB2 Z/OS
(in response to Terry Purcell)
Terry/Patrick,


Fully agree with the approach that the engine should not be in the business of guessing what the user "probably" meant.


Solt/Walter,


I think the old style of SQL is the main culprit for Cartesian joins.


The "old style":


SELECT ...FROM A, B..WHERE...


Vs. the "new style"


SELECT ...FROM A (INNER) JOIN B ON...WHERE...


All predicates are not the same and I wish SQL never allowed the old style which leads to accidental omission of join conditions resulting in Cartesian joins.

The ON predicates are "structure predicates" which are dictated by the ERD. The WHERE predicates are dictated by the business function. They should be coded separately. In my earlier job, I developed a SQL generator which would add the structure predicates automatically as many GUI tools do.


By forcing all developers to use the new style (with the ON clause), it makes it virtually impossible to have a Cartesian join. I concede they could still force one by using "ON 1=1", but at least it is a conscious decision and not an oversight.

This adds the safety catch they need to remove before shooting themselves in the foot.


Thx

Suresh


________________________________
From: Terry Purcell <[login to unmask email]>
Sent: Friday, May 4, 2018 2:04 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: AW: Set alert whenever nested loop join is used by SQL - DB2 Z/OS


Hi Walter,

Yes, I declined that RFE. In Db2 we will assume that any SQL that we receive is correct. It is very difficult for Db2 to decide what SQLs potentially contain coding errors and which do not. If Db2 would start to provide such recommendations, it is similarly difficult to know how far we should venture into providing query tuning and rewrite advice - it is a large space which already has some vendor tooling that attempts to address this.

Regards

Terry Purcell

In Reply to Walter Janißen:

Hi

I also see from time to time queries, which are selecting data from very big tables, which are joined without a join condition, e.g.
SELECT DISTINCT ... FROM BIG_T1, BIG_T2 WHERE BIG_T1 predicates
They just forgot to provide the join predicates. I raised an RFE to get at least a warning message, but unfortunately this RFE was declined. In general these queries don’t make any sense, but burn unnecessary a lot of CPU.

Kind regards
Walter Janißen

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Technische Anwendungsarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
Tel.: +49(0)211/477-2928
Fax: +49(0)211/477-6441
[login to unmask email]<mailto:[login to unmask email]>

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

Von: Terry Purcell
Gesendet: Freitag, 4. Mai 2018 14:45
An: [login to unmask email]
Betreff: [DB2-L] - RE: Set alert whenever nested loop join is used by SQL - DB2 Z/OS


I agree with Patrick's surprise here - as typically you are looking for a needle in a haystack (given that NLJ is the predominant join method). But given that you are looking, that implies that you have either been burned badly in the past, or you see many of these (or both).

Cartesian joins to combine filtering from multiple tables before joining to a larger table can be a very good thing. Where it's bad - 1) when the size of the tables participating in the cartesian join are estimated to be smaller than reality, AND/OR 2) the resultant join to the large(r) table does NOT result in increased MATCHCOLS from the additional table(s).

So if you are having this issue - find one example and look at the indexing on the large table to see if you can alter it to support increased matching. But also (and more importantly) - look at any statistics recommendations (either thru tooling or Db2 optimizer's externalized recommendations) on those cartesian join tables to see what would help.

There have been a couple of APARs in 2016/2017 to reduce the optimizer's choice of a cartesian join unless it will increase matching on the larger table.

Regards

Terry Purcell

In Reply to Solt K:

Hi Patrick,



We are looking particularly for Cartesian join as they often lead to high cpu usage.

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

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

Anguraj Rathinasamy

AW: Set alert whenever nested loop join is used by SQL - DB2 Z/OS
(in response to Suresh Sane)
If you any query monitor tool. That would help to identify high resource consumed (bad Sql). In our shop effectively using db2 query monitor to identify and poor performance Sql.

Anguraj


Sent from my iPhone

> On May 7, 2018, at 12:01 PM, suresh sane <[login to unmask email]> wrote:
>
> Terry/Patrick,
>
> Fully agree with the approach that the engine should not be in the business of guessing what the user "probably" meant.
>
> Solt/Walter,
>
> I think the old style of SQL is the main culprit for Cartesian joins.
>
> The "old style":
>
> SELECT ...FROM A, B..WHERE...
>
> Vs. the "new style"
>
> SELECT ...FROM A (INNER) JOIN B ON...WHERE...
>
> All predicates are not the same and I wish SQL never allowed the old style which leads to accidental omission of join conditions resulting in Cartesian joins.
>
> The ON predicates are "structure predicates" which are dictated by the ERD. The WHERE predicates are dictated by the business function. They should be coded separately. In my earlier job, I developed a SQL generator which would add the structure predicates automatically as many GUI tools do.
>
> By forcing all developers to use the new style (with the ON clause), it makes it virtually impossible to have a Cartesian join. I concede they could still force one by using "ON 1=1", but at least it is a conscious decision and not an oversight.
> This adds the safety catch they need to remove before shooting themselves in the foot.
>
> Thx
> Suresh
>
> From: Terry Purcell <[login to unmask email]>
> Sent: Friday, May 4, 2018 2:04 PM
> To: [login to unmask email]
> Subject: [DB2-L] - RE: AW: Set alert whenever nested loop join is used by SQL - DB2 Z/OS
>
> Hi Walter,
>
> Yes, I declined that RFE. In Db2 we will assume that any SQL that we receive is correct. It is very difficult for Db2 to decide what SQLs potentially contain coding errors and which do not. If Db2 would start to provide such recommendations, it is similarly difficult to know how far we should venture into providing query tuning and rewrite advice - it is a large space which already has some vendor tooling that attempts to address this.
>
> Regards
>
> Terry Purcell
>
> In Reply to Walter Janißen:
>
> Hi
>
> I also see from time to time queries, which are selecting data from very big tables, which are joined without a join condition, e.g.
> SELECT DISTINCT ... FROM BIG_T1, BIG_T2 WHERE BIG_T1 predicates
> They just forgot to provide the join predicates. I raised an RFE to get at least a warning message, but unfortunately this RFE was declined. In general these queries don’t make any sense, but burn unnecessary a lot of CPU.
>
> Kind regards
> Walter Janißen
>
> ITERGO Informationstechnologie GmbH
> Anwendungsentwicklung
> Technische Anwendungsarchitektur
> Victoriaplatz 2
> D-40198 Düsseldorf
> Tel.: +49(0)211/477-2928
> Fax: +49(0)211/477-6441
> [login to unmask email]<mailto:[login to unmask email]>
>
> ITERGO Informationstechnologie GmbH
> Vorsitzender des Aufsichtsrats: Christian Diedrich
> Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
> Lothar Engelke, Ina Kirchhof, Dr. Michael Regauer
> Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf HRB 37996
>
> Von: Terry Purcell
> Gesendet: Freitag, 4. Mai 2018 14:45
> An: [login to unmask email]
> Betreff: [DB2-L] - RE: Set alert whenever nested loop join is used by SQL - DB2 Z/OS
>
>
> I agree with Patrick's surprise here - as typically you are looking for a needle in a haystack (given that NLJ is the predominant join method). But given that you are looking, that implies that you have either been burned badly in the past, or you see many of these (or both).
>
> Cartesian joins to combine filtering from multiple tables before joining to a larger table can be a very good thing. Where it's bad - 1) when the size of the tables participating in the cartesian join are estimated to be smaller than reality, AND/OR 2) the resultant join to the large(r) table does NOT result in increased MATCHCOLS from the additional table(s).
>
> So if you are having this issue - find one example and look at the indexing on the large table to see if you can alter it to support increased matching. But also (and more importantly) - look at any statistics recommendations (either thru tooling or Db2 optimizer's externalized recommendations) on those cartesian join tables to see what would help.
>
> There have been a couple of APARs in 2016/2017 to reduce the optimizer's choice of a cartesian join unless it will increase matching on the larger table.
>
> Regards
>
> Terry Purcell
>
> In Reply to Solt K:
>
> Hi Patrick,
>
>
>
> We are looking particularly for Cartesian join as they often lead to high cpu usage.
>
> -----End Original Message-----
>
> 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]
> Faster data refresh is here! The long waits and babysitting of unload/load jobs is over. Contact
> ESAi to learn about BCV5 & XDM. Be a hero to users with fast on-demand test/QA data provisioning.See
> 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
>
>
> 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]
> ** ** ** IDUG Db2 Tech Conference in Sydney, Australia 2018 ** ** **
> ---> Sydney, Australia 11 - 13 September, 2018 <---
> http://www.idug.org/au
>
> Use of this email content is governed by the terms of service at:
> http://www.idug.org/p/cm/ld/fid=2
>

bernd oppolzer

AW: Set alert whenever nested loop join is used by SQL - DB2 Z/OS
(in response to Suresh Sane)
while the new join style does not allow to forget the
join predicates completely, it still allows for errors in the
join conditions.


I teached advanced DB2 SQL classes for about 20 years, and I used
for my exercises a very simple data model of only five tables, but at
one place there was a 1 to n relation which involved two key columns.


When in one of the exercises it was necessary to do a multi-way join
involving these two tables, more than half of my students forgot
the second join column in the ON condition, which lead to very strange
results
(some 200 results instead of 39, which would have been correct).


Such errors are often not obvious, especially if there is some
grouping or counting, and you don't see the original records ...


regarding old style vs. new style:


you have to take history into account. Old style is pre version 4 of DB2
and supports only INNER JOINS. There was no need for a ON condition
and a JOIN keyword (with different variants). With INNER JOIN, join
columns are indeed "equal" on both sides.


With DB2 V4, the different flavors of OUTER JOINs were supported,
and that's why we have the JOIN keyword and the ON condition (which
has semantics different from WHERE, at least with OUTER JOINs).


You cannot forbid old style, because many production joins out there
still are written in old style. The support for this must continue (and:
DB2 does a very good job and uses the same access path for old style
as it does for new style). And, sadly, many people still don't know
new style, because old style is all they learned (they took part in a
DB2 class in, say, 1990, and never learned anything new since).


Kind regards


Bernd




Am 07.05.2018 um 18:01 schrieb suresh sane:
>
> I think the old style of SQL is the main culprit for Cartesian joins.
>
>
> The "old style":
>
>
> SELECT ...FROM A, B..WHERE...
>
>
> Vs. the "new style"
>
>
> SELECT ...FROM A (INNER) JOIN B *ON...WHERE*...
>
>
> All predicates are *not* the same and I wish SQL never allowed the old
> style which leads to accidental omission of join conditions resulting
> in Cartesian joins.
>
> The ON predicates are "structure predicates" which are dictated by the
> ERD.  The WHERE predicates are dictated by the business function. 
> They should be coded separately.  In my earlier job, I developed a SQL
> generator which would add the structure predicates automatically as
> many GUI tools do.
>
>
> By forcing all developers to use the new style (with the ON clause),
> it makes it virtually impossible to have a Cartesian join.  I concede
> they could still force one by using "ON 1=1", but at least it is a
> conscious decision and not an oversight.
>
> This adds the safety catch they need to remove before shooting
> themselves in the foot.
>
>
> Thx
>
> Suresh
>

Philip Sevetson

AW: Set alert whenever nested loop join is used by SQL - DB2 Z/OS
(in response to bernd oppolzer)
It should also be noted that a Cartesian join is not going to affect performance in every circumstance.

Exemplar Gratia: we sometimes join to a nested table expression pulling register values and referencing SYSIBM.SYSDUMMY1. There's no way to _avoid_ a Cartesian join with that table. It's got no columns to join _with_ (IBMREQD doesn't count!!).

--Phil S.

From: Bernd Oppolzer [mailto:[login to unmask email]
Sent: Monday, May 07, 2018 6:20 PM
To: suresh sane
Subject: [DB2-L] - RE: AW: Set alert whenever nested loop join is used by SQL - DB2 Z/OS


while the new join style does not allow to forget the
join predicates completely, it still allows for errors in the
join conditions.



I teached advanced DB2 SQL classes for about 20 years, and I used
for my exercises a very simple data model of only five tables, but at
one place there was a 1 to n relation which involved two key columns.



When in one of the exercises it was necessary to do a multi-way join
involving these two tables, more than half of my students forgot
the second join column in the ON condition, which lead to very strange results
(some 200 results instead of 39, which would have been correct).



Such errors are often not obvious, especially if there is some
grouping or counting, and you don't see the original records ...



regarding old style vs. new style:



you have to take history into account. Old style is pre version 4 of DB2
and supports only INNER JOINS. There was no need for a ON condition
and a JOIN keyword (with different variants). With INNER JOIN, join
columns are indeed "equal" on both sides.



With DB2 V4, the different flavors of OUTER JOINs were supported,
and that's why we have the JOIN keyword and the ON condition (which
has semantics different from WHERE, at least with OUTER JOINs).



You cannot forbid old style, because many production joins out there
still are written in old style. The support for this must continue (and:
DB2 does a very good job and uses the same access path for old style
as it does for new style). And, sadly, many people still don't know
new style, because old style is all they learned (they took part in a
DB2 class in, say, 1990, and never learned anything new since).



Kind regards



Bernd





Am 07.05.2018 um 18:01 schrieb suresh sane:

I think the old style of SQL is the main culprit for Cartesian joins.



The "old style":



SELECT ...FROM A, B..WHERE...



Vs. the "new style"



SELECT ...FROM A (INNER) JOIN B ON...WHERE...


All predicates are not the same and I wish SQL never allowed the old style which leads to accidental omission of join conditions resulting in Cartesian joins.

The ON predicates are "structure predicates" which are dictated by the ERD. The WHERE predicates are dictated by the business function. They should be coded separately. In my earlier job, I developed a SQL generator which would add the structure predicates automatically as many GUI tools do.



By forcing all developers to use the new style (with the ON clause), it makes it virtually impossible to have a Cartesian join. I concede they could still force one by using "ON 1=1", but at least it is a conscious decision and not an oversight.

This adds the safety catch they need to remove before shooting themselves in the foot.



Thx

Suresh

-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**

Michael Hannan

RE: AW: Set alert whenever nested loop join is used by SQL - DB2 Z/OS
(in response to Suresh Sane)

In Reply to Suresh Sane:

I think the old style of SQL is the main culprit for Cartesian joins.

By forcing all developers to use the new style (with the ON clause), it makes it virtually impossible to have a Cartesian join. I concede they could still force one by using "ON 1=1", but at least it is a conscious decision and not an oversight.

This adds the safety catch they need to remove before shooting themselves in the foot.

I agree with your sentiments, even if you exaggerate a little. I certainly use ON clause style joins myself in SQLs. Why would one do otherwise? Certainly makes debugging easier, and recognising strange joins. We even have CROSS JOIN for cases where we don't want to have a join predicate, so we can explicitly choose a Cartesian Join if we want it, especially for joins to 1 row tables or other limited deliberate Cartesian Products. Did anyone notice that crept into DB2 10 for zOS?

Sometimes I code strange joins using PLAN_TABLE, since it is missing the CONTOKEN, SECTNOI was not guaranteed to be populated for old data, use of some columns changed slightly, Queryno could have been zero for cases over 32K, etc. SYSPACKSTMT has various columns, unreliable as well. This is an example that shows not all data to be joined is absolutely perfect.

It is not mandatory to put ALL join predicates in the ON clause. Additional ones can be coded in the WHERE. Perhaps that needs to be banned as well. LOL I am not serious here, since I have been known to put further complex predicates in the WHERE that do affect the join results (even if might be delayed till after some subsequent join).

Unfortunately, ON clause does not elminate faulty Joins, where one of the join predicates is missing, or an OR was not bracketed correctly, one even just wrong columns joined.

There also exist Theta Joins where the join predicates are range type predicates rather than Equals predicates, or a mixture.

Join Predicates can also have serious Data Type mismatches causing Stage 2 processing, e.g joining CHAR field to a Numeric field, with intermediate conversion (implicit Cast) of CHAR to DECFLOAT. Very imited predicates involving DECFLOAT are Stage 1, and join predicate is not one of them. Numeric column was not converted to CHAR, as that would change the official logical meaning of the equals between mismatched data types.

I have seen a horrific case of this non-indexable Equals join predicate, and some other non-Equals type  join predicates (like < or >), get an access path of Nested Loop join with full scan (no match cols) on the inner table. Merge Scan join would only be possible for a sargable Equals predicate, I believe. In this case there was little option but to rewrite the SQL, to make the join predicates workable. 

This case was well beyond the SQL capability of the normal programmer. So never join a numeric to a CHAR column unless you really know how to fix it, probably converting the CHAR column to a compatible numeric data type (not DECFLOAT), which may restrict the viable join sequence.

I think it would be handy to have a query that identifies all joins in Static SQL at least, that do not include full equals unique match on at least one of the tables in the join, being 1:1 or 1:M type join, and so highlight all Theta Join or M:M type joins (including Stage 2 join predicates), then maybe sort them into sequence of how many good equi join predicates were found (0 or more), and perhaps descending by estimated qualifying rows (or cost) to identify the most dangerous joins in the shop. I may try this some day when have time. I believe I did make a crude effort at it many years ago.

Valid M:M joins do exist, of course. So it remains, how to find the faulty ones?

I once fixed a monthly Production QMF reducing the CPU time from 3+ hours to several seconds, by putting in the missing join predicate and taking out the DISTINCT that disguised the result set (not so obviously wrong answers).

Would be nice to have an Explain extension that hightlighted the problem quickly, even for mere mortal developers. The cost estimate number for the join step, would be enormous I guess . So that would be a big hint.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Edited By:
Michael Hannan[Organization Members] @ May 10, 2018 - 01:30 PM (Europe/Berlin)
Michael Hannan[Organization Members] @ May 10, 2018 - 01:37 PM (Europe/Berlin)

Suresh Sane

Db2 z/OS V12 SQLLEVEL Precompiler option
(in response to Suresh Sane)
Team - does anyone have experience in using this option? Seems similar to the deprecated NEWFUN option I have used in the past.


According to documentation, specifying the level to be an earlier version will allow you to restrict the functionality to the earlier version. For example, when test is on V12 and prod is on V11, you can specify sqllevel to V11R1 to ensure that only V11 SQL functionality is used in test (presumably till prod also catches up to V12).


Any gotcha's or other thoughts?


Thx

Suresh

Chris Hoelscher

Db2 z/OS V12 SQLLEVEL Precompiler option
(in response to Suresh Sane)
It seems to me that SQLLEVEL (as NEWFUN before it) would restrict only compile-able (i.e. static) SQL - APPLCOMPAT would seem to have a more global reach ... V12R1M100 or even V11R1 would be a more complete solution (unless you WANTED to restrict static but not dynamic ...)

Chris Hoelscher
Technology Architect, Database Infrastructure Services
Technology Solution Services
Humana Inc.
123 East Main Street
Louisville, KY 40202
Humana.com
(502) 476-2538 or 407-7266

From: suresh sane [mailto:[login to unmask email]
Sent: Thursday, May 10, 2018 2:09 PM
To: [login to unmask email]
Subject: [DB2-L] - Db2 z/OS V12 SQLLEVEL Precompiler option


Team - does anyone have experience in using this option? Seems similar to the deprecated NEWFUN option I have used in the past.



According to documentation, specifying the level to be an earlier version will allow you to restrict the functionality to the earlier version. For example, when test is on V12 and prod is on V11, you can specify sqllevel to V11R1 to ensure that only V11 SQL functionality is used in test (presumably till prod also catches up to V12).



Any gotcha's or other thoughts?



Thx

Suresh

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

Venkat Srinivasan

RE: Set alert whenever nested loop join is used by SQL - DB2 Z/OS
(in response to Solt K)

Have you considered governor (RLF) assuming the problem originates from adhoc querying as an alternative? For bound static sql programs you can always interrogate plan table if you force explain yes on bind. you can set limits based on statement const info and alert the package for review.

going after all nested loop join is a overkill.