sql0954

Question posted by: mike_dba (Guest) on August 1st, 2008 05:45 PM

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

Guest

n/a Posts

August 5th, 2008
02:45 AM
#2

Re: sql0954

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

Quote:

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

Guest

n/a Posts

August 5th, 2008
01:15 PM
#3

Re: sql0954

On Aug 4, 10:44*pm, Serge Rielau <srie...@ca.ibm.comwrote:

Quote:

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.

Not the answer you were looking for? Post your question . . .
190,475 Experts ready to help you find a solution.
Sign up for a free account, or Login (if you're already a member).

Latest Articles: Read & Comment

Didn't find the answer you were looking for?
Post Your Question

Top Community Contributors