My DB2 7.2 enterprise edition databases crashes when i run a big query like
select something1,something2 from table1,table2 where table1.id in (123,2334,4455,6677.......
.......around 550 such values.
it works fine till 400 plus values.

i have tried increasing pool size, heap,application heap, log size etc.. but it still gives me an Dr watson error. i have a 1 GB RAM on an NT server out of which i have given 80% physical memory to Db2.

what shd i do
this db2 version has no fixpacks applied should i apply one?? or has it to do some configuration setting...

help

I would suggest that you reduce the memory allocation for bufferpool, so that your overall memory allocation for DB2 reduces, say to about 60% ...

The query may run slow, no doubts, but this will allow for other operations, like sort which are allocated on the fly to have memory available ...

Do you have one of the latest fixpacks of DB2 ?

What does db2diag.log say ? You may want to increase the diaglevel to 4 and capture more errors ...

Why not create a table to hold the 550 values and then say
table1.id in (select col1 from idtable)

If these values are dynamically generated, then CTE may be an option
with idvalues(col1) as
(
select (value1),(value2)......(valuen)
)
select something1,something2 from table1,table2 where table1.id in (select col1 from idvalues)

HTH

Sathyaram

thanks for the reply

initially i had low memory configuration and because of this problem i had to increase it. had 50% earlier on.
i do not have any fixpacks but i guess i should download one.
The log file does not say anything. i will try to capture more errors.

your query change idea(dynamic) is a good one but code changes are my last resort as this is a live running application and will ask for too many changes. as it is a user based roles based big application and today i have come accross in one table tomorrow may be more ..

how many BP R U using , default one or U create multiple BL.


& size of BP , BD & DBM cfg.

in DB2 UDB , if you give more resource , that will create lots of overhead.

need to give appropriate resource


let me know above details , I may get you right size for your needs


Lekharaju Ennam