vldb-protocols


ubuntu 16.04.1 LTS http://releases.ubuntu.com/16.04.1/ubuntu-16.04.1-server-amd64.iso
virtualbox
kernel 4.4.0-31 64 bit


user/user has sudo
ssh on localhost:4242

http://manpages.ubuntu.com/manpages/xenial/man8/tc-netem.8.html
iproute package
https://calomel.org/network_loss_emulation.html


1. Hemminger   S.  ,  "Network  Emulation  with  NetEm",  Open  Source
   Development    Lab,    April    2005    (http://devresources.linux-
   foundation.org/shemminger/netem/LCA2005_paper.pdf)

3. Salsano S., Ludovici F., Ordine A., "Definition of  a  general  and
   intuitive  loss model for packet networks and its implementation in
   the   Netem   module   in   the   Linux   kernel",   available   at
   http://netgroup.uniroma2.it/NetemCLG


monetdb from debian packages https://www.monetdb.org/downloads/deb/
Jun2016-SP1


mariadb 10.0
postgresql 9.5


sudo tc qdisc add dev lo root netem delay 0.3ms rate 100mbit
sudo tc qdisc del dev lo root netem


ifconfig gives rx/tx bytes


postgres client options
sslcompression=1, sslmode=require, keepalives=0

https://www.postgresql.org/docs/9.2/static/libpq-connect.html



psql --set=sslcompression=1 ...

this also covers redshift/greenplum/hyper/vertica!!







mariadb client options

https://mariadb.com/kb/en/mariadb/mysql_real_connect/

CLIENT_COMPRESS: Use compression protocol



https://mariadb.com/kb/en/mariadb/mysql_optionsv/

MYSQL_OPT_COMPRESS: Use the compressed protocol for client server communication. If the server doesn't support compressed protocol, the default protocol will be used.


MYSQL_OPT_PROTOCOL: Specify the type of client/server protocol. Possible values are: MYSQL_PROTOCOL_TCP, MYSQL_PROTOCOL_SOCKET, MYSQL_PROTOCOL_PIPE and MYSQL_PROTOCOL_MEMORY.


mysql --compress --protocol=tcp, socket, pipe, memory




oracle

https://docs.oracle.com/cd/E11882_01/network.112/e41945/performance.htm#NETAG014
 Session Data Unit = buffer
 512 bytes to 65535 bytes


Setting the SEND_BUF_SIZE and RECV_BUF_SIZE to at least the bandwidth-delay product insures that when large amounts of data are being sent that the network bandwidth will be optimally utilized.


https://www.gartner.com/doc/reprints?id=1-2PMFPEN&ct=151013&st=sb



systems

monetdb check

mariadb check
postgresql check

db2 DBMS T1 check
oracle DBMS T2 check

vertica DBMS A2 uses postgres protocol check

mongodb 

hbase/bigtable
http://hbase.apache.org/0.94/book/quickstart.html


clients

jdbc
odbc
native


data

result sets rows
100
1000
10000
100000
1000000
10000000




result set cols
1
10
100
1000
10000


data

/tpch_2_17_0/dbgen$ ./dbgen -s 2 -T L
sed -i 's/.$//' lineitem.tbl 




postgres setup

sudo -u postgres createuser -s $(whoami); createdb $(whoami)
set method=trust in /etc/postgresql/9.5/main/pg_hba.conf
psql --host 127.0.0.1 -w -t -A -c "SELECT 42" 


CREATE TABLE lineitem ( L_ORDERKEY    INTEGER NOT NULL,
                             L_PARTKEY     INTEGER NOT NULL,
                             L_SUPPKEY     INTEGER NOT NULL,
                             L_LINENUMBER  INTEGER NOT NULL,
                             L_QUANTITY    DECIMAL(15,2) NOT NULL,
                             L_EXTENDEDPRICE  DECIMAL(15,2) NOT NULL,
                             L_DISCOUNT    DECIMAL(15,2) NOT NULL,
                             L_TAX         DECIMAL(15,2) NOT NULL,
                             L_RETURNFLAG  CHAR(1) NOT NULL,
                             L_LINESTATUS  CHAR(1) NOT NULL,
                             L_SHIPDATE    DATE NOT NULL,
                             L_COMMITDATE  DATE NOT NULL,
                             L_RECEIPTDATE DATE NOT NULL,
                             L_SHIPINSTRUCT CHAR(25) NOT NULL,
                             L_SHIPMODE     CHAR(10) NOT NULL,
                             L_COMMENT      VARCHAR(44) NOT NULL);

COPY lineitem FROM '/home/user/lineitem.tbl' WITH DELIMITER AS '|';


mariadb setup
sudo -s; mysql
create user 'user'@'%';
create database 'user';
GRANT ALL PRIVILEGES ON user.* TO 'user'@'%' WITH GRANT OPTION;
mysql --host=127.0.0.1 user --skip-column-names --batch -e "select 42" 


CREATE TABLE lineitem ( L_ORDERKEY    INTEGER NOT NULL,
 L_PARTKEY     INTEGER NOT NULL,
 L_SUPPKEY     INTEGER NOT NULL,
 L_LINENUMBER  INTEGER NOT NULL,
 L_QUANTITY    DECIMAL(15,2) NOT NULL,
 L_EXTENDEDPRICE  DECIMAL(15,2) NOT NULL,
 L_DISCOUNT    DECIMAL(15,2) NOT NULL,
 L_TAX         DECIMAL(15,2) NOT NULL,
 L_RETURNFLAG  CHAR(1) NOT NULL,
 L_LINESTATUS  CHAR(1) NOT NULL,
 L_SHIPDATE    DATE NOT NULL,
 L_COMMITDATE  DATE NOT NULL,
 L_RECEIPTDATE DATE NOT NULL,
 L_SHIPINSTRUCT CHAR(25) NOT NULL,
 L_SHIPMODE     CHAR(10) NOT NULL,
 L_COMMENT      VARCHAR(44) NOT NULL);

LOAD DATA LOCAL INFILE '/home/user/lineitem.tbl' INTO TABLE lineitem FIELDS TERMINATED BY '|';


6001215



time psql --host 127.0.0.1 -w -t -A -c "SELECT * from lineitem limit 1000000" > /dev/null 
time mysql --host=127.0.0.1 user --skip-column-names --batch -e "select * from lineitem limit 1000000" > /dev/null


wire speed test
first 
nc -l 127.0.0.1 2121 > /dev/null
then 
time cat lineitem-1m.tbl | nc 127.0.0.1 2121




monetdb setup

CREATE TABLE lineitem (
l_orderkey INT NOT NULL,
l_partkey INT NOT NULL,
l_suppkey INT NOT NULL,
l_linenumber INT NOT NULL,
l_quantity INT NOT NULL,
l_extendedprice DECIMAL(15,2) NOT NULL,
l_discount DECIMAL(15,2) NOT NULL,
l_tax DECIMAL(15,2) NOT NULL,
l_returnflag VARCHAR(1) NOT NULL,
l_linestatus VARCHAR(1) NOT NULL,
l_shipdate DATE NOT NULL,
l_commitdate DATE NOT NULL,
l_receiptdate DATE NOT NULL,
l_shipinstruct VARCHAR(25) NOT NULL,
l_shipmode VARCHAR(10) NOT NULL,
l_comment VARCHAR(44) NOT NULL
);

COPY INTO lineitem FROM '/home/user/lineitem.tbl' USING DELIMITERS '|', '\n' LOCKED;


time mclient -h 127.0.0.1 -fcsv -s "select * from lineitem limit 1000000" > /dev/null 



postgres on the wire

https://www.pgcon.org/2014/schedule/attachments/330_postgres-for-the-wire.pdf


main finding: every row is sent in a separate protocol message with 56 bits global and 32 bits per field overhead


mysql result set structure
32MB packets

https://dev.mysql.com/doc/internals/en/binary-protocol-resultset.html





mongodb

https://docs.mongodb.com/manual/reference/mongodb-wire-protocol/

https://fastdl.mongodb.org/linux/mongodb-linux-x86_64-rhel62-3.2.8.tgz
./mongod --dbpath /local/hannes/mongodb-dbpath

tr "|" "," < ~/tpch_2_17_0/dbgen/lineitem.tbl > lineitem.csv

./mongoimport -d lineitem -c things --type=csv --file ~/tpch_2_17_0/dbgen/lineitem.tbl --fields "l_orderkey,l_partkey,l_suppkey,l_linenumber,l_quantity,l_extendedprice,l_discount,l_tax,l_returnflag,l_linestatus,l_shipdate,l_commitdate,l_receiptdate,l_shipinstruct,l_shipmode,l_comment"

time mongoexport -d lineitem  -c things --type=csv --fields "l_orderkey,l_partkey,l_suppkey,l_linenumber,l_quantity,l_extendedprice,l_discount,l_tax,l_returnflag,l_linestatus,l_shipdate,l_commitdate,l_receiptdate,l_shipinstruct,l_shipmode,l_comment" --limit 10 > /dev/null

-- broke querying

time ./mongoexport  -c lineitem --type=csv --fields "B5FkFxmESHAvEKkFC0ceKKDrI5fxxCWYnGvZKh4RhcO1SAS9PPQp6Aui7umcjISJYAzh4n5IMnPPo7VHE1VrSnSIyb1DAzDlTIaJqCCPUXJLaZ2R5amrC8IqJN6Yl1BPbGK6c1OMQZaHA3LHRVAG6k5zozVreBuGaFGYNbXqEA3fgqY1gV0cwWfSZf9vDKutaHJ4WJgiDtvVifb4xPNUNhkj1VyReFokaup70pjRos2Bams3YkpEw18CozNAPfrf0svBPaGUN7QTlVO6baCS8kIyofE29K00WumHi0KgooxwgHYOIN5AUp4CaUUsKX4gnPM05Yp0ERE90MTlWCEKM6tWz3mxeqsAXffUDpc00aRcceOzAyXleyQ3lYGvMEov84hXEEb65yvph0Yh6knebMw4MZlaAxIikqH5ri1gtGqTQKg1omINTeTu4MV9ZUSZc21VFjbKOStZJQCISvjgME5QCTKYfXukbWFeplAP68bWa62eiRysqxUNeJkwsyHj7mxYBbm90lchi7HpoJzKUpzYjHcggXE8w56HAQnCFWAZT6T6Sk6ekFluUl0IeNbZaNwTeiazWZXqvT8TbpsGgANKkt3RHkatm6sWs44y2kOi4kCKMDRIYVRE4FxcQoQQx9RnuJFhQkPlTnerHB47mA2P4BFpfzy11nxEJRU0G7TDRj9v7mfScccs31WbnQyFj3zyqIlQVKkbSLMVcxDxuO5z1pov0Upi0PqUsAb4pw5FSzIQ7Fz8Ry06ihmO0N1sOvewomWa56EWaf3pIhYRmnzXeUxutFAcD6rAhq70HxV7pBYfMVygX2Lqnh8nxjJcrougqIve8PMUwAY2YSDj3IIsXQvwaBHRmX9AL3w7CSg7WJN1X7Q2PcOFgXiCI7J6C5rcKHvfuC6F0VwEhXw9tLlkEVX3W2MTB3yq5syc0sDpQXNaWrJMmX3XzmwgRI86pZFLQKKIjMu9iPXAH1bjLL0eZK00EEcimNTKuIpn,yp8X5jmZ0823VXPmoUrFVQyODnAQ364IWTXUathhJyqVutQPSjw8K9ePi9R82jU9zhRs3pbtY315mC2q4WYHrqqPTLzREfPYffkDBL6gsXQzNpDYwnlc1Wg1qxoQbD8pbusi6WqVkzLAIZK8ZxqQeRRPC6n80szSCI1LNc65X63Gti2D4Cro6HHuYDOYuJAhEFrHRzK3XkCt5S5YIXU0ZQxMxijJoZAiyBiEHW4rSqIcNr2lXT1OIre23tIYhYGtTg78SbwjwfmyWl5I82PRelY86UynXoHJmYiZ7XG0EFJEp7xtMoq6u0ebjfFQjbE3RkzV5Haw4pKmuHyNalGFj3EsFaArPt45pL66BvVZM18JMnp7JXcyclihKCeawAFoDyE7M9PmSOnKe91mo05V1h9rqZOpGoZLzbhfbfaby9J3hU4fTgSAsoYmPaXZ2CyS9ZU1WUPrsjtx2Z8xFk3AiICbjwxg36Bbv05r83ORQhNZaLYKx38k0mZgO4sQUkIGGt0za3GfjL3vJ4ARpZJAOIlz1Gzz7gquAOeUgWXMqMrzj9L9wSuB1vaN2h5s2QT0YxUWr8SA6QTtIWLbZkf62iq88J8iP2sor29Y4MBuPDvA8LTxEgGkjhP23afAHDsTh23a7k0oIRJDTk6Tm18fJbWI9BKHGiikUAfqa0r4eRKDmmH86un6CxKSTHXDiaipaY58EXRBmmo7W1mBrrt6glRZqwrHuPCn2L8GcqHgs88LmWqZL8DO386azDoZ3CcH95ehQWc06iTSaIjEKxPo0Q0ItPZVQ1RxL85sTMhMyM05l5UoBEu92vtgUKmAR0ywTzFfUMMoJMboUPoGRHkPZcFb6rxrNVsJ4pwfLNv90W1rJVCBLFqQAG2uDGpu9KVYeLjjXaNqBiPOcDAwIOitsBlwPxcMOQX2pUzVEWh72Xwa4fjQWHoDcWymYEm4wwhLhZZNhbhReUQtTI9Gs3muZ3hoiQBA8Dcuah3jMwIs,p3Lpc0FJJ9MnDA05pPqUP3nbgHKzZwABDiSNqLOuiXuWXhYQYiBjpbKxVQRRpJuOBMgqcFTPQG4nmKf6VyK1PgtbAF7t1YsfoCghqJ7LO4Oqj1T7RJFJ1UTGVelxBzoWAifbG2BuFPY7eXU5mgGg3NrSl1MqGrEA4cMJX43AXOArRQRG0WgempjSvECXshF6RD6hFReTKM43zSsCLlaOhNCLONj0BAGkxnlrjgxR4rKIiyFNMgC5rKqCNygiarVFI6wsTGYXJWJFWcWRlLj2nREXuNnK0kz9Fn1VbUxA0oAkbiiTGM4D48frlkULNGr9qDRWvKumkZgPU4cuRAfKcAhs5S4LvkI3qiqQkjfziOBtlAUXp0wtzotuCKv9KTrHDQa90RK7ScB3YhPKnGsyZhA8ezw1xUZiG4LMvVOsciyLHpM2mfbGAGXXAwayOuN8UlMgzEg2yLVCV2hCtGx5hGfcEbWC2bWDl8Wqmpnv13m8S1CoZX8fJ23JMMrEGjMUOx7xA0lI9DN3L7zbhgyilllul2UpCNCxNaiB7emSuhK27S63cx4smRtrJQWz6TXUN8yY9jIzRCKqmVYFrN6fE3x9jYmizOsIjxFXlwb5xnYzI0XMCpqzSqLEKoQWEmnJzfGXOMgOvgO5B0il1BRXTG5njmZ6iPXflkBvbN9GRlRWBTIuxfDDMXoE18TqH0JuUzbxolaHFlvhNpijLbyaW0icaU49KBgf3ksylbRGZjMprGhmED9LwAzgxoOCfOKsUZt2Gge6ULriV0SPl9HMOJJTcoj7wyrQk9FfX3lMOCj7ODgGHrkws9EgsF0DGjTxpEPXs2Yk2wCrZBFriTTL1hWT4WiuLrjJanzSCGUiTYOAMSSHrYLfDuLzq6Dp8nlJHgQjEZ6lOxnRglxNoRrZOJHoPXyAvuJ0QFDxKGcMGkQUIDBsgWcVWXHRTMBgN9LC5GynFjpmv0rsW0UJgL7FFkksXkRjYJnveFPyROvT,fQwTFUfpirlOtiU6SoDJsuoS0bvswqjegGKHQzHneyM0jV0DXBCNJ5kPubnnTnn1qcxZUgMZ372o1o3Vf0Xl4XshmoZK9t6BHLNfTs7CntWnw8YWGLAKzOPyQ3Rqzq7AY2CELZzultzeDJ06oh4t5I1SwA3putfLQRI9EPr3985ZKPGs3ucLc3Oxr6fIFMNEuG10v5PS6xBsMCHalqKNsOINUOMOjPifRbkFiC56f9Eu839PcRvJGXUa2gIuwBRNvWSCCZZ5auxShxxcm55HXRH8snyEbcESQC9lKUJKb2UgnJPCsAjCkw9oun5u1iCrYGDBETL9R0ljEM2OVWLLrx05VuNR7zZX0FIQu0sHnxeuHW2OlYt9R6FnA922usoHe6j8nWlElR46kpYMRlKESRIZ0nKqM9k2a9FtWc8Hu0n5fvs2ZOb1eqS5niD82nLxNUxQQT8N6QzoT0vah9wLbHhMfzQkfAUf4Vnb3Ls7KVif6YDMU3jwzRYfbh63Z74aOOLEIxwhfi75QlM8nQQ3kPDcbo5wsRO5KgaPl4M1n0haNW7FlvEy2AUPxX6mLA3733Q9iafwZKXVkwLno2g2LnI7aUmfIVTe2PszMgyIECVLklTVtp4vnTyu4MtfujWEfjPQtcOv9xBpyyxM101oUn0FeDPlizVE6UBwXsjtmY6bmulmD641eRy9ZD9QXrvUXCPG2RtvOIrkZv3qTOOUEeiinxTvFIpMt8ANfUa8mblmilYQjDfKZ5fzG28tSItaGrQNgpgnh1x8jFtTxLuvy80lVT1NzjWp9LEqVzF2XUx99Gn2lhm6qLqpWICJe9SJwLCPKZmFYM3UWeQOYoH2KkMThOtrbT6NhpqWbcGCYzWVQGgHJiikKLugPDl1LigqYlfMnpA2OnEieFu3FGlYseGfrNDwEzTiJFWNWiILas5guggRztCw6bWY5RHaqO3SGt6b5JbEr0ZasYklRQDHMS9vJFy3TD7FB9AHuMLu,VJWDRzx13jquSwixxWK45MkMcftNPKSxQUzUHYSZr0gXXmMDMcNJJ9okHWPfrj0fQPqtl1RR1jLP8WOfeLE41AmA1scu0OvM2vVLQ13m4GTxU2LWsixFRnWlouB4GHbLtt0IGAPj9qRPWv6vFgypLm0cRmqpQbVbYOe4HyUVxnNxy4geEPzqBfOJQJJlQs5RPA7DVuOjDVP11aZTYIOV9DPT8SR9slG6gLDVoqq46W0hyfNVbHhrm2I0UUmqEgIsWzyDC7eJkKsVN0gYCWSbqnpY7Mbw7zDxjU6lpX9N86Py6xbKexY2VNYF1eQZoTunnNDiUw2b3grnEQAlr0kvKj9wC3OBs5jYbcsGQXaRbmmMjbEOAiiZE094xyQDqWseik2iUak21l66oGeEjyqbhmYVGphlHYzAz4tGTutaaETUqJcEsFrZOptAS5GsBGYsS5JklT0JCYkkAYtpLV9Tg5HrOu4ZxwPUbLHBMWgPJFjBPXO0gVXRfUgHCj3Wr92afKKxoZX227bmcE1kXanvbomiUlACEm7laKofHN3PfMVTijrIzOk9Kv4OsmwqZmm9N65UyuCrcQOti2rLkqnphnFD93qvwilJ6nUH4gauCiaLphLhYSQqwsHpi4BaPEUeZ7pbv3iatnivsveReh0P3V7NRXNu0XswcbtlaLGiGaY0aHYzrSu38Uz50oC4CNDpQyspz4nSVuaihntHHNADYVjZ1wzwEvQulwpz2YqYqh8bNzBMpqGzakMkMuSoKTABYvEg9WB1FW9DEZ0swrQ6B5FjawCyDvae2j3fZbeguvDrXNkJyrkkBgXT97eglvjVbTutKTpomScIyYHAPreZYgMjsGO2LHy3rPvyTMBgRmNwPHlq1XUS5yDTAzDr0QZkv7e7sa9NRVGsZCNahbLMqA4vRm0xBtF70E7kqCzkneoHD4qEONNc6ausRcSX973bPgm4DlVUQDinJwTuUXrRbGjLicCuMjUCu65gcnY2,UJrgEqUwFwjxxKmNLk5ZgjzJNTGwW72cs8A9VtEBrgUAmko9xv3SBXbfXaJk4D9I04iEeqBhnC601la9fuf1EzuMmVhM2op6hACzUepDFqANiomCFpf79rj4DHOlubOU42WfrSYK1xRPOnp7KiYtjafYDAbRSOqi17vSTBGGtqw2s0BLDU6u0LPTfPvtlnlaWAj3Ls4UjeTXjtglePDJP7m5FDOGbtGjEc5JfBMhGMN7g3KuO6J5FLX1xeXLZ18tkFNeHsmBRYIZXXHsAfpLTUv543LRRBH7C68ZAwk9oibytil6mLkV5Bz968fGxY2fAk6QCYYXJOHAuImsRU05gfMw4PLEs6YmmN94Ew45a4tjsxEbnWCllan2SkgsA2wDkyCUHGotXbZEvAyyeL9YavIpsJAfXvJZr2rQGEpJhAPtZrGAuHB0ABDf0DKjmh6BxzvPG258Di1UzQApCKYm8mS2mwcMu4Z0nTAlMcMwa105J87qH2CJ0rVE0fXc4pww0qPLnk9fXNiIi3jTYvFuuIRPi6aIuCFxO8WEkJ2PUL7VyH2x0iDcv7qw94cjfVEgLmNiphGqTJnSDofD9VHgHICiIZMcPjvAa6QLDU5AC551Shx2wC66ljG1LpwCRa6I2arHBE1xZZzrCe7QAZJ532bs5SOkTi4IvsItvKwR5H2ycK7zlTta9ruCAALJNc5VVCxMJl8934Yx5lU8f0z99cAtpQR9SXGJNiHSpziqSkDb4tDmYurCfh7Fx8BbWvz4uVQiyXfUzTcHPo1KUR9llyf0JIKNUA4YTwnVswqkumf9AqZFpkT2skFZ5Qv8lVqOWM2wmYovfxTu3JNiSQALKV6YDJYQHrGyQkpuIpguuninYNbA1DCtPTH2gf70ScOGz2828VgVK91XGsLq54xkzTXx163q3vrXZX4rFK0yWiwcW9q2Cm99MFDnSLn7ZDsrAAnJg8UTnv8966njlgRVr23wAbbJJmLoNtlhxsNU,sSmxvqzKGcWHS7shZn9L7mvJcCLRzLARxzWuqzbGiZ0OTB9uNGSpzheJpNAhlQpjxii2ieloa4UvTwDv9BhDp5n5fD5QgPn9BfPPkAXkCqCBZ02CiegHQrCRJrCIrbPZAOVzbo5pKj7hy2JKafDBISbVKyv3qeutrOkx5VVkWLAcYuhuxV9astowAxEiJNqwNInu5PL4NV27Yyktxnfzg2h0ieCNmvFTZx93gqfX4GeA0WQIozTSooPejaRAK0zb6hjXCxIRGZLWRJfz9RUpsqERvOilxwZSeUOytD4yhFW6SzsrfWIieRKgNf3zhJi1raZgWigDf9W7zFYK0Q6735Zlbqx46LR40kDaKiiEU1jg141E64F03OfhTX38Fi3lfq6MJf0qVVOCouJSxBJyicbtGyeD05NJtaHvw1gLNbiUP9ED1M2YMYUGF4ovLOiFkxc9cm17fpYylCXnZei8ssyo0lnL2BWjmkOnY37XEg236mIw5xnn6qGS3207EHrT3Y3t8UrXUyW7MY45V9F6ni2QZXaYssxjyahOIgnoSTtiBy2tpX8SNzBwH834bPs4hruge4QpS4C44XBAqkmuJ058P9wvpk4LH0YeuuQRgjR6firUOnr6XVQ5j6hubiQqfEBEawYgumixwNWJkasfztNKnB5c9WURrqvfphuUjw1WeeljaGa9bkwq1pCx8hkSPKs6PFr09E956ZUGOSsH04QWW9tpX1SEzr9EhowQA08kJqOlSyelngTuMl3e7r29mTt5wklT4ZHhM8NCvgsh5WEBssAgctJSK1B60Qj49jzJPG7PEfOE08Bpk5FZnGLApCrgbm6nkpQpbmzciiKENLA38HbJxCxCksGMkaMBxflQ4UEavenbwSCtg9VvgeqWAZKo1MfEIOeFkR4S1eGUyuLooMUTm8wNHv6RiKwmHmVVf0GwfeXvqvT5E9LxL8NYsZDNYgmESGy2vr1kE9rJ5KvSLnCh7ZsYHLtHOtH5,gTBqFGcBE90RoQSojmiaFvmqbu9PIZpTVFUnlMfrMHwKH5EzjMaB5xW5NtQITtKk61Ru5JR3sIBs8evL1MXpXFEmoPJPvE94MSGtGjb6mfjm3vzNwrzPXrTONzG4LQ0pTPDT0R7NKKzn9pLtJZJiyZ7TKhEo0S9vnvZkRk2XI1cRyPxksD9FThmRXgWzupXIlUOiqsvpSxvLtb6NYZhbCZiAhy6hVUhO7wP2g1LwktIoKlDQ5Y1KybOr9aCVHjhTVxroQGXHCbYuTpbL96Wfkv2iQl2ecj8Wta1lG8bg2azP40lmQOw8ZZOPEIlReVwfrnZUr6i3F5UleNAYSpzyhYDNwZ6megzzb5VgiVNa53lwjvog9LEN4m9RKU39Xk9XehL3zs38OrKPYtNtD6oLWZcWHGhOfD8llHDs1out6wpOA0WD45opOFRSkSi5cHMUhg5yTziqTE4s0ZvtGpHoWaIUsJasu4B62NxxxlPWtlGRpr5UCauynNK1twgKVKf7i3ah5J04JXjIyLQBer07QGhCsDoqP0YAh9ARFNkpvFStILszWjfXxIpN3j4kayKoH6VkKRQGnuJoNSucxAZUzfO6c94Jpa0AwW9yhEDskp4R9DobJDWt4sD3uss4u0pYVrtgOPWgj83K1yFmxwuIfx0QuarnYt3G1xQ26AUSpNOWLOBLGiPlfQhng3PHu4TQMU0OXRVExApAEF29x4nWtPsw59aXDXjvwPJLyK4FWlRt6uiQ7jFv2RfPCZS1cFCr26y157wW8NVWDtNQ9sE8htsZPU72Zh3wJokXwRC96OfCf4XkglYq1UQ0M3zxneEFJK7NNzXlL91o8SkWY2TTmcR7gVKokwBRIHFwAObln6vPUZAWwOc0ot86iOo5RqsjXSVi1CV6xafflTear0KvYGngACDXG2shoPlzFA8Q5oMNCfWBT1QijCVV2fVHMnC0LvA2wcTJ7lYGKVw5S3PQVQulrJRs4yUAE5cTLHHH,bDUuf1Khg7o5oMOqI7npcTCe2AUORR8m1nocw6RBQhjPfKGqEi61EovTHJKE9aWhoueqlVZ0qeuHv0xC30k6V3FOE5IaybqDN4wuAjIn0kGwrcaXpvHA23zElIDZykF97u4k6kiUOxEKfZp6iev4YKH5CvPaI3yHqX28NV7Ooj3ekhxDtOQolyBQtzvEolENbaTpSv4lS15f0HswEbWwYN8pL9bbFXfPsrF3k2inn58ilDDH5yUkHBVCSqjhzu1EwRh9XhsRO3omCbvMCJKSAWTuOfxKtUcsmjTDn8bXxHIFwTvLrQBIUuyN7uBz0XJiakM19B9P567HsbkIsStUn07NhTATzn9o5TffMge7YPGJ8QkMsag7MGtap7pTB3CcIfybqZEevEuunqkOc2YDyjH142vO8vDXS9wf4ogH65Wl9l0hDvE85N5tFcNo9T00ap4hoxjktHpox16wHLPZjXYlYjGHF1z1IAo5n1KfsGNVrbwaT6DcusKJOylocLGeTKw4PeCIpKcPrNf2Ueu0MGet2ciVNpENI8IVBFwts4xbhGpo3FPQYcGX06nlX90x0rI3nr4NcD0T0pgskYXVahEhQg6t9HPkFxUQnkww2wvNN9ayYeBqlBqMQlj5CiPyOrv5jxeIbIKwF0fcb3TJFwgiHqiVgrxymlTiN95TRFzTLC68xtCa4XKzYFnHrCZxSqge18XkJ8UhEyX3061Zu9mBoEjTiwKvVmpGJHILicfiMqeIuCABwDWEMCunabv2o43uZ1mlxQv6j9KZRcJrLXIKmPhmEoC90tEpagh5Ai38cKaimX48CMUvoptEnvXeoZW8PmhM9yA5KQPGhIbhvMVVYbiiM6hqK2A1nVtI7syt6K3vKr7WfZTpFIbPWqns1CR57vGq4UK2y7FXmpXRMkQVwHT8GezDDbL9oeRrlqjTc0QxcFpzl8NYiOxeVH7QuGK1SGYN8thMleUeAyhG28Q56yClALpCKapwJsSB,ohYQAXphQOPTxl3IUB8GEz0w1buo1W4ycD3I1bcJlKXMP8IxwJ9SuoHl8gTk60hG74DpJV6Bq9KFbNNCcmtJnHA6nv6Bm6BUMunPzhJSseigqvMoGeppf7YvMLFkQjXK28EMpBzrTYt6BAUzXos66kjEnT8ATiz1KP9fFGw69JKHoem3s8cUCZwM03WySjFriSn4nyAUIx0ilhVUyEiLX1s547iPKgXnvsL01TLVh2Tib2alqWnwSmGfzhW2N4PiCtbcs1sFKB52vs14nWXbKWEsGiaGOIY1JXBgOrIpP2A2eAKD19AXmWSCui3tmbffJy1w8RCwL4ff6HqjseS7VxXYYobrZm1ZbSF30XIrp6kqUmqsEVCaBRc8E7EifKjVq0L82nU1TPs6Lq5JZ3UID3cYDNTeT064LjN5KVLxBrNz8ATBiJCNP2FQuZbhI05SZsbroXje4p1Qkiceehc3fBozrO2yE2IhZKVGPegwKwFVr2RYIkEWPQ5iHswhvpCYamD0Hag4Cv4k15BRJilCf9aiL0ZHEYTf9gL0yvKaEqG3NN6fCXBorpDWoKZ0GW2xIQJgS3GrS8aGIaYhlxN45sAl3DkbUJhvTyaa9n9X5qFALtkTcPr8SIBNlaDtlaRrMP53utZUFnFcfKXqgqqRZ2ao3ptzOaxQZ0v3yy3mS99Q2afqC09Rq2jcNz03thRIPDZuXhDxyM4MYhUWDBLpmVSfj0WkOoei0zRjPlwtuOumfP4q0lX3h0s9YNRhi7mRKGgAvvaUHqgCcfo7CrEh56t1r1qyGy0NQh59ZGtkFj4LRTyXvaBAVTpvsRn6vwxCDP81L0cu5IrrJqmCRVC60xsFCa9tDanzErAFVfpk0nSYCUmZUfPODS3jFkU6pDzCph2HyvyXgYeqkfM9AGjcu5V0tGpgGmAxE66ily5wKQ6FI3xQMAs9Oh1WFkvnS89NkN6sXR6SziyLQ9R5gnmZ8fOO2PF5YpU3E712hQCl,h6QyjOikQUyAb945yONUt9J0EufVl1pVifq9s191lAXPCQXFUhcTqiWY0eKNvglgLrtODcis6qPIizS5VmzTiIwa8wk9cgLLD60bVBuv1SR7BHknDZrhLPcRh8Ak1rjX5PK3KG4IbaRFct1jbBeBXuvHj1WGmZN9hH7s47fT3OvA8V5bE5Y6UZZEcLJG0h8n4vp5TZwQo9D01qCGAw84jVOBoiTYhu6Ph57y6xeGfSgW2IPeKk46HgZQ6q4m9ZCi3slp2PgW07JPfspB2atSgiSfv9gcUBwhr87GtAOzD1gZIny5IhygVNKYsO5ubgVtlhKJHLzhEZAB9pUQxfILsDWemlPaY40F02UegAThC8Kpz8NSpYInVl54D6ACBqotFv9ENrjbuLRuKyAxctCNSrkTzIzOoBgFH8gXBnfMvsnQSbmI4Yt4aV3KJzrzkY17nE5cWU1C9NEOECTTvifiefGA81OhxM3cRrTbkr1VKZ7Yo8Vn5AAap11ZZDCSM36GnH48xSHsDhi5iUflreCvfetNi8nLPKw8uPsqUaJhQ8xcm9e9kb5wKU60kSy2G0wcG5V6rE9tiRIRVuK77tFO8hDWt9QT5RaCsFCVqw7Ju7u84J6iRiPAukg5bQPI2TQ7EJPpk00gAC6PytS82xpPNRp3FzvJWpX63IpFsCl6X7ye7lv7SVU8j5rvUQPnQzcY7oIoYnJmw0u32N0JJ074ahZHDukNAMnr9WLSc0mVVpk3nX4mncD7v2vW1R43QVPcKHlYeav94B2D5ETV6mc1WUUBD4lfVmkmmtks2XSPszsFQ3fcWFPll8KZsOYqXKQ0m7ScV1hPIzU0U1eI5XFg3A6SJZRzFVyoakC7cKW1ItPaSB3WrCSOzisaCNO6OFc10fmSXtHbXbptaJGMZ4nHG0kj1gjtCF8x1TmmbSqLmhBRC7VIIUK8sV2vv97OMENhuKH68KOETfSqTbIxz6nsLAkE2QWItu4cUz5Oe6K7,kHE4QMAUQpjwLjyhknyIlXMqDzGuDQUmksyaijrIK1YQbDFrmiOjnesY6y3LoALK7uIQlQao0joqicWj9vvO0wyawheo05nQIge8wUzqXOPgmq8CtsDXGbeqV4C1W04V663vCtOYqZKeyw65D34Rb5Fj8qcrGMxDobyZ3ART4ZsXKVDseTBtPwa3EZNLAhECeA1hnw4jL7YRUmUOgoKiSI3OK4VTOsOQ3uthyRI0XPXNWqnfVRP5uvxatMXGQVICrVFRolWaRfUG6IONAfRvcsZbLB6xZqIsvgKnk4OYHFYqGPFlPjVhF56a7IANggxxj9Dsf6QewUueEMZzbl75fcbjioCUqY6ec6NtsR2H9f2GUR0CaMM3bc5B5ju3NCCli7lXnST9XZNJRCJry3DfolgUR5By9FaGjes4qAJEjgppeeSBhQBzBg4jWGuwcICHZryZrfVBr5nToefxL7QHR8IeNWMf00S3ETIF5ip7CpxplO830DZVZIXTmcaQBRzPAUA9skytr4crSgvta3zJnpXApXToHQsfaTwVj9xIlxrMwRGjFw6kgyjGlLNogYZShMSy3ifeIJz5KfKM1H5HyyJFMPPbBtLchYIMQYcnt7RlgxtiiwioTXurF7Cn8vs5LHTIRYymHHyUvMvcphVjCMAknUOCnIqn9zoWV1sJRUIYA3GC3OjXa8ZApIrQm2fKI9Eg7224hqKMLVwb1tBxWsBPrpjJ67qWtWz6uvUmCqfHq7fUZIbMuA8ELVMnCm4FLTWeyNtyFJFHaLB23Gv8CvkrbfKjqfJCijzR29NyIu3cW5PIOpyiiyjKwtGMDHblnnCVLLxfcnGfZ9e12C5QX7PvRTUBV38P8pJIvuE8PH6B8msGCo3iqeqFl5T8MlK9HEKT6ukcHSuls3lJ22nfvvXYIy78F44289bmPgVKPUpCoXcx1uCEEQrvNUsQAXDDgTowrfGSky5EixyyQpPryA773g0PU6hNZaPhYFAi,sJL4T0FHjgtJmCZXO4HDla7n82OI8racEeZQ5O625C6Rw9CzSCe4v4QlxpPcRreZMUSjjvszNWrInmak9plefLZ8mBA6ohCl1brL9auehW28Wal9vwCxK2NMDtVgx7kZaAgKNaos5j11nh0fPFRRHKJ5LO4Hi39FMFuHusW5OFlYqeQD2VnjLC0NZtY1zegwePcxAIxVkxx1SXDIHgAFK7soHFw02T6jWmrAMSDaztiqAJhg9vqC1HQ3lIcLGGCM35Xtctq1gtuIYy1XUPpRr6aINL6uqq9pAWIaZhHCC5K4Kw0RNpUX0m7yb20w7aY7KlxGlUVGXJcunSZrSnI1bfOB1liSuAFOTJKOeynnz43cpsn0798jyD6gYMTcyCZsUHLNprw0zUjxM8MWFHPwaaqS0O2NgiMNqTKqggRs2ckG448Qg6HTE3ez97w5bXxAMMKH6BJtnLsuEZyPPK9KNTNrLUeEFfrh5J1tDJmGxkFQhX0mWicUloA6Su6BRYxG0lCWj3KJfADBjpLYyMDBFRyVHcmvj7k5lHbl61pJUSwFlVcP1WB4eESNIm1r24nw7haesNFfw6Gj4rKb2A2nYK0uS7xXvl0SPXor517XGmQg8qALJkeemsg200DkJA0ZsHAf19wY4HloK5eJFYYlWCboPQIp65kXUD0EhzOvig0EQygFR67KQ1SajDX6HnCjm1EVeoFnqeLrhBRIburQGKjiEXsPIkPpBKA5setUmThfZ0QY68iHo2zSxGJCJhZNfhGY0mylnvIQ4VjKgqwL7PKKOiTq1DkNT3lQ2z0psBOss3FwprK0G5ZMTvAGf801IVg8TH1ComZwsWxMTjApaIFqhqpLGP5ZI8wsghaPUPDHoioPWDN9Wzj9WlK6e7HWFKg9AIJVR6znjPWqXzyLOrlysDMoBFEKnTO7nwRqoyr6lJ9LeCIOFiBXoeog4rfW8or3WL1kGcRRzuB2tJ8NgyqYKYfi74tlb1TRfqWy,GmPh9kqouUh9gwnD5Z5CGR5338KylpmhuoaUmTMghcX1GhYbXMQyNlBLoajrHJw0X45GREpLGtlJktIIaGgP3sUKAchzcBHvaf118syve1Wl437BJzGol44l1vig3HDLMnXIEfHOyqcxI0WVrxFVCZ2RWyhC7gmJ4QEP5evaYIKFYBDw8uPIXP3YDFMNnBRuknAtVnNIEbnn975AljN2Xt2c6ezrxH8F74Yqb3f8xX2jWcXBWjlniDKNv7CxuY5AfwH91I5QyACbQlV757ZALWlwalK6YtW6j75vy7wiAcRL85vbk1eH57huKWXHEWvYPJHGRIkMMk1BLvYSZuJBiSP2g5tcvqJYYJ0QsCTrk33CiPfW3i0h2Cxx4yrLMyzXkmIgGPBpw5YDKuXs94bgcGg7RXSC0jeAmZLnthMQrsleV5KwPikL7xf1mOuVTYTISWCzPbBKXzXOPKO0itkrtmT4EI8rB1xmAhpmvy2BSknhYKl9baDqmAweSVw0XaqXTmMPjLnYuXjY09jCSRCPmGULjB0LP8yl87R0ITUHZ4i7FYpSBr6qgiEsSfkumnHPukGshKWUvIf0qSuWAzzu9aVO8CyzXF4ho2bEza9PoDW0JUwzoiQNpvbij0vWRmAlzM4iaz9WiGJ79bMZsWa8FXu8QWwNfGpyQtyPhX7rpQMsPWszJt8aoDQWlqmrVQQs3LwMrI5mZ0eyuZCKhLL6Vkr2z45mjj8EfszaqZZELGX6fQnrItyTt1KvDr87csNQWD0QuxUA0oaoKvxrkFEjx5Px7PhOmWVJShXYHiS9RT8rrvRvzVouKmNyePAxEIVBHbBZHwgSgt8SygDBT5TL2oZ8J4KnxpwZAHtTzaLfaEHba8sbRClmYwnIPmH2ywlNisH8VIaqE2GfPlmxsKYuXbGsx47IJiNQ2RUUqfyU7OKNoYGWTWIFytmnEi8XOWJEFJ4ZSG8nubGKbsxpkCPJJpme3m8ouJNU4FULC2BG,l7PTsN2K9ulYrPweGL1haNCxolBiqgOM19CfVsxPWgH26qnQL68rVlYehUl55Vl41ocyiBQczcXozrHmBkISV8fcWcziPFwZtHL0oYfQY1k75VIHK2RnopNIe5sC3HiAtuT8WxokZ8eGcSp0qnpjqH8a3jr6xEQyUkEwikaM1cE7gA1mxqJaHPqcgzCLwOmmYDx4tiAHPtN8Hf0sOGbtSeSc3GPwARVvoNY5vaYyfJqy1IkKYJff8T03yvLypwrcC2rCc0RaEjYMGjr9jgLP3Sewz9Iw8aGFZF8ay9geZD7e8xuYrCs157687x4K2v0o5gD6Vtyr9Dwc3A0DU6JU2uKkfI463V1kr4k2N7YPKIrWhWXJHs8gINISZc4oQt4iVUwa2LIr5xSzENvRj5DxtLgBf246QoLkM83tOVvYnwzZikPPju6MIUvAp53KPFTeEuiAPEjDxPfXEM0MPu5AxL2UgUvWfFZqtJ3ZwnqqeZBQ2emXCmmNekGm8vbvpAqb1YfOlSIpQD7vKTBQDjXj9py6E0MwsMWu4m23FOs0N4z2jlbezf4GDhXwjCwPmtBr2iTrPrlK9ujcTFfnjnZZjvCksErI1O0RKD3g9lglaybxXXTzE2D7tfqzLwsnTUbF1EGzT6DpznyQbIuZjATGrguXNlEPn8bPglQzxki3c9rUgbYa96ov0qoZLN9kXMqrz1tv2tokUP3xKMjooljB6mEYjhPYQfmNHOQJZWy4kk6ROBkF5bsNuVfu078grLzY61pztZPoXjiCpaT9xbbCGeMr2vH6UpwOhtNlGFMumk1HJnisHGNZzNV15tnTiCE92Gp18kghkx7pHTIDmQOEPPphlPOTUpozVuLBwTSb4SUPI8WAJRzirKvwjaXm851kTp2mnSz6Ihv5zWfSFcG12BLObWhv2895B5Jl3aUYDlHUqzpYIiN8DXathpU8ql1fMaZsnrKAYX8YfFyTsjO9XxvG6pZYsaRnsNiK3eEV,6iD9OZ8XQk7i7XVJhXK31S4RyQec8EeW9MkiYSBC62X2F8YUm7n0BJAqZxqiiPIEqhYK1p3BCUHE935f1aHsD1HlhaJECxp1eRV8Z8YFUckQ2Ij2qbWJYzPcxBcvffgh5prAW5Yw4rzThLFUB4yB5TR2cYPTDLT2F74K9xaf7mNgbqlX9RI6mCeLUN0blBUyjEhaPi8QrTMVhwjV31mTo6oJVbBbTEIMSHTptQ69GtjZwtLrRoulCyLz3IMIGu49hKUCOigTlXRmYr3rmAvnYkO3yvsgHjjR1SmFKosR1OGb4tsvY5XqDsavOWHgKQMOQPKcx4J5OiagmsCpLymUk113MZmf5CG32mTH2JcH59PQZ5Ou8Tx78sPIz3oL8GhwXaJVFS32CHHOzpJb76T4wKiP9TQWICTnKawkXsYk6GG9rgUlwVANBcrc8cPz8zW8q8ezyW1YoIzpAjo2Z1EbEGu4mQLn0ACv0RBHDuqxBxqOTToU3NHcU5mWnICLeNTrgWZThyPEpCT0T16EDlPOkrCxMjN4oImAwgcHXG277BaxZBGu7lggWT2DcmxnbXJPZRYcyorzzjXn4wGDT4IvQaalPzSMeog15MQfcAicavPXlghY905OXFw1gskbeDrDkeVFXnIwpHf2xOQwLaDUHDA9DQgyGfGh858K8ylBlhghFfEKhPAFEHSIPVoDUr7TmzSzoRJy6DDSnCFtmj7RUk2glbcMJVmC5ab8KmH2pftygQkR1oV6N8HRkxKV0WUOZCzqFpISxsJlOKW5Cmpzfitpv2CLmR0Cn7lQlo3H7DgpCjoQlQAcefQfpHxrXECRsVgWA76jlZ5czL9zerEh5NMohm5lZAqmT1N6GnrttUgECaUammnuTOnG9u6mfROgbsJ260uroCli8OXnDMTp4Rq0PV8iipDaMpDsJsDHo6IlIpTHHpHkQqn4FhxBPSa32Kp1MHADeJHpHqTJtumgyAmw4rSvsHKsbqcIFbWz"  --limit 10 > /dev/null





http://cyan4973.github.io/lz4/




jdbc

https://github.com/julianhyde/sqlline

echo "select * from tables;" | java -Djava.ext.dirs=/home/user/java/ -jar ~/java/sqlline.jar -u jdbc:monetdb://127.0.0.1/database -n monetdb -p monetdb -d nl.cwi.monetdb.jdbc.MonetDriver --fastConnect=true --outputformat=csv  --isolation=TRANSACTION_SERIALIZABLE --silent=true --showHeader=false > /dev/null


time echo "select * from lineitem limit 10000;" | java -Djava.ext.dirs=/home/user/java/ -jar ~/java/sqlline.jar -u jdbc:mysql://127.0.0.1/user -n user -d org.mariadb.jdbc.Driver --fastConnect=true --outputformat=csv  --isolation=TRANSACTION_SERIALIZABLE --silent=true --showHeader=false > /dev/null


time echo "select * from lineitem limit 10000" | java -Djava.ext.dirs=/home/user/java/ -jar ~/java/sqlline.jar -u jdbc:postgresql://127.0.0.1/user -n user -d org.postgresql.Driver --fastConnect=true --outputformat=csv  --isolation=TRANSACTION_SERIALIZABLE --silent=true --showHeader=false > /dev/null



time echo "select * from lineitem where rownum < 10" | java -Djava.ext.dirs=/home/user/java/ -jar ~/java/sqlline.jar -u jdbc:oracle:thin:@localhost:49161:XE -d oracle.jdbc.driver.OracleDriver -n system -p oracle --fastConnect=true --outputformat=csv  --isolation=TRANSACTION_SERIALIZABLE --silent=true --showHeader=false > /dev/null



driver needs to be in same dir!




1  VBoxManage import vldb-protocols.ova 
2  VBoxManage vldb-protocols --cpus 8
3  VBoxManage modifyvm vldb-protocols --cpus 8
4  VBoxManage modifyvm vldb-protocols --memory 16000
6  VBoxManage modifyvm vldb-protocols --usbehci off
9  VBoxManage modifyvm "VM name" --natpf1 "guestssh,tcp,,2222,,22"
10  VBoxManage modifyvm vldb-protocols --natpf1 "guestssh,tcp,,4242,,22"
7  VBoxManage startvm --type headless vldb-protocols 




sudo apt-get install openjdk-8-jdk maven



db2 setup


http://www.ibm.com/developerworks/downloads/im/db2express/

https://iwm.dhe.ibm.com/sdfdl/v2/regs2/db2pmopn/Express-C/DB2ExpressC11/Xa.2/Xb.aA_60_-i7wleYF1ULqO-8Z48YE9vKBsS4unp6c6pyao/Xc.Express-C/DB2ExpressC11/v11.1_linuxx64_expc.tar.gz/Xd./Xf.LPr.D1vk/Xg.8718392/Xi.swg-db2expressc/XY.regsrvs/XZ.OyjMsdFFqy6n0Yf8bs67RX-tJ30/v11.1_linuxx64_expc.tar.gz

11.1??

DBI1272I  To start using the database manager instance
      user, you must set up the instance environment by
      sourcing db2profile or db2cshrc in the sqllib directory, or you
      can open a new login window of the instance user.

db2start 
db2stop force



source ~/sqllib/db2profile

db2

create database db
connect to db

db2 UPDATE db cfg for db using LOGFILSIZ 10000


CREATE TABLE lineitem ( L_ORDERKEY INT, L_PARTKEY  INT, L_SUPPKEY INT, L_LINENUMBER  INT, L_QUANTITY    DECIMAL(15,2), L_EXTENDEDPRICE  DECIMAL(15,2), L_DISCOUNT DECIMAL(15,2), L_TAX DECIMAL(15,2), L_RETURNFLAG  CHAR(1), L_LINESTATUS  CHAR(1) , L_SHIPDATE    DATE, L_COMMITDATE  DATE , L_RECEIPTDATE DATE , L_SHIPINSTRUCT CHAR(25), L_SHIPMODE     CHAR(10), L_COMMENT      VARCHAR(44))


import from 'lineitem.tbl' of del modified by coldel| insert into lineitem




db2 update dbm cfg using svcename 50000
db2set DB2COMM=TCPIP





catalog tcpip node server1 remote 127.0.0.1 server 50000
catalog database db as remotedb at node server1
connect to remotedb user user using user

sudo ./db2rfe -f db2rfe.cfg , set OS_AUTHENTICATION = YES in that file



time echo "select * from lineitem limit 100" | java -Djava.ext.dirs=/home/user/java/ -jar ~/java/sqlline.jar -u jdbc:db2://localhost:50000/db -d com.ibm.db2.jcc.DB2Driver --fastConnect=true --outputformat=csv  --isolation=TRANSACTION_SERIALIZABLE --silent=true --showHeader=false -n user -p user > /dev/null



http://blog.whitehorses.nl/2014/03/18/installing-java-oracle-11g-r2-express-edition-and-sql-developer-on-ubuntu-64-bit/

pw: oracle
max 1G memory

https://github.com/wnameless/docker-oracle-xe-11g

docker run -d -p 49160:22 -p 49161:1521 -v /home/user:/opt/user wnameless/oracle-xe-11g





/usr/bin/time to the rescue


F      Number of major, or I/O-requiring, page faults that occurred while the process was running.  These are faults where the page has
       actually migrated out of primary memory.
K      Average total (data+stack+text) memory use of the process, in Kilobytes.
M      Maximum resident set size of the process during its lifetime, in Kilobytes.
t      Average resident set size of the process, in Kilobytes.


S      Total number of CPU-seconds used by the system on behalf of the process (in kernel mode), in seconds.
U      Total number of CPU-seconds that the process used directly (in user mode), in seconds.
e      Elapsed real (wall clock) time used by the process, in seconds.

r      Number of socket messages received by the process.
s      Number of socket messages sent by the process.

x      Exit status of the command.




sqlplus system/oracle@//localhost:49161

https://husnusensoy.wordpress.com/2010/10/22/create-your-own-oracle-tpc-h-playground-on-linux/


/u01/app/oracle/oradata/XE/system.dbf


alter database datafile '/u01/app/oracle/oradata/XE/system.dbf' autoextend on next 128m maxsize 8192m;



create or replace directory xtern_data_dir as '/opt/user';



CREATE TABLE lineitem ( L_ORDERKEY    INTEGER NOT NULL,
                             L_PARTKEY     INTEGER NOT NULL,
                             L_SUPPKEY     INTEGER NOT NULL,
                             L_LINENUMBER  INTEGER NOT NULL,
                             L_QUANTITY    DECIMAL(15,2) NOT NULL,
                             L_EXTENDEDPRICE  DECIMAL(15,2) NOT NULL,
                             L_DISCOUNT    DECIMAL(15,2) NOT NULL,
                             L_TAX         DECIMAL(15,2) NOT NULL,
                             L_RETURNFLAG  CHAR(1) NOT NULL,
                             L_LINESTATUS  CHAR(1) NOT NULL,
                             L_SHIPDATE    DATE NOT NULL,
                             L_COMMITDATE  DATE NOT NULL,
                             L_RECEIPTDATE DATE NOT NULL,
                             L_SHIPINSTRUCT CHAR(25) NOT NULL,
                             L_SHIPMODE     CHAR(10) NOT NULL,
                             L_COMMENT      VARCHAR(44) NOT NULL);



 
CREATE TABLE lineitem_ext (L_ORDERKEY  NUMBER(10),
                           L_PARTKEY NUMBER(10),
                           L_SUPPKEY NUMBER(10),
                           L_LINENUMBER  NUMBER(38),
                           L_QUANTITY NUMBER,
                           L_EXTENDEDPRICE   NUMBER,
                           L_DISCOUNT NUMBER,
                           L_TAX  NUMBER,
                           L_RETURNFLAG  CHAR(1),
                           L_LINESTATUS CHAR(1),
                           L_SHIPDATE  VARCHAR2(10),
                           L_COMMITDATE VARCHAR2(10),
                           L_RECEIPTDATE  VARCHAR2(10),
                           L_SHIPINSTRUCT  VARCHAR2(25),
                           L_SHIPMODE VARCHAR2(10),
                           L_COMMENT VARCHAR2(44))
ORGANIZATION EXTERNAL (
TYPE oracle_loader
DEFAULT DIRECTORY xtern_data_dir
ACCESS PARAMETERS (
       RECORDS DELIMITED BY NEWLINE
       FIELDS TERMINATED BY '|'
       MISSING FIELD VALUES ARE NULL)
LOCATION ('lineitem.tbl'))
PARALLEL 2
REJECT LIMIT 0
NOMONITORING;



insert /*+append*/into lineitem
select L_ORDERKEY,
       L_PARTKEY,
       L_SUPPKEY,
       L_LINENUMBER,
       L_QUANTITY,
       L_EXTENDEDPRICE,
       L_DISCOUNT,
       L_TAX,
       L_RETURNFLAG,
       L_LINESTATUS,
       to_date(L_SHIPDATE, 'YYYY-MM-DD'),
       to_date(L_COMMITDATE, 'YYYY-MM-DD'),
       to_date(L_RECEIPTDATE, 'YYYY-MM-DD'),
       L_SHIPINSTRUCT,
       L_SHIPMODE,
       L_COMMENT
from lineitem_ext;



sqlplus system/oracle@//localhost:49161 @query2.sql







hbase shell


create 'lineitem','l_orderkey','l_partkey','l_suppkey','l_linenumber','l_quantity','l_extendedprice','l_discount','l_tax','l_returnflag','l_linestatus','l_shipdate','l_commitdate','l_receiptdate','l_shipinstruct','l_shipmode','l_comment'



hbase org.apache.hadoop.hbase.mapreduce.ImportTsv  '-Dimporttsv.separator=|' -Dimporttsv.columns=a,b,c lineitem /home/user/lineitem.tbl



hbase org.apache.hadoop.hbase.mapreduce.ImportTsv -D'importtsv.separator=|' -Dimporttsv.columns="HBASE_ROW_KEY,l_orderkey,l_partkey,l_suppkey,l_linenumber,l_quantity,l_extendedprice,l_discount,l_tax,l_returnflag,l_linestatus,l_shipdate,l_commitdate,l_receiptdate,l_shipinstruct,l_shipmode,l_comment" lineitem /home/user/lineitem.tbl



oracle sqlplus slow buffer probably not configured well, 

try

SET TERMOUT OFF
SET VERIFY OFF -- evitar que imprima las sustituciones de variables que realiza
set trimspool on
set linesize 200
set longchunksize 200000 long 200000 pages 0

SET SERVEROUTPUT ON SIZE 1000000 

?


http://www.oracle.com/technetwork/topics/memory.pdf


To set the SDU size for the database server, configure the DEFAULT_SDU_SIZE parameter in the sqlnet.ora file.


netcat baseline 

100 0.0 12086
1000 0.0  123693
10000 0.02  1235631
100000 0.04 12461791
1000000 0.32  125538357
10000000  2.48  1265309071


comments
- skip the protocol, dump to csv/parqet, copy files over socket, read again, parse, time everything (only for Monet?)
- measure baseline select * from lineitem limit 1; subtract
- develop cost models base on line, types etc
- motivation: machine learning that needs lots of rows as input (decision tree?, classifier?, cross-validation, visualization)



compression/optimization settings

client flag --compress see scrp

mysql: mariadb.conf.d/50-server.cnf
max_allowed_packet      = 1G


postgres: postgresql.conf

pg_shared_buffers=10GB
pg_effective_cache_size=6GB
pg_work_mem=5GB


no visible client buffer flags

db2 OPTIMIZE FOR n ROWS will chunk stuff?





https://hemantoracledba.blogspot.nl/2008/06/tuning-very-large-selects-in-sqlplus.html
applied 


hive?

https://cwiki.apache.org/confluence/display/Hive/Parquet

https://cwiki.apache.org/confluence/display/Hive/GettingStarted#GettingStarted-RunningHiveServer2andBeeline


SET mapreduce.framework.name=local;


CREATE TABLE Staff (id int, name string, salary double) row format delimited fields terminated by ‘,’;

http://stackoverflow.com/questions/19320611/hadoop-hive-loading-data-from-csv-on-a-local-machine


LOAD DATA LOCAL INPATH '/home/yourcsvfile.csv' OVERWRITE INTO TABLE Staff;





CREATE TABLE lineitem_ext ( L_ORDERKEY INT, L_PARTKEY INT, L_SUPPKEY INT, L_LINENUMBER INT, L_QUANTITY DOUBLE, L_EXTENDEDPRICE DOUBLE, L_DISCOUNT DOUBLE, L_TAX DOUBLE, L_RETURNFLAG STRING, L_LINESTATUS STRING, L_SHIPDATE DATE, L_COMMITDATE DATE, L_RECEIPTDATE DATE, L_SHIPINSTRUCT STRING, L_SHIPMODE STRING, L_COMMENT STRING) row format delimited fields terminated by '|';


LOAD DATA LOCAL INPATH '/home/user/lineitem.tbl' OVERWRITE INTO TABLE lineitem_ext;



CREATE TABLE lineitem (L_ORDERKEY INT, L_PARTKEY INT, L_SUPPKEY INT, L_LINENUMBER INT, L_QUANTITY DOUBLE, L_EXTENDEDPRICE DOUBLE, L_DISCOUNT DOUBLE, L_TAX DOUBLE, L_RETURNFLAG STRING, L_LINESTATUS STRING, L_SHIPDATE DATE, L_COMMITDATE DATE, L_RECEIPTDATE DATE, L_SHIPINSTRUCT STRING, L_SHIPMODE STRING, L_COMMENT STRING) STORED AS PARQUET;


insert into lineitem select * from lineitem_ext;




$HIVE_HOME/bin/beeline -u jdbc:hive2://localhost:10000

insert overwrite directory '/tmp/export' STORED AS PARQUET select * from lineitem limit 10;