Using JDBC drivers in SAP BusinessObjects 4.2 SP4 and SP5 with Lumira tools for 2.0 and 2.1

[rick_kruyf3]

With the move to Lumira Discovery, JDBC drivers are required to allow users to query databases directly without a universe connection.  This can lead to extra administrative work if there are existing ODBC drivers in use for the same datasources.  This guide talks about the steps required to support JDBC on the desktop and server.

These instructions have been validated with SBOP 4.2 SP4 and SP5 and with Lumira Discovery 2.0 and 2.1`

JDBC Connectivity

The setup is different for the workstation and the server.  If the user is accessing JDBC connections using a universe, there is no setup required on the user workstation if the connections are setup on the server.

Webi

Access JDBC Connections using J2EE Connection to CMS instead of direct connection to the CMS.  This will access the data in three tier mode and eliminate the need for middleware on the client workstation.

Lumira

Setup is different for workstation and client see following notes.

Crystal Reports Enterprise

Connections are setup on the server and consumed in the designer by connecting to the CMS.  This is recommended for users publishing Crystal Reports to BusinessObjects Enterprise.

Information Design Tool

Create the connections running the IDT on the server to setup the JDBC access.  Once the connections are working on the server, they will work for published content for Webi, Lumira and Crystal Reports Enterprise.

 

Lumira Discovery JDBC Setup

There are three steps to enable the JDBC drivers:

• Acquire JAR files


• Point Lumira to Jar Files


• Update .sbo URL format if required (Oracle)

Acquire Jar Files
The  JDBC driver jar file for the DBMS has to be accessible on the developers workstation.  For simplicity it is recommended to store all of the JDBC drivers under a single root (ex: C:\JDBC) with a directory for each database connection type.

C:\JDBC\Oracle

C:\JDBC\MSSQL

C:\JDBC\Informix

Pointing Lumira to the JAR file

The location of the .jar file is defined using File / Preferences in the Discovery tool.  For the required JDBC connection, click on the connection name, and select the install.  The user is then prompted to enter the location of the JAR file.

Update <Database>.sbo file

Files are located in:
C:\Program Files\SAP BusinessObjects Lumira\Lumira Discovery\Desktop\plugins\com.businessobjects.connectionserver.standalone_20.0.3.A_20170705-142429\ConnectionServer\jdbc\

For some databases like MSSQL, there are no changes required to this file, others like Oracle the default
URL Format may require changes.

For Oracle connections using TNSnames.ora, the following change is required.
jdbc:oracle:thin:@(DESCRIPTION= (ADDRESS= (PROTOCOL=tcp) (HOST=$DATASOURCE$)(PORT=1530)) (CONNECT_DATA= (SID=$DATABASE$)))

Server

There are four steps required for each JDBC driver to allow the connections to be used in SAP BusinessObjects:

• Acquire JAR files
  Get the required file as defined in the Data Access Guide.


• Update .sbo file Classpath to point to jar file
  Uncomment the Classpath and Path variables and set the path to jar file (directorypath\jarfile.jar)


• Update .sbo URL format if required
  Depending on setup for DBMS access in the environment the default URL may have to be changed.


• Create a Classpath entry under environment variables to point to the location of the jar files.
  The classpath variable should include all of the manually created directories for the .jar files published to the server.

Acquire JAR Files

Add a directory for each JDBC jar file in E:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\dataAccess\connectionServer\jdbc\drivers and copy the jar file to the location.

Update .sbo Classpath

• Identify the .sbo file for the driver


• Locate the Database to configure using the <Database Active= “Yes” Name= “DBMSName Version”


• Change
  <!-- Uncomment and edit the following lines
  to define java classes required by JDBC driver
  &lt;ClassPath&gt;
  &lt;Path&gt;your jar or class files directory&lt;/Path&gt;
  &lt;/ClassPath&gt;
  -->


• To
  <ClassPath>
  <Path>E:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\dataAccess\connectionServer\jdbc\drivers\<driverdirectory>\JDBCfile.jar</Path>                </ClassPath>

Update .SBO URL Format

The example below shows the change required to use the oracle TNSnames.ora, the change will not work if the database is not running on port 1530.

• Replace
  <!-- Original connection
  <Parameter Name="URL Format">jdbc:oracle:thin:@//$DATASOURCE$/$DATABASE$</Parameter>
  -->


• With
  <Parameter Name="URL Format">jdbc:oracle:thin:@(DESCRIPTION= (ADDRESS= (PROTOCOL=tcp) (HOST=$DATASOURCE$)
  (PORT=1530)) (CONNECT_DATA= (SID=$DATABASE$)))</Parameter>

 

 

 

Update Server Classpath

• In the Control Panel on the server, select System


• Select Advanced system settings


• Select Environment variables


• Under System Variables add a CLASSPATH variable set to the location of each JAR file added to the system. Only the directory is required, not the filename


• Validate by doing the following
  
  
  
  • Open a Dos prompt
  
  
  • Type Set and hit enter
  
  
  • Check the CLASSPATH variable refers to all required directories.
    CLASSPATH=E:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\dataAccess\connectionServer\jdbc\drivers\Oracle11;E:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\dataAccess\connectionServer\jdbc\drivers\MSSQL2012;E:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\dataAccess\connectionServer\jdbc\drivers\informix11;
  
  
  
  

Database Specific Instructions when defining JDBC connections

MS SQL SERVER

• When accessing SQLServer, the servername:port must be used instead of servername\\Instance name.
  servername\\instance will return the metadata (databases and tables, but will not return data)
  NOTE: When using Instance name, there has to be a double “\\” between the server name and the instance name.  A single “\” will return an error.


• maximum current version currently supported is sqljdbc41.jar, sqljdbc42.jar is available but will not connect.

Oracle

• When accessing Oracle databases, only use the hostname of the server, DO NOT add the port.  The port is hardcoded to use 1530, this is the default port.


• maximum current version currently supported is ojdbc6.jar

Informix

• Default Connection Information works


• jar is the currently supported driver.

 

How to define configuration of JDBC Drivers

To simplify the addition of JDBC drivers, I would recommend using a standlone tool like SQL Workbench to assist with the configuration.  The tool allows different versions of the driver to be tested and the URL format of the connection string to be tested and validated outside of the SAP BusinessObjects Environment.

From the website http://www.sql-workbench.net/index.html

“SQL Workbench/J is a free, DBMS-independent, cross-platform SQL query tool. It is written in Java and should run on any operating system that provides a Java Runtime Environment.”

The default connection string can be extracted from the <database>.sbo file and tested with hardcoded values in SQL workbench and then parameterized to work in the sbo file.  The tool only uses JDBC to connect to databases.  The install does not include all of the JDBC jar files but users can add them to the library for each driver manually.