MaxDB 7.6.03 - error: KB Stack type illegal - please help

[Former Member]

Hi MaxDB experts,

We use MaxDB 7.6.03. We're getting the following exception with one of our reporting queries:

Message: com.sap.dbtech.jdbc.exceptions.DatabaseException: [-9211]: System

error: KB Stack type illegal

Here's the behavior : The exception happens under specific circumstances i.e. when someone runs the report query for a single machine. The exception can be consistently reproduced i.e. if you re-run the same report for same machine you get it.

The strange thing is now it's almost 24 hours since getting the exception and we can no longer reproduce the problem.

The report query is a nested query and essentially calculates averages based on half dozen columns of a table. There is a time range in the where clause of the inner query and computer filter in the where clause of the outer query.

The stack trace looks as follows:

Caused by: com.sap.dbtech.jdbc.exceptions.DatabaseException: [-9211]:

System error: KB Stack type illegal

at com.sap.dbtech.jdbc.packet.ReplyPacket.createException (ReplyPacket.java:64)

at com.sap.dbtech.jdbc.ConnectionSapDB.throwSQLError (ConnectionSapDB.java:984)

at com.sap.dbtech.jdbc.ConnectionSapDB.execute (ConnectionSapDB.java:622)

at com.sap.dbtech.jdbc.ConnectionSapDB.execute (ConnectionSapDB.java:489)

at com.sap.dbtech.jdbc.callableStatementSapDB.execute (CallableStatementSapDB.java:442)

at com.sap.dbtech.jdbc.callableStatementSapDB.execute (CallableStatementSapDB.java:314)

at com.sap.dbtech.jdbc.trace.PreparedStatement.execute (PreparedStatement.java:378)

... 47 more

In our implementation phase, we ran several reports and have run into this exception once before, at the time we noticed that simply bringing the database offline and back online again the exception went away. In this particular scenario, we could not find anything wrong with the Query and we could run the Query fine from "SQL Studio" without getting the exception at the very same time when we were getting the exception running the same Query from within the product.

This exception has us very concerned please let us know your recommendation for us to keep this from happening in the field. Please let us know if you need any more information from us. Thanks for your time.

Sincerely,

Sameer Apte

Edited by: samapt0100 on Nov 9, 2009 4:22 PM

[Former Member]

Hallo,

and the traces we need are named

DEFAULT

and

SELECT

Elke

[Former Member]

Hi Melanie and Elke,

I could finally reproduce the problem with the following query:

"select measuredobjectid,

sum(cpu_time_pct*count)/sum(count) as CpuUsedL

from d_windowscpu_summary

where

(completed = true or date(sampletime) = date(timestamp))

and sampletime >= date('2009-11-11') and sampletime <= date('2009-11-11')

and hour_range = '0024'

and dayofweek(sampletime) in (1,2,3,4,5,6,7)

group by measuredobjectid"

I ran some combinations of the query to track down part of the query that was causing the "stack type illegal" error and it turns out that the query runs fine with the follwing change --

Replace "complated is true" with "completed = true" -- where completed is a column of type boolean.

I'm going to change all our queries with the above change and get replace the word "is" with "=". What do you guys think? Please let me know. Thanks for your continued support.

Sameer Apte

[Former Member]

Hi,

mhm, unfortunately this statement does not look as complex (with ORs, some kind of expressions) as expected.

Despite the fact that I do not know dayofweeks, which are NOT in (1,2.3,4,5,6,7), meaning: I do not see the reason

for this qualification, the statement seems to be ok

I cannot imagine why the error sometimes occur and sometimes the query works well.

Do you receive sometimes 'row not found', sometimes some result if it works well or do we have to think of the behaviour if the inner select, for example, will not return some row?

Elke

[Former Member]

Hi Elke,

Inner select returning no data - in my opinion should not be a problem because we also have the condition in the where clause -

"rows.measuredobjectid = mo.measuredobjectid"

where "rows" is the resultset returned by the inner select and if it's empty the outer select returns nothing.

I have not received the 'row not found' error with this query.

If you think of something else please let us know. Thanks for your time.

Sameer Apte

[Former Member]

Hi Elke,

When the problem reappears we would like to submit output of the vtrace. We are not familiar with preparing the vtrace. Could you please let us know how to do it and what to submit or re-direct us to a place we can read about it? Please let us know. Thanks for your time.

Sincerely,

Sameer Apte

[Melanie]

Hello,

check out

http://wiki.sdn.sap.com/wiki/display/MaxDB/MaxDBdatabasetrace

'vtrace', 'kernel trace' and 'MaxDB database trace' is all the same.

Regards,

Melanie

[Former Member]

Hallo,

if it is a complex query (for example with a mixture of ANDs and ORs, then it might be that the part of the command resultng in the error -9211 sometimes is touched and sometimes not.

If we assume

qualification1 OR qualification2 OR ... qualification_N(including the buggy qualification description)

then qualificationN will only be checked in case all other qualification return false before.

Therefore the data checked and the qualifications checked before may influence the ability to reproduce the problem.

Please tell us if the query is a join, if you have some expressions (+, -, *, /, or functions like substr, lfill or the like) which do NOT use a column, but can be calculated from literals and parameters.

Such problem we know and fixed in a newer version.

On the other hand:

We can check the internal description of your statement.

Therefore we would need to know the statement, the table(view)-definitions concerned and the output of the vtrace if you switch on vtrace for select. Are you familiar with preparing the vtrace / did you find a corresponding description?

Elke

[Former Member]

Hi Elke,

Thanks so much for your response. Here's the query that caused the error:

select manageddevicedns as ManagedDevice, round(avg(UserAverageL), 1) as UserAverage, round(avg

(SystemAverageL), 1) as SystemAverage, round(avg(IdleAverageL), 1) as IdleAverage

from

(

select measuredobjectid,

sum(cpu_user_pct*count)/sum(count) as UserAverageL,

sum(cpu_time_pct*count)/sum(count) - sum

(cpu_user_pct*count)/sum(count) as SystemAverageL,

100 - sum(cpu_time_pct*count)/sum(count) as IdleAverageL

from d_windowscpu_summary

where

(completed is true or date(sampletime) = date(timestamp))

and sampletime >= '2009-11-05' and sampletime <= '2009-11-05'

and hour_range = '0024'

and dayofweek(sampletime) in (1,2,3,4,5,6,7)

group by measuredobjectid

)

rows, MeasuredObjects mo,Collections co, ManagedDevices md

where

rows.measuredobjectid = mo.measuredobjectid

and mo.collectionid = co.collectionid and co.manageddeviceid = md.manageddeviceid

and md.manageddevicedns in ('XYZ-2')

group by md.manageddevicedns

order by IdleAverage

We're currently unable to reproduce the problem but when we do, we will certainly post here other pieces of information that you had asked of us (like table definitions, output of vtrace etc...)

Would you have any recommendations or ideas based on the query provided earlier as to the cause of the exception? Please let us know - I appreciate your time. Thank you very much!

Sameer Apte