- users-list - monetdb.org

new gdk bug (on windows) that might've been introduced by Feb2013-SP4
by Anthony Damico 30 Sep '13

30 Sep '13

if you run this R code (with MonetDB.R), you will automatically load a series of brazilian census bureau data sets onto your computer. after all tables have been downloaded and imported, the R script will run a very simple CREATE TABLE command that will cause mserver to shut down when it should not. library(downloader) setwd( "C:/My Directory/CENSO/" ) source_url( " https://raw.github.com/ajdamico/usgsd/master/Censo%20Demografico/download%2…" , prompt = FALSE , echo = TRUE ) after mserver.exe crashes, even if i re-load mserver.exe manually and then connect to it with mclient.exe, and run this block of code: create table c10_dom_pre_fpc as (SELECT * FROM ac_dom) UNION ALL (SELECT * FROM al_dom) UNION ALL (SELECT * FROM am_dom) UNION ALL (SELECT * FROM ap_dom) UNION ALL (SELECT * FROM ba_dom) UNION ALL (SELECT * FROM ce_dom) UNION ALL (SELECT * FROM df_dom) UNION ALL (SELECT * FROM es_dom) UNION ALL (SELECT * FROM go_dom) UNION ALL (SELECT * FROM ma_dom) UNION ALL (SELECT * FROM mg_dom) UNION ALL (SELECT * FROM ms_dom) UNION ALL (SELECT * FROM mt_dom) UNION ALL (SELECT * FROM pa_dom) UNION ALL (SELECT * FROM pb_dom) UNION ALL (SELECT * FROM pe_dom) UNION ALL (SELECT * FROM pi_dom) UNION ALL (SELECT * FROM pr_dom) UNION ALL (SELECT * FROM rj_dom) UNION ALL (SELECT * FROM rn_dom) UNION ALL (SELECT * FROM ro_dom) UNION ALL (SELECT * FROM rr_dom) UNION ALL (SELECT * FROM rs_dom) UNION ALL (SELECT * FROM sc_dom) UNION ALL (SELECT * FROM se_dom) UNION ALL (SELECT * FROM sp1_dom) UNION ALL (SELECT * FROM sp2_rm_dom) UNION ALL (SELECT * FROM to_dom ) WITH DATA; the exact same crash occurs, so this is a new mserver.exe bug not a MonetDB.R bug. should i file a bug report? thanks!!! in mserver---- # MonetDB 5 server v11.15.15 "Feb2013-SP4" # Serving database 'censo_demografico', using 8 threads # Compiled for x86_64-pc-winnt/64bit with 64bit OIDs dynamically linked # Found 7.860 GiB available main-memory. # Copyright (c) 1993-July 2008 CWI. # Copyright (c) August 2008-2013 MonetDB B.V., all rights reserved # Visit http://www.monetdb.org/ for further information # Listening for connection requests on mapi:monetdb://127.0.0.1:50011/ # MonetDB/JAQL module loaded # MonetDB/SQL module loaded >= .\..\..\gdk\gdk_posix.c:800: MT_mremap(bat\02\65\26501.tail,468c50000,1889785 61024,755914244096): GDKextend() failed in mclient---- C:\Windows\System32>cd .. C:\Windows>cd .. C:\>cd "Program Files" C:\Program Files>cd MonetDB C:\Program Files\MonetDB>mclient censo_demografico -p 50011 'mclient' is not recognized as an internal or external command, operable program or batch file. C:\Program Files\MonetDB>mclient 'mclient' is not recognized as an internal or external command, operable program or batch file. C:\Program Files\MonetDB>ls 'ls' is not recognized as an internal or external command, operable program or batch file. C:\Program Files\MonetDB>dir Volume in drive C has no label. Volume Serial Number is 882E-FD8A Directory of C:\Program Files\MonetDB 09/06/2013 04:34 PM <DIR> . 09/06/2013 04:34 PM <DIR> .. 09/27/2013 12:27 PM <DIR> MonetDB5 0 File(s) 0 bytes 3 Dir(s) 451,568,218,112 bytes free C:\Program Files\MonetDB>cd MonetDB5 C:\Program Files\MonetDB\MonetDB5>mclient censo_demografico -p 50011 user(win32):monetdb password: Welcome to mclient, the MonetDB/SQL interactive terminal (Feb2013-SP4) Database: MonetDB v11.15.15 (Feb2013-SP4), 'censo_demografico' Type \q to quit, \? for a list of available commands auto commit mode: on sql>create table c10_dom_pre_fpc as (SELECT * FROM ac_dom) UNION ALL (SELECT * F ROM al_dom) UNION ALL (SELECT * FROM am_dom) UNION ALL (SELECT * FROM ap_dom) UN ION ALL (SELECT * FROM ba_dom) UNION ALL (SELECT * FROM ce_dom) UNION ALL (SELEC T * FROM df_dom) UNION ALL (SELECT * FROM es_dom) UNION ALL (SELECT * FROM go_do m) UNION ALL (SELECT * FROM ma_dom) UNION ALL (SELECT * FROM mg_dom) UNION ALL ( SELECT * FROM ms_dom) UNION ALL (SELECT * FROM mt_dom) UNION ALL (SELECT * FROM pa_dom) UNION ALL (SELECT * FROM pb_dom) UNION ALL (SELECT * FROM pe_dom) UNION ALL (SELECT * FROM pi_dom) UNION ALL (SELECT * FROM pr_dom) UNION ALL (SELECT * FROM rj_dom) UNION ALL (SELECT * FROM rn_dom) UNION ALL (SELECT * FROM ro_dom) U NION ALL (SELECT * FROM rr_dom) UNION ALL (SELECT * FROM rs_dom) UNION ALL (SELE CT * FROM sc_dom) UNION ALL (SELECT * FROM se_dom) UNION ALL (SELECT * FROM sp1_ dom) UNION ALL (SELECT * FROM sp2_rm_dom) UNION ALL (SELECT * FROM to_dom ) WITH DATA; GDK reported error. HEAPextend: failed to extend to 755914244096 for 02\65\26501.tail: MT_mremap() f ailed sql> sql>

3 4

MonetDB compile error 11.15.15
by Brian Hood 28 Sep '13

28 Sep '13

Hi Guys, I just trying to update my Arch Linux package and when building came up with this compile error the package passed the sha1sum after download. Hope someone can help. In file included from /usr/include/string.h:25:0, from /var/abs/AUR/monetdb/src/MonetDB-11.15.15/monetdb5/extras/jaql/parser/jaql.y:23: /usr/include/features.h:327:4: warning: #warning _FORTIFY_SOURCE requires compiling with optimization (-O) [-Wcpp] # warning _FORTIFY_SOURCE requires compiling with optimization (-O) ^ y.tab.c: In function 'jaqlparse': y.tab.c:1665:32: error: 'scanner' undeclared (first use in this function) y.tab.c:1665:32: note: each undeclared identifier is reported only once for each function it appears in make[9]: *** [libjaqlp_la-jaql.tab.lo] Error 1 Regards, Brian Hood

1 0

regular expression - substring
by Budulinku Dejmihrasku 27 Sep '13

27 Sep '13

Hi, i am looking for any idea/suggestion how to do something like Oracle has, i.e. REGEXP_SUBSTR(source, pattern[, position [, occurrence[, match_parameter]]]) which returns the desired substring. why i need this: i have vast amount of data to parse - i.e. i need to get all of the many possible [parameter=value] pairs from an url access log in order to feed the dimension tables w/parameter-value from the URL by parsing the string directly in MonetDB when i write a SQL function to do this (or any string parsing as a matter of fact), it is rather slow when parsing millions of records. i know that there is the PCRE.MAL and i already use with some success functions INDEX, MATCH or REPLACE but i was not able to find anything similar to the REGEXP_SUBSTR functionality to get the desired substring in one go would most likely help me to get better performance any suggestion? please? thanks milan

2 1

Stopping queries, deleting tuples and other general use questions
by Ruben Silva 27 Sep '13

27 Sep '13

Hi, after a while using MonetDB some general use questions pops up. I've collected some which answers I think will be of great help for any MonetDB user. * * *#1:* In the monetdb manpage we can read: *“The kill command immediately sends a SIGKILL and should only be used as last resort for a database that doesn't respond any more. Killing a database may result in (partial) data loss.”* * * 1.1 If kill is the last resort, what are the other options? * * *#2:* System monitor (Oct 2013)<http://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/monitor> 2.1 Will this be available in October or is there a new estimate? 2.2 Does the current development version include this feature yet so we can test it? I'm interested mainly in the methods sys.pause(qtag), sys.resume(qtag) and sys.stop(qtag), *specially sys.stop(qtag)*. 2.4 Is there some alternative method already available in the Feb2013 release to achieve this action? *#3:* In the monetdb manpage we can read about the *nclients* property: *“Sets the maximum amount of clients that can connect to this database at the same time. Setting this to a high value is discouraged.” * 3.1 What do you consider as a high value? Fabian Groffen stated in a similar question *"Concurrency is always a problem for MonetDB, since in the ideal case it means the processes are fighting for resources with each other." * 3.2 Does this mean (theoretically) that if I can run Query A in 10s in a system with resources R (Ram, Disk, Processor), with Rx2 I can run Query A and B (equivalent in effort to A) in the same 10s? *#4:* www.monetdb.org/Documentation/Manuals/SQLreference/Transactions: *"WARNING. The tuples being deleted are only marked as such. They do not reduce the table size. It calls for a vacuum cleaning algorithm."* * * I've found the /usr/lib/monetdb5/createdb/20_vacuum.sql script and after some googling I found the following post* * http://mail.monetdb.org/pipermail/users-list/2011-August/005051.html without an answer. 4.1 Does the following sequence frees the space of the deleted rows? sql>delete from sys.transfer_ip ; 651950 affected rows (117.620ms) sql>call vacuum('sys','transfer_ip'); Best regards, Ruben Silva

2 1

Create Sorted View in monetdb
by Khaled Dehdouh 27 Sep '13

27 Sep '13

Please can you help me. I want to create a view sorted according to attributes like Oracle, my query is as follows: *Create view V1 as Select Name, First_Name, Age from **People O**redr by Name**;* but Monetdb shows me the following error "CREATE VIEW: ORDER BY not supported." How should I do to create a sorted view ?.

2 1

Announcement: New Feb2013-SP4 Bugfix release of MonetDB suite
by Sjoerd Mullender 27 Sep '13

27 Sep '13

The MonetDB team at CWI/MonetDB BV is pleased to announce the Feb2013-SP4 bugfix release of the MonetDB suite of programs. More information about MonetDB can be found on our website at <http://www.monetdb.org/>. For details on this release, please see the release notes at <http://www.monetdb.org/Downloads/ReleaseNotes>. As usual, the download location is <http://dev.monetdb.org/downloads/>. Feb 2013-SP4 bugfix release Merovingian * monetdb now no longer compresses output when not connected to a terminal, bug #3292 Bug Fixes * 2781: SQL localtime() returns GMT time * 2962: client timezone information needs to be handled also for modifications to timestamp values * 3232: The function weekofyear returns results not according to the iso 8601 standard * 3255: M5 tests inlineFunction & ifthencst fail since changeset 207ed7d7e03a * 3292: monetdb status should print full line * 3307: distinct multicolumn performance and implementation suggestion for count distinct * 3310: Cast value to integer gives wrong result * 3311: strLength assertion on join and concat * 3313: Assertion in rel2bin_project * 3315: Incorrect behavior with subqueries in DELETE * 3317: correlated subselect does not work * 3318: UPDATE does not work with DECLEAREd TABLE in FUNCTION * 3319: UNIQUE column in DECLAREd TABLE in FUNCTION results in segfault * 3329: Function AVG fails during runtime in a procedure * 3330: Corrupt mitosis plan with group-by query * 3332: MEDIAN: no such operator 'median(decimal)' * 3336: DB files not removed if all rows are deleted, even after restart * 3337: Monet ODBC gives wrong schema information * 3339: Segvault in complex function call * 3340: crash on seeming incorrect SQL query * 3341: segfault caused by select rank() on a newly-loaded table * 3342: incorrect automatic cast from decimal to tinyint * 3345: copy binary into fails with the wrong error message * 3346: Deadlock in mal_dataflow's q_dequeue() (SciQL-2 branch) * 3348: SQL: LIKE incorrect with _ * 3349: When selecting everything from a joined result, column contents are incorrect in one of my use cases. * 3351: gzip'ed files cannot be bulkloaded into DB * 3352: Median function with decimal type doesn't work * 3353: Readonly tables call for UNSET * 3354: Introduce query time-out * 3358: Remote client connection stays in CLOSE_WAIT state * 3360: uninitialized memory causing bad output in query plans * 3362: ALTER TABLE operations silently remove previously set READ ONLY property * 3363: a join causes SIGSEGV after creation of Unique Key * 3367: Fully qualified order by column gives "relational query without result" * 3369: \0 Byte appears in MAPI

1 0

Re: Contents of users-list Digest, Vol 13, Issue 20
by Edgar Mejia 25 Sep '13

25 Sep '13

1. I have MonetDB Database Server Toolkit v1.1 (Feb2013-SP3) (i install so: sudo apt-get install monetdb5-sql monetdb-client) shell> monetdbd create /path/to/mydbfarm shell> monetdbd start /path/to/mydbfarm shell> monetdb create voc shell> monetdb release voc 2. Ubuntu 12.04.3 LTS 3. Schema and data: Schema: CREATE TABLE timedimension ( id integer NOT NULL, yeart int DEFAULT NULL, quarter varchar(2) DEFAULT NULL, weekyear int DEFAULT NULL, dayt int DEFAULT NULL, namemonth varchar(10) DEFAULT NULL, nameday varchar(20) DEFAULT NULL, montht int DEFAULT NULL, day_of_week varchar(15) DEFAULT NULL, date date DEFAULT NULL, month2 char(2) DEFAULT NULL ) CREATE TABLE bi_con_cdr_diario ( ano INT , facmesfac VARCHAR(6) , comcodigo INT , succodigo VARCHAR(3) , procodigo INT , pronombre VARCHAR(20) , prycodigo VARCHAR(20) , fecllamada timestamp , clicodigo INT , clinombre VARCHAR(80) , vencodigo INT , vennombre VARCHAR(50) , sercodigo VARCHAR(3) , pryservicio VARCHAR(4) , sertipo VARCHAR(3) , serclase VARCHAR(4) , trdtipotelefono CHAR(10) , cfacodigo INT , cfanombre VARCHAR(30) , trddurllamada INT , minutosreales DOUBLE , minutosenteros INT , minutosfacturados DOUBLE , costo DOUBLE , costohit DOUBLE , valorbruto DOUBLE , valorneto DOUBLE , trdvalorlocalneto DOUBLE , trdvalorlocalimpu DOUBLE , valorlocal DOUBLE , codpaisori INT , nompaisori VARCHAR(80) , codpaisdes INT , nompaisdes VARCHAR(80) , comnombre2 VARCHAR(15) , linprnombre VARCHAR(40) , trdcodciuori BIGINT , trdnomciuori VARCHAR(80) , sernombre VARCHAR(50) , idtimedimension integer , linprcodigo INT , trdcodciudes INT , trdnomciudes VARCHAR(80) ); Timedimension data:20080101,2008,"1",1,1,"Ene","",1,"Martes","2008-01-01","01" bi_con_cdr_diario data:2013,"092013",4,"CTA",4,"EMBRATEL","HITBRAEMB","2013-09-04 00:00:00",3608,"Air Liquide do Brasil Ltda - Canoas",420,"Flavio Eugenio/Caroline Carneiro","E-N","E-N","ddd","PSTN","Fijo",89,"Consumo",459,7.65,10,10,0.66924,0.66924,1.43199992,1.1456000328063967,3.437,0.471,3.9080000000000004,55,"Brasil",55,"Brazil","HitBrasil","HIT VOICE",0,"0","HIT CALLSERVICE EMB NAL",20130904,1,0,"0",\N But the error is when i do group by as (select sum(minutosfacturados),succodigo,montht,comnombre2 from bi_con_cdr_diario as cdr inner join timedimension as td on cdr.idtimedimension=td.id where comcodigo=4 group by succodigo,comnombre2,montht;) and have 10.000.000 of rows. I appreciate very much your help. I believe this is one great product. Best Regards, Edgar Mejia

1 0

Re: Contents of users-list Digest, Vol 13, Issue 20
by Edgar Mejia 25 Sep '13

25 Sep '13

Thank you Jennie Zhang. 1. I have MonetDB Database Server Toolkit v1.1 (Feb2013-SP3) (i install so: sudo apt-get install monetdb5-sql monetdb-client) shell> monetdbd create /path/to/mydbfarm shell> monetdbd start /path/to/mydbfarm shell> monetdb create voc shell> monetdb release voc 2. Ubuntu 12.04.3 LTS 3. Schema view schemadatamonetdb.sql file 4. Data: timedimension view datatimediM.csv bi_con_cdr_diario: cdr.csv But the error is when i do group by as (select sum(minutosfacturados),succodigo,montht,comnombre2 from bi_con_cdr_diario as cdr inner join timedimension as td on cdr.idtimedimension=td.id where comcodigo=4 group by succodigo,comnombre2,montht;) and have 10.000.000 of rows. I appreciate very much your help. I believe this is one great product. Best Regards, Edgar Mejia

1 0

Monetdb crashed as group by
by Edgar Mejia 24 Sep '13

24 Sep '13

select sum(minutosfacturados),succodigo,montht,comnombre2 from bi_con_cdr_diario as cdr inner join timedimension as td on cdr.idtimedimension=td.id where comcodigo=4 group by succodigo,comnombre2,montht; but for example as this query work fine: select sum(minutosfacturados),succodigo,montht from bi_con_cdr_diario as cdr inner join timedimension as td on cdr.idtimedimension=td.id where comcodigo=4 group by succodigo,montht; What i need to do for to fix this? Thanks Edgar M

3 2

Whats coming in upcoming build monetdb release
by Ashish Kumar Singh 21 Sep '13

21 Sep '13

Hi , During my periodic search for "Is a new monetdb build is going to be released soon" , I found one is placed under testing recently. Is there a place where it can be seen and reviewed what kind of features/fixes are expected in upcoming release? That will be a great help for some of the folks like me to plan ahead! Regards, Ashish

1 0

Re: users-list Digest, Vol 13, Issue 16
by Rose Beck 21 Sep '13

21 Sep '13

Thanks a lot for replying :) But its really unclear to me as to how can I load RDF data into monetdb. As I have a 1000 property values and I can not manually create table for each of the property values. TSQL allows something like SELECT * INTO someTable FROM sourceTable. RDF query engines like RDF3X, BitMat (Medha Atre, et.al.) have all employed monetdb. So, if there is some way out can you please help me with it. On Sat, Sep 21, 2013 at 12:00 PM, <users-list-request(a)monetdb.org> wrote: > Send users-list mailing list submissions to > users-list(a)monetdb.org > > To subscribe or unsubscribe via the World Wide Web, visit > http://mail.monetdb.org/mailman/listinfo/users-list > or, via email, send a message with subject or body 'help' to > users-list-request(a)monetdb.org > > You can reach the person managing the list at > users-list-owner(a)monetdb.org > > When replying, please edit your Subject line so it is more specific > than "Re: Contents of users-list digest..." > > > Today's Topics: > > 1. Re: How to automate creation of property tables in monetdb? > (Rose Beck) > 2. Re: How to automate creation of property tables in monetdb? > (Martin Kersten) > > > ---------------------------------------------------------------------- > > Message: 1 > Date: Sat, 21 Sep 2013 11:49:10 +0530 > From: Rose Beck <rosebeck.82(a)gmail.com> > To: users-list(a)monetdb.org > Subject: Re: How to automate creation of property tables in monetdb? > Message-ID: > <CAPx9ze9DR0bmy9joSZewwrSL_UNQGP=HefZLHax= > WZAAfDK3Cw(a)mail.gmail.com> > Content-Type: text/plain; charset="iso-8859-1" > > A gentle reminder. Can someone please help me with this? > > > > > On Fri, Sep 20, 2013 at 7:58 PM, Rose Beck <rosebeck.82(a)gmail.com> wrote: > > > I am trying to load data (from myfile, the format of myfile is explained > > below) into monetdb using itsSQL interface. mytable format: > > > > 1 2 3 > > 5 7 8 > > 8 2 3..... > > > > The commands which I am using for performing the same are given below: > > > > sql> create table mytable(subject integer, property integer, object > integer); > > > > sql> copy 10000000 records into mytable from myfile delimiter ' ','\n'; > > > > For each property value (i.e. middle column) I create a separate table: > > > > create table prop1(subject integer, object integer); > > create table prop2(subject integer, object integer); > > create table prop3(subject integer, object integer); ... > > > > and then fill these tables using the following commands: > > > > INSERT into prop1 SELECT subject, object FROM triples_table where > property=prop1 ORDER BY subject, object; > > > > Is there some way to automate this process. i.e. right now I am manually > > creating a separate table for all the distinct properties (using create > > table prop1(subject integer, object integer); ) and then filling this > > table using insert command (INSERT into prop1 SELECT subject, object FROM > > triples_table where property=prop1 ORDER BY subject, object; ). Is there > > some way by which I may automate this procedure. > > > > One of the methods which I know of is to create a C++/python program and > > then call all functions like "create table" by using shell script. But is > > there a faster method to achieve the same. > > > > Also can you please guide as to how can I create and integrate dictionary > > (RDF: id to string mapping) to sql queries? > > <users-list(a)monetdb.org> > > >

2 2

How to automate creation of property tables in monetdb?
by Rose Beck 21 Sep '13

21 Sep '13

I am trying to load data (from myfile, the format of myfile is explained below) into monetdb using itsSQL interface. mytable format: 1 2 3 5 7 8 8 2 3..... The commands which I am using for performing the same are given below: sql> create table mytable(subject integer, property integer, object integer); sql> copy 10000000 records into mytable from myfile delimiter ' ','\n'; For each property value (i.e. middle column) I create a separate table: create table prop1(subject integer, object integer); create table prop2(subject integer, object integer); create table prop3(subject integer, object integer); ... and then fill these tables using the following commands: INSERT into prop1 SELECT subject, object FROM triples_table where property=prop1 ORDER BY subject, object; Is there some way to automate this process. i.e. right now I am manually creating a separate table for all the distinct properties (using create table prop1(subject integer, object integer); ) and then filling this table using insert command (INSERT into prop1 SELECT subject, object FROM triples_table where property=prop1 ORDER BY subject, object; ). Is there some way by which I may automate this procedure. One of the methods which I know of is to create a C++/python program and then call all functions like "create table" by using shell script. But is there a faster method to achieve the same. Also can you please guide as to how can I create and integrate dictionary (RDF: id to string mapping) to sql queries? <users-list(a)monetdb.org>

2 2

Shouldn't multiple identical bind calls be impossible?
by Roberto Cornacchia 19 Sep '13

19 Sep '13

I'd like to ask a simple question before reporting what I suspect to be a bug. On a Feb2013, using default_pipe (same holds for no_mitosis and sequential pipes), no matter what the query and the data schema look like (really, that shouldn't matter here), is it for any reason possible to have a MAL plan with repeated, identical bind operations? Like this: function user.s4_1():void; ... X_40 := sql.bind(X_1,"spinque","dict","idstr",0); ... X_126 := sql.bind(X_1,"spinque","dict","idstr",0); ... X_298 := sql.bind(X_1,"spinque","dict","idstr",0); .. X_384 := sql.bind(X_1,"spinque","dict","idstr",0); ... ... and so on My understanding is: with commonTerms optimizer enabled (I'm just using the standard pipes), there should be no way to see those calls repeated: - they have no side-effects - they do not depend on any user data Why isn't the first bind reused in the rest of the plan? Of course my worries are not much about those calls per se, which are super fast, but about my suspicion that entire subqueries built on top of such binds are replicated unnecessarily. I don't want to focus on the actual query here, because if I am correct (please correct me otherwise), the situation above is already enough to submit a more complete bug report. By the way, another suspicious instruction that I see in my MAL plan is: X_25 := X_24; Shouldn't this be removed by the aliases optimizer? Best, Roberto

3 4

overflow in calculation
by Hassan, Ahmad 19 Sep '13

19 Sep '13

Hi, I am trying latest MonetDB-11.15.11 release on Ubuntu 64bit with TPCH dataset of size 120GB (scale factor 100). I am testing on a system with 512Gb RAM. Out of 22 TPCH queries, query #1 always fail and thrown the following error: overflow in calculation. Command exited with non-zero status 1 113.04 Rest of the queries 2-22 runs fine for 120GB data. If I try with 200GB of data then more queries fail. Any suggestions to work around this problem please? Thanks. Kind Regards, Ahmad

2 1

tomograph not working with remote function?
by Mengmeng Chen 18 Sep '13

18 Sep '13

Hi, We have a MAL function and we are trying to use tomograph to profile the execution of it when it is executed on a remote node. However, the tomograph fails right after the function is executed. We are just wondering if we did something wrong and if tomograph can work with a remote function call. Here's what we want to do: >From node A, we run a MAL function, which will execute a MAL function on both node A and node B using remote.exec() method. Here's what we did and what happened: 1. in A start mclient 2. in A and B, start tomograph "tomograph -d db1 -h localhost" 3. in A, start the driver function, which will call remote.exec for the MAL function on both A and B Then, in A and B, tomograpg exits promptly ubuntu@MC-MoDB1:~->tomograph -d db1 -h localhost -- opened UDP profile stream MC-MoDB1:50010 for localhost Created tomogram 'tomograph' Run: 'gnuplot tomograph.gpl' to create the 'tomograph.pdf' file The memory map is stored in 'tomograph.dat' The trace is saved in 'tomograph.trace' for use with --trace option Looking at the tomograph trace file generated on node B, we have something like: 9 1430 18111 17039 71 71 2 0 0 remote.connect "X_22 := remote.connect(\"mapi:monetdb:// 192.168.4.4:50000/db1\ <http://192.168.4.4:50000/db1%5C>",\"monetdb\",\"monetdb\",\"msql\");", ] 11 1007 10501 9496 70 71 2 0 0 remote.connect "X_24 := remote.connect(\"mapi:monetdb:// 192.168.5.4:50000/db1\ <http://192.168.5.4:50000/db1%5C>",\"monetdb\",\"monetdb\",\"msql\");", ] 69 23408 23605 198 72 72 2 0 0 remote.bintype "remote.bintype();", ] 11 10867 10867 0 71 71 5 0 0 profiler.wait 0.00 0.04 0.75 0.00 0.00 0.00 0.00 0.17 0.17 0.17 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.01 0.00 0.00 0.00 0.00 0.00 0.00 0.25 0.01 0.25 0.00 0.00 0.00 0.00 0.00 9 18497 18497 0 71 71 5 0 0 profiler.wait 0.00 0.04 0.75 0.00 0.00 0.00 0.00 0.17 0.17 0.17 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.01 0.00 0.00 0.00 0.00 0.00 0.00 0.25 0.01 0.25 0.00 0.00 0.00 0.00 0.00 69 23978 23978 0 72 72 4 0 0 profiler.ping 0.00 0.04 0.75 0.00 0.00 0.00 0.00 0.17 0.17 0.17 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.01 0.00 0.00 0.00 0.00 0.00 0.00 0.25 0.01 0.25 0.00 0.00 0.00 0.00 0.00 ================================ on node A, the tomograph trace file looks like: 4 327 453 127 66 66 2 0 0 remote.bintype "remote.bintype();", ] 4 684 684 0 66 66 4 0 0 profiler.ping 0.15 0.15 0.15 0.15 0.15 0.15 0.15 0.15 0.15 0.17 0.15 0.15 0.15 0.15 0.15 0.17 0.15 0.25 0.15 0.15 0.15 0.15 0.15 0.15 0.15 0.20 0.17 0.15 0.15 0.15 0.15 0.15 ================================ Any help would be appreciated! Thanks, Mengmeng

2 1

Re: Need information about culstering monetdb
by Hannes Mühleisen 16 Sep '13

16 Sep '13

Hello Luis, first, please direct such general questions to the mailing list. On 09/04/2013 01:01 AM, Luis Moreno wrote: > I want to know which way works a cluster system MonetDB > > This works distributing information in each node or replicated the > information in each node and distributing query processing or duplicated > information in each node and distributes the connexions as a load balancer > > I hope you can help me > > Im testing monetdb with the Pentaho business intelligence suit. To answer your questions, there are multiple ways of running a clustered MonetDB setup. There is some support for clustered operations in monetdbd, see the section on "MULTIPLEX-FUNNELS" [1]. Apart from that, some virtual duct tape is required to set things up, but this nothing to be afraid of and the load balancing scenario is certainly possible. Best, Hannes [1] http://www.monetdb.org/Documentation/monetdbd-man-page

1 0

Bulk Import
by Thomas Johann 15 Sep '13

15 Sep '13

Hi everybody, I have a problem inserting a large amount of data into a monetdb database using bulk import. I'm running the following commands in a loop: connection.execute("COPY *19000000* OFFSET 2 RECORDS INTO XXX FROM '" + csv + "' USING DELIMITERS ';','\n' ;") connection.commit() , where csv is a different csv-file in each round of the loop, but always containing 18000001 rows of data. To be sure that enough memory is allocated I chose 19000000 in the execute command. I now have two questions: 1. Should the number of records (here 19000000) represent the number of lines per .csv-file or the number of lines of the final database (number of csv-files * 18Mio.)??? 2. Can you think of any reason why monetdb would stop reading one specific variable, while continuing to read the others? Let's say my csv has 8 columns and 18000000 rows with no missing values in the raw data. Until Row 16537472 the total data is read-in, but for the following lines variable 3 is missing until line 18000000 while variable 1 as well as 3-8 are perfectly fine. Can this be due to memory or harddisk speed constraints? Why is no error message raised? It would be great if someone could help me. Thanks, Thomas

4 4

Monetdb blocked
by Edgar Mejia 14 Sep '13

14 Sep '13

Hi, please, i have on server as 3.750 mb as ubuntu 12.04, one processor and 2.000 mhz. I install here monetdb and have 12.000,000 millions of rows. I had one problem as one query. For example this query block database: select "timedimension"."namemonth" as "c0", "bi_con_cdr_diario"."comnombre2" as "c1", "bi_con_cdr_diario"."succodigo" as "c2", sum("bi_con_cdr_diario"."minutosfacturados") as "m0" from "timedimension" as "timedimension", "bi_con_cdr_diario" as "bi_con_cdr_diario" where "bi_con_cdr_diario"."idtimedimension" = "timedimension"."id" and "bi_con_cdr_diario"."comcodigo" = '4' group by "timedimension"."namemonth", "bi_con_cdr_diario"."comnombre2", "bi_con_cdr_diario"."succodigo" if i delete (and "bi_con_cdr_diario"."comcodigo" = '4' ) this work fine. Too i im testing very others querys and work fine, but why this not work and block database? Thaks for your help. Edgar M

1 0

Query Problem with LIKE clause
by Hancel Gonzalez 11 Sep '13

11 Sep '13

Hi, finally i installed MonetDB. Now i get the next problem =S When i execute one query, i get error and i dont know how to solve. Please, help me. =( Hancel. PD: Error is: *MAPI = (monetdb) /tmp/.s.monetdb.50000* *ACTION= read_line* *QUERY = select s0.value,s1.value* * from (* * (select f0.subject as r0,f0.object as r1* * from p1 f0,p0 f1* * where f1.subject=f0.subject and f1.object=58758 and ((select value from strings where id = f0.object) like '%ambilateral%'))* * )facts, strings s0, strings s1* * where s0.id=facts.r0 and s1.id=facts.r1;* *ERROR = !Connection terminated*

4 6

Compiling problem
by Hancel Gonzalez 11 Sep '13

11 Sep '13

Hi again, i have a problem installing MonetDB 11.9.7 from source code. When i do the * make* , get the next error in the process =S Can i help me? Many thanks. Regards, Hancel. PD: Error here: *fits.c:54:20: error: fitsio.h: No such file or directory* *fits.c:55:21: error: fitsio2.h: No such file or directory* *fits.c:56:21: error: longnam.h: No such file or directory* *fits.c: In function 'fits2mtype':* *fits.c:126: error: 'TBIT' undeclared (first use in this function)* *fits.c:126: error: (Each undeclared identifier is reported only once* *fits.c:126: error: for each function it appears in.)* *fits.c:127: error: 'TLOGICAL' undeclared (first use in this function)* *fits.c:129: error: 'TBYTE' undeclared (first use in this function)* *fits.c:130: error: 'TSBYTE' undeclared (first use in this function)* *fits.c:132: error: 'TSTRING' undeclared (first use in this function)* *fits.c:134: error: 'TUSHORT' undeclared (first use in this function)* *fits.c:135: error: 'TSHORT' undeclared (first use in this function)* *fits.c:137: error: 'TUINT' undeclared (first use in this function)* *fits.c:138: error: 'TINT' undeclared (first use in this function)* *fits.c:140: error: 'TLONG' undeclared (first use in this function)* *fits.c:141: error: 'TULONG' undeclared (first use in this function)* *fits.c:142: error: 'TLONGLONG' undeclared (first use in this function)* *fits.c:144: error: 'TFLOAT' undeclared (first use in this function)* *fits.c:146: error: 'TDOUBLE' undeclared (first use in this function)* *fits.c:149: error: 'TCOMPLEX' undeclared (first use in this function)* *fits.c:150: error: 'TDBLCOMPLEX' undeclared (first use in this function)* *fits.c: In function 'fits2subtype':* *fits.c:161: error: 'TBIT' undeclared (first use in this function)* *fits.c:162: error: 'TLOGICAL' undeclared (first use in this function)* *fits.c:165: error: 'TBYTE' undeclared (first use in this function)* *fits.c:166: error: 'TSBYTE' undeclared (first use in this function)* *fits.c:169: error: 'TSTRING' undeclared (first use in this function)* *fits.c:172: error: 'TUSHORT' undeclared (first use in this function)* *fits.c:173: error: 'TSHORT' undeclared (first use in this function)* *fits.c:176: error: 'TUINT' undeclared (first use in this function)* *fits.c:177: error: 'TINT' undeclared (first use in this function)* *fits.c:180: error: 'TULONG' undeclared (first use in this function)* *fits.c:181: error: 'TLONG' undeclared (first use in this function)* *fits.c:182: error: 'TLONGLONG' undeclared (first use in this function)* *fits.c:185: error: 'TFLOAT' undeclared (first use in this function)* *fits.c:188: error: 'TDOUBLE' undeclared (first use in this function)* *fits.c:192: error: 'TCOMPLEX' undeclared (first use in this function)* *fits.c:193: error: 'TDBLCOMPLEX' undeclared (first use in this function)* *fits.c: In function 'FITSexportTable':* *fits.c:209: error: 'fitsfile' undeclared (first use in this function)* *fits.c:209: error: 'fptr' undeclared (first use in this function)* *fits.c:303: error: 'USHORT_IMG' undeclared (first use in this function)* *fits.c:305: error: 'READWRITE' undeclared (first use in this function)* *fits.c:308: error: 'BINARY_TBL' undeclared (first use in this function)* *fits.c:332: error: 'TLOGICAL' undeclared (first use in this function)* *fits.c:361: error: 'TBYTE' undeclared (first use in this function)* *fits.c:419: error: 'TSHORT' undeclared (first use in this function)* *fits.c:448: error: 'TINT' undeclared (first use in this function)* *fits.c:477: error: 'TLONG' undeclared (first use in this function)* *fits.c:506: error: 'TFLOAT' undeclared (first use in this function)* *fits.c:535: error: 'TDOUBLE' undeclared (first use in this function)* *fits.c: In function 'FITSdir':* *fits.c:573: error: 'fitsfile' undeclared (first use in this function)* *fits.c:573: error: 'fptr' undeclared (first use in this function)* *fits.c:588: error: 'READONLY' undeclared (first use in this function)* *fits.c: In function 'FITSdirpat':* *fits.c:607: error: 'fitsfile' undeclared (first use in this function)* *fits.c:607: error: 'fptr' undeclared (first use in this function)* *fits.c:631: error: 'READONLY' undeclared (first use in this function)* *fits.c: In function 'FITStest':* *fits.c:646: error: 'fitsfile' undeclared (first use in this function)* *fits.c:646: error: 'fptr' undeclared (first use in this function)* *fits.c:651: error: 'READONLY' undeclared (first use in this function)* *fits.c: In function 'FITSattach':* *fits.c:670: error: 'fitsfile' undeclared (first use in this function)* *fits.c:670: error: 'fptr' undeclared (first use in this function)* *fits.c:684: error: 'READONLY' undeclared (first use in this function)* *fits.c:731: error: 'ASCII_TBL' undeclared (first use in this function)* *fits.c:731: error: 'BINARY_TBL' undeclared (first use in this function)* *fits.c:735: error: 'TSTRING' undeclared (first use in this function)* *fits.c: In function 'FITSloadTable':* *fits.c:834: error: 'fitsfile' undeclared (first use in this function)* *fits.c:834: error: 'fptr' undeclared (first use in this function)* *fits.c:842: error: 'FLEN_VALUE' undeclared (first use in this function)* *fits.c:878: error: 'READONLY' undeclared (first use in this function)* *fits.c:886: error: 'ASCII_TBL' undeclared (first use in this function)* *fits.c:886: error: 'BINARY_TBL' undeclared (first use in this function)* *fits.c:906: error: 'TSTRING' undeclared (first use in this function)* *fits.c:970: error: 'FLEN_ERRMSG' undeclared (first use in this function)* *make[9]: *** [lib_fits_la-fits.lo] Error 1* *make[9]: Leaving directory `/home/hancel/MonetDB-11.9.7/sql/backends/monet5/vaults'* *make[8]: *** [all] Error 2* *make[8]: Leaving directory `/home/hancel/MonetDB-11.9.7/sql/backends/monet5/vaults'* *make[7]: *** [all-recursive] Error 1* *make[7]: Leaving directory `/home/hancel/MonetDB-11.9.7/sql/backends/monet5'* *make[6]: *** [all] Error 2* *make[6]: Leaving directory `/home/hancel/MonetDB-11.9.7/sql/backends/monet5'* *make[5]: *** [all-recursive] Error 1* *make[5]: Leaving directory `/home/hancel/MonetDB-11.9.7/sql/backends'* *make[4]: *** [all] Error 2* *make[4]: Leaving directory `/home/hancel/MonetDB-11.9.7/sql/backends'* *make[3]: *** [all-recursive] Error 1* *make[3]: Leaving directory `/home/hancel/MonetDB-11.9.7/sql'* *make[2]: *** [all] Error 2* *make[2]: Leaving directory `/home/hancel/MonetDB-11.9.7/sql'* *make[1]: *** [all-recursive] Error 1* *make[1]: Leaving directory `/home/hancel/MonetDB-11.9.7'* *make: *** [all] Error 2*

4 11

Setting connection time-out?
by Dennis Pallett 10 Sep '13

10 Sep '13

Hello, Is it possible to set a time-out in MonetDB for connections? On our test instance of MonetDB we are running into connection limits because our developers are starting/stopping our application all day long without properly closing the connections. On production this is not a problem because there the connections are closed properly. Best regards, Dennis Pallett

1 0

GeoSpatial
by Lee Hachadoorian 05 Sep '13

05 Sep '13

I found out about MonetDB a few months ago and am currently giving it a whirl. I am a geographer and am interested in the GeoSpatial extension. I have found limited documentation [1][2], which (I think) has changed since I first saw it a few months ago. I remember something about a shapefile import application that was based on shp2pgsql used by PostGIS, and also something about using OpenJump to visualize the data stored in the MonetDB database, but I cannot find this information any more. I have successfully created a spatial table by copying a CREATE TABLE statement from PostGIS, then using COPY ... TO in PostGIS and COPY ... FROM in MonetDB. But I would like to know if a shp2pgsql equivalent exists for MonetDB, or if there is any work on or plans for OGR functionality. I would also like to know how users visualize MonetDB spatial data in OpenJump or any other GIS, which incidentally also leads into the question of OGR functionality since so many FOSS GISes (e.g. QGIS) use OGR for data loading. Best, --Lee [1] http://www.monetdb.org/Documentation/Extensions/GIS [2] http://www.monetdb.org/Documentation/Extensions/GIS/SpatialTypes -- Lee Hachadoorian Assistant Professor in Geography, Dartmouth College http://freecity.commons.gc.cuny.edu

2 1

Database crash; won't start anymore
by Ralph Holz 30 Aug '13

30 Aug '13

Hi, I've tried to load a good chunk of the Internet census into our MonetDB - about 100M rows. Although the import scripts completed, it seems it shot down Monet: 2013-08-28 04:10:35 MSG merovingian[9857]: database 'census' (10773) was killed by signal SIGBUS 2013-08-28 10:47:59 MSG merovingian[9857]: database 'census' has crashed after start on 2013-08-28 01:14:07, attempting restart, up min/avg/max: 4m/4d/3w, crash average: 1.00 0.10 0.03 (8-7=1) This is then followed by 2013-08-28 10:48:32 MSG census[19161]: # still reading write-ahead log "/mnt/monetdb/census/sql_logs/sql/log.990" (0% done) [...] 2013-08-28 10:53:12 MSG census[19161]: # still reading write-ahead log "/mnt/monetdb/census/sql_logs/sql/log.990" (4% done) 2013-08-28 10:53:34 MSG merovingian[9857]: database 'census' (19161) has crashed (dumped core) Restarting it, yielded the same results for a few times. Once or twice it got to 35% in the WAL 2013-08-28 15:34:27 MSG census[14420]: # still reading write-ahead log "/mnt/monetdb/census/sql_logs/sql/log.990" (35% done) 2013-08-28 15:34:59 MSG merovingian[9857]: database 'census' (14420) has crashed (dumped core) 2013-08-28 15:34:59 ERR merovingian[9857]: client error: database 'census' has crashed after starting, manual intervention needed, check monetdbd's logfile for details 2013-08-28 15:34:59 ERR merovingian[9857]: client error: client (local) sent challenge in incomplete block: 2013-08-28 15:34:59 ERR merovingian[9857]: client error: client (local) sent challenge in incomplete block: 2013-08-28 15:34:59 MSG merovingian[9857]: database 'census' has crashed after start on 2013-08-28 14:43:34, attempting restart, up min/avg/max: 4m/4d/3w, crash average: 1.00 0.90 0.30 (16-7=9) Since then, it won't start (I don't know what caused the client error BTW). Data loss is not a concern, but we'd sure like to know if this can be fixed or if we should start looking at another DB... Thanks for any help, Ralph -- Ralph Holz I8 - Network Architectures and Services Technische Universität München http://www.net.in.tum.de/de/mitarbeiter/holz/ Phone +49.89.289.18043 PGP: A805 D19C E23E 6BBB E0C4 86DC 520E 0C83 69B0 03EF

3 7

Archlinux AUR Package
by Brian Hood 29 Aug '13

29 Aug '13

Hi All, I maintain a package build in the user repository which i update every new release. aur.archlinux.org - just search Regards, Brian Hood

1 0

Clustered Indexes with monetdb
by Ashish Kumar Singh 28 Aug '13

28 Aug '13

HI All, Can somebody point me to relevant information on how to do clustered indexes with monetdb? Regards, Ashish

3 7

Server - location of tmp database objects
by Radovan Bičiště 27 Aug '13

27 Aug '13

Hello, I was wondering if there is a way to specify for database where to put tmp files? For example when we use temporary tables etc. it is filling our data disk. I have another disk on the machine that I would like to use for temporary tables etc. Thank you for any hints. Best regards, Radovan -- __________________________ Radovan Bičiště ceos data s.r.o. třída SNP 402/48 500 03 Hradec Králové Czech Republic mobil CZ: +420 601 563 014 skype: rbiciste

4 3

Technical Problem connecting with database
by Hancel Gonzalez 27 Aug '13

27 Aug '13

Hi, i have a problem: i installed *monetdb 11.9.7 *from source code and created the database farm and the database with commands *monetdbd *and *monetdb, *respectively. The problem is when i try connect to the database (*mclient*) and dont work, see the log file but i dont how to solve. Please, anyone can help me to solve? This is the message in log file: *2013-08-26 21:48:41 MSG merovingian[7622]: starting database 'Berlin10MDB', up min/avg/max: 0s/0s/0s, crash average: 0.00 0.00 0.00 (0-0=0)* * * *2013-08-26 21:48:41 MSG Berlin10MDB[7668]: arguments: /usr/local/bin/mserver5 --set gdk_dbfarm=/home/gdesimone/monet_data --dbname=Berlin10MDB --set merovingian_uri=mapi:monetdb://Athena:50000/Berlin10MDB --set mapi_open=false --set mapi_port=0 --set mapi_usock=/home/gdesimone/monet_data/Berlin10MDB/.mapi.sock --set monet_vault_key=/home/gdesimone/monet_data/Berlin10MDB/.vaultkey --set gdk_nr_threads=4 --set max_clients=64 --set sql_optimizer=default_pipe --set monet_daemon=yes* * * *2013-08-26 21:48:41 MSG merovingian[7622]: database 'Berlin10MDB' (7668) has exited with exit status 127* * * *2013-08-26 21:48:41 ERR merovingian[7622]: client error: database 'Berlin10MDB' appears to shut itself down after starting, check monetdbd's logfile for possible hints* * * Many thanks! Regards, Hancel.

3 5

Database Problem
by Thomas Johann 23 Aug '13

23 Aug '13

Dear all, I’m new to monetdb database programming and have a problem setting up my first database which I can not explain by myself: I’m basically running the following Python code: tempdir = 'E:\\xetra\\tempdir\\monetdb' connection = monetdb.sql.connect(username="monetdb", password="monetdb", hostname="localhost", database="database") # expected table layout connection.execute("CREATE TABLE XXX(Name_1 TEXT, Name_2 TEXT, Name_3 TEXT, Name_4 TEXT, Num_1 INT, Num_2 REAL,Num_3 INT,Name_5 CHARACTER(1))") def convert_table(filename,path): print("Reading CSV") try: start = time.clock() csv_to_sql = path + "\\" + filename connection.execute("COPY 100000000 OFFSET 2 RECORDS INTO XXX FROM '" + csv_to_sql + "' USING DELIMITERS ';','\n' ;") elapsed = (time.clock() - start) connection.commit() print("Time to write to database: " + str(elapsed) + " seconds") except: #get wrong transaction out of DB with rollback connection.rollback() try: #DB conversion start = time.clock() infile = open(csv_to_sql, 'rb') csv_to_sql_converted=path+ '\\converted.csv' outfile = open(csv_to_sql_converted, 'wb') BLOCKSIZE = 100000000 # experiment with size while True: block = infile.read(BLOCKSIZE) if not block: break outfile.write(block.decode('cp1252','strict').encode('utf-8','strict')) infile.close() outfile.close() elapsed = (time.clock() - start) print("Time to convert file to UTF-8: " + str(elapsed) + " seconds") #write to DB start = time.clock() connection.execute("COPY 100000000 OFFSET 2 RECORDS INTO XXX FROM '" + csv_to_sql_converted + "' USING DELIMITERS ';','\n' ;") connection.commit() os.remove(csv_to_sql_converted) elapsed = (time.clock() - start) print("Time to write to database: " + str(elapsed) + " seconds") except Exception as inst: #get wrong transaction out of DB with rollback connection.rollback() print type(inst) # the exception instance print inst.args # arguments stored in .args print inst # __str__ allows args to printed directly print("Could not write file "+csv_to_sql+"to the database XXX") filename_list = [“XYZ.csv”, “ABC.csv”,…] path = “C:\\” for filename in filename_list: convert_table(filename, path) I had to alter the code as the actual csvs contain very sensitive data, which I am not allowed to publish. But the structure is the same. I do not get any error message and the program runs through properly. Usually it goes through the “try:” part of the function. When I look into the resulting database the number of lines is as expected (so the observations must be somewhere in the database) but there are some gaps in the output, meaning that some values from the .csvs are just not copied and I am unable to compare those gap-lines to the original data as the identifier is usually among the missing variables. Some variables from the original data are just missing. This happens (at least to what I can say) in an unsystematic way. I have the impression that MonetDB might just be overwhelmed by the amount of data. Is this possible? The .csvs have each a size of 1.3GB and I’m reading more than 1000 of them into the database which has a final size of about 2 TB. I’m running this under Windows Server 2008 R2, 32GB RAM, AMD Opteron 4234 6-Core 3.10 GHz, using Anaconda. When I manually read in one single file, that made problems beforehand, I will find it in the database without error. So, I’m pretty puzzled what is going on here. Might it happen that the connection.execute-command runs through without triggering an error message but still not reading the whole dataset? I would be very glad about any help from the community. Thanks, Thomas

2 2

Installation and configuration on Windows 8
by m- dev 22 Aug '13

22 Aug '13

I'm not familiar with database administration, but I guess I'm having similar problems to this Windows user: http://mail.monetdb.org/pipermail/users-list/2013-June/006078.html I installed DB5-SQL, Geom and ODBC 32 and 64bit, default options for all. After that it's not clear to me which steps are for Linux or Windows, or both. Or how to set up the configuration file. In Windows 8 I start 'MonetDB SQL Server'. I can also start 'MonetDB SQL Client'. i) I added PATH in Environment variable to the installation folder (that defaults to 32-bit Program files): ;C:\Program Files\MonetDB\MonetDB5 But I couldn't set up a configuration file, either from within the Server: > cat ~/.monetdb SyntaxException:parseError:cat ~/.monetdb SyntaxException:parseError: ^,';' expected Or the Client: sql>cat ~/.monetdb more>name=#### more>password=######### more>language=SQL more> Creating that as a text file renamed config.monetdb in the installation folder doesn't work either. ii) I've extracted the voc_dump.sql file, but the Guide doesn't specify the location of mclient.exe and when I found it in C:\Program Files\MonetDB\MonetDB5\bin but it won't open because: "libmapi.dll is missing...Try reinstalling the program to fix this problem" Clearly I haven't set it up MonetDB correctly, I can only guess, but what are the correct steps? Is there any other guide or tutorial on installation and configuration on Windows 8? Thanks, Mark

2 2

SELECT DISTINCT on datatype inet causes high load even after cancelling
by Ralph Holz 22 Aug '13

22 Aug '13

Good day, We're experiencing a phenomenon that I can't quite explain. We have a table of maybe 110M entries mapping IP addresses to some values. The data type of field ip_addr is net. No indices or constraints set. A SELECT COUNT(ip) is zippy - result in some ms. Any SELECT, in fact, that does not use WHERE is fast. E.g.: SELECT ip, result FROM iptable LIMIT 10000; +------------------ 10000 tuples (4.6s) However: A SELECT COUNT(DISTINCT ip) does not return even after waiting an hour or, just to try it out once, 24 hrs. Load remained at 27 during that time. I restarted the server after that, as I had done ctrl-c in mclient, and that seems to let the current query continue? What would be the right way to cancel a query? I then tried SELECT ip FROM iptable WHERE ip = inet '1.0.0.1' That IP is the second in the column. The query did not return even after 10 minutes. That seems a bit excessive. Does anyone have a clue why this is so? Should I avoid inet? Thanks, Ralph Disclaimer: I come from a psql background and these are my first steps with vertical-col data stores. Server: MonetDB 5 server v11.15.11 "Feb2013-SP3" (64-bit, 64-bit oids) Copyright (c) 1993-July 2008 CWI Copyright (c) August 2008-2013 MonetDB B.V., all rights reserved Visit http://www.monetdb.org/ for further information Found 142.0GiB available memory, 24 available cpu cores Libraries: libpcre: 8.30 2012-02-04 (compiled with 8.02) openssl: OpenSSL 0.9.8o 01 Jun 2010 (compiled with OpenSSL 0.9.8o 01 Jun 2010) libxml2: 2.7.8 (compiled with 2.7.8) Compiled by: root(a)dev.monetdb.org (x86_64-pc-linux-gnu) Compilation: gcc -O3 -fomit-frame-pointer -pipe -Wp,-D_FORTIFY_SOURCE=2 Linking : /usr/bin/ld -m elf_x86_64 -- Ralph Holz I8 - Network Architectures and Services Technische Universität München http://www.net.in.tum.de/de/mitarbeiter/holz/ Phone +49.89.289.18043 PGP: A805 D19C E23E 6BBB E0C4 86DC 520E 0C83 69B0 03EF

1 0

MonetDB OOM crash in CentOS
by Ravikumar Balasubramaniam 20 Aug '13

20 Aug '13

Hi, MonetDB is crashing because of OOM. I have tried following config update but in vain. sysctl -w vm.overcommit_memory=2 sysctl -w vm.overcommit_ratio=75 Is there any configuration to limit mserver memory usage? Database contains 300 million records and the select query expects 120,620 records as result. This is the log message: Out of memory: Kill process 30650 (mserver5) score 981 or sacrifice child Killed process 30650, UID 222, (mserver5) total-vm:35263192kB, anon-rss:14911384kB, file-rss:208kB *mserver5 version*: MonetDB 5 server v11.15.11 "Feb2013-SP3" (64-bit, 64-bit oids) Copyright (c) 1993-July 2008 CWI Copyright (c) August 2008-2013 MonetDB B.V., all rights reserved Visit http://www.monetdb.org/ for further information Found 14.5GiB available memory, 4 available cpu cores Libraries: libpcre: 7.8 2008-09-05 (compiled with 7.8) openssl: OpenSSL 1.0.0 29 Mar 2010 (compiled with OpenSSL 1.0.0-fips 29 Mar 2010) libxml2: 2.7.6 (compiled with 2.7.6) Compiled by: ec2-user@ip-10-252-152-159 (x86_64-unknown-linux-gnu) Compilation: gcc -g -O2 Linking : /usr/bin/ld -m elf_x86_64 *Filesystem Size Used Avail Use% Mounted on* /dev/xvde1 5.0G 2.4G 2.4G 50% / tmpfs 7.3G 0 7.3G 0% /dev/shm /dev/xvdf 414G 209G 184G 54% /media/ephemeral0 (dbfarm) Regards, Ravi

1 0

MonetDB cluster management in EC2
by Ravikumar Balasubramaniam 20 Aug '13

20 Aug '13

I have followed http://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/ClusterManagement… to setup 3 node(in amazon EC2) cluster. I have considered the server from where I control remote databases as leader and others as remote servers. The 'monetdb discover' in the leader is not showing any other database(except self dbs). I have enabled discovery and control properties in the remote servers. What other configurations are needed for successful discovery in EC2 environment? MonetDB cluster enables mclient to combine all the (select)query results from remote servers and itself. The application should take care of shared write into nodes. Is this fair understanding? Regards, Ravi

2 2

MonetDB and Initial Memory requirements of database startup
by Ashish Kumar Singh 20 Aug '13

20 Aug '13

Hi All, Is there any check in monetdb at startup time which will ascertain that for a dataset of size x mb in hard disk how much minimum free memory should be available in system so that monetdb can run successfully. If so please point me to relevant section. Thanks! Regards, Ashish

2 2

change variables within select statement
by Christian Kleinke 19 Aug '13

19 Aug '13

Dear all, is there a possibility to change declared variables in monetdb within a select statement. For example in MySQL the variables can be declared with the @-symbol, and such variables easily could be changed in a case-when condition. Here a little example of what I want to do (in MySQL syntax): SET @id = ''; SET @pos = 1; drop table if exists test_table; create table test_table as select *, if (@id<> id, @pos:=1, @pos:=@pos+1) as position_number, @id:=id, from table_with_ids; Here the rows with identical ids should be counted, and if the id changes the position_number should start again as zero. If somebody has a hint for me, to execute such a query in monetdb, I would be very glad. Greetings, Christian

3 3

Fwd: MonetDB pip python3
by Martin Kersten 18 Aug '13

18 Aug '13

-------- Original Message -------- Subject: MonetDB pip python3 Date: Sat, 17 Aug 2013 20:21:51 +0200 From: Christian Tismer <tismer(a)stackless.com> To: Martin Kersten <Martin.Kersten(a)cwi.nl> Hi Martin, by chance I came along MonetDB today, and I think this can be the solution to my database problems. (used zodb and durus, but there are way too many columns and rows to handle with these). I am trying to use MonetDB with python 3.3. Unfortunately, the pip installer sees only the python 2 version. I had a look into the repository - the python 3 version is there. I saw you active right now, so I'm asking: Should I clone the repo and install the py3.3 version from there, or is it planned to add this to pip, soon? (I can work on this if it makes sense) cheers - Chris (that Stackless guy...) -- Christian Tismer :^) <mailto:tismer@stackless.com> Software Consulting : Have a break! Take a ride on Python's Karl-Liebknecht-Str. 121 : *Starship* http://starship.python.net/ 14482 Potsdam : PGP key -> http://pgp.uni-mainz.de phone +49 173 24 18 776 fax +49 (30) 700143-0023 PGP 0x57F3BF04 9064 F4E1 D754 C2FF 1619 305B C09C 5A3B 57F3 BF04 whom do you want to sponsor today? http://www.stackless.com/

2 1

b and g must be aligned
by Miguel Ping 16 Aug '13

16 Aug '13

Hi, I'm resurrecting this since I've been out of town and only today I got a chance to investigate further. I've recompiled with -O0 to prevent optimizations from "hiding" the values, and in the debugger I got this: b->U->count BUN 764314 g->U->count BUN 17 b->H->seq oid 0 g->H->seq oid 0 b->H->dense unsigned int 1 g->H->dense unsigned int 1 The stack call is as follows: BAThllaggr() at udf.c AGGRsubhllaggcand() at udf.c AGGRsubhllagg() at udf.c malCommandCall() at mal_interpreter.c runMALsequence() at mal_interpreter.c DFLOWworker() at mal_dataflow.c start_thread() at pthread_create.c clone() at clone.S 0x0 -------- Original Message -------- Subject: Re: b and g must be aligned Date: Fri, 26 Jul 2013 11:34:53 +0100 From: Sjoerd Mullender <sjoerd(a)acm.org> Reply-To: Communication channel for MonetDB users <users-list(a)monetdb.org> To: Communication channel for MonetDB users <users-list(a)monetdb.org> -----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On 2013-07-26 12:23, Miguel Ping wrote: > On 07/25/2013 01:51 PM, Sjoerd Mullender wrote: On 2013-07-25 > 14:04, Miguel Ping wrote: >>>> Hi all, >>>> >>>> We're hitting this error "b and g must be aligned". I tracked >>>> the src to a commit about some alignment code thing in >>>> gdk_calc: >>>>http://www.mail-archive.com/checkin-list@monetdb.org/msg09731.html >>>> >>>> (Fix alignment conversion in compatibility code for grouped >>>> aggregates.) >>>> >>>> Can you guys please explain what's the reason behind this >>>> error? I can't understand by just looking to the src of >>>> gdk_calc.c >>>> >>>> Thanks! > When using grouped aggregates, the grouping bat must be aligned > with the value bat. The value bat is b and contains the values you > want to aggregate. The group bat is g and contains for each value > in b the group (an oid) it belongs to. Equal group ids means the > same group. These bats must be aligned, because we need to know for > each value in b to which group it belongs. Aligned means: same > length, and same head column values. The head columns must be > dense (a sequence of numbers starting at some value, and each next > value exactly one larger than the previous). Dense sequences are > usually not stored explicitly in MonetDB. We only store the first > value in the hseqbase field. So the hseqbase fields of b and g > must be equal. The one exception to this is when the bats are both > empty. This last exception is the change to gdk_calc.c in that > changeset. > > -- Sjoerd Mullender >> _______________________________________________ users-list >> mailing listusers-list(a)monetdb.org >>http://mail.monetdb.org/mailman/listinfo/users-list >> >> > Thanks for your explanation. I still don't understand how can > there be a misalignment. I would expect MonetDB to feed my hll > aggregate functions with the correct values. Can it be that I may > have some NULL values and the validation is failing because of > that? If this happens when running a SQL query, it's a bug. I don't think NULLs have anything to do with it. NULL values are stored in-line. You might want to look at b->U->count, g->U->count, b->H->seq, g->H->seq, b->H->dense, g->H->dense when the misalignment happens (either in the debugger or by using printf--but realize that count and seq are not int, so %d is not going to work). Also things like the MAL plan (prepend SQL query with EXPLAIN) and the stack trace might be useful. - -- Sjoerd Mullender -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.13 (GNU/Linux) Comment: Using GnuPG with Thunderbird -http://www.enigmail.net/ iQCVAwUBUfJQyT7g04AjvIQpAQK/JAP9HCp/aFaYWv0jodfPUnSRVgFSsdjTn/VL ttSsmAF+yomGMDIne2311f/D51F3/nte7Utx+01lgvArapWErhjGN1hzPSr5LQbs PZ6dUfNcH8Rt2AtT3uSxfkFZy9VRDCNNXPei43IgMS2HxVZ48pnAVkNcpBbW3Gms GwdvU7bSZtM= =2zSg -----END PGP SIGNATURE----- _______________________________________________ users-list mailing list users-list(a)monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

5 20

monetdb lost column info when queryring
by integrity 16 Aug '13

16 Aug '13

some of the column "clientport" is NULL, but if change to another query method, it appeared again. 2. another bug: sql>select * from t_ivr_result limit 1; could not allocate space for HEAPextend: failed to extend to 15096348672 for 02/04/20413.theap another time: ERROR: HEAPextend: failed to extend to 23460839424 for 02/21/22122.theap it seems monetdb applied a heap of 15G ,the RAM is 64G, root@10.187.22.175:~# free -g total used free shared buffers cached Mem: 62 45 16 0 0 43 -/+ buffers/cache: 2 60 Swap: 2 0 2 so the left memory is 16G, but failed to applied 15G heap. and the table t_ivr_result is only 3G, there is no need to apply such a large heap. sql>select count(*) from t_ivr_result; +----------+ | L1 | +==========+ | 40299239 | +----------+ 1 tuple (137.023ms) sometimes the heap is 24G: ERROR: MALException:mat.pack:could not allocate space for ERROR: HEAPextend: failed to extend to 23460839424 for 02/21/22122.theap Thanks, Meng

2 2

Timestamp truncation in MonetDB
by Ravikumar Balasubramaniam 13 Aug '13

13 Aug '13

Hi, I'm trying to truncate timestamp value to hour and/or minute precision. I combined cast and extract functions as: Problem: Truncate '2013-08-12 23:59:59' to '2013-08-12 23:00:00' select cast(timestamp '2013-08-12 23:59:59' as date)||' '||extract(hour from timestamp '2013-08-12 23:59:59')||':00:00'; Output: 2013-08-12 23:00:00 But this is expensive because of repeated function calls. Is there any faster and built-in function? Regards, Ravi

2 1

Count number of records returned by a subquery in monetdb
by Ravikumar Balasubramaniam 13 Aug '13

13 Aug '13

Hi, I try to execute a query like following: When I execute, select table_name from system order by table_name; no syntax error. But when I execute, select count(*) from (select table_name from system order by table_name) as count; I get syntax error, unexpected ORDER, expecting INTERSECT or EXCEPT or UNION or ')' in: "select count(*) from (select table_name from system order" What is the correct syntax? Regards, Ravi

3 2

Integrating a C++ module in MonetDB
by Adnan Agbaria 13 Aug '13

13 Aug '13

Hi All, I've implemented a module implemented in C++ for MonetDB. You can think that this module has a user-defined function. What changes should be made in Makefile.ag (of this module) to compile and build it with C++ compiler. Regards!

2 1

potential data contention in _list_find_name() during BAT binding?
by Mengmeng Chen 13 Aug '13

13 Aug '13

Hi, I'm reading the source code of MonetDB and I have a question about multi-threaded BAT binding and would appreciate it if someone could help to answer. Here is my question: When a sql_bind() MAL instruction is called, I see we have a callstack something like: mvc_bind_wrap -> mvc_bind-> mvc_bind_column -> find_sql_column -> _cs_find_name->_list_find_name In _list_find_name, we have something like: if (l && !l->ht && list_length(l) > HASH_MIN_SIZE && l->sa) { l->ht = hash_new(l->sa, list_length(l), (fkeyvalue)&base_key); for (n = l->h; n; n = n->next ) { sql_base *b = n->data; int key = base_key(b); hash_add(l->ht, key, b); } } if (l && l->ht) { int key = hash_key(name); sql_hash_e *he = l->ht->buckets[key&(l->ht->size-1)]; for (; he; he = he->chain) { sql_base *b = he->value; if (b->name && strcmp(b->name, name) == 0) return b; } return NULL; } if (l) for (n = l->h; n; n = n->next) { sql_base *b = n->data; /* check if names match */ if (name[0] == b->name[0] && strcmp(name, b->name) == 0) { return b; } } Looks like the function is trying to create a hash table for the list if called the first time. However, there is no protection against data contention. Now suppose the mitosis optimizer is turned on and we could have something like: X_157:bat[:oid,:oid] := sql.tid(X_4,"sys","lineitem",0,8); X_159:bat[:oid,:oid] := sql.tid(X_4,"sys","lineitem",1,8); X_161:bat[:oid,:oid] := sql.tid(X_4,"sys","lineitem",2,8); X_163:bat[:oid,:oid] := sql.tid(X_4,"sys","lineitem",3,8); X_165:bat[:oid,:oid] := sql.tid(X_4,"sys","lineitem",4,8); X_167:bat[:oid,:oid] := sql.tid(X_4,"sys","lineitem",5,8); X_169:bat[:oid,:oid] := sql.tid(X_4,"sys","lineitem",6,8); X_171:bat[:oid,:oid] := sql.tid(X_4,"sys","lineitem",7,8); If my understanding is correct, since these MAL instructions has no dependency in each other, they could potentially be executed in different threads simultaneously. How would MonetDB prevent data contention in _list_find_name()? Particularly the case that if thread A has created a hash table and has not yet inserted the elements while thread B tries to search through the list and thinks the hash table is ready and starts to use it. It will fail to return the correct column since the hash table is empty, right? Thanks, Mengmeng

2 3

MonetDB and Tableau
by Dimitrios Gyalistras 12 Aug '13

12 Aug '13

Hello I am new to MonetDB and I want to use it together with the "Tableau Desktop" (Version 8) software running on a Mac, later possibly also together with a Tableau Server. However, the first attempts are discouraging: when attempting to connect to a MonetDB database, the Tableau Desktop software seems to get stuck in an endless loop. Some details: I run "MonetDB-11.15.11-i686-Darwin-9" on a Mac with OS X 10.6.8. I was able to create my first MonetDB database under OS X and define a simple 10-rows test table. The Tableau software runs under Windows 7 within Parallels Desktop 8. I installed the "2013-06-19 18:20 2.4M MonetDB ODBC driver, 32 bit" under Windows, and used the Administrative Tool "Data Sources (ODBC)" provided by Windows to configure the MonetDB DSN. In Tableau I finally selected Connect to Data/Other data bases (ODBC) and I was able to select a Table. Then I pressed OK – and Tableau got stuck in the process. I also tried using a custom SQL-query (SELECT * FROM "TestTable"). Here Tableau was able to establish a live connection to the MonetDB, but when working with the table, i.e. when dragging dimensions and measures into the rows and columns shelfs, Tableau announced connection errors. Does it make sense to proceed with this project? Any infos on the following configurations would be highly appreciated: MonetDB on a Mac <--> Tableau Desktop on a Mac MonetDB on a Linux server <--> Tableau Desktop on a Mac MonetDB on a Linux server <--> Tableau Server (Windows) Thanks Dimitrios –––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––– Dimitrios Gyalistras Synergy BTC AG Buristrasse 49 CH–3006 Bern Switzerland Tel: +41 78 602 54 09 Mail: gyalistras(a)synergy.ch Web: www.synergy.ch ––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––

1 0

COPY INTO with ordered column
by Sebastian Dorok 09 Aug '13

09 Aug '13

Hi all, I have some slow queries that I want to accelerate by utilizing information about sorting. My table definition: create table genomics.grch36 (site bigint, base char(1)); I use COPY INTO to populate the table. The source file size is 40GB and contains more than 3 billion rows. I can guarantee that the data in file is ordered by site. I query the data like this: sql>select base from genomics.grch36 where site between 10000 and 10010; +------+ | base | +======+ | N | | T | | A | | A | | C | | C | | C | | T | | A | | A | | C | +------+ 11 tuples (5m 23s) I think 5 minutes seem too much for this query. Primary key or an index on 'site' don't work or at least aren't recognized in query execution. I think MonetDB would benefit when knowing that the data is ordered by site. Wouldn't it? Because I know that the data is ordered by 'site', how can I tell MonetDB about this fact to speed up the query? Any suggestions? Regards, Sebastian

2 6

Monetdb performance improvement with more RAM
by Anshuman Kumar 08 Aug '13

08 Aug '13

Hello All, We are evaluating monetdb for use as a tick database. We are storing all the data into a single table. Our table has a column for timestamp and a few other fields. We wish to run queries against it, the most useful of which is to return all entries within a given timestamp range. This query takes a long time to execute for even a 15 minute interval (about 40 to 60 seconds). We have noted that the system RAM usage goes up to the maximum (almost 8GB) while we run such queries on monetdb. We wanted to understand whether installing more RAM (say, we upgrade to 16 GB) will result in a significant performance improvement for such queries on monetdb. Also, if this is the case, we would like to know, how much RAM is monetdb optimised for? The following is the specification of our server: Intel Xeon X3430 2.4Ghz 64Bit 8Gb Ram 500Gb HDD - Raid0 Running on Windows Server 2008 R2. We are using the 20130212 build of monetdb. Thank you, Anshuman

2 1

Local, searchable mail archive
by Brandon Jackson 08 Aug '13

08 Aug '13

Would anyone be able to recommend an approach or software on either Windows or Linux that would enable me to replicate the MonetDB mail archives to my local machine to make it easer to search for answers and study topics before posting questions asked before. Even if I could get an export of the whole thing in some format that be consumed by a mail client. I did read that mailman is capable of such archives, but it seems to require a real administrative user and password to create the archive. I wouldn't expect a frequent completely up to date copy of the archive, just a backup that is closer to now and stays reasonably up to date (few months) Thanks for any suggestions. Brandon Sent from my iPhone

1 0

Re:monetdb lost column info when queryring
by integrity 08 Aug '13

08 Aug '13

sql>select * from kbms_logserver_msg t where command = 'CmdWxInfo' and logdate = '2013-08-06' and functionret <> '0' and servertime > '1375757230' limit 10; +------------+-----------+--------------+-------------+----------+------------+---------------+------------+----------------+------------+------------+ | logdate | command | commandtype | functionret | costtime | servertime | serverip | serverport | clientip | clientport | clienttype | +============+===========+==============+=============+==========+============+===============+============+================+============+============+ | 2013-08-06 | CmdWxInfo | getwxinfoall | -535 | 3032 | 1375757231 | 10.187.143.80 | 7000 | 10.153.130.228 | | | | 2013-08-06 | CmdWxInfo | getwxinfoall | -535 | 3032 | 1375757244 | 10.187.143.80 | 7000 | 10.153.130.228 | | | | 2013-08-06 | CmdWxInfo | getwxinfoall | -535 | 3030 | 1375757257 | 10.187.143.80 | 7000 | 10.153.130.229 | | | | 2013-08-06 | CmdWxInfo | getwxinfoall | -535 | 3032 | 1375757962 | 10.187.143.80 | 7000 | 10.153.130.229 | | | | 2013-08-06 | CmdWxInfo | getwxinfoall | -535 | 3030 | 1375757970 | 10.187.143.80 | 7000 | 10.153.130.228 | | | | 2013-08-06 | CmdWxInfo | getwxinfoall | -535 | 60 | 1375760693 | 10.187.143.80 | 7000 | 10.153.130.229 | | | | 2013-08-06 | CmdWxInfo | getwxinfoall | -535 | 60 | 1375760693 | 10.187.143.80 | 7000 | 10.153.130.228 | | | | 2013-08-06 | CmdWxInfo | getwxinfoall | -535 | 59 | 1375760693 | 10.187.143.80 | 7000 | 10.153.130.229 | | | | 2013-08-06 | CmdWxInfo | getwxinfoall | -535 | 63 | 1375760872 | 10.187.143.80 | 7000 | 10.153.130.229 | | | | 2013-08-06 | CmdWxInfo | getwxinfoall | -535 | 54 | 1375760872 | 10.187.143.80 | 7000 | 10.153.130.229 | | | +------------+-----------+--------------+-------------+----------+------------+---------------+------------+----------------+------------+------------+ 10 tuples (55.709ms) sql>select * from kbms_logserver_msg t where command = 'CmdWxInfo' and logdate = '2013-08-06' and functionret <> '0' and servertime = '1375757231' limit 10; +------------+-----------+--------------+-------------+----------+------------+---------------+------------+----------------+------------+------------+ | logdate | command | commandtype | functionret | costtime | servertime | serverip | serverport | clientip | clientport | clienttype | +============+===========+==============+=============+==========+============+===============+============+================+============+============+ | 2013-08-06 | CmdWxInfo | getwxinfoall | -535 | 3032 | 1375757231 | 10.187.143.80 | 7000 | 10.153.130.228 | 44328 | | +------------+-----------+--------------+-------------+----------+------------+---------------+------------+----------------+------------+------------+ 1 tuple (52.761ms) in the first query,when servertime > '1375757230' limit 10;clientport are all NULL. while in the second query, servertime = '1375757231' ,clientport appeard again. it proves that the clientport exist but just didn't appear in the query. Thanks, Meng

4 5

Determining Storage Consumption
by Sebastian Dorok 08 Aug '13

08 Aug '13

Hi all, I'm trying to figure out how much storage MonetDB (Feb2013-SP2) uses when populated with my data on a fresh database. Here is my table definition: create table genomics.grch36 (site bigint, base char(1)); I'm populating the table via COPY INTO. The source file size is 40GB and contains more than 3 billion rows. Following the suggestions on http://www.monetdb.org/Documentation/Userguide/diskspace, I get following result for the SQL query: sql>select * from storage() where "table" = 'grch36'; +----------+--------+--------+--------+----------+------------+-----------+-------------+----------+---------+--------+ | schema | table | column | type | location | count | typewidth | columnsize | heapsize | indices | sorted | +==========+========+========+========+==========+============+===========+=============+==========+=========+========+ | genomics | grch36 | site | bigint | 12/1252 | 3137459420 | 8 | 25099675360 | 0 | 0 | false | | genomics | grch36 | base | char | 12/1247 | 3137459420 | 1 | 6274918840 | 131072 | 0 | false | +----------+--------+--------+--------+----------+------------+-----------+-------------+----------+---------+--------+ I assume that 'columnsize' refers to the storage demand. I'm I right? Based on the numbers given, I assume a storage need of round about 31 GB (without any compression). Calling 'du -h' on my database directory confirms this assumption. 4,0K ./bat/14 44K ./bat/10 108K ./bat/07 476K ./bat/01 30G ./bat/12 256K ./bat/02 4,0K ./bat/03 4,0K ./bat/HC 4,0K ./bat/16 4,0K ./bat/17 12K ./bat/13 4,0K ./bat/LEFTOVERS 28K ./bat/BACKUP 4,0K ./bat/15 4,0K ./bat/11 12K ./bat/04 30G ./bat 8,0K ./sql_logs/sql 12K ./sql_logs 4,0K ./box 30G . But why is the 'columnsize' of the 'base' column something about 6GB whereas 3 billion times 1 Byte (for a 1 char) is 3GB? For the 'site' column this calculation works! Another question that comes into my mind: The number of distinct values in the 'base' column is 5. Thus, assuming a ideal dictionary encoding 3 Bit would be sufficient to encode all values. Since MonetDB uses dictionary compression, can it utilize this fact? Regards, Sebastian

1 0

SQL - syntax error in DELETE ALL FROM statement
by Radovan Bičiště 08 Aug '13

08 Aug '13

Hi, It is me again. :( I'm trying to "truncate" according to http://www.monetdb.org/Documentation/SQLreference/Updates using DELETE ALL FROM statement. But what I get is syntax error. :( ceos@lux-analytics:~$ mclient -h localhost -d zkdev1 -u drmaxtst -p 50000 password: Welcome to mclient, the MonetDB/SQL interactive terminal (Feb2013-SP3) Database: MonetDB v11.15.11 (Feb2013-SP3), 'mapi:monetdb://lux-analytics:50000/zkdev1' Type \q to quit, \? for a list of available commands auto commit mode: on sql>delete all from crmkarta; syntax error, unexpected ALL, expecting FROM in: "delete all" sql> Thank you for any hints. Best regards, Radovan -- __________________________ Radovan Bičiště

3 5

rpm for monetdb
by Sanjoy Bose 07 Aug '13

07 Aug '13

Hi, I am new to monetdb and considering using it for our Analytics need. Is there any rpm available for Centos 5 / Centos 6 RedHat 5 / Redhat 6 for its latest release or do I have to build from source ? Thanks, Sanjoy

4 6

SQL - temporary table data inside stored procedure
by Radovan Bičiště 07 Aug '13

07 Aug '13

Hello, I must be doing something horribly wrong. I have a stored procedure where I have temporary table declared, something like: DECLARE TABLE t1tmp (id bigint); Then in the same procedure I do INSERT INTO t1tmp SELECT .....; When I do Count(*) from temporary table in the procedure it returns 0. Fun starts after procedure called. When call is done temporary table is full of data as expected. But it seems the data is nowhere to be found when I look for them inside the procedure. Am I missing something again? Thanks for any hints, Radovan TEST CASE: CREATE PROCEDURE test1() BEGIN DECLARE TABLE t1tmp (bigint); INSERT INTO t1tmp SELECT id FROM t1; DECLARE v_cont BIGINT; -- returns 0 SELECT COUNT(*) INTO v_count FROM t1tmp; END; CALL test1; -- actually returns the data SELECT * FROM t1tmp; -- __________________________ Radovan Bičiště ceos data s.r.o. třída SNP 402/48 500 03 Hradec Králové Czech Republic

2 2

Data Vault:SyntaxException
by integrity 06 Aug '13

06 Aug '13

hi all, What's the correct way to use FIT DATA VAULT in mal? I got the following errors and still havent try out the right syntax ...... [svomhpc@localhost /home/data2]$>/home/data1/opt/MonetDB.fits/bin/mclient -lmal -dmydb Welcome to mclient, the MonetDB interactive terminal (unreleased) Type \q to quit, \? for a list of available commands mal>?fits.attach() pattern fits.attach(fname:str):void address FITSattach; comment Open a FITS file and return catalog of the table HDUs mal>call fits.attach('/home/data3/gwacdata/20130111_177d550752_60d851283-530.fit'); MAPI = (monetdb) /tmp/.s.monetdb.50000 QUERY = call fits.attach('/home/data3/gwacdata/20130111_177d550752_60d851283-530.fit'); ERROR = !SyntaxException:parseError:call fits.attach('/home/data3/gwacdata/20130111_177d550752_60d851283-530.fit'); !SyntaxException:parseError: ^';' expected mal> mal> mal>call fitsattach('/home/data3/gwacdata/20130111_177d550752_60d851283-530.fit'); MAPI = (monetdb) /tmp/.s.monetdb.50000 QUERY = call fitsattach('/home/data3/gwacdata/20130111_177d550752_60d851283-530.fit'); ERROR = !SyntaxException:parseError:call fitsattach('/home/data3/gwacdata/20130111_177d550752_60d851283-530.fit'); !SyntaxException:parseError: ^';' expected mal> mal>call FITSattach('/home/data3/gwacdata/20130111_177d550752_60d851283-530.fit'); MAPI = (monetdb) /tmp/.s.monetdb.50000 QUERY = call FITSattach('/home/data3/gwacdata/20130111_177d550752_60d851283-530.fit'); ERROR = !SyntaxException:parseError:call FITSattach('/home/data3/gwacdata/20130111_177d550752_60d851283-530.fit'); !SyntaxException:parseError: ^';' expected Thanks! Meng

4 15

Re: How to use Data Vault with FITS files?
by integrity 05 Aug '13

05 Aug '13

Hi Ying, The thesis doesn't have instructions on how to use it....or it doesn't describe it very clearly maybe. Bests, Meng ------------------ Original ------------------ From: "Ying Zhang"<Y.Zhang(a)cwi.nl>; Date: Mon, Aug 5, 2013 04:18 PM To: "Communication channel for MonetDB users"<users-list(a)monetdb.org>; Subject: Re: How to use Data Vault with FITS files? Hai Meng, I already sent you the master thesis on FITS data vault. Didn't you receive it? Regards, Jennie On Mon, Aug 05, 2013 at 03:52:50PM +0800, integrity wrote: > Hi all, > > Could you give a tutorial of Data vault with FITS files, i searched the > moentdb.org and google, but didn't find the step-by-step tutorials. > > Thanks! > Meng > > _______________________________________________ > users-list mailing list > users-list(a)monetdb.org > http://mail.monetdb.org/mailman/listinfo/users-list _______________________________________________ users-list mailing list users-list(a)monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list .

2 1

Re: How to use Data Vault with FITS files?
by integrity 05 Aug '13

05 Aug '13

Thanks very much Stefan! Does ' configure MonetDB with "--enable-fits" ' mean I have to re-install MonetDB? If I re-install it, can i still use my current db-farm? I have loaded a large catalog into it for a long time... Regards, Meng ------------------ Original ------------------ From: "Stefan Manegold"<Stefan.Manegold(a)cwi.nl>; Date: Mon, Aug 5, 2013 04:13 PM To: "Communication channel for MonetDB users"<users-list(a)monetdb.org>; Subject: Re: How to use Data Vault with FITS files? Hi Meng, to get the MonetDB FITS support, you need to have the cfitsio-dev[el] package installed and you need to configure MonetDB with "--enable-fits". However, while the respective code is more than 2 years old, I just realize that there is no (user-)documentation for it, and so far, support for fits files is only available in MAL, but not (yet?) in SQL. Since the original author of that code is no longer with out group, I currently cannot estimate, when we will have the resources to make this experimental code user-/production-ready. If you are familiar with MAL, please have a look at $ mclient -lmal Welcome to mclient, the MonetDB interactive terminal (unreleased) Type \q to quit, \? for a list of available commands mal>?fits.*() pattern fits.attach(fname:str):void address FITSattach; comment Open a FITS file and return catalog of the table HDUs pattern fits.export(tablename:str):void address FITSexportTable; comment Export a table to a FITS file command fits.fitstest(filename:str):int address FITStest; comment Returns the type of first extension in the FITS file filename pattern fits.load(tablename:str):void address FITSloadTable; comment Load a FITS table from an attached file pattern fits.listdirpattern(dirname:str,pattern:str):void address FITSdirpat; comment Attach all FITS file in the directory, giving a pattern pattern fits.listdir(dirname:str):void address FITSdir; comment Attach all FITS files in the directory mal> And if you're familiar with the MonetDB code base, you find the respective code in sql/backends/monet5/vaults/*fits* Best, Stefan ----- Original Message ----- > Hi all, > > > Could you give a tutorial of Data vault with FITS files, i searched the > moentdb.org and google, but didn't find the step-by-step tutorials. > > > Thanks! > Meng > _______________________________________________ > users-list mailing list > users-list(a)monetdb.org > http://mail.monetdb.org/mailman/listinfo/users-list > -- | Stefan.Manegold(a)CWI.nl | DB Architectures (DA) | | www.CWI.nl/~manegold/ | Science Park 123 (L321) | | +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) | _______________________________________________ users-list mailing list users-list(a)monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list .

2 1

How to use Data Vault with FITS files?
by integrity 05 Aug '13

05 Aug '13

Hi all, Could you give a tutorial of Data vault with FITS files, i searched the moentdb.org and google, but didn't find the step-by-step tutorials. Thanks! Meng

4 3

How to use Data Vault with FITS files?
by integrity 04 Aug '13

04 Aug '13

Hi all, Could you give a tutorial of Data vault with FITS files, i searched the moentdb.org and google, but didn't find the step-by-step tutorials. Thanks! Meng

1 0

Scale Factor 2000 TPCH Database?
by Stephen P. Morgan 02 Aug '13

02 Aug '13

Has anyone successfully created a scale factor 2000 TPCH database with MonetDB? We're having some problems trying to do so. We don't have enough disk space to hold both the data (created by dbgen) and the MonetDB database, so we have to create the database in two steps. (Create the first half of the data as dbgen table files, load it, delete the dbgen files, create the second half of the data as dbgen table files, load it, then delete those files.) The problem shows up when loading the second half of the data: the mserver crashes and won't restart. This is what the dbfarm/merovingian.log says: 2013-08-02 18:42:19 MSG merovingian[39857]: database 'q1_s2000_n2' (39920) has crashed (dumped core) 2013-08-02 18:42:19 ERR merovingian[39857]: client error: database 'q1_s2000_n2' has crashed after starting, manual intervention needed, check monetdbd's logfile for details 2013-08-02 18:42:19 MSG merovingian[39857]: database 'q1_s2000_n2' has crashed after start on 2013-08-02 18:42:19, attempting restart, up min/avg/max: 0s/0s/0s, crash average: 1.00 0.50 0.17 (5-0=5) Other databases on the same system are still running. We're running Feb13 SP1 with minor local modifications that are not coming into play at this point. We're running on a large Linux server. Is there anything we can look at to help diagnose the problem? Thanks, Steve Morgan

3 3

Copy From SQL server
by soumik ghosh 02 Aug '13

02 Aug '13

Hello All, Could someone point me to an example of copying a table from SQL Server to MonetDB over a JDBC or ODBC bridge? Is this possible at all? Thanks Soumik

5 7

Background processes in MonetDB
by DrRock 01 Aug '13

01 Aug '13

Dear fellows, we installed MonetDB a few weeks ago on a Linux Machine (8 cores, 32GB RAM) and our overall experience is very good. So far, we are testing Monet with different ammounts of data, roughly peaking at 250GB. After loading plenty of data into the database or after interrupting a query, Monet feels somehow slow for a certain period of time. Another observation we made is that diskspace remains occupied after the deletion of all tables in the database for a certain time. Further, indices can be created, although this seems not to be really essential as MonetDB provides some kind of own intelligence, optimizing queries/ tables by themself. All of mentioned observations point at some kind of background processes and optimizing routines. Is there a way to modify the behaviour of MonetDB (Such as providing diskspace instantly after deletion of tables)? Is it possible to switch these processes off? Best regards, DrRock

1 0

user defined directory
by Adnan Agbaria 31 Jul '13

31 Jul '13

I'd like to add a new directory named CAP at MonetDBRoot/sql/backends/monet5 The new directory contains cap.c, cap.h, cap.mal, 80_cap.mal, 80_cap.sql, and makefile.ag I modified MonetDBRoot/sql/backends/monet5/Makefile.ag at: SUBDIRS = NOT_WIN32?vaults UDF CAP LSST ENABLE_DATACELL?datacell However, when I run MonetDBRoot/bootstrap, i got the following error message: ... /home/adnan/MonetDB-11.15.11/sql/backends/monet5 /home/adnan/MonetDB-11.15.11/sql/backends/monet5/vaults /home/adnan/MonetDB-11.15.11/sql/backends/monet5/UDF /home/adnan/MonetDB-11.15.11/sql/backends/monet5/CAP Traceback (most recent call last): File "buildtools/autogen/autogen.py", line 157, in <module> (InstallList, DocList, OutList) = main(topdir, topdir, automake, []) File "buildtools/autogen/autogen.py", line 147, in main main(d, topdir, automake, incdirsmap, conditional + cond) File "buildtools/autogen/autogen.py", line 147, in main main(d, topdir, automake, incdirsmap, conditional + cond) File "buildtools/autogen/autogen.py", line 147, in main main(d, topdir, automake, incdirsmap, conditional + cond) File "buildtools/autogen/autogen.py", line 147, in main main(d, topdir, automake, incdirsmap, conditional + cond) File "buildtools/autogen/autogen.py", line 132, in main read_makefile(p, cwd) File "buildtools/autogen/autogen.py", line 96, in read_makefile p.parse(token, lineno, line) File "buildtools/autogen/autogen.py", line 71, in parse v = self.stack[last] IndexError: list index out of range Any idea please?

3 2

Re: Monetdb copy binary time varys very much!
by integrity 30 Jul '13

30 Jul '13

Hi Stefan, Just as you said ,we issue the next copy into as soon as the previous ended. What would be different if i mimic the real-world scenario and respect these gaps between each two consecutive copy into's? Thank! Meng ------------------ Original ------------------ From: "Stefan Manegold"<Stefan.Manegold(a)cwi.nl>; Date: Mon, Jul 29, 2013 02:45 PM To: "Communication channel for MonetDB users"<users-list(a)monetdb.org>; Subject: Re: Monetdb copy binary time varys very much! Hi Meng, My analysis was mainly an (educated) guess of what (most probably) happens. To be sure, you need to profile your system in detail, e.g., monitor CPU and I/O activities. Having said that, with less RAM, you might force the system to write the loaded data to disk instantly with each copy into, making each copy into slower than merely loading the data into disk, but the worst case might become better since each time less data has to we written than with more RAM. Again, this is my guess what happening; the behaviour you observe might be caused by something else; only a detailed profiling analysis can tell. Also, if you eventually want to query your 300+ GB (or even more?) efficiently, you might want to have a suitable system, in particular sufficient RAM. (Would you mind sharing the hardware characteristics of your machine?). Moreover, what was the time gap between two consecutive copy into's in your experiment, i.e., did you issue the next copy into as soon as the previous ended? Does this mimic the your "real-world" scenario realistically? Or would there be a time gap between to copy into's in reality? I recall you mentioned some 15 seconds? If so, you should rerun your experiment respecting these gaps between each two consecutive copy into's. Best, Stefan ----- Original Message ----- > Hi Stefan, > > > Due to all the data in RAM having to be written to disk when it's full, if > the RAM grows larger and i don't change hard disk, the data volume become > larger, will it make the COPY BINARY INTO much slower compared to the small > RAM? > > > Contrarily, if the RAM get smaller, will it make the COPY BINARY INTO > quicker? > > > I just want to lower the peak value of COPY, the 10+ seconds every about a > hundred times. > > > For the most time COPY BINARY INTO took only less than 1 second, is it > because they are written to RAM not disk when the RAM is not full? if so, > can i write to disk more frequently before the disk is full so i could lower > down the peak value of COPY. > > > > > Regrads, > Meng > > > ------------------ Original ------------------ > From: "Stefan Manegold"<Stefan.Manegold(a)cwi.nl>; > Date: Fri, Jul 26, 2013 11:10 PM > To: "Communication channel for MonetDB users"<users-list(a)monetdb.org>; > > Subject: Re: Monetdb copy binary time varys very much! > > > > Hi, > > I'm not sure whether I understand correct what you are doing. > > If you repeat the test 1000 times, does that mean that (1) 10000 times you > re-create (or empty) the table and thus always copy into an empty table, or > (2) 10000 times you copy into the same (growing) table, i.e., resulting in a > table of 10,000 times 200,000 rows, i.e., 2,000,000,000 rows, i.e., ~16 GB > per column, i.e., ~336 GB in total? > > (Only) in case (1) the binary files to be imported are simply moved at zero > costs. > In case (2), only the first copy into (into the empty table) can simply move > the files at zero costs; all subsequent copy into (into a no longe empty > table) must copy the files (and delete them afterwards to mimic the same > behavior as the initial copy into), which is of cause not "for free". > > Also, as Martin explained, unless your machine has (significantly) more RAM > than the ~336 GB of data you copy, the data needs to be written to disk in > between, making some copy into's "slower" than others. There's not much to > do about that other than (a) getting more RAM, or (b) improving I/O > bandwidth by using either a high performance RAID or SSDs. > > Stefan > > . > _______________________________________________ > users-list mailing list > users-list(a)monetdb.org > http://mail.monetdb.org/mailman/listinfo/users-list > -- | Stefan.Manegold(a)CWI.nl | DB Architectures (DA) | | www.CWI.nl/~manegold/ | Science Park 123 (L321) | | +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) | _______________________________________________ users-list mailing list users-list(a)monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list .

2 1

SQL: AVG function works in SQL but it does not in procedure
by Radovan Bičiště 29 Jul '13

29 Jul '13

Hello, We are unable to solve the following problem. This SQL runs fine as standalone: INSERT INTO crmkartastitek ( cislo_karty, stitek_id, stitek_nazev, stitek_upresneni, stitek_hodnota_decimal ) select cislo_karty,170,'KEO','',round(avg(obrat),2) from (select ckp.cislo_karty, ckp.id_expedice, sum(ckp.pcena_sd) as obrat from crmkartapohyby ckp join crmkartaaktivita ck on ckp.cislo_karty = ck.cislo_karty where ckp.pohyb in ('VR', 'VV') and ck.aktivita between 1 and 3 -- omezení na LM a L3M group by ckp.cislo_karty, ckp.id_expedice ) as exp_pripady group by cislo_karty; But when wrapped in procedure raises an error as follows: CREATE PROCEDURE crm_segm_keo_rl(p_interval INT, p_datum timestamp) BEGIN -- hlavička procedury DECLARE v_proc VARCHAR(250); SET v_proc = 'crm_segm_kpn_rl'; -- nastav id pravidla DECLARE v_stitek_id int; DECLARE v_stitek_nazev,v_stitek_upresneni VARCHAR(200); -- nastav štítek SET v_stitek_id = 170; SELECT nazev,upresneni INTO v_stitek_nazev,v_stitek_upresneni FROM crmregstitek WHERE stitek_id = v_stitek_id; -- proveď výpočet pouze pokud spouštím měsíční přepočet IF p_interval = crm_segm_vrat_spusteni(v_stitek_id) THEN INSERT INTO crmkartastitek ( cislo_karty, stitek_id, stitek_nazev, stitek_upresneni, stitek_hodnota_decimal ) select cislo_karty,v_stitek_id,v_stitek_nazev,v_stitek_upresneni,round(avg(obrat),2) from (select ckp.cislo_karty, ckp.id_expedice, sum(ckp.pcena_sd) as obrat from crmkartapohyby ckp join crmkartaaktivita ck on ckp.cislo_karty = ck.cislo_karty where ckp.pohyb in ('VR', 'VV') and ck.aktivita between 1 and 3 -- omezení na LM a L3M group by ckp.cislo_karty, ckp.id_expedice ) as exp_pripady group by cislo_karty; END IF; END; Procedure is created fine. call crm_segm_keo_rl(3,timestamp '2013-07-01 00:00:00'); Causes: An error occurred when executing the SQL command: call crm_segm_keo_rl(3,timestamp '2013-07-01 00:00:00') TypeException:user.crm_segm_keo_rl[362]:'aggr.subavg' undefined in: _546:bat[:any,:dbl] := aggr.subavg(_543:bat[:oid,:dbl], _209:bat[:oid,:oid], r1_209:bat[:oid,:oid], _535:bit) [SQL State=22000] Next: TypeException:user.s4_24[5]:'user.crm_segm_keo_rl' undefined in: _9:void := user.crm_segm_keo_rl(_5:int, _7:timestamp) [SQL State=22000] Next: program contains errors [SQL State=39000] Are we missing something ? Thank you, Radovan -- Radovan Biciste Hradec Kralove Czech Republic

1 0

Re: Monetdb copy binary time varys very much!
by integrity 29 Jul '13

29 Jul '13

Yes, I use the server only myself only to run the MonetDB test and notify other users not to use the server in advance. and I reboot the server before tests . ------------------ Original ------------------ From: "Ying Zhang"<Y.Zhang(a)cwi.nl>; Date: Mon, Jul 29, 2013 04:28 PM To: "Communication channel for MonetDB users"<users-list(a)monetdb.org>; Subject: Re: Monetdb copy binary time varys very much! On Jul 29, 2013, at 05:57, integrity wrote: > Hi Stefan, > > Due to all the data in RAM having to be written to disk when it's full, if the RAM grows larger and i don't change hard disk, the data volume become larger, will it make the COPY BINARY INTO much slower compared to the small RAM? > > Contrarily, if the RAM get smaller, will it make the COPY BINARY INTO quicker? > > I just want to lower the peak value of COPY, the 10+ seconds every about a hundred times. Hai Meng, Have you made sure that there are no processes running other than the ones strictly necessary? Have you closed all user applications, such as mail client, browser, and chat clients? Regards, Jennie > > For the most time COPY BINARY INTO took only less than 1 second, is it because they are written to RAM not disk when the RAM is not full? if so, can i write to disk more frequently before the disk is full so i could lower down the peak value of COPY. > > > Regrads, > Meng > > ------------------ Original ------------------ > From: "Stefan Manegold"<Stefan.Manegold(a)cwi.nl>; > Date: Fri, Jul 26, 2013 11:10 PM > To: "Communication channel for MonetDB users"<users-list(a)monetdb.org>; > Subject: Re: Monetdb copy binary time varys very much! > > Hi, > > I'm not sure whether I understand correct what you are doing. > > If you repeat the test 1000 times, does that mean that (1) 10000 times you > re-create (or empty) the table and thus always copy into an empty table, or > (2) 10000 times you copy into the same (growing) table, i.e., resulting in a > table of 10,000 times 200,000 rows, i.e., 2,000,000,000 rows, i.e., ~16 GB > per column, i.e., ~336 GB in total? > > (Only) in case (1) the binary files to be imported are simply moved at zero > costs. > In case (2), only the first copy into (into the empty table) can simply move > the files at zero costs; all subsequent copy into (into a no longe empty > table) must copy the files (and delete them afterwards to mimic the same > behavior as the initial copy into), which is of cause not "for free". > > Also, as Martin explained, unless your machine has (significantly) more RAM > than the ~336 GB of data you copy, the data needs to be written to disk in > between, making some copy into's "slower" than others. There's not much to > do about that other than (a) getting more RAM, or (b) improving I/O > bandwidth by using either a high performance RAID or SSDs. > > Stefan > > . > _______________________________________________ > users-list mailing list > users-list(a)monetdb.org > http://mail.monetdb.org/mailman/listinfo/users-list _______________________________________________ users-list mailing list users-list(a)monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list .

1 0

Re: Monetdb copy binary time varys very much!
by integrity 29 Jul '13

29 Jul '13

Hi Stefan, Due to all the data in RAM having to be written to disk when it's full, if the RAM grows larger and i don't change hard disk, the data volume become larger, will it make the COPY BINARY INTO much slower compared to the small RAM? Contrarily, if the RAM get smaller, will it make the COPY BINARY INTO quicker? I just want to lower the peak value of COPY, the 10+ seconds every about a hundred times. For the most time COPY BINARY INTO took only less than 1 second, is it because they are written to RAM not disk when the RAM is not full? if so, can i write to disk more frequently before the disk is full so i could lower down the peak value of COPY. Regrads, Meng ------------------ Original ------------------ From: "Stefan Manegold"<Stefan.Manegold(a)cwi.nl>; Date: Fri, Jul 26, 2013 11:10 PM To: "Communication channel for MonetDB users"<users-list(a)monetdb.org>; Subject: Re: Monetdb copy binary time varys very much! Hi, I'm not sure whether I understand correct what you are doing. If you repeat the test 1000 times, does that mean that (1) 10000 times you re-create (or empty) the table and thus always copy into an empty table, or (2) 10000 times you copy into the same (growing) table, i.e., resulting in a table of 10,000 times 200,000 rows, i.e., 2,000,000,000 rows, i.e., ~16 GB per column, i.e., ~336 GB in total? (Only) in case (1) the binary files to be imported are simply moved at zero costs. In case (2), only the first copy into (into the empty table) can simply move the files at zero costs; all subsequent copy into (into a no longe empty table) must copy the files (and delete them afterwards to mimic the same behavior as the initial copy into), which is of cause not "for free". Also, as Martin explained, unless your machine has (significantly) more RAM than the ~336 GB of data you copy, the data needs to be written to disk in between, making some copy into's "slower" than others. There's not much to do about that other than (a) getting more RAM, or (b) improving I/O bandwidth by using either a high performance RAID or SSDs. Stefan .

3 2

Deleting millions of rows every night
by Dennis Pallett 29 Jul '13

29 Jul '13

Hello all, We are going to be using MonetDB as a data mart between our data warehouse and our front-end applications and are planning to insert and delete several millions of rows from MonetDB every night as part of our data update cycle. We've noticed that MonetDB doesn't support the TRUNCATE statement so we're deleting all the data with DELETE FROM table. But is this the best way to delete millions of rows every night? Won't performance or free disk space degrade over time? Best regards, Dennis Pallett

2 1

MonetDB and SQLWorkbench
by Satoshi Nagayasu 29 Jul '13

29 Jul '13

Hi, I have hacked SQLWorkbench to work with MonetDB. SQLWorkbench is a detabase-independent GUI query tool, written in Java. http://www.sql-workbench.net/ To support MonetDB in SQLWorkbench, a new entry needs to be added in DatabaseTemplates.xml, which is contained in sqlworkbench.jar. This modifed files are available: http://www.uptime.jp/downloads/monetdb/SQLWorkbench/ It seems working well so far, and this patch is supposed to be contributed to the SQLWorkbench upstream. If you are looking for a nice GUI tool to issue queries to MonetDB, take a look. Regards, -- Satoshi Nagayasu <snaga(a)uptime.jp> Uptime Technologies, LLC. http://www.uptime.jp

3 3

Re: Monetdb copy binary time varys very much!
by integrity 28 Jul '13

28 Jul '13

Hi Ying, As you mentioned the 2nd case, I just interchanged create 21 files and excute COPY INTO. The plot times include only the execution time of COPY INTO. In 15 seconds, the data volumn i have to process is just similar to storing 200000 rows,21 columns in this simulation program. Thanks! Meng ------------------ Original ------------------ From: "Ying Zhang"<Y.Zhang(a)cwi.nl>; Date: Sun, Jul 28, 2013 00:47 AM To: "Communication channel for MonetDB users"<users-list(a)monetdb.org>; Subject: Re: Monetdb copy binary time varys very much! Hai Meng, Since you repeat the COPY INTO statement 10000 times, I wonder how exactly did you it and measure the time. For instance, did you first create _all_ necessary files (i.e., 10000 x 21 files) and execute 10000 COPY INTO consecutively? Or did you interchange create 21 files and execute COPY INTO? In the second case, are the times in your plots only the execution time of COPY INTO, or do they also include the time to create the files? Again, in the second case, have you also measured the time of creating the files? You have ever mentioned that the total available execution time in your application is 15 seconds. How many data do you need to process within this time? Regards, Jennie On Jul 26, 2013, at 10:40, integrity wrote: > Hi dear all, > > I did a test of insert 2,000,000,000 rows into MonetDB with "COPY BINARY INTO FROM binary_file", > in this test > 1. i generate 21 files, each file represents a table column and has 200000 rows. > I created them with rand() number and use fwrite() binary write method. > > the table creation sql command is : > create table tmatch(id bigint,a float,b float,c float, d float, e float, > f float,g float,h float, i float,j float, > k float,l float,m float, n float,o float, > p float,q float,r float, s float,t float); > the table has 21 columns,each column has 8 bytes, so each column file is c1=200000*21*8 Byte= 268800000 Byte=3.2MB > 2. I use "COPY BINARY INTO FROM above_binary" to load each binary file into tmatch. > The test was run 10000 times repeatedly. > > the average time of 10000 times is only 1.0635589558955727, > but when at 9043th time, it cost 227m36.136 ,some times later the time value increase to a large number, is it because of flush data from cache into database after the cache is full? > The problem is since we have to control the total process time within 15 seconds , I am wondering if you can help me reduce the maximum time to a lower point? > > > Thanks very much! > Meng > > _______________________________________________ > users-list mailing list > users-list(a)monetdb.org > http://mail.monetdb.org/mailman/listinfo/users-list _______________________________________________ users-list mailing list users-list(a)monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list .

2 1

Monetdb copy binary time varys very much!
by integrity 27 Jul '13

27 Jul '13

Hi dear all, I did a test of insert 2,000,000,000 rows into MonetDB with "COPY BINARY INTO FROM binary_file", in this test 1. i generate 21 files, each file represents a table column and has 200000 rows. I created them with rand() number and use fwrite() binary write method. the table creation sql command is : create table tmatch(id bigint,a float,b float,c float, d float, e float, f float,g float,h float, i float,j float, k float,l float,m float, n float,o float, p float,q float,r float, s float,t float); the table has 21 columns,each column has 8 bytes, so each column file is c1=200000*21*8 Byte= 268800000 Byte=3.2MB 2. I use "COPY BINARY INTO FROM above_binary" to load each binary file into tmatch. The test was run 10000 times repeatedly. the average time of 10000 times is only 1.0635589558955727, but when at 9043th time, it cost 227m36.136 ,some times later the time value increase to a large number, is it because of flush data from cache into database after the cache is full? The problem is since we have to control the total process time within 15 seconds , I am wondering if you can help me reduce the maximum time to a lower point? Thanks very much! Meng

4 3

Re: Monetdb copy binary time varys very much!
by integrity 26 Jul '13

26 Jul '13

you mean change hardware?Is there any software resolution? ------------------ Original ------------------ From: "Martin Kersten"<martin(a)monetdb.org>; Date: Fri, Jul 26, 2013 10:52 PM To: "Communication channel for MonetDB users"<users-list(a)monetdb.org>; Subject: Re: Monetdb copy binary time varys very much! see http://en.wikipedia.org/wiki/Solid-state_drive On 7/26/13 4:48 PM, Angelasweet wrote: Yes, time is the biggest issue to our project, I cannot find SSD, could you please explain the SSD concept? Thanks! _______________________________________________ users-list mailing list users-list(a)monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

1 0

Re: Monetdb copy binary time varys very much!
by integrity 26 Jul '13

26 Jul '13

Is it possible to set the cache to low down the maximum copy time? ------------------ Original ------------------ From: "Martin Kersten"<Martin.Kersten(a)cwi.nl>; Date: Fri, Jul 26, 2013 10:13 PM To: "users-list"<users-list(a)monetdb.org>; Subject: Re: Monetdb copy binary time varys very much! Oops, correct, for binary copies it does not recognize it On 7/26/13 4:11 PM, Angelasweet wrote: > Hi Martin, > I follow you advise, and there is error as follows: > > ERROR = !COPY INTO: cannot pass number of records when using binary COPY > INTO in: "copy 200000 records binary into tmatch > from('/data/writeBinaryWM/id','/data/writeBinaryWM/0','/data/writeBinaryWM/1','/data/writeBinaryWM/2','/data/writeBinaryWM/3','/data/writeBinaryWM/4', > '/data/writeBinaryWM/5','/data/writeBinaryWM/6','/data/writeBinaryWM/7','/data/writeBinaryWM/8','/data/writeBinaryWM/9','/data/writeBinaryWM/10', '/data/writeBinaryWM/11', '/data/writeBinaryWM/12', '/data/writeBinaryWM/13', '/data/writeBinaryWM/14', '/data/writeBinaryWM/15', '/data/writeBinaryWM/16', '/data/writeBinaryWM/17', '/data/writeBinaryWM/18', '/data/writeBinaryWM/19' )" > !current transaction is aborted (please ROLLBACK) > > > 2013/7/26 Martin Kersten <Martin.Kersten(a)cwi.nl > <mailto:Martin.Kersten@cwi.nl>> > > This runs shows expected flushing. > > You might get somewhat better performance when you follow > the advice in > http://www.monetdb.org/__Documentation/Cookbooks/__SQLrecipes/LoadingBulkDa… > <http://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/LoadingBulkData> > e.g. use COPY 44000000 RECORDS .... > > The COPY INTO forms mentioned so far share one important drawback. > When a lot of data is to be inserted, the server doesn't know how > much memory to allocate for the tables, and so will likely allocate > too little. This means that during the insertion process, the server > has to grow the allocated memory area. This can be an expensive > operation. Therefore, it is better to give the server a count of > how many records are to be inserted: > > COPY n RECORDS INTO table FROM 'file'; > > Here n should be a number that is at least as large as the actual > number of records to be inserted. If the file contains more than n > records, only n will be inserted, if the file contains fewer, all > values will be inserted. Giving a higher number is especially > useful if multiple COPY INTO queries are to be done on the same > table. The first COPY INTO, when the table is still empty, should > be the total count of to-be-inserted values so that the server will > allocate enough memory when the tables are first created (they are > only really created once data is inserted). > On 7/26/13 10:58 AM, integrity wrote: > > > > ------------------ Original ------------------ > *From: * "Martin Kersten"<Martin.Kersten(a)cwi.nl > <mailto:Martin.Kersten@cwi.nl>__>; > *Date: * Fri, Jul 26, 2013 04:46 PM > *To: * "users-list"<users-list(a)__monetdb.org > <mailto:users-list@monetdb.org>>; > *Subject: * Re: Monetdb copy binary time varys very much! > > > Hi, > You should plot the times of each of your runs to see the trend. > This single run may be an outlier, which could come from anything > in your system environment. Even a seemingly harmless concurrent > program using significant memory could compete with MonetDB. > > And indeed, at some point you will see disk IO. > > > regards, Martin > > > > > _________________________________________________ > users-list mailing list > users-list(a)monetdb.org <mailto:users-list@monetdb.org> > http://mail.monetdb.org/__mailman/listinfo/users-list > <http://mail.monetdb.org/mailman/listinfo/users-list> > > _________________________________________________ > users-list mailing list > users-list(a)monetdb.org <mailto:users-list@monetdb.org> > http://mail.monetdb.org/__mailman/listinfo/users-list > <http://mail.monetdb.org/mailman/listinfo/users-list> > > > > > _______________________________________________ > users-list mailing list > users-list(a)monetdb.org > http://mail.monetdb.org/mailman/listinfo/users-list > _______________________________________________ users-list mailing list users-list(a)monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list .

3 3

Re: Monetdb copy binary time varys very much!
by integrity 26 Jul '13

26 Jul '13

------------------ Original ------------------ From: "Martin Kersten"<Martin.Kersten(a)cwi.nl>; Date: Fri, Jul 26, 2013 04:46 PM To: "users-list"<users-list(a)monetdb.org>; Subject: Re: Monetdb copy binary time varys very much! Hi, You should plot the times of each of your runs to see the trend. This single run may be an outlier, which could come from anything in your system environment. Even a seemingly harmless concurrent program using significant memory could compete with MonetDB. And indeed, at some point you will see disk IO. regards, Martin

3 3

remote.exec()
by Stephen P. Morgan 26 Jul '13

26 Jul '13

I have a question about remote.exec(). Suppose multiple (say N) remote machines call remote.exec() to start a function on a given local machine. What happens if N is greater than the number of threads supported by the local machine? Are the remote.execs queued until there is a thread available to execute them, or are they simply not started? If the letter, how can one tell whether a remote.exec was run? I have another question abut remote.get() transferring a BAT from one machine to another. Is there a limit on the size of a BAT that can be transferred by a single call to remote.get()? If the BAT is very large, is it paged through disk, or is it alsways transferred directly from memory to memory? Thanks, Steve Morgan

4 5

Re: Monetdb copy binary time varys very much!
by integrity 26 Jul '13

26 Jul '13

1 0

Re: Monetdb copy binary time varys very much!
by integrity 26 Jul '13

26 Jul '13

1 0

Re: Monetdb copy binary time varys very much!
by integrity 26 Jul '13

26 Jul '13

------------------ Original ------------------ From: "Martin Kersten"<Martin.Kersten(a)cwi.nl>; Date: Fri, Jul 26, 2013 04:46 PM To: "users-list"<users-list(a)monetdb.org>; Subject: Re: Monetdb copy binary time varys very much! Hi, You should plot the times of each of your runs to see the trend. This single run may be an outlier, which could come from anything in your system environment. Even a seemingly harmless concurrent program using significant memory could compete with MonetDB. And indeed, at some point you will see disk IO. regards, Martin

1 0

Re: Monetdb copy binary time varys very much!
by integrity 26 Jul '13

26 Jul '13

------------------ Original ------------------ From: "Martin Kersten"<Martin.Kersten(a)cwi.nl>; Date: Fri, Jul 26, 2013 04:46 PM To: "users-list"<users-list(a)monetdb.org>; Subject: Re: Monetdb copy binary time varys very much! Hi, You should plot the times of each of your runs to see the trend. This single run may be an outlier, which could come from anything in your system environment. Even a seemingly harmless concurrent program using significant memory could compete with MonetDB. And indeed, at some point you will see disk IO. regards, Martin On 7/26/13 10:40 AM, integrity wrote: > Hi dear all, > > I did a test of insert 2,000,000,000 rows into MonetDB with "COPY BINARY > INTO FROM binary_file", > in this test > 1. i generate 21 files, each file represents a table column and has > 200000 rows. > I created them with rand() number and use fwrite() binary write method. > > the table creation sql command is : > *create table tmatch(id bigint,a float,b float,c float, d float, e float,* > *f float,g float,h float, i float,j float,* > *k float,l float,m float, n float,o float,* > *p float,q float,r float, s float,t float);* > the table has 21 columns,each column has 8 bytes, so each column file is > c1=200000*21*8 Byte= 268800000 Byte=3.2MB > 2. I use "COPY BINARY INTO FROM above_binary" to load each binary file > into tmatch. > The test was run 10000 times repeatedly. > > the average time of 10000 times is only 1.0635589558955727, > but when at 9043th time, it cost 227m36.136 ,some times later the time > value increase to a large number, is it because of flush data from > cache into database after the cache is full? > The problem is since we have to control the total process time within 15 > seconds , I am wondering if you can help me reduce the maximum time to a > lower point? > > > Thanks very much! > Meng > > > > _______________________________________________ > users-list mailing list > users-list(a)monetdb.org > http://mail.monetdb.org/mailman/listinfo/users-list > _______________________________________________ users-list mailing list users-list(a)monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list .

1 0

ALTER in procedure
by Radovan Bičiště 23 Jul '13

23 Jul '13

Hello, This is my first post here. I tried to search the archive but did not find anything related. We build application and would like disable foreign keys in a procedure. Something like: CREATE PROCEDURE crmkartaaktivita_drop_cons() BEGIN -- hlavička procedury DECLARE v_proc VARCHAR(250); SET v_proc = 'crmkartaaktivita_drop_cons'; -- drop ALTER TABLE crmkartastitek DROP CONSTRAINT crmkartastitek_crmkartaaktivita_FK; ALTER TABLE crmkartastitekm DROP CONSTRAINT crmkartastitekm_crmkartaakitvita_FK; END; / I'm getting "Statement 'ALTER_TABLE' is not a valid flow control statement". Is there any way around please? Our algorithm should like this: 1. disable/drop foreign keys. 2. load data. 3. enable/create constraints. Thanks, Radovan -- Radovan Biciste Czech Republic

2 7

If MonetDB could use table partition ?
by integrity 22 Jul '13

22 Jul '13

I am using "COPY BINARY INTO ...FROM ..." to insert 1000 FITS files into a table and the table has 21 columns, every FITS file has 200000 records. After some FITS files, the loading becoming very slowly, so If MonetDB could use table partition to accelerating the copy? or if Data Vaults could be loading more quickly? Thanks! Meng

2 2

Re: Could i binary bulk load to a CERTAIN column?
by integrity 21 Jul '13

21 Jul '13

my table test4 is : create table test4(id bigint, a float, b float, c float); so i didnt selectively load to test4 but it crashed. my code is : #include <stdio.h> #include <stdlib.h> #include <time.h> #include <mapi.h> void itoa (int n,char s[]) { int i,j,sign,k; char tmp; if((sign=n)<0)//记录符号 n=-n;//使n成为正数 i=0; do{ s[i++]=n%10+'0';//取下一个数字 }while ((n/=10)>0);//删除该数字 if(sign<0) s[i++]='-'; s[i]='\0'; for(j=i-1,k=0;j>k;j--,k++) { tmp = s[k]; s[k] = s[j]; s[j] = tmp; } } int writetoFile() { unsigned int seed; int k,j,i=0; long al[200000]; double af[200000]; FILE *fp; char fname[5]; fp = fopen("id","wb"); //创建二进制文件 for(k=1;k<=200000;k++) { al[k] = rand(); } fwrite(al, sizeof(long), 200000,fp); //fwrite以二进制形式对文件操作 fclose(fp); for(j=1;j<=20;j++) { for(k=1;k<=200000;k++) { af[k] = (rand() /(double)(RAND_MAX)); } itoa(j,fname); fp=fopen(fname,"wb"); fwrite(af, sizeof(double), 200000,fp); fclose(fp); } } void die(Mapi dbh, MapiHdl hdl) { if (hdl != NULL) { mapi_explain_query(hdl, stderr); do { if (mapi_result_error(hdl) != NULL) mapi_explain_result(hdl, stderr); } while (mapi_next_result(hdl) == 1); mapi_close_handle(hdl); mapi_destroy(dbh); } else if (dbh != NULL) { mapi_explain(dbh, stderr); mapi_destroy(dbh); } else { fprintf(stderr, "command failed\n"); } exit(-1); } MapiHdl query(Mapi dbh, char *q) { MapiHdl ret = NULL; if ((ret = mapi_query(dbh, q)) == NULL || mapi_error(dbh) != MOK) die(dbh, ret); return(ret); } void update(Mapi dbh, char *q) { MapiHdl ret = query(dbh, q); if (mapi_close_handle(ret) != MOK) die(dbh, ret); } int filetoMonetDB() { Mapi dbh; MapiHdl hdl = NULL; dbh = mapi_connect("localhost",50000, "monetdb", "monetdb", "sql","mydb2" ); if(mapi_error(dbh)) die(dbh,hdl); char *sql = NULL; sql = (char *)malloc(100); memset(sql,0,100); //sprintf(sql, "copy binary into tmatch from('/home/data1/writeBinary/id','/home/data1/writeBinary/1','/home/data1/writeBinary/2','/home/data1/writeBinary/3','/home/data1/writeBinary/4')"); // '/home/data1/writeBinary/5','/home/data1/writeBinary/6','/home/data1/writeBinary/7','/home/data1/writeBinary/8','/home/data1/writeBinary/9','/home/data1/writeBinary/10', \ // '/home/data1/writeBinary/11', '/home/data1/writeBinary/12', '/home/data1/writeBinary/13', '/home/data1/writeBinary/14', '/home/data1/writeBinary/15', \ // '/home/data1/writeBinary/16', '/home/data1/writeBinary/17', '/home/data1/writeBinary/18', '/home/data1/writeBinary/19', '/home/data1/writeBinary/20' )"); // sprintf(sql,"insert into test2 values(2)"); sprintf(sql, "copy binary into test3 from('/home/data1/writeBinary/id','/home/data1/writeBinary/1')"); //sprintf(sql,"copy binary into test4 from('/home/data1/writeBinary/id','/home/data1/writeBinary/1','home/data1/writeBinary/2','home/data1/writeBinary/3')"); //sprintf(sql,"copy binary into test4(id) from('/home/data1/writeBinary/id')"); update(dbh, sql); mapi_destroy(dbh); free(sql); return 1; } int main() { struct timeval tpstart,tpend; double timeuse; gettimeofday(&tpstart,NULL); writetoFile(); gettimeofday(&tpend,NULL); timeuse=1000000*(tpend.tv_sec-tpstart.tv_sec)+tpend.tv_usec-tpstart.tv_usec;//unit:us timeuse /= 1000000;//unit:s printf("21 columns write to file used %f seconds.\n",timeuse) ; gettimeofday(&tpstart,NULL); filetoMonetDB(); gettimeofday(&tpend,NULL); timeuse=1000000*(tpend.tv_sec-tpstart.tv_sec)+tpend.tv_usec-tpstart.tv_usec;//unit:us timeuse /= 1000000;//unit:s printf("Files load to MonetDB used %f seconds.\n",timeuse) ; return 1; }

3 3

Could i binary bulk load to a CERTAIN column?
by integrity 21 Jul '13

21 Jul '13

Hi all, when i use : copy binary into test4 from('/home/data1/writeBinary/id','/home/data1/writeBinary/1','home/data1/writeBinary/2','home/data1/writeBinary/3'); it fails: 21 columns write to file used 0.195698 seconds. *** glibc detected *** ./fitscolumntodb: malloc(): memory corruption: 0x0000000001d743e0 *** ======= Backtrace: ========= /lib64/libc.so.6[0x3efde7d982] /lib64/libc.so.6(__libc_malloc+0x63)[0x3efde7f313] /lib64/libc.so.6(__strdup+0x22)[0x3efde85c42] /home/data1/opt/MonetDB/lib/libmapi.so.6(+0xb24f)[0x7f77d4ca724f] /home/data1/opt/MonetDB/lib/libmapi.so.6(mapi_query+0xa0)[0x7f77d4cac410] ./fitscolumntodb[0x400fc5] ./fitscolumntodb[0x40101c] ./fitscolumntodb[0x401198] ./fitscolumntodb[0x40127d] /lib64/libc.so.6(__libc_start_main+0xf5)[0x3efde21a05] ./fitscolumntodb[0x400bc9] ======= Memory map: ======== 00400000-00402000 r-xp 00000000 08:01 18874373 /home/data1/writeBinary/fitscolumntodb 00601000-00602000 r--p 00001000 08:01 18874373 /home/data1/writeBinary/fitscolumntodb 00602000-00603000 rw-p 00002000 08:01 18874373 /home/data1/writeBinary/fitscolumntodb 01d74000-01d95000 rw-p 00000000 00:00 0 [heap] 3efd600000-3efd620000 r-xp 00000000 fd:01 700425 /usr/lib64/ld-2.16.so 3efd820000-3efd821000 r--p 00020000 fd:01 700425 /usr/lib64/ld-2.16.so 3efd821000-3efd822000 rw-p 00021000 fd:01 700425 /usr/lib64/ld-2.16.so 3efd822000-3efd823000 rw-p 00000000 00:00 0 3efde00000-3efdfad000 r-xp 00000000 fd:01 700426 /usr/lib64/libc-2.16.so 3efdfad000-3efe1ad000 ---p 001ad000 fd:01 700426 /usr/lib64/libc-2.16.so 3efe1ad000-3efe1b1000 r--p 001ad000 fd:01 700426 /usr/lib64/libc-2.16.so 3efe1b1000-3efe1b3000 rw-p 001b1000 fd:01 700426 /usr/lib64/libc-2.16.so 3efe1b3000-3efe1b8000 rw-p 00000000 00:00 0 3efe200000-3efe216000 r-xp 00000000 fd:01 700427 /usr/lib64/libpthread-2.16.so 3efe216000-3efe416000 ---p 00016000 fd:01 700427 /usr/lib64/libpthread-2.16.so 3efe416000-3efe417000 r--p 00016000 fd:01 700427 /usr/lib64/libpthread-2.16.so 3efe417000-3efe418000 rw-p 00017000 fd:01 700427 /usr/lib64/libpthread-2.16.so 3efe418000-3efe41c000 rw-p 00000000 00:00 0 3efe600000-3efe603000 r-xp 00000000 fd:01 700430 /usr/lib64/libdl-2.16.so 3efe603000-3efe802000 ---p 00003000 fd:01 700430 /usr/lib64/libdl-2.16.so 3efe802000-3efe803000 r--p 00002000 fd:01 700430 /usr/lib64/libdl-2.16.so 3efe803000-3efe804000 rw-p 00003000 fd:01 700430 /usr/lib64/libdl-2.16.so 3efea00000-3efea15000 r-xp 00000000 fd:01 672671 /usr/lib64/libz.so.1.2.7 3efea15000-3efec14000 ---p 00015000 fd:01 672671 /usr/lib64/libz.so.1.2.7 3efec14000-3efec15000 r--p 00014000 fd:01 672671 /usr/lib64/libz.so.1.2.7 3efec15000-3efec16000 rw-p 00015000 fd:01 672671 /usr/lib64/libz.so.1.2.7 3efee00000-3efee07000 r-xp 00000000 fd:01 700431 /usr/lib64/librt-2.16.so 3efee07000-3eff006000 ---p 00007000 fd:01 700431 /usr/lib64/librt-2.16.so 3eff006000-3eff007000 r--p 00006000 fd:01 700431 /usr/lib64/librt-2.16.so 3eff007000-3eff008000 rw-p 00007000 fd:01 700431 /usr/lib64/librt-2.16.so 3eff600000-3eff615000 r-xp 00000000 fd:01 700435 /usr/lib64/libgcc_s-4.7.2-20121109.so.1 3eff615000-3eff814000 ---p 00015000 fd:01 700435 /usr/lib64/libgcc_s-4.7.2-20121109.so.1 3eff814000-3eff815000 r--p 00014000 fd:01 700435 /usr/lib64/libgcc_s-4.7.2-20121109.so.1 3eff815000-3eff816000 rw-p 00015000 fd:01 700435 /usr/lib64/libgcc_s-4.7.2-20121109.so.1 3effa00000-3effa5d000 r-xp 00000000 fd:01 700439 /usr/lib64/libpcre.so.1.0.1 3effa5d000-3effc5c000 ---p 0005d000 fd:01 700439 /usr/lib64/libpcre.so.1.0.1 3effc5c000-3effc5d000 r--p 0005c000 fd:01 700439 /usr/lib64/libpcre.so.1.0.1 3effc5d000-3effc5e000 rw-p 0005d000 fd:01 700439 /usr/lib64/libpcre.so.1.0.1 3effe00000-3effe1f000 r-xp 00000000 fd:01 700440 /usr/lib64/libselinux.so.1 3effe1f000-3f0001e000 ---p 0001f000 fd:01 700440 /usr/lib64/libselinux.so.1 3f0001e000-3f0001f000 r--p 0001e000 fd:01 700440 /usr/lib64/libselinux.so.1 3f0001f000-3f00020000 rw-p 0001f000 fd:01 700440 /usr/lib64/libselinux.so.1 3f00020000-3f00022000 rw-p 00000000 00:00 0 3f00200000-3f00216000 r-xp 00000000 fd:01 700438 /usr/lib64/libresolv-2.16.so 3f00216000-3f00415000 ---p 00016000 fd:01 700438 /usr/lib64/libresolv-2.16.so 3f00415000-3f00416000 r--p 00015000 fd:01 700438 /usr/lib64/libresolv-2.16.so 3f00416000-3f00417000 rw-p 00016000 fd:01 700438 /usr/lib64/libresolv-2.16.so 3f00417000-3f00419000 rw-p 00000000 00:00 0 3f06200000-3f06203000 r-xp 00000000 fd:01 700443 /usr/lib64/libcom_err.so.2.1 3f06203000-3f06402000 ---p 00003000 fd:01 700443 /usr/lib64/libcom_err.so.2.1 3f06402000-3f06403000 r--p 00002000 fd:01 700443 /usr/lib64/libcom_err.so.2.1 3f06403000-3f06404000 rw-p 00003000 fd:01 700443 /usr/lib64/libcom_err.so.2.1 3f06600000-3f06603000 r-xp 00000000 fd:01 665783 /usr/lib64/libkeyutils.so.1.4 3f06603000-3f06802000 ---p 00003000 fd:01 665783 /usr/lib64/libkeyutils.so.1.4 3f06802000-3f06803000 r--p 00002000 fd:01 665783 /usr/lib64/libkeyutils.so.1.4 3f06803000-3f06804000 rw-p 00003000 fd:01 665783 /usr/lib64/libkeyutils.so.1.4 3f06a00000-3f06a0a000 r-xp 00000000 fd:01 700441 /usr/lib64/libkrb5support.so.0.1 3f06a0a000-3f06c09000 ---p 0000a000 fd:01 700441 /usr/lib64/libkrb5support.so.0.1 3f06c09000-3f06c0a000 r--p 00009000 fd:01 700441 /usr/lib64/libkrb5support.so.0.1 3f06c0a000-3f06c0b000 rw-p 0000a000 fd:01 700441 /usr/lib64/libkrb5support.so.0.1 3f06e00000-3f06e28000 r-xp 00000000 fd:01 700442 /usr/lib64/libk5crypto.so.3.1 3f06e28000-3f07028000 ---p 00028000 fd:01 700442 /usr/lib64/libk5crypto.so.3.1 3f07028000-3f07029000 r--p 00028000 fd:01 700442 /usr/lib64/libk5crypto.so.3.1 3f07029000-3f0702a000 rw-p 00029000 fd:01 700442 /usr/lib64/libk5crypto.so.3.1 3f0702a000-3f0702b000 rw-p 00000000 00:00 0 3f07200000-3f072d9000 r-xp 00000000 fd:01 700444 /usr/lib64/libkrb5.so.3.3 3f072d9000-3f074d9000 ---p 000d9000 fd:01 700444 /usr/lib64/libkrb5.so.3.3 3f074d9000-3f074e3000 r--p 000d9000 fd:01 700444 /usr/lib64/libkrb5.so.3.3 3f074e3000-3f074e5000 rw-p 000e3000 fd:01 700444 /usr/lib64/libkrb5.so.3.3 3f07600000-3f07641000 r-xp 00000000 fd:01 700445 /usr/lib64/libgssapi_krb5.so.2.2 3f07641000-3f07840000 ---p 00041000 fd:01 700445 /usr/lib64/libgssapi_krb5.so.2.2 3f07840000-3f07841000 r--p 00040000 fd:01 700445 /usr/lib64/libgssapi_krb5.so.2.2 3f07841000-3f07843000 rw-p 00041000 fd:01 700445 /usr/lib64/libgssapi_krb5.so.2.2 3f07e00000-3f07f9b000 r-xp 00000000 fd:01 700446 /usr/lib64/libcrypto.so.1.0.1c 3f07f9b000-3f0819a000 ---p 0019b000 fd:01 700446 /usr/lib64/libcrypto.so.1.0.1c 3f0819a000-3f081b4000 r--p 0019a000 fd:01 700446 /usr/lib64/libcrypto.so.1.0.1c 3f081b4000-3f081bf000 rw-p 001b4000 fd:01 700446 /usr/lib64/libcrypto.so.1.0.1c 3f081bf000-3f081c4000 rw-p 00000000 00:00 0 3f08e00000-3f08e5b000 r-xp 00000000 fd:01 700447 /usr/lib64/libssl.so.1.0.1c 3f08e5b000-3f0905a000 ---p 0005b000 fd:01 700447 /usr/lib64/libssl.so.1.0.1c 3f0905a000-3f0905e000 r--p 0005a000 fd:01 700447 /usr/lib64/libssl.so.1.0.1c 3f0905e000-3f09064000 rw-p 0005e000 fd:01 700447 /usr/lib64/libssl.so.1.0.1c 3f0fa00000-3f0fa0f000 r-xp 00000000 fd:01 665786 /usr/lib64/libbz2.so.1.0.6 3f0fa0f000-3f0fc0e000 ---p 0000f000 fd:01 665786 /usr/lib64/libbz2.so.1.0.6 3f0fc0e000-3f0fc0f000 r--p 0000e000 fd:01 665786 /usr/lib64/libbz2.so.1.0.6 3f0fc0f000-3f0fc10000 rw-p 0000f000 fd:01 665786 /usr/lib64/libbz2.so.1.0.6 3f11600000-3f11663000 r-xp 00000000 fd:01 700543 /usr/lib64/libfreebl3.soAborted (core dumped) but if i load fewer columns, it succeed: sprintf(sql, "copy binary into test3 from('/home/data1/writeBinary/id','/home/data1/writeBinary/1')"); so if i can load into table with a certain column ? Thanks! Meng

2 1

XQuery support in MonetDB
by Stephen Cameron 20 Jul '13

20 Jul '13

Hello List, Is there anyone making use of XQuery with MonetDB? Why was this XQuery work terminated/frozen, obviously its now a low priority, but why? People seem to resist saying that something is not as good an idea as first thought! Thanks for your insights. Steve Cameron collinta.com.au

2 1

Must the file be deleted after "copy binary into table from file"？
by integrity 19 Jul '13

19 Jul '13

can i have the file reserved? Thanks Meng

4 3

Re: Must the file be deleted after "copy binary into table from file"？
by integrity 19 Jul '13

19 Jul '13

Thanks very much for explaining these to me, but is it possible to provide two options of move, one do the move and reserve, one just move? That will be very convenient for uses.^_^ Sincerely, Meng PS,google mail in China is very unstable, often got disconnected from internet for some reason, so I have to applie another account here. ------------------ Original ------------------ From: "Stefan Manegold"<Stefan.Manegold(a)cwi.nl>; Date: Fri, Jul 19, 2013 02:15 PM To: "Communication channel for MonetDB users"<users-list(a)monetdb.org>; Cc: "Niels Nes"<Niels.Nes(a)cwi.nl>; "sjoerd"<Sjoerd.Mullender(a)cwi.nl>; Subject: Re: Must the file be deleted after "copy binary into table from file"？ Hi Meng, for efficiently, i.e., to avoid a file copy, MonetDB's binary copy into simply moves(^) the user-provided and to be loaded files into its dbfarm. Thus, if you need to preserve a copy of the files you provide, you need to do so yourself before executing a binary copy into. Best, Stefan (^) 1) The actual behavior (move) is actually different than the copy that the documentation at http://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/BinaryBulkLoad suggests. We need to fix the latter. 2) The actual behavior currently differs between data types; for numerical types, the user-provided files are moved as explained above; for stings, the files are read and processed, i.e., the used-provided file remains in place as-is. We need to consider unifying this behavior, e.g., by deleting also the user provided files for strings once they have been processed/loaded. 3) In case the copy into file, e.g., due to a failing sanity check after the file is moved, that data is factually lost, i.e., the files are gone, but no data is loaded into the database; we need to either document this clearly, or make the implementation behave like a transaction. ----- Original Message ----- > can i have the file reserved? > > > > Thanks > Meng > _______________________________________________ > users-list mailing list > users-list(a)monetdb.org > http://mail.monetdb.org/mailman/listinfo/users-list > -- | Stefan.Manegold(a)CWI.nl | DB Architectures (DA) | | www.CWI.nl/~manegold/ | Science Park 123 (L321) | | +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) | _______________________________________________ users-list mailing list users-list(a)monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list .

2 1

Join index
by Bouslah Ahmed 18 Jul '13

18 Jul '13

Hi, I want to optimize the runtime of this query Select sum(a.C4), b.C1, b.C2, b.C3 from v1 a, v2 b where (b.C1 = a.C1 and b.C2 = a.C2 and b.C3 = a.C3) or (b.C1 = a.C1 and b.C2 = a.C2 and b.C3 = 0) or (b.C1 = a.C1 and b.C2 = 0 and b.C3 = 0) or (b.C1 = 0 and b.C2 = 0 and b.C3 = 0) group by b.C1, b.C2, b.C3; why I thought of creating a BITMAP index join or create a bitmap index for v1 columns (c1, c2, c3) and v2 (c1, c2, c3). I can't do this with MonetDB can you help me.

2 1

failed to open file when 'copy binary into table from binaryformfile'
by integrity 17 Jul '13

17 Jul '13

hi all, when i use the command in sql: sql>copy binary into testtable from('file1'); failed to open file file1 the file1 contents : [svomhpc@localhost /home/data1]$>cat file1 0123[svomhpc@localhost /home/data1]$>hexdump file1 0000000 0000 3000 0000 3100 0000 3200 0000 3300 0000010 can anyone tell me what's wrong with file1 or how to use copy binary with? Meng

5 8

International characters in MonetDB
by aaaaaa ggggggg 15 Jul '13

15 Jul '13

Hello All, I have been trying to do some bulk uploads from CSV files into a MonetDB database. All the fields in the database are VARCHAR. The text in some of the fields contains international characters such as : ü, Ó, é etc. When I execute : copy into database from 'filename.csv' using delimiters ',','\n','"' NULL as ''; I get an error stating that the fields with words containing the international characters could not be imported, and the import process stops. How can I solve this issue? (I am running Monetdb on windows) Thanks, SG

3 5

Giving monetdb a fair chance
by Franck Routier 12 Jul '13

12 Jul '13

Hi, I am benchmarking different DB alternatives for our BI application. We are using Mondrian/JPivot on a dataset with ~300 millions row, and several parent/child dimensions. We are running some 50 real life scenario (ie 50 differents cubes using parts of this dataset) and measuring user response time on a server with several stripped Sata3 SSD, 32 GB ram and a i7-3820 CPU @ 3.60GHz (6 cores). During the tests, Monetdb performed by far the worst of Postgresql, VectorWise and (obvioulsy) MonetDB. Things that take 10 seconds with Postgresql take 450 second with monetdb... This was not expected at all, so looking at the server, I saw that MonetDB was consuming all memory (why not), including swap : bad idea I think. I was expecting MonetDB to memory mapped files when needed, real memory, but not use swap as it it was real memory... So here is my question : with a (quite) big dataset, 32GB ram, is there anything I can do to help MonetDB behave according to expectancies ? (maybe I should use ulimit ?). Thanks for your input, Franck

4 10

Problem with Mtest.py and monetdb command
by Bingyi Cao 11 Jul '13

11 Jul '13

Hey, I am new to MonetDB. I installed MonetDB yesterday. And I run Mtest.py. The results are as below: *!ERROR: Testing FAILED SIGNIFICANTLY (18 out of 1854 tests failed)* * * * 40 out of 1854 tests could not be executed* * 1 out of 1854 tests produced slightly different output* * sql/test/BugTracker-2012/predicate_select.Bug-3090* * 17 out of 1854 tests produced SIGNIFICANTLY different output* * monetdb5/optimizer/inlineFunction* * monetdb5/optimizer/ifthencst* * monetdb5/tests/BugTracker/kunion-and-nil.Bug-1667* * sql/jdbc/tests/Test_PSsomeamount* * sql/jdbc/tests/Test_PSlargebatchval* * sql/jdbc/tests/Test_PSmanycon* * sql/jdbc/tests/Test_PSmetadata* * sql/jdbc/tests/Test_PSsqldata* * sql/jdbc/tests/Test_Rsqldata* * sql/jdbc/tests/BugConcurrent_sequences* * sql/test/BugTracker-2010/incomplete-utf8-sequence.Bug-2575* * sql/test/BugTracker-2012/conditions_when_for_triggers_do_not_work.Bug-2073* * sql/test/BugTracker-2012/create_function.Bug-3172* * sql/test/BugTracker-2012/currenttime.Bug-2781* * sql/test/BugTracker-2012/null_except_null.Bug-3040* * sql/test/BugTracker-2012/day-of-month-localization.Bug-2962* * sql/test/Triggers/recursive_triggers* * * * * * First, check the testing results in /usr/local/mTests/index.html !* * * * Then, fix the problems by:* * - fixing sources and test scripts* * - fixing stable output by hand* * - approving test output by Mapprove.py (cf. Mapprove.py -?)* * * * After that, re-run Mtest.* I hasn't change any source code yet. I am not sure whether I installed it correctly. Also now I have no problem with command monetdbd, mserver5, mclient. However, I cannot use command with monetdb, the error information is always as below: monetdb: cannot find a control socket, use -h and/or -p I did a little bit research, to me it seems that it should automatically find a socket. Tell me if I am wrong. I also try to use the option -h -P, which doesn't work still. Do I actually install it correctly I tried to uninstall and reinstall it,but it hasn't solve the problem at all. Thanks very much!

2 3

Re: Fw:how are user defined function arguments passed ?
by integrity 10 Jul '13

10 Jul '13

Thanks, it's working, I am so excited, thank you all Best regards! Meng ------------------ Original ------------------ From: "Martin Kersten"<Martin.Kersten(a)cwi.nl>; Date: Wed, Jul 10, 2013 05:23 PM To: "users-list"<users-list(a)monetdb.org>; Subject: Re: Fw:how are user defined function arguments passed ? Yes, PATTERNs and COMMANDs have different interfaces. regards, Martin On 7/10/13 10:41 AM, Lefteris wrote: > try on your mal definition toinstead of the keyword pattern, to use > the keyword command. Patterns have a different signature if i remember > correctly. > > On Wed, Jul 10, 2013 at 10:28 AM, integrity <357416268(a)qq.com> wrote: >> Thanks very much for your instant reply,Lefteris! >> >> I wrote a simple test UDF, but it doesnt work. >> >> myudf.c >> #include "monetdb_config.h" >> #include "myudf.h" >> >> str >> addint(int *ret,int *one) >> { >> >> *ret=(*one)+10; >> >> return MAL_SUCCEED; >> >> } >> ------------------------------------------------------------------------------------------------------------ >> myudf.h >> /* In your own module, replace "UDF" & "udf" by your module's name */ >> >> #ifndef _SQL_MYUDF_H_ >> #define _SQL_MYUDF_H_ >> #include "sql.h" >> #include <string.h> >> >> /* This is required as-is (except from renaming "UDF" & "udf" as suggested >> * above) for all modules for correctly exporting function on Unix-like and >> * Windows systems. */ >> >> #ifdef WIN32 >> #ifndef LIBMYUDF >> #define myudf_export extern __declspec(dllimport) >> #else >> #define myudf_export extern __declspec(dllexport) >> #endif >> #else >> #define myudf_export extern >> #endif >> >> /* export MAL wrapper functions */ >> >> myudf_export str addint(int *ret, int *one); >> #endif /* _SQL_MYUDF_H_ */ >> ----------------------------------------------------------------------------------------------------- >> myudf.mal >> # scalar MAL signatures >> module myudf; >> >> pattern addint(one:int):int >> address addint >> comment "add one and two"; >> ----------------------------------------------------------------------------------------------------- >> 82_myudf.sql >> create function addint(one INT) >> returns int external name myudf.addint; >> ----------------------------------------------------------------------------------------------------- >> when i execute in sql: >> sql>select addint(76777); >> +---------------------+ >> | addint_single_value | >> +=====================+ >> | 0 | >> +---------------------+ >> 1 tuple (1.364ms) >> sql> >> sql>select addint(100); >> +---------------------+ >> | addint_single_value | >> +=====================+ >> | 0 | >> +---------------------+ >> 1 tuple (0.295ms) >> sql> >> sql> >> sql> >> sql>select addint(101); >> +---------------------+ >> | addint_single_value | >> +=====================+ >> | 0 | >> +---------------------+ >> 1 tuple (0.289ms) >> sql>select addint(1010000000000); >> overflow in conversion of 1010000000000 to int. >> sql> >> sql>select addint(1010000); >> +---------------------+ >> | addint_single_value | >> +=====================+ >> | 0 | >> +---------------------+ >> 1 tuple (1.087ms) >> sql> >> >> i checked every format of above files many times, i cannot find what's wrong >> with my program... >> it seems that the parameter didn't got passed to MAL.... >> >> Thanks again for your help! >> >> Sincerely, >> >> Meng >> ------------------ Original ------------------ >> From: "Lefteris"<lsidir(a)gmail.com>; >> Date: Wed, Jul 10, 2013 04:13 PM >> To: "Communication channel for MonetDB users"<users-list(a)monetdb.org>; >> Subject: Re: Fw:how are user defined function arguments passed ? >> >> The str *ret is you return value as described in the MAL signature >> reverse(ra1:str):str >> >> the ra1 is the name of a str variable in MAL, which is mapped to the >> str *src variable of the UDF. Naming here plays no role, just the >> types and the order of definition. Similarly, the :str end of the mal >> signature reverse(ra1:str):str means that it returns a str type, which >> in turn is mapped to the str *ret variable of the UDF signature. >> Naming plays no role again, just that the first variable of the >> signature in the udf is the return variable. Ofcourse it does not need >> to be always a str, it depends on the definition of the mal signature. >> >> On Wed, Jul 10, 2013 at 9:59 AM, integrity <357416268(a)qq.com> wrote: >>> Could anyone explain this question to me, please? >>> >>> I am still wondering on it.... >>> >>> Thanks! >>> >>> Meng >>> ------------------ Original ------------------ >>> From: "integrity"<357416268(a)qq.com>; >>> Date: Fri, Jul 5, 2013 03:42 PM >>> To: "users-list"<users-list(a)monetdb.org>; >>> Subject: how are user defined function arguments passed ? >>> >>> Hi everyone, >>> >>> In >>> >>> http://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/UserDefinedFuncti… >>> In thesection about how to extend SQL with a simple scalar function to >>> reverse a string >>> >>> step 3. Extension starts with a definitin of the MAL signatures. See the >>> example given, or browse through the files in monetdb5/modules/mal/*.mal >>> to >>> get a glimpse on how to write them. The MonetDB kernel documentation >>> provides more details. The file contains the MAL snippet: >>> command reverse(ra1:str):str >>> address UDFreverse >>> comment "Reverse a string"; >>> >>> step 4. The signature says that it expects a command body implementation >>> under the name UDFreverse, shown below. The C-signature is a direct >>> mapping, >>> where arguments are passed by reference and the return value(s) >>> references >>> are the first in the arguments list. The body should return a (malloced) >>> string to denote an exception being raised or MAL_SUCCEED upon access. >>> #include "udf.h" >>> >>> static str >>> reverse(const char *src) >>> { >>> size_t len; >>> str ret, new; >>> >>> /* The scalar function returns the new space */ >>> len = strlen(src); >>> ret = new = GDKmalloc(len + 1); >>> if (new == NULL) >>> return NULL; >>> new[len] = 0; >>> while (len > 0) >>> *new++ = src[--len]; >>> return ret; >>> } >>> >>> str >>> UDFreverse(str *ret, str *src) >>> { >>> if (*src == 0 || strcmp(*src, str_nil) == 0) >>> *ret = GDKstrdup(str_nil); >>> else >>> *ret = reverse(*src); >>> return MAL_SUCCEED; >>> } >>> >>> >>> I don't know why the argument name of "command reverse(ra1:str):str" >>> different from function definition UDFreverse(str *ret, str *src)? >>> >>> how does ret come from? is it according to the naming rules that the first >>> argument is the result, must it be a string type? >>> >>> Thanks! >>> >>> Meng >>> >>> >>> _______________________________________________ >>> users-list mailing list >>> users-list(a)monetdb.org >>> http://mail.monetdb.org/mailman/listinfo/users-list >>> >> _______________________________________________ >> users-list mailing list >> users-list(a)monetdb.org >> http://mail.monetdb.org/mailman/listinfo/users-list >> . >> >> _______________________________________________ >> users-list mailing list >> users-list(a)monetdb.org >> http://mail.monetdb.org/mailman/listinfo/users-list >> > _______________________________________________ > users-list mailing list > users-list(a)monetdb.org > http://mail.monetdb.org/mailman/listinfo/users-list > _______________________________________________ users-list mailing list users-list(a)monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list .

1 0

Re: Fw:how are user defined function arguments passed ?
by integrity 10 Jul '13

10 Jul '13

Thanks very much for your instant reply,Lefteris! I wrote a simple test UDF, but it doesnt work. myudf.c #include "monetdb_config.h" #include "myudf.h" str addint(int *ret,int *one) { *ret=(*one)+10; return MAL_SUCCEED; } ------------------------------------------------------------------------------------------------------------ myudf.h /* In your own module, replace "UDF" & "udf" by your module's name */ #ifndef _SQL_MYUDF_H_ #define _SQL_MYUDF_H_ #include "sql.h" #include <string.h> /* This is required as-is (except from renaming "UDF" & "udf" as suggested * above) for all modules for correctly exporting function on Unix-like and * Windows systems. */ #ifdef WIN32 #ifndef LIBMYUDF #define myudf_export extern __declspec(dllimport) #else #define myudf_export extern __declspec(dllexport) #endif #else #define myudf_export extern #endif /* export MAL wrapper functions */ myudf_export str addint(int *ret, int *one); #endif /* _SQL_MYUDF_H_ */ ----------------------------------------------------------------------------------------------------- myudf.mal # scalar MAL signatures module myudf; pattern addint(one:int):int address addint comment "add one and two"; ----------------------------------------------------------------------------------------------------- 82_myudf.sql create function addint(one INT) returns int external name myudf.addint; ----------------------------------------------------------------------------------------------------- when i execute in sql: sql>select addint(76777); +---------------------+ | addint_single_value | +=====================+ | 0 | +---------------------+ 1 tuple (1.364ms) sql> sql>select addint(100); +---------------------+ | addint_single_value | +=====================+ | 0 | +---------------------+ 1 tuple (0.295ms) sql> sql> sql> sql>select addint(101); +---------------------+ | addint_single_value | +=====================+ | 0 | +---------------------+ 1 tuple (0.289ms) sql>select addint(1010000000000); overflow in conversion of 1010000000000 to int. sql> sql>select addint(1010000); +---------------------+ | addint_single_value | +=====================+ | 0 | +---------------------+ 1 tuple (1.087ms) sql> i checked every format of above files many times, i cannot find what's wrong with my program... it seems that the parameter didn't got passed to MAL.... Thanks again for your help! Sincerely, Meng ------------------ Original ------------------ From: "Lefteris"<lsidir(a)gmail.com>; Date: Wed, Jul 10, 2013 04:13 PM To: "Communication channel for MonetDB users"<users-list(a)monetdb.org>; Subject: Re: Fw:how are user defined function arguments passed ? The str *ret is you return value as described in the MAL signature reverse(ra1:str):str the ra1 is the name of a str variable in MAL, which is mapped to the str *src variable of the UDF. Naming here plays no role, just the types and the order of definition. Similarly, the :str end of the mal signature reverse(ra1:str):str means that it returns a str type, which in turn is mapped to the str *ret variable of the UDF signature. Naming plays no role again, just that the first variable of the signature in the udf is the return variable. Ofcourse it does not need to be always a str, it depends on the definition of the mal signature. On Wed, Jul 10, 2013 at 9:59 AM, integrity <357416268(a)qq.com> wrote: > Could anyone explain this question to me, please? > > I am still wondering on it.... > > Thanks! > > Meng > ------------------ Original ------------------ > From: "integrity"<357416268(a)qq.com>; > Date: Fri, Jul 5, 2013 03:42 PM > To: "users-list"<users-list(a)monetdb.org>; > Subject: how are user defined function arguments passed ? > > Hi everyone, > > In > http://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/UserDefinedFuncti… > In thesection about how to extend SQL with a simple scalar function to > reverse a string > > step 3. Extension starts with a definitin of the MAL signatures. See the > example given, or browse through the files in monetdb5/modules/mal/*.mal to > get a glimpse on how to write them. The MonetDB kernel documentation > provides more details. The file contains the MAL snippet: > command reverse(ra1:str):str > address UDFreverse > comment "Reverse a string"; > > step 4. The signature says that it expects a command body implementation > under the name UDFreverse, shown below. The C-signature is a direct mapping, > where arguments are passed by reference and the return value(s) references > are the first in the arguments list. The body should return a (malloced) > string to denote an exception being raised or MAL_SUCCEED upon access. > #include "udf.h" > > static str > reverse(const char *src) > { > size_t len; > str ret, new; > > /* The scalar function returns the new space */ > len = strlen(src); > ret = new = GDKmalloc(len + 1); > if (new == NULL) > return NULL; > new[len] = 0; > while (len > 0) > *new++ = src[--len]; > return ret; > } > > str > UDFreverse(str *ret, str *src) > { > if (*src == 0 || strcmp(*src, str_nil) == 0) > *ret = GDKstrdup(str_nil); > else > *ret = reverse(*src); > return MAL_SUCCEED; > } > > > I don't know why the argument name of "command reverse(ra1:str):str" > different from function definition UDFreverse(str *ret, str *src)? > > how does ret come from? is it according to the naming rules that the first > argument is the result, must it be a string type? > > Thanks! > > Meng > > > _______________________________________________ > users-list mailing list > users-list(a)monetdb.org > http://mail.monetdb.org/mailman/listinfo/users-list > _______________________________________________ users-list mailing list users-list(a)monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list .

3 2

Fw:how are user defined function arguments passed ?
by integrity 10 Jul '13

10 Jul '13

Could anyone explain this question to me, please? I am still wondering on it.... Thanks! Meng ------------------ Original ------------------ From: "integrity"<357416268(a)qq.com>; Date: Fri, Jul 5, 2013 03:42 PM To: "users-list"<users-list(a)monetdb.org>; Subject: how are user defined function arguments passed ? Hi everyone, In http://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/UserDefinedFuncti… In thesection about how to extend SQL with a simple scalar function to reverse a string step 3. Extension starts with a definitin of the MAL signatures. See the example given, or browse through the files in monetdb5/modules/mal/*.mal to get a glimpse on how to write them. The MonetDB kernel documentation provides more details. The file contains the MAL snippet: command reverse(ra1:str):str address UDFreverse comment "Reverse a string"; step 4. The signature says that it expects a command body implementation under the name UDFreverse, shown below. The C-signature is a direct mapping, where arguments are passed by reference and the return value(s) references are the first in the arguments list. The body should return a (malloced) string to denote an exception being raised or MAL_SUCCEED upon access. #include "udf.h" static str reverse(const char *src) { size_t len; str ret, new; /* The scalar function returns the new space */ len = strlen(src); ret = new = GDKmalloc(len + 1); if (new == NULL) return NULL; new[len] = 0; while (len > 0) *new++ = src[--len]; return ret; } str UDFreverse(str *ret, str *src) { if (*src == 0 || strcmp(*src, str_nil) == 0) *ret = GDKstrdup(str_nil); else *ret = reverse(*src); return MAL_SUCCEED; } I don't know why the argument name of "command reverse(ra1:str):str" different from function definition UDFreverse(str *ret, str *src)? how does ret come from? is it according to the naming rules that the first argument is the result, must it be a string type? Thanks! Meng

2 1

Bind variables and parameterising the expression in case when
by Dan 10 Jul '13

10 Jul '13

Hi, I seem to have come across a strange restriction in using bind variables in MonetDB. If i use bind variables in the normal way by saying where field=${var} then it works. But not in this case: Here is a sample: select case when ${Brand} = 'All' And here is the error: EXEC: wrong type for argument 1 of prepared statement: char, expected char This is using the latest version of the jdbc driver (2.9) via Pentaho. Not quite sure how to set up a test case of this via a normal SQL client though. Thanks for any suggestions! Dan

2 1

Connection monitoring
by Taka Yasuda 09 Jul '13

09 Jul '13

Hi, What is the best way to monitor the connections to the monetdb server? Regards, Taka

2 4

Problems executing custom aggregation function on large datasets
by Sebastian Dorok 09 Jul '13

09 Jul '13

Hi all, I'm developing a custom aggregation function. Everything works fine until I use it on large datasets (ca. 270 Mio. rows). Here is what my function signature looks like: str AGGSimpleGenotype(int *ret, int *bid, int *gid, int *eid, bit ignore_nils); I assume that the BATs identified by bid and gid have the same length. That's why my function checks this circumstance. On my test dataset (ca. 200 rows) everything works fine. When I use my production dataset my aggregation function returns an error complaining that the BATs have not the same length and stops aggregating (by design)! How can this happen? The SQL query is: select grpgenotype(base) from test.bases group by pos; I attached the MAL plan generated for the large dataset. When I execute it step by step I get an error in line 39 saying: mal>X_106 := sql.delta(X_56,X_70,X_71,X_8); MAPI = (monetdb) /tmp/.s.monetdb.50000 QUERY = X_106 := sql.delta(X_56,X_70,X_71,X_8); ERROR = !TypeException:user.main[1]:'sql.delta' undefined in: X_106:any := sql.delta(X_56:bat[:oid,:str], X_70:bat[:oid,:oid], X_71:bat[:oid,:str], X_8:bat[:oid,:any]) This error occurs before my function is entered but I think it must be related with my problem. Is it an internal MonetDB problem? I'm using the Feb2013-SP2 release. Any ideas? Thx.

4 4

how to debug and use user defined functions?
by hlfwm hlfwm 09 Jul '13

09 Jul '13

i defined a user defined function "binarycopy",when i use my function, there is error: sql>select binarycopy('starid','1111',1); TypeException:user.s1_1[5]:'svom.binarycopy' undefined in: _9:any := svom.binarycopy(_6:str, _7:str, _8:int) program contains errors i want to debug the error with mal, there also some error: [svomhpc@localhost /home/data1]$>mclient -l mal -u monetdb -d mydb4 password: Welcome to mclient, the MonetDB interactive terminal (unreleased) Type \q to quit, \? for a list of available commands mal> mal> mal>function test(i:int):str; mal>io.print(i); mal>i:=i*2; mal>b:=bat.new(:int,:int); mal>bat.insert(b,1,i); mal>io.print(b); mal>return test:= "ok"; mal>end test; MAPI = (monetdb) /tmp/.s.monetdb.50000 QUERY = end test; ERROR = !TypeException:user.test[3]:'bat.new' undefined in: b:any := bat.new(_5:int, _6:int) !TypeException:user.test[4]:'bat.insert' undefined in: _7:any := bat.insert(b:any, _8:int, i:int) mal> could anyone tell me how to debug my function ,please? Thanks!

8 28

create table as fails
by Franck Routier 08 Jul '13

08 Jul '13

Hi, I am trying to create a table as select, but it fails... Here is the statement: create table imagerie_ft as select dwhdim.*, dwhq135.q135 from dwhdim join dwhq135 on dwhdim.dwhcolcsu = dwhq135.dwhcolcsu; The select alone works fine. The same statement with create view instead of create table works fine. But the create table fails with: Error: syntax error, unexpected SCOLON, expecting WITH in: "create table imagerie_ft as SQLState: 42000 ErrorCode: 0 Error: select dwhdim.*, dwhq135.q135 SQLState: 22000 ErrorCode: 0 Error: from dwhdim join dwhq135 on dwhdim.dwhcolcsu = dwhq135.dwhcolcsu;" SQLState: 22000 ErrorCode: 0 Should I report a bug, or is using the * in the statement for a create table a non supported sql idiom? Franck

3 2

aggregate function in MAL
by Scott Mathieson 05 Jul '13

05 Jul '13

Hi, I am trying to create a simple aggregate function entirely in MAL and am now struggling at the final hurdle. I have created the aggregate function such that it aggregates across an entire table but in the sub aggregate function I can't work out how to link the group to data BAT (g) to the data. The aggregate is designed to simply collect a sample of the values from the named column and return them as a pipe delimited string value. Can anyone tell me how to use the 'g' bat to join to the 'b' bat in the sub... aggregate function to get only the data rows for a specific group - currently I only get 2 rows of data per group even though I know that there should be 100k+ rows in each group? Here's the code: function bin_sample(b:bat[:oid, :any]):str; value := ""; bsamp := sample.uniform(b, 1000:wrd); barrier (h, t) := iterator.new(bsamp); tmp := "" + t; type := bat.getTailType(bsamp); test := str.startsWith(type, "str"); # Force the string type to be cast to a string or you just end up with numeric values barrier xxxx := test; tmp := str.str(t); exit xxxx; value := value + tmp; value := value + "||"; redo (h, t) := iterator.next(bsamp); exit (h, t); return value; end bin_sample; function subbin_sample(b:bat[:oid,:any_1],g:bat[:oid,:oid],e:bat[:oid,:any_2],skip_nils:bit) :bat[:oid,:str]; bn:=bat.new(:oid, :str); # check 'e' has some values - this is the list of group IDs in the head # 'g' is the group to the data BAT head ID mapping # 'b' is the data column BAT that we're aggregating over barrier (grpid, t) := iterator.new(e); # create a single row BAT of the group id we're processing bn_gid := bat.new(:oid, :oid); bat.insert(bn_gid, grpid, grpid); # join this to the GID->DATA map BAT to get the Group DATA oids b_g2dr := bat.reverse(g); b_gid2data := algebra.leftjoin(bn_gid, b_g2dr); grpval := ""; # Get the BAT of the actual data to be processed # # !!! HOW TO GET THIS TO WORK???????? # b_data := algebra.join(b_gid2data, b); grpval := bin_sample(b_data); # Store the result for this group bat.insert(bn, grpid, grpval); redo (grpid, t) := iterator.next(e); exit (grpid, t); return bn; end subbin_sample; Scott Mathieson, ________________________________

2 3

Failing to load a table
by Franck Routier 05 Jul '13

05 Jul '13

Hi all, I am trying to copy a table from a postgresql database into monetdb, but it fails, with monetdb filling the disks. I am using latest version of Monetdb (Feb2013-SP3) on Ubuntu Linux, with 32 GB Ram and SSD disks. To copy into Monetdb, I have a java program that runs mclient as: mclient --language=sql --databas=database --host=host --user=user /tmp/command.ctl with command.ctl : COPY nnn RECORDS INTO table FROM STDIN USING DELIMITERS'|', '\n', '\' NULL AS '' Then the Postgresql resultset is read from jdbc, formatted and written into STDIN. So far so good, it work with my dimensions tables (small), and with a 295 millions rows fact table that has 109 columns. This fact table takes 86 GB on the disk for Postgresql storage (plus indices). I have another form of the fact table, with less rows (184 millions) but more columns (505). While the first form of the table has a measureId column and only one value column, the other form of the table has one value column per measure. (It is a "sum(case when measureId=X then value else null ... group by" version of the first table). Loading this "columnar" table fails, with monetdb filling the disks (something like 500 GB was left before the loading process) and quitting (not sure what happens exactly here on the server, but I get a broken pipe error in java, and the monetdb database is lost). The table size on disk for Postgresql is 37 GB. Using VectorWise, the table loads, and take only 7 GB on disks, but the loading process (very similar to what we do with MonetDB) takes a very long time( 25 hours), much longer than what happens with the 109 columns tables. I am trying to make sense of that and understand why this table is giving a (very) hard time to monetdb (and somehow VectorWise as well). Does anyone have an explanation ? thanks, Franck

4 12

how are user defined function arguments passed ?
by integrity 05 Jul '13

05 Jul '13

Hi everyone, In http://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/UserDefinedFuncti… In thesection about how to extend SQL with a simple scalar function to reverse a string step 3. Extension starts with a definitin of the MAL signatures. See the example given, or browse through the files in monetdb5/modules/mal/*.mal to get a glimpse on how to write them. The MonetDB kernel documentation provides more details. The file contains the MAL snippet: command reverse(ra1:str):str address UDFreverse comment "Reverse a string"; step 4. The signature says that it expects a command body implementation under the name UDFreverse, shown below. The C-signature is a direct mapping, where arguments are passed by reference and the return value(s) references are the first in the arguments list. The body should return a (malloced) string to denote an exception being raised or MAL_SUCCEED upon access. #include "udf.h" static str reverse(const char *src) { size_t len; str ret, new; /* The scalar function returns the new space */ len = strlen(src); ret = new = GDKmalloc(len + 1); if (new == NULL) return NULL; new[len] = 0; while (len > 0) *new++ = src[--len]; return ret; } str UDFreverse(str *ret, str *src) { if (*src == 0 || strcmp(*src, str_nil) == 0) *ret = GDKstrdup(str_nil); else *ret = reverse(*src); return MAL_SUCCEED; } I don't know why the argument name of "command reverse(ra1:str):str" different from function definition UDFreverse(str *ret, str *src)? how does ret come from? is it according to the naming rules that the first argument is the result, must it be a string type? Thanks! Meng

1 0

Announcement: New Feb2013-SP3 Bugfix release of MonetDB suite
by Hannes Muehleisen 02 Jul '13

02 Jul '13

The MonetDB team at CWI/MonetDB BV is pleased to announce the Feb2013-SP3 bugfix release of the MonetDB suite of programs. More information about MonetDB can be found on our website at <http://www.monetdb.org/>. For details on this release, please see the release notes at <http://www.monetdb.org/Downloads/ReleaseNotes>. As usual, the download location is <http://dev.monetdb.org/downloads/>.

2 1

SQL question
by Rémi Daniel 28 Jun '13

28 Jun '13

Hello, I'm a French user of MonetDB, and I have a SQL question, I want to execute on MonetDB equivalent of this mysql request, and i don't find any solution : "SELECT field1, field2 FROM table GROUP BY field3 LIMIT 5;" I want to do a GROUP BY of a field3 which is not in the SELECT to get a limit of request results by this field3, like the "first" function of some databases. Does anyone have any idea on how I can do that ? Thanks for your help, Remi -- <http://inbox.fr> Rémi Daniel Développeur Web rdaniel(a)inbox.fr 30, rue Vincent Moris 92240 Malakoff - France Fax : +33 (0)1 55 48 02 79

5 7