This chapter describes how to set up data sources for use with Oracle Business Intelligence when the Oracle BI Server is running on Linux or UNIX.
Most repository development is performed on Windows, because the 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 or UNIX platform.
See "System Requirements and Certification" for information about supported Linux and UNIX platforms.
This chapter contains the following topics:
When the Oracle BI Server is running on Linux or UNIX, most data source connections are for query-only access. The Administration Tool is used for importing objects and is a Windows-only tool. Because of this, data source connections for import must be set up on Windows.
Note that some data source connections on Linux and UNIX do support write operations for special functions, like data source connections for write-back, usage tracking, and annotations for Oracle Scorecard and Strategy Management.
When the Oracle BI Server is running on Linux or UNIX and you need to update database object settings (such as the database type) or connection pool settings, you can copy the repository file to a Windows computer, make the changes using the Administration Tool on Windows, and then copy the repository file back to the Linux or UNIX computer.
There are three types of data source connections on Linux and UNIX platforms:
Native data source gateway connections, such as OCI for Oracle Database or DB2 CLI for IBM DB2
ODBC connections using the DataDirect Connect ODBC drivers that are bundled with Oracle Business Intelligence
Native ODBC connections using external drivers, such as for Teradata data sources
Note that you can have a single repository that contains both DataDirect Connect ODBC connections and native ODBC connections. In this situation, both the native ODBC drivers and DataDirect ODBC drivers need to be managed by the same DataDirect ODBC driver manager. For example, the Teradata ODBC drivers include their required ODBC driver managers. When the Teradata ODBC driver is used with Oracle BI EE, the driver must be managed by the DataDirect ODBC driver manager bundled with Oracle BI EE.
You can connect to both Oracle Database and DB2 using native gateways (OCI and DB2 CLI, respectively).
For Oracle Database, note the following:
The Oracle BI Server uses the Oracle Call Interface (OCI) to connect to the database. OCI is installed by default with Oracle BI Enterprise Edition. 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 Oracle Business Intelligence environment, so that the Oracle BI Server can locate the entry:
BI_DOMAIN/config/fmwconfig/bienv/core
Edit the obis.properties file to set environment variables for the database client.
For DB2, note the following:
Install the appropriate database client on the computer running the Oracle BI Server, then edit the obis.properties file to set environment variables for the database client.
For Windows, you can set environment variables for DB2 in the obis.properties file. For example, if configuring DB2 CLI, then you must modify obis.properties to include the DB2 executable path.
You need to create a catalog associated with each database so that the client connects to the database by catalog name. To create a catalog associated with each database, enter and run the following command:
db2 catalog tcpip node <DB2 database> remote <hostname> server <port number>; db2 catalog database <DB2 database> as <DB2 database> at node <DB2 database>; connect to <DB2 database> user db2admin using welcome1
To edit the obis.properties file to set environment variables for Oracle Database or DB2:
Open the obis.properties file. You can find this file at:
BI_DOMAIN/config/fmwconfig/bienv/obis
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 are using a 32-bit or 64-bit database.
DB2INSTANCE=db2user IBM_DB_LIB=/scratch/db2user/sqllib/lib IBM_DB_DIR=/scratch/db2user/sqllib LD_LIBRARY_PATH=/scratch/db2user/sqllib/lib64:/scratch/db2user/sqllib/lib32 PATH=$PATH:/scratch/db2user/sqllib/bin:/scratch/db2user/sqllib/adm:/scratch/db2user/sqllib/misc DB2_HOME=/scratch/db2user/sqllib IBM_DB_INCLUDE=/scratch/db2user/sqllib/include DB2LIB=/scratch/db2user/sqllib/lib
See Example 16-1 for sample values.
Save and close the file.
Restart OBIS1.
Example 16-1 Sample obis.properties Entries for Oracle Database and DB2 (32-Bit)
This example shows sample entries in obis.properties for Oracle Database and DB2 on various platforms.
############################################################### # 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/10g #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 # DB2 Parameters #--------------------------- #make sure the /DB2ISTANCE/sqllib/lib points to 32 lib file #. /DB2ISTANCE/sqllib/db2profile #--------------------------- ############################################################### # Solaris: Oracle BI 64 bit mode ############################################################### #set +u # Oracle Parameters #--------------------------- # Make sure to install Oracle DB 64 bit Client #ORACLE_HOME=/export/home/oracle/10g #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 #--------------------------- # DB2 Parameters #--------------------------- #make sure the /DB2ISTANCE/sqllib/lib points to 64 lib file #. /DB2ISTANCE/sqllib/db2profile #LD_LIBRARY_PATH_64=/DB2ISTANCE/sqllib/lib:$LD_LIBRARY_PATH_64 #export LD_LIBRARY_PATH_64 #--------------------------- ############################################################### # HPUX Itanium: Oracle BI 64 bit mode ############################################################### #set +u # Oracle Parameters #--------------------------- #ORACLE_HOME=/export/home/oracle/10g #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 #--------------------------- # DB2 Parameters #--------------------------- #make sure the /DB2ISTANCE/sqllib/lib points to 64 lib file #. /DB2ISTANCE/sqllib/db2profile #SHLIB_PATH=/DB2ISTANCE/sqllib/lib:$SHLIB_PATH #export SHLIB_PATH #--------------------------- ############################################################### # AIX: Oracle BI 64 bit mode ############################################################### #set +u # Oracle Parameters #--------------------------- #ORACLE_HOME=/export/home/oracle/10g #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 #--------------------------- # DB2 Parameters #--------------------------- #make sure the /DB2ISTANCE/sqllib/lib points to 64 lib file #. /DB2ISTANCE/sqllib/db2profile #---------------------------
Note that the shell script excerpts shown are examples only and are not recommendations for particular software platforms. See "System Requirements and Certification" for information about supported software platforms.
There are several reasons why you might have trouble connecting to an Oracle Database using OCI.
Check to ensure that the following conditions are true:
The computer running the Oracle BI Server must use Oracle Call Interface (OCI) to connect to the database.
If you choose not to use the entire connect string in the repository connection pool, you must ensure that a valid tnsnames.ora file is set up in the following location within the Oracle Business Intelligence environment, so that the Oracle BI Server can locate the entry:
BI_DOMAIN/config/fmwconfig/bienv/core
If you choose not to use the entire connect string in the repository connection pool, ensure that the net service name in the tnsnames.ora file matches the Data Source Name used in the connection pool.
For example, in the following example of a tnsnames.ora entry, the corresponding Oracle BI repository connection pool Data Source Name is ITQA2.
ITQA2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ITQALAB2)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = ITQALAB2.corp)
)
)
The following procedure shows how to check repository database and connection pool settings against the Oracle tnsnames.ora settings.
To check that the repository database and connection pool settings are correct:
Open the repository in the Administration Tool.
In the Physical layer, double-click the database you want to check to display the Database dialog.
On the General tab, in the Data source definition: Database field, ensure that the appropriate Oracle Database version is selected. Then, click OK.
Open the Connection Pool dialog for this data source. You might need to expand the database object in the Physical layer to see the connection pool object.
In the Connection Pool dialog, check that the following is true:
The Call interface field displays the appropriate value for the release of the Oracle Database you are using.
The Data source name field displays the Oracle Database net service name that you defined in the tnsnames.ora entry.
The User name and password fields contain the correct values.
Change the values if necessary, then click OK.
In the Oracle Business Intelligence environment, open the tnsnames.ora file located in the following directory:
BI_DOMAIN/config/fmwconfig/bienv/core
Check that a valid net service name exists with the following characteristics:
Matches the connection pool settings for the Data Source Name
Specifies the targeted Oracle physical database
This topic applies if both of the following are true:
You are using the Update Rowcount functionality in the Administration Tool in offline mode.
You are running a heterogeneous environment, such as the Oracle BI Server on UNIX, while remote administrators run the Administration Tool on Windows computers.
When using the Update Rowcount functionality in offline mode, the Administration Tool uses local data source connection definitions on the client computer, not the server data sources. Therefore, Oracle Database or DB2 clients must be configured on the Windows computer running the Administration Tool so that the following conditions are true:
Data sources point to the same database identified in the Oracle Business Intelligence obis.properties file on the UNIX server.
The name of the local data source must also match the name of the data source defined in the Connection Pool object in the physical layer of the Oracle BI repository (.rpd) file.
If these conditions are not true, and if the server and client data sources are pointing at different databases, then erroneous updated row counts or incorrect results appear.
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.4/drivers.
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.
This section contains the following topics:
Configuring the DataDirect Connect ODBC Driver for Microsoft SQL Server 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
When you install Oracle BI EE, the required DataDirect 7.1.4 drivers are installed and automatically configured. You can define the default settings in obis.properties and odbc.ini files.
You will 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
This configuration requires you to modify the essbase.cfg file and the epmsys_registry.bat utility (epmsys_registry.sh for Linux and UNIX) so that Essbase connects to the DataDirect 7.1.4 drivers.
To modify the essbase.cfg file:
Open essbase.cfg for editing. You can find essbase.cfg at:
BI_DOMAIN/config/fmwconfig/biconfig/essbase
Locate the BPM_ORACLE_DriverDescriptor entry and change the value to "DataDirect 7.1.4 Oracle Wire Protocol".
Use Fusion Middleware Control to restart Essbase.
To modify the Essbase Server registry:
Open a command prompt and enter and run the command to view the ESSBASE_SERVER registry properties.
For Windows:
C:\OracleBI\instances\instance1\config\foundation\11.1.2.0>epmsys_registry.bat view ESSBASE_SERVER
For Linux/UNIX:
C:/OracleBI/instances/instance1/config/foundation/11.1.2.0>epmsys_registry.sh view ESSBASE_SERVER
Locate and copy the ID property.
Enter and run the following command to update the DataDirect version listed in the BPM_Oracle_DriverDescriptor property.
For Windows:
epmsys_registry.bat updateproperty #your_componentID/@BPM_Oracle_DriverDescriptor "DataDirect 7.1 Oracle Wire Protocol"
For Linux/UNIX:
epmsys_registry.sh updateproperty #your_componentID/@BPM_Oracle_DriverDescriptor "DataDirect 7.1 Oracle Wire Protocol"
If you scaled your Oracle BI EE environment, then repeat the above steps for all instances.
Use Fusion Middleware Control to restart Essbase.
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:
Open the obis.properties file. You can find this file at:
BI_DOMAIN/config/fmwconfig/bienv/obis
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/bifoundation/server/bin, $ORACLE_HOME/bifoundation/web/bin, $ORACLE_HOME/clients/epm/Essbase/EssbaseRTC/bin, $ORACLE_HOME/bifoundation/odbc/lib, $ORACLE_INSTANCE, $ORACLE_HOME/lib
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/common/ODBC/Merant/7.1.4/drivers, $ORACLE_HOME/bifoundation/server/bin, $ORACLE_HOME/bifoundation/web/bin, $ORACLE_HOME/clients/epm/Essbase/EssbaseRTC/bin, $ORACLE_HOME/bifoundation/odbc/lib, $ORACLE_INSTANCE, $ORACLE_HOME/lib
In obis.properties, locate the PATH variable and if necessary, include the DataDirect driver path.
Save and close the file.
Open the odbcinst.ini file. You can find this file at:
BI_DOMAIN/config/fmwconfig/bienv/core
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.4/drivers/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
Save and close the odbc.ini file.
Restart OBIS1.
Open the repository in the Administration Tool on a Windows computer.
In the Physical layer, double-click the database object for the Microsoft SQL Server database.
Click OK.
Save and close the repository.
On the Linux or UNIX computer, shut down Oracle Business Intelligence.
Copy the repository from the Windows computer to the Linux or UNIX computer.
Start Oracle Business Intelligence on the Linux or UNIX computer.
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:
Open the obis.properties file. You can find this file at:
BI_DOMAIN/config/fmwconfig/bienv/obis
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/bifoundation/server/bin, $ORACLE_HOME/bifoundation/web/bin, $ORACLE_HOME/clients/epm/Essbase/EssbaseRTC/bin, $ORACLE_HOME/bifoundation/odbc/lib, $ORACLE_INSTANCE, $ORACLE_HOME/lib
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.4/drivers, $ORACLE_HOME/bifoundation/server/bin, $ORACLE_HOME/bifoundation/web/bin, $ORACLE_HOME/clients/epm/Essbase/EssbaseRTC/bin, $ORACLE_HOME/bifoundation/odbc/lib, $ORACLE_INSTANCE$:$ORACLE_HOME/lib
In obis.properties, locate the PATH variable and if necessary, include the DataDirect driver path.
Save and close the file.
Open the odbc.ini file. You can find this file at:
BI_DOMAIN/config/fmwconfig/bienv/core
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.4/drivers/ARmysql27.so Description=DataDirect 7.1.4 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
Save and close the odbc.ini file.
Restart OBIS1.
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:
Open the obis.properties file. You can find this file at:
BI_DOMAIN/config/fmwconfig/bienv/obis
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/bifoundation/server/bin, $ORACLE_HOME/bifoundation/web/bin, $ORACLE_HOME/clients/epm/Essbase/EssbaseRTC/bin, $ORACLE_HOME/bifoundation/odbc/lib, $ORACLE_INSTANCE$:$ORACLE_HOME/lib
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/common/ODBC/Merant/7.1.4/drivers, $ORACLE_HOME/bifoundation/server/bin, $ORACLE_HOME/bifoundation/web/bin, $ORACLE_HOME/clients/epm/Essbase/EssbaseRTC/bin, $ORACLE_HOME/bifoundation/odbc/lib,$ORACLE_INSTANCE$:$ORACLE_HOME/lib
In obis.properties, locate the PATH variable and if necessary, include the DataDirect driver path.
Save and close the file.
Open the odbc.ini file. You can find this file at:
BI_DOMAIN/config/fmwconfig/bienv/core
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.4/drivers/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=
Save and close the odbc.ini file.
Restart OBIS1.
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:
Open the obis.properties file. You can find this file at:
BI_DOMAIN/config/fmwconfig/bienv/obis
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/bifoundation/server/bin, $ORACLE_HOME/bifoundation/web/bin, $ORACLE_HOME/clients/epm/Essbase/EssbaseRTC/bin, $ORACLE_HOME/bifoundation/odbc/lib, $ORACLE_INSTANCE$:$ORACLE_HOME/lib
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/common/ODBC/Merant/7.1.4/lib, $ORACLE_HOME/bifoundation/server/bin, $ORACLE_HOME/bifoundation/web/bin, $ORACLE_HOME/clients/epm/Essbase/EssbaseRTC/bin, $ORACLE_HOME/bifoundation/odbc/lib, $ORACLE_INSTANCE$:$ORACLE_HOME/lib
In obis.properties, locate the PATH variable and if necessary, include the DataDirect driver path.
Save and close the file.
Open the odbc.ini file. You can find this file at:
BI_DOMAIN/config/fmwconfig/bienv/core
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. Also, 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.4/drivers/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
Save and close the odbc.ini file.
Restart OBIS1.
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:
Open the obis.properties file. You can find this file at:
BI_DOMAIN/config/fmwconfig/bienv/obis
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/bifoundation/server/bin, $ORACLE_HOME/bifoundation/web/bin, $ORACLE_HOME/clients/epm/Essbase/EssbaseRTC/bin, $ORACLE_HOME/bifoundation/odbc/lib, $ORACLE_INSTANCE, $ORACLE_HOME/lib
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/common/ODBC/Merant/7.1.4/drvers, $ORACLE_HOME/bifoundation/server/bin, $ORACLE_HOME/bifoundation/web/bin, $ORACLE_HOME/clients/epm/Essbase/EssbaseRTC/bin, $ORACLE_HOME/bifoundation/odbc/lib, $ORACLE_INSTANCE, $ORACLE_HOME/lib
In obis.properties, locate the PATH variable and if necessary, include the DataDirect driver path.
Save and close the file.
Open the odbc.ini file. You can find this file at:
BI_DOMAIN/config/fmwconfig/bienv/core
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 path of the DataDirect Connect driver for Cloudera Impala. Also, 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 Impala_DB.
[Impala_DB] Driver=/ORACLE_HOME/bi/common/ODBC/Merant/7.1.4/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
Save and close the odbc.ini file.
Note that if you are using Impala 1.3.x, then you must complete the steps in the following "Configure Impala 1.3.x to 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.
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.
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. Note that there are three methods to specify this clause in the configuration, but Oracle recommends "Method 1: Modify the Impala daemon's default query options."
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.
Method 1: Modify the Impala daemon's default query options:
Oracle recommends this method of specifying the LIMIT clause.
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.
Go to the Cloudera Manager's home page and click the Impala service. The Impala service page appears.
In the Impala service page, click Configuration, and the select View and Edit. The Configuration page appears.
In the Configuration page, select Impala Daemon Default Group. Locate Impala Daemon Query Options Advanced (also called default_query_options) and add the following entries:
default_order_by_limit=2000000 abort_on_default_limit_exceeded=true
Click Save Changes.
In the Cloudera Manager's home page, restart the Impala service.
For queries over 2,000,000 rows, specify a higher default_order_by_limit value.
Method 2: Modify the Impala daemon's default query options when Impala is not managed by Cloudera Manager:
If your Impala environment is not managed by Cloudera Manager, then use the Impala product documentation to help you modify the LIMIT clause. To complete this task, use the "Configuring Impala Startup Options Through the Command Line" topic, located in the CDH 5 Installation Guide. This topic is available at the following location:
Using the "Configuring Impala Startup Options Through the Command Line" topic, add the following entry in IMPALA_SERVER_ARGS:
-default_query_options 'default_order_by_limit=2000000;abort_on_default_limit_exceeded=true'
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.
Method 3: Modify the DefaultOrderByLimit parameter in the odbc.ini Impala DSN entry:
Note:
Note that the Default Order By Limit can also be specified by 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.
Open the odbc.ini file. You can find this file at:
BI_DOMAIN/config/fmwconfig/bienv/core
Locate the Impala_DB database entry, and then locate the DefaultOrderByLimit parameter.
Update the value to 2000000. For example:
DefaultOrderByLimit=2000000
Save and close the odbc.ini file.
If you need your query to return more than 2,000,000 rows, then specify a higher DefaultOrderByLimit value.
The name of the DataDirect ODBC driver file to connect to a Apache Hive is libARhive27.so. See "System Requirements and Certification" for information about supported versions of Apache Hive. See "Limitations on the Use of Apache Hive with Oracle Business Intelligence" for information about using Apache Hive data sources.
To configure the DataDirect Connect ODBC Driver to connect to Apache Hive:
Open the obis.properties file. You can find this file at:
BI_DOMAIN/config/fmwconfig/bienv/obis
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/bifoundation/server/bin, $ORACLE_HOME/bifoundation/web/bin, $ORACLE_HOME/clients/epm/Essbase/EssbaseRTC/bin, $ORACLE_HOME/bifoundation/odbc/lib, $ORACLE_INSTANCE, $ORACLE_HOME/lib"
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/common/ODBC/Merant/7.1.4/drivers, $ORACLE_HOME/bifoundation/server/bin, $ORACLE_HOME/bifoundation/web/bin, $ORACLE_HOME/clients/epm/Essbase/EssbaseRTC/bin, $ORACLE_HOME/bifoundation/odbc/lib, $ORACLE_INSTANCE, $ORACLE_HOME/lib
In obis.properties, locate the PATH variable and if necessary, include the DataDirect driver path.
In obis.properties file, either above or below the LD_LIBRARY_PATH variable, create the HADOOP_DLL variable to point to the DataDirect driver.
For example:
HADOOP_DLL=ORACLE_HOME/bi/common/ODBC/Merant/7.1.4/drivers/ARhive27.so
Save and close the file.
Open the odbc.ini file. You can find this file at:
BI_DOMAIN/config/fmwconfig/bienv/core
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 Hive. Also, 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 Hive.
[Hive] Driver=/ORACLE_HOME/bi/common/ODBC/Merant/7.1.4/drivers/ARhive27.so Description=Oracle 7.1 Apache Hive Wire Protocol ArraySize=16384 Database=default DefaultLongDataBuffLen=1024 EnableDescribeParam=0 HostName=localhost LoginTimeout=30 MaxVarcharSize=2000 PortNumber=10000 RemoveColumnQualifiers=0 StringDescribeType=12 TransactionMode=0 UseCurrentSchema=0
Save and close the odbc.ini file.
Restart OBIS1.
Create a soft link libARicu27.so pointing to libARicu26.so.
For example:
cd //ORACLE_HOME/common/ODBC/Merant/7.1.4/drivers/ ln -s libARicu26.so libARicu27.so
Oracle Business Intelligence bundles UNIX ODBC drivers for some data sources, but not all. For these data sources, including Teradata and Oracle TimesTen In-Memory Database, you must install your own ODBC driver, then update the obis.properties and odbc.ini files to configure the data source.
If you are using Teradata, see also "Avoiding Spool Space Errors for Queries Against Teradata Data Sources" for related information.
To configure a database connection using a native ODBC driver:
Open the obis.properties file. You can find this file at:
BI_DOMAIN/config/fmwconfig/bienv/obis
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/opt/teradata/client/14.10/odbc_64/lib, $ORACLE_HOME/bifoundation/web/bin, $ORACLE_HOME/clients/epm/Essbase/EssbaseRTC/bin, $ORACLE_HOME/bifoundation/odbc/lib, $ORACLE_INSTANCE, $ORACLE_HOME/lib
If necessary, update the LD_LIBRARY_PATH variable to include the driver path. For example, to update the variable for the driver on Linux:
LD_LIBRARY_PATH=$ORACLE_HOME/opt/teradata/client/14.10/odbc_64/lib, $ORACLE_HOME/bifoundation/server/bin, $ORACLE_HOME/bifoundation/web/bin, $ORACLE_HOME/clients/epm/Essbase/EssbaseRTC/bin, $ORACLE_HOME/bifoundation/odbc/lib, $ORACLE_INSTANCE, $ORACLE_HOME/lib
In obis.properties, locate the PATH variable and if necessary, include the DataDirect driver path.
Save and close the file.
Open the odbc.ini file. You can find this file at:
BI_DOMAIN/config/fmwconfig/bienv/core
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 native ODBC driver for the database, with the library suffix that is appropriate for the operating system (for example, .so for Solaris and AIX, or .sl for HP-UX).
The following example provides details for a Teradata data source on Linux, with a data source name of Tera_Northwind.
[Tera_Northwind] Driver=/opt/teradata/client/14.10/odbc_64/lib/tdata.so Description=NCR 3600 running Teradata V2R6.2 DBCName=10.345.67.899 astUser= Username=northwind Password=northwind Database=northwind DefaultDatabase=northwind NoScan=no
Note that the DefaultDatabase parameter can be left empty only if you have selected the option Require fully qualified table names in the General tab of the Connection Pool dialog for this data source in the Administration Tool.
Still in the odbc.ini file, add an entry to the section [ODBC Data Sources] with the details appropriate for the data source. The following example provides details for a Teradata data source with a data source name of Tera_Northwind.
Tera_Northwind=tdata.so
Restart OBIS1.
Using the Administration Tool, open the repository and add the new DSN you created as the Connection Pool Data source name for the appropriate physical databases. See "Creating or Changing Connection Pools" for more information.
To set up Oracle TimesTen In-Memory Database data sources, first follow the instructions in "Configuring TimesTen Data Sources" to set up the TimesTen data source. Ensure that you go to the section "Configuring Database Connections Using Native ODBC Drivers" to obtain the correct steps for Linux and UNIX systems.
Next, review the best practices described in "Improving Use of System Memory Resources with TimesTen Data Sources" and implement them as needed.
Finally, if the user that starts OBIS1 does not have the path to the TimesTen DLL ($TIMESTEN_HOME/lib) in their operating system LD_LIBRARY_PATH variable (or SHLIB_PATH and LIBPATH on HP-UX and AIX, respectively), you must add the TimesTen DLL path as a variable in the obis.properties file.
To update obis.properties to include TimesTen variables on Linux and UNIX:
Open obis.properties for editing. You can find obis.properties at:
BI_DOMAIN/config/fmwconfig/bienv/obis
Add the required TimesTen variable TIMESTEN_DLL, and also update the LD_LIBRARY_PATH variable (or equivalent), as shown below:
TIMESTEN_DLL=$TIMESTEN_HOME/lib/libttclient.so LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$TIMESTEN_HOME/lib
Save and close the file.
Repeat these steps on each computer that runs the Oracle BI Server process. If you are running multiple Oracle BI Server instances on the same computer, then ensure that you update the ias-component tag appropriately for each instance in obis.properties (for example, ias-component id="coreapplication_obis1", ias-component id="coreapplication_obis2", and so on).
Restart OBIS1.
You can access Oracle RPAS ODBC data sources when the Oracle BI Server is running on an AIX UNIX platform. To configure this database connection, first update the odbc.ini file to configure the Oracle RPAS ODBC data source, then use the rdaadmin tool to define dimension tables as not normalized at run time.
See "Setting Up Oracle RPAS Data Sources" for information about configuring Oracle RPAS ODBC data sources on Windows.
To configure Oracle RPAS ODBC as a data source on AIX UNIX:
Log on as a separate telnet session.
Open the odbc.ini file. You can find this file at:
BI_DOMAIN/config/fmwconfig/bienv/core
In the RPAS data source section, edit the values. For example:
[RPAS Sample]
Data Source Name=RPAS Sample
Driver=[client RPASClient/lib/raix/oaodbc.so
DriverUnicodeType=1
Description=OpenRDA DSN
The Data Source Name you provide must match the value entered for DATABASE: in Step 3 of the following procedure. Also, you must add the line DriverUnicodeType=1 as shown in the preceding example.
To use the rdaadmin client tool to define dimension tables as not normalized at run time:
Locate the rdaadmin client tool in the following location:
/bin/rdaadmin
Run the rdaadmin client tool by typing the following command:
rdaadmin
Enter appropriate text when prompted, as follows:
DATABASE: [Oracle_RPAS_database_name]
The database name must match the name given for the Data Source Name in the previous task (for example, RPAS Sample).
ADDRESS: [ip_address]
PORT: [port_number]
An example port number value is 1707.
CONNECT_STRING: [NORMALIZE_DIM_TABLES=NO]
This value treats dimension tables as not normalized at run time.
TYPE: []
SCHEMA_PATH: []
REMARKS: []
The RPAS environment variable OPENRDA should be declared in the Oracle BI Server session on UNIX. For example, declare the variable as follows using the 64 bit rdaadmin client tool:
OPENRDA_INI=/rpasclient64/config/raix/openrda.ini export OPENRDA_INI
The Oracle BI Server uses the Essbase client libraries to connect to Essbase data sources. The Essbase client libraries are installed by default with Oracle BI Enterprise Edition. No additional configuration is required to enable Essbase data source access for full installations of Oracle BI Enterprise Edition.
However, for HP-UX Itanium systems, the following additional steps are required:
Define ESSLANG and LANG.
For example:
ESSLANG=English_UnitedStates.UTF-8@Binary export ESSLANG LANG=en_US.utf8 export LANG
Comment out LOCALE, SORT_ORDER_LOCALE, and SORT_TYPE in the NQSConfig.ini file. For example:
[ GENERAL ] // Localization/Internationalization parameters. // LOCALE="English-usa"; // SORT_ORDER_LOCALE="English-usa"; // SORT_TYPE="binary";
IBM DB2 Connect does not support the option of automatically disconnecting when an application using it receives an interrupt request.
When the native database uses DB2 Connect workstation, then you must change the setting of the parameter INTERRUPT_ENABLED. This parameter must be changed on any Oracle Business Intelligence computer if the database or any data source resides on IBM DB2 on a mainframe running z/OS or s/390 platforms.
Note:
If IBM DB2 is used, DB2 Connect must be installed on the Oracle BI Server computer. The version of DB2 Connect must match the most recent DB2 instance that was configured as a data source.To configure the INTERRUPT_ENABLED parameter:
Configure a database alias to be used as the native CLI Data Source Name. For example, create a new database entry using DB2 Configuration Assistant.
Using the database alias created and the name of the actual target DB2 database, set the INTERRUPT_ENABLED parameter using the following syntax:
uncatalog dcs db local_dcsname catalog dcs db local_dcsname as target_dbname parms \",,INTERRUPT_ENABLED\"
where:
local_dcsname represents the local name of the host or database (database alias name)
target_dbname represents the name of database on the host or database system
Note:
Ensure that you use backslashes to pass the quotation marks as part of the string.The following example uses an OS390 DB2 instance:
uncatalog dcs db DB2_390 catalog dcs db DB2_390 as Q10B parms \",,INTERRUPT_ENABLED,,,,,\" catalog database DB2_390 as DB2_390 at node NDE1EF20 authentication dcs