MonetDB Archives - bizkapish

0010 Install MonetDB Server on Ubuntu Linux

MonetDB / By Bizkapish / October 29, 2025 October 30, 2025

Notice: In this blog post, we will create a VOC database. This database will be used as the main database for most subsequent blog posts.

Getting the Codename of our Ubuntu Version

First, we need to know the code name of our Ubuntu version. We can find that by reading from the file "os-release". From this file we can read only the line that has words "VERSION_CODENAME" inside of it.

cat /etc/os-release | grep VERSION_CODENAME

Our Ubuntu codename is "focal". It is also possible to use command:

lsb_release -cs

We can see from the command line above that our user account is "fffovde". "FffOvdeKomp" is the name of our computer.

Adding a Repository Where MonetDB is Stored

Inside of this file we must place this text. These are addresses to MonetdDB repository. deb https://dev.monetdb.org/downloads/deb/ focal monetdb deb-src https://dev.monetdb.org/downloads/deb/ focal monetdb

We can add this text by running these two lines in our terminal:

sudo sh -c 'echo "deb https://dev.monetdb.org/downloads/deb/ focal monetdb" >> monetdb.list'
sudo sh -c 'echo "deb-src https://dev.monetdb.org/downloads/deb/ focal monetdb" >> monetdb.list'

Now our file looks like this:

Installing GPG key

Then, we would execute this command. This command will read GPG key file from the internet, and it will place that file in location "/etc/apt/trusted.gpg.d/monetdb.gpg". GPG key is a file which will be used to verify MonetDB packages before installing them.

sudo wget --output-document=/etc/apt/trusted.gpg.d/monetdb.gpg https://dev.monetdb.org/downloads/MonetDB-GPG-KEY.gpg

MonetDB Installation

Now we can install MonetDB. First, we will update our list of available software with the command: sudo apt update

Then we can install MonetDB server and client:
sudo apt install monetdb5-sql monetdb-client

Creating a DBfarm

`monetdbd create /home/fffovde/DBfarm1`	We will use "`monetdbd`" to create a DBfarm on our disk.
	Inside of this folder, a new file "`.merovingian_properties`" will appear. `ls -A /home/fffovde/DBfarm1`

The Merovingian dynasty was the ruling family of the Franks from the mid-5th century until 751. This dynasty ruled the Netherlands, the country from which MonetDB originates. MonetDB is using this term for some of its internal files and commands.

Now, that DBfarm is created, I will start the daemon. We can use daemon to control DBfarm.	`monetdbd start /home/fffovde/DBfarm1`
	Inside of the DBfarm1, we now have 4 files: `ls -A /home/fffovde/DBfarm1`

Creation of a Database

While monetdbd is used to manage DBfarm, "monetdb" console application is used to manage individual databases. In the background, "monetdb" will send our commands to monetdbd, and monetdbd will be the one exercising direct control over databases. So, we control databases from "monetdb", but through the power of monetdbd.

When database is created, it will only have one default user. That user is administrator "monetdb", and he has default password "monetdb". Database will be created in the maintenance mode. That means that only administrator will be able to start the database, and only on the local computer.

Administrator should start the database with this command. Then he should log into database, and he should change his default password to some other secret and complex password. We will not do that this time, we will continue using the default password "monetdb".

The process with our database server is called "mserver5". This process will run when the database is opened:
pgrep mserver5 This command will return process ID of our database server process.

Logging into Database

Now that our database is working and is listening on port 50000, we can try to use it. We will now use another application, with the name "mclient". Let's first recapitulate three console applications used by MonetDB:

We can exit the "mclient" program by typing the word "quit".

How to Stop our Server?

This is how we can stop "`mserver5`" process, the process of the Monetdb database. `monetdb stop voc`
	Log file will tell us what happened. `tail -1 /home/fffovde/DBfarm1/merovingian.log`

We can put our database in maintenance mode at any time. It doesn't matter if database is opened or closed. We use "lock" command.
monetdb lock voc

Next time, only administrator, on the local computer, can start the database with "monetdb start voc" command. He can start the database in exclusive mode, so that he can run some maintenance operations on the database ( he can do backup, or he can make changes in the schema ). We saw previously that database can be taken out of maintenance mode with "monetdb release voc" command. After that, any user can login to a database.

We can also stop "`monetdbd`" daemon.	`monetdbd stop /home/fffovde/DBfarm1`
	Log file will show us that daemon has stopped.

Install MonetDB in Alternative Way

This time I will jump to the newer version of the Ubuntu. It is "noble".
cat /etc/os-release | grep VERSION_CODENAME

We will install this "monetdb-repo.deb" package.
sudo apt install /home/fff/Downloads/monetdb-repo.deb

sudo apt update sudo apt install monetdb-sql monetdb-client We can now continue installing MonetDB in the standard way.
The process is the same as above.

Uninstalling Of the MonetDB

monetdbd create /home/fff/DBfarm1 monetdbd start /home/fff/DBfarm1 I will uninstall MonetDB on the "noble" server.
We will first create and start a DBfarm, because I want to show you the whole process.

For uninstallation process, if monetdbd is running, we must stop it. monetdbd stop /home/fff/DBfarm1

We still have "monetdb" group and user.
cat /etc/passwd | grep monetdb
getent group monetdb

This will not delete DBfarms, only MonetDB application.

0030 Systemd Unit File, MonetDB Sample Database

MonetDB / By Bizkapish / October 29, 2025 October 31, 2025

Systemd Unit File

Systemd Unit File is a file with settings that Systemd will use when starting and controlling some daemon. From version 11.53.13, MonetDB is using a new systemd file that allows us to change the default directory ( DBfarm ) for databases that are controlled by Systemd.

Old Systemd Unit File

New Systemd Unit File

From version 11.53.13, we have a new system file that allows us to change DBfarm folder, controlled by Systemd.

systemctl status monetdbd
This command will show us where is systemd unit file for Monetdbd daemon.
/usr/lib/systemd/system/monetdbd.service

Changing The Default Directory

I am using linux distribution "KDE Neon". This distribution doesn't use "SELinux". "SELInux" is security feature of the "Red Hat" distribution. The original Systemd unit file provided by the MonetDB developer team is using the command "chcon" that is only useful for the distributions that are using SELinux. I will exclude this code from my Systemd unit file.

ExecStartPre=/bin/bash -c 'test -d ${DBFARM} || (mkdir -m 770 ${DBFARM}; chcon -u system_u -r object_r -t mserver5_db_t ${DBFARM} )'

ExecStartPre=/bin/bash -c 'test -f ${DBFARM}/.merovingian_properties || (umask 0007; /usr/bin/monetdbd-11.53.13 create ${DBFARM}; /usr/bin/monetdbd-11.53.13 set pidfile=/run/monetdb/merovingian.pid ${DBFARM}; touch ${DBFARM}/.merovingian_lock; chcon -u system_u -r object_r -t monetdbd_lock_t ${DBFARM}/.merovingian_lock; chcon -u system_u -r object_r -t monetdbd_etc_t ${DBFARM}/.merovingian_properties)'

Extraordinary, because I am using distro without SELinux, I will also add these corrected lines to my "drop-in" Systemd unit file.

# First, we will delete all of the "ExecStartPre=".
ExecStartPre=
# This line will add the first part of the "ExecStartPre=". The line "ExecStartPre" is split into three parts,
# just to make it more readable. These three lines can be considered as one script.
ExecStartPre=/bin/bash -c 'test -d ${DBFARM} || (mkdir -m 770 ${DBFARM})'
# This line will add the second part of the "ExecStartPre=".

ExecStartPre=/bin/bash -c 'test -f ${DBFARM}/.merovingian_properties || (umask 0007; /usr/bin/monetdbd-11.53.13 create ${DBFARM}; /usr/bin/monetdbd-11.53.13 set pidfile=/run/monetdb/merovingian.pid ${DBFARM}; touch ${DBFARM}/.merovingian_lock)'

# Third line is unchanged.
ExecStartPre=/usr/bin/grep -q pidfile=/run/monetdb/merovingian.pid ${DBFARM}/.merovingian_properties

The Changes We Made

This command will show us the new value of the DBFARM environ:
systemctl show monetdbd | grep ^Environment=

systemctl cat monetdbd This command is also useful because it will show us the original file, and it overrides, and their locations, all together.

Testing The Changes

Because we are going to use Systemd, we should add our user to "monetdb" group.

`sudo usermod -aG monetdb "$USER"`	After that, we should log out and then log in.
`newgrp monetdb`	Sometimes log out/in will not be enough. In that case, try to open a new session ( a tab ) in a terminal, or run this command in the existing session. This is happening because OS will try to recycle the old session, so changes are not applied.

We will now create one database and after that we will restart our computer to see if it will be started automatically.

systemctl start monetdbd monetdb create DBdesktop monetdb release DBdesktop systemctl enable monetdbd reboot -- Start monetdbd daemon controlled by systemd.
-- Create a database.
-- Make database available.
-- Make monetdbd to start automatically after computer reboot.
-- Restart our computer.

What is Schema

Schema as a File

We can create a file that will contain all the instructions the server needs to create database objects inside of the schema. This file would tell the database which tables, relations, indexes, views to create. In this way, we can create everything that makes up one schema. That is why we call such a file a "schema file". Although we have not learned all the SQL commands needed to create such a file, we can use the "schema file" that the MonetDB development team has prepared for us.

Creation of a New User

For our schema we will create a new user. First, we will enter mclient with 'monetdb' privileges.
> monetdbd start /home/fffovde/DBfarm1 > mclient -u monetdb -d voc --password monetdb

For creation of a user, we need username (USER), password (WITH PASSWORD), user's full name (NAME), and default schema for that user (SCHEMA). The default schema is schema that MonetDB will use as a current schema when the user log in. For tables in the current schema, the user can type "SELECT * FROM Table1", but for tables in NON current schemas, the user must type "SELECT * FROM schema.Table1".

"sys" schema is a built-in schema in MonetDB. We will use it temporarily so we can create a new user.
sql> CREATE USER "voc" WITH PASSWORD 'voc' NAME 'VOC Explorer' SCHEMA "sys";

As a 'monetdb' administrator we can create a new schema. We will say that previously created "voc" user is the owner of that schema.

sql> CREATE SCHEMA "voc" AUTHORIZATION "voc";

Don't get confused. The name of our database is "voc", but the name of the new schema is also "voc", and the name of the user is "voc".

We will set the new schema as the default schema for our user.
sql> ALTER USER "voc" SET SCHEMA "voc"; sql> \q -- we can exit mclient with "quit" or "\q"

Since the "voc" schema is the default schema for the "voc" user, this schema will be active when this user logs in to MonetDB. Everything the user does will be reflected in this schema, unless the user explicitly mentions that they want to work in a different schema.

Populating our Schema with Database Objects

Our "voc" schema is currently empty, but we have definitions of all the tables, view, indices … inside of our downloaded SQL script. We will use that script to populate our schema. We type:

DBeaver Database Manager Program

We will install DBeaver database manager program to peruse our database.
> sudo snap install dbeaver-ce

At the end, objects of our database will appear inside of pane on the left side of a program. There, we should double click on schema name (1). After that we can select tab "ER Diagram" (2). There, after some rearrangement, we will see ER diagram of our database (3). As we can see, tables are organized in star schema with "voyages" table as the only fact table. All tables are connected with foreign key constraints, where foreign key is also primary key inside of dimension tables. The only exception is Invoice table where foreign key columns are not primary key columns, and that is why that relationship is shown with dashed line (4).

Here is download for voc_dump.zip file used in this blog post:

Download

DBeaver is an excellent program. If you want to learn more about it, you can try this linkedin tutorial.

https://www.linkedin.com/learning/dbeaver-essential-training?trk=learning-topics_trending-courses_related-content-card&upsellOrderOrigin=default_guest_learning

0600 Running MonetDB in Docker Container

MonetDB / By Bizkapish / October 18, 2025 October 21, 2025

Introduction

Virtual Machines

Container

Container is something in between portable application and virtual machine.

Portable application	Container	Virtual machine
– Application that doesn't need installation. – It should match OS version. – Lightest and fastest. – Not isolated from other applications.	– Application with most of its dependencies. – It should match class of OS (Linux, Windows…). – Middle weight. – Mostly isolated.	– Full OS with all the applications. – Should match hypervisor. – Heavy weight. – Fully isolated.

Containers are similar to portable applications, they can be easily deployed, moved, and upgraded. In addition, containers are isolated from other applications and will not compete for resources between them. Containers are much lighter and faster than virtual machines. Containers are immutable, so it is not possible to update parts of the container. This gives us consistency that we can rely on.

Usage of a Container

Images are templates used to create containers. An image contains everything needed to build a single container. Images are created by application developers. A developer would create an image and then provide it to users, who would then be able to create containers based on the image.

Let's say we want to publish our application as an image. We will package all our source code along with a "Dockerfile" (explained later) into a single folder. We would use the "Build" command to create a "Docker image". The image is the template from which containers are built. We will upload that image to a Docker image repository (registry). "Docker hub" is a well-known cloud image registry. Users can download our image from the registry. Based on that image, the user will launch the container. In the container, the user would find the application we created.

Anatomy of a Dockerfile

If we go to this git hub web page "link", we will find an example of files that are needed to create an image for MonetDB.

All Dockerfiles are made like this. They usually have the same steps:	1) get linux image for API	2) define a folder	3) copy your application	4) download dependencies
5) build/compile	6) create user	7) expose network ports	8) set some environs	9) define startup command

Docker file is basically a list of steps we would take on a physical machine to make our application operational. We will not go any deeper into docker technology. In this article we will only learn how to install docker and download and use MonetDB container.

Installation of a Docker

On this web page we can read how to install Docker on Ubuntu. It is a long page, but we can take a shortcut.
https://docs.docker.com/engine/install/ubuntu

We should add our user to docker group.
sudo usermod -aG docker $USER After that we should log out and log in.

Monetdb Container Installation

Getting a Monetdb Image

We type this command to observe our images. In non-compressed state, our image is 536 MB.
docker images

"Tag" is a version of the image. If we don't provide a tag, we will always get the "latest" version.

Starting MonetDB Container

The server will stay in the foreground in the terminal. We will open a new tab inside terminal and there we will type:
docker ps #this will list opened containers

We can connect to bash in the new container. "28629" are start figures of the container ID. We will be connected as a root.
docker exec -it 28629 bash

What Will Happen If We Reboot Computer

After restart "docker ps" command will show containers active on our computer. We can see that our container STATUS is "Up 43 seconds".

If we type "docker ps -a" we would get all the containers on our computer, both stopped and active.

MonetDB Container Options

Above we use the "-d" option to detach our container. This means that after we run this command, docker will not remain in the foreground, but we will get the command line again, so we can continue typing commands in the same shell.

At the bottom of the image, we can see ID of the new container.

1666729293f….

MDB_FARM_DIR and MDB_CREATE_DBS

We can define in which directory in the container our farm will be created. That is defined with MDB_FARMDIR. With MDB_CREATE-DBS, we can create one or several databases. Between the names of databases there must be no spaces, just the comma.

-e MDB_CREATE_DBS=db1,db2 -e DB_FARM_DIR=/var/monetdb5/dbfarm2 I will again enter container's bash to
check whether we have this folder and databases. docker exec -it 166 bash

We don't have explicitly define MDB_FARM_DIR and MDB_CREATE_DBS. They can take the default values. /var/monetdb5/dbfarm monetdb

Volume

If we want the data to be safe and persistent, we should use volumes.

MDB_SNAPSHOT_DIR, MDB_SNAPSHOT_COMPRESSION and MDB_LOGFILE

By default, logfile will be placed inside of the DBfarm, and will have a name "merovingian.log".

We have set snapshot options:
-e MDB_SNAPSHOT_DIR=/snapshot -e MDB_SNAPSHOT_COMPRESSION=.tar.bz2 I will make a snapshot with the default settings:

monetdb snapshot create db1

Snapshots cannot be created if we don't provide snapshots environs. It would be wise to place snapshots inside of the volume to preserve them.

MDB_FARM_PROPERTIES and MDB_DB_PROPERTIES

With MDB_FARM_PROPERTIES environs we set two DBfarm properties.
monetdbd get all /var/monetdb5/dbfarm2 | egrep 'exittimeout|keepalive'

Possible properties are explained inside of this blog post "link". We cannot set properties "listenaddr, control and passphrase". "Listenaddr" is always set to "all". "Passphrase" is set by the environ MDB_DAEMON_PASS. If we set MDB_DAEMON_PASS, then "control" will be set to true.

Possible properties for a database are explained in this blog post "link".

MDB_DB_ADMIN_PASS and MDB_DAEMON_PASS

MDB_DB_ADMIN_PASS is a mandatory environ. We must set it in order to run MonetDB container.

MDB_DAEMON_PASS is a password for remote Monetdbd control. Remote control of a daemon is explained in the blog post "link". In the context of the containers, this password will allow us to control the daemon in the container from the host operating system.

I will run this command from the host OS to control monetdbd daemon inside of the container.
monetdb -h localhost -P daemonPass create db3

For this to work we need to have some conditions fulfilled on the host operational system:
– We must have MonetDB installed on the host computer.
– DBfarm must be started. Without that we cannot use "monetdb" command.
– DBfarm on the host computer must use port number different than the port number of the MonetDB inside of the container.

monetdbd get port /home/clean/DBfarm1

Host computer has Monetdbd on the port 50.0001.

How to Stop and Start Container?

Clean Up

Deleting Images and Containers

Removing Docker from Your System

Deleting Docker includes these steps:

We can now uninstall all parts of the docker:
sudo apt purge -y docker-ce docker-ce-cli containerd.io docker-buildx-plugin docker-compose-plugin

In the next step, we will remove docker repository and GPG key.
sudo rm -f /etc/apt/sources.list.d/docker.list sudo rm -f /etc/apt/keyrings/docker.gpg

We can update the list of available packages.
sudo apt update

We will delete docker user group.
sudo groupdel docker

Now, we can conclude that docker is deleted:
docker --version

0590 System, Session Procedures and Queries Que

MonetDB / By Bizkapish / September 15, 2025 September 18, 2025

DBfarm1

I will login as admin into "voc" database. monetdbd start /home/fffovde/DBfarm1 mclient -u monetdb -d voc #password monetdb

System Procedures

Table Statistics

In one of the previous blog posts "link", we saw the existence of a system table containing statistics about database tables:

SELECT * FROM sys.statistics WHERE minval = 'A';

We saw that this table can be updated partially with these commands. Now, we will see that for each of these commands we can use stored procedure, as an alternative.

	Update schema statistics:	Update table statistics:	Update column statistics:
Command	`ANALYZE sys;`	`ANALYZE sys.auths;`	`ANALYZE sys.auths ( name );`
Procedure	`CALL sys.analyze('sys');`	`CALL sys.analyze('sys', 'auths');`	`CALL sys.analyze( 'sys', 'auths', 'name' );`

There is also a procedure that will update statistics for all the schemas: CALL sys.analyze();

Table Data Dump

I will read from one system table. We'll see that this table is empty.
SELECT * FROM sys.dump_statements; I will now call this procedure:
CALL sys.dump_table_data( 'sys', 'auths' );

We can empty this table normally:
DELETE FROM sys.dump_statements;

Eval Procedure

CREATE OR REPLACE PROCEDURE TableWithBiggestID() BEGIN DECLARE TableName VARCHAR(50); SET TableName = ( SELECT name FROM sys.tables WHERE id = ( SELECT MAX( id ) FROM sys.tables ) ); CALL sys.eval( 'SELECT * FROM ' || TableName || ';' ); END; For that task, I will use stored procedure "sys.eval". It will transform text into statement.

This function is only available to administrator.

Vacuum

Vacuuming is a maintenance process where we clean databases of obsolete data and defragment columns. After deleting or updating many rows, old records will not be deleted but will only be marked as deleted. This can lead to excessive disk usage and prevent sequential reading of column values.

We can manually vacuum one table or one column. CALL sys.vacuum('voc', 'voyages'); CALL sys.vacuum('voc', 'voyages', 'number');

It is possible to schedule column vacuuming at regular intervals. The interval is expressed in seconds. CALL sys.vacuum('voc', 'voyages', 'number', 3600);
This is how we stop the schedule.

CALL sys.stop_vacuum('voc', 'voyages','number');

Session Procedures

Session Procedures for Users

User can read his own session ID.
SELECT sys.current_sessionid(); User can limit memory consumption for his queries. This is measured in MB.

CALL sys.setmemorylimit(500); User can disable his memory limit if he call this procedure with the argument equal to zero.

CALL sys.setmemorylimit(0);

Users can set query timeout to 1 second.

CALL sys.setquerytimeout(1); If our query is longer than 1 second, error message will appear. Zero will reset query timeout.
CALL sys.setquerytimeout(0);

We can limit session to 3 seconds.
CALL sys.setsessiontimeout(3); This will happen if we run a query after 3 seconds. Zero will reset session timeout.

CALL sys.setsessiontimeout(0);

Users can choose their optimizer.
CALL sys.setoptimizer('minimal_pipe'); We can find a list of optimizers in the system table "sys.optimizers". Default optimizer is "default_pipe".

We can limit the number of threads that user queries can use.
CALL sys.setworkerlimit(8); Zero argument will remove this limit.
CALL sys.setworkerlimit(0);

Client Info Procedure for Users

I will change one of these properties.
CALL sys.setclientinfo('ClientHostname', 'OldServer' ); We can read current values of these properties in the "sys.sessions".
SELECT * FROM sys.sessions;

Session Procedures for Admins

`CALL sys.setmemorylimit( 1, 100 );`	Admin can limit memory usage to 100 MB.	We disable this limit by setting it to zero.
`CALL sys.setoptimizer( 1, 'sequential_pipe');`	Admin can set optimizer.	Default optimizer is "`default_pipe`".
`CALL sys.setquerytimeout( 1, 8 );`	User's queries will time out after 8 sec.	Zero will disable query time out.
`CALL sys.setsessiontimeout( 1, 30 );`	Session will time out after 30 seconds.	Zero will disable session time out.
`CALL sys.setworkerlimit( 1, 8 );`	User's queries are limited to 8 threads.	Zero will disable this limit.
`CALL sys.stopsession( 1 );`	We can stop user's session.

Queries Que

The Size of Queries Que

I will quit the session, and I will stop the "voc" database.
quit
monetdb stop voc After that I will start the database, and I will login to it.
monetdb start voc
mclient -u monetdb -d voc --password monetdb

The result is a table that has a history of the last run queries. This table was emptied when we restarted the database, so now it only has 2 queries.

Changing The Que Size

The number of queries saved is the same as the maximal number of clients that can connect to database. I will quit my session. I will stop my database and then I will change "nclients" property of the database. quit
monetdb stop voc
monetdb set nclients=10 voc I will log in into database.
mclient -u monetdb -d voc

We can read the current value of "nclients" property with this query.
select * from environment where name = 'max_clients';

Management of Queries

There are five possible statuses.

Query is executing. running

Query is halted.
paused

Query is stopping.
stopping

Query is stopped.
aborted

Query is executed. finished

I will open one more tab in the terminal, where I will log in as a admin.

mclient -u monetdb -d voc --password monetdb I will use this query on the right. This is recursive CTE that uses LEFT OUTER JOIN. Such query will never end. It is eternal.

I will run this query in the first terminal tab, and I will check the result in the second terminal table. WITH recursive numbers AS ( SELECT 1 UNION ALL SELECT 1 FROM numbers LEFT OUTER JOIN sys.auths ON 1 = 1 ) SELECT * FROM numbers;

The last query in my "sys.queue" is number 93 ( column "tag" ). I know that when I run my eternal query, it will get number 94. I will run eternal query.

I will run this statement in the terminal second tab. My query will have status "running". SELECT status FROM sys.queue WHERE tag = 94;

I can resume my query. Status will change again. CALL sys.resume( 94 ); SELECT status FROM sys.queue WHERE tag = 94;

Even when we use "Ctrl+C" to stop query, its status will become "aborted". SELECT status FROM sys.queue WHERE tag = 94;

If we are admin we can manage queries of other users. For that we must provide the name of a user and his query number ( from column "tag" ).

--CALL sys.pause( 999, 'User1'); --CALL sys.resume( 999, 'User1' ); --CALL sys.stop( 999, 'User1');

0580 Mclient for MonetDB

MonetDB / By Bizkapish / September 7, 2025 October 12, 2025

Mclient is the MonetDB command line client that connects to the MonetDB server to execute SQL commands. We have used this client in all the previous videos, but now we will see how we can make better use of it.

General Information

Information About Our Database

In one of the previous blogposts "link", I created database farm "DBfarm". Inside of it we have a database "DBfarmDB". I will now login to that database as an admin and I will create some database objects. The objects will be created in the "sys" schema.
monetdbd start /home/fffovde/Desktop/DBfarm
monetdb set readonly=no DBfarmDB #if it is still readonly
mclient -u monetdb -d DBfarmDB #password monetdb CREATE TABLE tablo( Number INT ); CREATE VIEW viewo( Number ) AS SELECT Number from tablo; CREATE SEQUENCE seqo; CREATE FUNCTION funco() RETURNS INTEGER RETURN SELECT 2; CREATE SCHEMA schemo;

Query Execution Control

Query Formatting

We can control how the result of a query will be presented ( default is "sql" ). Let's run "SELECT * FROM sys.auths;" with different modes.

If we don't want to see the result of a query, we can use "trash" and "rowcount" modes. \f trash --trash will return nothing \f rowcount -- only row count will return

There are some special modes for the CSV.

The Number of Rows

When we run query that will return a lot or rows, we will not be able to see all of them. They will just whiz by before our eyes. We want to control how the rows are displayed. We want to see them page by page. For that we will use "\r" command.

We will exit paging mode by setting the command to minus one "\r -1".

Width of a Table

Auto Commit Mode

Logging and Monitoring

I will leave the session and then I will log in, just to clear the history.
quit mclient -u monetdb -d DBfarmDB --password monetdb I will turn on logging and tracing on mclient. This is mostly useful for debugging.
\L /home/fffovde/Desktop/FileLog --log to this file
\X --trace everything that mclient is executing

I will again quit the session, and log again,
so I can change current directory. quit cd /home/fffovde/Desktop mclient -u monetdb -d DBfarmDB --password monetdb

SQL Input and Output

Other Backslash Commands

We can always exit mclient with the "\q", or "quit".

Dash Commands

When we log in to mclient, we can provide an IP address, port number, username and database name. There are many more of these arguments # mclient -h 192.168.100.124 -p 50001 -u monetdb -d DBfarmDB

Already Known Commands

Many commands exist in both backslash and dash version. I will give you example of such commands here.
During this example I will alternately login and quit, because dash commands can be run only during the login.

With "-a" switch we can turn off autocommit for the whole session: "mclient -u monetdb -d DBfarmDB -a".

History, Null and Timezone

I will login again with some new options:
mclient -u monetdb -d DBfarmDB -z -n "zero" -H Option "-z" is for disabling the time zone. Statement:
SELECT CURRENT_TIME;
, will give me Greenwich time, not my local time.

Getting Data from URLs

I will create a new table and then I will try to get data from the internet to insert into that table. Mclient will refuse to do that.

CREATE TABLE IndustryList( Industry VARCHAR(50) ); 
COPY INTO IndustryList FROM 'https://cdn.wsform.com/wp-content/uploads/2020/06/industry.csv' ON CLIENT;