Procedures

Procedures dinther Wed, 07/22/2020 - 17:34

MonetDB provides the following groups of built-in SQL procedures.

System procedures

System procedures dinther Wed, 07/22/2020 - 17:40
Procedure Description Example
sys.analyze(minmax int, samplesize bigint) create or update sys.statistics data for all tables in all schemas. When minmax is 0 the expensive unique count operation is not performed. When samplesize is > 0 only the specified sample size number of rows are analysed.
Warning: Execution could take a long time depending on the size of your database.
call sys.analyze(0, 4000)
sys.analyze(minmax int, samplesize bigint, sch string) create or update sys.statistics data for all tables data in schema sch. When minmax is 0 the expensive unique count operation is not performed. When samplesize is > 0 only the specified sample size number of rows are analysed. call sys.analyze(1, 0, 'sys')
sys.analyze(minmax int, samplesize bigint, sch string, tbl string) create or update sys.statistics data of table tbl in schema sch. When minmax is 0 the expensive unique count operation is not performed. When samplesize is > 0 only the specified sample size number of rows are analysed. call sys.analyze(1, 0, 'sys', 'statistics')
sys.analyze(minmax int, samplesize bigint, sch string, tbl string, col string) create or update sys.statistics data of column col in table tbl in schema sch. When minmax is 0 the expensive unique count operation is not performed. When samplesize is > 0 only the specified sample size number of rows are analysed. call sys.analyze(1, 0, 'tst', 'weblog', 'url')
sys.clearrejects() clear the rejects table from possible errors encoutered from previous COPY INTO tbl FROM 'datafile.csv' ... BEST EFFORT execution call sys.clearrejects()
sys.createorderindex(schm string, tabl string, col string) create an ordered index on a specific column. The index name and location will be determined based on the schema name, table name and column name. call sys.createorderindex('schemaX', 'tableY', 'columnZ')
sys.droporderindex(schm string, tabl string, col string) remove a previously created ordered index call sys.droporderindex('schemaX', 'tableY', 'columnZ')
sys.hot_snapshot(tarfile string) write a snapshot (a binary copy of all data files) of the database into a (potentially very big) tar file. Optionally the tar file can be compressed by adding an extra file name suffix .lz4 or .gz or .bz2 or .xz. See HotBackup for details. call sys.hot_snapshot('/tmp/db_x_2020_06_30.tar.gz');

call sys.hot_snapshot(R'D:\Temp\voc_backup_2020_07_27.tar.gz');
sys.pause(tag bigint) pause the execution of a (long) running statement using its tag id.
run query: select * from sys.queue; to list the running statements
call sys.pause(842)
sys.querylog(filename string) use file filename to log queries call sys.querylog('/tmp/queries_log_2020_07_27.txt')
sys.resume(tag bigint) resume execution of a previouly paused statement call sys.resume(842)
sys.resume_log_flushing() resume logging queries of a previouly suspended query log call sys.resume_log_flushing()
sys.shutdown(delay tinyint) close all other client connections after delay seconds call sys.shutdown(20)
sys.shutdown(delay tinyint, force bool) close all other client connections after delay seconds, if force is set then always stop the system the hard way call sys.shutdown(20, true)
sys.stop(tag bigint) stop/abort execution of a running statement call sys.stop(842)
sys.storagemodelinit() initialize the data for the storage model call sys.storagemodelinit()
sys.suspend_log_flushing() suspend logging of queries call sys.suspend_log_flushing()

 

Session procedures

Session procedures dinther Wed, 07/22/2020 - 19:09
Procedure Description Example
sys.setmemorylimit("limit" int) set memory limit in MB for current session. 0 will disable any memory limit. call sys.setmemorylimit(500)
sys.setoptimizer("optimizer" string) change optimizer pipeline for current session call sys.setoptimizer('oltp_pipe')
sys.setprinttimeout("timeout" integer) set print timeout in milliseconds for current session. 0 will disable timing out. call sys.setprinttimeout(1000)
sys.setquerytimeout("query" int) set query execution timeout in milliseconds for current session. 0 will disable timing out. call sys.setquerytimeout(8000)
sys.setsession("timeout" bigint) set session timeout in milliseconds for current session
Warning: This procedure is Deprecated as of Jun2020 (11.37.7). Use sys.setsessiontimeout("timeout") instead.
call sys.setsession(30000)
sys.setsessiontimeout("timeout" int) set session timeout in milliseconds for current session. 0 will disable timing out. call sys.setsessiontimeout(30000)
sys.settimeout("query" bigint) set query timeout in milliseconds for current session
Warning: This procedure is Deprecated as of Jun2020 (11.37.7). Use sys.setquerytimeout("query") instead.
call sys.settimeout(8000)
sys.settimeout("query" bigint, "session" bigint) set query and session timeout in milliseconds for current session
Warning: This procedure is Deprecated as of Jun2020 (11.37.7). Use sys.setquerytimeout("query") and sys.setsessiontimeout("timeout") instead.
call sys.settimeout(8000, 30000)
sys.setworkerlimit("limit" int) set number of worker threads limit for current session. 0 will disable any limit of worker threads. call sys.setworkerlimit(16)

 

Session procedures for monetdb admin user only

To list all sessions info in your MonetDB server first run query: SELECT * FROM sys.sessions;

Procedure Description Example
sys.setmemorylimit(sessionid int, "limit" int) set memory limit in MB for session with sessionid. 0 will disable any memory limit.
You will need monetdb admin privileges.
call sys.setmemorylimit(2, 500)
sys.setoptimizer(sessionid int, "optimizer" string) change optimizer pipeline for session with sessionid.
You will need monetdb admin privileges.
call sys.setoptimizer(2, 'minimal_pipe')
sys.setquerytimeout(sessionid int, "query" int) set query execution timeout in milliseconds for session with sessionid. 0 will disable timing out.
You will need monetdb admin privileges.
call sys.setquerytimeout(2, 8000)
sys.setsessiontimeout(sessionid int, "timeout" int) set session timeout in milliseconds for session with sessionid. 0 will disable timing out.
You will need monetdb admin privileges.
call sys.setsessiontimeout(2, 30000)
sys.setworkerlimit(sessionid int, "limit" int) set number of worker threads limit for session with sessionid. 0 will disable any limit of worker threads.
You will need monetdb admin privileges.
call sys.setworkerlimit(2, 16)
sys.stopsession(sessionid int) stop session with sessionid.
Caution: This procedure stops the specified session which may be undesired.
You will need monetdb admin privileges.
call sys.stopsession(2)

 

Querylog procedures

Querylog procedures dinther Thu, 07/30/2020 - 22:45
Procedure Description Example
sys.querylog_disable() stop logging queries call sys.querylog_disable();
sys.querylog_empty() empty the query log call sys.querylog_empty();
sys.querylog_enable() start logging queries call sys.querylog_enable();
sys.querylog_enable(threshold_in_ms integer) start logging queries but only the ones which execution time exceeds the threshold_in_ms time. call sys.querylog_enable(123);

 

These procedures are used with: QueryTiming / QueryHistory.

 

Profiler procedures

Profiler procedures dinther Thu, 07/23/2020 - 15:20
Procedure Description Example
profiler.setheartbeat(beat int) Set heart beat performance tracing call profiler.setheartbeat(100)
profiler.setlimit(lim integer) Set profiler limit call profiler.setlimit(500)
profiler.start() Start offline performance profiling call profiler.start()
profiler.stop() Stop offline performance profiling call profiler.stop()

Note: You must include the profiler. prefix for these procedures and functions (see examples) in order to work properly.

Profiler functions

Function Return type Description Example Result
profiler.getlimit() int Get profiler limit select profiler.getlimit() 500

 

Logging procedures

Logging procedures dinther Thu, 07/23/2020 - 15:51
Procedure Description Example
logging.flush() Flush the buffer explicitly call logging.flush()
logging.resetadapter() Resets the adapter back to the default: BASIC call logging.resetadapter()
logging.resetcomplevel(comp_id string) Resets the log level for a specific component back to the default: ERROR call logging.resetcomplevel('SQL_REWRITER')
logging.resetflushlevel() Resets the flush level back to the default: INFO call logging.resetflushlevel()
logging.resetlayerlevel(layer_id string) Resets the log level for a specific layer back to the default: ERROR call logging.resetlayerlevel('GDK_ALL')
logging.setadapter(adapter_id string) Sets the adapter call logging.setadapter('BASIC')
logging.setcomplevel(comp_id string, level_id string) Sets the log level for a specific component call logging.setcomplevel('SQL_PARSER', 'DEBUG')
logging.setflushlevel(level_id string) Sets the flush level call logging.setflushlevel('WARNING')
logging.setlayerlevel(layer_id string, level_id string) Sets the log level for a specific layer call logging.setlayerlevel('MAL_ALL', 'DEBUG')

Note: You must include the logging. prefix for these procedures and functions (see examples) in order to work properly.

For explanation how to use the logging procedures see Tracer Tutorial

Logging functions

Function Return type Description Example Result
logging.compinfo() table("id" int, "component" string, "log_level" string) Returns a table with for each component its id and current logging level select * from logging.compinfo()
id component log_level
0 ACCELERATOR M_ERROR
1 ALGO M_ERROR
2 ALLOC M_ERROR
3 BAT_ M_ERROR
4 CHECK_ M_ERROR
5 DELTA M_ERROR
6 HEAP M_ERROR
7 IO_ M_ERROR
8 PAR M_ERROR
9 PERF M_ERROR
10 TEM M_ERROR
11 THRD M_ERROR
12 GEOM M_ERROR
13 LIDAR M_ERROR
14 FITS M_ERROR
15 SHP M_ERROR
16 SQL_PARSER M_ERROR
17 SQL_TRANS M_ERROR
18 SQL_REWRITER M_ERROR
19 SQL_EXECUTION M_ERROR
20 SQL_STORE M_ERROR
21 MAL_WLC M_ERROR
22 MAL_REMOTE M_ERROR
23 MAL_MAPI M_ERROR
24 MAL_SERVER M_ERROR
25 MAL_OPTIMIZER M_ERROR
26 GDK M_ERROR

For convenience there is also a system view logging.compinfo which selects all from logging.compinfo().
So you can also use query: SELECT * FROM logging.compinfo to get the same result.

 

Capture and Replay procedures

Capture and Replay procedures dinther Thu, 07/23/2020 - 16:56

Workload Capture procedures

Procedure Description Example
wlc.beat(duration int) set beat call wlc.beat(123)
wlc.flush() flush capture data call wlc.flush()
wlc.master(path string) set master with path call wlc.master('dbfarm/dbfree')
wlc.master() set master call wlc.master()
wlc.stop() stop capture call wlc.stop()

Note: You must include the wlc. prefix for these procedures and functions (see examples) in order to work properly.

Workload Capture functions

Function Return type Description Example Result
wlc.clock() clob get clock as string select wlc.clock()  
wlc.tick() bigint get tick select wlc.tick()  

 

 

Workload Replay procedures

Procedure Description Example
wlr.accept() accept the error reported an skip the record call wlr.accept()
wlr.beat(duration integer) control the interval for replication call wlr.beat(123)
wlr.master(dbname string) set database name of master call wlr.master('dbfree')
wlr.replicate(id integer) run replicator until condition is met call wlr.replicate(12)
wlr.replicate(id smallint) run replicator until condition is met call wlr.replicate(12)
wlr.replicate(id tinyint) run replicator until condition is met call wlr.replicate(12)
wlr.replicate(pointintime timestamp) run replicator until condition is met call wlr.replicate(timestamp '2020-07-25 09:00:00')
wlr.replicate(id bigint) run replicator until condition is met call wlr.replicate(12)
wlr.replicate() run it forever call wlr.replicate()
wlr.stop() stop replicator call wlr.stop()

Note: You must include the wlr. prefix for these procedures and functions (see examples) in order to work properly.

Workload Replay functions

Function Return type Description Example Result
wlr.clock() clob get clock as string select wlr.clock()  
wlr.tick() bigint get tick select wlr.tick()