Re: How to log queries in Apache Derby?
Hi Bryan, and thanks for your input. I launch derby by issuing the command (from $DERBY_HOME) Does it work if you do: java -jar $DERBY_HOME/lib/derbyrun.jar -Dderby.language.logStatementText=true server start No, but this does (at least the server starts - fails on the above command) java -jar -Dderby.language.logStatementText=true $DERBY_HOME/lib/derbyrun.jar server start But again, no joy for the log file(s). http://db.apache.org/derby/docs/10.10/adminguide/tadminconfigsysteminformation.html See the output below (end of post) from linehanp@lg12l9:~/derby/db-derby-10.10.1.1-bin/bin$ NetworkServerControl sysinfo The output from this caused me to look in my own home directory - but the only thing it contains is (just like the others) linehanp@lg12l9:~$ more derby.log Sat Jan 25 17:21:38 GMT 2014: Shutting down Derby engine linehanp@lg12l9:~$ --- I'm still at a loss to understand where my logfile with my query is? As mentioned, I have a file called derby.properties in $DERBY_HOME/bin and $DERBY_HOME with the line derby.language.logStatementText=true in it. Paul... bryan -- lineh...@tcd.ie Mob: 00 353 86 864 5772 linehanp@lg12l9:~/derby/db-derby-10.10.1.1-bin/bin$ NetworkServerControl sysinfo - Derby Network Server Information Version: CSS10100/10.10.1.1 - (1458268) Build: 1458268 DRDA Product Id: CSS10100 -- listing properties -- derby.drda.maxThreads=0 derby.drda.sslMode=off derby.drda.keepAlive=true derby.drda.minThreads=0 derby.drda.portNumber=1527 derby.drda.logConnections=false derby.drda.timeSlice=0 derby.drda.startNetworkServer=false derby.drda.host=localhost derby.drda.traceAll=false -- Java Information -- Java Version:1.7.0_45 Java Vendor: Oracle Corporation Java home: /users/ugrad/linehanp/Downloads/software/jdk/jdk1.7.0_45/jre Java classpath: /users/ugrad/linehanp/derby/db-derby-10.10.1.1-bin/lib/derbyrun.jar OS name: Linux OS architecture: amd64 OS version: 3.2.0-58-generic Java user name: linehanp Java user home: /users/ugrad/linehanp Java user dir: /users/ugrad/linehanp/derby/db-derby-10.10.1.1-bin java.specification.name: Java Platform API Specification java.specification.version: 1.7 java.runtime.version: 1.7.0_45-b18 - Derby Information [/users/ugrad/linehanp/derby/db-derby-10.10.1.1-bin/lib/derby.jar] 10.10.1.1 - (1458268) [/users/ugrad/linehanp/derby/db-derby-10.10.1.1-bin/lib/derbytools.jar] 10.10.1.1 - (1458268) [/users/ugrad/linehanp/derby/db-derby-10.10.1.1-bin/lib/derbynet.jar] 10.10.1.1 - (1458268) [/users/ugrad/linehanp/derby/db-derby-10.10.1.1-bin/lib/derbyclient.jar] 10.10.1.1 - (1458268)
Re: How to log queries in Apache Derby?
Hi Rick (and thanks for your input), java -jar $DERBY_HOME/lib/derbyrun.jar server start or sometimes ./bin/startNetworkServer That command line suggests that the current directory of the JVM is $DERBY_HOME. No - I run ./bin/startNetworkServer from $DERBY_HOME. I can run it from anywhere because I have $DERBY_HOME/bin in my $PATH. But, what I normally do (now) is the java -jar command below. The derby.properties file should live in the current directory, according to the instructions here: http://db.apache.org/derby/docs/10.10/devguide/cdevsetprop13074.html And indeed it does - I have derby.properties in $DERBY_HOME (I have removed it from $DERBY_HOME/bin - seems to have no effect anyway). To recap - I have tried (from $DERBY_HOME) java -jar -Dderby.system.home=$DERBY_HOME $DERBY_HOME/lib/derbyrun.jar server start with various permutations of -Dderby.language.logStatementText=true and -Dderby.infolog.append=true I have used the file derby.properties - 1 line derby.language.logStatementText=true (no commas) on its own, with blank line(s) leading and trailing (that seems to do something sometimes) and still no joy for a simple SELECT query which I issue from ij (also run from $DERBY_HOME). I run COMMIT after the statement even though it's only a SELECT. Is there something glaringly obvious that I'm doing incorrectly? Paul... -Rick -- lineh...@tcd.ie Mob: 00 353 86 864 5772
Re: How to log queries in Apache Derby?
Hi again, As a further clarification to my last post, the database itself is called firstdb and sits in a directory pdata, i.e. $DERBY_HOME/pdata/firstdb My connection string is 'jdbc:derby://localhost:1527/pdata/firstdb'; HTH, Paul... -- lineh...@tcd.ie Mob: 00 353 86 864 5772
Re: How to log queries in Apache Derby - problem solved!
First remove / rename all the previously created derby.log files. Then start your server. Look for a fresh derby.log file (if not the current dir when you start networkserver, also check under your home directory) and put the derby.properties file in *that* directory, then restart... You may also consider adding the following: derby.stream.error.logSeverityLevel=0 derby.infolog.append=true Hi Myrna, Apache Derby query logging problem solved! Create file derby.properties in $DERBY_HOME derby.language.logStatementText=true derby.stream.error.logSeverityLevel=0 derby.infolog.append=true From the $DERBY_HOME run (i.e. start server) java -jar -Dderby.system.home=$DERBY_HOME $DERBY_HOME/lib/derbyrun.jar server start From the $DERBY_HOME run ij. Connect to your database i.e. CONNECT 'jdbc:derby://localhost:1527/pdata/firstdb'; Run your query (I then committed - possibly unnecessary) Exit ij. cat/more/less derby.log and voilà - my query is there! Thank you very much - that's great. Now I can log my queries - I can always play around with the settings, but I know I can always get back to a working setup! Yaay! And thanks to everyone else who contributed. Paul... Myrna -- lineh...@tcd.ie Mob: 00 353 86 864 5772
Re: Can't open more than one connection to database?
Hi again all, I normally connect to derby using either ij or SQuirreL SQL or both. Found it! Using connect 'jdbc:derby://localhost:1527/pdata/firstdb'; I can use multiple connections - I was using a different connection string CONNECT 'jdbc:derby:firstdb'; which obviously picks up the embedded driver in some shape or form! Sorry about the panic! Paul... -- lineh...@tcd.ie Mob: 00 353 86 864 5772
How to log queries in Apache Derby?
Hi all, I am trying to log queries to my Apache Derby server. My $DERBY_HOME is as follows - Ubuntu Linux 64bit - Java jdk1.7.0_45. Can access my database through Squirrel and ij. === linehanp@lg12l9:~/derby/db-derby-10.10.1.1-bin/bin$ echo $DERBY_HOME /users/ugrad/linehanp/derby/db-derby-10.10.1.1-bin linehanp@lg12l9:~/derby/db-derby-10.10.1.1-bin/bin$ === I launch derby by issuing the command (from $DERBY_HOME) java -jar $DERBY_HOME/lib/derbyrun.jar server start or sometimes ./bin/startNetworkServer I created a file called derby.properties in $DERBY_HOME/bin in which there is one line == linehanp@lg12l9:~/derby/db-derby-10.10.1.1-bin/bin$ more derby.properties derby.language.logStatementText=true linehanp@lg12l9:~/derby/db-derby-10.10.1.1-bin/bin$ == Restart server. I run ij from $DERBY_HOME and issue a query. == ij connect 'jdbc:derby://localhost:1527/pdata/firstdb'; ij set schema linehanp; 0 rows inserted/updated/deleted ij select * from analysis; ANALYSIS_ID|CREATED Result set deleted. ij commit; ij exit; = I've looked everywhere and can't find my logged query == linehanp@lg12l9:~/derby/db-derby-10.10.1.1-bin$ find . -name *.log ./pdata/derby.log == same as ./derby.log below. ./derby.log a line about shutting down, nothing else ./bin/derby.log === empty file. linehanp@lg12l9:~/derby/db-derby-10.10.1.1-bin$ cd bin/ Could some kind soul tell me what I'm doing wrong - thanks. If any other information is required, please don't hesitate to let me know. Rgs, Paul... -- lineh...@tcd.ie Mob: 00 353 86 864 5772
Migrating from MySQL to Apache - having difficulty with Timestamp.
Hi all, I have the following MySQL statement and I wish to insert the same data into a Derby table. INSERT INTO analysis VALUES (6,'-00-00 00:00:00','RNASeqGene',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL) My Derby table is of the fomat (some columns missing). COLUMN_NAME |TYPE_NAME|DEC|NUM|COLUM|COLUMN_DEF|CHAR_OCTE|IS_NULL -- ANALYSIS_ID |SMALLINT |0 |10 |5 |NULL |NULL |NO CREATED |TIMESTAMP|9 |10 |29|NULL |NULL |NO LOGIC_NAME |VARCHAR |NULL|NULL|128 |NULL |256 |NO DB |VARCHAR |NULL|NULL|120 |NULL |240 |YES DB_VERSION |VARCHAR |NULL|NULL|40|NULL |80|YES DB_FILE |VARCHAR |NULL|NULL|120 |NULL |240 |YES PROGRAM |VARCHAR |NULL|NULL|80|NULL |160 |YES From here http://db.apache.org/derby/docs/10.5/ref/rrefsqlj27620.html, is *_appears_* that my data is OK, but I get the error == Error: The string representation of a date/time value is out of range. SQLState: 22007 ErrorCode: 3 === From Squirrel SQL. Could anybody kindly point out where I'm going wrong? TIA and rgs, Paul... -- lineh...@tcd.ie Mob: 00 353 86 864 5772
Re: Migrating from MySQL to Apache - having difficulty with Timestamp.
Hi Rick, Maybe the meaning of that MySQL timestamp is supposed to be equivalent to null? Thanks again for your help. My project is a proof of concept - basically, I have a system (genomes/genomics related) that uses MySQL. This system is Java/Javascript based and I'm seeing if the datastore/database side could be swapped out for Derby. The advantages of this is that there would be no need for a MySQL install, no admin (the reaction of most biologists to Unix Eeek, what's that?...) I've taken enough short cuts for the time being (changed both enum and set types into VARCHAR...) that I think that changing those dates to null might do the trick. Obviously, there could be dependencies on these dates that I don't know about, but having looked at all my (sample) data, no timestamp is anything other than '-00-00 00:00:00' so null is probably a good idea. I'll go with that and if the worst comes to the worst, I'll look at the code gulp... Rgs, Paul... -Rick -- lineh...@tcd.ie Mob: 00 353 86 864 5772
Creating tables - migrating from MySQL.
Hi all, I'm trying to migrate a system (72 tables) from MySQL to Apache. I've looked at the docco here http://db.apache.org/derby/docs/10.0/manuals/reference/sqlj27.html#CREATE+TABLE+statement, but it isn't readily apparent to me how to create INDEXES within the CREATE TABLE statement. I'd rather not have to manually perform this procedure - a sample might help. CREATE TABLE analysis ( analysis_id smallint(5) unsigned NOT NULL AUTO_INCREMENT, created datetime NOT NULL DEFAULT '-00-00 00:00:00', logic_name varchar(128) NOT NULL, db varchar(120) DEFAULT NULL, PRIMARY KEY (analysis_id), UNIQUE KEY logic_name_idx (logic_name), KEY db_idx (db) ); Tried this through ij and it failed. What do I have to substitute for PRIMARY KEY, UNIQUE KEY and KEY in the CREATE TABLE statement (I'll either use sed/awk/vi/ or Java eventually) - or do I have to create the PRIMARY KEY and other KEYs outside of the main CREATE TABLE statement? I did try doing this automatically using SQuirrel SQL, but there are (MySQL!!!) some fields called end I'll try and quote these with double quotes for the time being. Any help, ideas, references, URLs appreciated. TIA and rgs, Paul... -- lineh...@tcd.ie Mob: 00 353 86 864 5772
Re: Creating tables - migrating from MySQL.
Hi Bryan, I'm trying to migrate a system (72 tables) from MySQL to Apache. Have you considered using ddlutils: http://db.apache.org/ddlutils/ Yeah, looks interesting for when I come to settle down and write this sort of thing, but what I'm trying to do for the moment is just get it up and running on an ad-hoc basis. I do know that this isn't ideal - but for the moment (I have to demo something on Thursday morning!) it'll suffice. Also found http://flywaydb.org/ and http://migrate4j.sourceforge.net/ and will be looking at these too. Thanks for your interest. Paul... bryan -- lineh...@tcd.ie Mob: 00 353 86 864 5772
Re: Creating tables - migrating from MySQL.
2014/1/21 Rick Hillegas rick.hille...@oracle.com: Hi Rick, and thanks for your input. You might want to take a look at the optional foreignViews tool, which can be useful for data migration projects like yours: http://db.apache.org/derby/docs/10.10/tools/rtoolsoptforeignviews.html. You can create and populate a Derby table from an external table by following the instructions for loading the foreignViews tool and then issuing statements like the following: -- create the local Derby table create table S1.T1 as select * from XYZ_S1.T1 with no data; insert into S1.T1 select * from XYZ_S1.T1; I had a quick look at this - launch it from within ij! I didn't construct my URL properly and got a suitable error message. Just wondering - how will this cope with ENUM types? Tinytext? Mediumtext? In the meantime, I've about 50% of a tool which will get me to where I want - very crude, but it should cover the basics - I'll have a couple of weeks after Thursday and might be able to thrash out something usable. I will experiment with what you've showed me - again after Thursday. I'm Setting Mediumtext and Longtext to BLOB - hope this is OK? Thanks again and rgs, Paul... -Rick -- lineh...@tcd.ie Mob: 00 353 86 864 5772
Re: Biggest Apache Derby database(s).
Hi Rick, I would like to know (with references/URLs preferably) what are the kinds of sizes that large Derby installs stretch to? Would it go to 1TB? More? Less? At Sun Microsystems Straight from the horse's mouth no less! :) we ran a stress test on a Derby database which successfully grew to 500GB. I have no reason to believe that Derby wouldn't scale to 1TB as well. I perhaps should have mentioned that I'm interested in both the client-server and embedded - no difference? Though, at the moment, 0.5TB - 1TB would be the absolute max - and performance would not be the major criterion. Paul... -Rick -- lineh...@tcd.ie Mob: 00 353 86 864 5772
Biggest Apache Derby database(s).
Hi all, I would like to know (with references/URLs preferably) what are the kinds of sizes that large Derby installs stretch to? Would it go to 1TB? More? Less? Thanks in advance for any info, pointers and esp. references and URLs. Rgs, Paul... -- lineh...@tcd.ie Mob: 00 353 86 864 5772
Embedded - can it be multi-user?
Hi all, I've just started to use Derby and really like it. I've come from a Firebird embedded environment with Java, but the fact that Derby is written in Java is a decisive factor in its favour - its feature set is also impressive. However, there's one thing that's confusing me. First thing I did was go to the getstartderby.pdf docco and go through the tutorial. All worked fine, however I was confused by this bit (End of page 33, start of 34) - Derby's two architectures have caused confusion for some new Derby users, who mistakenly think that embedded is a single-user configuration. This is not true. The embedded driver supports multiple simultaneous connections, performs locking, and provides performance, integrity, and recoverability. Any application that uses the Getting Started with Derby embedded driver can open multiple Derby connections and then provide a means for multiple users to interact with the database on each connection. The Derby Network Server is an example of such an application. - Now, I was using the SQuirreL SQL Client to look at my databases as they were being created, but I couldn't use ij with the database *_and_* SQuirreL at the same time. What I'm wondering is, if there's a single app connecting to the database multiple times, is it up to the app to manage the connections so that only one connection is active at any one time, or how exactly does that work? Say in a context where an App Server is connecting to an embedded Derby database - i.e. no server running - does the App Server have to manage requests to the database in a queue or how, exactly does the system work? TIA for any pointers, tips, clues, references, URL's or anything else useful. Rgs, Paul... -- lineh...@tcd.ie Mob: 00 353 86 864 5772
