Number of tables in Join

Sibimon Philip

Number of tables in Join
We have upgraded our DB2 to Version 6.1 in OS/390 2.9 in TEST. We had a
query with 19 table join and some of them are view and it has multiple
tables. So there was totally 23 base tables in the join. Even though it was
slow it was working in DB2 version 5.1. But after the upgrade it is giving
-129 SQLcode (too many tables in the query). Based on the V6 features, I
thought the 15 tables limit is increased to 225. Is that right? Does anybody
had this problem after the upgrade.


Thanks...sibi



Jeff Frazier

Re: Number of tables in Join
(in response to Sibimon Philip)
The enforcement of 15 table joins was reinstated in v6. see apar pq31326




"Philip, Sibimon" <[login to unmask email]>@RYCI.COM> on 12/15/2000 03:52:36
PM

Please respond to DB2 Data Base Discussion List <[login to unmask email]>

Sent by: DB2 Data Base Discussion List <[login to unmask email]>


To: [login to unmask email]
cc:

Subject: Number of tables in Join


We have upgraded our DB2 to Version 6.1 in OS/390 2.9 in TEST. We had a
query with 19 table join and some of them are view and it has multiple
tables. So there was totally 23 base tables in the join. Even though it
was
slow it was working in DB2 version 5.1. But after the upgrade it is giving
-129 SQLcode (too many tables in the query). Based on the V6 features, I
thought the 15 tables limit is increased to 225. Is that right? Does
anybody
had this problem after the upgrade.


Thanks...sibi








Sibimon Philip

Re: Number of tables in Join
(in response to Jeff Frazier)
Then what is meant by

"DB2 increases the maximum number of tables allowed in a view, and in
SELECT, UPDATE, INSERT, and DELETE statements, from 15 to 225. The maximum
number of base tables that are allowed in a view, tables in a FROM clause,
and subqueries in a statement is 15."

in V6 features. I am confused about this.

thanks...sibi



-----Original Message-----
From: Jeff Frazier [mailto:[login to unmask email]
Sent: Friday, December 15, 2000 03:01 PM
To: [login to unmask email]
Subject: Re: Number of tables in Join


The enforcement of 15 table joins was reinstated in v6. see apar pq31326




"Philip, Sibimon" <[login to unmask email]>@RYCI.COM> on 12/15/2000 03:52:36
PM

Please respond to DB2 Data Base Discussion List <[login to unmask email]>

Sent by: DB2 Data Base Discussion List <[login to unmask email]>


To: [login to unmask email]
cc:

Subject: Number of tables in Join


We have upgraded our DB2 to Version 6.1 in OS/390 2.9 in TEST. We had a
query with 19 table join and some of them are view and it has multiple
tables. So there was totally 23 base tables in the join. Even though it
was
slow it was working in DB2 version 5.1. But after the upgrade it is giving
-129 SQLcode (too many tables in the query). Based on the V6 features, I
thought the 15 tables limit is increased to 225. Is that right? Does
anybody
had this problem after the upgrade.


Thanks...sibi













Harold Lee

Re: Number of tables in Join
(in response to Sibimon Philip)
15 time 15 is 225

> -----Original Message-----
> From: Philip, Sibimon [SMTP:[login to unmask email]
> Sent: Friday, December 15, 2000 2:09 PM
> To: [login to unmask email]
> Subject: Re: Number of tables in Join
>
> Then what is meant by
>
> "DB2 increases the maximum number of tables allowed in a view, and in
> SELECT, UPDATE, INSERT, and DELETE statements, from 15 to 225. The maximum
> number of base tables that are allowed in a view, tables in a FROM clause,
> and subqueries in a statement is 15."
>
> in V6 features. I am confused about this.
>
> thanks...sibi
>
>
>
> -----Original Message-----
> From: Jeff Frazier [mailto:[login to unmask email]
> Sent: Friday, December 15, 2000 03:01 PM
> To: [login to unmask email]
> Subject: Re: Number of tables in Join
>
>
> The enforcement of 15 table joins was reinstated in v6. see apar pq31326
>
>
>
>
> "Philip, Sibimon" <[login to unmask email]>@RYCI.COM> on 12/15/2000 03:52:36
> PM
>
> Please respond to DB2 Data Base Discussion List <[login to unmask email]>
>
> Sent by: DB2 Data Base Discussion List <[login to unmask email]>
>
>
> To: [login to unmask email]
> cc:
>
> Subject: Number of tables in Join
>
>
> We have upgraded our DB2 to Version 6.1 in OS/390 2.9 in TEST. We had a
> query with 19 table join and some of them are view and it has multiple
> tables. So there was totally 23 base tables in the join. Even though it
> was
> slow it was working in DB2 version 5.1. But after the upgrade it is giving
> -129 SQLcode (too many tables in the query). Based on the V6 features, I
> thought the 15 tables limit is increased to 225. Is that right? Does
> anybody
> had this problem after the upgrade.
>
>
> Thanks...sibi
>
>
>
>
>
>
>
>
> the
>
>
>
>
>
>
> .



Sibimon Philip

Re: Number of tables in Join
(in response to Harold Lee)
Thanks to all those who responded. Now I understand the meaning of 225
tables. This list is great.

Regards..sibi

-----Original Message-----
From: Duane Lee - ATCX [mailto:[login to unmask email]
Sent: Friday, December 15, 2000 03:16 PM
To: [login to unmask email]
Subject: Re: Number of tables in Join


15 time 15 is 225

> -----Original Message-----
> From: Philip, Sibimon [SMTP:[login to unmask email]
> Sent: Friday, December 15, 2000 2:09 PM
> To: [login to unmask email]
> Subject: Re: Number of tables in Join
>
> Then what is meant by
>
> "DB2 increases the maximum number of tables allowed in a view, and in
> SELECT, UPDATE, INSERT, and DELETE statements, from 15 to 225. The maximum
> number of base tables that are allowed in a view, tables in a FROM clause,
> and subqueries in a statement is 15."
>
> in V6 features. I am confused about this.
>
> thanks...sibi
>
>
>
> -----Original Message-----
> From: Jeff Frazier [mailto:[login to unmask email]
> Sent: Friday, December 15, 2000 03:01 PM
> To: [login to unmask email]
> Subject: Re: Number of tables in Join
>
>
> The enforcement of 15 table joins was reinstated in v6. see apar pq31326
>
>
>
>
> "Philip, Sibimon" <[login to unmask email]>@RYCI.COM> on 12/15/2000 03:52:36
> PM
>
> Please respond to DB2 Data Base Discussion List <[login to unmask email]>
>
> Sent by: DB2 Data Base Discussion List <[login to unmask email]>
>
>
> To: [login to unmask email]
> cc:
>
> Subject: Number of tables in Join
>
>
> We have upgraded our DB2 to Version 6.1 in OS/390 2.9 in TEST. We had a
> query with 19 table join and some of them are view and it has multiple
> tables. So there was totally 23 base tables in the join. Even though it
> was
> slow it was working in DB2 version 5.1. But after the upgrade it is giving
> -129 SQLcode (too many tables in the query). Based on the V6 features, I
> thought the 15 tables limit is increased to 225. Is that right? Does
> anybody
> had this problem after the upgrade.
>
>
> Thanks...sibi
>
>
>
>
>
>
>
>
> the
>
>
>
>
>
>
> .








Scott Trometer

Re: Number of tables in Join
(in response to Sibimon Philip)
Well...now I'm not followin' ya...

Are we saying that joining 15 views, each containing 15 base tables, is how
the new max of 225 is achieved ?




-----Original Message-----
From: Philip, Sibimon [mailto:[login to unmask email]
Sent: Monday, December 18, 2000 9:29 AM
To: [login to unmask email]
Subject: Re: Number of tables in Join


Thanks to all those who responded. Now I understand the meaning of 225
tables. This list is great.

Regards..sibi

-----Original Message-----
From: Duane Lee - ATCX [mailto:[login to unmask email]
Sent: Friday, December 15, 2000 03:16 PM
To: [login to unmask email]
Subject: Re: Number of tables in Join


15 time 15 is 225

> -----Original Message-----
> From: Philip, Sibimon [SMTP:[login to unmask email]
> Sent: Friday, December 15, 2000 2:09 PM
> To: [login to unmask email]
> Subject: Re: Number of tables in Join
>
> Then what is meant by
>
> "DB2 increases the maximum number of tables allowed in a view, and in
> SELECT, UPDATE, INSERT, and DELETE statements, from 15 to 225.

The maximum number of base tables that are allowed in a view, tables in a
FROM clause,
> and subqueries in a statement is 15."
>
> in V6 features. I am confused about this.
>
> thanks...sibi
>
>
>
> -----Original Message-----
> From: Jeff Frazier [mailto:[login to unmask email]
> Sent: Friday, December 15, 2000 03:01 PM
> To: [login to unmask email]
> Subject: Re: Number of tables in Join
>
>
> The enforcement of 15 table joins was reinstated in v6. see apar pq31326
>
>
>
>
> "Philip, Sibimon" <[login to unmask email]>@RYCI.COM> on 12/15/2000 03:52:36
> PM
>
> Please respond to DB2 Data Base Discussion List <[login to unmask email]>
>
> Sent by: DB2 Data Base Discussion List <[login to unmask email]>
>
>
> To: [login to unmask email]
> cc:
>
> Subject: Number of tables in Join
>
>
> We have upgraded our DB2 to Version 6.1 in OS/390 2.9 in TEST. We had a
> query with 19 table join and some of them are view and it has multiple
> tables. So there was totally 23 base tables in the join. Even though it
> was
> slow it was working in DB2 version 5.1. But after the upgrade it is giving
> -129 SQLcode (too many tables in the query). Based on the V6 features, I
> thought the 15 tables limit is increased to 225. Is that right? Does
> anybody
> had this problem after the upgrade.
>
>
> Thanks...sibi

Sibimon Philip

Re: Number of tables in Join
(in response to Scott Trometer)
Yes. Basically in the From clause you cannot have more than 15
tables/views. This is my understanding until somebody tells otherwise.



thanks..sibi

-----Original Message-----
From: Scott Trometer [mailto:[login to unmask email]
Sent: Monday, December 18, 2000 08:50 AM
To: [login to unmask email]
Subject: Re: Number of tables in Join



Well...now I'm not followin' ya...

Are we saying that joining 15 views, each containing 15 base tables, is how
the new max of 225 is achieved ?




-----Original Message-----
From: Philip, Sibimon [ mailto:[login to unmask email]
<mailto:[login to unmask email]> ]
Sent: Monday, December 18, 2000 9:29 AM
To: [login to unmask email]
Subject: Re: Number of tables in Join


Thanks to all those who responded. Now I understand the meaning of 225
tables. This list is great.

Regards..sibi

-----Original Message-----
From: Duane Lee - ATCX [ mailto:[login to unmask email]
<mailto:[login to unmask email]> ]
Sent: Friday, December 15, 2000 03:16 PM
To: [login to unmask email]
Subject: Re: Number of tables in Join


15 time 15 is 225

> -----Original Message-----
> From: Philip, Sibimon [SMTP:[login to unmask email]
> Sent: Friday, December 15, 2000 2:09 PM
> To: [login to unmask email]
> Subject: Re: Number of tables in Join
>
> Then what is meant by
>
> "DB2 increases the maximum number of tables allowed in a view, and in
> SELECT, UPDATE, INSERT, and DELETE statements, from 15 to 225.

The maximum number of base tables that are allowed in a view, tables in a
FROM clause,
> and subqueries in a statement is 15."
>
> in V6 features. I am confused about this.
>
> thanks...sibi
>
>
>
> -----Original Message-----
> From: Jeff Frazier [ mailto:[login to unmask email]
<mailto:[login to unmask email]> ]
> Sent: Friday, December 15, 2000 03:01 PM
> To: [login to unmask email]
> Subject: Re: Number of tables in Join
>
>
> The enforcement of 15 table joins was reinstated in v6. see apar pq31326
>
>
>
>
> "Philip, Sibimon" <[login to unmask email]>@RYCI.COM> on 12/15/2000 03:52:36
> PM
>
> Please respond to DB2 Data Base Discussion List <[login to unmask email]>
>
> Sent by: DB2 Data Base Discussion List <[login to unmask email]>
>
>
> To: [login to unmask email]
> cc:
>
> Subject: Number of tables in Join
>
>
> We have upgraded our DB2 to Version 6.1 in OS/390 2.9 in TEST. We had a
> query with 19 table join and some of them are view and it has multiple
> tables. So there was totally 23 base tables in the join. Even though it
> was
> slow it was working in DB2 version 5.1. But after the upgrade it is giving

> -129 SQLcode (too many tables in the query). Based on the V6 features, I
> thought the 15 tables limit is increased to 225. Is that right? Does
> anybody
> had this problem after the upgrade.
>
>
> Thanks...sibi

Terry Purcell

Re: Number of tables in Join
(in response to Sibimon Philip)
RE: Number of tables in JoinSibi,

You are correct. The limits are:

- 15 table max in a FROM clause
- 15 table max in a view definition
- 14 subquery max in statement.

The combined total of these cannot exceed 225, however there were some
issues with this implementation in V6, of which APAR PQ31326 (mentioned by
Jeff Frazier) was supposed to solve. I have not tested the limits with the
APAR applied.

I am assuming also that the inclusion of Nested Table Expressions in the
FROM clause can overcome the 15 table limit, since these are evaluated
similar to non-correlated subqueries (and correlated table expressions like
correlated subqueries); although once again I have not tested this.

Regards
Terry Purcell
Yevich Lawson & Associates
[login to unmask email]
WWW.YLASSOC.COM
WWW.DB2-SYMPOSIUM.COM
-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On Behalf Of
Philip, Sibimon
Sent: Monday, December 18, 2000 9:11 AM
To: [login to unmask email]
Subject: Re: Number of tables in Join


Yes. Basically in the From clause you cannot have more than 15
tables/views. This is my understanding until somebody tells otherwise.



thanks..sibi