Parent topic: Installing the database client for Cognos Transformer
On Microsoft Windows 2008, Cognos supports OLE DB type to connect SQL Server database. There is no need to install the database client for SQL Server, because Windows has the necessary drivers for the OLE DB connection already built-in.
When using the default database instance for SQL Server, leave the metrics.db.local.name
property blank in the cognos-setup.properties
file, so it looks like the following example:
In addition, there is a known issue when using the default database instance for SQL Server, described in the IBM Technote, OLEDB connection fails when Metrics database is deployed on the default instance of MS SQLSERVER
For Linux and AIX
On Linux and AIX, 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. If you want to avoid the cost for the licensed driver, you can consider deploying the Cognos transformer on Windows.
- 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:
- 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.
To verify that DataDirect and the data source are configured correctly, follow these steps:
Here is an example of the .odbc.ini
configuration with data source for metricsds
[ODBC Data Sources]
metricsds=DataDirect 7.1 SQL Server Wire Protocol
Description=DataDirect 7.1 SQL Server Wire Protocol
- 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.
- Set the appropriate library path environment variable to specify the location of the ODBC libraries for your operating system:
- Linux: LD_LIBRARY_PATH
- AIX: LIBPATH
Set the ODBCINI environment as follows:
For example, on Linux:
Go to the demo subdirectory in the DataDirect installation directory, and run the command demoobc:
Set the password as the Metrics database password of your SQL Server.
./demoodbc -uid metricsuser -pwd **** metricsds
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'.