469,138 Members | 1,295 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,138 developers. It's quick & easy.

Should a very complex SQL statement crash the instance?

SELECT columns
FROM t1
JOIN t2
Nov 12 '05 #1
10 2040
"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.
Nov 12 '05 #2
"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.
Nov 12 '05 #3
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.


Nov 12 '05 #4
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.


Nov 12 '05 #5
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/

Nov 12 '05 #6
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/

Nov 12 '05 #7
> 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
Nov 12 '05 #8
> 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
Nov 12 '05 #9
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" <ma@switchboard.net> 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.

Nov 12 '05 #10
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" <ma@switchboard.net> 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.

Nov 12 '05 #11

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

303 posts views Thread by mike420 | last post: by
10 posts views Thread by xixi | last post: by
5 posts views Thread by Martin Lacoste | last post: by
2 posts views Thread by Racerx | last post: by
10 posts views Thread by timor.super | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
1 post views Thread by Mortomer39 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.