Re: derby performance and 'order by'
Rick Hillegas wrote: Hi Oyvind, I agree that this is inelegant. As you note, this approach step by step forces a plan which the current Derby optimizer is capable of considering--with or without the covering index. Regardless of whether we teach the optimizer some better tricks, I think it's worth beefing up our support for in-memory temporary tables: o There are always deceptively simple queries which the optimizer misjudges. It's good to give the customer tools for getting unstuck while they wait for the bugfix release. o Often the customer knows facts about the data which the optimizer can't plausibly learn. Yes, I agree with you. o The current Derby optimizer is capable of considering only a very limited subset of the useful plans. That reminds me of a very entertaining presentation which was held at VLDB this year: Slides: http://www.vldb2005.org/program/slides/fri/s1228-reddy.ppt Paper: http://www.vldb2005.org/program/paper/fri/p1228-reddy.pdf Have a look, it's worth the time. We should definitely consider more execution plans in Derby, so that we, too, could draw such interesting pictures. ;o) -- Oyvind Bakksjo Sun Microsystems, Database Technology Group Trondheim, Norway http://weblogs.java.net/blog/bakksjo/
Re: Distributed databases
JAC == James A Craig/O/VCU [EMAIL PROTECTED] writes: JAC Hi, I'm fairly new to Derby but I was curious if its possible JAC to use it in a distributed setup. I currently have a small JAC cluster and want to set it up so that I have a distributed JAC database on it using Derby. So my questions are: JAC JAC 1) Is this possible? It is a bit unclear to me what you mean by using it in a distributed setup. Do you just want to access the database from all nodes of the cluster, or do you actually want for performance reasons a single distributed database which runs on all nodes. The first part is possible and is called a network server, the latter is not. The rest of my answers will assume that you want to be able to access a single-node database server from other nodes. JAC 2) Is there a resource that shows me how to do this? (the JACmanuals don't seem to explain how to do this) This is explained in the Derby Server and Administration Guide. JAC 3) From what I've read it seems like I need to use the DB2 JACdriver to do this JAC (like I've said, I've found very little info on the subject JAC of setting up a distributed database). Is this even remotely JAC on base? This was true for the first Derby release. The latest release comes with an open-source JDBC driver. JAC 4) Does anyone know of any decent tutorials when it comes to JACDerby? I haven't JAC found anything that great thus far. JAC 5) Can I use ij to manipulate a distributed database? How do I do this? Yes, ij can connect to a network server if you specify an URL containing hostname and port number. JAC 6) Is there a way to setup one using ij? There are scripts that may be used to start a network server. -- Øystein
Re: Distributed databases
James A Craig/O/VCU wrote: 4) Does anyone know of any decent tutorials when it comes to Derby? I haven't found anything that great thus far. The recent Linux Magazine article referenced in a previous thread on this mailing list might function as a sort of general tutorial for (newbie) derby users, although some information in the article is already outdated. http://www.linux-mag.com/content/view/2134/ -- John
Locks not released on error
Hi! We're using Derby version 10.1 (Bundle-Version: 10.1.100.208786) embeddedly in our system. Even though I've put extensive rollback and statement closing handling in the code, we still occasionally see cases where a table gets permanently locked after an error during update (detail below). Is there something other than closing open statements and rolling back existing savepoints that could be required for releasing locks? Am I doing the closing/rolling back wrongly? Any other ideas on how to cure this would be welcome. Thanks, -Lars The errors we get are as follows: First we get the error during an update of the HARVEST_CONFIGS table (covered by rollback and statement closing in finallys), then a little later another thread (using a separate Connection) tries to obtain a lock on the HARVESTDEFINITIONS table. The latter table was updated earlier in the same transaction that had the error, but the statement that updated the HARVESTDEFINITIONS table has already been closed. The methods used are shown below the exceptions. dk.netarkivet.exceptions.IOFailure: SQL error while updating harvest definition HD #44: 'test' at dk.netarkivet.harvestdefinition.HarvestDefinitionDBDAO.update(HarvestDefinitionDBDAO.java:487) at dk.netarkivet.harvestdefinition.PartialHarvest.addSeeds(PartialHarvest.java:375) at dk.netarkivet.webinterface.EventHarvest.addConfigurations(EventHarvest.java:110) at org.apache.jsp.Definitioner_0002daddseeds_jsp._jspService(Definitioner_0002daddseeds_jsp.java:68) at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:137) at javax.servlet.http.HttpServlet.service(HttpServlet.java:853) at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:210) at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:295) at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:241) at javax.servlet.http.HttpServlet.service(HttpServlet.java:853) at org.mortbay.jetty.servlet.ServletHolder.handle(ServletHolder.java:358) at org.mortbay.jetty.servlet.WebApplicationHandler.dispatch(WebApplicationHandler.java:294) at org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:567) at org.mortbay.http.HttpContext.handle(HttpContext.java:1807) at org.mortbay.jetty.servlet.WebApplicationContext.handle(WebApplicationContext.java:525) at org.mortbay.http.HttpContext.handle(HttpContext.java:1757) at org.mortbay.http.HttpServer.service(HttpServer.java:879) at org.mortbay.http.HttpConnection.service(HttpConnection.java:790) at org.mortbay.http.HttpConnection.handleNext(HttpConnection.java:961) at org.mortbay.http.HttpConnection.handle(HttpConnection.java:807) at org.mortbay.http.SocketListener.handleConnection(SocketListener.java:197) at org.mortbay.util.ThreadedServer.handle(ThreadedServer.java:276) at org.mortbay.util.ThreadPool$PoolThread.run(ThreadPool.java:511) Caused by: SQL Exception: The statement was aborted because it would have caused a duplicate key value in a unique or primary key constraint or unique index identified by 'SQL050921094346420' defined on 'HARVEST_CONFIGS'. at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source) at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source) at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source) at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source) at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source) at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(Unknown Source) at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeStatement(Unknown Source) at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeUpdate(Unknown Source) at dk.netarkivet.harvestdefinition.HarvestDefinitionDBDAO.createHarvestConfigsEntries(HarvestDefinitionDBDAO.java:226) at dk.netarkivet.harvestdefinition.HarvestDefinitionDBDAO.update(HarvestDefinitionDBDAO.java:476) ... 22 more Sep 22, 2005 3:48:02 PM dk.netarkivet.harvestscheduler.HarvestScheduler$1 run SEVERE: Exception while scheduling new jobs dk.netarkivet.exceptions.IOFailure: SQL Error while asking for all harvest definitions at dk.netarkivet.harvestdefinition.HarvestDefinitionDBDAO.getAllHarvestDefinitions(HarvestDefinitionDBDAO.java:518) at dk.netarkivet.harvestdefinition.HarvestDefinitionDAO.generateJobs(HarvestDefinitionDAO.java:173) at dk.netarkivet.harvestscheduler.HarvestScheduler.scheduleJobs(HarvestScheduler.java:199) at dk.netarkivet.harvestscheduler.HarvestScheduler.access$100(HarvestScheduler.java:57) at
Re: Distributed databases
On Thursday 22 September 2005 04:07, Øystein Grøvlen wrote: JAC == James A Craig/O/VCU [EMAIL PROTECTED] writes: JAC Hi, I'm fairly new to Derby but I was curious if its possible JAC to use it in a distributed setup. I currently have a small JAC cluster and want to set it up so that I have a distributed JAC database on it using Derby. So my questions are: JAC JAC 1) Is this possible? It is a bit unclear to me what you mean by using it in a distributed setup. Do you just want to access the database from all nodes of the cluster, or do you actually want for performance reasons a single distributed database which runs on all nodes. The first part is possible and is called a network server, the latter is not. The rest of my answers will assume that you want to be able to access a single-node database server from other nodes. I can understand your confusion. At first it looks like he wants to create an HADR setup using Derby. But in a later question, he used the term distributed meaning remote Assuming that he wants to do HADR and not create a parallelized engine, I think that there is some merit to this. JAC 5) Can I use ij to manipulate a distributed database? How do I do this? (This is the question that I was referencing above.) -- Michael Segel Principal MSCC (312) 952-8175
Re: Distributed databases
Thank you for the fast response. I was looking to set it up so that it was a single database with table 1 on node 1, table 2 on node 2, etc. But if I'm reading what you said correctly, this isn't possible. I was reading a book that dealt with doing this and I wanted to try it out in practice (it didn't go into any details with actually doing it). However it sounds like Derby will not let me do this. Is there a database system out there that will let me do this?However I really like Derby thus far and John thank you for the article. Between it and using the manual I've been able to get started.James[EMAIL PROTECTED] (Øystein Grøvlen) wrote: -To: Derby Discussion derby-user@db.apache.orgFrom: [EMAIL PROTECTED] (Øystein Grøvlen)Date: 09/22/2005 05:07AMSubject: Re: Distributed databases "JAC" == James A Craig/O/VCU [EMAIL PROTECTED] writes: JAC Hi, I'm fairly new to Derby but I was curious if its possible JAC to use it in a distributed setup. I currently have a small JAC cluster and want to set it up so that I have a distributed JAC database on it using Derby. So my questions are: JAC JAC 1) Is this possible?It is a bit unclear to me what you mean by using it in a distributedsetup. Do you just want to access the database from all nodes of thecluster, or do you actually want for performance reasons a singledistributed database which runs on all nodes. The first part ispossible and is called a network server, the latter is not. The restof my answers will assume that you want to be able to access asingle-node database server from other nodes. JAC 2) Is there a resource that shows me how to do this? (the JAC manuals don't seem to explain how to do this)This is explained in the Derby Server and Administration Guide. JAC 3) From what I've read it seems like I need to use the DB2 JAC driver to do this JAC (like I've said, I've found very little info on the subject JAC of setting up a distributed database). Is this even remotely JAC on base?This was true for the first Derby release. The latest release comeswith an open-source JDBC driver. JAC 4) Does anyone know of any decent tutorials when it comes to JAC Derby? I haven't JAC found anything that great thus far. JAC 5) Can I use ij to manipulate a distributed database? How do I do this?Yes, ij can connect to a network server if you specify an URLcontaining hostname and port number. JAC 6) Is there a way to setup one using ij?There are scripts that may be used to start a network server.-- Øystein
Re: Distributed databases
Hi, if you will ultimately access your database using JDBC, you might want to check this: http://c-jdbc.objectweb.org/. I'm not sure if that fits the bill in your situation but you could waste 5 minutes reading the blurbs there ;) Regards, Carlos.
Re: Distributed databases
Carlos A. Carnero Delgado wrote: Hi, if you will ultimately access your database using JDBC, you might want to check this: http://c-jdbc.objectweb.org/. I'm not sure if that fits the bill in your situation but you could waste 5 minutes reading the blurbs there ;) Emmanuel Cecchet wrote a HOWTO use C-JDBC with Apache Derby document you can download from here: http://db.apache.org/derby/integrate/index.html -jean
Re: Distributed databases
James A Craig/O/VCU wrote: Thank you for the fast response. I was looking to set it up so that it was a single database with table 1 on node 1, table 2 on node 2, etc. But if I'm reading what you said correctly, this isn't possible. I was reading a book that dealt with doing this and I wanted to try it out in practice (it didn't go into any details with actually doing it). However it sounds like Derby will not let me do this. Is there a database system out there that will let me do this? However I really like Derby thus far and John thank you for the article. Between it and using the manual I've been able to get started. Take a look at C-JDBC, see the presentation on this page: http://db.apache.org/derby/integrate/index.html C-JDBC, clustered JDBC, supports what you want to do and supports Derby. Dan.
Re: Can you use createFrom to create a database from a backup database stored in a JAR file?
Michael Vinca wrote: Hello, I wasn't sure if this was going to work or not. There was nothing in the documentation to suggest it would, I was just hopeful. I tried searching the archives for createFrom but only got one hit, so I hope this is not a repeat question. I am attempting to create a writable database from a read only database in a JAR file. The idea is to have a clickable JAR file that can determine if an external database exists, and if not, creates it from an original (not blank) database stored within the JAR file. A code snippet follows: This would be a good addition for Derby. I've long wanted the backup procedure to be able to produce a jar file that can be used as a read-only database. A logical extension of that would be what you want, the ability to restore from such a jar. Dan.
Re: Distributed databases
James A Craig/O/VCU wrote: Hi, I'm fairly new to Derby but I was curious if its possible to use it in a distributed setup. I currently have a small cluster and want to set it up so that I have a distributed database on it using Derby. So my questions are: 1) Is this possible? 2) Is there a resource that shows me how to do this? (the manuals don't seem to explain how to do this) 3) From what I've read it seems like I need to use the DB2 driver to do this (like I've said, I've found very little info on the subject of setting up a distributed database). Is this even remotely on base? 4) Does anyone know of any decent tutorials when it comes to Derby? I haven't found anything that great thus far. 5) Can I use ij to manipulate a distributed database? How do I do this? 6) Is there a way to setup one using ij? Sorry for all of the questions but I've searched through a couple hundred websites while searching on google and have found little to no info. Thanks in advance for any help. Andy Hi, There is a paper on integrating Derby with C-JDBC (Clustered Jdbc) at: http://db.apache.org/derby/integrate/index.html The C-JDBC info can be found at - http://c-jdbc.objectweb.org/ Hope the above helps. Regards, Rajesh
Re: derby performance and 'order by'
Thanks for the pointer to this presentation, Oyvind. It's a pretty startling observation though I'm not sure how to use it. I'd be interested in hearing your thoughts about this some time. Cheers, -Rick That reminds me of a very entertaining presentation which was held at VLDB this year: Slides: http://www.vldb2005.org/program/slides/fri/s1228-reddy.ppt Paper: http://www.vldb2005.org/program/paper/fri/p1228-reddy.pdf Have a look, it's worth the time. We should definitely consider more execution plans in Derby, so that we, too, could draw such interesting pictures. ;o)
Re: Distributed databases
Thank you, I'll take a look at C-JDBC. I'm actually a little surprised how fast I got an answer to all my questions. Thank you to everyone.James-Rajesh Kartha [EMAIL PROTECTED] wrote: -To: Derby Discussion derby-user@db.apache.orgFrom: Rajesh Kartha [EMAIL PROTECTED]Date: 09/22/2005 11:24AMSubject: Re: Distributed databasesJames A Craig/O/VCU wrote: Hi, I'm fairly new to Derby but I was curious if its possible to use it in a distributed setup. I currently have a small cluster and want to set it up so that I have a distributed database on it using Derby. So my questions are: 1) Is this possible? 2) Is there a resource that shows me how to do this? (the manuals don't seem to explain how to do this) 3) From what I've read it seems like I need to use the DB2 driver to do this (like I've said, I've found very little info on the subject of setting up a distributed database). Is this even remotely on base? 4) Does anyone know of any decent tutorials when it comes to Derby? I haven't found anything that great thus far. 5) Can I use ij to manipulate a distributed database? How do I do this? 6) Is there a way to setup one using ij? Sorry for all of the questions but I've searched through a couple hundred websites while searching on google and have found little to no info. Thanks in advance for any help. AndyHi,There is a paper on integrating Derby with C-JDBC (Clustered Jdbc) at:http://db.apache.org/derby/integrate/index.htmlThe C-JDBC info can be found at - http://c-jdbc.objectweb.org/Hope the above helps.Regards,Rajesh
