How to configure BIEE 11 Connection Pool for JDBC?

User_U0LN6

Dear Specialists,

Please could you guide me on the following topic:

In order to test different usage scenario, looking for the last millisecond of performance, I want to connect to biee 11 connection pool using JDBC.

I already have test's with OCI and ODBC but now I want a scenario with JDBC.

How to configure BIEE 11 Connection Pool for JDBC?

Thanks.

Michel.

Christian Berg-0racle

Hi  Michel,

https://rmoff.net/2016/03/28/connecting-to-obiee-via-jdbc-with-jisql/

User_U0LN6

Christian

I really appreciate your answer.

I had already found this link and some others, but I still do not understand the totality of how this can be accomplished.

Can I create a connection pool connection using jdbc (sources oracle18, vertica, apache drill and others using JDBC)?

I see that biee is capable of using jdbc but I don't see how I do this setting in the connection pool?

If you can help me out by doing that, I appreciate it.

Look this document https://docs.oracle.com/middleware/11119/biee/BIEIT/odbc_data_source.htm#BIEIT1738

in this words, I reed the readm.txt and magic is realized. But I dont understand this.

>

8.2.2 About Integrating with the Oracle BI Server Using JDBC

In addition to using ODBC, you can also integrate with the Oracle BI Server using JDBC. For full information, see the README.TXT file contained in the bijdbc.jar file. You can find the bijdbc.jar file in the following directory:

ORACLE_HOME/bifoundation/jdbc

<<<<

See README.TXT

<<<<

Oracle BI JDBC URL specification and Driver Configuration.

This document specifies the URL format for Oracle Business

Intelligence JDBC type 4 driver. This driver inteded for application

to connect to Oracle BI EE Server. It is not for the Oracle relational

database.

Revision History

-------------------------------------------------------------------------------

Date Author Comment

03/01/2006 Hsing-Chen Tsai Draft of the spec

06/13/2006 Hsing-Chen Tsai Add cluster spec

08/23/2006 Hsing-Chen Tsai Add SSL option

01/11/2008 Kenneth Eng Add additional properties

02/28/2008 Kenneth Eng Add details of failover logic

05/16/2011 Basavaraj Kirunge  ConnectionPoolDataSource Usage Details

07/26/2011 Added logging example

URL Format

-------------------------------------------------------------------------------

The URL is case insensitive. To connect to the Oracle BI Server, user

can specify the URL in following specification.

<URL>:= <Prefix>:[//<Host>:<Port>/][<Property Name>=<Property Value>;]*

Examples:

jdbc:oraclebi://localhost:9703/catalog=SalesSubjectArea;user=username;password=password;

jdbc:oraclebi://localhost:9703/NQ_SESSION.PREFERRED_CURRENCY=Currency 1;

jdbc:oraclebi://localhost:9703/USER=username;PASSWORD=password;LOGFILEPATH=/tmp;loglevel=finest;

In this example it �ll create the log file in /tmp/bijdbc.log with finest detail.

<Prefix>:= jdbc:oraclebi

<Host>:= The hostname of the analytics server it is connecting. It can

be IP Address or hostname.  Default is localhost

<Port>:= The port number that server is listening on. It can be

application name in the future if the server supports multiple

application on the same server machine. Default is 9703

<Property Name>:= <Catalog> | <Repository> | <User> | <Password> |

<SSL> |<SSLKeyStoreFileName> | < SSLKeyStorePassword> |

<TrustAnyServer> |

<TrustStoreFileName > |  <  TrustStorePassword> |

<LogLevel> | <LogFilePath> |

<PrimaryCCS> | <PrimaryCCSPort> |

<SecondaryCCS> | <SecondaryCCSPort> |

<MaxReconnectAttempts> | <RpcClientExpirationTime> |

<MaxRpcClientCount> | <MaxRpcClientCreateAttempts> |

<HeartbeatInterval> | <MaxHeartbeatAttempts>

Valid Property Values

-------------------------------------------------------------------------------

<Catalog>

It can be any catalog name that's available on the server. If it's

empty, then it will be default to the default catalog specified by

server. If the catalog name is not found in the server, it will still

use the default catalog and gives a warning during connect.

<Repository>

It can be any logical repository name that's available on the server. If it's

empty, then it will be default to the default repository specified by

server. For multi-tenancy mode, tenantguid:tenantservice should be used. NQ_SESSION.TENANTGUID and NQ_SESSION.TENANTSERVICE takes the priority if both are specified.

<User>

Specifies the username of the BI Server. Default is Administrator

<Password>

Specifies the password for BI Server for the username. The password

will be encrypted using 3DES.

<SSL>

True/False, default is False. Specifies if JDBC driver will use SSL or

not. If it's true, driver will check if SSLKeyStoreFileName is

readable, if not it will throw an error message.

<SSLKeyStoreFileName>

The file name that store SSL Keys. This file need to exist in the

local file and readable by the driver.

<SSLKeyStorePassword>

The password to open up the file pointed by SSLKeyStoreFileName.

<TrustAnyServer>

True | False, default is False.  Specifies if need to check trust

store for the server, if SSL is True. If TrustAnyServer is set to

false, driver will check if TrustStoreFileName is readable.

<TrustStoreFileName>

If TrustAnyServer is set to false, this property is needed to specify

the trust store file name

<TrustStorePassword>

If TrustAnyServer and TrustStoreFileName are specified, this specifies

the password to open up the file specified by TrustStoreFileName.

<LogLevel>

SEVERE | WARNING | INFO | CONFIG | FINE | FINER | FINEST

<LogFilePath>

This is a path to a directory. Only used if LogLevel is set.

The driver will log to a file named bijdbc.log in the specified path.

The logger name is "oracle.bi".

<PrimaryCCS>

This property specifies the primary CCS machine name instead of using

the "host" to connect. If this property is specified, the

"host" property value is ignored. Then jdbc driver will try to

connect to the CCS to obtain the load-balanced machine. Default is

localhost.

<PrimaryCCSPort>

This property specifies the primary CCS port number running on the

PrimaryCCS machine. Default is 9706.

<SecondaryCCS>

This property specifies the secondary CCS machine name instead of

using the "host" to connect. If this property is specified, then the

jdbc driver will try to connect to the CCS to obtain the load-balanced

machine. Default is localhost.

<SecondaryCCSPort>

This property specifies the secondary CCS port number running on the

secondary machine. Default is 9706.

<MaxReconnectAttempts>

This property specifies the number of attempts to establish an initial

connection or to reconnect a failed connection to an active server

node. Default is 3.

<RpcClientExpirationTime>

This property specifies the length of time in seconds for unused RPC

Clients to remain in the pool for future use. Default is 60 seconds.

<MaxRpcClientCount>

This property specifies the maximum number of RPC Client that is

available in the pool. Default is 100.

<MaxRpcClientCreateAttempts>

This property specifies the maximum number of attempts to open a new

RPC Client when there is no available RPC Client in the pool. Default

is 3.

<HeartbeatInterval>

This property specifies the interval in seconds between each heartbeat

message sent to the server for each connection. Default is 60 seconds.

<MaxHeartbeatAttempts>

This property specifies the maximum number of times a heartbeat

message can fail before a server is confirmed to be down. Default is

3.

URL Error Checking

-------------------------------------------------------------------------------

The driver is responsible for verify the URL entered by

application. If the application does not specify the wrong file name

for logging or the driver doesn't have permission to write to the

file, driver may fail to give connection and throws SQLException.

JDBC Driver Class Name

-------------------------------------------------------------------------------

To invoke the driver using the DriverManager interface, specify the

following line in the program:

Class.forName("oracle.bi.jdbc.AnaJdbcDriver");

JDBC Test Driver

-------------------------------------------------------------------------------

In the bijdbc.jar, a test client called JDBCCmd is included for 1.5

compliant version. A JVM 1.5 is required. The usage of the JDBCCmd is

not for production usage, rather for providing the client a way to

quickly test the driver. The usage of the JDBCCmd is:

java -jar bijdbc.jar -u <url> -s <src script directory>

-o <output script directory>

The source script directory contain text file that contains SQLs. The

SQLs are separated by semicolon, and can be multiple lines.

Additional Connection Properties for Authentication

-------------------------------------------------------------------------------

To access the BI server using proxy support, you can utilize the JDBC

Connection properties and supply the KEYWORD: IMPERSONATE.

Class.forName("oracle.bi.jdbc.AnaJdbcDriver");

String url = "jdbc:oraclebi://htsaidc5000:9703/catalog=snowflakeSales;USER=Administrator;PASSWORD=;";

Properties myprops = new Properties();

myprops.setProperty("IMPERSONATE", "USER1");

conn = DriverManager.getConnection(url, myprops);

After this is done, the username will be treated as USER1 when running

a report in Oracle BI Server. The report will be run as if user login

as USER1. However, the caller of the BI server does not need to

provide the password for user1. This typically is done for trusted

middle tier server.

To assert the impersonation is setup correctly, with a correct

repository configuration, you can get the NQ_SESSION.USER variable by

the following SQL.

public void testSessionValues() throws SQLException {

        Statement stmt = getConnection().prepareStatement("call NQSGetSessionValues('NQ_SESSION.USER')");

        ResultSet s = stmt.getResultSet();

        if (s.next()) {

            assertTrue(s.getString(1).equalsIgnoreCase("USER1"));

        }

       

    }

Failover Logic

-------------------------------------------------------------------------------

The failover process is transparent to the JDBC caller, i.e. the

caller will receive the expected results from its query as long as

failover is successful.

If a server failed during a JDBC call, the failover process is

performed in the following steps by the JDBC driver:

1. Obtain an active server node from the cluster controller server.

2. Open a new session on the active server node with the

previously-set session values.

3. Re-execute the logic to complete the JDBC call depending on the

stage of the request when the server failed.

Code example with comments on failover

Statement stmt = DriverManager.getConnection(url).createStatement();

ResultSet rs = stmt.executeQuery("select * from sales;");

rs.next(); // <-- Server fails at this point during fetching of the results.

           // Failover is triggered within rs.next(). After opening a new

           // session on the new server, the last query is re-executed by the

           // JDBC driver on the new server before the results are fetched.

A failure during failover results in a SQLException will be thrown if

failover did not succeed due to reasons such as:

1. Inability to connect to a cluster controller server

2. Inability to connect to a new server node

Web Logic

-------------------------------------------------------------------------------

There's a weblogic deployment template located in

common/templates/applications/oracle.bijdbc_template_11.1.1.jar

ConnectionPoolDataSource Usage Details

-------------------------------------------------------------------------------

Using ConnectionPoolDataSource significantly improves performance, and scalability in 3-tier and 2-tier environment.

ConnectionPoolDataSource can be used in cases where client frequently creates and releases connections to bi server, ConnectionPoolDataSource doesn't release a physical connection

when closed, it will reuse it on next connection request.

1. Using it on 2-tier Environment

  oracle.bi.jdbc.AnaJdbcConnectionPoolDataSource cpds = new oracle.bi.jdbc.AnaJdbcConnectionPoolDataSource();

  cpds.setMachineName("hostname");

  cpds.setDatabaseName("database");

  cpds.setDescription("Oracle BI Server");

  cpds.setPortNumber(Port Number);

  cpds.setUser("User_Name");

  cpds.setPassword("Password");

  try{

       PooledConnection conn = cpds.getPooledConnection("User_Name","Password");

       Connection physcon1 = conn.getConnection();

      

       // Uses physcon1 for running queries/creating Statements

      

       // close connection

    pyscon1.close();

    conn.close

      }

   Catch(SQLException sql){

  

   }

  

   //Close ConnectionPoolDataSource

   cpds.close();

  

2. Using it on 3-Tier Environment (Registering with JNDI)

   Create a DataSource using class oracle.bi.jdbc.AnaJdbcConnectionPoolDataSource on application server (like weblogic server) that provides JNDI services

   This steps registers this object with a JNDI name that can be queried by client code as follows.

  

         Context ctx = createContext();

         String jndiName = "jdbc/AppOBISDS";   //jndi name used to register on application server

         try {

             DataSource dataSource = (DataSource) ctx.lookup(jndiName);

             Connection physcon = dataSource.getConnection();

            // run queries on physcon

        

         //close connection

         physcon.close();

         dataSource.close();

         }catch(SQLException sql){

         }

  

  

  

<<<<

I can't clearly see how to set this up in the connection pool.

Thank you very much! Michel.

Gianni Ceresa

Hi,

The link to the doc you posted is to connect from a 3rd party tool to the BI Server to run queries against the BI Server.

If you said you already tested OCI and ODBC it sounds more like you want to use a JDBC driver as source inside the RPD, it's a totally different topic.

So what direction are you looking for? JDBC as a source to be used inside the RPD or connecting to the BI Server via JDBC to query the BI Server?

PS: OBIEE 11g is a tons of versions, could you be a bit more explicit on the version you are dealing with?

Christian Berg-0racle

+1 - was I mistaken about the direction you want to go? As Gianni said using the BI server as a JDBC source or using a JDBC source inside OBI?

User_U0LN6

Gentlemen Gianni  and  Christian

I'm sorry if I was not clear.

To clarify, I am looking for a way to configure the Connection Pool within RPD to query data in a source using JDBC (sources can be Oracle, Vertica, Apache Drill and others).

Using a JDBC source inside OBI (in rpd connection pool configuration instead of oci and odbc).

I know this works natively on biee12c. But at this moment I can't do the upgrade I would love to do.

So the focus is biee version is 11.1.1.1.9

Thank you very much for your time

Gianni Ceresa

OBIEE 11.1.1.9 is so old I don't remember if it was already possible (and don't have an Admintool to see if the entry was there already in the menu).

If you look at OBIEE tips and tricks: Using Apache Drill as a JDBC data source in OBIEE 12c  there is an interesting piece:

I will be experimenting with an approach based on a new feature of OBIEE 12c: the ability to have a JDBC/JNDI based data source in the repository

Not remembering and not having an OBIEE 11g around, if I take what has been written, it is a 12c thing only to use a JDBC as source in the RPD.

Look at the blog post and check if in your Admintool, once a RPD is open, you have something like the "Load Java sources" entry in the menu. That would be a sign that it really is only a 12c thing.

BI Publisher already had JDBC sources (as that's what the tool uses mainly), in case that could be an alternative.

Christian Berg-0racle

Beaten by Gianni, but to confirm: OBI can use JDBC as a source in the RPD as of 12c. 11g doesn't support it.

User_U0LN6

Srs Christian and Giani,

Thank you very much for your answers!

I couldn't find a way to use biee11 to source jdbc.

As BIP is not the tool because we depend on a complex and extensive physical and semantic layer.

I am starting the installation of biee12 and made the customer happy by punctuating the opportunities it brings.

Thank You, Michel.