Many database browsers provide the functionality to create the DDL for the database. This lua script provides the same functionality based on system tables.

This article will show you how you can create a synchronization of LDAP groups with Roles and Users in the Database

Generally speaking, NULL is not a special value, but it represents an undefined value. This article describes how to work with NULL values and the types of valid comparisons

Problem To reproduce certain problems, Exasol support may ask you to send DDL statements for required tables and views. At this point you have two choices: Start drilling around to find each and every required view / table etc. This may be a lot of work and may end up with some communication round trips in case you overlooked a dependency or two. Skip all that and just send us the full DDL for all schemas in your database instance. Just let us sort out what is needed and what is not. Both options are not optimal. Solution The attachment of this article contains a procedure script (Lua) that can create DDL statements for recursive dependencies of a view. The DDL are presented as a single-column result-set and are ready for copy/paste into a text editor (or EXAplus) for saving. Example Call Script call execute script meta.create_DDL( 'DUT' , 'TRUNK' ); Example output --DDL created by user SYS at 2017-11-14 09:44:59.554000 --========================================-- -- table dependencies -- --========================================-- CREATE SCHEMA "DUT"; CREATE TABLE "DUT"."TAB1"( "I" DECIMAL(18,0) IDENTITY NOT NULL, "J" DECIMAL(3,0) ); -- SYSTEM TABLE: SYS.EXA_METADATA --========================================-- -- function dependencies -- --========================================-- function func( param decimal(3) ) returns decimal(3) as begin return sqrt(param) * (select max(i) from dut.tab1); end / --========================================-- -- script dependencies -- --========================================-- CREATE LUA SCALAR SCRIPT "LUA_SCALAR" () RETURNS DECIMAL(18,0) AS function run() return decimal(10,18,0) end / --========================================-- -- view dependencies -- --========================================-- --> level 2 -- SYSTEM VIEW: SYS.CAT --> level 1 CREATE VIEW "DUT"."BRANCH" as ( select * from exa_metadata, cat ); -- final query/view: CREATE VIEW "DUT"."TRUNK" as ( select * from dut.tab1, dut.branch where func(j) > lua_scalar() ); Caution This script is work in progress and has only seen minimal testing so far. Things known not to work: Virtual Schemas – It is unlikely we would be able to clone your remote system anyway. Connections and IMPORT inside views – Like virtual schemas, it probably does not make much sense. Dependencies inside scripts – This is branded 'dynamic SQL' and our engine can not determine those dependencies without actually executing the script with specific input. SELECT dependencies inside functions – Just don't do that. Like with scripts, these dependencies to not show up in the system. There are the following prerequisites to run the script: "SELECT ANY DICTIONARY" privilege to access some of data dictionary views. Access to all direct and indirect dependencies of the view. If your model contains any of the above and it turns out to be relevant for reproduction of a problem, you might have to revert to "Skip all that" above. The "Copy Database" script in create-ddl-for-the-entire-database   may be of use then. Additional References https://www.exasol.com/support/browse/IDEA-359 https://community.exasol.com/t5/database-features/create-ddl-for-the-entire-database/ta-p/1417

This article explains the information found in EXA_ALL_OBJECT_SIZES

As Exasol only supports the transaction isolation level "SERIALIZABLE", this article looks at transactions and potential transaction conflicts.

NULL and nil have two different meanings in the context of Lua Programming. This article explains the difference

MERGE is designed to use a small UPDATE table to affect a larger FACT table. This article explains how it works

SQL statement to add locking information to your session system tables using the EXA_SQL_LAST_DAY statistics.

This article talks about using DECIMAL datatype to achieve more stringent results than DOUBLE datatype.

Question With Exasol's automated disk and memory management, it its not always easy to answer questions like How much data is stored in the database? How much disk space do I use? How much memory is in use? How much disk space is left? And how much more data can I actually store? Answer This article addresses a few of those questions by giving an overview of the relations between the different values one can read from Exasol's system tables or web management interface (EXAoperation). Overview Have a look at the following diagram showing the general relationships between many of the keywords used when talking about data sizes in Exasol:       Notes: The ratio between   RAW_   and   MEM_OBJECT_SIZE   is what Exasol commonly defines as   compression ratio The distinction between   RAW   and   MEM   sizes also applies to system data: Everything is   compressed in RAM   and mapped to/from disk. Usually there is no need to fit all of the data into DB RAM. Systems with a large amount of /passive data/ may perform well with 10% of   DB_RAM   compared to overall   MEM_OBJECT_SIZE A typical "first guess" estimation for required   DB_RAM   is about 10% of the expected   RAW_OBJECT_SIZE While   deleted blocks   in storage are reused by later operations, as a general rule, storage volumes   do not shrink   unless forced to do so. In   DB_RAM , the ratio between active data and temp data is flexible, but temp is hard-limited at 80%. Information Location Object Level Metric Location Comment RAW_OBJECT_SIZE System table   EXA_DBA_OBJECT_SIZES Theoretical value, never actually required MEM_OBJECT_SIZE System table   EXA_DBA_OBJECT_SIZES   Index size AUXILIARY_SIZE   in System table   EXA_DBA_INDICES   Statistics + Audit Size STATISTICS_SIZE   in System table   EXA_STATISTICS_OBJECT_SIZES   HDD_READ / HDD_WRITE N/A   Database Level Metric Location Comment RAW_OBJECT_SIZE System table   EXA_DB_SIZE_LAST_DAY   MEM_OBJECT_SIZE System table   EXA_DB_SIZE_LAST_DAY   Index Size System table   EXA_DB_SIZE_LAST_DAY   Statistics+Audit Size System table   EXA_DB_SIZE_LAST_DAY   HDD_READ / HDD_WRITE System Table   EXA_MONITOR_LAST_DAY   DB_RAM Size System table   EXA_SYSTEM_EVENTS You can assume that the database will always "use" all that RAM and does not yield to others. RECOMMENDED_DB_RAM_SIZE System table   EXA_DB_SIZE_LAST_DAY   TEMP_DB_RAM_SIZE System Table   EXA_MONITOR_LAST_DAY == (DB_RAM:temp + Temporary:used) DB_RAM:active data N/A   DB_RAM:temp see Node Level below   Persistent:size STORAGE_SIZE   in   EXA_DB_SIZE_LAST_DAY   Persistent:committed see Node Level below ~= ( MEM_OBJECT_SIZE + AUXILIARY_SIZE + STATISTICS_SIZE ) Persistent:deleted see Node Level below size = committed * (100 / USE )   Node Level EXASOL 6.0.0   introduces a new system table for more detailed storage usage:   EXA_VOLUME_USAGE Please refer to the user manual or column comments for details on the columns. Metric Location Comment Persistent:size sum(VOLUME_SIZE) Size is always synchronized across nodes Persistent:committed data sum(COMMIT_DATA)   Temporary:used sum(SWAP_DATA)   Temporary:unused sum(UNUSED_DATA)     Storage Level Metric Location Comment Persistent Volume Size EXAStorage -> (Labels: <dbname>_persistent) -> Size == Persistent:size from Database level Temporary Volume Size EXAStorage -> (Labels: <dbname>_temporary) -> Size   Exists Slave Segment? EXAStorage -> persistent -> Redundancy Gives total number of copies: 1 == no redundancy Free Disk Space EXAStorage -> Space on Disks -> sum(sum:free)   Free database (disk) space See  how-to-monitor-free-database-disk-space      

Questions What data can you read using subconnections? What data can you insert using subconnections? How to establish parallel connections? What about transactions in parallel mode? What about transactions in parallel mode? Answer You can use subconnections to read or insert data in parallel from and into the EXASOL server. This will increase performance considerably. Using this interface you can read/write data in parallel from different physical hosts or from different processes or in one process from different threads. What data can you read using subconnections? You need a statement that produces a large result set. The result set can be then retrieved in parts from the parallel connections. For small results this interface has no advantage. What data can you insert using subconnections? Like when reading data, using this interfaces only makes sense if you want to insert large amounts of data. You can simply insert rows into a table in EXASOL using a prepared insert statement. The data coming through the parallel connections will be put together by the server into the right table. How to establish parallel connections? First you need a normal JDBC connection. On this you can use the method   EnterParallel()   to start operating with subconnections. You will receive connection strings for all new parallel connections you can start now. Start the subconnections with auto commit off. After this you can start reading or sending data, nearly like in a normal connection. Attention: You can specify the maximum number of subconnections in   EnterParallel() . This number may be reduced by the server because only one subconnection is allowed per database node. You have to establish the subconnections by using all connection strings received from   Get Worker Hosts() . Subconnections can only be used after all connections have been established. What about transactions in parallel mode? Start the subconnections with auto commit off. Commits should be made only on the main connection after the subconections have inserted all data and they have closed the prepared statements. An Java example is attached. In the example a main connection reads the connection strings for the subconnections from the server. For each subconnection a thread is started that inserts a few rows. Commit is executed on the main connection. Then other threads read the data from the table.

This article gives general information about Exasol's indices.

This article describes how Unicode is supported in Exasol

Estimating work duration when doing reorganizations within Exasol.

Background 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) Prerequisites 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 Step 1 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 ) Step 2 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. Step 3 Start the VM and wait until Exasol is up and running.  Configuration of DNS server Step 1 Browse to EXAoperation and login. Step 2 Go to Network. In the System tab, click on "Edit". Step 3 Add one or two DNS servers reachable from the VM (e.g. "8.8.8.8" for an environment that can reach internet DNS servers) and click "Apply". Step 4 Log out. Additional Notes Troubleshooting Firewalls and/or company proxy servers may block or restrict traffic to internet hosts.

This article describes the difference between local and global joins, and how to convert them. 

This article is about  using the IMPORT command over JDBC to connect to your Exasol database and wrap the LUA-scripts you want to execute in parallel in the STATEMENT-Clause.

SOL: Metadata Backup Background How it works The file contains several scripts which will copy the DDL for all objects in the database. It also contains the necessary scripts to restore the metadata. If the config file is set up correctly, "_backup._sh" performs the following tasks: Creates the specified directory. This is where all of the files will be stored Connects to the database using an Exaplus profile (must be created beforehand) Once connected to the database, the script creates a schema "BACKUP_SCRIPTS" and 2 scripts which are used in the backup process. EXPORT statements are generated using the database script "BACKUP_SYS" for several system tables which can be referenced later on. The CSV files are saved in the './ddl/' path. A database "restore_sys.sql" script is created and saved in the './ddl/' path that includes all the commands neccesary to restore the system tables on a new "SYS_OLD" schema. The script executes the database script "METADATA_BACKUP" and creates DDL for all database objects, including schemas, tables, views, users, roles. Limitations in the script are listed at the end. Specifically, this script will read data from system tables and create the necessary CREATE statements which can be executed at a later time. The script creates a new schema, stores the data into a table in this new schema, and then exports the table contents into an SQL file to prevent formatting errors. After the export, the schema is dropped. The DDL and CSV's of the old tables are compressed and saved as a .tar.gz file in the './backups' directory or on a different location if "EXTERNAL_DIR" is set on the "config" file. Limitations Creating DDL based on system tables is not perfect and has some limitations and imperfections. To safeguard incorrect DDL creation, the script also saves the system tables which are used in the script. If a DDL is not created perfectly or if you discover imperfections/errors, you can query the system tables directly and create your own DDL. invalid (outdated) views and their DCL will not be created views and functions for which the schema or object itself was renamed still contain the original object names and may cause an error on DDL execution comments at the end of view text may cause problems GRANTOR of all privileges will be the user that runs the SQL code returned by the script passwords of all users will be 'Start123', except for connections where passwords will be left empty functions with dependencies will not be created in the appropriate order UDF scripts with delimited identifiers (in- or output) will cause an error on DDL execution If authenticating using Kerberos (< 6.0.8), please alter the script BI_METADATA_BACKUPV2 lines 155 and 156 Comments containing apostrophes (single-quote) will cause an error on DDL execution Note: This solution has been updated to include new permissions/rights in version 6.1 Prerequisites Linux system Exaplus installed Database is already created and is able to be connected from the system you are running the scripts Database user, which you will connect to the database with, has the following system privileges: CREATE SCHEMA CREATE TABLE CREATE SCRIPT SELECT ANY DICTIONARY How to create a Metadata Backup Concept? Step 1 Save the attached tar.gz file to a directory of your choice. The only requirements are that the system is Linux-based and that Exaplus command line is installed. Step 2 Unzip the file: tar xf metabackup_vYYYYMMDD.tar.gz Step 3 Create an Exaplus profile with all of the connection details, including database user, password, and connection string. Details on the parameters for Exaplus can be found in the user manual. Example: /usr/opt/EXASuite-6/EXASolution-6.0.10/bin/Console/exaplus -u [YOU_USER] -p [YOUR_PASSWORD] -c [DB IP Address]:8563 -wp metadata_backup_profile Step 4 Change directory to the newly created folder: cd metabackup Step 5 Edit config file with the help of following information: Global Section: EXAPLUS = Path to EXAPLUS excluding the exaplus. For the example above, it would be ' /usr/opt/EXASuite-6/EXASolution-6.0.10/bin/Console' PROFILENAME = The name of the profile created in the previous step as ( metadata_backup_profile   ) Backup Section: EXTERNAL_DIR = The path where the metadata backup will be stored if specified. Can be an external filesystem. Should be mounted or available beforehand SYSPATH = The path where metabackup_vYYYYMMDD.tar.gz was extracted to DB_NAME = The Database Name EXAPLUS_TIMEOUT = Timeout for Exaplus (default 300 seconds). If you want to prevent long-running queries, set the timeout accordingly. Please note, for very large databases, it might take over 5 minutes to run all of the scripts, so please set the timeout higher. EXAPLUS_RECONNECT = Reconnect tries for Exaplus if the connection fails. Default value is set to '1' Step 6 Make .SH files executable chmod 755 *.sh Step 7 Run backup.sh ./backup.sh or bash backup.sh SOL: Metadata Restore Background This script will import the CSV's created in the Backup and run all of the CREATE statements. The script opens an Exaplus session, creates a schema called 'SYS_OLD' containing the same system tables that were created in the backup, and then imports the CSV's into these tables. All of the CREATE statements are executed, which restores the 'structure' of the database, however all tables are empty. Note: During execution, the owner of all objects is the user running the script. At the end of the script, the owner of all schema changes to match the correct owner. To monitor errors, profiling is enabled by default. You can search through EXA_DBA_PROFILE_LAST_DAY to find commands which were rolled back Limitations If the database is not empty, some objects may fail to be created if they already exist in the database. Objects will not be overwritten. Limitations of the create DDL script may cause errors during the CREATE statements. Please check the restore log, profiling or auditing to identify statements which were not created successfully. If restoring to a database running a different version than the database from the backup, the IMPORT of old sys tables may fail due to different columns. Prerequisites Linux system Exaplus command line Database is already created and is able to be connected from the system you are running the scripts on It is recommended to start the database with auditing ENABLED Database user will need extensive CREATE privileges. It is recommended that the user running the scripts has DBA privileges as the following commands will be carried out: CREATE SCHEMA, TABLE, VIEW, SCRIPT, CONNECTION, ETC GRANT How to apply a  Metadata Restore? Step 1 The restore script can be run from the same system you ran the backup on or a different system. If running the restore on a new system, please follow steps 1-4 found in the Backup instructions to set up the files. NOTE: When setting up your exaplus profile, please enter the information for the database you are restoring to. Step 2 Unpack the backup tar into the directory of your choice tar xf ddl-backup-DB_NAME-YYYY-MM-DD-HH-Mi-SS.tar.gz Step 3 Edit config file with the following information from: Backup Section: SYSPATH = The path where metabackup.tar.gz was extracted to DB_NAME = The Database Name Restore Section: BACKUP_RESTORE_PATH = The path that you unpacked the backup file to (should end with '/ddls') RESTORE_SYS_SQL_PATH = The path containing the restore script Step 4 Run restore.sh ./restore.sh or bash restore.sh Additional References https://www.exasol.com/support/secure/attachment/78805/metadatabackup_v20190418.tar.gz  https://community.exasol.com/t5/database-features/create-ddl-for-the-entire-database/ta-p/1417 https://www.exasol.com/support/browse/IDEA-371

This article give suggestions on how to minimize transaction conflicts.