MaxDB 7.6 carshes on calling stored procedure with driver 7.6

[Former Member]

Hi All,

Recently I upgraded MaxDB from v7.5 to v7.6 after several hiccups I am almost done but one strange problem remains. I have a stored procedure which is used for generating Bill Of Material (recursive cursor). The said stored procedure uses some views (if that matters at all). If I call that stored procedure using JDBC driver v7.5 then all works well and if JDBC driver v7.6 is used to call the stored procedure it simply crashes the database. Does not it sound strange

Does anyone have any clues?

Thanks,

Vinod

[Former Member]

Hi,

you did not manage to prepare a reproducable testcase, did you?

Did you check if setting the installation parameter

OPTIMIZE_QUERYREWRITE to STATEMENT or NO will help?

Which exact version are we talking about?

As there is no traceback it is hard to guess where the problem may com from. Therefore some more info/reproducable testcase would be helpful.

Elke

[Former Member]

Hi Elke,

Due to time crunch I could not create a test case. It is really nice to know that you are following the user problems

Changing OPTIMIZE_QUERYREWRITE to STATEMENT does not make any difference though making it to NO reduces the severity of the problem. Now instead of crashing the DB it simply drops the query execution with following error-

Caused by: com.sap.dbtech.jdbc.exceptions.DatabaseException: [-9400]: Error
	at com.sap.dbtech.jdbc.packet.ReplyPacket.createException(ReplyPacket.java:63)

I will try to create a standalone runnable test case to reproduce it.

Thanks,

Vinod

[Former Member]

Oops! forgot to mention the DB version, it is 7.6.03.07.

Thanks,

Vinod

[Former Member]

Hi Elke,

I have created a small runnable test case which shows the problem. Please drop me an email (vinod at agileobjects dot com), I will send it along with necessary information.

Thanks,

Vinod

[former_member185699]

Hi,

the JDBC driver version 7.6 uses an enhanced communication protocol between the client and the database kernel. There seems to be a problem when using this enhanced protocol and DB procedures. Could you please provide me a database trace while reproducing the error. For details on how to create a database trace please see at:

<a href="http://sapdb.2scale.net/maxdb-wiki/VTrace">database trace</a>

Regards,

Marco

[Former Member]

Hi,

The <a href="http://www.agileobjects.com/MaxDb_knldiag_trace.zip">trace and knldiag</a> are available here.

Thanks,

Vinod

[Former Member]

Can someone provide some inputs on this?

Thanks,

Vinod

[markus_doehr2]

I think all your problems listed in knldiag.err are related to the fact, that your CACHE_SIZE is MUCH too small. You have 800 pages, which is 2,4 MB for the whole database structures.

Try the following:

dbmcli -u <dbmuser>,<dbmpassword> -d SUMANGGN param_directput CACHE_SIZE 20000

then stop and start your instance and try again.

--

Markus

[Former Member]

These files were from my development machine. On production server CACHE_SIZE is 120,000. Do I need to increase that also?

Thanks,

Vinod

[markus_doehr2]

So - I can't help you with the proper logfiles...

(I mean without)

Your development machine is not correctly configured, that error may be related to that.

Please post the files of your actual production system.

--

Markus

Message was edited by:

Markus Döhr

[Former Member]

Only difference between development and production server is of CACHE_SIZE everything else is same. And this CACHE_SIZE does not have any impact on the error as I have tried it both ways. Anyway will post actual files but result is same.

Thanks,

Vinod

[former_member185699]

Hi Vinod,

unfortunately we could not find the reason for the crash in the database trace file. My colleague guess that it comes from the recursive SELECT statement within your dbprocedure. Is it possible to provide us a small example that reproduces the error so that we can run it in our environment. We need at least the definition of the dbprocedure.

regards,

Marco

[Former Member]

Hi Marco,

Here is the stored procedure, which causes database crash-

-

---

START -

---

[code]CREATE DBPROC shortlist_all_month (IN schedule VARCHAR(14)) RETURNS CURSOR AS

$CURSOR = 'bom';

BEGIN

CREATE TABLE temp.bom_view

(bom_version, main_item_code, sub_item_code, sub_item_qty, bom_item_remarks) AS

SELECT bom_version, main_item_code, sub_item_code, sub_item_qty, bom_item_remarks

FROM suman.mfg_bom_m

WHERE bom_version = (SELECT MAX(bom_version) FROM suman.mfg_bom_m M

WHERE M.main_item_code = mfg_bom_m.main_item_code);

DECLARE :$CURSOR CURSOR FOR

WITH RECURSIVE PX (main, sub, qty, super_main) AS

(SELECT main_item_code, sub_item_code, sub_item_qty, main_item_code

FROM temp.bom_view WHERE main_item_code IN (

SELECT schedule_plan_item FROM suman.ppc_schedule_m WHERE schedule_code = :schedule)

UNION ALL

SELECT main_item_code, sub_item_code, sub_item_qty, super_main

FROM temp.bom_view B, suman.PX

WHERE sub = B.main_item_code)

SELECT sub AS sub_item_code, item_item_desc, item_mfg_flag, stock_item_qty,

SUM(qty * schedule_plan_qty) AS req_qty, (stock_item_qty - SUM(qty * schedule_plan_qty)) AS short_qty

FROM suman.PX B, suman.ppc_schedule_m S, suman.mfg_item_p I, suman.mfg_item_stock V

WHERE B.super_main = S.schedule_plan_item

AND schedule_code = :schedule

AND B.sub = I.item_item_code

AND B.sub = V.stock_item_code

AND V.stock_dept_code = 'DP0008'

GROUP BY sub, item_item_desc, item_mfg_flag, stock_item_qty

HAVING SUM(qty * schedule_plan_qty) > 0 ORDER BY 3, 1;

DROP TABLE TEMP.BOM_VIEW;

END;[/code]----

---

END -

---

This used to work fine for last >4 years. See if you can find something fishy. I will try to give a runnable test case to reproduce the crash.

Thanks,

Vinod

[markus_doehr2]

What do you see in knldiag (and/or knldiag.err) when the database crashes?

--

Markus