Use DataDirect Connect ODBC Drivers on Linux

Oracle Analytics Server provides DataDirect Connect ODBC drivers and driver managers for Linux operating systems for connectivity to MySQL, Sybase ASE, Informix, Hive, and Impala databases.

Amazon Redshift data sources are also supported. You need to use the Amazon Redshift ODBC driver available from Amazon Web Services. Configure the Amazon Redshift data source using the steps documented for other data sources.

The DataDirect drivers are installed in the Oracle Analytics Server installation process. You can find the DataDirect Connect ODBC drivers in the MW_HOME/bi/modules/oracle.bi.datadirect.odbc/7.1.6/lib directory.

You don't need to set the ODBCINI environment variable to set up the DataDirect Connect ODBC drivers. This variable is set automatically during installation.

Configure Oracle Analytics Server to Use DataDirect

When you install Oracle Analytics Server, the required DataDirect 7.1.6 drivers are installed and automatically configured.

You can define the default settings in the obis.properties and odbc.ini files.

You need to modify your existing database configurations to use the DataDirect drivers. For information about modifying your existing database configuration, see the following procedures:

• Configure the DataDirect Connect ODBC Driver for DB2 Database

• Configure the DataDirect Connect ODBC Driver for MySQL Database

• Configure the DataDirect Connect ODBC Driver for Sybase ASE Database

• Configure the DataDirect Connect ODBC Driver for Informix Database

• Configure the DataDirect Connect ODBC Driver for Cloudera Impala Database

• Configure the DataDirect Connect ODBC Driver for Apache Hive Database

Additional DataDirect Configuration for Oracle Essbase

Modify the DataDirect configuration to connect to Essbase data sources.

The name of the DataDirect 7.1.6 driver file to use with Essbase is essbase.cfg.

1. Open essbase.cfg for editing from the following location:

   BI_DOMAIN/config/fmwconfig/biconfig/essbase

2. In the configuration file, locate the BPM_ORACLE_DriverDescriptor element and change the value to "DataDirect 7.1.6 Oracle Wire Protocol".
3. Use Fusion Middleware Control to restart Essbase.

Configure the DataDirect Connect ODBC Driver for DB2 Database

Use these steps to connect to a DB2 database.

The name of the DataDirect ODBC driver file to connect to a MySQL database is ARdb227.so.

1. Open the obis.properties file located in:

   BI_DOMAIN/config/fmwconfig/bienv/OBIS

2. Locate the LD_LIBRARY_PATH variable. Use the following:

   • For Linux, the library path variable is LD_LIBRARY_PATH.

   • For AIX, the library path variable is LIBPATH.

3. If necessary, update the LD_LIBRARY_PATH variable to include the DataDirect driver path.
4. In obis.properties, locate the PATH variable and if necessary, include the DataDirect driver path.
5. Save and close the file.
6. Open the odbc.ini file. You can find this file at:

   BI_DOMAIN/config/fmwconfig/bienv/core

7. Create an entry for the database:

   Use the same ODBC connection name to the data source name specified in the connection pool defined in the repository.

   Set the Driver parameter to the file name and location of the DataDirect Connect driver for DB2 Database. For the NetworkAddress use the IP address or fully qualified host name and the port number.

   In the following example, the Driver parameter is set to the DataDirect Connect driver, and the data source name is ABCDEF.

   [ABCDEF]
   Driver=/u01/app/product/12.2.1.4.0/obi_1/bi/modules/oracle.bi.datadirect.odbc/7.1.6/lib/ARdb227.so
   Description=Oracle 7.1 DB2 Wire Protocol
   AccountingInfo=
   AddStringToCreateTable=
   AlternateID=
   AlternateServers=
   ApplicationName=
   ApplicationUsingThreads=1
   AuthenticationMethod=0
   BulkBinaryThreshold=32
   BulkCharacterThreshold=-1
   BulkLoadBatchSize=1024
   BulkLoadFieldDelimiter=
   BulkLoadRecordDelimiter=
   CatalogSchema=
   CharsetFor65535=0
   ClientHostName=
   ClientUser=
   #Collection applies to z/OS and iSeries only
   Collection=
   ConcurrentAccessResolution=0
   ConnectionReset=0
   ConnectionRetryCount=0
   ConnectionRetryDelay=3
   CurrentFuncPath=
   #Database applies to DB2 UDB only
   Database=ABCDEF 
   DefaultIsolationLevel=1
   DynamicSections=1000
   EnableBulkLoad=0
   EncryptionMethod=0
   FailoverGranularity=0
   FailoverMode=0
   FailoverPreconnect=0
   GrantAuthid=PUBLIC
   GrantExecute=1
   GSSClient=native
   HostNameInCertificate=
   IpAddress=10.116.26.27
   KeyPassword=
   KeyStore=
   KeyStorePassword=
   LoadBalanceTimeout=0
   LoadBalancing=0
   #Location applies to z/OS and iSeries only
   Location=
   LogonID=
   MaxPoolSize=100
   MinPoolSize=0
   Password=
   PackageCollection=NULLID
   PackageNamePrefix=DD
   PackageOwner=
   Pooling=0
   ProgramID=
   QueryTimeout=0
   ReportCodePageConversionErrors=0
   TcpPort=50002
   TrustStore=
   TrustStorePassword=
   UseCurrentSchema=0
   ValidateServerCertificate=1
   WithHold=1
   XMLDescribeType=-10

8. Save and close the odbc.ini file.

Configure the DataDirect Connect ODBC Driver for MySQL Database

Use these steps to connect to a MySQL database.

The name of the DataDirect ODBC driver file to connect to a MySQL database is ARmysql27.so.

1. Open the obis.properties file located in:

   BI_DOMAIN/config/fmwconfig/bienv/OBIS

2. Locate the LD_LIBRARY_PATH variable. Use the following:

   • For Linux, the library path variable is LD_LIBRARY_PATH.

   • For AIX, the library path variable is LIBPATH.

   For example, to set the library path variable for the driver on Linux:

   LD_LIBRARY_PATH=$MW_HOME/bi/bifoundation/server/bin,
   $MW_HOME/bi/bifoundation/web/bin,
   $MW_HOME/clients/epm/Essbase/EssbaseRTC/bin,
   $MW_HOME/bi/bifoundation/odbc/lib,
   $ORACLE_INSTANCE,
   $MW_HOME/lib
   

3. If necessary, update the LD_LIBRARY_PATH variable to include the DataDirect driver path. For example, to update the variable for the driver on Linux:

   LD_LIBRARY_PATH=$MW_HOME/bi/modules/oracle.bi.datadirect.odbc/7.1.6/lib,
   $MW_HOME/bi/bifoundation/server/bin,
   $MW_HOME/bi/bifoundation/web/bin,
   $MW_HOME/clients/epm/Essbase/EssbaseRTC/bin,
   $MW_HOME/bi/bifoundation/odbc/lib,
   $ORACLE_INSTANCE
   $ORACLE_HOME/lib:$MW_HOME/lib
   

4. In obis.properties, locate the PATH variable and if necessary, include the DataDirect driver path.
5. Save and close the file.
6. Open the odbc.ini file. You can find this file at:

   BI_DOMAIN/config/fmwconfig/bienv/core

7. Create an entry for the database:

   Use the same ODBC connection name to the data source name specified in the connection pool defined in the repository.

   Set the Driver parameter to the file name and location of the DataDirect Connect driver for MySQL Database. For the NetworkAddress use the IP address or fully qualified host name and the port number.

   In the following example, the Driver parameter is set to the DataDirect Connect driver, and the data source name is MySQL_DB.

   [MySQL Wire Protocol]
   Driver=/refresh/home/oracle/middleware/oracle_home/bi/modules/oracle.bi.datadirect.odbc/7.1.6/lib/ARmysql27.so
   Description=Oracle 7.1 MySQL Wire Protocol
   AlternateServers=
   ApplicationUsingThreads=1
   ConnectionReset=0
   ConnectionRetryCount=0
   ConnectionRetryDelay=3
   Database=<database_name>
   DefaultLongDataBuffLen=1024
   EnableDescribeParam=0
   EncryptionMethod=0
   FailoverGranularity=0
   FailoverMode=0
   FailoverPreconnect=0
   HostName=<MySQL_host>
   HostNameInCertificate=
   InteractiveClient=0
   LicenseNotice=You must purchase commercially licensed MySQL database software or a MySQL Enterprise subscription in order to use the DataDirect Connect for ODBC for MySQL Enterprise driver with MySQL software.
   KeyStore=
   KeyStorePassword=
   LoadBalanceTimeout=0
   LoadBalancing=0
   LogonID=
   LoginTimeout=15
   MaxPoolSize=100
   MinPoolSize=0
   Password=
   Pooling=0
   PortNumber=<MySQL_server_port>
   QueryTimeout=0
   ReportCodepageConversionErrors=0
   TreatBinaryAsChar=0
   TrustStore=
   TrustStorePassword=
   ValidateServerCertificate=1

8. Save and close the odbc.ini file.

Configure the DataDirect Connect ODBC Driver for Sybase ASE Database

The name of the DataDirect ODBC driver file to connect to a Sybase ASE database is ARase27.so.

1. Open the obis.properties file located in:

   BI_DOMAIN/config/fmwconfig/bienv/OBIS

2. Locate the LD_LIBRARY_PATH variable use the following information:

   • For Linux, the library path variable is LD_LIBRARY_PATH.

   • For AIX, the library path variable is LIBPATH.

   For example, to set the library path variable for the driver on Linux:

   LD_LIBRARY_PATH=$MW_HOME/bi/bifoundation/server/bin,
   $MW_HOME/bi/bifoundation/web/bin, 
   $MW_HOME/clients/epm/Essbase/EssbaseRTC/bin,
   $MW_HOME/bi/bifoundation/odbc/lib,
   $ORACLE_INSTANCE$:$MW_HOME/lib
   

3. If necessary, update the LD_LIBRARY_PATH variable to include the DataDirect driver path.

   To update the variable for the driver on Linux, review the following example:

   LD_LIBRARY_PATH=$MW_HOME/bi/modules/oracle.bi.datadirect.odbc/7.1.6/lib,
   $MW_HOME/bi/bifoundation/server/bin,
   $MW_HOME/bi/bifoundation/web/bin,
   $MW_HOME/clients/epm/Essbase/EssbaseRTC/bin,
   $MW_HOME/bi/bifoundation/odbc/lib,$ORACLE_INSTANCE$:$MW/lib
   

4. Locate the PATH variable and if necessary, include the DataDirect driver path.
5. Save and close the file.
6. Open the odbc.ini file located in:

   BI_DOMAIN/config/fmwconfig/bienv/core

7. Create an entry for the database.

   Use the same ODBC connection name to the data source name specified in the connection pool defined in the repository.

   Set the Driver parameter to the file name and location of the DataDirect Connect driver for Sybase ASE Database. For the NetworkAddress provide the IP address or fully qualified host name and the port number.

   The following example shows the Driver parameter set to the DataDirect connect driver, and SybaseASE_DB as the data source name.

   [SybaseASE_DB]
   Driver=/scratch/aime1/work/mw3108/bi/modules/oracle.bi.datadirect.odbc/7.1.6
   Description=DataDirect 7.1 Sybase Wire Protocol
   AlternateServers=
   ApplicationName=
   ApplicationUsingThreads=1
   ArraySize=50
   AuthenticationMethod=0
   Charset=
   ConnectionRetryCount=0
   ConnectionRetryDelay=3
   CursorCacheSize=1
   Database=Paint
   DefaultLongDataBuffLen=1024
   EnableDescribeParam=0
   EnableQuotedIdentifiers=0
   EncryptionMethod=0
   GSSClient=native
   HostNameInCertificate=
   InitializationString=
   Language=
   LoadBalancing=0
   LogonID=my_id
   NetworkAddress=111.111.111.111,5005
   OptimizePrepare=1
   PacketSize=0
   Password=
   RaiseErrorPositionBehavior=0
   ReportCodePageConversionErrors=0
   SelectMethod=0
   ServicePrincipalName=
   TruncateTimeTypeFractions=0
   TrustStore=
   TrustStorePassword=
   ValidateServerCertificate=1
   WorkStationID=
   

8. Save and close the odbc.ini file.

Configure the DataDirect Connect ODBC Driver for Informix Database

Use these steps to configure the DataDirect ODBC driver file to connect to an Informix database. The file to use is ARifcl27.so.

1. Open the obis.properties file located in:

   BI_DOMAIN/config/fmwconfig/bienv/OBIS

2. Locate the LD_LIBRARY_PATH variable, using the following information:

   • For Linux, the library path variable is LD_LIBRARY_PATH.

   • For AIX, the library path variable is LIBPATH.

   For example, to set the library path variable for the driver on Linux:

   LD_LIBRARY_PATH=$MW_HOME/bi/bifoundation/server/bin,
   $MW_HOME/bi/bifoundation/web/bin,
   $MW_HOME/clients/epm/Essbase/EssbaseRTC/bin,
   $MW_HOME/bi/bifoundation/odbc/lib,
   $ORACLE_INSTANCE$:$MW_HOME/lib
   

3. If necessary, update the LD_LIBRARY_PATH variable to include the DataDirect driver path.

   To update the variable for the driver on Linux, review the following example:

   LD_LIBRARY_PATH=$MW_HOME/bi/modules/oracle.bi.datadirect.odbc/7.1.6/lib
   $MW_HOME/bi/bifoundation/server/bin,
   $MW_HOME/bi/bifoundation/web/bin,
   $MW_HOME/clients/epm/Essbase/EssbaseRTC/bin,
   $MW_HOME/bi/bifoundation/odbc/lib,
   $MW_INSTANCE$:$MW_HOME/lib
   

4. In obis.properties, locate the PATH variable and if necessary, include the DataDirect driver path.
5. Save and close the file.
6. Open the odbc.ini file located in:

   BI_DOMAIN/config/fmwconfig/bienv/core

7. Create an entry for the database.

   You must use the identical ODBC connection name to the data source name specified in the connection pool defined in the repository.

   Set the Driver parameter to the file name and location of the DataDirect Connect driver for Informix. You must specify the HostName parameter, you can use the fully qualified host name or the IP address, and the PortNumber parameter.

   In the following example, the Driver parameter is set to the DataDirect Connect driver, and the data source name is Informix_DB.

   [Informix_DB]
   Driver=/scratch/aime1/work/mw3108/bi/modules/oracle.bi.datadirect.odbc/7.1.6
   Description=DataDirect Informix Wire Protocol
   AlternateServers=
   ApplicationUsingThreads=1
   CancelDetectInterval=0
   ConnectionRetryCount=0
   ConnectionRetryDelay=3
   Database=
   HostName=111.111.111.111
   LoadBalancing=0
   LogonID=informix
   Password=mypassword
   PortNumber=1526
   ReportCodePageConversionErrors=0
   ServerName=
   TrimBlankFromIndexName=1
   

8. Save and close the odbc.ini file.

Configure the DataDirect Connect ODBC Driver for Cloudera Impala Database

The DataDirect ODBC driver file name, to connect to a Cloudera Impala database is ARimpala27.so.

See Configure Impala 1.3.x to Include a LIMIT Clause.

1. Open the obis.properties file located at:
   BI_DOMAIN/config/fmwconfig/bienv/OBIS
2. Locate the LD_LIBRARY_PATH variable.
   The library path variable is:

   • For Linux : LD_LIBRARY_PATH.

   • For AIX: LIBPATH

   For example, to set the library path variable for the driver on Linux, use:

   LD_LIBRARY_PATH=$MW_HOME/bi/bifoundation/server/bin,
   $MW_HOME/bi/bifoundation/web/bin,
   $MW_HOME/clients/epm/Essbase/EssbaseRTC/bin,
   $MW_HOME/bi/bifoundation/odbc/lib,
   $ORACLE_INSTANCE,
   $MW_HOME/lib

3. If necessary, update the LD_LIBRARY_PATH variable to include the DataDirect driver path.
   For example, to update the variable for the driver on Linux:

   LD_LIBRARY_PATH=$MW_HOME/bi/modules/oracle.bi.datadirect.odbc/7.1.6/lib,
   $MW_HOME/bi/bifoundation/server/bin,
   $MW_HOME/bi/bifoundation/web/bin,
   $MW_HOME/clients/epm/Essbase/EssbaseRTC/bin,
   $MW_HOME/bi/bifoundation/odbc/lib,
   $ORACLE_INSTANCE,
   $MW_HOME/lib
   

4. In obis.properties, locate the PATH variable and, if necessary, include the DataDirect driver path.
5. Save and close the file.
6. Open the odbc.ini file located at:

   BI_DOMAIN/config/fmwconfig/bienv/core

7. Create an entry for the database and specify the HostName parameter.

   You can use the fully qualified host name or the IP address as the HostName parameter, and the PortNumber parameter.

   • The ODBC connection name is identical to the data source name specified in the connection pool defined in the repository.

   • The Driver parameter is set to the file path of the DataDirect Connect driver for Cloudera Impala.

   • The HostName parameter uses the fully qualified host name, or the IP address as the HostName parameter and the PortNumber parameter.

   The following example shows the Driver parameter set to the DataDirect Connect driver, and the Impala_DB data source name.

   [Impala_DB]
   Driver=/scratch/aime1/work/mw3108/bi/modules/oracle.bi.datadirect.odbc/7.1.6/ARimpala27.so
   Description=Oracle 7.1 Cloudera Impala Wire Protocol
   ArraySize=16384
   Database=default
   DefaultLongDataBuffLen=1024
   DefaultOrderByLimit=-1
   EnableDescribeParam=0
   HostName=localhost
   LoginTimeout=30
   MaxVarcharSize=2000
   PortNumber=21050
   RemoveColumnQualifiers=0
   StringDescribeType=12
   TransactionMode=0
   UseCurrentSchema=0
   WireProtocolVersion=2
   

8. Save and close the odbc.ini file.
   If you're using Impala 1.3.x, you must configure include a LIMIT clause section. If you're using Impala 1.4 (CDH 5.1) or later, then you can skip the additional steps.

Configure Impala 1.3.x to Include a LIMIT Clause

Impala 1.3.x requires that queries with an ORDER BY clause contain a LIMIT clause.

There are three methods to specify this clause in the configuration. Oracle recommends using the Modify the Impala daemon's default query options method. For the second and third methods, see Modify the Impala DefaultOrderByLimit Alternate Methods.

Specifying a default order by limit using any of the following methods returns a maximum of 2,000,000 rows for queries with an ORDER by clause.

If you specify the LIMIT clause using the Modify the Impala daemon's default query options method, and your queries include an ORDER BY clause, then Impala returns a maximum of 2,000,000 rows. If this limit is exceeded, then Impala throws an exception.

For queries over 2,000,000 rows, specify a higher default_order_by_limit value.

You can also specify the Default Order By Limit by using the client instead of the Impala server.

Use this method if you don’t have rights to modify the Impala daemon using the previous methods. If you use this method, then Impala silently truncates your value to 2,000,000 rows.

1. Go to the Cloudera Manager's home page and click the Impala service.
2. In the Impala service page, click Configuration, and the select View and Edit.
3. In the Configuration page, select Impala Daemon Default Group.
4. Locate Impala Daemon Query Options Advanced, also known as the default_query_options, and add the following entries:

   default_order_by_limit=2000000
   abort_on_default_limit_exceeded=true

5. Click Save Changes.
6. In the Cloudera Manager's home page, restart the Impala service.

Modify the Impala DefaultOrderByLimit Alternate Methods

Use the first DefaultOrderByLimit option if your Impala environment isn't managed by Cloudera Manager. Use the second DefaultOrderByLimit option if you don't have rights to modify the Impala daemon.

Modify the Impala Daemon's Default Query Options Without Cloudera Manager

If your environment is managed by Cloudera Manager and you've the required permissions, use DefaultOrderByLimit, the recommended method for updating the Impala daemon. See Configure Impala 1.3.x to Include a LIMIT Clause.

If your Impala environment isn't managed by Cloudera Manager, use the Impala product documentation to help you modify the LIMIT clause. See “Configuring Impala Startup Options Through the Command Line” in the CDH 5 Installation Guide.

If you specify the LIMIT clause using this method and your queries include an ORDER BY clause, then Impala returns a maximum of 2,000,000 rows. If this limit is exceeded, then Impala throws an exception.

• After completing the steps in the Configuring Impala Startup Options Through the Command Line task, add the following entry in IMPALA_SERVER_ARGS:

  -default_query_options 'default_order_by_limit=2000000;abort_on_default_limit_exceeded=true'
  

Modify the DefaultOrderByLimit Parameter in the odbc.ini Impala DSN Entry

Use this method if you don't have rights to modify the Impala daemon using the previous methods. If you use this method, then Impala silently truncates your value to 2,000,000 rows.

If you need your query to return more than 2,000,000 rows, then specify a higher DefaultOrderByLimit parameter value.

You can specify the Default Order By Limit using the client instead of the Impala server.

1. Open the odbc.ini file from the BI_DOMAIN/config/fmwconfig/bienv/core directory.
2. Locate the Impala_DB database entry, and then locate the DefaultOrderByLimit parameter.
3. Update the value to 2000000, for example, DefaultOrderByLimit=2000000.
4. Save and close the odbc.ini file.