Should a very complex SQL statement crash the instance?

[Philip Sherman]

SELECT columns
FROM t1
JOIN t2

[Mark A]

"Philip Sherman" <ps******@ameritech.net> wrote in message
news:gu***************@newssvr32.news.prodigy.com. ..

SELECT columns
FROM t1
JOIN t2
.
.
.
WHERE predicates

The from/join contains 5 tables, one of which is created from a select
statement.

The columns area contains 35 selects, some of them containing a join,
referencing a total of 40 tables.

UDB 8.1 Windows and Linux both hard crashed (complete instance shutdown)
trying to process this statement. (Yes, I have dumps from Linux and may
be able to get to the Windows dumps next week.)

Please don't ask me where this came from - I didn't write it and just
have to make it work within the next two days.

Check the SQL limits in the SQL Reference for the most tables or joins in
one statement. If you are not on fixpak 5, I would install it now.

I don't care where it came from, but the person who did it should be tarred
and feathered.

[Mark A]

"Philip Sherman" <ps******@ameritech.net> wrote in message
news:gu***************@newssvr32.news.prodigy.com. ..

SELECT columns
FROM t1
JOIN t2
.
.
.
WHERE predicates

The from/join contains 5 tables, one of which is created from a select
statement.

The columns area contains 35 selects, some of them containing a join,
referencing a total of 40 tables.

UDB 8.1 Windows and Linux both hard crashed (complete instance shutdown)
trying to process this statement. (Yes, I have dumps from Linux and may
be able to get to the Windows dumps next week.)

Please don't ask me where this came from - I didn't write it and just
have to make it work within the next two days.

Check the SQL limits in the SQL Reference for the most tables or joins in
one statement. If you are not on fixpak 5, I would install it now.

I don't care where it came from, but the person who did it should be tarred
and feathered.

[Philip Sherman]

We are on FP5.

I went through the limits section of SQL REF V1 3 times and couldn't
find that particular limit. Maybe age is getting to my eyes.

Phil Sherman
Mark A wrote:

Check the SQL limits in the SQL Reference for the most tables or joins in
one statement. If you are not on fixpak 5, I would install it now.

I don't care where it came from, but the person who did it should be tarred
and feathered.

[Philip Sherman]

We are on FP5.

I went through the limits section of SQL REF V1 3 times and couldn't
find that particular limit. Maybe age is getting to my eyes.

Phil Sherman
Mark A wrote:

Check the SQL limits in the SQL Reference for the most tables or joins in
one statement. If you are not on fixpak 5, I would install it now.

I don't care where it came from, but the person who did it should be tarred
and feathered.

[Jonathan Leffler]

Philip Sherman wrote:

SELECT columns
FROM t1
JOIN t2
.
.
.
WHERE predicates

The from/join contains 5 tables, one of which is created from a select
statement.

The columns area contains 35 selects, some of them containing a join,
referencing a total of 40 tables.

UDB 8.1 Windows and Linux both hard crashed (complete instance shutdown)
trying to process this statement. (Yes, I have dumps from Linux and may
be able to get to the Windows dumps next week.)

Please don't ask me where this came from - I didn't write it and just
have to make it work within the next two days.

If this were IBM Informix Dynamic Server, I'd answer "No; no statement
is allowed to crash the server". The server may decline to answer,
generate errors where you can see an answer could be produced, or any
of a number of other things (not excluding producing the right
answer), but the server may not crash because of what you asked it to
do. If there was a question of exceeding a server limit, the server
is allowed to diagnose this, but it still isn't allowed to crash.

I imagine the DB2 development team feels somewhat similarly about DB2.

--
Jonathan Leffler #include <disclaimer.h>
Email: jl******@earthlink.net, jl******@us.ibm.com
Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/

[Jonathan Leffler]

Philip Sherman wrote:

SELECT columns
FROM t1
JOIN t2
.
.
.
WHERE predicates

The from/join contains 5 tables, one of which is created from a select
statement.

The columns area contains 35 selects, some of them containing a join,
referencing a total of 40 tables.

UDB 8.1 Windows and Linux both hard crashed (complete instance shutdown)
trying to process this statement. (Yes, I have dumps from Linux and may
be able to get to the Windows dumps next week.)

Please don't ask me where this came from - I didn't write it and just
have to make it work within the next two days.

If this were IBM Informix Dynamic Server, I'd answer "No; no statement
is allowed to crash the server". The server may decline to answer,
generate errors where you can see an answer could be produced, or any
of a number of other things (not excluding producing the right
answer), but the server may not crash because of what you asked it to
do. If there was a question of exceeding a server limit, the server
is allowed to diagnose this, but it still isn't allowed to crash.

I imagine the DB2 development team feels somewhat similarly about DB2.

--
Jonathan Leffler #include <disclaimer.h>
Email: jl******@earthlink.net, jl******@us.ibm.com
Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/

[Serge Rielau]

> I imagine the DB2 development team feels somewhat similarly about DB2.
Absolutely and unconditionally!!

Having that said Wndows (I don't know about Linux) reacts in a very
alergic way to stack overflow and there is precious little DB2 can do to
soften the blow once the stack is exceeded.
We have seen (and fixed) Windows stack overflows in stress testing for
VERY long chains of predicates such as:
prd1 AND prd2 AND prd2 AND .. AND prdn (we are talking hundreds in my
experience)

First assure you are on FP5. If the problem persists please contact
support so we can fix it.
There also is a db2 utility you can use to increae the stack size.
- which's name I keep forgetting :-(

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Serge Rielau]

> I imagine the DB2 development team feels somewhat similarly about DB2.
Absolutely and unconditionally!!

Having that said Wndows (I don't know about Linux) reacts in a very
alergic way to stack overflow and there is precious little DB2 can do to
soften the blow once the stack is exceeded.
We have seen (and fixed) Windows stack overflows in stress testing for
VERY long chains of predicates such as:
prd1 AND prd2 AND prd2 AND .. AND prdn (we are talking hundreds in my
experience)

First assure you are on FP5. If the problem persists please contact
support so we can fix it.
There also is a db2 utility you can use to increae the stack size.
- which's name I keep forgetting :-(

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Mark Yudkin]

I see nothing untoward about a 40 table query. And obviously DB2 should not
be crashing.

As Serge says, check you're on 8.1.5, then open a PMR against the bug.

"Mark A" <[e-mail]> wrote in message
news:4G******************@news.uswest.net...

"Philip Sherman" <ps******@ameritech.net> wrote in message
news:gu***************@newssvr32.news.prodigy.com. ..

SELECT columns
FROM t1
JOIN t2
.
.
.
WHERE predicates

The from/join contains 5 tables, one of which is created from a select
statement.

The columns area contains 35 selects, some of them containing a join,
referencing a total of 40 tables.

UDB 8.1 Windows and Linux both hard crashed (complete instance shutdown)
trying to process this statement. (Yes, I have dumps from Linux and may
be able to get to the Windows dumps next week.)

Please don't ask me where this came from - I didn't write it and just
have to make it work within the next two days.
Check the SQL limits in the SQL Reference for the most tables or joins in
one statement. If you are not on fixpak 5, I would install it now.

I don't care where it came from, but the person who did it should be

tarred and feathered.

[Mark Yudkin]

I see nothing untoward about a 40 table query. And obviously DB2 should not
be crashing.

As Serge says, check you're on 8.1.5, then open a PMR against the bug.

"Mark A" <[e-mail]> wrote in message
news:4G******************@news.uswest.net...

"Philip Sherman" <ps******@ameritech.net> wrote in message
news:gu***************@newssvr32.news.prodigy.com. ..

SELECT columns
FROM t1
JOIN t2
.
.
.
WHERE predicates

The from/join contains 5 tables, one of which is created from a select
statement.

The columns area contains 35 selects, some of them containing a join,
referencing a total of 40 tables.

UDB 8.1 Windows and Linux both hard crashed (complete instance shutdown)
trying to process this statement. (Yes, I have dumps from Linux and may
be able to get to the Windows dumps next week.)

Please don't ask me where this came from - I didn't write it and just
have to make it work within the next two days.
Check the SQL limits in the SQL Reference for the most tables or joins in
one statement. If you are not on fixpak 5, I would install it now.

I don't care where it came from, but the person who did it should be

tarred and feathered.