473,718 Members | 2,066 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Should a very complex SQL statement crash the instance?

SELECT columns
FROM t1
JOIN t2
Nov 12 '05 #1
10 2258
"Philip Sherman" <ps******@ameri tech.net> wrote in message
news:gu******** *******@newssvr 32.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******@ameri tech.net> wrote in message
news:gu******** *******@newssvr 32.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******@earthl ink.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******@earthl ink.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******@ameri tech.net> wrote in message
news:gu******** *******@newssvr 32.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

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

303
17678
by: mike420 | last post by:
In the context of LATEX, some Pythonista asked what the big successes of Lisp were. I think there were at least three *big* successes. a. orbitz.com web site uses Lisp for algorithms, etc. b. Yahoo store was originally written in Lisp. c. Emacs The issues with these will probably come up, so I might as well mention them myself (which will also make this a more balanced
17
3162
by: Chris Travers | last post by:
Hi all; I just made an interesting discovery. Not sure if it is a good thing or not, and using it certainly breakes first normal form.... Not even sure if it really works. However, as I am able to CRASH the backend, there is a bug here somewhere... test=# select version(); version
10
9540
by: xixi | last post by:
i have db2 udb v8.1 on windows 64 bit 2003 server, after db2 server start , i found this in the db2diag.log, is this error? 2004-05-05-15.28.30.780000 Instance:DB2 Node:000 PID:1692(db2syscs.exe) TID:2860 Appid:AC10040A.GD5F.00FC56D8BEC5 base sys utilities sqledint Probe:30 Crash Recovery is needed. 2004-05-05-15.28.31.890000 Instance:DB2 Node:000
5
3883
by: NG | last post by:
Hi, We are having DB2-V7.2 DB on AIX 5.2 machine. Recently we upgraded our system to fixpack 13 and activated activate AIX asynchronous IO function. Our DB is going to crash recovery with this error 0xFFFFF707. I have attached the related details. Any suggestions is much appreciated.
5
2165
by: Martin Lacoste | last post by:
There's likely not a simple answer for this, I know, but, I thought I'd try anyways... Background.. I've been racking my brain with some queries that I thought were straightforward, but have been giving me errors, etc.. and am wondering if I shouldn't be looking more towards using VBA. I have used it in the past to do more complex stuff, but I would consider myself just a little above a newbie in that department, since I seem to have...
175
8856
by: Ken Brady | last post by:
I'm on a team building some class libraries to be used by many other projects. Some members of our team insist that "All public methods should be virtual" just in case "anything needs to be changed". This is very much against my instincts. Can anyone offer some solid design guidelines for me? Thanks in advance....
2
3646
by: Racerx | last post by:
Hi All : I use db2 8.1 fixpack 3 on AIX. I recieved the following message in the diaglog ====================================================== ADM7513W Database manager has started. 2007-01-13-18.55.08.262174 Instance:db2inst1 Node:000 PID:467078(db2agent (mumar) 0) TID:1 Appid:GA010302.O03F.01101B9A3444 base sys utilities sqledint Probe:30
3
15914
by: Eric Davidson | last post by:
DB2 9.5 I keep geting the message. SQL0101N The statement is too long or too complex. SQLSTATE=54001 When one of my sql statements takes over 60 seconds to compile the sql statement. Is there any parameter that controls how long DB2 allows a statement to compile for.
10
1723
by: timor.super | last post by:
Hi all, Imagine I've an array of int : int anArray = new int; I want to extract all the integer that are superior to 500 I can do :
0
8724
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9356
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9207
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
9121
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9053
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
5971
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4481
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
3182
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
2122
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.