Irfan's World

February 17, 2013

JDBC connectivity in SAP BI 4.0

Filed under: SAP BI 4.0 Administration — Tags: , , — Irfan @ 11:32 pm

If you want to connect IDT(Information Design Tool)  to reporting data sources using JDBC you need a .jar file for that relational database.  Here in this example, I am going to show you how to connect to ORACLE database 11g and SQL Server 2008 databases.  First make sure you have the middle ware /client software installed on the machine where you are using IDT. Also every database vendor will provide their own JDBC connectivity driver software to connect to their databases or you can write your own class definition and use it.

The most common errors that you will see are the following ones when you try to connect to a database using JDBC drivers using IDT:

 Java Class not found in classpath and No suitable driver found (Generic)
 java.lang.ClassNotFoundException: oracle.jdbc.driver.OracleDriver (for Oracle only)
oracle.JDBC.OracleDriver (for Oracle only)
com.microsoft.sqlserver.jdbc.SQLServerDriver (for SQLServer only)

Oracle database

1) Lets create a new relational connection in IDT to connect to Oracle using JDBC drivers. If you don’t find ojdbc14.jar(required for oracle 11g) then you can download directly from www.oracle.com for the version you need.
image
2) Give it a name and click Next, Select the database middleware driver, Oracle 11.
image
3) In the above screen select JDBC Drivers and click Next to go to next screen
image
4) In the above screen specify the user name, password, oracle server host name:port # and oracle service.  Click Test Connection, it may display the error discussed above.  To resolve this  error copy the ojdbc14.jar to the following
directory : <install folder>\SAP BusinessObjects Enterprise XI 4.0\dataAccess\connectionServer\jdbc\drivers\oracle\ojdbc14.jar on the BI Server or a client machine.   If you don’t see oracle directory under drivers directory
in the said path then create it and save the .jar file under it.
5) Restart IDT and try creating the JDBC connection again, It should be successful and hence we resolved the error.

image



SQL Server 2008

Similarly trying to connect to SQL Server 2008  using IDT throws an error shown below.

image

To resolve the error, download the latest jdbc driver for SQL Server 2008 from Microsoft website (http://www.microsoft.com/en-us/download/details.aspx?id=11774) , As of this writing the file available from microsoft is “sqljdbc_4.0.2206.100_enu.exe”, extract this file to a temp folder on your machine and copy sqljdbc4.jar to the following folder: <install folder>\SAP BusinessObjects Enterprise XI 4.0\dataAccess\connectionServer\jdbc\drivers\sqlsrv2008.

After saving the sqljdbc4.jar in the right folder, try to create a new JDBC connection to connect to SQL Server 2008 using IDT the connection was successful.

image

One last thing I would like to bring is that most of jdbc connectivity errors will be resolved if the classes or jar files are found in the path environment variable.  That is all for today.

June 30, 2012

SAP BI 4.0 – Central Management Server ports

Filed under: Live Traffic, SAP BI 4.0 Administration — Tags: — Irfan @ 10:16 am

In BI 4.0 Central Management Server (CMS) uses two different ports:

a) Name Server Port

CMS by default uses port 6400 to communicate with Designer, WebI Rich Client, Information Design Tool, Business View Manager, application server(s) and may be firewall internal to the organization. In other words CMS listens for the requests on this port, this port is referred as name server port. When the BI server starts all the other BI services will register with CMS on name server port, CMS then sends its request port # to other service to communicate after its registration with CMS.

If the name server port is changed then all the BI services that needs to communicate with CMS has to know on which port the CMS is running to register themselves.

b) Request port
Each BI server binds to a request port, for example the Web Intelligence Processing Server  select its request port number dynamically when the server starts or restarts, unless it is configured to use a specific port number. This port is used by CMS to communicate with the Web Intelligence Processing Server.

For CMS the name server port and request port must be different otherwise the server will not start. If you by mistake set these ports with the same port # and saves it, the next time when the BI server restarts then CMS will not start because of port conflict. It may throw this error in CCM.   (@IRFANCLSTR is the cluster name)

 

image

To resolve the port conflict follow the following steps: 

  stop the SIA in CCM
  go to properties and startup tab
  click the central management server
  click properties and delete the request port shown below.   

image

Click OK and restart the SIA.

In conclusion, do not assign the same port # for CMS in CMC Launchpad as it is not advisable. You can also assign static ports to each of the BI services in BI 4.0 stack but that is all together a different topic.

image

Create a free website or blog at WordPress.com.