July 2010 - users-list

[MonetDB-users] SQL text normalisation principles in relation to MonetDB
by Stefan de Konink 14 Jul '10

14 Jul '10

-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA512 Hi, I rather have a general question and I would like to know something about normalisation in MonetDB in relation to the underlying storage model. Since my first courses in SQL, that was PostgreSQL at the time. It was a strong advise always try to normalise duplications of data, because it would always increase your search preformance to look up numerical keys over string comparisons. Given that in MonetDB, text-strings are deduplicated in a best effort way, factually become numbers, how does this compare to the additional costs of: - enums - foreign key relations For example, some database schema's use enums (or worse: varchars) for two values. For example 'accessible', 'inaccessible'. It is clear that the storage size for this field BOOLEAN NOT NULL, would be sufficient and of the length of one bit (best case). Now within MonetDB the string data is currently optimised, would it therefore cost additional time to create a secondary table to join to vs using a string field. Thus, does normalisation for 'manual' deduplication, hurt or not? Stefan -----BEGIN PGP SIGNATURE----- Version: GnuPG v2.0.15 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEAREKAAYFAkw9uQAACgkQYH1+F2Rqwn2sjACeNnvDQS+cXjzs1USpomkL6rz8 7wkAn2rF2ZRvEJbQ1cX+oxJsWPcMoTk6 =RzDh -----END PGP SIGNATURE-----

3 2

[MonetDB-users] how to write function like oracle nvl()?
by Mike Jason 13 Jul '10

13 Jul '10

Hi, I need some help on how to write some special functions that monetdb currently doesn't have. E.g., I want to use the oralce funtion nvl(), which is described as follows: http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions105… Then, how can I write a function that has the some ability as the oralce's? By the way, now I use MonetDB-Feb2010.

2 5

[MonetDB-users] MonetDB - Function Sequence Error
by r8rdarklord 13 Jul '10

13 Jul '10

I have written a multithreaded ODBC program which tries to insert data to 11 tables in MonetDB. Each thread has a connection handle, a statment handle (comprising of insert into blah!....). Each thread on instantiation creates the handles (connection, statement etc.), connects to the db, binds meta-data, prepares query and executes the insert in a loop after reading the bind values from a file. The program runs fine to a point following which: SQLExecute: Error: SQLstate HY010, Errnr 0, Message [MonetDB][ODBC Driver 1.0]Function sequence error On closer examination it's the statement handle that is not in Prepared state. I thought that this maybe because the asynchronous execution of the previous execution of the commit isn't complete yet. I recoded my commit as follows: ret_sts = SQLEndTran(SQL_HANDLE_DBC, dbch, SQL_COMMIT); while (ret_sts == SQL_STILL_EXECUTING) ret_sts = SQLEndTran(SQL_HANDLE_DBC, dbch, SQL_COMMIT); check(ret_sts, SQL_HANDLE_DBC, dbch, "SQLEndTran"); However, the ret_sts returns -1 which means a SQL_ERROR This has me perplexed. Clearly the SQL is good else it wouldn't have executed a no. of times in the first place. The stmth also look good, so there is no stepping by threads on each others memory. I can post the whole program if needed. -- View this message in context: http://old.nabble.com/MonetDB---Function-Sequence-Error-tp29148194p29148194… Sent from the monetdb-users mailing list archive at Nabble.com.

1 0

Re: [MonetDB-users] [Monetdb-developers] about import *.csv
by Stefan de Konink 12 Jul '10

12 Jul '10

-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA512 You must reply to the mailinglist, and not to me directly. In order to help you with your segmentation fault you also must provide what version you are using. Op 12-07-10 06:57, kun ren schreef: > Thank you, when I am trying,something is wrong, > when i use command," mclient -l sql -d demo" > and then use command "create table t1(name char(100),number int);" to > create a table; > and then I create a csv file, the content of the file is: > renkun,1 > shaolong,2 > laoluo,3 > and then i use command as you said,"copy 3 records into t1 from 1.csv > delimiters ',','\n';" but the server said:"Segmentation fault > ",what wrong with me?thanks > Best regards; > > > > > > 2010/7/12 Stefan de Konink <stefan(a)konink.de <mailto:stefan@konink.de>> > > On Mon, 12 Jul 2010, kun ren wrote: > > Dear all, > I want to import *.csv into MonetDB,what interface do i will > use？ > > > Just mclient, create your table. And use copy into so for example > > copy i<<numofrows>> records into <<justcreatedtable>> from > 'filename' delimiters ',', '\n', null as ''; > > > Stefan > > -----BEGIN PGP SIGNATURE----- Version: GnuPG v2.0.15 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEAREKAAYFAkw6oSYACgkQYH1+F2Rqwn1OzwCglSIGOl+tKxMk+bbDPPaLUX+i plwAn0aeeJKs3TLiNk4cHPURovJqh8T7 =+Tr/ -----END PGP SIGNATURE-----

3 4

[MonetDB-users] hash accelerator
by Miguel Ángel Águila Lorente 09 Jul '10

09 Jul '10

Hello, I'm working with mal and I've seen in the web documentation, in the part of the inner core that there are built-in accelerator functions. As I can see, there is one search accelerator: hashing. ¿Could you tell me how to use this accelerator using mal language? Looking forward to hearing from you. Thank you. Mike

2 2

[MonetDB-users] MonetDB scalability
by Chris 08 Jul '10

08 Jul '10

Hi, I'm currently evaluating MonetDB for business reporting, and looking for information on scalability, and I can't seem to find any information on clustering options. Is there any support in MonetDB for clustering across multiple physical servers to improve performance? Thanks, Chris

6 10

[MonetDB-users] sql_debug magic number?
by Mark Bucciarelli 06 Jul '10

06 Jul '10

What's a good value for sql_debug in monetdb5.conf? I tried 1 and 17, but got nothing in merovingian.log. Looks like perhaps something was written to dbfarm/<db>/sql_logs/sql/log, but I couldn't make any sense of it. Thanks, m

1 0

Re: [MonetDB-users] SQL: count() counts distinct?
by Ying Zhang 04 Jul '10

04 Jul '10

Hi Niels, I have just updated my stable branch with your backport. It's fixed. Thanks! Jennie On Jul 04, 2010, at 18:42 , Niels Nes wrote: > On Sun, Jul 04, 2010 at 02:49:11PM +0200, Ying Zhang wrote: >> Dear all, >> >> I have a table "a" as the following: >> >> sql>select * from a; >> +------+------+ >> | x | y | >> +======+======+ >> | 1 | 1 | >> | 1 | 2 | >> | 1 | 3 | >> | 2 | 1 | >> | 2 | 2 | >> | 2 | 3 | >> | 2 | 1 | >> | 1 | 2 | >> | 2 | 3 | >> +------+------+ >> 9 tuples >> >> I want to count the number of distinct tuples in the table, so I did the following: >> >> sql>select count(*) from (select distinct x,y from a) as b; >> +------+ >> | L17 | >> +======+ >> | 2 | >> +------+ >> 1 tuple >> sql>select count(x) from (select distinct x,y from a) as b; >> +------+ >> | L20 | >> +======+ >> | 2 | >> +------+ >> 1 tuple >> sql>select count(y) from (select distinct x,y from a) as b; >> +------+ >> | L21 | >> +======+ >> | 3 | >> +------+ >> 1 tuple >> >> It looks like that count() automatically counts the distinct values of x and y, while "select distinct x,y from b" does give me what I want: >> >> sql>select distinct x, y from b; >> +------+------+ >> | x | y | >> +======+======+ >> | 1 | 1 | >> | 1 | 2 | >> | 1 | 3 | >> | 2 | 1 | >> | 2 | 2 | >> | 2 | 3 | >> +------+------+ >> 6 tuples >> >> Is this an expected behaviour of count()? If yes, how can I get the #tuples of "select distinct x,y from b"? >> >> Thanks in advance! >> >> Jennie >> >> PS> info about mserver5: > Jenny it seems one of the m5 optimizers breaks the count (select distinct) case. > In current this seems fixed again. > > Niels >> >> $ mserver5 >> # MonetDB server v5.18.6, based on kernel v1.36.6 >> # Serving database 'demo', using 4 threads >> # Compiled for x86_64-unknown-linux-gnu/64bit with 64bit OIDs dynamically linked >> # Found 7.751 GiB available main-memory. >> # Copyright (c) 1993-July 2008 CWI. >> # Copyright (c) August 2008-2010 MonetDB B.V., all rights reserved >> # Visit http://monetdb.cwi.nl/ for further information >> #warning: please don't forget to set your vault key! >> #(see [prefix]/stable/debug/etc/monetdb5.conf) >> # Listening for connection requests on mapi:monetdb://127.0.0.1:50000/ >>> include sql; >> # MonetDB/SQL module v2.36.6 loaded >> >> >> ------------------------------------------------------------------------------ >> This SF.net email is sponsored by Sprint >> What will you do first with EVO, the first 4G phone? >> Visit sprint.com/first -- http://p.sf.net/sfu/sprint-com-first >> _______________________________________________ >> MonetDB-users mailing list >> MonetDB-users(a)lists.sourceforge.net >> https://lists.sourceforge.net/lists/listinfo/monetdb-users > > -- > Niels Nes, Centrum Wiskunde & Informatica (CWI) > Science Park 123, 1098 XG Amsterdam, The Netherlands > room L3.14, phone ++31 20 592-4098 sip:4098@sip.cwi.nl > url: http://www.cwi.nl/~niels e-mail: Niels.Nes(a)cwi.nl

1 0

[MonetDB-users] SQL: count() counts distinct?
by Ying Zhang 04 Jul '10

04 Jul '10

Dear all, I have a table "a" as the following: sql>select * from a; +------+------+ | x | y | +======+======+ | 1 | 1 | | 1 | 2 | | 1 | 3 | | 2 | 1 | | 2 | 2 | | 2 | 3 | | 2 | 1 | | 1 | 2 | | 2 | 3 | +------+------+ 9 tuples I want to count the number of distinct tuples in the table, so I did the following: sql>select count(*) from (select distinct x,y from a) as b; +------+ | L17 | +======+ | 2 | +------+ 1 tuple sql>select count(x) from (select distinct x,y from a) as b; +------+ | L20 | +======+ | 2 | +------+ 1 tuple sql>select count(y) from (select distinct x,y from a) as b; +------+ | L21 | +======+ | 3 | +------+ 1 tuple It looks like that count() automatically counts the distinct values of x and y, while "select distinct x,y from b" does give me what I want: sql>select distinct x, y from b; +------+------+ | x | y | +======+======+ | 1 | 1 | | 1 | 2 | | 1 | 3 | | 2 | 1 | | 2 | 2 | | 2 | 3 | +------+------+ 6 tuples Is this an expected behaviour of count()? If yes, how can I get the #tuples of "select distinct x,y from b"? Thanks in advance! Jennie PS> info about mserver5: $ mserver5 # MonetDB server v5.18.6, based on kernel v1.36.6 # Serving database 'demo', using 4 threads # Compiled for x86_64-unknown-linux-gnu/64bit with 64bit OIDs dynamically linked # Found 7.751 GiB available main-memory. # Copyright (c) 1993-July 2008 CWI. # Copyright (c) August 2008-2010 MonetDB B.V., all rights reserved # Visit http://monetdb.cwi.nl/ for further information #warning: please don't forget to set your vault key! #(see [prefix]/stable/debug/etc/monetdb5.conf) # Listening for connection requests on mapi:monetdb://127.0.0.1:50000/ >include sql; # MonetDB/SQL module v2.36.6 loaded

1 0

[MonetDB-users] Announcement: New Jun2010 feature release of MonetDB suite
by Sjoerd Mullender 02 Jul '10

02 Jul '10

It is not June anymore, but nevertheless, the MonetDB team at CWI/MonetDB BV is pleased to announce the Jun2010 feature release of the MonetDB suite of programs. More information (including release notes) on this release is available at <http://monetdb.cwi.nl/Development/Releases/Jun2010/>. The download location has changed to <http://dev.monetdb.org/downloads/>. Please fix any bookmarks you may have. -- Sjoerd Mullender

1 2