com.sybase.jdbc2. jdbc. SybSQLException

[Brusk Baran]

 XXXQuery: com.sybase.jdbc2.jdbc.SybSQLException: Can't allocate space 
for object 'temp worktable' in database 'tempdb' because 'system' segment is 
full/has no free extents. If you ran out of space in syslogs, dump the 
transaction log. Otherwise, use ALTER DATABASE or sp_extendsegment to increase
 size of the segment. 

Is this because JDBC has data limit it can cover?
i make many selects, aggregate functions, order by etc..
for a small set of data it works!!!

[edited to add line breaks]
[ September 26, 2005: Message edited by: Jeanne Boyarsky ]

[Tony Yan]

It means your log space is not enough to hold your transaction. Use dump transaction to clear the inactive log. However, if you job is too big. You have to either increase your log space or chop transaction into smaller pieces. Check sybase manual for transation log space management.
It has nothing to do with JDBC.

[Tony Yan]

It means your log space is not enough to hold your transaction. Use dump transaction to clear the inactive log. However, if you job is too big. You have to either increase your log space or chop transaction into smaller pieces. Check sybase manual for transation log space management.
It has nothing to do with JDBC.

[G. Nick]

I am having a similiar issue today. I am able to run the query (quite big with distinct, group by, etc...) through ISQL, but it throws the following error through java runtime environment ..


java.lang.RuntimeException: - com.sybase.jdbc2.jdbc.SybSQLException: Can't allocate space for object 'temp worktable' in database 'tempdb1' because 'system' segment is full/has no free extents. If you ran out of space in syslogs, dump the transaction log. Otherwise, use ALTER DATABASE to increase the size of the segment.



The query is :

--------------------------------------------------------------
[removed query]
--------------------------------------------------------------

[ September 26, 2005: Message edited by: ecomkid ]

[edited to remove code tags]

[ September 26, 2005: Message edited by: Jeanne Boyarsky ]
[ September 28, 2005: Message edited by: G. Nick ]

[Bear Bibeault]

"ecomkid",

There aren't many rules that you need to worry about here on the Ranch, but one that we take very seriously regards the use of proper names. Please take a look at the JavaRanch Naming Policy and adjust your display name to match it.

In particular, your display name must be a first and a last name separated by a space character, and must not be obviously fictitious.

Thanks!
bear
Forum Bartender

[Jeanne Boyarsky]

Nick,
Thanks for updating your display name.

I removed the code tags from your message because it was causing the page to scroll too much (which makes the posts difficult to read.) Feel free to add them back after inserting some line breaks.

Also note that you can start a new question. If it has been discussed a while ago (a year and a half in this case), feel free to link to that thread.

And welcome to JavaRanch!

[Jeanne Boyarsky]

Now on to your question:
The database is invariably running out of memory and resources because the query is so large. Try to refactor it into something smaller or multiple queries.

[Maximilian Xavier Stocker]

Nick,

Just to follow up on what Jeanne posted. I could not agree more. I think SQL often gets forgotten when it comes to maintaining code and what you posted is just so large.

I would really consider breaking that query into individual pieces or at have your DBA break it up into a proper Stored Procedure.

But to your question you mention that this does work in one circumstance but not in an other (Java). That always is an indication of some environment differences. There is no reason that something works in something and not in Java. As a wild guess something like the following is happening... when you execute in Java you are doing so as a different user with different permissions and don't have the permissions neccessary to allocate the required space on the server.

So I would consult with your DBA and see why you have different permissions or memory settings when you execute whereever it is that it works vs working in Java

[G. Nick]

thanks guys. I ran the CASE part separetly, and just tagged the result from that to the main SQL. this seems to be working fine.

its the same userid/database/ etc... I had checked all the obivious scenarios beforehand itself I am still baffled at why it ran in the iSQL window and not thru JDBC. Obviously something else is missing ...