Hi,

I'm looking for some guidance with a problem I'm having with a stored
procedure and running out of memory.
ASA ver 8.0.2.4122
What I'm trying to figure out is what elements within a stored procedure
could eat up the memory if done a repeated number of times or does my stored
procedure fail because my system lacks the appropriate amount of memory to
do the job.

I have a stored procedure that loops around based on the number of records
in a cursor, performs 'selects' within the loop to find other data based on
the cursor data, performs some calculations and finally updates the DB
depending on the final result.
The number of records in the cursor is estimated at a few million records,
so the stored procedure would need to loop around a few million times.
However, the system runs out of memory even before 200,000 let alone 1
million. Now the system I am running on has 1GB of physical memory, but I
think the availability is more like 250MB.


Below is part of the error log that is produced by ASA.


Part of the error log produced by iSQL
----------------------------
+-----------------+

Current thread: Database interface thread
java.lang.OutOfMemoryError
<<no stack trace available>>



+----------+

Thu Nov 11 01:51:36 EST 2004
ISQL version = 8.0.2, Build 4218
CommandLineOptions
ConnectionInfo:
DSN=CRMDB;UID=dba;PWD=sql;DBN=CRMDB;<JDBCDRIVER>=c om.sybase.jdbc2.jdbc.SybDr
iver
Command tail: call asi_RunPricing(1)
Initial SQL statement: (none)
Execute initial SQL statement: No
Use GUI: No
Quiet mode is: Off
Syntax check mode is: Off

ConnectionInfo for active connection:
DSN=CRMDB;UID=dba;PWD=sql;DBN=CRMDB;<JDBCDRIVER>=c om.sybase.jdbc2.jdbc.SybDr
iver

*** A java.lang.OutOfMemoryError exception was caught while gathering
application information
null
-----------------------------------------------


The actual stored procedure code is really long, so I'm not going to post
all of it, but the following is part real/pseudo code (mostly pseudo).


/* declare statements */
/* cursor declare statement */
declare cur_Customer dynamic scroll cursor for
select intm_listing.customer_number, intm_listing.material_number,
intm_product.material_class, intm_customer.tax_classification,
intm_product.tax1
from intm_listing, intm_product, intm_customer
where intm_listing.customer_number = intm_customer.customer_number
and intm_listing.material_number = intm_product.material_id
and intm_listing.valid_from <= current date and intm_listing.valid_to >=
current date
union
select intm_listing.customer_number, intm_listing.material_number,
intm_product.material_class, intm_customer.tax_classification,
intm_product.tax1
from intm_listing, intm_product, intm_customer, (select a.customer_number
as cn1, b.customer_number as cn2 from intm_customer a left outer join
(select distinct intm_listing.customer_number from intm_listing) b on
a.customer_number=b.customer_number and b.customer_number=null) d
where intm_listing.customer_number = d.cn1
and d.cn1=intm_customer.customer_number
and intm_listing.material_number = intm_product.material_id
and intm_listing.valid_from <= current date and intm_listing.valid_to >=
current date;

open cursor with hold
loop
fetch first record from cursor

find parent nodes for this particular customer

/* Condition 1 */
set some variables;
perform select based on the variable values;
perform calculation based on results of select;

/* Condition 2 */
set some variables;
perform select based on the variable values;
perform calculation based on results of select;

/* Condition X (there are about 12 different conditions) */
....same as above....

check if resultant value is the same was what is already in database
if calculated value different from database, update database
else if calculated value the same don't do anything
else if calculated value doesn't exist, insert into database
for every 50 records processed, perform a commit;
end loop
close cursor
commit


TIA for any help

tom

This may be tied to some limitation of JConnect ...
try running it with the JDBC-ODBC bridge selected
(which in 8.0.2 is not the Sun one but our iAnywhere
JDBC driver that is now the default and recommended
JDBC driver in ASA 9).

Also feel free to compare this against one of the latest
8.0.2/.3 ebfs (only the client side is required for the test)
or 9.0.1 (the Developer Edition is freely downloadable)

Also JConnect 5.5 has numerous ebfs available for it. Last
time I checked EDS#13 (which would make it something
like version 5.5.0.13 if JConnect followed the standard numbering
system that ASA does). Those ebfs are available at

http://downloads.sybase.com/swd/base.do

(Note you will need to increase the timeframe to at least 9 months
to get the

"JConnect for JDBC"

Product Family to show up on that page since it has been a little while
since it rolled out an ebf for that ... Date: Apr 30, 2004 for ESD#13)




"Thomas" <thomas (AT) someplace (DOT) net> wrote

Are you referring to the -datasource flag for dbisql?
If so, do I just type '-datasource "JDBC-ODBC"'?

Thanks,

Tom

"Nick Elson" <no_spam_nicelson (AT) sybase (DOT) com> wrote

I believe you just use

-ODBC -c "UID=...;PID=...;ENG=...;DBF...;LINKS=TCPIP{HOST=. ..};....."


"Thomas" <thomas (AT) someplace (DOT) net> wrote