Using DataDirect Connect ODBC Drivers on Linux and UNIX

Oracle Business Intelligence provides DataDirect Connect ODBC drivers and driver managers for Linux and UNIX operating systems for connectivity to Microsoft SQL Server, Sybase ASE, Informix, Hive, and Impala databases.

After Oracle Business Intelligence is installed, the DataDirect Connect ODBC drivers are installed in ORACLE_HOME/bi/common/ODBC/Merant/7.1.5/lib.

You do not need to set the ODBCINI environment variable to set up the DataDirect Connect ODBC drivers. This variable is set automatically during installation.

Refer to System Requirements and Certification for information about supported operating systems, databases, and driver versions for the DataDirect Connect ODBC drivers.

Note:

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.

Configuring Oracle Business Intelligence to Use DataDirect

When you install Oracle BI EE, the required DataDirect 7.1.5 drivers are installed and automatically configured.

You can define the default settings in 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:

• Configuring the DataDirect Connect ODBC Driver for Microsoft SQL Server Database

• Configuring the DataDirect Connect ODBC Driver for MySQL Database

• Configuring the DataDirect Connect ODBC Driver for Sybase ASE Database

• Configuring the DataDirect Connect ODBC Driver for Informix Database

• Configuring the DataDirect Connect ODBC Driver for Cloudera Impala Database

• Configuring the DataDirect Connect ODBC Driver for Apache Hive Database

Additional DataDirect Configuration for Oracle Essbase

The DataDirect configuration requires you to modify the essbase.cfg file, so that Essbase connects to the DataDirect 7.1.5 drivers.

To modify the essbase.cfg file:

1. Open essbase.cfg for editing. You can find essbase.cfg at:

   BI_DOMAIN/config/fmwconfig/biconfig/essbase

2. Locate the BPM_ORACLE_DriverDescriptor entry and change the value to "DataDirect 7.1.5 Oracle Wire Protocol".

3. Use Fusion Middleware Control to restart Essbase.

Configuring the DataDirect Connect ODBC Driver for Microsoft SQL Server Database

The name of the DataDirect ODBC driver file to connect to a Microsoft SQL Server database is ARsql27.so.

See System Requirements and Certification for supported versions of Microsoft SQL Server.

To configure the DataDirect Connect ODBC Driver to connect to Microsoft SQL Server:

1. Open the obis.properties file. You can find this file at:

   BI_DOMAIN/config/fmwconfig/bienv/obis

2. Locate the LD_LIBRARY_PATH variable. Note the following:

   • For Solaris, Linux, and HP-UX, 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=$ORACLE_HOME/bi/bifoundation/server/bin,
   $ORACLE_HOME/bi/bifoundation/web/bin,
   $ORACLE_HOME/clients/epm/Essbase/EssbaseRTC/bin,
   $ORACLE_HOME/bi/bifoundation/odbc/lib,
   $ORACLE_INSTANCE,
   $ORACLE_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=$ORACLE_HOME/bi/common/ODBC/Merant/7.1.5/lib,
   $ORACLE_HOME/bi/bifoundation/server/bin,
   $ORACLE_HOME/bi/bifoundation/web/bin,
   $ORACLE_HOME/bi/clients/epm/Essbase/EssbaseRTC/bin,
   $ORACLE_HOME/bi/bifoundation/odbc/lib,
   $ORACLE_INSTANCE,
   $ORACLE_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, ensuring that the ODBC connection name is identical to the data source name specified in the connection pool defined in the repository. Ensure that you set the Driver parameter to the file name and location of the DataDirect Connect driver for Microsoft SQL Server. In the following example, the Driver parameter is set to the DataDirect Connect driver, and the data source name is SQLSERVER_DB.

   [SQLSERVER_DB]
   Driver=/ORACLE_HOME/bi/common/ODBC/Merant/7.1.5/lib/ARsqls27.so
   Description=DataDirect 7.1 SQL Server Wire Protocol
   Address=111.111.111.111,1433
   AlternateServers=
   AnsiNPW=Yes
   ConnectionRetryCount=0
   ConnectionRetryDelay=3
   Database=dbschema_name
   LoadBalancing=0
   LogonID=
   Password=
   QuoteID=No
   ReportCodePageConversionErrors=0
   

8. Save and close the odbc.ini file.
9. Restart OBIS1.

Configuring the DataDirect Connect ODBC Driver for MySQL Database

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

The name of the DataDirect ODBC driver file to connect to a MySQL database is ARmysql27.so. See System Requirements and Certification for information about supported versions of MySQL.

To configure the DataDirect Connect ODBC Driver to connect to MySQL Database:

1. Open the obis.properties file. You can find this file at:

   BI_DOMAIN/config/fmwconfig/bienv/obis

2. Locate the LD_LIBRARY_PATH variable. Use the following:

   • For Solaris, Linux, and HP-UX, 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=$ORACLE_HOME/bi/bifoundation/server/bin,
   $ORACLE_HOME/bi/bifoundation/web/bin,
   $ORACLE_HOME/clients/epm/Essbase/EssbaseRTC/bin,
   $ORACLE_HOME/bi/bifoundation/odbc/lib,
   $ORACLE_INSTANCE,
   $ORACLE_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=$ORACLE_HOME/bi/common/ODBC/Merant/7.1.5/lib,
   $ORACLE_HOME/bi/bifoundation/server/bin,
   $ORACLE_HOME/bi/bifoundation/web/bin,
   $ORACLE_HOME/clients/epm/Essbase/EssbaseRTC/bin,
   $ORACLE_HOME/bi/bifoundation/odbc/lib,
   $ORACLE_INSTANCE$:$ORACLE_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, ensuring that the ODBC connection name is identical to the data source name specified in the connection pool defined in the repository. Ensure that you set the Driver parameter to the file name and location of the DataDirect Connect driver for MySQL Database. For NetworkAddress, provide 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_DB]
   Driver=/ORACLE_HOME/bi/common/ODBC/Merant/7.1.5/lib/ARmysql27.so
   Description=DataDirect 7.1.5 MySQL Wire Protocol
   ApplicationUsingThreads=1
   ConnectionRetryCount=0
   ConnectionRetryDelay=3
   Database=default
   DefaultLongDataBuffLen=1024
   EnableDescribeParam=0
   HostName=localhost
   InteractiveClient=0
   LoadBalancing=0
   LogonID=my_id
   Password=my_password
   PortNumber=1526
   ReportCodepageConversionErrors=0
   TreatBinaryAsChar=0
   

8. Save and close the odbc.ini file.
9. Restart OBIS1.

Configuring 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.

See System Requirements and Certification for information about supported versions of Sybase ASE.

To configure the DataDirect Connect ODBC Driver to connect to Sybase ASE Database:

1. Open the obis.properties file. You can find this file at:

   BI_DOMAIN/config/fmwconfig/bienv/obis

2. Locate the LD_LIBRARY_PATH variable. Note the following:

   • For Solaris, Linux, and HP-UX, 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=$ORACLE_HOME/bi/bifoundation/server/bin,
   $ORACLE_HOME/bi/bifoundation/web/bin, 
   $ORACLE_HOME/clients/epm/Essbase/EssbaseRTC/bin,
   $ORACLE_HOME/bi/bifoundation/odbc/lib,
   $ORACLE_INSTANCE$:$ORACLE_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=$ORACLE_HOME/bi/common/ODBC/Merant/7.1.5/lib,
   $ORACLE_HOME/bi/bifoundation/server/bin,
   $ORACLE_HOME/bi/bifoundation/web/bin,
   $ORACLE_HOME/clients/epm/Essbase/EssbaseRTC/bin,
   $ORACLE_HOME/bi/bifoundation/odbc/lib,$ORACLE_INSTANCE$:$ORACLE_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, ensuring that the ODBC connection name is identical to the data source name specified in the connection pool defined in the repository. Ensure that you set the Driver parameter to the file name and location of the DataDirect Connect driver for Sybase ASE Database. For NetworkAddress, provide 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 SybaseASE_DB.

   [SybaseASE_DB]
   Driver=/ORACLE_HOME/bi/common/ODBC/Merant/7.1.5/lib/ARase27.so
   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.
9. Restart OBIS1.

Configuring the DataDirect Connect ODBC Driver for Informix Database

The name of the DataDirect ODBC driver file to connect to an Informix database is ARifcl27.so.

See System Requirements and Certification for information about supported versions of Informix.

To configure the DataDirect Connect ODBC Driver to connect to Informix:

1. Open the obis.properties file. You can find this file at:

   BI_DOMAIN/config/fmwconfig/bienv/obis

2. Locate the LD_LIBRARY_PATH variable. Note the following:

   • For Solaris, Linux, and HP-UX, 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=$ORACLE_HOME/bi/bifoundation/server/bin,
   $ORACLE_HOME/bi/bifoundation/web/bin,
   $ORACLE_HOME/clients/epm/Essbase/EssbaseRTC/bin,
   $ORACLE_HOME/bi/bifoundation/odbc/lib,
   $ORACLE_INSTANCE$:$ORACLE_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=$ORACLE_HOME/bi/common/ODBC/Merant/7.1.5/lib,
   $ORACLE_HOME/bi/bifoundation/server/bin,
   $ORACLE_HOME/bi/bifoundation/web/bin,
   $ORACLE_HOME/clients/epm/Essbase/EssbaseRTC/bin,
   $ORACLE_HOME/bi/bifoundation/odbc/lib,
   $ORACLE_INSTANCE$:$ORACLE_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, ensuring that the ODBC connection name is identical to the data source name specified in the connection pool defined in the repository. Ensure that you 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=/ORACLE_HOME/bi/common/ODBC/Merant/7.1.5/lib/ARifcl27.so
   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.
9. Restart OBIS1.

Configuring the DataDirect Connect ODBC Driver for Cloudera Impala Database

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

See System Requirements and Certification for information about supported versions of Cloudera Impala.

To configure the DataDirect Connect ODBC Driver to connect to Cloudera Impala:

1. Open the obis.properties file. You can find this file at:
   BI_DOMAIN/config/fmwconfig/bienv/obis
2. Locate the LD_LIBRARY_PATH variable.
   The library path variable is:

   • For Solaris, Linux, and HP-UX : LD_LIBRARY_PATH.

   • For AIX: LIBPATH

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

   LD_LIBRARY_PATH=$ORACLE_HOME/bifoundation/server/bin,
   $ORACLE_HOME/bi/bifoundation/web/bin,
   $ORACLE_HOME/clients/epm/Essbase/EssbaseRTC/bin,
   $ORACLE_HOME/bi/bifoundation/odbc/lib,
   $ORACLE_INSTANCE,
   $ORACLE_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=$ORACLE_HOME/bi/common/ODBC/Merant/7.1.5/lib,
   $ORACLE_HOME/bi/bifoundation/server/bin,
   $ORACLE_HOME/bi/bifoundation/web/bin,
   $ORACLE_HOME/clients/epm/Essbase/EssbaseRTC/bin,
   $ORACLE_HOME/bi/bifoundation/odbc/lib,
   $ORACLE_INSTANCE,
   $ORACLE_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, verify the following: Ensure that you set You must 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=/ORACLE_HOME/bi/common/ODBC/Merant/7.1.5/lib/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 are using Impala 1.3.x, you must configure include a LIMIT Clause section. If you are using Impala 1.4 (CDH 5.1) or later, then you do not need to complete the additional steps. See Configuring Impala 1.3.x to Include a LIMIT Clause.
9. Restart OBIS1.

Go to the Administration Tool to import the Cloudera Impala metadata. See Importing Cloudera Impala Metadata Using the Windows ODBC Driver for more information.

Configuring 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 Modifying 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 do not 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.

To use the recommended method to modify the Impala daemon's default query options:

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.

Modifying the Impala DefaultOrderByLimit Alternate Methods

Use the first modifying the DefaultOrderByLimit option if your Impala environment is not managed by Cloudera Manager. Use the second DefaultOrderByLimit option if you do not 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 have the required permissions, you should use the recommended method for updating the DefaultOrderByLimit. See Configuring Implaa 1.3x to Include a Limit Clause.

If your Impala environment is not managed by Cloudera Manager, use the Impala product documentation to help you modify the LIMIT clause. To complete this task, see “Configuring Impala Startup Options Through the Command Line”, located 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 using the instructions in “Configuring Impala Startup Options Through the Command Line,” 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 do not 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.

To modify the DefaultOrderByLimit parameter in the odbc.ini Impala DSN entry:

1. Open the odbc.ini file from the following location:

   BI_DOMAIN/config/fmwconfig/bienv/core

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.