Configuring Data Source Connections Using Native Gateways

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 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, 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

For an example, see Sample obis.properties Entries for Oracle Database and DB2 (32-Bit).

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

Save and close the file.
Restart OBIS1.

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 for information about supported software platforms 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

Note:

Troubleshooting OCI Connections

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.

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

About Updating Row Counts in Native Databases

Learn when you can use the Update Rowcount function.

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