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.
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. You must set up data source connections for import on Windows.
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
You can have a single repository that contains both DataDirect Connect ODBC connections and native ODBC connections. If you are using the native ODBC drivers and DataDirect ODBC drivers, you must manage the drivers with 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, you must manage the driver with the DataDirect ODBC driver manager that is bundled with Oracle BI EE.
Learn about the environment variable settings that you must configure for Oracle Database and DB2 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 BI EE. 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 BI EE 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, you must do 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
For an example, see 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.
The shell script excerpts shown are examples only and are not recommendations for particular software platforms. See System Requirements and Certification and Configuring 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 # DB2 Parameters 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 ############################################################### # Solaris: Oracle BI 64 bit mode ############################################################### #set +u # 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 #--------------------------- # DB2 Parameters 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 ############################################################### # HPUX Itanium: Oracle BI 64 bit mode ############################################################### #set +u # 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 #--------------------------- # DB2 Parameters #--------------------------- #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 ############################################################### # 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 #--------------------------- # DB2 Parameters 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
Learn when you can use the Update Rowcount function.
If the following are true:
You are using the Update Rowcount 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. Configure Oracle Database or DB2 clients 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 BI obis.properties file on the UNIX server.
The name of the local data source matches the name of the data source defined in the Connection Pool object in the physical layer of the Oracle BI repository (.rpd) file.
If the above 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.
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 EE 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.
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.
The DataDirect drivers are installed in the Oracle Business Intelligence is 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 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.
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.
When you install Oracle Business Intelligence, 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:
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
The name of the DataDirect ODBC driver file to connect to a Microsoft SQL Server database is ARsqls27.so.
See System Requirements and Certification for supported versions of Microsoft SQL Server.
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. See System Requirements and Certification.
The name of the DataDirect ODBC driver file to connect to a Sybase ASE database is ARase27.so.
See System Requirements and Certification for supported versions of Sybase ASE.
Use these steps to configure the DataDirect ODBC driver file to connect to an Informix database. The file to use is ARifcl27.so.
See System Requirements and Certification for supported versions of Informix.
The DataDirect ODBC driver file name, to connect to a Cloudera Impala database is ARimpala27.so.
See System Requirements and Certification for the supported versions of Cloudera Impala and 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 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.
Use the first 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, use DefaultOrderByLimit, the recommended method for updating the Impala daemon. 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. 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 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.
odbc.ini file from the BI_DOMAIN/config/fmwconfig/bienv/core directory.DefaultOrderByLimit parameter.DefaultOrderByLimit=2000000.odbc.ini file.The name of the DataDirect ODBC driver file to connect to a Apache Hive is libARhive28.so.
See System Requirements and Certification and Limitations on the Use of Apache Hive with Oracle Business Intelligence.
See Quick Start: Progress DataDirect for ODBC for Apache Hive Wire Protocol Driver for UNIX/Linux located in the mwhome\bi\common\ODBC\Merant\8.0.2\help directory.
Oracle BI EE 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 Avoiding Spool Space Errors for Queries Against Teradata Data Sources.
You must perform some prerequisite tasks before setting up Oracle TimesTen In-Memory Database data sources.
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.
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.
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 Defining Dimension Tables as Not Normalized in Oracle RPAS ODBC on AIX UNIX. You must add the line DriverUnicodeType=1 as shown in the preceding example.
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 Business Intelligence. No additional configuration is required to enable Essbase data source access for full installations of Oracle BI EE .
You must perform the additional configuration steps listed below for HP-UX Itanium systems.
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. You must change the parameter 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.