sql0954

[mike_dba]

I am running v9 DPF on 64-bit SuSe Linux. I submit a particular
dynamic SQL statement and am getting a sql0954c error.

Query #1 has a particular predicate, Query #2 has a slightly different
predicate. The execution plans and costs are exactly the same. Query
#1 runs successfully. Query #2 fails at execution time. Is this
because Query #2 requires additional sub-agents at run time and cannot
get the additonal memory?

I cannot determine any difference from the query plans but do know
that cutting down the time period, and thus limitting the data volume,
resolves the problem.

I would like to better understand this before blindly doubling
applheapsz. Can someone shed some light on this for me? Thanks.

[Serge Rielau]

If I change the MCT_MCT_ID from 1234 to 4567, It will fail at run

time. Also, If I shorten the date predicate to fewer days, then this
failing 4567 will then work. So I think that it is something at run
time, such as subagents being allocated and its hit or miss as to
whether that can be allocated. Any comments are appreciated.

I think your problem is the SUM() OVER()
In older versions/fixpacks DB2 would buffer the rows exclusively in teh
application heap.
Nowadays DB2 uses the sort heap (I think) and can spill into SYSTEM TEMP
(just like sort and hash-joins)

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[mike_dba]

On Aug 4, 10:44*pm, Serge Rielau <[e-mail]:

If I change the MCT_MCT_ID from 1234 to 4567, It will fail at run
time. *Also, If I shorten the date predicate to fewer days, then this
failing 4567 will then work. *So I think that it is something at run
time, such as subagents being allocated and its hit or miss as to
whether that can be allocated. Any comments are appreciated.

I think your problem is the SUM() OVER()
In older versions/fixpacks DB2 would buffer the rows exclusively in teh
application heap.
Nowadays DB2 uses the sort heap (I think) and can spill into SYSTEM TEMP
(just like sort and hash-joins)

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Thanks. I am running V9.1 FP4 so I imagine that it must be a recent
change. Thanks for pointing this out.