[zLinux] RHEL and SUSE: Configuring an Oracle datasource to use ODBC

First you install platform-specific versions of unixODBC, then configure them.

Before you begin

Restriction: This topic only applies to Netcool Configuration Manager installations on Linux on System z.

Procedure

Install unixODBC

Perform the following platform-specific installation steps for unixODBC.
- For SUSE Linux Enterprise Server 12, you compile the unixODBC Driver Manager on Linux operating systems for use with CLI and ODBC applications. For the s390x platform, unixODBC needs to be compiled as a 31-bit application.
  1. Download the latest unixODBC source code from the following location: http://www.unixodbc.org
  2. Untar the source files:
```
gunzip unixODBC-2.3.4.tar.gz 
tar -xvf unixODBC-2.3.4.tar 
```
  3. Install the gcc-32bit Compiler package:
```
[root]:#~ zypper install gcc-32bit
```
  4. Set the following environment variables:
```
[root]:# export CFLAGS=-m31 LDFLAGS=-m31 CXXFLAGS=-m31
```
  5. Change to the /install_location/unixODBC-2.3.4 directory.
  6. Install the driver manager in the default /usr/localprefix directory:
```
./configure 
```
  7. Build and install the driver manager:
```
make
make install 
```
    Libraries are copied to the [prefix]/libdirectory, and executable files are copied to the [prefix]/bindirectory
  8. Add the unixODBC library directory /usr/local/lib to /etc/ld.so.conf and issue the ldconfig command to regenerate dynamically linked libraries.
- For RHEL 7, you install 31 bit unixODBC pkg and associated dependencies for zLinux:
```
[root]# yum install unixODBC.s390
```

Configure unixODBC

Download the appropriate versions of the Oracle Instant Client Package from the following Oracle support site: http://www.oracle.com/technetwork/topics/zlinuxsoft-096525.html
- Oracle Instant Client Package Basic 31bit
- Oracle Instant Client Package ODBC 31bit
Example for Oracle 12.1.0.2.0:

instantclient-basic-linux.zseries31-12.1.0.2.0.zip

Unpack the Oracle Instant Client Package Basic 31bit into /opt/oracle/clients to create the instantclient_12_1 file.

instantclient-odbc-linux.zseries31-12.1.0.2.0.zip

Unpack Oracle Instant Client Package ODBC 31bit into the same directory.

Create /etc/oracle/tnsnames.ora and define the ITNCM DB connection details.

ITNCM= (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = <Fully qualified Domain Name>)(PORT = <Port on which the Oracle database is listening>)) ) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ITNCM) (INSTANCE_NAME=ORCL) ))

Tip: INSTANCE_NAME is usually ORCL

Check the SERVICE_NAME and INSTANCE_NAME using the lsnrctl application, status command on the server running the ITNCM Oracle database.

Example tnsnames.ora:

ITNCM= (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = myhost.example.com)(PORT = 1521)) ) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ITNCM) (INSTANCE_NAME=ORCL) ))

Point to the location of the tnsnames.ora file using one of the following techniques:
- Create a profile script /etc/profile.d/oracle_instant_client.sh to define the location of the tnsnames.ora file.
- Export the TNS_ADMIN variable that points to the directory containing the tnsnames.ora file.
```
export TNS_ADMIN=/etc/oracle 

#!/bin/bash 
TNS_ADMIN=/etc/oracle 
export TNS_ADMIN
```
  Source the oracle_instant_client if not already configured in your unix login profile
```
. /etc/profile.d/oracle_instant_client.sh
```
  Verify that the TNS_ADMIN environment variable is set correctly
```
[root]# echo $TNS_ADMIN
```
Add the Oracle library directory /opt/oracle/clients/instantclient_12_1 to /etc/ld.so.conf and use the ldconfig command to regenerate dynamically linked libraries.

Configure and install the unixODBC Driver Manager by locating the odncinst.ini and odbc.ini files using the odbcinst command.

root]# odbcinst -j
unixODBC 2.3.1
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /root/.odbc.ini
SQLULEN Size.......: 4
SQLLEN Size........: 4
SQLSETPOSIROW Size.: 2

Configure odncinst.ini and odbc.ini

odncinst.ini example:

[OracleODBC-12c]
Description=TEST ODBC
Driver=/opt/oracle/clients/instantclient_12_1/libsqora.so.12.1
Setup=
FileUsage=1
CPTimeout=5
CPReuse=5
UsageCount=5

Note: The 'Driver' value is the path to the Oracle libsqora.so.12.1 library.

odbc.ini example:

[ITNCM]
Description="TEST"
Driver=OracleODBC-12c
DSN=OracleODBC-12c
ServerName=ITNCM

After you have configured odncinst.ini and odbc.ini you install the driver and system DSN:

Install the driver:

[root]# odbcinst -i -d -f /etc/odbcinst.ini
odbcinst: Driver installed. Usage count increased to 5.
   Target directory is /etc
odbcinst: Driver installed. Usage count increased to 5.
   Target directory is /etc

Install system DNS:

[root]# odbcinst -i -s -l -f /etc/odbc.ini

Test your system DSN installation by listing your installed data sources:
```
[root] # odbcinst -s -q
[ITNCM]
```

Use the isql tool to test a connection to the ITNCM database via ODBC.

[root]# isql -v ITNCM <UserID> <Password>
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL>

As the root user, change the access permissions to r-w for the directory containing the odncinst.ini and odbc.ini files.
Configure a ODBC datasource to connect to the ITNCM db in Cognos.
1. In IBM Cognos Administration, select the Configuration tab, then click Data Source Connections > New Data Source.
2. On the name and description page, enter ITNCM in the Name field. Optionally, enter a description and screen tip, and then click Next.
3. On the connection page, open the Type drop-down list, and select New Data Source.
4. Select IBM Oracle from the list of data sources.
5. Deselect Configure JDBC Connection, then click Next.
6. Specify the connection parameters for the connection string.
In Netcool Configuration Manager ITNCM Account Management > Users: In order for a user ID to be able to run the reports, map the LDAP user IDs exactly into Netcool Configuration Manager and assign the user ID to the Administrator group.