December 2007 - users-list

[MonetDB-users] Changing default monetdb password
by Venks 06 Dec '07

06 Dec '07

How can I change the default MonetDB password? ALTER USER on "monetdb" gives me the following error: !InvalidCredentialsException:setPassword:The administrator cannot set its own password, use changePassword instead Thanks, -Venks

3 17

[MonetDB-users] Profiling queries
by m h 06 Dec '07

06 Dec '07

Hello all- First of all thanks to you guys for your great help these past few weeks. I really appreciate it. I've learned a bit, and hopefully your product is improving from my bug reports. I've managed to pull a bit of my data from my proprietary db into monetdb. (Have some 60 million rows in the fact table, pulled in 5 million)! I even ran a query against it and got the same numbers (though in a different order)! So I did a very basic poor mans benchmark. My python code can hit the old db or monetdb by changing one line. So I took timestamps around the query execution portion. The old db takes 0.21 seconds when cold. Subsequent queries take 0.027-0.072 seconds Monetdb takes 0.67 seconds when cold and 0.12-0.23 otherwise. It appears that the old db is faster when cold than monetdb is after being primed. A few more caveats. (Yes, this is a poor benchmark but after reading pdfs saying that monetdb was 7 times faster than a 64 way oracle box, I thought I'd get quite a bit of speedup.) The old db is running under vmware on a Core2 1.86Ghz box. Monetdb is running on my laptop, a Pentium M 1.86 Ghz machine. Since the query is confidential I can't really put it here, but I can describe it. I'm pulling stuff out of my fact table and two other dimension tables. The select clause sums a few columns, and on one sums the product of one column and the sum of a few others. The coalesce function is used to convert NULLs to 0. The where statement joins the tables using the = operator and then has two "IN" clauses. Then there is a group by (since I'm aggregating in the select) and an order by. Any hints on how to increase performance? Do I need to tune my query or tweak other things? (Yes I know the laptop machine is not apples to apples to the vmware machine) Am open to suggestions. thanks much, -matt

3 17

[MonetDB-users] JDBC driver, where? (sorry)
by Martin Koch 05 Dec '07

05 Dec '07

Dear Reader, I compiled a Mars-Superball-SR4 and a SR5. Both apperently succesfull, as I have sql functionality. But in neiter I found a /share/MonetDB/ lib/ directory containing the promised JDBC driver. What am I doing wrong? I use a standard Ubuntu Gutsy 32 bit, with sun JRE 5.0 (but not the JDK) to run SQuirreL SQL Thanks for your attention and for your software, Kind regards, Martin

4 4

[MonetDB-users] update query performance
by Lange Marcus 05 Dec '07

05 Dec '07

Hello, I have a question. I have a table with the columns ( id, name (string), num(int) ). In this table I have lets say 100 rows of data. In a Java app. I am updating the different rows individually with the sql command: "UPDATE "tableName" SET "num" = ('99') WHERE "id" = (5);" The time to run this single query is between 15 to 32 milliseconds, except every once in a while when it can be as much as 110 milliseconds. Since I want to do this several times each second for all rows this is way to long time. Am I doing something wrong, or am I using Monet in a way that it is not meant to be used or what ? Is there another way to solve this (Updating individual cells/variables in individual rows FAST) ? Thanks for any input! Regards, Marcus

1 0

[MonetDB-users] inserting into default schema?
by m h 05 Dec '07

05 Dec '07

If I create a table, by default it goes into the "tmp" schema. But when I try to create a "users" table, it complains, since there is one in the "sys" schema. Why doesn't it create one in the "tmp" schema? ie: CREATE TABLE users (fcol FLOAT(25)); !CREATE TABLE: name 'users' already in use Just wondering about the resolution order. thanks -matt

3 6

[MonetDB-users] Optimal hardware/software configuration?
by m h 04 Dec '07

04 Dec '07

Say one wanted to do some benchmarks for Monetdb. Here would be a few questions: * Does Raid 0+1 help more than RAM? * What about multi cores? Are they currently useful? * Are big processor caches useful? * What about 64 bit linux? Does that help or hurt? Or here's another way to put it. If you had between $2-$5K to spend what hardware would you buy? A bunch of PS3's ;) Any input would be great? thanks matt

3 2

[MonetDB-users] converting strings to dates?
by m h 04 Dec '07

04 Dec '07

In Oracle I can do the following: select * from table where timestampcol BETWEEN to_date('01/01/07', 'MM/DD/RR') AND to_date('12/31'/07'), 'MM/DD/RR') It's not clear that this is possible in monetdb. There's the EXTRACT function which pulls a numeric value (month, day or year) from a string representing a timestamp, but I can't see how to get a timestamp/date object out? -matt

3 2

[MonetDB-users] Portuguese Characters
by Paulo Jorge Dias 04 Dec '07

04 Dec '07

Hi, I am trying to insert data which has Portuguese Characters and I get some errors. Using the copy command: sql>copy into d_concelho from '/monetdb5/bo/dados/d_concelho.txt' delimiters '|'; !SQLException:sql:value ALTER DO CH├O|12|20071202 from line 23 not inserted !SQLException:importTable:failed to import table Using a java program with jdbc: D:\MonetDB5>java Concelho java.sql.SQLException: unexpected end of input at nl.cwi.monetdb.jdbc.MonetConnection$ResponseList.executeQuery(MonetConnection.java:2048) at nl.cwi.monetdb.jdbc.MonetConnection$ResponseList.processQuery(MonetConnection.java:1798) at nl.cwi.monetdb.jdbc.MonetStatement.internalExecute(MonetStatement.java:371) at nl.cwi.monetdb.jdbc.MonetStatement.execute(MonetStatement.java:340) at nl.cwi.monetdb.jdbc.MonetPreparedStatement.execute(MonetPreparedStatement.java:209) at Concelho.main(Concelho.java:25) Is there any way to get this running? Thanks very much, Paulo Jorge Dias -- View this message in context: http://www.nabble.com/Portuguese-Characters-tf4938671.html#a14136589 Sent from the monetdb-users mailing list archive at Nabble.com.

2 3

[MonetDB-users] Question about used Encoding
by Markus Gritsch 04 Dec '07

04 Dec '07

Hi, I would like to know how encoding related issues are handles in MonetDB. For example, when I run the following Python script # -*- coding: utf-8 -*- import MonetSQLdb x = MonetSQLdb.connect(host = 'localhost', user = 'voc', password = 'voc', lang = 'sql') c = x.cursor() c.execute('DROP TABLE test') c.execute('CREATE TABLE test(id int, name varchar(20))') c.execute('INSERT INTO test (id, name) VALUES (%s, %s)', (0, 'ä ö ü')) c.close() x.close() and then look at the inserted data using DbVisualizer I get Ã¤ Ã¶ Ã¼ in the 'name' column. When I try to edit the value in the DbVisualizer, I get an error message "unexpected end of input". (The SQL statements reads update voc.test set name = 'ö' where id = 0). Is this a JDBC driver problem? When I connect to an MySQL database with the MySQL JDBC driver included in DbVisualizer I can insert arbitrary characters, and they get properly displayed in the GUI. Kind regards, Markus

3 29

[MonetDB-users] primary key retrieval
by Lange Marcus 04 Dec '07

04 Dec '07

Hello, How do I retrieve the primary key id value after inserting something new into the database ? I am writing a simple Java app. (using JDBC) and would like to get this info back into my program. Thanks for any help! Regards Marcus

2 2