Mark Wong wrote:
> Aside from problems with repmcli dumping core when a store
> procedure is
> too long, I have come across some other issues with using a ODBC
> application to load a stored procedure.
>
> As much as I would like to post the stored procedure in full, but it's
> been rejected twice so I'l briefly reproduce it. :) :
>
> CREATE DBPROC order_display(IN c_uname VARCHAR(20), IN c_passwd
> VARCHAR(20),
> OUT items FIXED(2),
> OUT o_id FIXED(10), OUT c_fname VARCHAR(15), OUT c_lname VARCHAR(15),
> OUT c_phone VARCHAR(16), OUT c_email VARCHAR(50), OUT o_date
> TIMESTAMP,
> OUT o_sub_total FIXED(17, 2), OUT o_tax FIXED(17, 2), OUT o_total
> FIXED(17, 2),
> OUT o_ship_type VARCHAR(10), OUT o_ship_date TIMESTAMP,
> OUT o_status VARCHAR(15),
> OUT bill_addr_street1 VARCHAR(40), OUT bill_addr_street2 VARCHAR(40),
> OUT bill_addr_city VARCHAR(30), OUT bill_addr_state VARCHAR(20),
> OUT bill_addr_zip VARCHAR(10), OUT bill_co_name VARCHAR(50),
> OUT ship_addr_street1 VARCHAR(40), OUT ship_addr_street2 VARCHAR(40),
> OUT ship_addr_city VARCHAR(30), OUT ship_addr_state VARCHAR(20),
> OUT ship_addr_zip VARCHAR(10), OUT ship_co_name VARCHAR(50),
> OUT i_id1 FIXED(10), OUT i_title1 VARCHAR(60),
> OUT i_publisher1 VARCHAR(60), OUT i_cost1 FIXED(17, 2),
> OUT ol_qty1 FIXED(3), OUT ol_discount1 FIXED(17, 2),
> OUT ol_comments1 VARCHAR(100),
>
> ...
>
> OUT i_id100 FIXED(10), OUT i_title100 VARCHAR(60),
> OUT i_publisher100 VARCHAR(60), OUT i_cost100 FIXED(17, 2),
> OUT ol_qty100 FIXED(3), OUT ol_discount100 FIXED(17, 2),
> OUT ol_comments100 VARCHAR(100),
> OUT cx_type VARCHAR(10), OUT cx_auth_id CHAR(15)) AS
> VAR c_id FIXED(10); o_bill_addr_id FIXED(10);
> o_ship_addr_id FIXED(10);
> BEGIN
> SET i_id1 = 0;
> SET i_title1 = '';
> SET i_publisher1 = '';
> SET i_cost1 = 0;
> SET ol_qty1 = 0;
> SET ol_discount1 = 0;
> SET ol_comments1 = '';
>
> ...
>
> SET i_id100 = 0;
> SET i_title100 = '';
> SET i_publisher100 = '';
> SET i_cost100 = 0;
> SET ol_qty100 = 0;
> SET ol_discount100 = 0;
> SET ol_comments100 = '';
> SELECT c_id, c_fname, c_lname, c_phone, c_email
> FROM tpcw.customer
> WHERE c_uname = :c_uname
> AND c_passwd = :c_passwd;
> IF $rc = 0 THEN
> BEGIN
> FETCH INTO :c_id, :c_fname, :c_lname, :c_phone, :c_email;
> SELECT o_id, o_date, o_sub_total, o_tax, o_total, o_ship_type,
> o_ship_date,
> o_status, o_bill_addr_id, o_ship_addr_id
> FROM tpcw.orders, tpcw.customer
> WHERE o_c_id = :c_id
> ORDER BY o_date DESC;
> IF $rc = 0 THEN
> BEGIN
> FETCH INTO :o_id, :o_date, :o_sub_total, :o_tax, :o_total,
> :o_ship_type,
> :o_ship_date, :o_status, :o_bill_addr_id,
> :o_ship_addr_id;
> SELECT cx_type, cx_auth_id
> INTO :cx_type, :cx_auth_id
> FROM tpcw.cc_xacts
> WHERE cx_o_id = :o_id;
> SELECT addr_street1, addr_street2, addr_city, addr_state, addr_zip,
> co_name
> INTO :bill_addr_street1, :bill_addr_street2, :bill_addr_city,
> :bill_addr_state, :bill_addr_zip, :bill_co_name
> FROM tpcw.address, tpcw.country
> WHERE addr_id = :o_bill_addr_id
> AND addr_co_id = co_id;
> SELECT addr_street1, addr_street2, addr_city, addr_state, addr_zip,
> co_name
> INTO :ship_addr_street1, :ship_addr_street2, :ship_addr_city,
> :ship_addr_state, :ship_addr_zip, :ship_co_name
> FROM tpcw.address, tpcw.country
> WHERE addr_id = :o_ship_addr_id
> AND addr_co_id = co_id;
> SELECT ol_i_id, i_title, i_publisher, i_cost, ol_qty, ol_discount,
> ol_comments
> FROM tpcw.item, tpcw.order_line
> WHERE ol_o_id = :o_id
> AND ol_i_id = i_id;
> SET items = 0;
> IF $rc = 0 THEN
> BEGIN
> FETCH INTO :i_id1, :i_title1, :i_publisher1, :i_cost1, :ol_qty1,
> :ol_discount1, :ol_comments1;
> SET items = items + 1;
> END;
>
> ...
>
> IF $rc = 0 THEN
> BEGIN
> FETCH INTO :i_id100, :i_title100, :i_publisher100, :i_cost100,
> :ol_qty100, :ol_discount100, :ol_comments100;
> SET items = items + 1;
> END;
> END;
>
> Here is the only resulting SQL error:
>
> SQLSTATE S1000
> [SAP AG][LIBSQLOD SO][SAP DB]General error;-1110 POS(1) View
> definition
> too long.
>
> The full description is:
>
> -1107: Too complicated SQL statement (too many internal commands)
> Explanation: The SQL statement is so complicated that more internal
> statements must be generated for it than can be administered
> for an SQL
> statement. User Action: Simplify the SQL statement or split it into
> several SQL statements.
>
>
> I get the impression that SAP DB cannot handle a stored procedure this
> large, even when increasing _PACKE_SIZE to the max of 131072 bytes and
> _MAXTASK_STACK to 2048 KB. The file size of my stored procedure is
> 56762 bytes. Any help on how to reduce my procedure size,
> short of not
> supporting 100 items (resulting in over 600 parameters), would be
> greatly appreciated.
Mhm, as you can imagine, we tried your dbprocedure by ourselves in the
meantime.
We found that error, too.
We have to change some coding to allow such length of dbprocs.
Therefore: even the next kernel version (7.3.0.21) will not be able to
handle
your special problem. Sorry, therefore we thought for a workaround, too.
1. to increase _PACKET_SIZE is fine, at least 64KB are ok for your dbproc,
if you like 131072, ok
2. increasing _MAXTASK_STACK to 2048KB is not that fine.
it will not help in your case (your case is quite different to that one
of
Cataldo out of this list. And even he needed a _MAXSTACK_SIZE
of around 360-380KB, not 2048KB).
I would ask you to decrease it ! Set it to the default-value. That will
be enough and save memory (especially if MAXUSERTASKS will be more
than a handful)
3) As far as I understand your dbproc, you input 2 values, prepare several
intermediate results, whose values are returned from this dbproc.
With the last select:
SELECT ol_i_id, i_title, i_publisher, i_cost, ol_qty, ol_discount,
ol_comments
FROM tpcw.item, tpcw.order_line
WHERE ol_o_id = :o_id
AND ol_i_id = i_id;
you create a resultset with not more than 100 results (or the first 100
results
are enough for you). Their values are returned, increase the number of
output-Variable
to this enormous number and make the dbproc that long.
What do you think to create a (TMP) table inside or outside the dbproc
to hold the results of the last select, empty that table in the beginning of
the stored proc, and do an
INSERT mytmptable
SELECT ol_i_id, i_title, i_publisher, i_cost, ol_qty, ol_discount,
ol_comments
FROM tpcw.item, tpcw.order_line
WHERE ol_o_id = :o_id
AND ol_i_id = i_id;
Then you can select from that table and fetch the resultrows one by one.
The output-parameter of your dbproc would just be those intermediate
results,
the initialisation and the fetches of some hundred values could be avoided
(BTW: all output-parameter are initialized with NULL internally.)
but your way of selecting (those 4-5 selects in the dbproc) can be hidden
from the user.
We are working to make such kind of dbproc a possible one, but I do not
know if you have time to wait for that release which will fix everything
(remember, it will be NOT fixed in 7.3.0.21, the next relase to come).
Elke
SAP Labs Berlin
_______________________________________________
sapdb.general mailing list
[EMAIL PROTECTED]
http://listserv.sap.com/mailman/listinfo/sapdb.general
