This article describes the contents of the SYS schema
View full article
This article describes the purpose of the Exasol Logserver
View full article
This article describes how to do data loading performance tests.
View full article
Question I would like to be able to find out which user created a table. Answer If you have enabled audit-logging you can query it as follows: CREATE SCHEMA myschema; create table myschema.testtable (a int ); flush statistics; select USER_NAME, SQL_TEXT FROM EXA_DBA_AUDIT_SESSIONS s JOIN EXA_DBA_AUDIT_SQL a ON s.SESSION_ID = a.SESSION_ID WHERE COMMAND_NAME= 'CREATE TABLE' AND INSTR(upper(SQL_TEXT),upper( 'myschema' ))>0 AND INSTR(upper(SQL_TEXT),upper( 'testtable' ))>0 and a.ERROR_TEXT is null order by START_TIME desc; Additional References https://docs.exasol.com/database_concepts/auditing.htm  https: //docs.exasol.com/sql/flush_statistics.htm 
View full article
Question How does Exasol determine the data type of a multiplication of different dataypes? Example: what data type is the result when you multiply database columns number (18,3) by number (18,9)? Can numeric overflows occur? Answer In operations with multiple operands (e.g. the operators +,-,/,*) the operands are implicitly converted to the biggest occurring data type (e.g. DOUBLE is bigger than DECIMAL) before executing the operation. This rule is also called numeric precedence. Numeric overflow can occur and result in a data exception - numeric value out of range. The result for this specific calculation would be decimal(36,12). Regularly the precision and scale were added. Examples: (12,0) * (15,3) = (27,3) (12,0) * (15,9) = (27,9) (18,3) * (18,9) = (36,12) For sums the scale is taken from the factor with the highest preciseness. (12,0) + (15,9) = (22,9) (15,3) + (15,9) = (22,9)
View full article
Problem When reading ODBC result sets in PHP, column names are truncated to 32 characters This is not a fault in the driver or communication, but a builtin limitation in the php code. Solution Compile PHP from source code after editing file ...<php-dir>/ext/odbc/php_odbc_includes.c: In "struct odbc_result_value", increase size of "char name [32] " to your needs.
View full article
Background You always get the highest performance if the data to be analyzed fits into main memory available to the database (your license). Explanation EXASolution stores data column-wise both in memory and on disk. Each column is spread across a number of physical blocks and of course across the nodes. Only blocks, required to compute the query, will be loaded into main memory. Indexes will be treated in the same way: indexes are physically stored in blocks and only the needed blocks will be loaded into main memory. Please refer to indexes   for more details on indexes. Typically, there's no need to hold the full set of data in memory. EXASolution loads the data requested by queries into main memory on demand (so called hot data) and swapps them out if needed. EXASolution monitors the memory consumption and provides you with a suggestion for the optimum sizing (RECOMMENDED_DB_RAM_SIZE_*). If you persistently observe, that "RECOMMENDED_DB_RAM_SIZE_AVG" is at least 30 to 50% higher than your license and you experience performance issues, it means you would need to take an increase of DBRAM into consideration. On the other hand, a deeper system analysis could help to identify queries causing high memory consumption. There are a number of ways for improving the performance and for reducing the memory consumption in EXASolution. In general, upgrading hardware/RAM or license should be bottleneck driven. To increase performance or it has measurably deterred over time, than we suggest to investigate the bottleneck and work on this by identifying critical queries and optimize them adding more nodes if your system is CPU bound adding more disks/nodes if the space runs out anyway adding a better network if your system is network bound increasing DBRAM if your system has lots of HDD_READ Additional References Please consider becoming an EXASOL Certified Performance Expert by attending our Performance Training: see   http://www.exasol.com/en/services/training/performance-expert/ Index creation:  https://community.exasol.com/t5/database-features   
View full article
Background This article will explain about the details on encryption at Exasol Database. It gives you answers for questions like : Is data transfer through client connection encrypted by default or not? How to check encryption is enabled in database? How to enable/disable encryption? What is the parameter forceProtocolEncryption used for How to enforce encrypted client connections ? and so on..   Explanation In Exasol database, when data is transferred through a network , the data is by default encrypted (from Exasol versions 6.0 and above). Exasol uses ChaCha20 encryption for JDBC, ODBC, ADO.NET, and CLI. For WebSockets, Exasol uses TLS v 1.2 encryption.  On all clients and drivers, the encryption can be enabled by using their respective connection string parameters, for example: EXAPlus: -encrpytion <ON|OFF> JDBC: encryption=<1|0> (1 = on, 0= off, default is 1) ODBC: ENCRYPTION=<"Y"|"N"> (Y is default) ADO.NET: encryption=<ON|OFF> (on is default)   How to check if data transferred was encrypted or not: One can check the ‘encrypted’ column in exa_dba_sessions or exa_dba_audit_sessions tables that encryption was set to true or false for that particular session.   The Parameter forceProtocolEncryption: In addition to these driver properties, one can set a database parameter in EXAoperation to force incoming connections to be encrypted. The parameter is: -forceProtocolEncryption=1. This can be done while creating a database or setting it in Exaoperation and restarting the database again.   Further Clarifications: If the parameter '-forceProtocolEncryption=1' is set to the database, it means that regardless of what the client requests, protocol encryption will be FORCED (i.e. required) by Exasol for the connection. If either Exasol or the client requests encryption, encryption will be used. So we can conclude that no matter which client/driver you use, if the parameter '-forceProtocolEncryption=1' is set over the database, then all the connections are encrypted.   Additional notes: With '-forceProtocolEncryption=1', clients are only rejected if they do not support encryption at all (e.g. older drivers). An unencrypted connection is only allowed if both Exasol and the client disable encryption. Having this parameter (forceProtocolEncryption=1) set, means that even if the client/driver side encryption is turned off then (with the exception of -- the driver being not old/does not support encryption) then the client/driver is forced to encrypt data. In other case (when this parameter would not have been set) then client/driver connection would be allowed to transfer data UNENCRYPTED.   Additional references: https://docs.exasol.com/planning/data_security.htm?Highlight=encryption#GeneralConcepts https://docs.exasol.com/sql_references/metadata/metadata_system_tables.htm bug related to this topic: EXASOL-2649  
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
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
Background In rare cases, to debug a problem Exasol will require detailed information regarding the status of the EXAplus client. As EXAplus is a java application, that information can be supplied as a   java stacktrace   using oracle's java SDK. Prerequisites Java SDK must be installed on the machine where EXAplus was started. The SDK can be downloaded here: https://www.oracle.com/java/technologies/javase-jdk8-downloads.html How to get a java stacktrace for EXAplus? While the following examples and paths were generated on the Windows (tm) operating system, the process does also apply to MacOSX and linux systems. Step 1:   Open a console Any console will do, depending on your operating system. Step 2: Change into the 'bin' directory of the JDK installation: cd "C:\Program Files\Java\jdk1.8.0_20\bin" Please note: The actual path may vary by operating system, installation and java version. This is only required if the bin folder is not included in your PATH variable. Step 3: Determine the PID of exaplus This can be done with the system's process monitor (windows task manager) or with the   jps   program that is part of the JDK: Here, we have two running java programs:   jps   itself and something named   Program . The latter is exaplus, with PID 2252. Step 4: Generate the stacktrace The actual stacktrace is produced with   jstack   from the java SDK: jstack's output can also be redirected into a file: Step 5: Attach the generated stacktrace to your ticket with Exasol.
View full article
This article describes what the error message " number of open result sets exceeds 250" means
View full article
This article explains what to do about Unica sending Unicode strings like N'String' to Exasol.
View full article
Problem EXASolution's ROWNUM is not compatible with Oracle's ROWNUM! While ROWNUM in Oracle can be used to limit output data of almost arbitrary statements, EXASolution implements ROWNUM while sticking to the overall SQL semantics: Anything you put into the WHERE clause of a statement filters   input data. To avoid confusion and seemingly wrong results, we only allow ROWNUM in situations where the result is in line with Oracle's semantic. Diagnosis Example to clarify: Assumed there's a table "customer" containing a large number of "Schmitts". select * from customer where c_name like 'Schmitt%' ; -- works, but too many rows select * from customer where rownum < 11; -- works, only ten rows , but no "Schmitt's" select * from customer where c_name like 'Schmitt%' and rownum < 11; -- Error, ROWNUM can't be combined with other conditions in where clause In the last statement, Oracle would first filter for all the Schmitts and only output the first 10 matches. Using strict SQL semantics, the filters on c_name and ROWNUM would be independent, meaning that only Schmitts appearing in the first 10 rows of the table get returned. As this is probably not what you expect (coming from Oracle), we prevent this statement:   ROWNUM has to be the only one condition in the where clause Solution The follwoing SQL depicts a Workaround / Solution: -- use a subselect select * from ( select * from customer where c_name like 'Schmitt%' ) where rownum < 11; -- works, ten rows with "Schmitt's" -- use LIMIT instead of ROWNUM select * from customer where c_name like 'Schmitt%' LIMIT 10; Additionally, there are some statements which generally don't allow the usage of ROWNUM select c_name with invalid primary key (c_custkey) from customer where rownum < 11; -- Error, ROWNUM cannot be used in combination with this statement
View full article
Background Filter on DOUBLE columns returns unexpected results.  The DOUBLE values you see in EXAplus may differ from the actual database values due to the JDBC double handling and rendering. Explanation As the DOUBLE data type is only an approximative numeric type, filters on DOUBLE columns may return unexpected results, due to the approximative nature of this data type. The datatype DOUBLE in the Exasol DB (including 6.x) is defined as an 64-Bit floating point value which represents values with a combination of an exponent and a fraction in binary form. This means that not every existing (numeric) value can be exactly represented by this type. We recommend to filter only on DECIMAL columns to avoid described problems. Additional References The actual value range of this type can be seen in our documentation: https://docs.exasol.com/sql_references/data_types/datatypedetails.htm
View full article
Problem 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. Solution 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' end if tokens[i] == '<' and string.upper(tokens[i+1]) == 'CURRENT_DATE' then tokens[i] = '' end if string.upper(tokens[i]) == 'UNSUPPORTED' and tokens[i+1] == '>' then tokens[i] = '' tokens[i+1] = '' end end return table.concat(tokens) end / Activate this preprocessor script for all Unica session or system wide: ALTER [SESSION|SYSTEM] SET sql_preprocessor_script=DATAMART.PPSCRIPT_MYSQL;
View full article
This article presents a fully home-made solution written in Lua to find textual differences.
View full article
This article shows give a workaround for addressing non-ascii characters with regular expressions by using the unicode equivalent. 
View full article
This article shows you how to view queries running at a point in time in the past
View full article
Blog snapshot In this first part of a series of blogs, you will learn: How to set up Lua Development Tools in Eclipse How to get coding support and what each function does Ways to make testing easier with MoreUnit   Every craft needs the right tools. Imagine a modern master cabinet maker trying to carve the wood with a butter knife - it's unthinkable.   Software development is no different. You need the right tools to code faster, reduce the risk of errors and make your job as easy as possible. But the key is knowing what's right for you - as there are a wide range of IDEs available, all with their own merits and challenges.  The Eclipse IDE is probably best know for its superb Java programming support. And as Eclipse was built around the idea of maximum extensibility, a large ecosystem of plug-ins has grown around it over the years. Which Eclipse plug-ins should you use? Today I want to draw your attention to a collection of Eclipse plug-ins called Lua Development Tools (LTD). A short disclaimer: while the project is stable and works nicely, no features have been added in quite a while, due to the lack of developer availability. How to Setup LTD in Eclipse 20.4 Download Eclipse IDE for Java programmers as a basis (we will learn later with that is a good pick for Lua too) Unpack it in your home directory e.g. under "Applications/Eclipse-LDT" Download the Dynamic Languages Toolkit (DLT) to the temp directory. This isn't part of Eclipse anymore so we need to install it by hand. In Eclipse IDE Help → Install new software... In dialog "Install": Click "Add" In dialog "Add Repository": Click "Archive" Select "/tmp/dltk-core-R-5.7.1-201703020501.zip" Name: "DLTK Core 5.7.1" Click "Add" Select "Uncategorized" in the software list: Click "Next", "Next", accept the license and finish the wizard. Don't restart yet. We have more to install. Help → Install new software... In dialog "Install": Click "Add" In dialog "Add Repository": Name: "Lua Development Tools (LDT)" Location: "http://download.eclipse.org/ldt/releases/stable" Click "Add" Click "Next", "Next", accept the license and finish the wizard. Again, don't restart just yet. Help → Eclipse Marketplace In dialog "Eclipse Marketplace" Find: "MoreUnit" Click "Install" Click "Confirm" Accept license Click "Finish" The package is unfortunately not signed. So Eclipse wants confirmation at this point that you want to install anyway. Restart. For now we are set. Later we will install another plug-in for even more convenience. Importing a project Let's dive in directly what the LDT can do for you. And to do so, we will use an existing Lua project, Exasol's remotelog. If you worked with a project in a Git repository before, this procedure will look very familiar. Window → Show View → Other... Select the "Git Repositories" view Click "Open" Paste the following link in the empty space in the "Git Repositories" view: https://github.com/exasol/remotelog-lua.git You should now have an entry that looks like this: Import the contained project How to get coding support and what each function does You now have a Lua project with implementation, tests and documentation. Next, please open the main implementation file of this project: src/remotelog.lua The first thing you will notice is that you get syntax highlighting for the Lua code and comments and LuaDoc. Of course LDT also brings code completion. Those two things are the minimum feature set you can expect from an IDE. But that's not all. LDT also recognizes LuaDoc comments and displays them nicely. Hover your mouse over the function M.init and you will see, what I mean. Now, scroll all the way down put your cursor on the line reading: start_high_resolution_timer() Click F3 and LDT jumps to the definition of this function. Note that due to the dynamic nature of Lua this works only in simple cases where the LDT can trace back a function reference. If you store the reference in a table for example, LDT does not realize this. Still handy in many situations though. Another useful feature is the "Rename variable" refactoring. Put the cursor on a variable or function and press [ALT] + [SHIFT] +[R]. The name gets selected and if you change it, all related instances get changed too. This is probably the most often used refactoring of all and it is good to have it. Other refactorings like extract method like you know them from Java for example are unfortunately missing. They are on the other hand terribly hard to safely implement on a dynamic language like Lua, so this does not come as a surprise. Another important aspect LDT offers is defining source directories in a project. They are automatically added to the Lua path, enabling you to execute unit tests directly from within Eclipse. Right-click on the Project " remotelog " in the Script Explorer and select "Properties". In the properties dialect find the section Lua/Build path. As you can see I marked the directories "src" and "test" as Lua Sources. While you are here you can also checkout the other project-specific Lua settings LDT offers: Formatter, Grammar and Task Tags. The Formatter is simple, but quite useful if you want to achieve uniformly looking Lua code in a team. In the grammar section you can define which Lua version your code targets. As in other programming languages you can define task tags which you can use as reminders in your comments. They are then displayed in Eclipse's task list. While they are sometimes handy, remember not to check them into version control when you publish your code. Using change tickets is the more professional variant because it keeps your code base clean and allows you to track task progress. One of the most useful functions is that you can run Lua scripts directly from the IDE. Unit testing with LDT This comes in particularly handy in case of unit testing. Open the file " test/test_remotelog.lua ". This file contains a set of unit tests written in LuaUnit. At the end of this file you see a typical LuaUnit idiom: os.exit(luaunit.LuaUnit.run()) This means the file is executable and when you run it, you run the contained unit tests. Hit [CTRL] + [F11] and the unit tests are executed. If you only use [F11] the tests are started in the debugger. To run all unit tests in a project it is customary in the Lua world to provide a script called " run_unit_tests.lua " in the main project directory. You can execute that one too. If you prefer a graphical view of the test results, you can modify the run configuration for " run_unit_tests.lua " to get the test results in a JUnit-compatible XML file. Run → Run Configurations. Find the run configuration for the script " run_unit_tests.lua " Check the "Arguments" tab and notice the following Script arguments -o junit -n ${project_loc}/target/unit_test_report.xml This tells LuaUnit to additionally output the test results in JUnit XML format. And since Eclipse knows that format, you can view it in the JUnit view. Right-click on the generated test report file in the "Script Explorer" and open it with the JUnit view. Testing convenience with MoreUnit MoreUnit is maybe the most useful little extension that I ever stumbled over on the Eclipse Marketplace. With MoreUnit you can switch quickly between implementation and corresponding unit tests using keyboard shortcuts. This is an insanely handy little trick especially if you prefer Test Driven Development (TDD). Let's first install MoreUnit. Help → Eclipse Marketplace... In dialog "Eclipse Marketplace" Search for "MoreUnit" Click "Install" Select all sub-features Click "Confirm" Accept the license Click "Finish" Next we are going to teach MoreUnit how where our test and implementation files reside and how they are named, so that the switch works. Note that for Java you usually can skip that step since there are well-established conventions for naming and location of tests and implementation. In Lua this requires a little extra setup. Window → Preferences Search for "MoreUnit" Click sub-section "User Languages" Enter source and test path, using placeholders for the project directory (here " ${srcProject}/src " and " ${srcProject}/test ") Enter the test file name pattern (in this example " test_${srcFile} " Click "Apply and Close" Now that MoreUnit is configured, open the file " remotelog.lua " in the editor. Press [CTRL] + [J] to jump to the corresponding test file. This works in both directions. When you want to work in TDD, your workflow becomes: ❌ Red Create a test file Create a test Press [CTRL] + [F11] to run it and verify that it fails (which it should since you did not touch the implementation yet!) Press [CTRL] + [J] to jump to the implementation Implement the new functionality ✔️ Green Press [CTRL] +[J] to jump back to the test The test should now succeed 🌟 Clean Press [CTRL] +[J] to jump to the implementation Refactor until you are satisfied with the code quality Press [CTRL] +[J] to jump back to the unit test Rerun the test with [CTRL] +[F11]   Debugging I saved the best part for last. While alt the other features I introduced to you are already very useful, it's debugging where an IDE really sets itself apart from mere editors. Open the file " test/test_remotelog_console.lua ". Double-click the first line in the function test_fatal() to set a break point there. Press [F11] to run the file in the debugger. In the console you can now see the debugger connecting. When you do this the first time Eclipse will offer switching to the Debug perspective. That is a good idea, so confirm this and make it the default. Debugger v1.4.2 Debugger: Trying to connect to 127.0.0.1:10000 ... Debugger: Connection succeed. As expected, the test runs up to the break point. The "Debug" view now shows the stack trace. Additionally you get a variable view, breakpoint view, expressions and an interactive console. In the example below you see how you can inspect the variable hierarchy down from the test's self reference to the log and the VERSION . The breakpoints view contains exactly what you would expect. A list of breakpoint you can enable, disable, remove, import, export and so on. Expressions let you watch the program in realtime and allow you to do conversions. The interactive console is useful if you want to modify the state of the program while your are debugging it. Of course you also have all the stepping options that a regular debugger provides. "Step into", "Step over", "Step return" and so on. I recommend memorizing the keyboard shortcuts. That's more efficient than working with the mouse. Conclusion Lua is a compact but powerful and speedy programming language built right into Exasol to enable high-performance scripting. With the right IDE setup programming and debugging Lua becomes a real pleasure. In the next articles of our "Exasol loves Lua" series we will dive deeper into unit testing and mocking with Lua, remote logging from Lua Scripts and installing standard modules.
View full article
Top Contributors