We are using db2 ese v8.1.3 on Win2k platform.

We have a data warehouse query, which is quite big (actually, sums up "Net Sale of Units" from Forecast Actual table based on certain criteria) containing several hundreds of "OR" condition in the "WHERE" clause. Now we ran this big query against our database (with default db/dbm parameters) and found that the query actually crashes the server!!

My question is:
- Why does db2 server crash if the query is big? An exception message would have been fine.
- Is there any limit on the number of expressions that can be included in the WHERE clause?
- How do we handle if the query grow even further?

Any thoughts / comments?

Hi,

there is no sql statement that may result in the server crashing. If you can repeat the problem, I would file a pmr with IBM support.

Johann

As Johann says, no query should crash the server ...

For limits on SQL, refer to the 'SQL Limits' topic in SQL Reference for your version

HTH

Sathyaram

... Yes, I agree with both of you. But, surprisingly database crashes if we run that query. Anyway, can this be due to fixpak?

Hi dr_suresh20,

well, it is always a good idea to stay current with regard to fixes. You should definitely ask for support from IBM. Also, you may want to catch the output from db2diag.log when the crash occurs and post it here.

Johann

I would suggest storing the predicate that contains several hundreds "OR" conditions in a separate DB2 table and then doing a sub-select. You could load the data in a persistent (regular) table, or create a temporary table each time the query was executed. It would obviously be more efficient to use a persistent table.

Hi Marcus,

Your idea seems really great!!! However do you think that is feasible in production environments??

No one can possibly create persistent tables for all such queries, especially in scenarios where one expects dynamic queries.

Regards

Nitin.

Hi Johann:

Please have a look at the db2diag.log file:

***
2004-02-13-15.00.21.538000 InstanceB2 Node:000
PID:1300(db2syscs.exe) TID:1448 Appid:none
routine_infrastructure sqlerKillAllFmps Probe:5

Bringing down all db2fmp processes as part of db2stop
0x014AFB5C : 0x00000000 ....

2004-02-13-15.00.22.320000 InstanceB2 Node:000
PID:1300(db2syscs.exe) TID:516 Appid:none
base sys utilities DB2StopMain Probe:911

ADM7514W Database manager has stopped.

2004-02-13-15.06.13.859000 InstanceB2 Node:000
PID:796(db2dasstm.exe) TID:996 Appid:none
oper system services sqloSSemClose Probe:20

Unexpected system error 0x6 has occurred.
This has been mapped to ZRC 0x83000006.

PID:796 TID:996 Node:000 Title: SYSTEM ERROR DESCRIPTION
The handle is invalid.


2004-02-13-15.06.14.031000 InstanceB2 Node:000
PID:796(db2dasstm.exe) TID:996 Appid:none
oper system services sqloSSemClose Probe:20

Unexpected system error 0x6 has occurred.
This has been mapped to ZRC 0x83000006.

PID:796 TID:996 Node:000 Title: SYSTEM ERROR DESCRIPTION
The handle is invalid.


2004-02-13-15.06.14.156000 InstanceB2 Node:000
PID:796(db2dasstm.exe) TID:996 Appid:none
oper system services sqloSSemClose Probe:20

Unexpected system error 0x6 has occurred.
This has been mapped to ZRC 0x83000006.

PID:796 TID:996 Node:000 Title: SYSTEM ERROR DESCRIPTION
The handle is invalid.


2004-02-13-15.06.14.281000 InstanceB2 Node:000
PID:796(db2dasstm.exe) TID:996 Appid:none
oper system services sqloSSemClose Probe:20

Unexpected system error 0x6 has occurred.
This has been mapped to ZRC 0x83000006.

PID:796 TID:996 Node:000 Title: SYSTEM ERROR DESCRIPTION
The handle is invalid.


2004-02-13-15.06.16.609000 InstanceB2 Node:000
PID:1352(iwh2log.exe) TID:1368 Appid:none
oper system services sqloSSemClose Probe:20

Unexpected system error 0x6 has occurred.
This has been mapped to ZRC 0x83000006.

PID:1352 TID:1368 Node:000 Title: SYSTEM ERROR DESCRIPTION
The handle is invalid.


2004-02-13-15.06.16.734000 InstanceB2 Node:000
PID:1352(iwh2log.exe) TID:1368 Appid:none
oper system services sqloSSemClose Probe:20

Unexpected system error 0x6 has occurred.
This has been mapped to ZRC 0x83000006.

PID:1352 TID:1368 Node:000 Title: SYSTEM ERROR DESCRIPTION
The handle is invalid.


2004-02-13-15.06.18.812000 InstanceB2 Node:000
PID:1300(db2syscs.exe) TID:1332 Appid:none
base sys utilities DB2StartMain Probe:911

ADM7513W Database manager has started.

2004-02-13-15.22.51.703000 InstanceB2 Node:000
PID:2196(db2bp.exe) TID:2180 Appid:none
database utilities sqlubckp Probe:26

DiagData
0x00E610E4 : 0xFFFFFBF5 õûÿÿ

***

Hi Marcus:

We cannot store these conditions in tables since they are dynamic in nature and they are controlled by user / application actions.

I am still unable to figure out "why" db2 database crashes - however, suitable approach would have been to throw an exception.

I have already raised a PMR with IBM. Let's see how it goes.

Thanks.

Hi dr_suresh20,

looks like you have hit on something really nasty. Did you check if filesystems fill up, like that one for the temporary tablespace? You can find warnings like that in db2diag.log. Just search for full or temp.

I wonder if the db2diag.log trace is complete? Because normally I would expect the offending statement to show up someplace in a hex dump.

Anyway, please keep us informed about what IBM support finds out.

Johann

I did not realize that such SQL statements that you were talking about were ad-hoc queries. However, if you talk to the end user, you may find that the combination of predicate values for the OR list has a specific meaning and that it may be able to be stored in a table. You will probably have to allow for multiple combinations of values in the OR list to be selected, but if you investigate further, I suspect you will find that the queries with the long OR list are repeated.

I have to admint what dr suresh tells here. We have a UDB V7.2FP9 that where the DB instance once ONE user runs a quite big M$access macro via ODBC against the database on a Solaris 4 CPU box (16Gig Mem). The Macro does quite big select insert into statements, but a crashing DB?