By default, an Exasol virtual machine (VM) is configured to use a host-only network. This configuration allows to access the database and the EXAoperation management interface locally from the host machine. Nevertheless, this configuration prevents the use of publically available hosts and services on the internet from the virtual machine. This How-To provides information about configuring Exasol to be able to access the internet and enables users to:
use DNS to access publicly reachable servers for making backups or in database IMPORT/EXPORT statements
use LDAP servers of your choice for database or EXAoperation accounts
use standard repositories for the installation of UDF packages
use the Exasol Version Check (only if this feature has not been disabled)
If not already done, import the Exasol OVA file into the VM tool of your choice (in Virtualbox: File -> Import Appliance). Accept the "End User License Agreement" and the "Privacy Statement" and wait until the image has been successfully loaded.
How to enable internet access for Exasol Community Edition
Configuration of VM network
Now, a new VM has been created. Change its network settings to use NAT (in Virtualbox: right click on VM -> Settings -> Network -> Adapter 1 -> Attached to NAT )
Define port forwardings to guest ports 8563 (database) and 443 (EXAoperation management interface) (in Virtualbox: Adapter 1 -> Port Forwarding -> Add rule -> Host Port 8563 and Guest Port 8563 -> Add rule -> Host Port 4443 and Guest Port 443). The port forwarding rules will enable you to use the VM from the physical host machine.
Start the VM and wait until Exasol is up and running.
Configuration of DNS server
Browse to EXAoperation and login.
Go to Network. In the System tab, click on "Edit".
Add one or two DNS servers reachable from the VM (e.g. "18.104.22.168" for an environment that can reach internet DNS servers) and click "Apply".
Firewalls and/or company proxy servers may block or restrict traffic to internet hosts.
When switching from EXAplus, you would like
to change the shortcut for executing the current statement
to do Auto completion
create LUA Scripts / UDFs
use of Favorites
Change keyboard shortcuts
When switching from EXAplus, the first thing that you would like to change is the shortcut for executing the current statement. In DBVisualizer this is set to Ctrl + . per default. In EXAplus on the other hand this shortcut is defined as Ctrl + Enter. To chance it go to Tools -> Tool Properties and open the Key Bindings Dialog in the tree, create a copy of the current keymap called exasol and change the Binding for Main menu -> SQL Commander -> Execute Current:
It's important to note that in DbVisualizer the auto completion of table/column names works a little bit different than in EXAplus. In EXAplus you had to type first the schema name to access the tables. In DBVisualizer on the other hand you can directly access the table or column name without the need to fully qualify the object. It's also important to note that in DBVisualizer if you are in the context of a schema only the tables of that schema are shown in the auto completion.
You can also customize the behavior of the auto-completion in Tools -> Tool Properties in the Dialog SQL Commander -> Auto Completion:
Creation of LUA Scripts / UDFs
In DBVisualizer LUA scripts / UDFs should be created as a SQL block.
The start of the SQL Block is --/ and the end of the block is defined with /:
CREATE LUA SCRIPT MY_CAT RETURNS TABLE AS
return query([[select * from cat]])
Per default keywords in DBVisualizer are displayed in uppercase, so if you open an existing LUA script keywords like are automatically transformed to uppercase, so the case sensitive LUA script is not working anymore, please change this setting in Tool Properties -> Tools - SQL Formatting to "Keep Case Asis"
Please also have a look at the official documentation http://confluence.dbvis.com/display/UG100/Executing+Complex+Statements
Favorites versus Bookmarks
In EXAplus frequently used SQL Statements have been stored in the favorite tab. In DBVisualizer you have a similiar feature called Bookmarks. Learn more in the official documentation: http://confluence.dbvis.com/display/UG100/Managing+Frequently+Used+SQL .
EXAplus commands like set autocommit off are not working in DBVisualizer. DBVisualizer has it's syntax for it's client commands starting with @, e.g. @set autocommit off; See also official documentation: ** http://confluence.dbvis.com/display/UG100/Using+Client-Side+Commands
Known Issues of DBVisualizer
The open schema call is currently not interpreted by DBVisualizer
Newly created schemas are not shown in the SQL Editor drop down menu -> Workaround reconnect the connection
R Scripts sent from a Windows OS using DBVisualizer can't be executed because of windows CRLF, Following Workaround
In the current version, DbVisualizer uses the platforms linefeed convention, i.e. CRLF for Windows and LF for all other platforms. There is a way to force DbVisualizer to use LF for the text it sends to the database regardless of platform:
1) Open the file named DBVIS-HOME/resources/dbvis-custom.prefs (where DBVIS-HOME is the DbVisualizer installation folder) in a text editor,
2) Add the following on a new row: dbvis.exasol.ReplaceCRLFwithLF=true
3) Restart DbVisualizer.
This article describes a way how to connect Spotfire to Exasol.
Download the EXASOL JDBC driver and extract the exajdbc.jar
How to connect Spotfire to Exasol
Copy the exajdbc.jar to to the Spotfire library directory, see the following link where this directory is located: https://community.tibco.com/wiki/tibco-spotfirer-jdbc-data-access-connectivity-details#toc-2
Restart the Spotfire Server so the EXASOL jdbc driver is loaded.
Start the Spotfire Server configuration tool
Go to the Configuration tab and create a new Data Source Template
In the Add data source template enter EXASOL as name and copy paste the following template:
<supports-catalogs> false </supports-catalogs>
<supports-schemas> true </supports-schemas>
<from max-length= "2000000" > String </from>
<from> Integer </from>
<from> Long </from>
<from> Float </from>
<from> Double </from>
Save the config, close the configuration tool and restart the server
Check the logs if the XML config for EXASOL was validated correctly
Use the Information Designer tool in the Spotfire Analyst to create a new Data Source
IBM Unica Campaign with MySQL template
[EXASOL] [EXASolution driver] syntax error, unexpected '<' [line x, column y] of [EXASOL] [EXASolution driver] function or script ADDDATE not found [line x, column y]
Unica generates SQL statements that are not identically supported by EXASolution.
Create a pre-processor script that adjusts the SQL statements to appropriate ones.
CREATE OR REPLACE LUA SCRIPT DATAMART.PPSCRIPT_MYSQL RETURNS ROWCOUNT AS function unicaproc(sqltext)
local tokens = sqlparsing.tokenize(sqltext)
for i=1,#tokens do
if string.upper(tokens[i]) == 'ADDDATE' then
tokens[i] = 'ADD_DAYS'
if tokens[i] == '<' and string.upper(tokens[i+1]) == 'CURRENT_DATE' then
tokens[i] = ''
if string.upper(tokens[i]) == 'UNSUPPORTED' and tokens[i+1] == '>' then
tokens[i] = ''
tokens[i+1] = ''
Activate this preprocessor script for all Unica session or system wide:
ALTER [SESSION|SYSTEM] SET sql_preprocessor_script=DATAMART.PPSCRIPT_MYSQL;