Maximum number of parameters in IN-clause?

[Terje Krång]

Hi,

I am using DB2 UDB 7.1, and are having some problems with IN-
clauses. It works just fine up until 75 parameters in an IN-clause,
if I use more parameters than that I get the following message:

Error: SQL0973N Not enough storage is available in the "APP_CTL_HEAP"
heap to process the statement. SQLSTATE=57011
(State:57011, Native Code: FFFFFC33)

I have tried to increase app_ctl_heap_sz from 128 (default value) to
2048 4Kb pages, but this doesn't seem to solve the problem. Does anybody
have a solution for this?

Best Regards,
Terje Krång, te...@dreamscape.no

[Michael Bhola]

Not meaning to sound condascending, but you did recycle the database
afterwards, right ?

"Terje Krång" <te...@dreamscape.no> wrote in message
news:6ny57.29593$H37.5...@news.easynews.com...

[pm3...@attglobal.net]

With IN() you may get a lot of parallel sub-agents working on the query.

SQLSTATE 57011: Virtual storage or database resource is not available
Maybe 0xFC33 is more specific.

I would bet that it's the first part that applies here.
I'd monitor the server's memory usage and see how's the dbheap doing.

It may also have to do with MAXGENTS and similar parms?

You can also try things such as reducing the current query degree
for this particular query?

You may try other plans using 'in (select)'
SELECT *
FROM ZZ
WHERE C1 IN
(select *
from table(values ('2'), ('1') ) as t0(col1))

PM

[pm3...@attglobal.net]

I have a test query with +- 100 items in an in-list.

PM