[MicroStrategy][Oracle] Connect failed. Error type: Odbc error. Odbc operation attempted: SQLDriverConnect. Optional feature not implemented. Driver's SQLSetConnectAttr failed. Socket closed. Connection Dead.

Issue:

The 'Connection Dead' error sporadically occurs when MicroStrategy Intelligence Server uses the MicroStrategy ODBC driver for Oracle Wire Protocol to connect to metadata.

This error, indicative of a disconnection from the database, specifically arises on UNIX machines hosting an Oracle database. The low value of the maxuproc setting (maximum processes per user) on UNIX is identified as a potential cause.

Root Cause:

The 'Connection Dead' error signals disconnection from the database, often linked to a low maxuproc setting on the Oracle UNIX machine. This setting restricts the maximum number of processes per user. Competition for resources, especially when other processes (e.g., a Web Server) are contending, can quickly exhaust this limit.

Fix:

Check the maxuproc setting value using lsattr -El sys0. The default is usually 128.

Increase the maxuproc value to a higher number (e.g., 4096) using

chdev -l sys0 -a maxuproc=4096.

Note: The 'Connection Dead' error is generic, and this technical note addresses one potential cause. Another possibility is covered in KB17194.

Published: Apr 2, 2017 Last Updated: Jul 20, 2023 Related Versions: 10.7, 10.6, 10.5, 10.4, 10.3, 10.2, 10.1, 10.0, 9.5.1, 9.4.1

Keys: Connection Dead
LINK: https://community.microstrategy.com/s/article/KB20403-Connection-dead-error-message-occurs-with-the?language=en_US

[MicroStrategy][Oracle][ODBC driver for Oracle][Oracle] Error while trying to retrieve text for error ORA-01019" error message occurs

 Issue:

Users encounter the "[ODBC driver for Oracle][Oracle] Error while trying to retrieve text for error ORA-01019" message when opening the Warehouse Catalog in MicroStrategy Developer 9.4.1 on a new machine. The error indicates a database connection failure.

Root Cause: The error arises from the presence of multiple 32-bit client libraries (SQAORA32.dll) on the same machine. The application is unable to determine which library to use when communicating with the driver, leading to the ORA-01019 error.

Fix: Remove the additional 32-bit client library from the client machine. In the case of a 3-tier connection, ensure the extra library is also removed from the Intelligence Server machine. This resolves the conflict and allows for proper communication between MicroStrategy and the Oracle driver.

Note: The issue is independent of MicroStrategy and relates to the configuration of third-party client libraries.

Published: Mar 31, 2017 Last Updated: Apr 12, 2019 Related Versions: 9.5.1, 9.4.1

LINK: https://community.microstrategy.com/s/article/KB249428-quot-ODBC-driver-for-Oracle-Oracle-Error-while-trying?language=en_US

[MicroStrategy][Oracle] best practice on integration of Oracle in MicroStrategy BI platform

Best Practices to integrate Oracle in MicroStrategy (Version 19c with DDA Mode)

Construction Considerations for Project Database Tuning: When initiating a project using MicroStrategy with Oracle as the warehouse (Version 19c in DDA mode), careful construction steps are crucial for optimal performance.

1. 1. VLDB True Temporary Table vs. Derived Table:

   • The default Intermediate Table Type is True temporary table, generating multi-pass queries.
   • Derived table and Common table expression produce single-pass SQL queries.
   • Experiment with different Intermediate Table Type settings, especially for Derived table and Common table expression, to find the optimal configuration for your specific business scenarios.

2. 2. Review and Set Database Parameters:

   • Collaborate with the project DBA to review and set database parameters according to instance resource quotas.
   • Adjust parameters like SGA_MAX_SIZE and SGA_TARGET based on available memory for improved performance.
   • MicroStrategy's in-house model testing showcased a 30% performance improvement by modifying these parameters.

3. 3. Create Index and Optimize SQL Execution Plan:

   • For traditional databases, a well-optimized query execution plan is crucial for performance.
   • Collect database statistics to inform the query execution plan.
   • Use Oracle AWR reports or database monitoring to identify low-performance queries.
   • Analyze low-performance queries and intervene by adding indexes or SQL hints to enhance the execution plan.
   • Modify the SQL execution plan to leverage a better SQL profile.

4. Example of SQL Tuning Adviser:

   • Presented an SQL statement with a 54s execution time before tuning.
   • Executed the SQL Tuning Adviser, resulting in an improved execution time of 2.37s.
   • Demonstrated the acceptance of a recommended new SQL profile to enhance the execution plan.

Additional Recommendations for Performance Enhancement:

1. 1. Table Compression:

   • For large data sizes (>80M) and less transactional tables (e.g., history tables), consider compressing them to free up space on data blocks and reduce table scan size during queries.

2. 2. Table Partitioning:

   • For data models involving querying on parts of large tables, consider using table partitioning based on specific columns (e.g., fiscal year/month or product categories) to decrease table scan costs.

3. 3. Enable Result Cache:

   • Oracle result cache stores query results in memory for reuse.
   • Enable result cache through VLDB settings or the driver connection to improve performance for queries with fixed result returns.
4. 

This comprehensive guide provides practical steps and considerations for tuning performance in MicroStrategy using Oracle 19c in DDA mode.

Regularly refer to MicroStrategy's documentation for the latest updates and version-specific recommendations.

LINK: https://community.microstrategy.com/s/article/KB484932-Best-Practice-for-Performance-Tuning-using-Oracle?language=en_US

[MicroStrategy][Oracle] MicroStrategy Oracle ODBC Driver Error Message with “Driver does not support this function

 Issue:

When executing dossiers or reports in MicroStrategy using Oracle as a data warehouse with parameterized queries enabled, users encounter the error message:

"Error: QueryEngine encountered error: OdbcPreparedStatement::GetParameterDataTypes() failed. [IM001:0: on SQLHANDLE] [Microsoft][ODBC Driver Manager] Driver does not support this function."

This issue arises when attempting parameterized queries, and the feature is confirmed to be enabled.

Root Cause: The error is caused by the Oracle ODBC Driver encountering a limitation – it does not support the SQLDescribeParam function. This limitation becomes apparent when executing parameterized queries in MicroStrategy.

Fix: To address the issue, users need to check and add a specific parameter for Windows-created Oracle ODBC DSN (Data Source Name).

For Oracle Linux ODBC DSN and Oracle ODBC DSN-less connection strings, the parameter "EnableDescribeParam = 1" should be added.

This action ensures compatibility and resolves the "driver does not support this function" error.

Note: MicroStrategy's Technology team is currently reviewing the situation for potential enhancement in an upcoming MicroStrategy release. Always refer to MicroStrategy's documentation for the latest information and updates related to specific versions.

LINK: https://community.microstrategy.com/s/article/KB484759-MicroStrategy-Oracle-ODBC-Driver-Error-Message-with-Driver-does-not-support-this-function?language=en_US

[MicroStrategy][Oracle] Kerberos Authentication is not supported by this Oracle Server.

 Issue:

When attempting to connect Oracle in MicroStrategy using the provided JDBC driver and the user's password consists of only spaces, an error arises. Users creating an Oracle DB role on MicroStrategy web with a JDBC connection string, including spaces as a password, encounter the following error message: "[MicroStrategy][Oracle JDBC Driver]Kerberos Authentication is not supported by this Oracle Server." This occurs when Kerberos authentication is attempted due to the "auto" AuthenticationMethod connect option with no specified username/password.

Root Cause: The issue is rooted in the attempt to perform Kerberos authentication on the Oracle Server, which does not support this method when the password contains only spaces. The error is triggered by the default authentication behavior when no explicit username/password is provided.

Fix: A workaround is available to enclose the space password with curly braces {} in the JDBC connection string. This modification allows for successful connection to Oracle in MicroStrategy even when the password consists solely of spaces.

Note: This Knowledge Base article is relevant to MicroStrategy versions 2019 and later. Always refer to MicroStrategy's documentation for the latest information and updates related to specific versions.

Kerberos Authentication is not supported by this Oracle Server. The driver attempted Kerberos authentication because the AuthenticationMethod connect option was "auto" and no username/password was specified

LINK: https://community.microstrategy.com/s/article/KB484361-Connect-Using-Oracle-JDBC-Driver-When-the-Password-Consists-of-Only-Spaces?language=en_US

​[MicroStrategy][MySQL]Can’t connect to local MySQL server through socket /tmp/mysql.sock

 Issue:

The error "Can’t connect to local MySQL server through socket /tmp/mysql.sock" arises during the configuration of MicroStrategy Intelligence Server Universal 9.4.x on Red Hat Enterprise Linux when accessing a locally installed MySQL instance. This occurs when using the MicroStrategy Configuration Wizard to configure metadata access, resulting in a connection failure.

Root Cause: The issue stems from a misconfiguration in MySQL. As the MySQL instance resides on the same machine as the MicroStrategy Intelligence Server, the server uses a socket connector rather than a TCP/IP connector to access the database. The error is triggered because the socket file is configured to be written in a directory where the MySQL server lacks write permissions.

Fix: To resolve the problem, contact the systems administrator to grant the necessary permissions for MySQL to write to the specified directory. This action ensures that the MicroStrategy Intelligence Server can establish a successful connection to the local MySQL server.

Note: This Knowledge Base article is applicable to MicroStrategy Intelligence Server Universal 9.4.x and serves as a guide for addressing the specific connection issue. Always consult MicroStrategy's documentation for the most up-to-date information and solutions.

LINK:

https://community.microstrategy.com/s/article/KB211246-The-error-Can-t-connect-to-local-MySQL-server-through-socket-tmp-mysql-sock-occurs-when-configuring-MicroStrategy-Intelligence-Server-Universal-9-4-x-instal?language=en_US

slow in containerized environment, timeout for TLS connectivity

ISSUES: 

For TLS connectivity, it is slow in containerized environment that might be blocked on random number generation.

eg: in FIPS env, you must enable TLS for all JDBC connectivity, the JDBC driver may take quite a long time in establishing the JDBC connection to the DB server.