Problem The scripts attached to this issue can be used to gather and export database usage statistics for databases in version 6.x. Solution We provide scripts for three different purposes: 3rdLevelStatsIndices.sql: statistics on indices and database objects 3rdLevelStatsLastDay.sql: statistics of the last day 3rdLevelStatsHourly.sql:hourly statistics for the last x days (x is a script parameter) The script exportStatsHourly.sql contains a parameter which defines the number of days in the past to export statistics for. When executing the script via the command line or the GUI the user will be prompted to enter this parameter. NOTE: The size of the generated CSV's vary and could be quite large - make sure that there is enough space on the directory that you are saving them to. The "days" parameter is using EXAplus syntax, which may have to be modified for other clients. The scripts use EXPORT to LOCAL CSV, which requires clients using the Exasol JDBC driver (odbc, ADO etc would not work).  
View full article
This article provides steps to manage session-level use of resources when there are a high number of sessions attached to Exasol.
View full article
This article gives general information about Exasol's indices.
View full article
Problem You have successfully created a LDAP user, but the user cannot log in. There might be a configuration problem with your LDAP server, the distinguished name might be wrong or any other database independent problem. Solution Try to connect/bind to your LDAP server with the ldapsearch command line tool. If you cannot connect with ldapsearch, the database won't be able to connect the LDAP user. Here is a simple example: ldapsearch -v -x -D "uid=Ben,ou=People,dc=myserv,dc=com" -w my_password -H ldap://my_ldap_server.com  
View full article
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      
View full article
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
View full article
Question What is a smart table replication and how can the replication border be modified? Answer General information on smart table replication Replicates are used "on-the-fly" for local joins if a table is "small" regarding the threshold. A replicated table accesses data directly from other nodes' database memories and keeps a local copy in its own DBRAM. If a replicated table is modified, only changed data is reloaded into database memories of other nodes. Modified tables and subselects cannot be used with smart table replication. Table replication border does not apply to those.   💡 Using large replicated tables might cause decreased performance. Queries with expensive table scans (filter expressions) or between / cross joins may fall into this category. Soft replication border A table will be replicated if none of the thresholds below are reached. The table size threshold refers to the RAW_OBJECT_SIZE like in EXA_*_OBJECT_SIZES. The replication borders can be modified through   extra database parameters   in the web interface: -soft_replicationborder_in_numrows=<numrows> [ default is 100000 rows] -soft_replicationborder_in_kb=<kb> [ default is 1000000 -> 1GB] Additional References  replication-border-in-exasol-6-1 
View full article
This article shows you how to calculate the backup duration from the database
View full article
Question What is a replicated table and how can the replication border be modified? Answer Replicates are used "on-the-fly" for local joins if a table is "small" regarding the threshold. A replicated table accesses data directly from other nodes' database memories and keeps a local copy in its own DBRAM. If a replicated table is modified, only changed data is reloaded into database memories of other nodes. Modified tables and subselects cannot be used with smart table replication. Table replication border does not apply to those.   💡 Using large replicated tables might cause decreased performance. Queries with expensive table scans (filter expressions) or between / cross joins may fall into this category. Replication border A table will be joined by smart table replication if it has fewer or equal rows than the threshold below. The replication borders can be modified through   extra database parameters   in the web interface: -soft_replicationborder_in_numrows=<numrows> [ default is 100000 rows]
View full article
Problem Sometimes databases growing too much on data. It is possible to set size limits for Exasol databases, so as to prevent databases from growing too much on data. This limit can be set for a database instance using database parameters. Solution Configuration setup You can configure local database limit setting the the following database parameters: Parameter Default Description rawSizeLimitInMiB 0 (no limit) Raw data size limit of the database in MiB. memSizeLimitInMiB 0 (no limit) Memory data size limit of the database in MiB. sizeLimitWarningPercent 85 Warning trigger margin. When reaching the stated % of the limit a warning message will be logged in EXAoperation. You can either choose a raw data or a memory data limit, but not both. These parameters are set in the Extra DB Parameters field of the database instance screen:   Limit checking When the Exasol database reaches 105% of the given size limit, no more data inserts are permitted. This impacts the following SQL actions: • IMPORT • INSERT • CREATE TABLE AS • MERGE • SELECT INTO All such statements are rejected with a SQL exception alike the following: [R0010] Statement currently not allowed because latest database raw size of 54.1 GiB exceeded local raw size limit of 50.0 GiB (108.2%). Please increase your limit or delete some data and FLUSH STATISTICS. To get the database back to normal operation, you have to reduce its data size below 100% of the limit. You can achieve this by either restarting the database with no or a larger limit, or by deleting or dropping table data. Whenever your data size passes critical limit margins at 85%, 105% and 100%, you get an EXAoperation warning, error or notice message respectively. Additional References For more information on managing data inserting restrictions and license limits, please consult   this article.  
View full article
Background Exasol database automatically computes and stores column statistics on demand, e.g. when joining tables for the first time. Those statistics consist of distinct estimates, minimum & maximum values, balancing, and other information. They are used by our query optimizer for estimation of join costs, filter sensitivities, etc. The computation of column statistics performs a complete column scan. Therefore it might be expensive, especially for large tables and/or tables not having all data in memory. This means that a query doing statistics computation may experience a significant slow down compared to the next execution. Column statistics are maintained as part of DML statements. They are recomputed if a significant amount of data has been changed since the last computation. Explanation Problem Description Due to format changes, all column statistics are invalidated during an update from EXASOL 5.0 to EXASOL 6.0. Required User Action   After the update to EXASOL 6.0, we recommend to recompute the statistics for the whole database to avoid any potential unexpected performance losses. Please note that the following command is introduced with version 6.0.4 (see EXASOL-2110 ). ANALYZE DATABASE REFRESH STATISTICS;   Alike other multi-table statements, ANALYZE DATABASE does an implicit COMMIT after each table minimizing transaction conflicts.   Time Estimation It is often useful to obtain an estimate on the duration of the ANALYZE DATABASE REFRESH STATISTICS statement. The query below delivers such an estimate (measured in seconds) when running before the update (that is, on EXASOL 5.0, while statistics are still valid): select cast ( zeroifnull( sum (raw_object_size) / 1024 / 1024 / 150 / nproc() ) as dec (18, 1) ) as COLUMN_STATISTICS_REFRESH_SECONDS from "$EXA_COLUMN_SIZES" where (column_schema, column_table, column_name ) in ( select column_schema, column_table, column_name from "$EXA_COLUMN_STATISTICS" where -- filter does not work on 6.0 before the REFRESH min_value_estimate is not null ); Additional References https://community.exasol.com/t5/database-features/exa-statistics/ta-p/1413
View full article
This article describes how to do data loading performance tests.
View full article
This solution describes how to determine all granted system and object privileges per user. A license for "Graph Search" is required to run the attached query.
View full article
Background Impersonation is a new feature in Exasol 6.1. It allows for switching to a different effective user during a session. Use this to impersonate another user identity. Explanation The new system privilege IMPERSONATE ANY USER has been granted to sys and to the DBA role. This allows sys respectively grantees of the DBA role to become any user without having to specify their password: -- Example 1: sys is connected and becomes fred: IMPERSONATE fred; Otherwise, the IMPERSONATION ON <user_name> privilege can be granted to a user that should be allowed to impersonate that other user. -- Example 2: bob is allowed to impersonate sys GRANT IMPERSONATION ON sys TO bob; Using the IMPERSONATE command, users can change the effective user within their sessions: -- Example 3: bob impersonates sys, so that he has sys' privileges SELECT current_user ; -- shows BOB IMPERSONATE sys; SELECT current_user ; -- shows SYS The following system tables contain information about impersonations: EXA_USER_SESSIONS, EXA_ALL_SESSIONS, EXA_DBA_SESSIONS: The column USER_NAME shows the user connected to the database; that is the user who opened the session. The column EFFECTIVE_USER shows the current effective user after impersonation. Queries are executed with the privileges of the effective user. EXA_DBA_AUDIT_IMPERSONATION: IMPERSONATOR: The user who impersonates (before executing the IMPERSONATE command). IMPERSONATEE: The new effective user (after executing IMPERSONATE). SESSION_ID, STMT_ID: The session id and statement id of the IMPERSONATE command withing this session. Mind that EXA_DBA_AUDIT_... tables are only populated with data if auditing is enabled in the database settings in EXAoperation. EXA_DBA_AUDIT_SQL does not contain any information about the effective user that executed a SQL statement. EXA_DBA_AUDIT_SESSIONS shows only the user that opened the connection. The following query adds an EFFECTIVE_USER column to the EXA_DBA_AUDIT_SQL. It shows for every query with whose user's privileges a query was executed: with impersonations as ( select stmt_id + 1 as first_stmt_id, lead(stmt_id, 1, 999999999999) over ( partition by session_id order by stmt_id ) as last_stmt_id, impersonatee as effective_user, session_id from exa_dba_audit_impersonation ) select nvl(ai.effective_user, se.user_name) effective_user, sq.* from exa_dba_audit_sql sq join exa_dba_audit_sessions se on sq.session_id = se.session_id left join impersonations ai on sq.session_id = ai.session_id and sq.stmt_id between ai.first_stmt_id and ai.last_stmt_id where sq.session_id = current_session order by stmt_id; Additional References See here for a video that explains impersonation:   https://www.youtube.com/watch?v=h2Mrbd0r67k for documentation   https://docs.exasol.com/sql/impersonate.htm   
View full article
Top Contributors