What would cause this, and how can I adjust ?

Using SQL Anywhere 16, dbsrv16 64 Bit, Java 8 64 Bit, on Windows 7 with 8G Memory. No other applications are running

The error occurs running a Stored Procedure, that has a Dynamic Scroll Cursor looping thru 1.8 Million Records

It has worked previously with up to 360,000 Records

Any advice appreciated

Greg

asked 11 Jun, 16:31

Gregory%20Durniak's gravatar image

Gregory Durniak
31347
accept rate: 0%

edited 12 Jun, 01:37

Oh! I know, I know! Oops, sorry – to find who is consuming the heap, please tell JVM to create the dump after OOM:

http://www.oracle.com/technetwork/java/javase/clopts-139448.html#gbzrr

You should analyze it with "mat" to see the heaviest objects in the heap. You can give us your heap as well, but pay attention that one can read your private stuff.

Without the heap dump, I won't help much since I don't know what your code is doing.

(12 Jun, 02:37) Vlad
Replies hidden

So what application is running besides the database server, and what is it doing? Or does the database server make use of external stored procedures written in Java?

(12 Jun, 03:01) Volker Barth

The Stored Procedure is run with a Query from FileMaker Pro ( on another machine ), using a JDBC Plugin. The Stored Procedure is in SQL Anywhere.

Just curious: if dbsrv16 is an exe application, what does Java do ?

(12 Jun, 03:16) Gregory Durniak
Replies hidden

> what does Java do ?

The default answer is "nothing". Two possibilities come to mind: First, the stored procedure is written in Java... but since you know that the stored procedure contains "a Dynamic Scroll Cursor", presumably you would also know if the procedure is written in Java.

Second, the client is running Java; for example, the CALL statement is run in a dbisql window, and it is dbisql (a Java client application) which is failing, not the server.

It is up to you to determine where the Java code is.

(12 Jun, 03:43) Breck Carter

The CALL Statement is sent thru JDBC from a Client machine. Does that open dbisql on the Server Machine ? How can I adjust the default Heap Size ? The procedure was written using Watcom-SQL in Sybase Central

(12 Jun, 03:50) Gregory Durniak
Replies hidden

Hm, I still get the data flow between the client machine using JDBC to call a Watcom-SQL stored procedure within the database server and the "Query from FileMaker Pro ( on another machine ), using a JDBC Plugin" - is FileMaker Pro that client, or is the database server accessing another data source within the stored procedure?

As to your last statement: No, a JDBC client application does not open a DBISQL session on the server machine.

And just to assure: Where does the error message appear - on the client or in the database server's machine?

(12 Jun, 04:18) Volker Barth

FileMaker just sends a query thru JDBC, to run the Procedure. It does not get, or need a response, so I do not believe the problem is on the "client" side

Again, if DBISQL does not open, what does JAVA do ?

(12 Jun, 04:23) Gregory Durniak
Replies hidden

FileMaker just sends a query thru JDBC, to run the Procedure. It does not get, or need a response, so I do not believe the problem is on the "client" side.

So what is the result of the stored procedure (SP)? If it returns a result set (say, via one or more final SELECT statements within its body), it will supply a result set, and it's up to the client (the FileMaker Pro app in your case, in my understanding) to consume that. Stored procedures are not called in a "fire-and-forget" mode, the server expects the client to consume the result...

Here's a FAQ dealing with the way SQL Anywhere handles SP result sets (compared to other DBMS's), see Mark's response.


In contrast, if the SP just does heavy calculations within the database (by scrolling through large result sets) and just returns some return value (or fill some inout/out parameters), like number of "appropriate rows" or a calculation result or the like, and if it does not itself use Java (i.e. it does not call any external stored procedure/function written in Java), I would not know why it should generate a Java error.

(12 Jun, 04:54) Volker Barth

The Stored procedure scolls thru a View ( a UNION of two tables ), does calculations within the database, and sets various values. No value, or result set is returned. Not sure why it would "break", just because there are more records.

(12 Jun, 05:05) Gregory Durniak

So does the error also appear when you call the SP from another client? (Say via the C-based dbisqlc utility that does not use Java at all...)

I still have no clue why you get a Java Heap error at all.

(12 Jun, 06:16) Volker Barth
1

Hm, it seems that you have ignored my suggestion. Instead of guessing and blindly reviewing existing or 3rd party code, the heap dump would answer all your questions.

Believe me, regardless my rating I know what I am talking about :-/

(12 Jun, 08:56) Vlad
More comments hidden
showing 5 of 11 show all flat view

Thanks for all your quick responses ! Since it can't be the database, I checked the 360Works JDBC Plugin on the "client" side. It does have an ini to increase the Java Heap. That appears to have fixed it.
Note: not sure how a few short text queries can run out of memory, or why the number of records processed matters. The Procedure has no return value.

permanent link

answered 12 Jun, 07:47

Gregory%20Durniak's gravatar image

Gregory Durniak
31347
accept rate: 0%

converted 15 Jun, 10:15

Breck%20Carter's gravatar image

Breck Carter
25.7k428592852

JAVA uses JNI for shared lib access, obviously in guindows its some different in memory management.. but the principle was to work in any UNIX or other OS due performance and stability.. The new behaviour in Sybase from version 12 was multiplatform.. no so much impact in the bussines.. the JNI implementation permits access to OS specific properties using shared lib objects files due JAVA are very very slower and unoptimized in performance..

JNI its a java implementation to combine other programin access languaje like C with interacting to JAVA languaje

permanent link

answered 15 Jun, 09:33

mckaygerhard's gravatar image

mckaygerhard
81
accept rate: 0%

edited 15 Jun, 09:34

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×65
×7

question asked: 11 Jun, 16:31

question was seen: 409 times

last updated: 15 Jun, 09:35