Installing the SQL Server database driver for Cognos Transformer
Install the Microsoft SQL Server database driver on the computer where you will host IBM® Cognos® Business Intelligence.
About this task
Follow these steps to install the SQL Server database driver for Cognos Transformer on AIX and Linux. Cognos supports ODBC type to connect SQL Server database. The only ODBC driver supported by Cognos is "Process DataDirect Connect for ODBC". The driver is not free. To avoid the cost for the licensed driver, consider deploying both Cognos BI and Transformer on Windows.
If you do not use the default database instance, then metrics.db.local.name should be set to the name of the database instance.
- You must install the 32-bit DataDirect driver on the server where you will deploy Cognos Transformer. The detailed installation instructions are in Installation on UNIX and Linux on the DataDirect Web site.
- Create a file named ..odbc.ini at a directory like /root/.odbc.ini. You can copy a template .odbc.ini from where the DataDirect ODBC driver is installed.
- Edit the odbc.ini configuring the
SQL Server data source as follows:
Here is an example of the .odbc.ini configuration with data source for metricsds:Where:
- In the [ODBC Data Sources] section, define a data source name and driver for the Metrics database, such as metricsds.
- In the [ODBC] section, specify the ODBC root directory and whether Driver Manager tracing is enabled.
- Create a section [metricsds] like the following sample and define the details in this section.
[ODBC Data Sources] metricsds=DataDirect 7.1 SQL Server Wire Protocol [ODBC] IANAAppCodePage=4 InstallDir=/opt/Progress/DataDirect/Connect_for_ODBC_71 Trace=0 TraceFile=odbctrace.out TraceDll=/opt/Progress/DataDirect/Connect_for_ODBC_71/lib/ivtrc27.so [metricsds] Driver=/opt/Progress/DataDirect/Connect_for_ODBC_71/lib/ivsqls27.so Description=DataDirect 7.1 SQL Server Wire Protocol Database=metrics HostName=lwptsthink68.cn.ibm.com PortNumber=1511
- Database indicates the name of the Metrics database. The name should be “Metrics”, which is decided by Connection database install wizard.
- HostName is the hostname of sqlserver machine.
- PortNumber is the port of the database instance where Metrics is created.
- To verify that DataDirect and the data source are configured
correctly, follow these steps:
- Set the appropriate library path environment variable
to specify the location of the ODBC libraries for your operating system:
- AIX: LIBPATH
- Linux: LD_LIBRARY_PATH
- Set the ODBCINI environment as follows:
- Go to the demo subdirectory in
the DataDirect installation directory, and run the command demoobc:
cd /opt/Progress/DataDirect/Connect_for_ODBC_71/samples/demo ./demoodbc -uid metricsuser -pwd **** metricsds
- Set the password as the Metrics database password of
your SQL Server. If you receive the following message,
it means your ODBC driver works fine and can connect to your Metrics
database (the "EMP" error does not matter):
./demoodbc DataDirect Technologies, Inc. ODBC Sample Application. ./demoodbc: will connect to data source 'metricsds' as user 'metricsuser/password1'. ......SQLExecute has Failed. RC=-1 SQLSTATE = S0002 NATIVE ERROR = 208 MSG = [DataDirect][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]Invalid object name 'EMP'.
- Set the appropriate library path environment variable to specify the location of the ODBC libraries for your operating system: