Re: Record not found in some SQL - Bug?
Have you tried to run the Derby consistency checker? (See http://wiki.apache.org/db-derby/DatabaseConsistencyCheck). Maybe that could give some clue about what is wrong. -- Øystein jrgchip wrote: I have reported the problem as https://issues.apache.org/jira/browse/DERBY-4032. The problem is reproducible given the current state of the DB. However, if I reload the data, the problem goes away. So I conclude that use of the DB has altered its state in some way as to cause this problem to arise. I do not know if it is a single row that has the problem. The Java app failed when it processed this record (because the record was not found) and my investigation into the failure led me to discovery of this anomaly. There may be others. I have had two very similar failures over the last 3 months which I did not investigate as deeply...instead rebuilding the DB from scratch. So it appears the problem does arise occasionally...at least in 10.3.2.1. It's clear the SQL returns incorrect results. I am hoping someone else has seen similar behavior and can point me to a solution. jrgchip wrote: I have a Product table with a Num column that contains a record that is only accessible by some SQL and not others.
Re: performance issue
Jonas Ahlinder wrote: The benchmark client is single-threaded atm. To run it multi-threaded some sort of locking will most likely have ot me implemented ( which will be done as soon as we can confirmt he performance is ok ). I have tried running more threads, and it does seem to give better performance, but the current state of the client doesnt really allow for reliable testresults. With autocommit on, and with the disk running 100% usage ( and quite a bit of wait queue at least on Linux ) do you think multiple threads will really help ? And CPU ( 4 cores ) seem to run about 50% wait and 50% idle, which seems rather wierd to me, but i guess its mostly waiting for IO. Which disk is running at 100% usage? The data disk or the log disk? If it is the log disk that is saturated, having multiple threads may help throughput because then it will be possible to commit multiple transactions per disk write. -- Øystein From: Bryan Pendleton [EMAIL PROTECTED] Sent: Thursday, October 16, 2008 5:43 PM To: Derby Discussion Subject: Re: performance issue The first issue is that on a desktop machine ( running vista ) with two 7.2k rpm sata disks I get over 900 tps, while on a server ( running RHEL 5 ) and two 15k rpm sas disks, I get around 250 tps. Is your benchmark client multi-threaded? Or single-threaded? During the run(s) are your machine(s) CPU-bound? Or disk-bound? thanks, bryan
Re: Cant update my database anymore
Jamel Meslamani wrote: Dear all, I am using Derby10..3 with a chemical program JChem to update my database. But now, I am facing this problem when I try to add more structures. Error that I get is : ERROR 25502: An SQL data change is not permitted for a read-only connection, user or database. With ij, I've tried these commands but no changes: ij connect 'jdbc:derby://MyLocalhost:1527/MyChemicalDB;'; ij CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY( 'derby.database.sqlAuthorization','true'); ERREUR 25502 : Une modification des donnees SQL n'est pas autorisee pour une connexion, un utilisateur ou une base de donnees en lecture seule. ij CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY( 'derby.database.fullAccessUsers','test'); ERREUR 25502 : Une modification des donnees SQL n'est pas autorisee pour une connexion, un utilisateur ou une base de donnees en lecture seule. How Can I modify the database rights, to be able to update it? I think full disk will give this symptom. Please, check derby.log to see if it contains any warning about setting the database in read-only mode. -- Øystein
Re: Seg file not updating?
Curry, David CIV NAVAIR 41K300D wrote: Vista, NetBeans 6.0, using embedded Derby driver. I have 2 apps that share a database. admin.jar is the admin app, demo.jar is a simple client that cannot change data in the database. Both apps are in the same folder, along with the database folder. When I make data changes to the db using admin, and close admin and run demo, I can see the data changes, no problem. But when I look at seg\files, none of them have had their modified/accessed timestamps updated. The log file does have the timestamp updated. When I examine the seg\file that I know should have the new data, it does not, but the log file does have the new data. Have you made sure to properly shut down the database before exiting the admin app? Before exiting the application that embeds Derby, you should connect to it with connection parameter 'shutdown=true'. E.g.: DriverManager.getConnection(jdbc:derby:;shutdown=true); Derby will as part of the shutdown force the database buffer to disk. -- Øystein
Re: Speed of using Derby DB
vodarus vodarus wrote: it takes up to 12 seconds to calculate TESTTOTALS. Oracle PL/SQL procedure with the same algorithm need 1,5 second. *How can i improve performance? Or Derby is so slow because of Java / JVM issues???* Thanks. I do not have any experience with performance of stored procedures, but note that Derby has a pretty high overhead for query compilation since it compiles queries directly into Java byte code. Hence, comparison on single executions of statements will usual not be in favor of Derby. However, if same statements are executed many times efficiency improves (both due to reuse of compiled plans and hot-spot compilation of the Java code). For you particular problem, why use stored procedures when you can do the same with the following query: insert into testtotals select client, sum(order_amount) from testbig group by client; -- Øystein
Re: Speed of using Derby DB
And what tools can you use to analyse big amount of relation-data? SQL can not be used in many cases. So people usually use stored procedures in RDBMS - PL/SQL in Oracle. I have idea: replase Oracle with Java application. And that Java application have RDBMS inside (like Derby in Embedded mode). If you are using Derby in embedded mode, I do not think there will be a performance advantage of using a stored procedure over executing the code directly in the application. It may even be faster to skip the stored procedure. It would be interesting if you could try it out and report the results. PS does derby / javaDB have it's own forum? Do you mean outside this mailing list? -- Øystein
Re: Speed of using Derby DB
vodarus vodarus wrote: I thought tt using stored procedure will avoid transferring data from DataEngine to Application. That cost is very little when using embedded where both are executing in the same VM. Using Java + Oracle will be slower than usage of PL/SQL code. But Java + Oracle implies client/server and transfer of data between different processes. Application will spend a lot of resources to transfer data from DBMS to external code and back. Do you mean usage of Java application itself and avoid usage of stored procedure? Yes. PS does derby / javaDB have it's own forum? Do you mean outside this mailing list? Yes. I think forum is more visual tool for communication than mail-list. (I would think that depends on your mail client.) Nabble and several other sites provide a way to access the mailing list in as a forum. http://www.nabble.com/Apache-Derby-Users-f93.html -- Øystein
Re: Database connection not bound to default database schema when authenticating through LDAP?
Andreas Kyrmegalos wrote: Hello again, I don't know what to make of this. I blame it to the late hour though. After successfully authenticating user usertest through LDAP to establish a connection , I get this message ERROR 42Y07: Schema 'usertest' does not exist when executing a statement against the database. The database's schema is not 'usertest'. And the database creator is not 'usertest' either.The same error is reported with other user names. I was under the impression that a database connection is bound to the default database schema regardless of the user name used to connect. Is this a LDAP related thing? Or is it just the right time to hit the sack? The default schema is the same as the username, regardless of whether it exists or not. You can use 'set schema' to switch to another schema. -- Øystein
Re: ShutdownException when I haven't asked the database to shut down
Daniel Noll wrote: Hi all. I'm getting a load of errors like this. They seem to happen at random times; sometimes I see it when creating a statement, sometimes when executing, sometimes when iterating over a result set. Once I even had it happen when creating the connection itself, which is hard to believe. Caused by: java.sql.SQLException: Java exception: ': org.apache.derby.iapi.error.ShutdownException'. at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(SQLExceptionFactory.java:45) at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Util.java:87) at org.apache.derby.impl.jdbc.Util.javaException(Util.java:244) at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(TransactionResourceImpl.java:403) at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(TransactionResourceImpl.java:346) at org.apache.derby.impl.jdbc.EmbedConnection.handleException(EmbedConnection.java:2125) at org.apache.derby.impl.jdbc.ConnectionChild.handleException(ConnectionChild.java:81) at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(EmbedStatement.java:1325) at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeStatement(EmbedPreparedStatement.java:1652) at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeQuery(EmbedPreparedStatement.java:277) Does nextException for this exception give you any more information? Have you checked derby.log? Maybe it contains information that could shed light on this. -- Øystein
Re: Null Pointer ? any idea how to solve
Templexp Tan wrote: Hi, There are some problem like the following: java.sql.SQLException: DERBY SQL error: SQLCODE: -1, SQLSTATE: XJ001, SQLERRMC: java.lang.NullPointerException#20;#20;XJ001.U at org.apache.derby.client.am.SQLExceptionFactory40.getSQLException(Unknown Source) at org.apache.derby.client.am.SqlException.getSQLException(Unknown Source) at org.apache.derby.client.am.Statement.executeQuery(Unknown Source) any idea how it happens? More information is needed to tell the origin of the NullPointerException. If you could print the entire exception chain, this could give more information. Also, if the NPE occurs on the server, there is probably a stack trace in derby.log that could tell a bit more. -- Øystein
Re: Derby get-together at Java One
Rick Hillegas wrote: A number of Derby people have expressed interest in getting together the week of Java One. I'm thinking that people may be busy attending sessions during Java One itself, so it might be better to meet the first day before the Java One sessions start. That would be the Community One day (Monday, May 5). However, that may be an awkward day for some people for other reasons. I'm leaning toward Monday May 5. I'm also leaning toward a bring-your-own-lunch format as we've done before with Derby developer get-togethers. Let me know if lunchtime (say 11:30 onward) that day sounds good to you. Thanks, for organizing this, Rick. I plan to be there. -- Øystein
Re: A network protocol error was encountered and the connection has been terminated
Vic Ricker wrote: Sorry about that. I added something to dump follow getNextException() and getCause(). I got the following exception from getCause(), I believe. I don't think getNextException() returned anything. org.apache.derby.client.am.DisconnectException: A network protocol error was encountered and the connection has been terminated: the requested command encountered an unarchitected and implementation-specific condition for which there was no architected message org.apache.derby.client.net.NetConnectionReply.parseCMDCHKRM(NetConnectionReply.java:888) Have you checked the derby.log file? If the server has detected any error, it should be reported in this file. -- Øystein
Re: limit, offset and random
unludo wrote: OK thanks a lot for your answers. Regarding the 'ORDER BY RANDOM()', you also plan to have something similar? ORDER BY RANDOM() already works for me. Example: ij select i from t where i 5; I --- 1 2 3 4 4 rows selected ij select i from t where i 5 order by random(); I --- 1 2 4 3 4 rows selected ij -- Øystein
Re: Re-using an existing db and archiving data
Mark Hiles wrote: I've just started using Derby for a uni project and have a couple of quick questions. When creating an embedded database, it seems to get overridden each time I run my application. How can I make it create a database the first time it runs, and then re-use it in the future? This is actually what will happen if your connection URL contains create=true. If the DB already exists, you will get a connection to the existing DB. I'd also like to know if it's possible to select certain data for archiving / compression? I've searched for material but can't find anything. It is possible to encrypt data on disk, but I do not think compression is supported. -- Øystein
Re: Re-using an existing db and archiving data
Mark Hiles wrote: I've been using create=true and it just keeps overriding the existing db.. Someone mentioned to me that it might have something to do with the fact that I'm running an embedded version.. I'm not sure why that would matter but could that be it? What do you mean by overriding? Are tables that you have created not there when you start it the next time? That is not how it is supposed to be, regardless of whether you are using embedded or client/server. -- Øystein
Re: Derby, identity columns locks on syscolumns
Andrew Lawrenson wrote: I've done some more experimentation testing. At the moment, when syscolumns is updated, if a sub-transction is done, the update is done with an expicit no-wait on locks. I've tried changing this so that it will use the same wait policy as the parent transaction - when this is done, I see none of the problems reported, and can have up to 100 concurrent threads inserting without any failing (whereas before this would instantly lock up). So the question now is: is the no-wait for the sub-transaction actually necessary?. Personally, I can't see why it is, although I'm not exactly a guru at derby internals. If the reason why is simply to increase concurrency, then I think it's flawed, as it forces more updates to be carried out by the parent transaction, which will hold the lock much longer before committing... Any ideas? Or is this the wrong list to be asking - should I pose this on derby-developers instead? I think derby-dev is more appropriate for this discussion. I do not know why there is a no-wait for subtransactions, maybe it is done to avoid risks of deadlocks. You could try running the Derby test suites to see if some problems are revealed. -- Øystein
Re: using SELECT as an SQL triggered action
musky wrote: i will make my aim more clearer here: i have created an AFTER insert trigger on a table called chattable which has two fields(ID INT,Message VARCHAR(255)).here is the trigger: CREATE TRIGGER insert_trigger AFTER INSERT ON chattable REFERENCING NEW_TABLE AS chattableupdate FOR EACH STATEMENT SELECT * FROM chattableupdate where chattableupdate is a transition table. i want the transition table to contain the newly inserted rows whenever an insert takes place into chattable. please help me,because with this trigger,when i select * from chattableupdate and assign it to a resultset i get an error that the chattable view/table does not exist I do not think doing a select in a trigger does not make sense. JDBC does not provide you with any way to get hold of the result set. From your description, I think you need an insert statement in your trigger, not a select statement. -- Øystein
Re: using SELECT as an SQL triggered action
Øystein Grøvlen wrote: I do not think doing a select in a trigger does not make sense. Sorry, to many negations here. I mean of course that doing a select in a trigger does not make sense. JDBC does not provide you with any way to get hold of the result set. From your description, I think you need an insert statement in your trigger, not a select statement. -- Øystein
Re: Iterating through large result set in network mode causes OutOfMemoryException
Briggs wrote: Well, unfortunately, freeing the Blob didn't work, the app still runs out of memory. The app? Your first posting indicated that the server ran out of memory. Also, the occasional commit is also causing a problem. I end up eventually getting this: java.sql.SQLException: The data in this BLOB or CLOB is no longer available. The BLOB/CLOB's transaction may be committed, or its connection is closed. A Blob/Clob object is not valid after the transaction it was created in has committed. -- Øystein
Re: Failed to start database
tom_ wrote: I have tried with the latest bits/trunk, here the problem is an exception upgrade from 10.3 to 10.4 is not supported. Have you followed my advice about setting the derby.database.allowPreReleaseUpgrade property? -- Øystein
Re: Newbie Blues : Unable to connect to derby database using JDBC
Aneez, Can you provide the call stack for the exception you get? -- Øystein Aneez Backer wrote: Hi Am trying to connect to derby database, but have not been successful. I have created a database called 24k , and have also populated the tables Here's the code: - org.apache.derby.jdbc.EmbeddedSimpleDataSource ds = null; Connection conn = null; Properties props = new Properties(); props.put(user, username); props.put(, ); Class.forName(driver).newInstance(); System.out.println(Loaded the appropriate driver.); conn = DriverManager.getConnection(protocol +24k;, props); System.out.println(Connected to database 24k); conn.setAutoCommit(false); Statement s = conn.createStatement(); ResultSet rs = s.executeQuery(SELECT firstname, lastname FROM USERS WHERE uid = 1001); --- --- ERROR --- Loaded the appropriate driver. Connected to database 24k exception thrown: java.sql.SQLDataException: Invalid character string format for type int. --- Please guide Thanks Aneez Get easy, one-click access to your favorites. Make Yahoo! your homepage. http://us.rd.yahoo.com/evt=51443/*http://www.yahoo.com/r/hs
Re: Network protocol error in prepareStatement()
Daniel Noll wrote: Hi all. I'm getting this unusual exception during prepareStatement(): Caused by: org.apache.derby.client.am.DisconnectException: A network protocol error was encountered and the connection has been terminated: the requested command encountered an unarchitected and implementation-specific condition for which there was no architected message at org.apache.derby.client.net.NetConnectionReply.parseCMDCHKRM(Unknown Source) at org.apache.derby.client.net.NetStatementReply.parsePrepareError(Unknown Source) at org.apache.derby.client.net.NetStatementReply.parsePRPSQLSTTreply(Unknown Source) at org.apache.derby.client.net.NetStatementReply.readPrepareDescribeOutput(Unknown Source) at org.apache.derby.client.net.StatementReply.readPrepareDescribeOutput(Unknown Source) at org.apache.derby.client.net.NetStatement.readPrepareDescribeOutput_(Unknown Source) at org.apache.derby.client.am.Statement.readPrepareDescribeOutput(Unknown Source) at org.apache.derby.client.am.PreparedStatement.readPrepareDescribeInputOutput(Unknown Source) at org.apache.derby.client.am.PreparedStatement.flowPrepareDescribeInputOutput(Unknown Source) at org.apache.derby.client.am.PreparedStatement.prepare(Unknown Source) at org.apache.derby.client.am.Connection.prepareStatementX(Unknown Source) The connection is from the host to itself so network issues should hopefully be out of the picture. Any idea what causes this error? The derby log is completely empty this time (no error messages, no success messages either.) The call stack indicates that the server has sent and CMDCHKRM command which I should a reply indicating that the client, as part of the prepare message, sent something it was not able to understand. I looked at the Network Server code, and I could not find that it would necessarily write something if an error happens during prepare, regardless of what debug flags you might turn on. Hence, I think the best bet is to turn on tracing on the client and/or serve to see if we can see something wrong with the message being sent. -- Øystein
Re: having a problem with not in
Jørgen Løland wrote: tyoup wrote: i'm having problems with the following query select a,b,c from ta where (b,c) not in (select bb,cc from tb where dd = 3) wich won't work in derby how can i work this around ? Hi Tyoup If I remember correctly, IN clauses can only take handle one attribute. I think you'll get what you want if you rewrite the query to: select a,b,c from ta where not (b in (select bb from tb where dd=3) and c in (select cc from tb where dd=3)); I do not think this is right since it is ok that the b and c values exists in different rows of tb, but not in the same row. I think the two queries below should work: select a,b,c from ta where not exists (select * from tb where dd=3 and b=bb and c=cc) select a, b, c from ta except select a, b, c from ta, tb where dd=3 and b = bb and c = cc -- Øystein
Re: Problem with valueSpecification() grammar rule
Dhananjay Muli wrote: Hello, I am facing problem with a query in postgresql having following condition in where clause bullyear=(acadyear-year+1) where acadyear, bullyear and year are columns of table. The existing sqlgrammar.jj file has a problem parsing the 'year' token occurence after the '-' sign. It expects a literal value there. The exception while parsing the query is given at the end. I modified valueSpecification() rule to add a new rule (the first one in the two rules given below) so that grammar should also allow column reference along with literal values. Maybe your problem is that YEAR is a reserved word in Derby. Hence, it should not be used for the name of a column. However, it seems strange that you have been able to use a reserved word for the name of a column. -- Øystein
Re: Hibernate: table/view does not exist
Mathias Conradt wrote: Thanks for the hint, the schema is app. It seems that when I use ij (EmbeddedMode) to connect to jdbc:derby:realty;(create=true) and use hibernate, it creates/connects the database in two different physical location. When I use ij, the database folder is created under C:\server\javadb10.3.1.4\bin, but when I use Hibernate, it must be somewhere else, at least I cannot find it under C:\server\javadb10.3.1.4\bin. Note that if you do not specify yourself where the database is to be stored, it will be stored in current directory. I am not a Windows expert, but from your experience it seems like the current diretory may be the directory where the executable is located. You can take control of where the database is located in at least two ways: 1. Set the property derby.system.home to the directory where you want your database. 2. Explicitly state the location of your database when connecting: URL: jdbc:derby:C:/server/javadb10.3.1.4/bin/realty But now I found a solution, using the Server Mode instead of the Embedded Mode, which somehow makes sure that I'm using the same (physical) database. property name=driverClassvalueorg.apache.derby.jdbc.ClientDriver/value/property property name=jdbcUrlvaluejdbc:derby://localhost:1527/realty;create=true/value/property The server mode works because the default directory when starting the server will be used regardless of which client is connecting to it. However, there should be no need to use the client/server mode if you do not need to support concurrent connections from multiple clients. Hope this helps, Øystein
Re: SYSCS_GET_DATABASE_PROPERTY not working?
David Van Couvering wrote: OK, fair enough. So, how *do* I find out what derby.system.home is set to?? I would think it is the same way as with any Java System property (Kathey implicity told you that): System.getProperty(derby.system.home); I have not tried it, but it is possible that if this property is not explicitly set, this will also return null since Derby default values are not normally registered as properties. (There was a discussion about this a couple of months ago.) -- Øystein
Re: Why need JDBC driver?
jiangshachina wrote: Hello, The question may be so stupid, but I really have some puzzles. Generally, JDBC is the connector between Java application and RDBMSs. But I think that's because the RDBMSs aren't written by Java, then we need the middleware. Now that, Derby is pure Java application, why we still need JDBC driver? I think Java application certainly can tie with Derby directly. And if you use embedded Derby it actually does. In that case, the JDBC calls tie directly into the Derby database engine. The JDBC API is used because it is a standard API for accessing a database from Java. In the client/server environment you will still need a JDBC driver which handles the communication over the network. -- Øystein
Re: [ANNOUNCE] Apache Derby 10.3.1.4 released
Rahul Dwivedi wrote: Are there any plans for implementing such a feature where lob locators are stored in tables and LOB are stored some where else, or such similar functionality to enhance performance with multiple lobs in single table. I do not think that anyone has so far indicated that they have any plans for this. However, I would very much like to understand better what you are requesting. If I understand you correctly, your main objective is that if LOBs are stored outside the table, a table scan will be much quicker. Is that what you are thinking of? Others have asked for locators in order to save space if two columns are referring the same LOB. That raises the question of what should happen if a LOB is updated (e.g., through JDBC by Blob.setBytes). Should that update be reflected by all columns referring the Blob, or just the column being updated. -- Øystein
Re: SQLTransientConnectionException with BLOB reads
Arthur Blake wrote: Hello, I have been using Apache Derby 10.2.2.0 for a few months, and it has been working quite well for me. I upgrade to 10.3.1.4 just after the release came out, and I have been testing it out with my application. It seems to work fine, except when I read some BLOB fields from the database, this Exception is thrown: java.sql.SQLTransientConnectionException: No current connection. at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source) at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Unknown Source) at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Unknown Source) at org.apache.derby.impl.jdbc.Util.noCurrentConnection(Unknown Source) at org.apache.derby.impl.jdbc.EmbedConnection.checkIfClosed(Unknown Source) at org.apache.derby.impl.jdbc.EmbedBlob.checkValidity(Unknown Source) at org.apache.derby.impl.jdbc.EmbedBlob.getBytes(Unknown Source) .. Are you accessing the Blob after the Connection from which is was obtained has been closed? As described in the release notes for 10.3, it now required that the Connection is still open in order to access the Blob. In 10.2, small Blobs may be accessed after the connection is closed, but not larger Blobs. Please, check whether the Connection should still be open when the failure occurs. If not, is it possible for you to change your application to keep the connection open as long as needed? -- Øystein
Re: SQLTransientConnectionException with BLOB reads
Arthur Blake wrote: Yes, that was the problem. If I change my code to keep the Connection open after I have a Blob in hand, and then read the Blob it works. It appears that also the ResultSet and PreparedStatement I used to fetch the Blob must also remain open until I'm done reading the Blob. Generally, you should be able to close result sets and statements and still be able to access the Blob. However, if you run with autocommit on (default), closing a result set may commit the transaction, and Blobs may not be accessed after the transaction has been committed. That seems reasonable. Sorry that I didn't see that in the release notes (they are quite big) as I didn't even realize I was doing this in the code... (my Lob reading code was buried in some utility classes.) I believe the Release note entry for this is DERBY-2729 . Paraphrasing: Now Blobs and Clobs store their data in temporary files... Can I assume that Blob reads (especially for smaller Blobs) will be slower now because they will have to be copied to temp files? The temporary files will only be created if you use the JDBC Blob methods to change the content of a blob (e.g., setBytes). For the embedded driver, these methods were added in 10.3. For methods existing in 10.2, I do not think the performance has changed much. (For the client driver, we have switched to a locator-based inmplementation and some operations should be much faster, while a few will be probably be slightly slower.) -- Øystein
Re: ERROR XSAI2: The conglomerate requested does not exist.
I observed this error a few times during some experiments I did some time ago. See DERBY-637. Not the same setting, though. Client/server, 30 GB database. When this started to occur, all my transaction fail with this error. Restarting the database, made the error disappear. -- Øystein
Re: Derby select query speed questions
Adam Bovill wrote: Hi Olav, Thanks. That seems to have improved things. I was wondering whether or not there was a way to create one PreparedStatement from another. I have the first one that I've created and would like to clone or duplicate this, w/o needing to recompile it. So when I'm using a PreparedStatement, I can set the parameters w/o incurring too much of a penalty because it's already compiled? In order to answer this it would be good to know a bit more about what you are trying to achieve. Why do need several prepared statements? Note that a prepared statement is local to a connection. If you want to execute the same statement in another connection, you will have to prepare it for that connection. However, Derby has a statement cache so if the statement is lexically equal to a previous statement, the result of the previous compilation will be reused, and recompilation will not be needed. -- Øystein
Re: [n00b] Recovering from program crashes
John C. Turnbull wrote: I have just started to use Derby for configuration data for an application and have found that whenever the app terminates unexpectedly then Derby will not start the next time I start the application as it complains that it’s already been opened. At the moment, I need to reboot my machine to clear whatever flag/data is indicating that the database is still open. Is there a cleaner way to close the database? Ideally my app would try to start Derby and if it gets the error about it already being open then it would somehow clean up and close the database (that is if the application is not actually running in another instance already) and then open it properly. Normally the lock file used to lock the database for concurrent access by other applications, should be cleaned up automatically, but I seem to recall that there have earlier been some discussions about platform differences in this area. What error do you get, exactly? Which platform (OS and JVM version) are you running on? -- Øystein
Re: Can I pass an array to a stored procedure?
Brown, Andrew W (Rosetta) wrote: After reading through the documentation it doesn't seem like passing an array to a stored procedure is possible: Derby does not support the SQL ARRAY type. So it will not be possible to pass arrays as stored procedures. -- Øystein
Re: Bug in Embedded Driver?
Xavier Hanin wrote: con.createStatement().executeUpdate(create table Issue ( ID char(50) )); PreparedStatement st = con.prepareStatement(select count(*) from Issue where ? is null); st.setNull(1, Types.VARBINARY); I do not think that CHAR and VARBINARY are compatible types. Try using Types.VARCHAR instead. In this case I think it is the client driver that is wrong. It is not first example where it allows type conversions that are not according to the spec. (Ref. https://issues.apache.org/jira/browse/DERBY-1501) -- Øystein
Re: Bug in Embedded Driver?
Xavier Hanin wrote: On 3/31/07, *Øystein Grøvlen* [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: Xavier Hanin wrote: con.createStatement().executeUpdate(create table Issue ( ID char(50) )); PreparedStatement st = con.prepareStatement(select count(*) from Issue where ? is null); st.setNull(1, Types.VARBINARY); I do not think that CHAR and VARBINARY are compatible types. Try using Types.VARCHAR instead. Yes, but why does derby consider the type as CHAR? Is it something from the specification to assume CHAR when no type can be assumed from the query? Note that I do not compare to the only column I've created. The column could have been compatible with VARBINARY. Ah, I see. My mistake. Then I do not see a reason why this should not be allowed. Which version of Derby are you using? There have been some fixes in this area lately (see DERBY-1610), and I will try your test case on the development trunk later today. Indeed in my usecase I can't use VARCHAR because I'm not at the origin of the query. I use hibernate and do something like this: from Issue where :status is null or status = :status Hibernate converts that to something like select * from Issue where ? is null or status_id = ? and bind the two parameters with the same value, i.e. the id of the status I bind in Hibernate, which is a BIGINT, and thus use VARBINARY as sql type for both parameters binding. And I can't really blame Hibernate, can I? Why not? :-) I would blame Hibernate for using VARBINARY when it should use BIGINT, but I guess that is not the issue here. -- Øystein
Re: out of memory when writing blobs
For tables with Blob or Clob columns, the default page size for the will be 32 kB. (Ref. http://db.apache.org/derby/docs/dev/tuning/ctunperf816059.html ) -- Øystein Anders Morken wrote: Anders Morken: [...] the page size (Seemed to be 32K in this case [...] Aha. Those 32 kilobyte pages were overflow pages, created to hold rows that won't fit on a regular page. The regular pages were 4 kilobytes, the default size, but obviously those 6 megabyte blobs had to be split across a few overflow pages. =) That explains why blob workloads suddenly consume a lot more memory than regular workloads with few (if any) overflow pages. Maybe this is common knowledge, but I just hadn't realized. =) Sorry for the noise, just wanted to clear up (my own) confusion. =)
Re: Derby row locking semantic
Alex Boisvert wrote: Hi, I stumbled upon an interesting locking behavior in Derby 10.2.2.0 http://10.2.2.0 yesterday and thought I'd post to the list to get a better understanding of what's happening under the covers. Assume a table with two indexes: create table MY_TABLE ( ID bigint, FOO varchar(255), BAR varchar(255), primary key (ID) ); create index IDX_FOO on MY_TABLE (FOO); create index IDX_BAR on MY_TABLE (BAR); With isolation level READ_COMMITTED, here's a simple concurrent scenario: CLIENT_A: start transaction CLIENT_A: insert into MY_TABLE values (1, 'FOO-1', 'BAR'); === OK (1 row inserted) CLIENT_B: start transaction CLIENT_B: insert into MY_TABLE values (2, 'FOO-2', 'BAR'); === OK (1 row inserted) CLIENT_B: select * from MY_TABLE where FOO = 'FOO-2' === OK (1 row matched) CLIENT_B: select * from MY_TABLE where FOO = 'FOO-2' and BAR = 'BAR' === OK (1 row matched) CLIENT_B: select * from MY_TABLE where BAR = 'BAR' and FOO = 'FOO-2' === OK (1 row matched) CLIENT_B: select * from MY_TABLE where BAR = 'BAR' === BLOCKS! at that point CLIENT_B blocks until CLIENT_A commits or rollback. And if CLIENT_A also selects BAR = 'BAR' we get into a deadlock situation. What I don't understand is why CLIENT_B blocks to obtain a read lock on a row that has not been committed by CLIENT_A. Would anyone be able to explain the underlying mechanism at work here? I think most database systems that do not have multiversion concurrency will behave as describe. When a scan encounters a exclusively locked row, it will generally not know whether the uncommitted operation is an insert or an update. For the latter, the row cannot be skipped since its previous version should be included in the result set if the transaction that updated the row is rolled back. -- Øystein
Re: renaming columns
Ralf Wiebicke wrote: Hi! I try to rename a column using the following statements: create table t (a int) rename column t.a to b The second statement throws an exception: [Error Code: 3, SQL State: 42X01] Syntax error: Encountered column at ine 1, column 8. Although this feature is not yet documented, according to this bug entry: http://issues.apache.org/jira/browse/DERBY-1490 this should work since subversion revision 472708. I'm using version 10.2.2.0 - (485682) which is later. When I look at the diff for revision 472708, this seems to be, what I want. Any ideas? The release note for 10.2 does not mention that rename column has been added. Note that 10.2.2 is made from another svn branch than the development branch (trunk). Revision numbers on different branches are not directly comparable. -- Øystein
Re: SQLSTATE: XSDG0Page Page(7,Container(0, 848)) could not be read from disk.
David Sitsky wrote: Some causes in the past are: o DERBY-700 o DERBY-1838 o using durability=test mode o running on hardware that doesn't actually sync disk when asked o running separate jvm's on 2 machines accessing the same db across a networked file system. This is running on windows, we never set durability=test, so I highly suspect the last point must have occurred at this site. How about the second to last point? Most windows machines by default is set up to cache writes in the disk controller. If the machine goes down in an uncrontrolled manner, disk writes may get lost. Could this have happened? To be safe, one needs to turn off the disk caching. -- Øystein
Re: GROUP_BY with user functions
Robert Enyedi wrote: If I need to group the values returned by the MY_USER_FUNCTION, I simply cannot do so because the following query is invalid in Derby: SELECT MY_USER_FUNCTION(t1.field1) AS MY_VALUE FROM T1 GROUP BY MY_VALUE I have not tried this, but maybe something like this will work: SELECT UserFunction.value AS my_value FROM (SELECT MY_USER_FUNCTION(t1.field1) AS value FROM T1) AS UserFunction GROUP BY my_value -- Øystein
Re: GROUP_BY with user functions
Robert Enyedi wrote: Thanks for the tip. I tried this, but it has the same problem with alias referencing. Sorry, I should have tried this first. Fernanda has given you the right solution. -- Øystein
Re: JMX Extensions to Derby
Sanket Sharma wrote: I would also appreciate your suggestions on features the community would like to see being implemented as JMX extensions. On the top of my head: - Performancs statistics (e.g., transactions committed/aborted per second) - Change dynamic properties (e.g., derby.storage.pageSize) - Stop a network server (would require some kind of authorization) A question: How will JMX work in an embedded environment? Will it be possible to connect from another process? If yes, if yes that introduces security issues that one today does need to address for an embedded configuration. -- Øystein
Re: ERROR 25502: An SQL data change is not permitted for a read-only connection, user or database.
Thomas J. Taylor wrote: Hi There, I have a Derby database that is giving me a read-only error (25502), and I don't know why. The database was working fine, up until about a month ago. Now, I cannot INSERT, UPDATE, or DELETE from the database - I always get ERROR 25502. I checked Windows Security permissions (user has Full Control: READ/MODIFY/WRITE/etc.), File attributes (all files in the database directory are writable); finally, I deleted the 'db.lck' file that is in the database directory. Derby may set a database to be read-only if something goes wrong during the booting of the database (e.g., not able to create db.lck file). Is there any message written to derby.log during the booting of the system? It might be that you will get more information in derby.log if you run with on a recent build instead of a released version. (I guess it would be a good idea to try this on a copy of your database). I added more output to derby.log for this case in DERBY-555. -- Øystein
Re: ERROR 40XD1:container was opened in read-only mode
princy wrote: i have been using derby it worked well until now does not accept insert,create queries.but select query works well. the error which occurs is ERROR 40XD1:Container was opened in read-only mode Have you checked the derby.log file? You may run into this problem if your disk is full. -- Øystein Grøvlen, Senior Staff Engineer Sun Microsystems, Database Technology Group Trondheim, Norway
Re: clustered index
Thomas Vatter wrote: A clustered index should be the fastest access for retrieving ordered columns. 'Create index' command does not yet support it. Since ordering is my main problem at the moment I post this as request for enhancement. What do you mean by a clustered index? It usually means an index on primary key where records are stored witin the index, but I am not quite sure that is what you mean since one would not use 'create index' for a clustered index. Do you a composite index? I thought Derby supported that. -- Øystein
Re: clustered index
tom wrote: I mean an index that kept up to date on disk permanently. This is the definition that I know from the time I was using Informix Online. The syntax was 'create clustered index ...'. I don't mean to put it on the primary key as SQL-Server does, but having the possibility to create one would be helpful. I would think any index would be kept up to date on disk permanently. -- Øystein
Re: [DERBY-39] An ON clause associated with a JOIN operator is not valid
[EMAIL PROTECTED] wrote: Hi, This is issue DERBY-39, can someone give an opinion on this? It works in MSSQL and DB2 SELECT UNBOUND_P.PROJID FROM applicationidentity0.PERSONS THIS, applicationidentity0.PROJECTS UNBOUND_P WHERE EXISTS (SELECT 1 FROM applicationidentity0.PROJECT_MEMBER THIS_PROJECTS_P LEFT OUTER JOIN applicationidentity0.PROJECTS UNBOUND_P_NAME ON UNBOUND_P.PROJID = UNBOUND_P_NAME.PROJID WHERE THIS_PROJECTS_P.MEMBER = THIS.PERSONID AND THIS_PROJECTS_P.MEMBER = THIS.PERSONID AND UNBOUND_P.PROJID = THIS_PROJECTS_P.PROJID AND UNBOUND_P_NAME.NAME = ? AND (THIS.DISCRIMINATOR = ? OR THIS.DISCRIMINATOR = ? OR THIS.DISCRIMINATOR = ?)) I'll admit outer joins are not my area of expertise, but could you explain what you are trying to achieve here? You are doing a left outer join on two tables and specifies an ON-clause that does only refer to one of these tables. So what makes this an outer join? Is seems to me to be a cartesian product. Why not just move the predicate in the ON-clause to the WHERE-clause? Or am I missing something? -- Øystein
Re: What is wrong with this sql script , im sure that it is ok , but derby return error.
LW == Legolas Woodland [EMAIL PROTECTED] writes: LW Hi LW Thank you for reading my post. LW can some one please check and see what is wrong with this scripts ? LW im sure that they should execute but derby return errors like : LW org.apache.derby.client.am.SqlException:Constraints LW 'SQL060103004635123' and 'SQL060103004635121' have the same set of LW columns, which is not allowed. I think this is a usability issues since the error message is not very helpful in order to figure out what is the cause of this error. I have filed a jira issue, DERBY-789, for this. -- Øystein
Re: I need some advice to choose database for an upcomming job
BP == Bryan Pendleton [EMAIL PROTECTED] writes: A Guidelines section starts on slide 19. Slide 24 lists 100-500 updates per second -- but, of course, your actual performance will depend on the complexity of your transactions. BP Is there a simple way that I can observe what actual performance BP I'm getting? That is, does Derby keep its own monitoring statistics BP about the activity that is occuring, and, if so, how do I retrieve BP those statistics and view them? BP I see that there is SYSCS_GET_RUNTIME_STATISTICS, but that seems BP to be mostly about execution of a single query; how do I see BP information like: BP - transactions per second BP - IOs per second (read and write) BP - active users BP - memory usage BP etc. I agree that this would be very useful. -- Øystein
Re: How to update cardinality statistics
I did not get any replies to this email. It would be useful if someone could clarify whether the statistics are automatically updated or not. -- Øystein ØG == Øystein Grøvlen [EMAIL PROTECTED] writes: MM == Mike Matrigali [EMAIL PROTECTED] writes: MM Would need to see the exact documentation reference, but this is not MM the case. The storage system keeps an estimate of the number of MM rows in a table. For performance reasons this estimate is not exact MM (ie. we don't update a single could for every insert/delete/abort MM insert/abort delete/commit). The actual update is tied to the time MM pages move out of the cache. Because the number can drift there is MM an optimization in the language execution engine that if it has just MM executed a complete scan of the table then it updates the value MM in the storage system. MM But in almost all cases the automatic maintained row count maintained MM by store is good enough and never needs to be updated. The scan MM optimization is there to automatically catch the drift if it is MM cheap to do so, it wasn't really meant for users to use to explicitly MM update it (it was take advantage if a user happened to be executing MM a complete scan for some other reason). ØG The tuning guide says: ØG -- ØG As you saw in When cardinality statistics are automatically updated, ØG cardinality statistics are automatically updated only in limited ØG cases. Normal insert, update, and delete statements do not cause the ØG statistics to be updated. This means that statistics can go ØG stale. Stale statistics can slow your system down, because they worsen ØG the accuracy of the optimizer's estimates of selectivity. ØG -- ØG Note especially: Normal insert, update, and delete statements do not ØG cause the statistics to be updated. Is this wrong? ØG The When cardinality statistics are automatically updated section ØG says ØG -- ØG For the following operations that you perform on a table, Derby ØG automatically creates statistics. Those operations are: ØG * (new index only) When you create a new index on an existing ØG non-empty table. ØG * (new backing indexes only) When you add a primary key, unique, ØG or foreign key constraint to an existing non-empty table. ØG For other operations, Derby automatically updates statistics for the ØG table and all indexes on the table if they are already exist. Those ØG operations are: ØG * (all indexes) When you execute SYSCS_UTIL.SYSCS_COMPRESS_TABLE. ØG * (index only) When you drop a column that is part of a table's ØG index; the statistics for the affected index are dropped, and ØG statistics for the other indexes on the table are updated. ØG -- ØG -- ØG Øystein
Re: How to update cardinality statistics
MM == Mike Matrigali [EMAIL PROTECTED] writes: MM Would need to see the exact documentation reference, but this is not MM the case. The storage system keeps an estimate of the number of MM rows in a table. For performance reasons this estimate is not exact MM (ie. we don't update a single could for every insert/delete/abort MM insert/abort delete/commit). The actual update is tied to the time MM pages move out of the cache. Because the number can drift there is MM an optimization in the language execution engine that if it has just MM executed a complete scan of the table then it updates the value MM in the storage system. MM But in almost all cases the automatic maintained row count maintained MM by store is good enough and never needs to be updated. The scan MM optimization is there to automatically catch the drift if it is MM cheap to do so, it wasn't really meant for users to use to explicitly MM update it (it was take advantage if a user happened to be executing MM a complete scan for some other reason). The tuning guide says: -- As you saw in When cardinality statistics are automatically updated, cardinality statistics are automatically updated only in limited cases. Normal insert, update, and delete statements do not cause the statistics to be updated. This means that statistics can go stale. Stale statistics can slow your system down, because they worsen the accuracy of the optimizer's estimates of selectivity. -- Note especially: Normal insert, update, and delete statements do not cause the statistics to be updated. Is this wrong? The When cardinality statistics are automatically updated section says -- For the following operations that you perform on a table, Derby automatically creates statistics. Those operations are: * (new index only) When you create a new index on an existing non-empty table. * (new backing indexes only) When you add a primary key, unique, or foreign key constraint to an existing non-empty table. For other operations, Derby automatically updates statistics for the table and all indexes on the table if they are already exist. Those operations are: * (all indexes) When you execute SYSCS_UTIL.SYSCS_COMPRESS_TABLE. * (index only) When you drop a column that is part of a table's index; the statistics for the affected index are dropped, and statistics for the other indexes on the table are updated. -- -- Øystein
Re: How to update cardinality statistics
MM == Mike Matrigali [EMAIL PROTECTED] writes: MM I 2nd Satheesh's query, it is useful to know why you care. Derby MM unlike most other database's automatically maintains histogram MM type information about the tables (this does require indexes to MM exist). This information is gathered directly from the indexes, MM and is automatically maintained by Derby. MM The row count is also automatically maintained by Derby - whether MM an index exists or not. Sounds good, but I think read somewhere in the Derby documentation that some statistical information was only updated when a query did a sequential scan of the table. -- Øystein
Re: transient priviledgeException
XV == Xavier Vigouroux [EMAIL PROTECTED] writes: XV If I understand you, you propose to improve my call to ping with a XV loop on the creation (ie. url with create=true) of a *FAKE* schema XV until it succeeds. XV Then I have to delete all the associated files. This is what I XV call a work-around :-) Xavier, What if you try to connect to a non-existing database without 'create=true'? In that case, no files will be created. I have not tried this out myself, but maybe you will be able to observe a different behavior when the db is ready to create a database. E.g, you get priviledgeException if not yet ready and Database not found when it is ready. In that case you will be able to loop without having to clean up any files. Another alternative could be to try do this in the server VM instead of/in addition to ping(). If you are lucky, your getConnection will in that case not return until it is possible to create a database. -- Øystein
Re: transient priviledgeException
XV == Xavier Vigouroux [EMAIL PROTECTED] writes: XV Hi, XV I have a transient priviledgeException when connection to the DB with XV ij. XV here is the scenario: XV 1/ I start an embeddedServer XV 2/ wait for the ping() be ok (tested in the JVM creating the server) XV 3/ start ij to create a schema. XV then in 4, I get a transient error...i.e if I retry it works. XV what should I wait for to be sure the server is ready to recieve cmd? I checked the implementation of ping(), and it seems to only check that it is able to get in touch with the network server. It does not try to get a connection to a database. Does anyone have any suggestion for how Xavier can determine that the server is ready to create a database? -- Øystein
Re: cant' get user defined function to work
EB == Erik Bengtson [EMAIL PROTECTED] writes: EB Dan, EB That doesn't help. I already tried variations of the same function, using EB the same code. EB While using any java.lang.Math or other EB java.lang.Class.operation, derby was able to retrieve and run EB the function. Where should I send the code to reproduce it? Have you made sure that the function can be found from your classpath? -- Øystein
Re: Locks not released on error
LC == Lars Clausen [EMAIL PROTECTED] writes: LC Hi! LC We're using Derby version 10.1 (Bundle-Version: 10.1.100.208786) LC embeddedly in our system. Even though I've put extensive rollback and LC statement closing handling in the code, we still occasionally see cases LC where a table gets permanently locked after an error during update LC (detail below). Is there something other than closing open statements LC and rolling back existing savepoints that could be required for LC releasing locks? Am I doing the closing/rolling back wrongly? Any LC other ideas on how to cure this would be welcome. Lars, Note that rolling back to savepoints does not roll back the entire transaction. This means that locks set by the transaction are not released. In order to release locks you need to do call Connection.rollback() (without any parameters). -- Øystein
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: embeddedServer vs NetworkServer: any differences in terms of provileges
XV == Xavier Vigouroux [EMAIL PROTECTED] writes: XV hi, XV I used to spawn a networkserver that I access through JDBC. XV I have to change my design to a Embedded Server. XV I have now a PriviledgeActionException (on client side) when opening XV socket to the server XV do you have any clue? Are you starting the network server by setting the derby.drda.startNetworkServer property in the embedded server? Are the client running on another computer than the server? If yes, you need to enable connection from other computers. This is disabled by default for security reasons. You can open for connections from other computers by setting the property derby.drda.host=0.0.0.0. -- Øystein
Re: derby performance and 'order by'
SAD == Suavi Ali Demir [EMAIL PROTECTED] writes: SAD Another little detail about optimization is that SAD Statement.setMaxRows() kind of functions on the JDBC side may SAD not be sufficient since it is called after SQL statement is SAD prepared and returned as an object (after query plan is SAD built). Therefore, it may be necessary to have language SAD syntax to indicate the intention to fetch first 1000 rows SAD only, so that when the query is prepared, this intention can SAD be taken into account. It would be much better if this could be changed at execute-time for an already prepared statement. That is, the same prepared statement could be used regardless of how many rows one is going to fetch. -- Øystein
Re: suppress connection %d message on console
KM == Kathey Marsden [EMAIL PROTECTED] writes: KM Øystein Grøvlen wrote: XV == Xavier Vigouroux [EMAIL PROTECTED] writes: XV hi, XV is there a way to remove the message on console indicating new XV connection to the server ? As far as I can see from the code, these messages will always go to System.out. Please file a JIRA issue if you want this to change. KM You could specify an alternate console PrintWriter with KM NetworkServerControl.start (null will suppress output) or use the KM derby.drda.startNetworkServer property to start the server and then the KM console output will go to derby.log. If I understand this correctly, both these options requires that you create your own java program in order to start the network server. If you invoke NetworkServerControl.main (as the provided scripts do), it does not seem to be any way to turn off the console output. Anyhow, your solution will turn off all console output. The request was to be able to turn off logging of new connections. In my opinion, the logConnections property should affect both console output and output to the derby.log file. -- Øystein
Re: suppress connection %d message on console
XV == Xavier Vigouroux [EMAIL PROTECTED] writes: XV hi, XV is there a way to remove the message on console indicating new XV connection to the server ? As far as I can see from the code, these messages will always go to System.out. Please file a JIRA issue if you want this to change. -- Øystein
Re: Derby ClientDriver versus EmbeddedDriver 10.1.1.0 and a question about the Eclipse plugin
PB == Piet Blok [EMAIL PROTECTED] writes: PB Hi, PB I noticed some different behaviour between Derby EmbeddedDriver and ClientDriver. This was a disappointment, because I wanted to develop a Derby application that may switch between the two drivers. PB Differences I noticed are: PB 1) EmbeddedDriver accepts and uses a Properties object as the second parameter on the connect method. ClientDriver accepts, but does NOT USE the Properties object. A bug? I guess the problem here is that many of the properties has to be set when the Derby is started. In the embedded framework this is done on the first connect. In the client/server framework, I guess Derby is started before the first connect. Hence, it will be too late to set the properties. In my opinion, the ClientDriver should throw an exception if it is not able to set a property. -- Øystein
Re: Simple question about Derby and Transactions
KK == Kostas Karadamoglou [EMAIL PROTECTED] writes: KK Hello, KK I am new to Derby and to Transactions in general. KK I want to perform some tasks to my databases using one thread and lock KK the table that it uses from the remaining threads. KK My qyestion is simple! KK Haw can this be done with Jdbc and Derby? I think you need to consult a JDBC text book and the Derby manuals in order to learn JDBC. You could also learn something from the example programs that comes with Derby. In order to lock a table for the rest of a transaction you can execute the SQL statement: LOCK TABLE t IN EXCLUSIVE MODE; -- Øystein
Re: Behaviour of SYSCS_COMPRESS_TABLE
MM == Mike Matrigali [EMAIL PROTECTED] writes: MM Derby will also reclaim space automatically in btree indexes for this MM case as long as all the old rows on a given leaf are deleted and MM the background process gets a chance to get a table level lock MM at some point - the current algorithm requires a table level lock MM to do the index management to delete an entire leaf from the tree. What about non-leaf pages of the B-Tree? -- Øystein
Re: Behaviour of SYSCS_COMPRESS_TABLE
MM == Mike Matrigali [EMAIL PROTECTED] writes: MM Note that cloudscape automatically reuses space from deleted rows when MM new rows are inserted into the table. The main problem MM SYSCS_COMPRESS_TABLE is solving is if there are a number of deletes MM which will not be followed by a number of inserts. The reuse of space MM is not as efficient as the compress table at it squeezes every last bit MM of free space out, and returns that space to the OS. Is this also true for B-tree indexes? I would imagine that if you have a index on a monotocally increasing key (e.g., a timestamp) and where you regularly delete old records, there may be a lot of empty B-tree pages that will never be possible to reuse. -- Øystein
Re: Behaviour of SYSCS_COMPRESS_TABLE
TW == The Wogster [EMAIL PROTECTED] writes: TW Øystein Grøvlen wrote: Is this also true for B-tree indexes? I would imagine that if you have a index on a monotocally increasing key (e.g., a timestamp) and where you regularly delete old records, there may be a lot of empty B-tree pages that will never be possible to reuse. TW What happens in most databases. is that the database has a fixed page TW size, say 8K, when an index page is full, it splits that page into 2 TW half pages. When an index page is empty it's dropped from the index, TW and added to the empty page pool. Many will merge almost empty TW neighbouring pages, but that doesn't matter for this discussion. I know this. The reason I asked was because I have got the impression that in Derby the only way to drop empty index pages is to do compression. -- Øystein
Re: Automatic database and schema creation
ØG == Øystein Grøvlen - Sun Norway [EMAIL PROTECTED] writes: ØG Peter Nabbefeld wrote: Hello, I just want to create a database, if it does not yet exist. Database creation works, schema creation does not. I'm using the following syntax (in Java): Statement stmt = con.createStatement(); stmt.execute(CREATE SCHEMA USER); The result is an SQLException with SQLState = 42X01 (Syntax error). What am I doing wrong? Before creation, I'm trying to insert a record. If an Exception is thrown, I ask for status 42Y07 (Schema does not exist). Derby is stable version (10.0.2.1). ØG USER is a reserved word. I think it would be nice if the error ØG message reflected that. When I tried this myself in the embedded configuration, I got: ERROR 42X01: Syntax error: Encountered user at line 1, column 15. which should give you a hint of what the problem is. Is it possible to change the message to Encountered reserved word user at ...? -- Øystein
Re: Automatic database and schema creation
Peter Nabbefeld wrote: Hello, I just want to create a database, if it does not yet exist. Database creation works, schema creation does not. I'm using the following syntax (in Java): Statement stmt = con.createStatement(); stmt.execute(CREATE SCHEMA USER); The result is an SQLException with SQLState = 42X01 (Syntax error). What am I doing wrong? Before creation, I'm trying to insert a record. If an Exception is thrown, I ask for status 42Y07 (Schema does not exist). Derby is stable version (10.0.2.1). USER is a reserved word. I think it would be nice if the error message reflected that. -- Øystein
