Additional steps necessary to prepare CABI on Linux to work with remote MS-SQL Server.

Document ID : KB000029114
Last Modified Date : 14/02/2018
Show Technical Document Details

 

CABI Reporting using datadirect drivers on UNIX

 

1     INTRODUCTION

1.1  PRE-REQUISITES

2     CREATING ODBC DSN ON UNIX PLATFORMS

3     ENABLING OEM DRIVERS

4     CONFIGURING CABI 3.3 ENVIRONMENT

 

5     Set up ODBC Environment Variable 

 

 

1       INTRODUCTION

As of CA Business Intelligence 3.3, now includes a unified DataDirect OEM driver called CRmsss23.so that is licensed for Crystal Reports, Web Intelligence (Webi) and Desktop Intelligence (Deski) reporting off Microsoft SQL Server.

 

1.1     Pre-requisites

·        CABI 3.3 Environment on Unix platform

·        Microsoft SQL Server

·        DSN created on the database server

 

1.2     Intended audience

·        Customers who are planning to use Sql server as a data source for crystal and webi reports on Unix environment

 

2       CReating ODBC DSN On UNix platforms

 

1.      Login to the UNIX machine

2.      Switch to the bobje user with which cabi is installed

3.      Traverse to /home/<bobjeuser>

4.      Open .odbc.ini file in vi editor

5.      It should look like below

 

[ODBC Data Sources]

BOE120=SQLAnywhere 12.0

BOE120_AUDIT=SQLAnywhere 12.0

 

[BOE120]

UID=dba

DatabaseName=BOE120

Host=127.0.0.1:3306

Driver=/opt/CA/SharedComponents/CommonReporting3/bobje//SQLAW/lib32/libdbodbc12.so

 

[BOE120_AUDIT]

UID=dba

DatabaseName=BOE120_AUDIT

Host=127.0.0.1:3306

Driver=/opt/CA/SharedComponents/CommonReporting3/bobje//SQLAW/lib32/libdbodbc12.so

 

6.       Modify the .odbc.ini to add the DSN. The new entry should look like below

 

[CRSQLServer]

Driver=$BOBJEDIR/enterprise120/<platform>/odbc/lib/CRmsss23.so

Description=DataDirect 5.3 SQLServer Wire Protocol Driver

Address=<sql_server host or ip>, <port>

Database=<db_name>

QuotedId=Yes

AnsiNPW=No

 

Note: if CABI is installed on default path then the DSN entry should look like below

 

[CRSQLServer]

Driver=/opt/CA/SharedComponents/CommonReporting3/bobje/enterprise120/linux_x86/odbc/lib/CRmsss23.so

Description=DataDirect 5.3 SQLServer Wire Protocol Driver

Address=cabi-pool2,1433

Database=TEST

QuotedId=Yes

AnsiNPW=No

LogonID=

Password=

 

7.      DSN name (CRSQLServer) must be the same DSN name you used when creating the ODBC connection in Windows database server.

8.      Save the ini file

 

 

3       Enabling OEM drivers

The drivers provided by XI are OEM drivers. Webi and Deski are dependent on the ConnectionServer. By default the ConnectionServer is set to use NON-OEM drivers. You must edit the connection server to allow the use of the OEM branded DD driver.

1.      Traverse to $BOBJEDIR, which is /opt/CA/SharedComponents/CommonReporting3/bobje in default conditions

2.      Make a backup copy of $BOBJEDIR/enterprise120/<platform>/dataAccess/RDBMS/connectionServer/odbc/odbc.sbo

3.      Open odbc.sbo with vi editor, search for DataDirect, there are 4 entries one for each MSSQL server we support.

4.      Change all four of the entries from No to Yes

Eg: <Parameter Name="Use DataDirect OEM Driver" Platform="Unix">Yes</Parameter>

4       Configuring CABI 3.3 environment

 

1.      Log out completely from your UNIX shell and log back in (to make sure new env variables are setup) as bobjeuser

2.      Traverse to $BOBJEDIR, which is /opt/CA/SharedComponents/CommonReporting3/bobje in default conditions

3.      Stop all the servers by executing the command “./stopservers”

4.      Wait for the servers to stop

5.      Execute the command source setup/env.sh to load all the environment variables

6.      Execute following command to add OEM drivers to the environment variables

 

export LD_LIBRARY_PATH= /opt/CA/SharedComponents/CommonReporting3/bobje/enterprise120/linux_x86/odbc/lib/:/opt/CA/SharedComponents/CommonReporting3/bobje/enterprise120/linux_x86/:/opt/CA/SharedComponents/CommonReporting3/bobje/enterprise120/linux_x86/dataAccess/RDBMS/ConnectionServer:$ LD_LIBRARY_PATH

 

7.      Start all the servers by executing the command “./startservers”

 We should now be able to to run Crystal, Webi, or Deski reports using the DataDirect odbc driver on linux.

 

5.      Set up ODBC Environment Variable

1)      Copy all the content from $HOME/.odbc.ini to $BOBJEDIR/odbc.ini

2)      Set ODBCINI environment variable to $BOBJEDIR/odbc.ini using the command

 

ODBCINI=$BOBJEDIR/odbc.ini

export ODBCINI

3)      Restart all the servers.