I take a look on cloudscape 10.0. My job is to check out, how cloudscape handles queries with JOINS on 5-10 tables. No problem, if I create small queries.
But on biger queries ij print out this error:
[i]ERROR 58009: Execution failed due to a distribution protocol error that caused deallocation of the conversation. A DRDA Data Stream Syntax Error was detected. Reason: 0x13[/i]
My test tables have between 15 and 30 attributes with 20,000 120,000 entries. I will check out the performance of Cloudscape.
I believe Cloudscape stop queries, if they need more than 20 seconds. I could not find the right property. (http://publib.boulder.ibm.com/infocenter/cldscp10/index.jsp?topic=/com.ibm.cloudscape.doc/perf61.htm)
Can somebody give me an advice? [b]How can I scale the timeout up?[/b]
Thanks in advance!
NOTICE: developerWorks Community will be offline May 29-30, 2015 while we upgrade to the latest version of IBM Connections. For more information, read our upgrade FAQ.
This topic has been locked.
11 replies Latest Post - 2008-02-02T00:59:06Z by Stan
Pinned topic timeout @ executing queries ?!?
Answered question This question has been answered.
Unanswered question This question has not been answered yet.
Updated on 2008-02-02T00:59:06Z at 2008-02-02T00:59:06Z by Stan
Re: timeout @ executing queries ?!?2004-12-22T19:53:09Z in response to SystemAdminHi,
We'll need to get some more info to determine the source of the problem.
Please run the same queries in a database that does not contain any data, do you still see the errors?
Can you run the same queries with the original data and database using the embedded driver instead of using the Network Server and DB2 driver? Do you see any errors?
If so, please send the output.
Can you run sysinfo to get the version of the server you are using and send the output?
Is there any output in the derby.log file? If so, please send it.
Re: timeout @ executing queries ?!?2004-12-23T09:21:30Z in response to SystemAdminHello
Cloudscape: 10.0.2.0 (Build: 30301)
Java: 1.5.0 @ win XP && 1.4.2_04 @ solaris
Hardware: P4 3GHz 1GB RAM
JARs (derby, derbytools, derbynet): 10.0.2.0
JARs (db2jcc.jar, db2jcc_licence_c): 2.4
If I execute the queries on the empty database then I dont see any errors.
I tried to execute the queries with the embedded driver. Then I see a very interesting fact in derby.log:
[i]java.lang.OutOfMemoryError: Java heap space[/i]
I know, some of my test queries are realy big And use for example till 35 seconds on our oracle database. But my main test query use on Oracle less than 1 second.
Can somebody give me an advice, how I should go on?
thanks & merry christmas
cnig (from switzerland)
Re: timeout @ executing queries ?!?2004-12-23T18:07:40Z in response to SystemAdminHi,
Thanks for providing the additional information.
The information from derby.log is helpful. I'd like for you to try tuning Cloudscape using the embedded driver to see if you can alleviate the OutOfMemory errors. If you can resolve those, then we can see if the problem you are having with the Network Server persists.
Please refer to the article, A compendium of Cloudscape frequently asked questions, as a guide to resolving the OutOfMemory errors. There is a section, Troubleshooting, which specifically discusses the memory issues.
Primarily the advice involves configuring the JVM heap size and the Derby property, pageCacheSize.
Once you've tuned Cloudscape to resolve these memory errors, start the Network Server and run the same queries to see if you still get the DRDA errors.
If you do still get the errors when starting the network server 'normally', try applying the tuning parameters you used to resolve the OutOfMemory errors when using the embedded driver.
Let me know what happens in each of these steps.
Re: timeout @ executing queries ?!?2004-12-24T17:17:01Z in response to SystemAdminThank you very much! Now I have vacation while the next 3 weeks =)
But after, I will check out your advice and I will tell you about my success... ;-)
Merry X-mas and a happy new year!
Re: timeout @ executing queries ?!?2005-02-03T14:53:06Z in response to SystemAdminHi,
Now I tried your tips, but without success.
Now I finished my trial, because my problem occurred only with a test database. In the next months we dont use cloudscape for bigger databases. Or else we will look for a solution.
Thank you very much for your help!
Re: timeout @ executing queries ?!?2005-02-04T19:08:11Z in response to SystemAdminHi Cnig -
Susan asked me to take a look at your post. Can you provide additional information on the problem you are encountering? Are you still getting OutOfMemory (OOM) exceptions? If you still wish to work on this issue please post the messages from the derby.log file. I'd like to see the stack trace to know where the OOM (or other problem(s))happen. It would be helpful if you would set the development properties documented in the FAQ document (see Susans previous post) in the section: "Q: How do I set up my development environment for Cloudscape?" - this may also provide additional information on the problem.
Re: timeout @ executing queries ?!?2005-07-12T14:27:29Z in response to SystemAdmin
I am just another new guy trying it out, on Solaris as client-server, and I got this error from my ij client selecting count(*) from an unindexed unkeyed 400K-800K row table of character fields, when it got bigger. I also saw it if I queried while loading. I do not see any other errors logged, but maybe I have to turn on the stop hiding errors property. I ran SYSCS_UTIL.SYSCS_COMPRESS_TABLE() on the table, and for a while it worked (when not loading or compressing). Now, it seems to immediately hate select count(* ) but can do count (distinct COLNAME) and count(COLNAME) and count(1) OK, even 2 at once. Maybe it takes the * at face value in the count(*) context, and tries to lift the whole table! I know a lot of people feel funny saying where exists ( select * . . . for fear it is slower than where exists ( select 1 . . . ; maybe the fear has come home to roost! Here are two queries I ran, through my ksh ij wrapper (I love the ksh command recall and edit for queries, too):
$ timex cludb2 testdb x select count(exch_cd) from OCC_410
1 row selected
$ timex cludb2 testdb x select count(*) from OCC_410
ERROR 58009: Execution failed due to a distribution protocol error that caused deallocation of the conversation. A DRDA Data Stream Syntax Error was detected. Reason: 0x13
Apparently, the default install has a conservative 60 second timer or some storage limit for queries. I think we need to set the timeout to -1 (and ship it that way), but no useful example derby.properties file is provided and the instructions are really vague. Even syslook does not say where the system directory is, either. My guess is that it is where I start up the server (and syslook), but the documents should be more explicit in both explanation and example. I guess I can hack in a -D until I figure out the derby.properties format. Of course, I am not sure which property is the culprit!
Re: timeout @ executing queries ?!?2005-07-13T02:14:17Z in response to SystemAdminI found the right call in the 10.1 Alpha Derby docs today, to set the lock wait to -1. I recommend downloading them all into a directory so you can use the Acrobat 6 "search all files in directory" feature. This was a URL when I pasted it in:
so what I did was:
Re: timeout @ executing queries ?!?2008-02-01T23:40:08Z in response to SystemAdminI get the same error. I tried everything, looked up half of the world wide web and still nothing. I finally ended up here and hope I'll get the answer. Thanks in advance!
Stan 120000HAGM267 PostsACCEPTED ANSWER
Re: timeout @ executing queries ?!?2008-02-02T00:59:06Z in response to SystemAdminHi -
Regarding: ERROR 58009: Execution failed due to a distribution protocol error ....
This error is very generic so I will need to know more about your configuration (sysinfo output) and what SQL is processing when the error happens. Are you using Derby 10.3? Are you using large datatypes? Old versions of Derby and the client driver required properties in order to handle large data values, others did not handle them at all.
Tell me more about what you are doing, your data and your environment and I will attempt to advise you.