Topic
11 replies Latest Post - ‏2008-02-02T00:59:06Z by Stan
SystemAdmin
SystemAdmin
1525 Posts
ACCEPTED ANSWER

Pinned topic timeout @ executing queries ?!?

‏2004-12-22T13:46:04Z |
Hello!

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!
cnig
Updated on 2008-02-02T00:59:06Z at 2008-02-02T00:59:06Z by Stan
  • SystemAdmin
    SystemAdmin
    1525 Posts
    ACCEPTED ANSWER

    Re: timeout @ executing queries ?!?

    ‏2004-12-22T19:53:09Z  in response to SystemAdmin
    Hi,

    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.

    Thanks,

    Susan
    • SystemAdmin
      SystemAdmin
      1525 Posts
      ACCEPTED ANSWER

      Re: timeout @ executing queries ?!?

      ‏2004-12-23T09:21:30Z  in response to SystemAdmin
      Hello

      Versions:
      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 don’t 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)
      • SystemAdmin
        SystemAdmin
        1525 Posts
        ACCEPTED ANSWER

        Re: timeout @ executing queries ?!?

        ‏2004-12-23T18:07:40Z  in response to SystemAdmin
        Hi,

        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.

        http://www-128.ibm.com/developerworks/db2/library/techarticle/dm-0408bradbury/index.html

        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.

        Thanks,

        Susan
        • SystemAdmin
          SystemAdmin
          1525 Posts
          ACCEPTED ANSWER

          Re: timeout @ executing queries ?!?

          ‏2004-12-24T17:17:01Z  in response to SystemAdmin
          Thank 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!
        • SystemAdmin
          SystemAdmin
          1525 Posts
          ACCEPTED ANSWER

          Re: timeout @ executing queries ?!?

          ‏2005-02-03T14:53:06Z  in response to SystemAdmin
          Hi,

          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 don’t use cloudscape for bigger databases. Or else we will look for a solution.

          Thank you very much for your help!
          cnig
          • SystemAdmin
            SystemAdmin
            1525 Posts
            ACCEPTED ANSWER

            Re: timeout @ executing queries ?!?

            ‏2005-02-04T19:08:11Z  in response to SystemAdmin
            Hi 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.
      • SystemAdmin
        SystemAdmin
        1525 Posts
        ACCEPTED ANSWER

        Re: timeout @ executing queries ?!?

        ‏2005-01-05T23:41:19Z  in response to SystemAdmin
        You might find this link helpful.
        http://issues.apache.org/jira/browse/DERBY-106
        As a workaround you might try setting derby.language.maxMemoryPerTable=0

  • SystemAdmin
    SystemAdmin
    1525 Posts
    ACCEPTED ANSWER

    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
    802032

    1 row selected

    real 1:27.06
    user 3.04
    sys 0.44

    $ 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

    real 4.38
    user 2.91
    sys 0.43

    $

    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!
  • SystemAdmin
    SystemAdmin
    1525 Posts
    ACCEPTED ANSWER

    Re: timeout @ executing queries ?!?

    ‏2005-07-13T02:14:17Z  in response to SystemAdmin
    I 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:
    http://incubator.apache.org/derby/manuals/tuning/perf82.html#HDRSII-PROPER-46141

    so what I did was:

    CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.locks.waitTimeout','-1')
  • SystemAdmin
    SystemAdmin
    1525 Posts
    ACCEPTED ANSWER

    Re: timeout @ executing queries ?!?

    ‏2008-02-01T23:40:08Z  in response to SystemAdmin
    I 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!
    Prelit trees
    • Stan
      Stan
      267 Posts
      ACCEPTED ANSWER

      Re: timeout @ executing queries ?!?

      ‏2008-02-02T00:59:06Z  in response to SystemAdmin
      Hi -

      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.