17 Set Up Data Sources on Linux

You can learn how to set up data sources for use with Oracle Analytics Server when the Oracle BI Server is running on Linux.

Most repository development is performed on Windows, because the Model Administration Tool runs only on Windows. When you move to a production system, however, you can choose to run the Oracle BI Server on a Linux platform.

This chapter contains the following topics:

Settings for Data Source Connections Using Native Gateways

Learn about the environment variable settings that you must configure for Oracle Database using native gateways.

For Oracle Database:

  • The Oracle BI Server uses the Oracle Call Interface (OCI) to connect to the database. OCI is installed by default with Oracle Analytics Server. You must use the bundled version to connect.

  • In the tnsnames.ora file, the Oracle Database alias, the defined entry name, must match the Data Source Name used in the repository connection pools of all physical Oracle databases.

    When connecting to an Oracle Database data source, you can include the entire connect string, or you can use the net service name defined in the tnsnames.ora file. If you choose to enter only the net service name, you must set up a tnsnames.ora file in the following location within the environment, so that the Oracle Analytics Server can locate the entry:

    BI_DOMAIN/bidata/components/core/serviceinstances/ssi/oracledb

  • Edit the obis.properties file to set environment variables for the database client.

Sample obis.properties Entries for Oracle Database

This example shows sample entries in obis.properties for Oracle Database on various platforms.

The shell script excerpts shown are examples only and aren't recommendations for particular software platforms. See Configure Data Source Connections Using Native Gateways. 

###############################################################
# Linux: Oracle BI 32 bit mode
################################################################
#set +u

# Oracle Parameters
#---------------------------
# Make sure that Oracle DB 32 bit Client is installed
#ORACLE_HOME=/export/home/oracle/12c
#export ORACLE_HOME
#TNS_ADMIN=$ORACLE_HOME/network/admin
#export TNS_ADMIN
#PATH=$ORACLE_HOME/bin:/opt/bin:$PATH
#export PATH
#LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
#export LD_LIBRARY_PATH

# If you have Linux 64 bit Platform, and would like to run Oracle BI 32 bit
# then you must install Oracle DB 64 bit client, and this client comes with 
# 32 bit libraries under $ORACLE_HOME/lib32. The LD_LIBRARY_PATH in this case
# shall be like this:
#LD_LIBRARY_PATH=$ORACLE_HOME/lib32:$LD_LIBRARY_PATH
#export LD_LIBRARY_PATH
 
# Oracle Parameters
#---------------------------
# Make sure to install Oracle DB 64 bit Client
#ORACLE_HOME=/export/home/oracle/12c
#export ORACLE_HOME
#TNS_ADMIN=$ORACLE_HOME/network/admin
#export TNS_ADMIN
#PATH=$ORACLE_HOME/bin:/opt/bin:$PATH
#export PATH
#LD_LIBRARY_PATH_64=$ORACLE_HOME/lib:$LD_LIBRARY_PATH_64:/opt/j2se/jre/lib/sparc
#export LD_LIBRARY_PATH_64
#---------------------------
 
# Oracle Parameters
#---------------------------
#ORACLE_HOME=/export/home/oracle12c
#export ORACLE_HOME
#TNS_ADMIN=$ORACLE_HOME/network/admin
#export TNS_ADMIN
#PATH=$ORACLE_HOME/bin:/opt/bin:$PATH
#export PATH
#SHLIB_PATH=$ORACLE_HOME/lib:$SHLIB_PATH:/opt/j2se/jre/lib/hp700
#export SHLIB_PATH
#---------------------------

###############################################################
# AIX: Oracle BI 64 bit mode
###############################################################
#set +u
 
# Oracle Parameters
#---------------------------
#ORACLE_HOME=/export/home/oracle/12c
#export ORACLE_HOME
#TNS_ADMIN=$ORACLE_HOME/network/admin
#export TNS_ADMIN
#PATH=$ORACLE_HOME/bin:/opt/bin:$PATH
#export PATH
#LIBPATH=$ORACLE_HOME/lib:$LIBPATH:/opt/j2se/jre/lib/sparc
#export LIBPATH
#---------------------------

Configure Data Source Connections Using Native Gateways

You can connect to Oracle Database using native gateways.

  1. Open the obis.properties file located at:

    BI_DOMAIN/config/fmwconfig/bienv/obis

  2. Include the appropriate environment variable settings for the database client of your choice. Ensure that you point to the appropriate libraries, depending on whether you're using a 32-bit or 64-bit database.
  3. Save and close the file.
  4. Restart OBIS1.

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:

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